I have a created a dataframe consisting of two columns. I want to count the number of occurences over these two columns.
The dataframe looks like-
No Name
1 A
1 A
5 T
9 V
Nan M
5 T
1 A
And I want to use value_counts() to get a dataframe like this-
No Name Count
1 A 3
5 T 2
9 V 1
Nan M 1
I tried doing df[["No", "Name"]].value_counts() which counts everything except the nan row. Is there a way to use value_counts() to count Nan as well?
2 Answers
You can use groupby with dropna=False:
df.groupby(['No', 'Name'], dropna=False, as_index=False).size()
Output:
No Name size
0 1.0 A 3
1 5.0 T 2
2 9.0 V 1
3 NaN M 1
P.S. Interestingly enough, pd.Series.value_counts method also supports dropna argument, but pd.DataFrame.value_counts method does not
You can still use value_counts() but with dropna=False rather than True (the default value), as follows:
df[["No", "Name"]].value_counts(dropna=False)
So, the result will be as follows:
No Name size
0 1 A 3
1 5 T 2
2 9 V 1
3 NaN M 1
