How to perform column based replacement in python dataframe-CodePudding

df1

Var	Val1	Val2
var1	6	7
var2	5	-4
var3	-9	3
var4	3	-2

df2

var	S1	S2	S3	S4
var1	1	0	0	2
var2	0	1	2	0
var3	1	0	1	2
var4	1	0	2	0

Expected outcome

df3

var	S1	S2	S3	S4
var1	6	0	0	7
var2	0	5	-4	0
var3	-9	0	-9	3
var4	3	0	-2	0

Dear Experts please help me to solve the query, where Val1 column values from df1 should be replaced when df2 column values are equal to 1 and Val2 column values should be replaced when df2 column values are equal to 2.

I am completely new to this please explain

CodePudding user response：

you can use np.select():

import numpy as np
loop_cols=list(df2.columns)  #save original columns

df2=df2.merge(df1,how='left',left_on='var',right_on='Var') #merge on 'var'

def replace(col_name):
    return np.select([df2[col_name]==1,df2[col_name]==2],[df2['Val1'],df2['Val2']],default=df2[col_name])
    

for i in loop_cols[1:]: #apply function to needed columns. (S1,S2,S3,S4)
    df2[i]=replace(i)
    
df2=df2[loop_cols]
df2
    var     S1  S2   S3 S4
0   var1    6   0    0  7
1   var2    0   5   -4  0
2   var3    -9  0   -9  3
3   var4    3   0   -2  0

You can find the details of the function I use below.

def replace(col_name):
    condlist = [df2[col_name]==1,df2[col_name]==2] # --- > we have two contitions. İf equal 1 or equal 2

    choicelist = [df2['Val1'],df2['Val2']] # -- > if the conditions are met, what should be replaced with
    default = df2[col_name] # -- > if the conditions are not met keep original value

    return np.select(condlist, choicelist,default)