How to find min after a max value in DataFrame python?

Question

Imagine for the following DataSet:

data = {"row1":[1,2,3,4,5], "row2" : [2,3,4,1,3], "row3":[3,4,2,0,0] }
data = pd.DataFrame(data)
data = data.T
print(data)


      0  1  2  3  4
row1  1  2  3  4  5
row2  2  3  4  1  3
row3  3  4  2  0  0

I want to find the min value that appears after the max value. So any min value that exists before the max value is not of my interest. I have written this code:

data["Max_Value"] = data.max(axis=1)
data["Max_Index"] = data.idxmax(axis=1)

dt["Min_Value"] = dt.iloc[:, dt["Max_Index"]:].min(axis=1)
dt["Min_Index"] = dt.iloc[:, dt["Max_Index"]:].idxmin(axis=1)

dt["Max_Index"]:] is my attempt to get the index of max value in a dynamic way. So then I can find the min value which appears after that part of the data set. But it doesn't work as I intend to.

I want to have an outcome as follow:

      0  1  2  3  4  Max_Value   Max_Index  Min_Value  Min_Index
row1  1  2  3  4  5      5           4         5          4
row2  2  3  4  1  3      4           2         1          3
row3  3  4  2  0  0      4           1         0          3

It doesn't work and I don't want to use the loop as well, what is my mistake?

Answer 1

Dynamically get the Min value and Index based on Max Value

To get the Min value and its Index based on the Max Value in a row, you will need to extract the data in the row as a list. Then do operations on the list to get the min and index value.

To do this, you can use the below code:

import pandas as pd
data = {"row1":[1,2,3,4,5], "row2" : [2,3,4,1,3], "row3":[3,4,2,0,0] }
data = pd.DataFrame(data)
data = data.T
print(data)
data["Max_Value"] = data.max(axis=1)
data['Max_Val_Index'] = data.iloc[:5].idxmax(axis=1)

def custom_find_min(x):
    y = x[x.index(x[5]):5]
    return min(y)
def custom_find_min_idx(x):
    y = x[x[6]:5]
    return x.index(x[5]) + y.index(x[-1])

data["Min_Value"]   = data.apply(lambda x: custom_find_min(x.tolist()),axis=1)
data["Min_Val_Idx"] = data.apply(lambda x: custom_find_min_idx(x.tolist()),axis=1)

print (data)

Alternatively, you can skip the function definition and do it all on the same line. Here's the code for it:

data["Min_Value"] = data.apply(lambda x: min(x.tolist()[x['Max_Val_Index']:5]),axis=1)
data["Min_Val_Index"] = data.apply(lambda x: x['Max_Val_Index'] + x.tolist()[x['Max_Val_Index']:5].index(x['Min_Value']),axis=1)

The Output of this will be:

Original DataFrame:

      0  1  2  3  4
row1  1  2  3  4  5
row2  2  3  4  1  3
row3  3  4  2  0  0

Updated DataFrame with Max, Max Index, Dynamic Min, Dynamic Min Index.

      0  1  2  3  4  Max_Value  Max_Val_Index  Min_Value  Min_Val_Idx
row1  1  2  3  4  5          5              4          5            4
row2  2  3  4  1  3          4              2          1            3
row3  3  4  2  0  0          4              1          0            3

To retain history, I am keeping the below section.

Edit Ver #1: Get Min & Max in a row

Answer for the earlier version of the question. Find the Min & Max for each row

You should use iloc and give the range of columns.

import pandas as pd
data = {"row1":[1,2,3,4,5], "row2" : [2,3,4,1,3], "row3":[3,4,2,0,0] }
data = pd.DataFrame(data)
data = data.T
print(data)
data["Max_Value"] = data.max(axis=1)
data['Max_Val_Index'] = data.iloc[:5].idxmax(axis=1)
data["Min_Value"] = data.min(axis=1)
data['Min_Val_Index'] = data.iloc[:5].idxmin(axis=1)
print (data)

Original DataFrame:

      0  1  2  3  4
row1  1  2  3  4  5
row2  2  3  4  1  3
row3  3  4  2  0  0

Updated DataFrame with Min, Max, and idx for Min & Max.

      0  1  2  3  4  Max_Value  Max_Val_Index  Min_Value  Min_Val_Index
row1  1  2  3  4  5          5              4          1              0
row2  2  3  4  1  3          4              2          1              3
row3  3  4  2  0  0          4              1          0              3