Using Numpy to filter two dataframes

user_10162059 0
Viewed 62

I have two data frames. They are structured like this:

df a

Letter ID
A 3
B 4

df b

Letter ID Value
A 3 100
B 4 300
B 4 100
B 4 150
A 3 200
A 3 400

I need to take for each combo of Letter and ID in df A values from df B and run an outlier function on then.

Currently I am using over 40,000 rows of A and a list of 4,500,000 of list b

a['Results'] =  a.apply(lambda x: outliers(b[(b['Letter']==x['Letter']) & (b['ID']==x['ID'])]['value'].to_list()),axis=1)

As you can imagine this is taking forever. Is there some mistake im making or something that can improve this code?

pandas numpy python
2 Answers

I'd first aggregate every combination of [Letter, ID] in df_b into a list using .groupby, then merge with df_a and apply your outliers function afterwards. Should be faster:

df_a["results"] = df_a.merge(
    df_b.groupby(["Letter", "ID"])["Value"].agg(list),
    left_on=["Letter", "ID"],
    right_index=True,
    how="left",
)["Value"].apply(outliers)
print(df_a)
user_100359850

You can first try to merge the datasets a and b and then run a group by over letter and ID, aggregate the Value by Outlier function.

pd.merge(a,b,how="inner",on = ['letter','ID']).groupby(['letter','ID']).agg(outlier).reset_index()
user_57693790
Related