Pandas df how to parse column value to extract string to int with regex

0

Issue

I have a Pandas df with 2 columns:

    name       Count_Relationship
0   allicin    DOWNREGULATE: 1
1   allicin    DOWNREGULATE: 2
2   allicin    UPREGULATE: 1 | DOWNREGULATE: 1
3   aspirin    UPREGULATE: 5 | DOWNREGULATE: 1
4   albuterol  DOWNREGULATE: 1
5   albuterol  UPREGULATE: 3

I would like to filter out only the rows where if I group by the ‘names’ and count in the ‘Count_Relationship’ column the amount of DOWNREGULATE to be more than the amount of the UPREGULATE. In this case allicin would have DOWREGULATE 1+2+1=4 and UPREGULATE =1 so num_downregulate>num_upregulate, while in the rest(aspirin,albuterol) that’s not the case.
I would like to return this filtered df:

    name      Count_Relationship
0   allicin   DOWNREGULATE: 1
1   allicin   DOWNREGULATE: 2
2   allicin   UPREGULATE: 1 | DOWNREGULATE: 1

The column Count_Relationship is a string, so I would have to parse the number part of the string and convert it to int.

I tried this:

    import pandas as pd

    data = {'name': ['allicin', 'allicin', 'allicin', 'aspirin', 'albuterol', 'albuterol'],
    'Count_Relationship': ['DOWNREGULATE: 1', 'DOWNREGULATE: 2', 'UPREGULATE: 1 | DOWNREGULATE: 1', 'UPREGULATE: 5 | DOWNREGULATE: 1', 'DOWNREGULATE: 1' , 'UPREGULATE: 3']
    }

    df = pd.DataFrame(data)

    substances = df["name"].tolist()
    substances = list(set(substances)) # to get the unique names

    result_substances = []
    
    for substance in (substances):
        try:
            numberOfdownregulate = df[(df["name"] == substance) & (\
            (df["Count_Relationship"].str.match(pat = '("DOWNREGULATE:"([0-9]))')).values[0].astype(int)        
        except:
            pass
        try:    
            numberOfupregulate = df[(df["name"] == substance) & (\
            (df["Count_Relationship"].str.match(pat = '("UPREGULATE:"([0-9]))')).values[0].astype(int)
        except:
            pass
    
        result = numberOfdownregulate - numberOfupregulate
        
        if result > 0:
            result_substances.append(substance)


    df_filtered = df[df["name"].isin(result_substances)]

but I get a syntax error at the line numberOfdownregulate where my regex is.
How can fix the algorithm? thanks so much

Solution

You could extract the infos, compare the up and down, and build a mask to select the data:

drugs = (df.join(df['Count_Relationship'].str.extractall('(?P<down>(?<=DOWNREGULATE: )\d+)|(?P<up>(?<=UPREGULATE: )\d+)')
                   .groupby(level=0).first().fillna(0).astype(int)
                 )
           .groupby('name').agg({'down': 'sum', 'up': 'sum'})
           .query('down >= up')
           .index
        )

df[df['name'].isin(drugs)]

output:

      name               Count_Relationship
0  allicin                  DOWNREGULATE: 1
1  allicin                  DOWNREGULATE: 2
2  allicin  UPREGULATE: 1 | DOWNREGULATE: 1

Answered By – mozway

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More