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.

It's just an example. All I want to do is get a dataframe that shows the number of times and ID has a particular random_date in it's row. As I randomized the year, in the rows below, Jul 3 2019 may appear many times again (I don't know how many times exactly). — Kaustubh Mulay
– Kaustubh Mulay, Commented May 18, 2022 at 1:33

BENY · Accepted Answer · 2022-05-18 01:37:31Z

2

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()

answered May 18, 2022 at 1:37

BENY

324k22 gold badges176 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kaustubh Mulay Over a year ago

Thanks a ton. I never thought I could do a value counts when I know about this function. However, now I am trying to plot it, and the new dataframe doesn't have any headers. Will add them. Thanks.

Kaustubh Mulay Over a year ago

Hey Beny. I decided to go with the values counts code above instead of the groupby. However, now I am unable to plot using the plot_df function. It says "'DataFrame' object has no attribute 'Count_of_abs'" whereas I have already defined the column this way: abscount.columns = ["Counts_abs"] Any ideas?

BENY Over a year ago

out = df['Random_date'].value_counts().to_frame('Counts_abs') @KaustubhMulay

Collectives™ on Stack Overflow

Pivoting data in Python using Pandas

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related