Filter a database groupby output without storing it in a variable

user_10916136 0
Viewed 29

I have a data with Person ID in col1, which can repeat implying another row for same person. col2 says whether that row (instance) is Y or N for some parameter.

Sample:

col1    col2
1       Y
1       Y
1       N
1       Y
1       N
2       Y
2       N
2       N
3       N
3       N
3       N
3       N

There are a million such rows.

I need to get a filtered output of those persons who only has 'N's and their count of 'N'.

So I did something like this:

data.groupby('col1')['col2'].value_counts().unstack(level=1)

This gets me the following:

col1    Y    N
1       3    2
2       1    2
3       NaN  4

But I only need the third row in that.

I tried applying a filter using following but they do not work.

data.groupby('col1')['col2'].value_counts().unstack(level=1)['Y'.isna()]
data.groupby('col1')['col2'].value_counts().unstack(level=1).query("'Y'.isna()")

I understand that I cannot refer to 'Y' column directly without adding the data variable before it in the format data['Y']. But I do not wish to save this groupby result in a new variable.

Any way we can filter the output without saving it to a variable?

For example:

data.groupby('col1')['col2'].value_counts().unstack(level=1)['Y'].isna().sum()

This will give me number of cases. However, I need col1 values too.

pandas dataframe filter python-3.x python
1 Answers

Remove the quotes around Y:

>>> data.groupby('col1')['col2'].value_counts().unstack(level=1).query("Y.isna()")
col2    N   Y
col1         
3     4.0 NaN
>>>

Or with True:

>>> x = data.groupby('col1')['col2'].value_counts().unstack(level=1)
>>> x[x[True].isna()]
col2  False  True 
col1              
3       4.0    NaN
>>>
user_87083640
Related