Ahh almost got it... but not quite...
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 ?
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 ?
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-2019I am obviously doing something wrong in the filter on the pandas. but the result is an empty dataframe... :/
04-05-2019
09:53
10:02
Empty DataFrame
Columns: [date, dtime, departure, atime, arrival, cost, duration, iswork]
Index: []
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).Hi
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.
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 ?