How to update rows in pandas dataframe from another dataframe on condition with diffrenet indexes?-CodePudding

I have two sample datasets

test1

        ID      Label   Key
0   K1a89aKkkkkk    23  23_TAMPA
1   Ka18d8Kkkkkk    2   2_MIAMI
2   Kae851Kkkkkk    10  10_WEST PALM BEACH
3   Kf054cKkkkkk    27  27_JACKSONVILLE
4   Ka1129Kkkkkk    2   2_MIAMI
5   Kae8e1Kkkkkk    10  10_WEST PALM BEACH
6   Ka9045Kkkkkk    50  50_ORLANDO
7   K1a95eKkkkkk    51  51_SAINT PETERSBURG
8   Kae931Kkkkkk    19  19_FORT LAUDERDALE
9   Ka1382Kkkkkk    19  19_FORT LAUDERDALE

test2

            ID          Label   Key
9791    Ke53a3Kkkkkk    NaN NaN
9792    Ke1c8dKkkkkk    NaN NaN
9793    Kf69acKkkkkk    NaN NaN
9794    Ke2821Kkkkkk    NaN NaN
9795    Ke0a14Kkkkkk    NaN NaN
9796    Kf6a4cKkkkkk    NaN NaN
9797    Ka83acKkkkkk    NaN NaN
9798    Kf4698Kkkkkk    NaN NaN
9799    Ke1981Kkkkkk    NaN NaN
9800    Ka9a40Kkkkkk    NaN NaN

I'm trying to update Label and Key in test2 based on conditions from test1

Ex:

if ID == Ke2821Kkkkkk in test2 Label and Key should be updated with 2, 2_MIAMI

test2.loc[test2["ID"]=="Ke2821Kkkkkk"][["Label", "Key"]] = test1.loc[test1["ID"]=="Ka1129Kkkkkk"][["Label", "Key"]]
test2.loc[test2["ID"]=="Ka83acKkkkkk"][["Label", "Key"]] = test1.loc[test1["ID"]=="Ka9045Kkkkkk"][["Label", "Key"]]
test2.loc[test2["ID"]=="Ka9a40Kkkkkk"][["Label", "Key"]] = test1.loc[test1["ID"]=="Ka1129Kkkkkk"][["Label", "Key"]]

As it has a different index it's not updating.

How do I update rows in a dataframe from another dataframe on conditions with different indexes?

Desired Output

            ID          Label   Key
9791    Ke53a3Kkkkkk    NaN NaN
9792    Ke1c8dKkkkkk    NaN NaN
9793    Kf69acKkkkkk    NaN NaN
9794    Ke2821Kkkkkk    2   2_MIAMI
9795    Ke0a14Kkkkkk    NaN NaN
9796    Kf6a4cKkkkkk    NaN NaN
9797    Ka83acKkkkkk    50  50_ORLANDO
9798    Kf4698Kkkkkk    NaN NaN
9799    Ke1981Kkkkkk    NaN NaN
9800    Ka9a40Kkkkkk    2   2_MIAMI

CodePudding user response：

Convert values to numpy arrays, for improve solution remove nested ][:

test2.loc[test2["ID"]=="Ke2821Kkkkkk",["Label", "Key"]] = test1.loc[test1["ID"]=="Ka1129Kkkkkk",["Label", "Key"]].to_numpy()
test2.loc[test2["ID"]=="Ka83acKkkkkk",["Label", "Key"]] = test1.loc[test1["ID"]=="Ka9045Kkkkkk",["Label", "Key"]].to_numpy()
test2.loc[test2["ID"]=="Ka9a40Kkkkkk",["Label", "Key"]] = test1.loc[test1["ID"]=="Ka1129Kkkkkk",["Label", "Key"]].to_numpy()
print (test2)
                ID  Label         Key
9791  Ke53a3Kkkkkk    NaN         NaN
9792  Ke1c8dKkkkkk    NaN         NaN
9793  Kf69acKkkkkk    NaN         NaN
9794  Ke2821Kkkkkk    2.0     2_MIAMI
9795  Ke0a14Kkkkkk    NaN         NaN
9796  Kf6a4cKkkkkk    NaN         NaN
9797  Ka83acKkkkkk   50.0  50_ORLANDO
9798  Kf4698Kkkkkk    NaN         NaN
9799  Ke1981Kkkkkk    NaN         NaN
9800  Ka9a40Kkkkkk    2.0     2_MIAMI