Python code to return element value in dataframe based on another dataframe

Question

I have a dataset similar to this generated from a file with yearly data

d1 = pd.DataFrame({'category': ['A', 'B', 'C', 'D', 'E', 'F'], 
                    'col': ['20%', '40%', '80%', '40%', '60%', '20%']})

Also, a dataset similar to this is generated using another dataset which is monthly

d2 = pd.DataFrame({'category': ['A', 'B', 'C', 'D', 'E', 'F'], 
                    '20%': ['2.1', '4.1', '6.8', '5.9', '3.4', '5.4'], 
                    '40%': ['1.9', '3.7', '6.1', '4.8', '4.1', '7.2'], 
                    '60%': ['3.1', '4.9', '6.5', '7.1', '7.9', '5.1'], 
                    '80%': ['2.5', '4.5', '5.6', '6.9', '8.4', '7.4']})

I wish to get an output like this, where it returns the value of element from d2 based on the two columns in d1

op =  pd.DataFrame({'category': ['A', 'B', 'C', 'D', 'E', 'F'],
                    'col': ['20%', '40%', '80%', '40%', '60%', '20%'],
                    'min_value': ['2.1', '3.7', '5.6', '4.8', '7.9', '5.4']})

I am not sure how to do this look up of values

Answer 1

Use DataFrame.melt for unpivot and then left join in DataFrame.merge:

df = d1.merge(d2.melt('category', value_name='min_value', var_name='col'), how='left')
print (df)
  category  col min_value
0        A  20%       2.1
1        B  40%       3.7
2        C  80%       5.6
3        D  40%       4.8
4        E  60%       7.9
5        F  20%       5.4

Alternative with DataFrame.join and DataFrame.stack:

df = d1.join(d2.set_index('category').stack().rename('min_value'), on=['category','col'])