pd.merge_asof with aggregation

user_1439912 0
Viewed 114

Given a data frame with start time of a new time period (a new work shift), sum all sales that occur up to next time period (work shift).

import pandas as pd
df_checkpoints = pd.DataFrame({'time':[1,5,10], 'shift':['Adam','Ben','Carl']})
df_sales = pd.DataFrame({'time':[2,6,7,9,15], 'soldCount':[1,2,3,4,5]})

# This is the wanted output...
df_output = pd.DataFrame({'time':[1,5,10], 'shift':['Adam','Ben','Carl'], 'totSold':[1,9,5]})

So pd.merge_asof does what I want except it only does 1:1 merge. Best would be to get a multiIndex dataframe with index[0] being the checkpoints and index[1] being the sales rows, such that I can aggregate freely afterwards. Last resort would be an ugly O(n) loop.

Number of rows in each df is a couple of millions. Any idea?

pandas time-series python
1 Answers

You can use pd.cut

For instance if you want to group by range you can use like this. As you aware I added 24 to show finish of range

pd.cut(df_sales["time"], [1,5,10,24])

If you want to automate this you can use like this:

get your checkpoints, add 24 to finish time, group it, sum sales, reset index for concat

group_and_sum = df_sales.groupby(pd.cut(df_sales["time"], df_checkpoints['time'].append(pd.Series(24))),as_index = False).sum().drop('time',axis=1)

concat 2 dataframes for names

pd.concat([group_and_sum,df_checkpoints],axis=1)

output

soldCount   time    shift
0   1   1   Adam
1   9   5   Ben
2   5   10  Carl
user_32312500
Related