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.

Answer 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