Pandas: Join if value of df1 column is in list of df2 column

user_2205916 0
Viewed 1204

Suppose we have two Pandas DataFrames as follows:

df1 = pd.DataFrame({'id': ['a', 'b', 'c']})
df1
    id
0   a
1   b
2   c

df2 = pd.DataFrame({'ids': [['b','c'], ['a', 'b'], ['a', 'z']], 
                    'info': ['asdf', 'zxcv', 'sdfg']})
df2
    ids     info
0   [b, c]  asdf
1   [a, b]  zxcv
2   [a, z]  sdfg

How do I join/merge the rows of df1 with df2 where df1.id is in df2.ids?

In other words, how do I achieve the following:

df3
   id   ids     info
0  a    [a, b]  asdf
1  a    [a, z]  sdfg
2  b    [b, c]  asdf
3  b    [a, b]  zxcv
4  c    [b, c]  asdf

And also a version of the above aggregated on id, like so:

df3
   id   ids               info
0  a    [[a, b], [a, z]]  [asdf, sdfg]
2  b    [[a, b], [b, c]]  [asdf, zxcv]
3  c    [[b, c]]          [asdf]

I tried the following:

df1.merge(df2, how = 'left', left_on = 'id', right_on = 'ids')
TypeError: unhashable type: 'list'

df1.id.isin(df2.ids)
TypeError: unhashable type: 'list'
pandas dataframe python
2 Answers

Using stack, merge and groupby.agg:

df = df2.set_index('info').ids.apply(pd.Series)\
        .stack().reset_index(0, name='id').merge(df2)\
        .merge(df1, how='right').sort_values('id')\
        .reset_index(drop=True)

print(df)
   info id     ids
0  zxcv  a  [a, b]
1  sdfg  a  [a, z]
2  asdf  b  [b, c]
3  zxcv  b  [a, b]
4  asdf  c  [b, c]

For aggregation use:

df = df.groupby('id', as_index=False).agg(list)

print(df)
  id          info               ids
0  a  [zxcv, sdfg]  [[a, b], [a, z]]
1  b  [asdf, zxcv]  [[b, c], [a, b]]
2  c        [asdf]          [[b, c]]
user_83208610

Use - 

df2[['id1','id2']] = pd.DataFrame(df2.ids.values.tolist(), index= df2.index)
new_df1 = pd.merge(df1, df2,  how='inner', left_on=['id'], right_on = ['id1'])
new_df2 = pd.merge(df1, df2,  how='inner', left_on=['id'], right_on = ['id2'])
new_df = new_df1.append(new_df2)[['id','ids','info']]

Output

id  ids info
0   a   [a, b]  zxcv
1   a   [a, z]  sdfg
2   b   [b, c]  asdf
0   b   [a, b]  zxcv
1   c   [b, c]  asdf

Aggregation Part

new_df.groupby('id')['ids', 'info'].agg(lambda x: list(x))

Output

ids info
id      
a   [[a, b], [a, z]]    [zxcv, sdfg]
b   [[b, c], [a, b]]    [asdf, zxcv]
c   [[b, c]]    [asdf]
user_40980130
Related