CODEWITHSUNDEEP
python-3.xpandas
Binayak Chatterjee
Binayak Chatterjee

Reputation: 326

Get value from another dataframe column based on condition

I have a dataframe like below:

>>> df1
           a   b
0  [1, 2, 3]  10
1  [4, 5, 6]  20
2     [7, 8]  30

and another like:

>>> df2
   a
0  1
1  2
2  3
3  4
4  5

I need to create column 'c' in df2 from column 'b' of df1 if column 'a' value of df2 is in coulmn 'a' df1. In df1 each tuple of column 'a' is a list.

I have tried to implement from following url, but got nothing so far: https://medium.com/@Imaadmkhan1/using-pandas-to-create-a-conditional-column-by-selecting-multiple-columns-in-two-different-b50886fabb7d

expect result is

>>> df2
   a  c
0  1 10
1  2 10
2  3 10
3  4 20
4  5 20

Upvotes: 3

Views: 5691

Answers (3)

jezrael
jezrael

Reputation: 863531

Use Series.map by flattening values from df1 to dictionary:

d = {c: b for a, b in zip(df1['a'], df1['b']) for c in a}
print (d)
{1: 10, 2: 10, 3: 10, 4: 20, 5: 20, 6: 20, 7: 30, 8: 30}

df2['new'] = df2['a'].map(d)
print (df2)
   a  new
0  1   10
1  2   10
2  3   10
3  4   20
4  5   20

EDIT: I think problem is mixed integers in list in column a, solution is use if/else for test it for new dictionary:

d = {}
for a, b in zip(df1['a'], df1['b']):
    if isinstance(a, list):
        for c in a:
            d[c] = b
    else:
        d[a] = b

df2['new'] = df2['a'].map(d)

Upvotes: 5

Erfan
Erfan

Reputation: 42946

First we unnest the list df1 to rows, then we merge them on column a:

df1 = df1.set_index('b').a.apply(pd.Series).stack().reset_index(level=0).rename(columns={0:'a'})
print(df1, '\n')

df_final = df2.merge(df1, on='a')
print(df_final)

    b    a
0  10  1.0
1  10  2.0
2  10  3.0
0  20  4.0
1  20  5.0
2  20  6.0
0  30  7.0
1  30  8.0 

   a   b
0  1  10
1  2  10
2  3  10
3  4  20
4  5  20

Upvotes: 2

anky
anky

Reputation: 75140

Use :

m=pd.DataFrame({'a':np.concatenate(df.a.values),'b':df.b.repeat(df.a.str.len())})
df2.merge(m,on='a')

   a   b
0  1  10
1  2  10
2  3  10
3  4  20
4  5  20

Upvotes: 4

Related Questions