Add a column to a dataframe by looking up values in another dataframe

Question

Consider these two dataframes:

index = [0, 1, 2, 3]
columns = ['col0', 'col1']
data = [['A', 'D'],
        ['B', 'E'],
        ['C', 'F'],
        ['A', 'D']
       ]
df1 = pd.DataFrame(data, index, columns)

df2 = pd.DataFrame(data = [10, 20, 30, 40], index = pd.MultiIndex.from_tuples([('A', 'D'), ('B', 'E'), ('C', 'F'), ('X', 'Z')]), columns = ['col2'])

I want to add a column to df1 that tells me the value from looking at df2. The expected result would be like this:

index = [0, 1, 2, 3]
columns = ['col0', 'col1', 'col2']
data = [['A', 'D', 10],
        ['B', 'E', 20],
        ['C', 'F', 30],
        ['A', 'D', 10]
       ]
df3 = pd.DataFrame(data, index, columns)

What is the best way to achieve this? I am wondering if it should be done with a dictionary and then map or perhaps something simpler. I'm unsure.

Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — G. Anderson, Feb 18 '22 at 17:54
The answer I have accepted is a specific application of one of the functions that is covered in that article, yes. — ric, Feb 21 '22 at 10:59

ddejohn · Accepted Answer · 2022-02-18T18:49:36.747

2

Merge normally:

pd.merge(df1, df2, left_on=["col0", "col1"], right_index=True, how="left")

Output:

  col0 col1  col2
0    A    D    10
1    B    E    20
2    C    F    30
3    A    D    10

edited Feb 18 '22 at 18:49

answered Feb 18 '22 at 18:44

ddejohn

8,775
3
17
30

score 1 · Answer 2 · answered Feb 18 '22 at 18:43

1

try this:

indexes = list(map(tuple, df1.values))
df1["col2"] = df2.loc[indexes].values

Output:

#print(df1)
  col0 col1 col2
0    A    D   10
1    B    E   20
2    C    F   30
3    A    D   10

answered Feb 18 '22 at 18:43

woblob

1,349
9
13

Add a column to a dataframe by looking up values in another dataframe

2 Answers2