0

I am trying to update the values of a specific column in a pandas dataframe from another dataframe. I have checked many posts on StackOverflow, but none worked for me.

Below is an example with the expected output:

df1

idx a1 b1   c1 d1 e1
12  4  x10  1  2  5
13  5  x2   3  5  4
14  6  x4   4  5  6
15  7  x13  7  9  2

df2

idx a2  b2 c2 d2 e2
1   x2  x1 2  2  4
2   x8  x2 5  7  8
3   x6  x4 6  9  5
4   x4  x7 6  8  9

b1 is compared with b2 and in case of matching b1 should be updated with the value of a2. So, df1 should be updated as below. Update: the tricky bit is that once comparing b1 with b2 is that b2 should be updated with the value of a2 not b1

df1

idx a1  b1   c1 d1 e1
12  4   x10  1  2  5
13  5   x8   3  5  4
14  6   x6   4  5  6
15  7   x13  7  9  2
sam_alloy
  • 65
  • 4
  • `df1['b1'] = df1['b1'].map(df2.set_index('b2')['a2']).fillna(df1['b1'])` – mozway Mar 29 '23 at 14:40
  • @mozway, it didn't work and triggered the following error: `InvalidIndexError: Reindexing only valid with uniquely valued Index objects` – sam_alloy Mar 29 '23 at 15:54
  • you didn't provide an index in you example, make sure it's unique, if not make it temporarily a column – mozway Mar 29 '23 at 16:52
  • 1
    Do you need `df1['b1'] = df1['b1'].map(df2.drop_duplicates('b2').set_index('b2')['a2']).fillna(df1['b1'])` ? – jezrael Mar 30 '23 at 08:41

0 Answers0