Pandas DataFrame group by month and Count results if 0-CodePudding

I have two pandas Dataframe

Customer

CustomerID              
    1
    2
    3

Orders

OrderId  CustomerID      Date
1          1           2022-01-02
2          1           2022-01-04
3          2           2022-02-03
4          2           2022-03-03

I would like to transpose my results like this. For each month, I would like to know the count of orders. If there is some or not (0)

 CustomerID       Date       CountOrderID
    1            2022-01         2 
    2            2022-01         1
    3            2022-01         0
    1            2022-02         0 
    2            2022-02         1
    3            2022-02         0
    1            2022-03         0 
    2            2022-03         1
    3            2022-03         0

How I can do this in Pandas?

CodePudding user response：

Use GroupBy.size with DataFrame.reindex by new MultiIndex created by MultiIndex.from_product:

Orders['Date'] = pd.to_datetime(Orders['Date']).dt.to_period('m')

mux = pd.MultiIndex.from_product([Orders['Date'].unique(), Customer['CustomerID']], 
                                 names=['Date','CustomerID'])
df = (Orders.groupby(['Date','CustomerID'])
             .size()
             .reindex(mux, fill_value=0)
             .reset_index(name='CountOrderID'))

print (df)
      Date  CustomerID  CountOrderID
0  2022-01           1             2
1  2022-01           2             0
2  2022-01           3             0
3  2022-02           1             0
4  2022-02           2             1
5  2022-02           3             0
6  2022-03           1             0
7  2022-03           2             1
8  2022-03           3             0