2
\$\begingroup\$

The code is supposed to group start and end time logs and provide log counts and unique ID counts. The grouping will be variable 1 hour, 6 hours, 12 hours, 24 hours, etc.

What is the better way to rewrite the simpler version for this code?

Input: No of day reamins 'x' in inputs.

import pandas as pd

fn = r'Dart.csv'
df = pd.read_csv(fn)

df['m'] = df.StopTime + df.StartTime
df['d'] = df.StopTime - df.StartTime

#df.StartTime = pd.to_datetime(df.StartTime, unit='s')
#df.StopTime = pd.to_datetime(df.StopTime, unit='s')
# 'start' and 'end' for the reporting DF: `r`
# which will contain equal intervals (1 hour in this case)

start = pd.to_datetime(df.StartTime.min(), unit='s').date()
end = pd.to_datetime(df.StopTime.max(), unit='s').date() + pd.Timedelta(days=1) #change this to 'n'

# building reporting DF: `r`
freq = '1H'
idx = pd.date_range(start, end, freq=freq)
r = pd.DataFrame(index=idx)
r['start'] = (r.index - pd.datetime(1970,1,1)).total_seconds().astype(np.int64)

interval = 60*60 - 1

r['LogCount'] = 0
r['UniqueIDCount'] = 0

# counting ...
for i, row in r.iterrows():
    # intervals overlap test
    # https://en.wikipedia.org/wiki/Interval_tree#Overlap_test
    # i've slightly simplified the calculations of m and d
    # by getting rid of division by 2,
    # because it can be done eliminating common terms

    mask = np.abs(df.m - 2*row.start - interval) < df.d + interval
    r.ix[i, ['LogCount', 'UniqueIDCount']] = [len(df[mask]), df[mask].UserID.nunique()]

r['Day'] = pd.to_datetime(r.start, unit='s').dt.dayofweek
r['StartTime'] = pd.to_datetime(r.start, unit='s').dt.time
r['EndTime'] = pd.to_datetime(r.start + interval + 1, unit='s').dt.time

print(r[r.LogCount > 0])

Logs:

UserID, StartTime, StopTime, GPS1, GPS2
00022d9064bc,1073260801,1073260803,819251,440006
00022d9064bc,1073260803,1073260810,819213,439954
00904b4557d3,1073260803,1073261920,817526,439458
00022de73863,1073260804,1073265410,817558,439525
00904b14b494,1073260804,1073262625,817558,439525
00022d1406df,1073260807,1073260809,820428,438735
00022d9064bc,1073260801,1073260803,819251,440006
00022dba8f51,1073260801,1073260803,819251,440006
00022de1c6c1,1073260801,1073260803,819251,440006
003065f30f37,1073260801,1073260803,819251,440006
00904b48a3b6,1073260801,1073260803,819251,440006
00904b83a0ea,1073260803,1073260810,819213,439954
00904b85d3cf,1073260803,1073261920,817526,439458
00904b14b494,1073260804,1073265410,817558,439525
00904b99499c,1073260804,1073262625,817558,439525
00904bb96e83,1073260804,1073265163,817558,439525
00904bf91b75,1073260804,1073263786,817558,439525
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.