Bob
Bob

Reputation: 32

How to access common values from two or more columns?

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

Answers (1)

David Erickson
David Erickson

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

Related Questions