CODEWITHSUNDEEP
pythonpandasdataframe
bigmacattack
bigmacattack

Reputation: 35

For loop that assigns value to new column in one dataframe based on index in another dataframe

I am dealing with two dataframes.

df1:

   name:  col1:  col2:  col3:
0    a      10     155     0
1    b      12     195     1
2    c      100    100     1

df2:

   name:  col1:  col2:  col3:
0    b      10     124     0
1    a      12     150     0
2    c      100    100     1
3    a      100    100     1
4    b      100    100     1
5    a      100    100     1

I am trying to create a for loop that adds a new column to both dataframes based on 'col3'. 'col3' references the index in the opposite dataframe and should return 'col2' as a new column in the opposite dataframe.

Example output:

df1:

   name:  col1:  col2:  col3:  new_col:
0    a      10     155     0     124
1    b      12     195     1     150
2    c      100    100     1     150

df2:

   name:  col1:  col2:  col3:  new_col:
0    b      10     124     0     155
1    a      12     150     0     155
2    c      100    100     1     195
3    a      100    100     1     195
4    b      100    100     1     195
5    a      100    100     1     195

This is what I am trying so far:

for index1, row1 in df1.iterrows():
    df1['new_col'] = df2.iloc[row1['col3']]
    
for index2, row2 in df2.iterrows():
    df2['new_col'] = df1.iloc[row2['col3']]

Can anyone let me know what is going wrong. Thanks!

Upvotes: 1

Views: 418

Answers (1)

Andrej Kesely
Andrej Kesely

Reputation: 195438

Try:

df1["new_col"] = df2.loc[df1["col3"], "col2"].values
df2["new_col"] = df1.loc[df2["col3"], "col2"].values

print(df1)
print(df2)

Prints:

  name  col1  col2  col3  new_col
0    a    10   155     0      124
1    b    12   195     1      150
2    c   100   100     1      150

  name  col1  col2  col3  new_col
0    b    10   124     0      155
1    a    12   150     0      155
2    c   100   100     1      195
3    a   100   100     1      195
4    b   100   100     1      195
5    a   100   100     1      195

Upvotes: 2

Related Questions