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
?
Advertisement
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