create new df3 with max values from df1 and corresponding values (same position) from df2

user_9106985 0
Viewed 43

I would like to create a new df3 based on

1) finding the max value in each column of df1 and

2) then appending a row with the corresponding values from df2 (same position of df1)

Input:

data = [[2, 10], [5, 15], [7, 14]]
data1 = [[0.5, 0.25], [.55, .115], [.45, .414]]
    
    df1 = pd.DataFrame(data)
    df2 = pd.DataFrame(data1)
    df1.max()
    df1.idxmax()

Output: df1

   0   1
0  2  10
1  5  15
2  7  14

Output: df2

      0      1
0  0.50  0.250
1  0.55  0.115
2  0.45  0.414

Desired Output: df3

         0     1
0        7     15
1        0.45  0.115

Note: The columns in df1 will be unique.

pandas python
2 Answers

You can try:

df3 = pd.DataFrame([df1.max().tolist(), [df2.at[row, col] for row, col in zip(df1.idxmax(), df1.columns)]])

Output:

      0       1
0  7.00  15.000
1  0.45   0.115
user_74758380

There is no need to loop via creating a mask.

df3 = pd.DataFrame([df1.max(), df2.values.flatten('F')[(np.amax(df1.values, axis=0) == df1.values).flatten('F')]])

df3
###
      0       1
0  7.00  15.000
1  0.45   0.115
user_145278860
Related