Calculate counter for each month for dataframe in Python

user_18396935 0
Viewed 42

I have the following dataframe:

cluster_ID counter_1 counter_2 date
0 1 0 2021-01-02 10:00:00
0 1 2 2021-01-03 12:00:24
0 0 1 2021-01-04 09:10:30
0 2 1 2021-02-15 08:10:21
0 1 1 2021-03-04 14:23:43
1 2 0 2020-12-30 13:16:45
1 2 3 2021-01-07 12:13:23
1 1 2 2021-03-06 07:28:23
2 1 1 2021-01-10 14:24:23
2 1 0 2021-01-15 17:23:35
2 0 1 2021-01-20 13:28:13
2 1 2 2021-02-11 11:23:15
3 3 2 2021-04-13 21:14:19

I would like to define a function that generates a new dataframe that includes 2 new columns for each existing month in the table for the date column generated from the counter_1 and counter_2 information. For each group by cluster_ID, sum for each counter separately the columns counter_1 and counter_2 during each month. If no values exist for that month, the resulting table shall be filled as 0. The value of date is a Python Timestamp.

Example of the resulting dataframe:

cluster_ID counter_1_2020-12 counter_2_2020-12 counter_1_2021-01 counter_2_2021-01 counter_1_2021-02 counter_2_2021-02 counter_1_2021-03 counter_2_2021-03 counter_1_2021-04 counter_2_2021-04
0 0 0 2 3 2 1 1 1 0 0
1 2 0 2 3 0 0 1 2 0 0
2 0 0 2 2 1 2 0 0 0 0
3 0 0 0 0 0 0 0 0 3 2

I hope you can help me with my problem. I am grateful for your help.

pandas dataframe date python
1 Answers

You can convert datetimes to YYYY-MM strings, pivoting by DataFrame.pivot_table with aggfunc='sum', sorting columns by dates and flatten MultiIndex by map:

df['date'] = pd.to_datetime(df['date'])

df1 = (df.assign(date = df['date'].dt.strftime('%Y-%m'))
         .pivot_table(index='cluster_ID', columns='date', fill_value=0, aggfunc='sum')
         .sort_index(level=[1,0], axis=1))
df1.columns = df1.columns.map(lambda x: f'{x[0]}_{x[1]}')
df1 = df1.reset_index()
print (df1)
   cluster_ID  counter_1_2020-12  counter_2_2020-12  counter_1_2021-01  \
0           0                  0                  0                  2   
1           1                  2                  0                  2   
2           2                  0                  0                  2   
3           3                  0                  0                  0   

   counter_2_2021-01  counter_1_2021-02  counter_2_2021-02  counter_1_2021-03  \
0                  3                  2                  1                  1   
1                  3                  0                  0                  1   
2                  2                  1                  2                  0   
3                  0                  0                  0                  0   

   counter_2_2021-03  counter_1_2021-04  counter_2_2021-04  
0                  1                  0                  0  
1                  2                  0                  0  
2                  0                  0                  0  
3                  0                  3                  2
user_29010020
Related