Filtering and grouping rows in one DataFrame, by another DataFrame

user_8541840 0
Viewed 110

I have two DFs. I want to iterate through rows in DF1 and filter all the rows in DF2 with same id and get column"B" value in new columns of DF1.

data = {'id': [1,2,3]}
df1 = pd.DataFrame(data)

data = {'id': [1, 1, 3,3,3], 'B': ['ab', 'bc','ad','ds','sd']}
df2 = pd.DataFrame(data)

DF1 - id (15k rows) DF2 - id, col1 (50M rows)

Desired output

data = {'id': [1,2,3],'B':['[ab,bc]','[]','[ad,ds,sd]']}
pd.DataFrame(data)

def func(df1):
    temp3=df2.merge(pd.DataFrame(data=[df1.values]*len(df1),columns=df1.index),how='right',on= 
    ['id'])
    temp1 = temp3.B.values
    return temp1
df1['B']=df1.apply(func,axis=1))

I am using merge for filtering and applying lambda function on df1. The code is taking 1 hour to execute on large data frame. How to make this run faster ?

pandas dataframe optimization python
1 Answers

Are you looking for a simple filter and grouped listification?

df2[df2['id'].isin(df1['id'])].groupby('id', as_index=False)[['B']].agg(list)

   id             B
0   1      [ab, bc]
1   2      [ca, as]
2   3  [ad, ds, sd]

Note that grouping as lists is considered suboptimal in terms of performance.

user_49090870
Related