Extract corresponding df value with reference from another df

There are 2 dataframes with 1 to 1 correspondence. I can retrieve an idxmax from all columns in df1.

Input:

df1 = pd.DataFrame({'ref':[2,4,6,8,10,12,14],'value1':[76,23,43,34,0,78,34],'value2':[1,45,8,0,76,45,56]})
df2 = pd.DataFrame({'ref':[2,4,6,8,10,12,14],'value1_pair':[0,0,0,0,180,180,90],'value2_pair':[0,0,0,0,90,180,90]})

df=df1.loc[df1.iloc[:,1:].idxmax(), 'ref']

Output: df1, df2 and df

     ref  value1  value2
0    2      76       1
1    4      23      45
2    6      43       8
3    8      34       0
4   10       0      76
5   12      78      45
6   14      34      56  

     ref  value1_pair  value2_pair
0    2            0            0
1    4            0            0
2    6            0            0
3    8            0            0
4   10          180           90
5   12          180          180
6   14           90           90

    5    12
    4    10
Name: ref, dtype: int64

Now I want to create a df which contains 3 columns

Desired Output df:

ref   max value   corresponding value
12    78          180
10    76          90

What are the best options to extract the corresponding values from df2?

Answer

Your main problem is matching the columns between df1 and df2. Let’s rename them properly, melt both dataframes, merge and extract:

(df1.melt('ref')
    .merge(df2.rename(columns={'value1_pair':'value1', 
                               'value2_pair':'value2'})
              .melt('ref'), 
           on=['ref', 'variable'])
    .sort_values('value_x')
    .groupby('variable').last()
)

Output:

          ref  value_x  value_y
variable                       
value1     12       78      180
value2     10       76       90