Pivoting data in Python using Pandas

Question

I am doing a time series analysis. I have run the below code to generate random year in the dataframe as the original year did not have year values:

wc['Random_date'] = wc.Monthdate.apply(lambda val: f'{val} {randint(2019,2022)}') 
#Generating random year from 2019 to 2022 to create ideal conditions  

And now I have a dataframe that looks like this:

wc.head()

The ID column is the index currently, and I would like to generate a pivoted dataframe that looks like this:

Random_date	Count_of_ID
Jul 3 2019	2
Jul 4 2019	3

I do understand that aggregation will be needed to be done after I pivot the data, but the following code is not working:

abscount = wc.pivot(index= 'Random_date', columns= 'Random_date', values= 'ID')

Here is the ending part of the error that I see:

Please help. Thanks.

Answer 1

You may check with

df['Random_date'].value_counts()

If need unique count

df.reset_index().drop_duplicates('ID')['Random_date'].value_counts()

Or

df.reset_index().groupby('Random_date')['ID'].nunique()