Pivot by count of values in multiple columns pandas

user_14335237 0
Viewed 213

I have a dataframe where multiple columns have a 0, 1, or 5 as the values. I want to be able to pivot the table so that the columns are 0, 1 and 5, the rows are the original column names and the values are the count of each time a 0, 1, or 5 appeared in the original column.

the original table:

col1 col2 col3 col4
0    1    0    5
1    1    0    1
1    0    1    1

Desired Table:

      0   1   5
col1  1   2   0
col2  1   2   0
col3  2   1   0
col4  0   2   1

What is the best way to do this in Python?

pandas pivot-table count python
2 Answers

You can try Series.value_counts:

df.apply(pd.Series.value_counts).fillna(0).astype(int).T

      0  1  5
col1  1  2  0
col2  1  2  0
col3  2  1  0
col4  0  2  1
user_128331660

An approach is using df.melt , then crosstab:

u = df.melt()
out = pd.crosstab(u['variable'],u['value']).rename_axis(index=None,columns=None)

print(out)

      0  1  5
col1  1  2  0
col2  1  2  0
col3  2  1  0
col4  0  2  1
user_98406370
Related