How to search for a value in a pandas matrix and give the column names?

Question

Im new to programming with python and pandas and I have to write a script that, when given a matrix, finds the max values and prints the column names and row names the value is in. I have already searched here for an answer and there is a similar question, but the answer doesn't work for me:

matrix2 = pd.DataFrame(matrix1, index=row_names, columns=column_names)
print(matrix2)
print(matrix2.values.max())
print((matrix2 == (matrix2.values.max())).idxmax())

This is what I got so far, the output I get is:

            ABC123  DEF456  GHI789  JKL012  MNO057  QRS047
ABC123      40      16      -5       1      -4       1
DEF456      16      38      -5       1      -7       1
GHI789      -5      -5      58      -5      -1      -5
JKL012       1       1      -5      60       5      60
MNO057      -4      -7      -1       5      32       5
QRS047       1       1      -5      60       5      60
60
ABC123    ABC123
DEF456    ABC123
GHI789    ABC123
JKL012    JKL012
MNO057    ABC123
QRS047    JKL012
dtype: object

The desired output I would like to get in this case is:

JKL012 JKL012
JKL012 QRS047
QRS047 JKL012
QRS047 QRS047

I dont really understand why it gives me the output I have. Any help would be greatly appreciated!

Answer 1

You can make use of DF.stack so that you get those row and column values corresponding to the maximum value as the index for the creation of the new dataframe as shown:

pd.DataFrame(df[df == df.values.max()].stack().index.values.tolist())