CODEWITHSUNDEEP
pythonpandas
SM079
SM079

Reputation: 743

Getting count of unqiue column values based on another column in df

I have df as below

User  Action
AA     Page1
AA     Page1
AA     Page2
BB     Page2
BB     Page3
CC     Page3
CC     Page3

Is there a way to get the count for different Pages for each user as a df. something like below

User  Page1  Page2  Page3
AA        2      1      0
BB        0      1      1
CC        0      0      2

So far i have tried to get the overall count all the actions per user

df['User'].value_counts().reset_index(name='Action')

What should i do to get the unique values of Action column as separate columns of a df and its count as values?

Upvotes: 2

Views: 65

Answers (1)

ThomasIsCoding
ThomasIsCoding

Reputation: 102529

You can try pandas.pivot_table like below

df.pivot_table(index="User", columns="Action", fill_value=0, aggfunc=len).reset_index()

or, a shorter code with pandas.unstack

df.value_counts().unstack(fill_value=0).reset_index()

which gives

Action User  Page1  Page2  Page3
0        AA      2      1      0
1        BB      0      1      1
2        CC      0      0      2

data

import pandas as pd

data = {
    "User": ["AA", "AA", "AA", "BB", "BB", "CC", "CC"],
    "Action": ["Page1", "Page1", "Page2", "Page2", "Page3", "Page3", "Page3"],
}

df = pd.DataFrame(data)

Upvotes: 1

Related Questions