CODEWITHSUNDEEP
pythonpandas
coder_bg
coder_bg

Reputation: 370

How to split two kind of values in the column and count the number of occurrence?

I want have a dataframe which has a TRUE and FALSE column. I want to get a dataframe like :

Sample Input

Country | Class | Catalog
A | abc | TRUE  
A | abc | FALSE
B | def | TRUE
C | ghi | FALSE

Sample Output

Country | Class | TRUE | FALSE | TOTAL 
A       |  abc  |    1 | 1     | 2
B       | def   |   1  | 0     | 1
C       | ghi   |   0  | 1     | 1

I had tried :

df.groupby(['Country','Class','Country'])['Catalog'].value_counts()

but I did not get the desired results.

Any help around this?

Upvotes: 1

Views: 128

Answers (1)

jezrael
jezrael

Reputation: 863256

Use crosstab with remove last row by DataFrame.iloc:

df1 = (pd.crosstab([df['Country'], df['Class']],
                    df['Catalog'],
                    margins=True,
                    margins_name='TOTAL')
         .iloc[:-1])
print (df1)
Catalog        False  True  TOTAL
Country Class                    
A       abc        1     1      2
B       def        0     1      1
C       ghi        1     0      1

Your solution should be changed by Series.unstack and added new column by DataFrame.assign filled by sums:

#removed repeated column in groupby
df1 = (df.groupby(['Country','Class'])['Catalog']
         .value_counts()
         .unstack(fill_value=0)
         .assign(Total = lambda x: x.sum(axis=1)))
print (df1)
Catalog        False  True  Total
Country Class                    
A       abc        1     1      2
B       def        0     1      1
C       ghi        1     0      1

Last for columns from MultiIndex add DataFrame.reset_index and DataFrame.rename_axis:

df1 = df1.reset_index().rename_axis(None, axis=1)
print (df1)
  Country Class  False  True  Total
0       A   abc      1     1      2
1       B   def      0     1      1
2       C   ghi      1     0      1

Upvotes: 2

Related Questions