Pandas groupby and convert grouped values to columns-CodePudding

I have a Dataframe like the following:

  Date          User 
01-06-2022       A
01-06-2022       A
02-06-2022       A
01-06-2022       B
01-06-2022       C
03-06-2022       A
03-06-2022       C
03-06-2022       B
03-06-2022       B

How can I group them by date and get the count of occurrence on same date and convert the users as columns?

Expected output:

Date         A   B   C
01-06-2022   2   1   1
01-06-2022   1   0   0
01-06-2022   1   2   1

CodePudding user response：

Let's try

out = (df.groupby(['Date', 'User']).size()
       .unstack(level=1)
       .fillna(0)
       .reset_index()
       .rename_axis(columns=None))

print(out)

         Date    A    B    C
0  01-06-2022  2.0  1.0  1.0
1  02-06-2022  1.0  0.0  0.0
2  03-06-2022  1.0  2.0  1.0

CodePudding user response：

import pandas as pd

df = pd.DataFrame( { 
    "Date" : ["01-06-2022", "01-06-2022", "01-06-2022", "02-06-2022  "] , 
    "User" : ["A", "A", "B", "A"] } )
df_out = df.groupby( [ "User","Date"] ).size().unstack(level=0).fillna(0)
df_out

This is based on sample of the data

User    A   B
Date        
01-06-2022  2.0 1.0
02-06-2022  1.0 0.0

CodePudding user response：

try this:

df.groupby([*df]).value_counts().unstack(fill_value=0)

>>>
    User    A   B   C
Date            
01-06-2022  2   1   1
02-06-2022  1   0   0
03-06-2022  1   2   1

CodePudding user response：

here is another way to do it. Create a on-the-fly column to use for count and then use pivot_table

df.assign(id=1).pivot_table(index='Date', columns='User', values='id', aggfunc='count').fillna(0).reset_index()

User    Date    A       B       C
0   01-06-2022  2.0     1.0     1.0
1   02-06-2022  1.0     0.0     0.0
2   03-06-2022  1.0     2.0     1.0