CODEWITHSUNDEEP
pythonpandasdataframe
Johan
Johan

Reputation: 43

Can you join dataframes with multiple keys in one of the joining columns?

I would like to join the following two dataframes.

The first dataframe has multiple keys in one column

>>> import pandas as pd
>>> df = pd.DataFrame(data={'col1': [1,2,3], 'key': ['x, y','y', 'z, x']})
>>> df
   col1   key
0     1  x, y
1     2     y
2     3  z, x

For each of the key in the first dataframe i have a mapping of sorts in a second dataframe. Like this:

>>> df2 = pd.DataFrame(data= {'key': ['x','y','z'], 'value': ["v1,v2, 
v3","v4,v3", "v5"]})

>>> df2
  key      value
0   x  v1,v2, v3
1   y      v4,v3
2   z         v5

I would like to end up with all values next to their corresponding keys in one column. Ideally with duplicates removed as in col1 (x and y both have v3).

>>> df3
   col1   key           value
0     1  x, y  v1, v2, v3, v4
1     2     y          v4, v3
2     3  z, x  v1, v2, v3, v5

Upvotes: 4

Views: 124

Answers (3)

Erfan
Erfan

Reputation: 42916

First we unnest (split) your values to rows:

df_new= pd.concat([pd.Series(row['col1'], row['key'].split(','))              
                    for _, row in df.iterrows()]).reset_index().rename({0:'col1', 'index':'key'},axis=1)

print(df_new)
  key  col1
0   x     1
1   y     1
2   y     2
3   z     3
4   x     3

Then we merge the values together on the key column and groupby to aggregate on col1:

df_final = pd.merge(df_new,df2, on='key',how='left')
df_final = df_final.groupby('col1').agg(', '.join).reset_index()

print(df_final)

   col1   key             value
0     1  x, y  v1,v2, v3, v4,v3
1     2     y             v4,v3
2     3  z, x     v5, v1,v2, v3

Upvotes: 1

BENY
BENY

Reputation: 323306

Check with 

d=dict(zip(df2.key,df2.value))
df['New']=[','.join([d.get(y) for y in x.split(', ')]) for x in df.key]

and now we remove the duplicate 

df.New=df.New.str.split(',').apply(lambda x : ','.join(set(x)))
df
   col1   key          New
0     1  x, y  v3,v1,v2,v4
1     2     y        v3,v4
2     3  z, x  v5,v3,v1,v2

Upvotes: 4

rafaelc
rafaelc

Reputation: 59274

Simple for loop

for k,v in zip(df2.key, df2.value): 
    df.key = df.key.str.replace(k,v)

Outputs

    col1    key
0   1       v1,v2, v3, v4,v3
1   2       v4,v3
2   3       v5, v1,v2, v3

To remove the duplicates, can transform

df.key.transform(lambda s: sorted(set([k.strip() for k in s.split(',')])))

    col1    key
0   1       [v1, v2, v3, v4]
1   2       [v3, v4]
2   3       [v1, v2, v3, v5]

Upvotes: 3

Related Questions