python: How to delete all items in a group if one matches a condition (string comparison)?

user_14892855 0
Viewed 147

Disclaimer: I tried to do this in SQL and none of the answers/my attempts worked so I have been trying to use python as it seems to be better suited

I am hoping to create a function that can delete all items in a group if any one of them matches a specific condition.

Specifically, I have a dataset 'family' and I want to delete all members of the family if that family contains twins.

A portion of the dataset looks like this:

Subject ID Mother_ID Zygosity_SR
1001 2001 MZ
1002 2001 MZ
1003 2001 NotTwin
1004 2002 NotTwin
1005 2002 NotTwin

In this case I want to delete all rows with individuals with the same Mother_ID as the Subjects with Zygosity_SR = MZ.

My resulting table would look like this:

Subject ID Mother_ID Zygosity_SR
1004 2002 NotTwin
1005 2002 NotTwin

This is the python code I have:

import pandas as pd

family = pd.read_excel('HCP database 97 excel vers.xlsx')
family_drop = family.groupby('Mother_ID').filter(lambda x: x['ZygositySR'].str.strip() == 'MZ' )
family_drop.reset_index(drop=True, inplace=True)
family_drop = family_drop[['Subject','Mother_ID']] 
print(family_drop)

I have been getting the error:

TypeError: filter function returned a Series, but expected a scalar bool

Any tips at all on how to fix this would be greatly appreciated. Thank you so much!

pandas-groupby pandas conditional-statements python
3 Answers

if you need to remove the MZ and their mothers' rows:

tdf = df[df['Zygosity_SR'] == 'MZ']   # rows of MZ
tset = set(tdf['Mother_ID'])          # set of MZ's Mother_ID
fdf = df[~df['Mother_ID'].isin(tset)] # rows with NotTwin Mother_ID

This condition ~df['Mother_ID'].isin(tset) means filter out Mother_ID in the set.

print(fdf)
   Subject ID  Mother_ID Zygosity_SR
3        1004       2002     NotTwin
4        1005       2002     NotTwin
user_76152600

DataFrameGroupBy.filter() wants a single boolean to determine if a given group should be returned.

In this case, it looks like you're trying to get groups where "all Zygosity_SR values are not MZ":

family.groupby('Mother_ID').filter(
    lambda group: all(group.Zygosity_SR.str.strip() != 'MZ'))

#    Subject ID  Mother_ID Zygosity_SR
# 3        1004       2002     NotTwin
# 4        1005       2002     NotTwin
user_131383640

It seems like this SQL statement will do what you're asking (delete all subjects whose mother has had twins).

delete 
from table 
where mother_id in (
    select distinct mother_id 
    from table 
    where Zygosity_SR = MZ
)
user_38170680
Related