Iterate and compare values from two different columns in different dataframes- if values match add a new field/column

Question

Say I have a dataframe (df1) that looks something like this

df1
+----+-------+
| id | name  |
+----+-------+
| 1  | name1 |
+----+-------+
| 2  | name2 |
+----+-------+
| 3  | name3 |
+----+-------+
| 4  | name4 |
+----+-------+

and I've got another dataframe that has a subset of the values under the "name" column

for example it has a column that looks like

df2
+-------+
| name  |
+-------+
| name1 |
+-------+
| name2 |
+-------+
| name4 |
+-------+
| name5 |
+-------+

I am grabbing a list of all the values in the "name" column in df1

TypeNameList = df1['name'].tolist()

and then iterating through the rows of df2 and seeing if the "name" value is in the list (because while the name column here is a subset of the name column in df1, it can also contain other values that are not in df1)

for index, row in df2.iterrows():
    if (row["name"],) in TypeNameList:
        print(row["name"]) # Would like to take the value found in the "id" column in df1 that is mapped to this value and insert it into this dataframe

What I would like to do, if conditions are met, is take the id from df1 that is mapped to the specific value in the "name" and insert it into a new column in df2 that also maps to that value

So the final product would like this

+-------+----+
| name  | id |
+-------+----+
| name1 | 1  |
+-------+----+
| name2 | 2  |
+-------+----+
| name4 | 4  |
+-------+----+
| name5 |    |
+-------+----+

(name5 would not have a value in the id column because name5 is not in df1)

score 1 · Accepted Answer · answered Jul 05 '23 at 03:34

Pandas has a merge function you could use:

df1 = pd.DataFrame({'id': [1, 2, 3, 4], 'name': ['name1', 'name2', 'name3', 'name4']})
df2 = pd.DataFrame({'name': ['name1', 'name2', 'name4', 'name5']})

df2.merge(df1, on=['name'], how='left')

    name    id
0   name1   1.00
1   name2   2.00
2   name4   4.00
3   name5   NaN

Iterate and compare values from two different columns in different dataframes- if values match add a new field/column

1 Answers1