| Date | Items | Location |
|---|---|---|
| 2022-01-01 | Item1 | Location1 |
| 2022-01-01 | Item2 | Location1 |
| 2022-01-02 | Item1 | Location3 |
| 2022-01-03 | Item3 | Location1 |
| 2022-02-01 | Item1 | Location2 |
| … | … | … |
I have a pandas dataframe thats similar to the table above. How do I create a new data frame with the Items column grouped and made into the index and the Date column organized into specific months with each month as a column? The numbers inside the dataframe will be the number of occurrences of each item for each month.
I’ll need to change the date to months and change the index, but I’m not too sure how to do it exactly.
The table of the table I want to create is something like the one below:
| – | Date | |
|---|---|---|
| Items | Jan | Feb |
| Item1 | 5 | 3 |
| Item2 | 3 | 2 |
| Item3 | 3 | 5 |
| … | … | … |
>Solution :
Looks like you want a crosstab using periods of your dates:
out = pd.crosstab(df['Items'], pd.PeriodIndex(df['Date'], freq='M'))
output:
col_0 2022-01 2022-02
Items
Item1 2 1
Item2 1 0
Item3 1 0