CODEWITHSUNDEEP
pythonpandasdataframe
Prashant Rai
Prashant Rai

Reputation: 135

taking a count of numbers occurring in a column in a dataframe using pandas

I have a dataframe like the one given below. There is one column on top. There is a second column with element name given below it. I am trying to take a count of all the numbers under each element and trying to transpose the data so that the ranking will become the column header after transposing and the count will be the data underneath each rank. tried multiple methods using pandas like

df.eq('1').sum(axis=1) df2=df.transpose

but not getting the desired output.

how would you rank these items on a scale of 1-5

X Y Z

1 2 1
2 1 3
3 1 1
1 3 2
1 1 2
2 5 3
4 1 2
1 4 4
3 3 5

desired output is something like

   1  2  3  4  5

X (count of 1s)(count of 2s).....so on

Y (count of 1s)(count of 2s).......

Z (count of 1s)(count of 2s)............

any help would really mean a lot.

Upvotes: 1

Views: 1736

Answers (2)

piRSquared
piRSquared

Reputation: 294258

Option 0
pd.concat 

pd.concat({c: s.value_counts() for c, s in df.iteritems()}).unstack(fill_value=0)

Option 1
stack preserves int dtype

df.stack().groupby(level=1).apply(
    pd.value_counts
).unstack(fill_value=0)

Upvotes: 2

akuiper
akuiper

Reputation: 214957

You can apply the pd.value_counts to all columns, which will count values from all the columns and then transpose the result:

df.apply(pd.value_counts).fillna(0).T

#     1   2   3   4   5
#X  4.0 2.0 2.0 1.0 0.0
#Y  4.0 1.0 2.0 1.0 1.0
#Z  2.0 3.0 2.0 1.0 1.0

Upvotes: 3

Related Questions