Get Max Value Comparing Multiple Columns and Return Specific Values

Get Max value comparing multiple columns and return specific values

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)

How to retrieve the column name which has maximum value by comparing the values from multiple columns using 'Case' statements

With CASE? Something like this, perhaps?

SQL> with test (a, b, c, d) as
  2    (select 1, 2, 3, 1 from dual)
  3  select
  4    case when a >= b and a >= c and a >= d then a
  5         when b >= a and b >= c and b >= d then b
  6         when c >= a and c >= b and c >= d then c
  7         when d >= a and d >= b and d >= c then d
  8    end result
  9  from test;

    RESULT
----------
         3

SQL>

SQL MAX of multiple columns?

This is an old answer and broken in many way.

See https://stackoverflow.com/a/6871572/194653 which has way more upvotes and works with sql server 2008+ and handles nulls, etc.

Original but problematic answer:

Well, you can use the CASE statement:

SELECT
    CASE
        WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1
        WHEN Date2 >= Date1 AND Date2 >= Date3 THEN Date2
        WHEN Date3 >= Date1 AND Date3 >= Date2 THEN Date3
        ELSE                                        Date1
    END AS MostRecentDate

Find MAX value in a range with several Columns and Rows and return the value contained in row 1 and Column C

Use below formula to get column value.

=INDEX(A1:AO1,,ARRAYFORMULA(MAX(IF(TodosOsRanges=MAX(TodosOsRanges),COLUMN(TodosOsRanges)))))

And following formula to get row value.

=INDEX(C1:C101,ARRAYFORMULA(MAX(IF(TodosOsRanges=MAX(TodosOsRanges),ROW(TodosOsRanges)))))

See your sheet. I have edited to your provided sheet also.

Compare a column with multiple columns and return the index where condition matched first in pandas

Use DataFrame.lt with axis=0 for compare DataFrame with Series and then compare by DataFrame.any with DataFrame.idxmax and set new values by numpy.where:

df1 = df.iloc[:, 3:9]
mask = df1.lt(df['Age']* 0.75, axis=0)
df['Output'] = np.where(mask.any(axis=1), mask.idxmax(axis=1), df1.idxmax(axis=1))
print (df)
   Weight    Name  Age     1     2     3     4     5     6     7 Output
0      45     Sam  100 -75.0 -45.0 -92.0 -79.0 -57.0 -55.0 -35.0      1
1      88  Andrea   25  30.0 -17.0  -4.0  18.0  20.0  40.0   NaN      2
2      56    Alex   55 -47.0 -34.0 -12.0 -10.0  10.0   NaN   NaN      1
3      15   Robin    8  13.0  35.0  37.0  57.0   NaN   NaN   NaN      4
4      71     Kia   21  22.0  24.0  44.0   NaN   NaN   NaN   NaN      3
5      44     Sia   43   2.0  22.0   NaN   NaN   NaN   NaN   NaN      1
6      54    Ryan   45  20.0   NaN   NaN   NaN   NaN   NaN   NaN      1
7      34    Dimi   65   NaN   NaN   NaN   NaN   NaN   NaN   NaN    NaN

How to calculate max and min of multiple columns (row wise) using awk

You may use this awk:

awk 'BEGIN{FS=OFS=","} NR==1 {print $0, "New_col"; next} {print $0, ($2 > $4 ? $2 : $4) - ($3 < $5 ? $3 : $5)}' df.csv

col1,col2,col3,col4,col5,New_col
A,2,5,7,9,2
B,6,10,2,3,3
C,3,4,6,8,2

A more readable version:

awk '
BEGIN { FS = OFS = "," }
NR == 1 {
   print $0, "New_col"
   next
}
{
   print $0, ($2 > $4 ? $2 : $4) - ($3 < $5 ? $3 : $5)
}' df.csv

SQL compares the value of 2 columns and select the column with max value row-by-row

You can use window functions to solve the bulk of your problem:

select t.*
from (select t.*,
             row_number() over (partition by group order by value1 desc, value2 desc) as seqnum
      from t
     ) t
where seqnum = 1;

The one caveat is the condition:

If they're still the same, select the first one.

SQL tables represent unordered (multi-) sets. There is no "first" one unless a column specifies the ordering. The best you can do is choose an arbitrary value when all the other values are the same.

That said, you might have another column that has an ordering. If so, add that as a third key to the order by.

---

Related Topics