How to find single largest value from all rows and column array in Python and also show its row and column index

Question

I am new to python. I want to find one largest value from a grid and also show its respective row and column index label in output.The value should be absolute. (Irrespective of + or - sign) My data structure is like image shown: My data set

EleNo._ Exat0_  Exat10_ Exat20_ Exat30_ Exat40_ Exat50

1000____ 10____   20___  -30____  23_____ 28____  18

2536____-20___   -36___  -33___  -38_____ 2____  -10

3562_____ 3____    4______ 8_____  8_____ 34_____  4

2561_____ 2____    4______ 7_____  6_____ 22____  20

I tried (df.abs().max()) but it is showing max value for every row and only positive values. I want absoulte max value.

Expected Results: what i want in output

EleNo.: 2536

Exat30 : -38

Actual Result: what i am getting in output

Element No. 3562

Exat0:  20

Exat10: 36

Exat20: 33

Exat30: 38

Exat40: 34

Exat50: 20

jezrael · Accepted Answer · 2019-05-14 06:52:56Z

3

Use numpy.unravel_index for indices and create DataFrame by constructor with indexing:

df = pd.DataFrame({'Exat0': [10, -20, 3, 2], 
                   'Exat10': [20, -36, 4, 4], 
                   'Exat20': [-30, -33, 8, 7], 
                   'Exat30': [23, -38, 8, 6],
                   'Exat40': [28, 2, 34, 22], 
                   'Exat50': [18, -10, 4, 20]}, index=[1000, 2536, 3562, 2561])
df.index.name='EleNo.'
print (df)
        Exat0  Exat10  Exat20  Exat30  Exat40  Exat50
EleNo.                                               
1000       10      20     -30      23      28      18
2536      -20     -36     -33     -38       2     -10
3562        3       4       8       8      34       4
2561        2       4       7       6      22      20

a = df.abs().values
r,c = np.unravel_index(a.argmax(), a.shape)
print (r, c)
1 3

df1 = pd.DataFrame(df.values[r, c], 
                   columns=[df.columns.values[c]], 
                   index=[df.index.values[r]])
df1.index.name='EleNo.'
print (df1)
        Exat30
EleNo.        
2536       -38

Another only pandas solution with DataFrame.abs, DataFrame.stack and indices of max value by Series.idxmax:

r1, c1 = df.abs().stack().idxmax()

Last select by DataFrame.loc:

df1 = df.loc[[r1], [c1]]
print (df1)
        Exat30
EleNo.        
2536       -38

EDIT:

df = pd.DataFrame({'Exat0': [10, -20, 3, 2], 
                   'Exat10': [20, -36, 4, 4], 
                   'Exat20': [-30, -33, 8, 7], 
                   'Exat30': [23, -38, 8, 6],
                   'Exat40': [28, 2, 34, -38], 
                   'Exat50': [18, -10, 4, 20]}, index=[1000, 2536, 3562, 2561])
df.index.name='EleNo.'
print (df)
        Exat0  Exat10  Exat20  Exat30  Exat40  Exat50
EleNo.                                               
1000       10      20     -30      23      28      18
2536      -20     -36     -33     -38       2     -10
3562        3       4       8       8      34       4
2561        2       4       7       6     -38      20

s = df.abs().stack()
mask = s == s.max()

df1 = df.stack()[mask].unstack()
print (df1)
        Exat30  Exat40
EleNo.                
2536     -38.0     NaN
2561       NaN   -38.0

df2 = df.stack()[mask].reset_index()
df2.columns = ['EleNo.','cols','values']
print (df2)
   EleNo.    cols  values
0    2536  Exat30     -38
1    2561  Exat40     -38

edited May 14, 2019 at 6:52

answered May 14, 2019 at 5:26

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

axay Over a year ago

Thanks. But there are 2 issues in output. Every time the EleNo. 3562 is displayed even if the row value is changed. The Index should change for respective largest value from column 1 to column 6. Also if a positive value is greater than -38, i.e. +45 for a speecific row, output is still showing -38 as maximum value. I want the largest Numeric value

jezrael Over a year ago

@AkshayK. - Added sample data, can you test my solution with it? Because if change data max or min, then it working nice for me. E.g.

df = pd.DataFrame({'EleNo.': [1000, 2536, 3562, 2561],                    'Exat0': [10, -200, 3, 2],                     'Exat10': [20, -36, 4, 4],                     'Exat20': [-30, -33, 8, 7],                     'Exat30': [23, -38, 8, 6],                    'Exat40': [28, 2, 34, 22],                     'Exat50': [18000, -10, 4, 20]})      print (df)

axay Over a year ago

Thanks for it. Actually I dont want the largest value from "EleNo." column. I want to find the largest value from Column Name(Exat0 to Exat50). Only is corresponding Row value from (EleNo) must be diplayed. For your example array, I want output in form of EleNo. :1000 , Exat50:18000. Thanks

jezrael Over a year ago

@AkshayK. - Now understand, adited answer.

axay Over a year ago

Posted a modified question

|

run-out · Accepted Answer · 2019-05-14 05:52:45Z

Use a combination of max() and dropna()

First create a dataframe:

df = pd.DataFrame(np.random.randn(4,4))

          0         1         2         3
0  0.051775  0.352410 -0.451630 -0.452446
1 -1.434128  0.516264 -0.807776 -0.077892
2  1.615521  0.870604 -0.010285 -0.322280
3 -0.027598  1.046129 -0.165166  0.365150

Calculate the max() twice to get the maximum value in the dataframe, and then cut out the rows and columns with nan.

result = df[df == abs(df).max().max()].dropna(axis=0, how="all").dropna(axis=1, how='all')

print(result)
          0
2  1.615521

Finally, get the column and row value, plus the max value.

max_value = result.values.item()
max_column = result.columns.values[0]
max_row = result.index.values[0]

print('max_value', max_value, 'max_column', max_column,'max_row', max_row)

max_value 1.615520522284493 max_column 0 max_row 2

Serge Ballesta · Accepted Answer · 2019-05-14 06:12:55Z

1

Your problem is that you have forgotten to tell pandas that the column EleNo. was the index. After that point, things are simpler: just build a series with the max of the absolute value of each line, take the index of the max of that serie, and use it to find the required line in the original dataframe. Code could be:

s = df.set_index('EleNo.').apply(np.absolute).max(axis=1)

print(df[df['EleNo.'] == s[s == s.max()].index[0]])

Display is as expected:

   EleNo.  Exat0  Exat10  Exat20  Exat30  Exat40  Exat50
1    2536    -20     -36     -33     -38       2     -10

answered May 14, 2019 at 6:12

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

3 Comments

axay Over a year ago

The diplay should only include EleNo. 2356 and Exat30 -38 The remaining must not be displayed.

axay Over a year ago

I only want maximum value from the columns Exat0 to Exat50 and its corresponding index value from EleNo. column

user3483203 Over a year ago

You should be using abs instead of apply

Collectives™ on Stack Overflow

How to find single largest value from all rows and column array in Python and also show its row and column index

3 Answers 3

14 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

14 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related