I have a pandas DataFrame like below:
| List Values | Date |
|---|---|
[1, 2, 3, 4] |
2022-07-17 |
[1, 3] |
2022-06-17 |
[1, 4, 5] |
2022-06-17 |
[2, 4, 1] |
2022-06-17 |
[1, 3] |
2022-05-17 |
The list contains unique elements so getting duplicate counts or conversion into a set would not be required.
I want to get a pivot table where the frequency of each element in list across month is derived, like below. The columns should be till month level (i.e. Year-Month):
| Value | 2022-05 | 2022-05 | 2022-05 |
|---|---|---|---|
1 |
1 | 3 | 1 |
2 |
0 | 1 | 1 |
3 |
1 | 1 | 1 |
4 |
0 | 2 | 1 |
5 |
0 | 1 | 0 |
.explode() and then use pd.crosstab():
df = df.explode("List Values")
out = pd.crosstab(df["List Values"], df["Date"])
print(out)
Prints:
Date 2022-05-17 2022-06-17 2022-07-17
List Values
1 1 3 1
2 0 1 1
3 1 1 1
4 0 2 1
5 0 1 0