Koji Kawamura Koji Kawamura - 6 months ago 7
Python Question

How to get and show counts of values each column

I have a pandas dataframe.

df:

col1 col2 col3 col4 col5
0 1.0 1.0 NaN NaN 1.0
1 NaN 1.0 1.0 2.0 1.0
2 2.0 NaN 1.0 NaN 1.0


I want to get the count number of rows that have the same data each column like following.

OutPut:

col1 col2 col3 col4 col5
1.0 1 2 2 0 3
2.0 1 0 0 1 0


or only the count of a value.

col1 col2 col3 col4 col5
1.0 1 2 2 0 3


Are there any ways to get my expected output?

Answer

You could use value_counts method of pandas Series and then fillna for filling NaN values with 0:

In [7]: df
Out[7]:
   col1  col2  col3  col4  col5
0   1.0   1.0   NaN   NaN   1.0
1   NaN   1.0   1.0   2.0   1.0
2   2.0   NaN   1.0   NaN   1.0

In [8]: df.apply(lambda x: x.value_counts()).fillna(0)
Out[8]:
     col1  col2  col3  col4  col5
1.0     1   2.0   2.0   0.0   3.0
2.0     1   0.0   0.0   1.0   0.0

If you need int values instead of float you could also use astype with int:

In [9]: df.apply(lambda x: x.value_counts()).fillna(0).astype(int)
Out[9]:
     col1  col2  col3  col4  col5
1.0     1     2     2     0     3
2.0     1     0     0     1     0