how can i assign columns values based on common list elements between two dataframes?

I find it difficult to work with lists in dataframes so I first explode the lists, then merge the dataframes and put the lists back together.

#This just helps me keep the columns straight
df2.columns = ['Industry', 'Terms']

# Drop Industry in df1 and explode the lists in df1 & df2
df1 = df1.drop('Industry', axis=1).explode('Terms')
df2 = df2.explode('Terms')

# Merge the terms back into df1
df1 = df1.merge(df2, how='left', on='Terms')

# Bring the lists back together
df1 = df1.groupby('Record Name').agg(lambda x: x.dropna().unique().tolist())

The result is:

                                                         Terms     Industry
Record Name                                                                
a                      [Elementary, Pre Schools, High Schools]  [Education]
b                                                    [Museums]       [Arts]
c            [Junior High Schools, Military - Misc., Roads,...  [Education]

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top