Reputation: 32
I need to find the number of common values in a column wrt another column.
For example:
There are two columns X
, Y
.
X: | a | b | c | a | d | a | b | b | a | a |
---|---|---|---|---|---|---|---|---|---|---|
Y: | NaN | 2 | 4 | Nan | NaN | 6 | 4 | NaN | 5 | 4 |
So how do I group values like NaN wrt a,b,c,d.
For example,
a
has 2 NaN
values.
b
has 1 NaN
values.
Upvotes: 0
Views: 65
Reputation: 16683
Per my comment, I have transposed your dataframe with df.set_index(0).T
to get the following starting point.
In[1]:
0 X Y
1 a NaN
2 b 2
3 c 4
4 a NaN
5 d NaN
6 a 6
7 b 4
8 b NaN
9 a 5
10 a 4
From there, you can filter for null values with .isnull()
. Then, you can use .groupby('X').size()
to return the count of null values per group:
df[df['Y'].isnull()].groupby('X').size()
X
a 2
b 1
d 1
dtype: int64
Or, you could use value_counts()
to achieve the same thing:
df[df['Y'].isnull()]['X'].value_counts()
Upvotes: 2