Ana Ana - 7 months ago 6
Python Question

How to get the rows within a time limit using Python?

I read sales transactions table from Excel, and I'm interested to know the count of sales within 1 hour of the first items being sold. Let

A
be the sales report, I want to create
B
.

A=
item Location time
X Canada 10:03:18
X Canada 10:08:38
X Canada 10:24:46
X Canada 11:16:35
X US 10:00:16
X US 11:52:12
Y Canada 2:08:38
Y Canada 4:01:48
Y US 13:32:02
Y US 14:07:03

B=
item location first sale count
X Canada 10:03:18 3
X US 10:00:16 1
Y Canada 2:08:38 1
Y US 13:32:02 2


This is what I did:

A= A.sort('time', ascending=True).reset_index()
sale_loc= pd.DataFrame(A.groupby(['item', 'Location'], sort = False).first()).reset_index()
for i in sale_loc.index:
sale_cutoff = (A.time[i] + dt.timedelta(hours=1)).time


But I get error for the time manipulation part. I tried different functions, and I also tried add a new column A (time+1hour) instead of the loop, but similar issue...

Answer
import numpy as np
import pandas as pd

df = pd.DataFrame({'Location': ['Canada', 'Canada', 'Canada', 'Canada', 'US', 'US', 'Canada', 'Canada', 'US', 'US'], 'item': ['X', 'X', 'X', 'X', 'X', 'X', 'Y', 'Y', 'Y', 'Y'], 'time': ['10:03:18', '10:08:38', '10:24:46', '11:16:35', '10:00:16', '11:52:12', '2:08:38', '4:01:48', '13:32:02', '14:07:03']})

df['start'] = pd.to_datetime(df['time'])
grouped = df.groupby(['item', 'Location'])
df['end'] = (grouped['start'].transform(lambda grp: grp.min()+pd.Timedelta(hours=1)))
df['mask'] = (df['start'] < df['end'])

result = grouped['mask'].sum()
print(result)

yields

item  Location
X     Canada      3.0
      US          1.0
Y     Canada      1.0
      US          2.0
Name: mask, dtype: float64

The main idea is to group by item and Location, find the minimum starting time for each group, and then add 1 hour:

df['end'] = (grouped['start'].transform(lambda grp: grp.min()+pd.Timedelta(hours=1)))

transform returns a Series of the same length as df, so each row gets a value:

In [319]: df
Out[319]: 
  Location item      time               start                 end
0   Canada    X  10:03:18 2016-05-06 10:03:18 2016-05-06 11:03:18
1   Canada    X  10:08:38 2016-05-06 10:08:38 2016-05-06 11:03:18
2   Canada    X  10:24:46 2016-05-06 10:24:46 2016-05-06 11:03:18
3   Canada    X  11:16:35 2016-05-06 11:16:35 2016-05-06 11:03:18
4       US    X  10:00:16 2016-05-06 10:00:16 2016-05-06 11:00:16
5       US    X  11:52:12 2016-05-06 11:52:12 2016-05-06 11:00:16
6   Canada    Y   2:08:38 2016-05-06 02:08:38 2016-05-06 03:08:38
7   Canada    Y   4:01:48 2016-05-06 04:01:48 2016-05-06 03:08:38
8       US    Y  13:32:02 2016-05-06 13:32:02 2016-05-06 14:32:02
9       US    Y  14:07:03 2016-05-06 14:07:03 2016-05-06 14:32:02

Now you can easily identify the rows of interest. They are the ones where start is less than end:

In [320]: df['mask'] = (df['start'] < df['end'])
In [321]: df
Out[321]: 
  Location item      time               start                 end   mask
0   Canada    X  10:03:18 2016-05-06 10:03:18 2016-05-06 11:03:18   True
1   Canada    X  10:08:38 2016-05-06 10:08:38 2016-05-06 11:03:18   True
2   Canada    X  10:24:46 2016-05-06 10:24:46 2016-05-06 11:03:18   True
3   Canada    X  11:16:35 2016-05-06 11:16:35 2016-05-06 11:03:18  False
4       US    X  10:00:16 2016-05-06 10:00:16 2016-05-06 11:00:16   True
5       US    X  11:52:12 2016-05-06 11:52:12 2016-05-06 11:00:16  False
6   Canada    Y   2:08:38 2016-05-06 02:08:38 2016-05-06 03:08:38   True
7   Canada    Y   4:01:48 2016-05-06 04:01:48 2016-05-06 03:08:38  False
8       US    Y  13:32:02 2016-05-06 13:32:02 2016-05-06 14:32:02   True
9       US    Y  14:07:03 2016-05-06 14:07:03 2016-05-06 14:32:02   True

Grouping once more by item and Location, the desired result is found by summing up the number of times mask is True for each group:

result = grouped['mask'].sum()