Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Two dataframes merged
#5
Ahh almost got it... but not quite...
import os
import re
import datetime as dt
import numpy
import pandas as pd

data = None
try:
    df = pd.read_csv('privatefiles/t.csv',';')
    #df = pd.read_csv('...','\t')
    #xlsx = Excel_File('t.xlsx')
    #df = pd.read_excel(xlsx,0)
    print(df.head(3))
    print(len(df))
except Exception as err:
    print('Problem with reading the file.<br/> '+err)


checkin = df[df['Transaction']=='Check-in']
checkin = checkin.rename(columns={'Check-in':'dtime','Date':'date','Departure':'departure'}).reset_index()
checkin = checkin[['date','dtime','departure']]
#checkin.head(5)

checkout = df[df['Transaction']=='Check-out']
checkout = checkout.rename(columns={'Check-out':'atime','Amount':'cost','Destination':'arrival','Date':'date'}).reset_index()
checkout = checkout[['date','atime','arrival','cost']]
#checkout.head(5)

cleaned = pd.DataFrame(columns=['date','dtime','departure','atime','arrival','cost','duration','iswork'])
#cleaned

cleaned = pd.concat( [cleaned,checkin] , ignore_index=False, sort=False)
#cleaned.head(5)

cleaned.sort_values(by=['date','dtime'],ascending=[True,True],inplace=True)
checkout.sort_values(by=['date','atime'],ascending=[True,True],inplace=True)
#print(cleaned.head(5))
#print(checkout.head(3))

for i,r in checkout.iterrows():
    print(cleaned['date'][i])
    print(r['date'])
    print(cleaned['dtime'][i])
    print(r['atime'])
    f = cleaned[(cleaned['date']==r['date']) & (cleaned['dtime']<cleaned['atime'])]
    print(f)
    break
#cleaned.head(4)
Quote:04-05-2019
04-05-2019
09:53
10:02
Empty DataFrame
Columns: [date, dtime, departure, atime, arrival, cost, duration, iswork]
Index: []
I am obviously doing something wrong in the filter on the pandas. but the result is an empty dataframe... :/
Oh, and if it does find one (with no data already in) would it be updatable ?
Or do I need to update and send to another output dataframe ?

(Jun-06-2019, 07:02 PM)chisox721 Wrote: Is your goal to combine the data in the "checkin" and "checkout" frames? If that is the case, first off use the "merge" function - not "concat". Also, when using "merge" you need to specify which column you want to merge on (identify the column with identical values in the "checkin" and "checkout" frame).

If you attach the file you're working with I'll write out the script for you but it would be more helpful long term if you understand the concepts yourself.
Hi

I cannot use merged as you require a unique field. I do not have one.
I am trying to get the check out for each check in to produce a trip/journey, duration, cost etc...
But I need the check out that is immediately after the checkin (assuming I have checked in and out properly). There is a small chance I checked in and forgot to check out... Unlikely but possible.

Any ideas on why its not bringing back at least one record when filtered on date and time ?
Reply


Messages In This Thread
Two dataframes merged - by Ecniv - Jun-04-2019, 06:46 PM
RE: Two dataframes merged - by chisox721 - Jun-05-2019, 06:51 PM
RE: Two dataframes merged - by Ecniv - Jun-06-2019, 06:41 PM
RE: Two dataframes merged - by chisox721 - Jun-06-2019, 07:02 PM
RE: Two dataframes merged - by Ecniv - Jun-06-2019, 08:52 PM
RE: Two dataframes merged - by chisox721 - Jun-15-2019, 08:58 PM
RE: Two dataframes merged - by Ecniv - Jun-11-2019, 07:14 PM
RE: Two dataframes merged - by Ecniv - Jun-11-2019, 08:25 PM
RE: Two dataframes merged - by Ecniv - Jun-12-2019, 08:12 PM
RE: Two dataframes merged - by Ecniv - Jun-13-2019, 06:25 PM
RE: Two dataframes merged - by Ecniv - Jun-16-2019, 09:10 PM

Possibly Related Threads…
Thread Author Replies Views Last Post
  Merging two DataFrames based on indexes from two other DataFrames lucinda_rigeitti 0 1,725 Jan-16-2020, 08:36 PM
Last Post: lucinda_rigeitti
  Read exel with merged cells and write to another excel SriMekala 0 2,935 Aug-10-2019, 07:14 AM
Last Post: SriMekala
  how read and write merged cells in excel SriMekala 1 15,058 Aug-07-2019, 11:27 PM
Last Post: scidam

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020