CODEWITHSUNDEEP
pythonpython-3.xpandasdataframe
ash
ash

Reputation: 33

Groupby multiple column to find the unique count of one column using python pandas

I have dataframe like:

column1    column2    column3
 ram        tall        good
 rohan      short       fine
 ajay       tall        best
 alia       tall        good
 aman       medium      fine
 john       short       good
 jack       short       fine

now i need output like:

unique count of good in tall, short, medium on basis of column1->

tall=2 , short=1 , medium=0

unique count of fine in tall, short, medium on basis of column1->

tall=0 , short=2 , medium=1

unique count of best in tall, short, medium on basis of column1->

tall=1 , short=0 , medium=0

I am beginner in pandas. Thanks in advance

Upvotes: 1

Views: 51

Answers (2)

Dani Mesejo
Dani Mesejo

Reputation: 61930

Use value_counts + unstack

res = df[['column3', 'column2']].value_counts().unstack('column2', fill_value=0)
print(res)

Output

column2  medium  short  tall
column3                     
best          0      0     1
fine          1      2     0
good          0      1     2

As an alternative groupby + unstack:

res = df.groupby(['column3', 'column2']).count().unstack('column2', fill_value=0)
print(res)

Output (groupby)

        column1           
column2  medium short tall
column3                   
best          0     0    1
fine          1     2    0
good          0     1    2

The idea behind both approaches is to create an index and then unstack it. If you want to match the same order as specify in your question, convert to Categorical first:

df['column2'] = pd.Categorical(df['column2'], categories=['tall', 'short', 'medium'], ordered=True)
res = df[['column3', 'column2']].value_counts().unstack('column2', fill_value=0)
print(res)

Output

column2  tall  short  medium
column3                     
best        1      0       0
fine        0      2       1
good        2      1       0

Upvotes: 1

Shubham Sharma
Shubham Sharma

Reputation: 71707

Let's try pd.crosstab:

pd.crosstab(df['column3'], df['column2'])

column2  medium  short  tall
column3                     
best          0      0     1
fine          1      2     0
good          0      1     2

Upvotes: 5

Related Questions