Change values in one dataframe if values appear in another dataframe based on one column value

Question

I have two dataframe, "big" and "correction":

big:

>>>ID    class    fruit
0  1     medium   banana
1  2     medium   banana
2  3     high     peach
3  4     low      nuts
4  5     low      banana

and correction:

>>> ID  class  time  fruit
0   2  medium   17   melon
1   5  high     19   oranges

I want to fix the table "big" according to information in table correction. in order to get the following table:

>>>ID    class    fruit
0  1     medium   banana
1  2     medium   **melon**
2  3     high     peach
3  4     low      nuts
4  5     **high** **oranges**

as you can see, the starred values "fixed" according to the correction tabl, on the ID field.

I thought to use for nested loop but I believe there are better ways to get the same results.

anky · Accepted Answer · 2020-09-13 15:08:27Z

3

Try df.update after aligning the indexes of both dataframes:

big.set_index("ID",inplace=True)
big.update(correction.set_index("ID")

big = big.reset_index() #ifyou want `ID` as a column back
print(big)

   ID   class    fruit
0   1  medium   banana
1   2  medium    melon
2   3    high    peach
3   4     low     nuts
4   5    high  oranges

answered Sep 13, 2020 at 15:08

anky

75.3k11 gold badges46 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Reut Over a year ago

@is possible to determine which column I want to change? (for example to write in the code that I want to change only the class and fruit in df big, from the column class and fruit from correction (to be sure it doesn't change other things)

anky Over a year ago

@Reut yes , you can take a subset of the correction dataframe : big.update(correction.set_index("ID")[['class','fruit']])

Scott Boston · Accepted Answer · 2020-09-13 15:05:06Z

1

Let's try:

corr_df.set_index('ID').combine_first(big_df.set_index('ID'))

answered Sep 13, 2020 at 15:05

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

2 Comments

Joe Ferndz Over a year ago

but there are no null values. Does this work for duplicates?

Scott Boston Over a year ago

Duplicate ID, no. This will not work. You will need to either use merge or update and create some sort of helper column to handle duplicate ID if they have different data for each subsequent occurance of that ID. In this case, we are using ID as a unique identifier for each record in both dataframes.

Collectives™ on Stack Overflow

Change values in one dataframe if values appear in another dataframe based on one column value

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related