Grouping and sum the value for every 5min / resampling the data for 5min with string values-CodePudding

I want to sum the value of each gender for every 5 min timestamp.

Main Table:-

Time   Gender  value

10:01  Male      5
10:02  Female    1
10:03  Male      5
10:04  Male      5
10:05  Female    1
10:06  Female    1
10:07  Male      5
10:08  Male      5
10:09  Male      5
10:10  Male      5

Required Result:-

Time   Gender  value
10:00  Male     15
10:00  Female   2
10:05  Male     20
10:05  Female   1

CodePudding user response：

You could convert to TimeDelta, floor the result, and use it to groupby agg:

t = pd.to_timedelta(df['Time'] ':00')
(df
 .groupby([t.dt.floor('5min'), 'Gender']) 
 .agg({'value': 'sum'})
 .reset_index()
)

output:

             Time  Gender  value
0 0 days 10:00:00  Female      1
1 0 days 10:00:00    Male     15
2 0 days 10:05:00  Female      2
3 0 days 10:05:00    Male     15
4 0 days 10:10:00    Male      5

matching the provided output

To match your provided output, it needs a few more things.

subtracting one minute to floor '00:05:00' on '00:00:00'
converting back to string

t = pd.to_timedelta(df['Time'] ':00').sub(pd.to_timedelta('1min'))

(df
 .groupby([t.dt.floor('5min'), 'Gender']) 
 .agg({'value': 'sum'})
 .reset_index()
 .assign(Time=lambda d: (pd.to_datetime(0) d['Time']).dt.strftime('%H:%M'))
)

output:

    Time  Gender  value
0  10:00  Female      2
1  10:00    Male     15
2  10:05  Female      1
3  10:05    Male     20

variant

t = pd.to_timedelta(df['Time'] ':00').sub(pd.to_timedelta('1min'))
(df.assign(Time=t.dt.floor('5min').astype(str).str[-8:-3])
   .groupby(['Time', 'Gender'])
   ['value'].sum().reset_index()
)