Python pandas complex group by

user_11158950 0
Viewed 93

I have a Pandas data frame (df) with 3 columns: "Drug" , "Affect" , "Journal" Every row is a scientific paper and each paper states what effect the drug has on the disease. Example:

       Drug      Affect    Journal
       aspirin  downregulate  paper1
       aspririn downregulate  paper2
       aspirin  upregulate    paper3
       aspirin  neutral       paper4
       aspirin  downregulate  paper5
       aspririn upregulate    paper6
       iboruprofen  upregulate   paper7
       iboruprofen  upregulate   paper8
       iboruprofen  downregulate paper9
       other_drug     ...            ...

The output should look like this:

Drug
aspirin

I want to make a filter of the df to get only the drugs that in the majority of the papers show to downregulate the disease, so if aspirin for example has 3 papers that tell that it downregulates and 2 papers that say that it upregulates and 1 that says that it has a neutral effect then we assume that the majority of the evidence show that the effect is to downregulate. So I want to have aspirin in my filtered df. Therefore, iboruprofen should not make it to my filtered df.

How do I make the group by? Thanks for your help!

pandas group-by python
3 Answers

Group by drug and the affect to count the number of records.

df_grouped = df.groupby(["Drug","Affect"], as_index=False).count()

The output:

    Drug         Affect        Journal
0   aspirin      downregulate    3
1   aspirin      neutral         1 
2   aspirin      upregulate      2
3   iboruprofen  downregulate    1
4   iboruprofen  upregulate      2

Loop over the unique drug list and check the numbers. If downregulate > upregulate, add this drug to result list.

result_drugs = []
for drug in np.unique(df["Drug"]:
    numberOfdownregulate = df_grouped[(df_grouped["Drug"] == drug) & (df_grouped["Affect"] == "downregulate")].Journal.values[0]
    numberOfupregulate = df_grouped[(df_grouped["Drug"] == drug) & (df_grouped["Affect"] == "upregulate")].Journal.values[0]
    result = numberOfdownregulate - numberOfupregulate
    
    if result > 0:
        result_drugs.append(drug)

Finally, filter your original dataframe.

df[df["Drug"].isin(result_drugs)]

If you only want to have dataframe with the desired drug values.

pd.DataFrame(result_drugs, columns=["Drug"])

Final output:

    Drug
0   aspirin
user_131433790

An alternative option would be using convtools library:

from convtools import conversion as c

# fmt: off
input_data = [
    {'Drug': 'aspirin', 'Affect': 'downregulate', 'Journal': 'paper1'},
    {'Drug': 'aspririn', 'Affect': 'downregulate', 'Journal': 'paper2'},
    {'Drug': 'aspirin', 'Affect': 'upregulate', 'Journal': 'paper3'},
    {'Drug': 'aspirin', 'Affect': 'neutral', 'Journal': 'paper4'},
    {'Drug': 'aspirin', 'Affect': 'downregulate', 'Journal': 'paper5'},
    {'Drug': 'aspririn', 'Affect': 'upregulate', 'Journal': 'paper6'},
    {'Drug': 'iboruprofen', 'Affect': 'upregulate', 'Journal': 'paper7'},
    {'Drug': 'iboruprofen', 'Affect': 'upregulate', 'Journal': 'paper8'},
    {'Drug': 'iboruprofen', 'Affect': 'downregulate', 'Journal': 'paper9'}
]
# fmt: on

converter = (
    c.group_by(c.item("Drug"))
    .aggregate(
        {
            "drug": c.item("Drug"),
            "down": c.ReduceFuncs.Count(
                where=c.item("Affect") == "downregulate"
            ),
            "up": c.ReduceFuncs.Count(where=c.item("Affect") == "upregulate"),
            "neutral": c.ReduceFuncs.Count(
                where=c.item("Affect") == "neutral"
            ),
        }
    )
    .filter(
        c.and_(
            c.item("down") > c.item("up"),
            c.item("down") > c.item("neutral"),
        )
    )
    # after the above the result is:
    #   [{"drug": "aspirin", "down": 2, "up": 1, "neutral": 1}]
    .iter(c.item("drug"))
    .as_type(list)
    .gen_converter()
)

assert converter(input_data) == ["aspirin"]
user_53877380

A simpler approach using filter and value_counts:

(
    df.groupby("Drug")
    .filter(lambda x: x["Affect"].value_counts().nlargest(1).index[0] == "downregulate")
    .get(["Drug"])
    .drop_duplicates()
)

values_counts returns counts for every unique value in Affect column.
nlargest(1) returns single row with the highest count
index returns an array with the name of the most frequent item

user_158625690
Related