Create column from another dataframe, using existing columns

Question

I have a pandas DataFrame with columns "Quantity" and "Unit", e.g.:

    Quantity    Unit
0   Current     A
1   Current     mA
2   Voltage     V
3   Length      m
4   Length      km

and I have a pandas DataFrame with ratios of units, e.g.:

      Current  Voltage  Length           
A       1        NaN      NaN
mA     1000      NaN      NaN
V      NaN       1        NaN
m      NaN       NaN      1000
km     NaN       NaN       1 

Is it possible to create in first dataframe column with values of ratios?:

    Quantity    Unit   Ratio
0   Current     A        1
1   Current     mA      1000
2   Voltage     V        1
3   Length      m        1
4   Length      km      1000

I have tried to get every value by iterrows, but I have really big dataframe and it's not working correctly in case of time. Maybe there is another, more expedient way to solve my issue, but I have to work with dataframes of ratios.

Answer 1

Pandas' merge() efficiently works as mapping data from one df to another

# stack the second df and merge it with the first on Unit and Quantity
df1.merge(df2.stack().rename('Ratio'), left_on=['Unit', 'Quantity'], right_index=True)