CODEWITHSUNDEEP
pythonpandasdataframe
Nicolas
Nicolas

Reputation: 399

Update dataframe based on match in another dataframe if condition is met

I have two dataframes and I want to update the first one. df1 contains different Markets (M1, M2, etc.) and some Codes for each of them (numbers or Dummy)

import pandas as pd
labels = ["Market","Code"]
values = [["M1","1234"],["M1","Dummy"],["M1","1234"],["M2","Dummy"],["M1","1234"]]
df = pd.DataFrame.from_records(values,columns=labels)
print(df)
 Market   Code
0     M1   1234
1     M1  Dummy
2     M1   1234
3     M2  Dummy
4     M1   1234

If Code == Dummy, then I want to update the Code in df based on the value in df2 for this specific market. So each Market should receive a different new Code.

labels = ["Market","Code(New)"]
values = [["M1","4567"],["M2","5678"]]
df2 = pd.DataFrame.from_records(values,columns=labels)
print(df2)
 Market Code(New)
0     M1      4567
1     M2      5678

in the end I should get 

labels = ["Market","Code"]
values = [["M1","1234"],["M1","4567"],["M1","1234"],["M2","5678"],["M1","1234"]]
df_clean = pd.DataFrame.from_records(values,columns=labels)
print(df_clean)
  Market  Code
0     M1  1234
1     M1  4567
2     M1  1234
3     M2  5678
4     M1  1234

Upvotes: 2

Views: 5325

Answers (2)

Zero
Zero

Reputation: 76917

Use .merge on Market and .loc for subset of df.Code == 'Dummy' values

In [288]: df.loc[df.Code=='Dummy', 'Code'] = df.merge(df2, on='Market', how='left')['Code(New)']

In [289]: df
Out[289]:
  Market  Code
0     M1  1234
1     M1  4567
2     M1  1234
3     M2  5678
4     M1  1234

Upvotes: 4

BENY
BENY

Reputation: 323226

Base on your example 

pd.concat([df1[df1.Code!='Dummy'],df2],axis=0)

You edited your input after I posted my answer, below is the solution with the updated input . 

df2.columns=["Market","Code"]
df2.index=df[df.Code=='Dummy'].index
pd.concat([df[df.Code!='Dummy'],df2],axis=0).sort_index()


Out[372]: 
  Market  Code
0     M1  1234
1     M1  4567
2     M1  1234
3     M2  5678
4     M1  1234

Upvotes: 2

Related Questions