How to pivot by keeping the index as year and month python-CodePudding

I am trying to group values by Year month, from my dataset by using below code

pd.to_datetime(All_Insurer_Portal_Reindex['New_Booking/Issued Date']).dt.month.value_counts().sort_index().to_frame()

But ending up by getting this PFA screen shot

enter image description here

but I am want this type of output PFA screen shot

enter image description here

which code I need to used to achieve this type of output

My data

New_Booking/Issued Date
09-10-2022
22-09-2022
10-10-2022
23-09-2022
11-10-2022
09-10-2021
22-09-2021
10-10-2021
23-09-2021
11-10-2021

CodePudding user response：

Use groupby with count aggregation

booking = df['New_Booking/Issued Date']
df.groupby([booking.dt.year, booking.dt.month]).count()

Output

                                                 New_Booking/Issued Date
New_Booking/Issued Date New_Booking/Issued Date                         
2021                    9                                              2
                        10                                             3
2022                    9                                              2
                        10                                             3

If the column is not in datetime format, use,

df['New_Booking/Issued Date'] = pd.to_datetime(df['New_Booking/Issued Date'], dayfirst=True)