Update subset of values from one column based on another dataframe

Question

I have a dataframe df1 :-

Store_id	fruit	region
1	orange	x
2	apple	y
3	NotKnown	z
5	Notknown	q
6	banana	w

I have a dataframe df2 :-

Store_id	fruit	region
1	orange	x
2	apple	y
3	pears	z
5	strawberry	q
6	banana	w
8	mango	i

Expected df1 :-

Store_id	fruit	region
1	orange	x
2	apple	y
3	pears	z
5	strawberry	q
6	banana	w

Store_id is the primary key. How do I update column fruit of df1 based on df2 column fruit for value NotKnown under fruit column of df1

the question is unclear, what if you have a different fruit for the same id? several? a non-matching region? — mozway, Sep 05 '22 at 12:43
Everything. You provide no logic at all, this makes any answer dependent on the interpretation of the answerer… — mozway, Sep 05 '22 at 12:49

score 1 · Answer 1 · answered Sep 05 '22 at 12:46

Let's try

df1['fruit'] = (df1['fruit'].str.lower().replace('notknown', pd.NA)
                .fillna(df1.merge(df2, on=['Store_id', 'region'], how='left')['fruit_y']))

print(df1)

   Store_id       fruit region
0         1      orange      x
1         2       apple      y
2         3       pears      z
3         5  strawberry      q
4         6      banana      w

score 0 · Accepted Answer · answered Sep 05 '22 at 12:41

0

reg_to_fru = df2.set_index("region")["fruit"]
df1.fruit  = df1.region.map(reg_to_fru)

you can form a mapper (a Series) from df2 as region -> fruit, then map the region in df1 with it:

In [39]: reg_to_fru = df2.set_index("region")["fruit"]

In [40]: reg_to_fru 
Out[40]:
region
x        orange
y         apple
z         pears
q    strawberry
w        banana
i         mango
Name: fruit, dtype: object

In [41]: df1.fruit = df1.region.map(reg_to_fru)

In [42]: df1
Out[42]:
   Store_id       fruit region
0         1      orange      x
1         2       apple      y
2         3       pears      z
3         5  strawberry      q
4         6      banana      w

answered Sep 05 '22 at 12:41

Mustafa Aydın

17,645
4
15
38

Where are we replaving `NotKnown` under `fruit` column in this approach? – Scope Sep 05 '22 at 12:48
When `.map`ping. I assumed other values are by definition are the same in the other dataframe under region -> fruit mapping, so they get mapped, too. Like region "z" is looked up in `df2` and fruit is "pears", so that's put in `df1`. But also, region "x" is looked up as well, and its fruit "orange" is put as well (which was orange anyway). – Mustafa Aydın Sep 05 '22 at 12:52

Update subset of values from one column based on another dataframe

2 Answers2