pivot dataframe after groupby result with value_counts()

user_12247716 0
Viewed 51

I've tried a lot, but can't find the solution.

I run this code on my df:

df.groupby(df['Operatiejaar'])['ASA'].value_counts().sort_index()

This has the expected result:

Operatiejaar  ASA
2017          1.0    1523
              2.0    2658
              3.0     685
              4.0      28
2018          1.0    1357
              2.0    2749
              3.0     729
              4.0      26
2019          1.0    1505
              2.0    2770
              3.0     787
              4.0      27
              5.0       1
Name: ASA, dtype: int64

Now, I would like to pivot this to:

    ASA 1   ASA 2   ASA 3   ASA 4
Jaar                
2017    1523    2658    685     28
2018    1357    2749    729     26
2019    1505    2770    787     27

I made this pivot.df by hand now. My question is: Based on the groupby code above: how can I pivot my result to the wanted df? I believe this must be able, but how.... By the way: the result of the groupby is a Series. With pd.DataFrame(df) I've converted it to a DataFrame.

thanks!! greetings Jan

pandas pivot group-by
1 Answers

Use Series.unstack, then convert columns names to integers and last use DataFrame.add_prefix:

s = df.groupby(df['Operatiejaar'])['ASA'].value_counts().sort_index()

df1 = s.unstack().rename(columns=int).add_prefix('ASA ')

If need only first 4 rows per groups add GroupBy.head:

df1 = s.groupby(level=0).head(4).unstack().rename(columns=int).add_prefix('ASA ')
user_29010020
Related