Set values in a Pandas dataframe column with the value of another dataframe column where match between other two columns values (one with duplicates)

Question

I'm newbe in Pandas and i'm stuck in gettin the code for do this:

df1 ('c-code' column is unordered and may contain duplicate values):

c-code	c-text	Column B	Column C
970000	null	'val X'	'val 1'
932201	null	'val Y'	'val 2'
413220	null	'val Z'	'val 3'
932201	null	'val A'	'val 0'
932201	null	'val A'	'val 0'
213201	null	'val A'	'val 0'

df2 ('code' column is ordered an has unique values):

code	text
100101	'Some text 1'
100102	'Some text 2'
100103	'Some text 3'
......	.............
213201	'Some text 11'
......	.............
413220	'Some text 18'
......	.............
932201	'Some text 20'
......	.............
970000	'Some text 31'
......	.............
990090	'Some text 99'

Desired df1:

c-code	c-text	Column B	Column C
970000	'Some text 31'	'val X'	'val 1'
932201	'Some text 20'	'val Y'	'val 2'
413220	'Some text 18'	'val Z'	'val 3'
932201	'Some text 20'	'val A'	'val 0'
932201	'Some text 20'	'val A'	'val 0'
213201	'Some text 11'	'val A'	'val 0'

I have tried .loc like:

df1.loc[df1['c-code'] == df2['code'], 'c-text'] = df_cpv['text']

with .merge too, but I haven't achieved anything good.

Many thanks in advance!!

`df1['c-text'] = df1['c-code'].map(df2.set_index('code')['c-text'])` — mozway, Jul 05 '23 at 14:25
Thank you very much @mozway your solution runs like a charm! Only have to modify last c-text' with 'text' like: `df1['c-text'] = df1['c-code'].map(df2.set_index('code')['text'])` — DavidRit, Jul 07 '23 at 10:44

score 0 · Accepted Answer · answered Jul 05 '23 at 14:14

Merge is the right way to go! After merging there will be extra columns left, so additionally you should do some renaming and dropping.

df3 = df1.merge(df2, how="left", left_on="c-code", right_on="code")
df3["c-text"] = df3["text"]
df3 = df3.drop(["text", "code"], axis=1)
print(df3)

Output:

   c-code          c-text Column B Column C
0  970000  'Some text 31'  'val X'  'val 1'
1  932201  'Some text 20'  'val Y'  'val 2'
2  413220  'Some text 18'  'val Z'  'val 3'
3  932201  'Some text 20'  'val A'  'val 0'
4  932201  'Some text 20'  'val A'  'val 0'
5  213201  'Some text 11'  'val A'  'val 0'

Many thanks Maria!! Your solution gave me knowledge in another poblem I had too. — DavidRit, Jul 07 '23 at 10:52

Set values in a Pandas dataframe column with the value of another dataframe column where match between other two columns values (one with duplicates)

1 Answers1