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.
CodePudding user response：
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