I'm quite new to Python.
I've the below sample dataframe df.
Col1 Col2 Col3
0 0 1 1
1 1 1 0
2 0 1 1
3 1 0 1
4 0 0 1
If I use this code df.apply(pd.Series.value_counts, axis=0)
And I'm getting result as:
Col1 Col2 Col3
0 3 2 1
1 2 3 4
But, I want the result as below (like pivot):
Col_Name 0 1
0 Col1 3 2
1 Col2 2 3
2 Col3 1 4
Please suggest. Thanks in advance.
Add to your solution transpose by T and for new column from index rename_axis with reset_index:
df1 = df.apply(pd.Series.value_counts, axis=0)
df1 = df1.T.rename_axis('Col_Name').reset_index()
print (df1)
Col_Name 0 1
0 Col1 3 2
1 Col2 2 3
2 Col3 1 4
Another solutions:
First reshape columns by stack or melt, use SeriesGroupBy.value_counts and last reshape back by unstack:
df = df.stack().groupby(level=1).value_counts().unstack()
print (df)
0 1
Col1 3 2
Col2 2 3
Col3 1 4
For new column:
df = (df.stack()
.groupby(level=1)
.value_counts()
.unstack()
.rename_axis('Col_Name')
.reset_index())
print (df)
Col_Name 0 1
0 Col1 3 2
1 Col2 2 3
2 Col3 1 4
Another solution:
df = (df.melt(var_name='Col_Name')
.groupby('Col_Name')['value']
.value_counts()
.unstack()
.rename_axis(None, axis=1)
.reset_index()
)
print (df)
Col_Name 0 1
0 Col1 3 2
1 Col2 2 3
2 Col3 1 4
