CODEWITHSUNDEEP
pythonpandas
vestland
vestland

Reputation: 61254

Pandas: How to group by one column and show count for unique values for all other columns per group?

(Data sample and attempts at the end of the question)

With a dataframe such as this:

    Type    Class   Area    Decision
0   A       1       North   Yes
1   B       1       North   Yes
2   C       2       South   No
3   A       3       South   No
4   B       3       South   No
5   C       1       South   No
6   A       2       North   Yes
7   B       3       South   Yes
8   B       1       North   No

How can I group by Decision and get a count of Decision for unique values under the other columns so that I end up with this:

Decision  Area_North  Aread_South  Class_1  Class_2  Type_A  Type_B  Type_C
Yes       3           1            2        0        2       2       1
No        1           4            1        1        1       2       2

I was sure I could get a good start using groupby().agg() like this:

dfg = df.groupby('Decision').agg({'Type':'count',
                           'Class':'count',
                           'Decision':'count'})

And then pivot the result, but it's not enough by far. I'll need to include the unique values of all other columns somehow. I was sure I've seen somwehere that you could replace 'Position':'count' with 'Position':pd.Series.unique, but I can't seem to get it to work.

Code:

import pandas as pd

df = pd.DataFrame({'Type': {0: 'A',
                          1: 'B',
                          2: 'C',
                          3: 'A',
                          4: 'B',
                          5: 'C',
                          6: 'A',
                          7: 'B',
                          8: 'B'},
                     'Class': {0: 1, 1: 1, 2: 2, 3: 3, 4: 3, 5: 1, 6: 2, 7: 3, 8: 1},
                     'Area': {0: 'North',
                          1: 'North',
                          2: 'South',
                          3: 'South',
                          4: 'South',
                          5: 'South',
                          6: 'North',
                          7: 'South',
                          8: 'North'},
                     'Decision': {0: 'Yes',
                          1: 'Yes',
                          2: 'No',
                          3: 'No',
                          4: 'No',
                          5: 'No',
                          6: 'Yes',
                          7: 'Yes',
                          8: 'No'}})

dfg = df.groupby('Decision').agg({'Type':'count',
                           'Class':'count',
                           'Decision':'count'})
dfg

Upvotes: 2

Views: 167

Answers (2)

BENY
BENY

Reputation: 323396

melt with groupby + value_counts

s=df.melt('Decision').groupby(['Decision','variable']).\
    value.value_counts().unstack(level=[1,2],fill_value=0)
variable  Area       Class       Type      
value    South North     1  3  2    B  C  A
Decision                                   
No           4     1     2  2  1    2  2  1
Yes          1     3     2  1  1    2  0  2

You can also modify above columns by 

s.columns = s.columns.map('{0[0]}_{0[1]}'.format)

Upvotes: 2

jezrael
jezrael

Reputation: 863801

Use DataFrame.melt with DataFrame.pivot_table and flatten MultiIndex:

df = df.melt('Decision').pivot_table(index='Decision', 
                                     columns=['variable','value'], 
                                     aggfunc='size', 
                                     fill_value=0)
df.columns = df.columns.map('{0[0]}_{0[1]}'.format)
df = df.reset_index()
print (df)
  Decision  Area_North  Area_South  Class_1  Class_2  Class_3  Type_A  Type_B  \
0       No           1           4        2        1        2       1       2   
1      Yes           3           1        2        1        1       2       2   

   Type_C  
0       2  
1       0

Upvotes: 4

Related Questions