Get Max value comparing multiple columns and return specific values
Without using numpy
wizardry:
- First, there are some really great solutions to this problem, by others.
- Data will be that provided in the question, as
df
# find the max value in the Duration columns
max_value = max(df.filter(like='Dur', axis=1).max().tolist())
# get a Boolean match of the dataframe for max_value
df_max = df[df == mv]
# get the row index
max_index = df_max.dropna(how='all').index[0]
# get the column name
max_col = df_max.dropna(axis=1, how='all').columns[0]
# get column index
max_col_index = df.columns.get_loc(max_col)
# final
df.iloc[max_index, [0, max_col_index, max_col_index + 1]]
Output:
Sequence 1008
Duration3 981
Value3 82
Name: 7, dtype: int64
Update
- Last night, actually 4 a.m., I dismissed a better solution, because I was overly tired.
- I used
max_value = max(df.filter(like='Dur', axis=1).max().tolist())
, to return the maximum value within theDuration
columns - Instead of
max_col_name = df.filter(like='Dur', axis=1).max().idxmax()
, to return the column name where the maximum value occurs - I did that because my addled brain told me I was returning the max value of the column names, instead of the maximum value in the column. For example:
- I used
test = ['Duration5', 'Duration2', 'Duration3']
print(max(test))
>>> 'Duration5'
- This is why being overtired, is a poor problem solving condition
- With sleep, and coffee, a more efficient solution
- Similar to others, in the use of
idmax
- Similar to others, in the use of
New & Improved Solution:
# column name with max duration value
max_col_name = df.filter(like='Dur', axis=1).max().idxmax()
# index of max_col_name
max_col_idx =df.columns.get_loc(max_col_name)
# row index of max value in max_col_name
max_row_idx = df[max_col_name].idxmax()
# output with .loc
df.iloc[max_row_idx, [0, max_col_idx, max_col_idx + 1 ]]
Output:
Sequence 1008
Duration3 981
Value3 82
Name: 7, dtype: int64
Methods used:
pandas.DataFrame.max
pandas.DataFrame.filter
pandas.DataFrame.idxmax
pandas.Index.get_loc
pandas.DataFrame.iloc
With wide data it can be easier to first reshape with wide_to_long
. This creates 2 columns ['Duration', 'Value']
, and the MultiIndex tells us which number it was. There is no reliance on any specific column ordering.
import pandas as pd
df = pd.wide_to_long(df, i='Sequence', j='num', stubnames=['Duration', 'Value'])
df.loc[[df.Duration.idxmax()]]
Duration Value
Sequence num
1008 3 981 82
Try the following, quite short code, based mainly on Numpy:
vv = df.iloc[:, 1::2].values
iRow, iCol = np.unravel_index(vv.argmax(), vv.shape)
iCol = iCol * 2 + 1
result = df.iloc[iRow, [0, iCol, iCol + 1]]
The result is a Series:
Sequence 1008
Duration3 981
Value3 82
Name: 7, dtype: int64
If you want to "rehape" it (first index values, then actual values), you can get something like this executing:
pd.DataFrame([result.values], columns=result.index)