[FIXED] Counting number of occurrences for each item per month in pandas dataframe

Issue

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

Answered By - mozway