Formatting the output of a max and index values Pandas Python

user_14884004 0
Viewed 53

The code below outputs the max and its indexes for the specified columns max_columns and min_columns. I want to print it in the format of the expected result. Where the max values and their indexes are at the same row. How would I be able to arrange the max_values_STD_Q1, max_index_STD_Q1 so that I get the expected result.

input.csv file:

element,LNPT,SNPT,NLP,NSP,TNT,TPnL,MxPnL,MnPnL,MxU,MxD
[ 2.  2. 30.],0,0,4,4,8,-0.1,-0.0,-0.1,17127,-3
[ 2.  2. 40.],0,0,2,2,4,0.0,-0.0,-0.0,17141,-3
[ 2.  2. 50.],0,0,2,2,4,0.0,-0.0,-0.0,17139,-3
[ 2.  2. 60.],2,0,6,6,12,0.5,2.3,-1.9,17015,-3
[ 2.  2. 70.],1,0,4,4,8,0.3,0.3,-0.0,17011,-3

Code:

df = pd.read_csv('input.csv')
max_columns = ['LNPT', 'SNPT', 'NLP', 'MxPnL', 'MnPnL', 'MxD'] # named indexes

def max_vals():
    max_index = df[max_columns].idxmax() # so not df.iloc[:, max_columns]
    max_values = df[max_columns].max()
    return max_values, max_index

max_values_STD_Q1, max_index_STD_Q1= max_vals(R1,max_columns_STD_Q1)

print("Max values for STDOutputs_Q1:\n{}".format(max_values_STD_Q1))
print("Index: \n{}".format(max_index_STD_Q1))

Output:

Max values for STDOutputs_Q1:
LNPT      1777.0
SNPT      1677.0
NLP       7410.0
MxPnL       97.9
NSP       7410.0
TNT      14820.0
TPnL       853.1
MxU      26060.0
dtype: float64
Index: 
LNPT       215
SNPT       211
NLP        214
MxPnL    56175
NSP        214
TNT        214
TPnL      6842
MxU          1

Expected Output:

LNPT      1777.0   Index: 215
SNPT      1677.0   Index: 211
NLP       7410.0   Index: 214
MxPnL       97.9   Index: 56175
NSP       7410.0   Index: 214
TNT      14820.0   Index: 214
TPnL       853.1   Index: 6842
MxU      26060.0   Index: 1
dtype: float64
pandas dataframe formatting python database
2 Answers

Use DataFrame.agg by both functions with transpose by DataFrame.T:

df = pd.read_csv('input.csv')
max_columns = ['LNPT', 'SNPT', 'NLP', 'MxPnL', 'MnPnL', 'MxD'] # named indexes

df1 = df[max_columns].agg(['max','idxmax']).T
df1.columns = ['max_value', 'max_index']

print (df1)
       max_value  max_index
LNPT         2.0        3.0
SNPT         0.0        0.0
NLP          6.0        3.0
MxPnL        2.3        3.0
MnPnL       -0.0        1.0
MxD         -3.0        0.0

max_columns = ['LNPT', 'SNPT', 'NLP', 'MxPnL', 'MnPnL', 'MxD'] # named indexes

df1 = df[max_columns].agg(['max','idxmax']).T
df1.columns = ['max_value', 'max_index']
df1['max_index'] = 'Index: ' + df1['max_index'].astype(int).astype(str)

print (df1)
       max_value max_index
LNPT         2.0  Index: 3
SNPT         0.0  Index: 0
NLP          6.0  Index: 3
MxPnL        2.3  Index: 3
MnPnL       -0.0  Index: 1
MxD         -3.0  Index: 0

EDIT:

max_columns = ['LNPT', 'SNPT', 'NLP', 'MxPnL', 'MnPnL', 'MxD'] # named indexes

df1 = df.set_index('element')[max_columns].agg(['max','idxmax']).T
df1.columns = ['max_value', 'max_index']
df1['max_index'] = 'Index: ' + df1['max_index'].astype(str)

print (df1)
      max_value            max_index
LNPT          2  Index: [ 2. 2. 60.]
SNPT          0  Index: [ 2. 2. 30.]
NLP           6  Index: [ 2. 2. 60.]
MxPnL       2.3  Index: [ 2. 2. 60.]
MnPnL      -0.0  Index: [ 2. 2. 40.]
MxD          -3  Index: [ 2. 2. 30.]
user_29010020

You can concat max_values_STD_Q1 and max_index_STD_Q1, then rename columns. Add information to column max_index.

df = pd.concat([max_values_STD_Q1, max_index_STD_Q1], axis=1)

df.columns=['max_value', 'max_index']

df['max_index'] = 'Index: ' + df['max_index'].astype(str)

# print(df)

       max_value max_index
LNPT         2.0  Index: 3
SNPT         0.0  Index: 0
NLP          6.0  Index: 3
MxPnL        2.3  Index: 3
MnPnL       -0.0  Index: 1
MxD         -3.0  Index: 0
user_103151630
Related