After reviewing some of the answers I rewrited the code and added some descriptions
This does not work because I have a index error in pandas...
'''
This code intends to do an excel SUMIF between two tables with different indexes.
The first tables has GPS data with timestamp, vehicle ID and distance
The second table has vehicle ID and timestamps of events
I want to measure the distance run during events
Initially I tried to join the tables (dataframes) somehow while working with pandas but I failed
After that I made them lists
'''
import pandas as pd
from datetime import datetime
import xlrd
import numpy as np
import timeit
tic = timeit.default_timer()
dfRaw = pd.read_excel("C:\\Users\\pavlo\\PycharmProjects\\PEXproject1\\DataCleaning\\sample data\\VehicleEvents.xlsx", sheet_name="vhcllist") #reading the vehicle event list
dfRaw = dfRaw.fillna(2000, inplace=False) #replacing the NaN values with 2000 to avoid datetime errors
book = xlrd.open_workbook("C:\\Users\\pavlo\\PycharmProjects\\PEXproject1\\DataCleaning\\sample data\\VehicleEvents.xlsx") #I re-open the file, not sure why...
datemode = book.datemode
dfRaw["Engineon"].map(lambda x: # Because the date-times in xls were saved with the excel float format, I found this way to make it into datetime
xlrd.xldate_as_tuple(x, datemode))
dfRaw["Engineoff"].map(lambda x:
xlrd.xldate_as_tuple(x, datemode))
dfRaw["WorkStart"].map(lambda x:
xlrd.xldate_as_tuple(x, datemode))
dfRaw["WorkEnd"].map(lambda x:
xlrd.xldate_as_tuple(x, datemode))
dfRaw["ParkStart"].map(lambda x:
xlrd.xldate_as_tuple(x, datemode))
dfRaw["ParkEnd"].map(lambda x:
xlrd.xldate_as_tuple(x, datemode))
dfRaw["ENGINEON"] = dfRaw["Engineon"].map(lambda x: # I made new columns in the dataframe because I had trouble updating the current ones
datetime(*xlrd.xldate_as_tuple(x,
datemode)))
dfRaw["ENGINEOFF"] = dfRaw["Engineoff"].map(lambda x:
datetime(*xlrd.xldate_as_tuple(x,
datemode)))
dfRaw["WORKSTART"] = dfRaw["WorkStart"].map(lambda x:
datetime(*xlrd.xldate_as_tuple(x,
datemode)))
dfRaw["WORKEND"] = dfRaw["WorkEnd"].map(lambda x:
datetime(*xlrd.xldate_as_tuple(x,
datemode)))
dfRaw["PARKSTART"] = dfRaw["ParkStart"].map(lambda x:
datetime(*xlrd.xldate_as_tuple(x,
datemode)))
dfRaw["PARKEND"] = dfRaw["ParkEnd"].map(lambda x:
datetime(*xlrd.xldate_as_tuple(x,
datemode)))
dfRaw['TMP'] = dfRaw['ID']
dfRaw = dfRaw.drop('ID', axis=1)
dfRaw['ID'] = dfRaw['Vhcl']
templist = dfRaw[['ID', 'TMP', # I make the dataframe into a temp list
'ENGINEON', 'ENGINEOFF', 'WORKSTART',
'WORKEND', 'PARKSTART', 'PARKEND', 'Mode', 'Vhcl']]
vehiclist = [1, 2, 3, 4, 5, 6, 7, 8, 9] # Now it is a list of lists with the indexes I need
vehiclist[0] = templist['ID'].tolist()
vehiclist[1] = templist['TMP'].tolist()
vehiclist[2] = templist['ENGINEON'].tolist()
vehiclist[3] = templist['ENGINEOFF'].tolist()
vehiclist[4] = templist['WORKSTART'].tolist()
vehiclist[5] = templist['WORKEND'].tolist()
vehiclist[6] = templist['PARKSTART'].tolist()
vehiclist[7] = templist['PARKEND'].tolist()
vehiclist[8] = templist['Mode'].tolist()
for x in range(1,34): # here the code will read from 34 csv files containing GPS informations into a dataframe
df = pd.read_csv("C:\\Users\\pavlo\\PycharmProjects\\PEXproject1\\DataCleaning\\sample data\\GpsData"
+ str(x) + '.csv',
parse_dates=[10])
df['ID'] = df['gps_id']
gps = df[['ID','Timestamp','distance']] # here I copy the data from the dataframe to a list
gpslist = [1,2,3] # I make the list of lists
gpslist[0] = gps['ID'].tolist()
gpslist[1] = gps['Timestamp'].tolist()
gpslist[2] = gps['distance'].tolist()
driving = 0
idle = 0
working = 0
dists = [[], [], [], []] #this list of lists will capture the distances in the various states
for i in range(len(vehiclist[1])): #I go through all rows of vehicle list
driving = idle = working = 0
for j in range(len(gps[1])): #I go through all rows of gps list
if gps[1][j] <= vehiclist[3][i] or gps[1][j] >= vehiclist[2][i]: #I want to exclude if the vehicle was off at the gps timestamp
if vehiclist[0][i] == gps[0][j]:
c1 = gps[2][i]
c2 = gps[1][j]
if vehiclist[8][i] == 'Manual' :
if vehiclist[6][i] <= c1 <= vehiclist[7][i] :
driving += c2
if vehiclist[2][i] <= c1 <= vehiclist[6][i] :
idle += c2
else:
if vehiclist[7][i] <= c1 <= vehiclist[3][i] :
driving += c2
if vehiclist[2][i] <= c1 <= vehiclist[7][i] :
idle += c2
if vehiclist[3][i] <= c1 <= vehiclist[2][i - 1] :
working += c2
toc = timeit.default_timer()
if i % 100 == 0:
print('processing algorithm: {}'.format(toc - tic))
print('we are at row {}'.format(i))
dists[0].append(vehiclist[1][i])
dists[1].append(driving)
dists[2].append(idle)
dists[3].append(working)
driving = 0
idle = 0
working = 0
toc = timeit.default_timer()
np.savetxt("outfile" + str(x)
+ ".csv", np.column_stack((dists[0], dists[1], dists[2], dists[3])), delimiter=",", fmt='%s')
tac = timeit.default_timer()
print('exporting {}'.format(tac - toc))
for
-loop. \$\endgroup\$