CODEWITHSUNDEEP
pythonpandas
Relax ZeroC
Relax ZeroC

Reputation: 683

How to get another dataframe value according to the value of the column and set to the corresponding field

This is my two dataframe , 

df1 = pd.DataFrame([['@1','A',2],['@2','A',1],['@3','A',4],['@4','B',1],['@5','B',1],['@6','B',3],['@7','B',3],['@8','C',4]],columns=['key1','key2','value'])

  key1 key2  value
0   @1    A      2
1   @2    A      1
2   @3    A      4
3   @4    B      1
4   @5    B      1
5   @6    B      3
6   @7    B      3
7   @8    C      4

df2 = pd.DataFrame([['@5','B',None],['@7','B',None],['@6','B',None],['@3','A',None],['@6','B',None]],columns=['key1','key2','value'])

  key1 key2 value
0   @5    B  None
1   @7    B  None
2   @6    B  None
3   @3    A  None
4   @6    B  None

I know I can use isin to select the df1's rows where key1+key2 is in the df2

df1[(df1['key1']+df1['key2']).isin(df2['key1']+df2['key2'])]

  key1 key2  value
2   @3    A      4
4   @5    B      1
5   @6    B      3
6   @7    B      3

but how can I assign df1's ['value'] to df2 , so that let df2 is 

  key1 key2 value
0   @5    B  1
1   @7    B  3
2   @6    B  3
3   @3    A  4
4   @6    B  3

Upvotes: 1

Views: 118

Answers (2)

BENY
BENY

Reputation: 323396

Or you can using fillna 

df2.set_index(['key1','key2']).value.fillna(df1.set_index(['key1','key2']).value).\
        reset_index()
Out[84]: 
  key1 key2  value
0   @5    B      1
1   @7    B      3
2   @6    B      3
3   @3    A      4
4   @6    B      3

Upvotes: 2

piRSquared
piRSquared

Reputation: 294546

Use pd.DataFrame.merge 

df2[['key1', 'key2']].merge(df1, 'left')

  key1 key2  value
0   @5    B      1
1   @7    B      3
2   @6    B      3
3   @3    A      4
4   @6    B      3

Or with pd.DataFrame.join 

keys = ['key1', 'key2']
df2[keys].join(df1.set_index(keys).value, on=keys)

  key1 key2  value
0   @5    B      1
1   @7    B      3
2   @6    B      3
3   @3    A      4
4   @6    B      3

Supposing you want to update df2 while leaving other columns intact. You can update in place

keys = ['key1', 'key2']
df2.update(df2[keys].join(df1.set_index(keys).value, on=keys).value)

Or create a copy

keys = ['key1', 'key2']
df2.assign(value=df2[keys].join(df1.set_index(keys).value, on=keys).value)

Upvotes: 2

Related Questions