How do I get total sum of data in every 3 minutes against 2 different IDs?

user_19915841 0
Viewed 73

Suppose I have a DataFrame like this:

ID-A   ID-B   time 
A      1      2022-02-14 00:01:07
              2022-02-14 00:02:06
              2022-02-14 00:02:55

A      2      2022-02-14 00:00:07
              2022-02-14 00:01:07

I want to sum it in a way such that each combination of ID1 and ID2 I have total sum of instances in every 3 minutes. The issue with calling resample for every 3 minutes is that it creates a new dataframe where the IDs are not retained.

Desired dataframe:

A     1    3
A     2    2
pandas dataframe numpy python
1 Answers
import pandas as pd
import numpy as np


df = pd.DataFrame({'ID-A':['A','A','A','A','A'],'ID-B':[1,1,1,2,2],
'time':['02-14-2022 00:01:07','02-14-2022 00:02:06','02-14-2022 00:02:55','02-14-2022 00:00:07','02-14-2022 00:01:07']})

df['datetime'] =pd.to_datetime(df['time'])
df.sort_values(['ID-A','ID-B','datetime'],inplace=True)

df_final = df.groupby(['ID-A','ID-B']).count().reset_index()
df_final

# df.dtypes

Hi Brother, if you only need the counts based on the group, please use the code above, but if you need the 3 mins as a group please confirm for me, I need to adjust the code

Thanks Leon

You want to get output like.

A     1    3
A     2    2

If A and 1 has 4 records but 3 of them in 3 min but the last one is not, the output will be like this right?

A     1    3
A     1    1
A     2    2
user_133069050
Related