How do I reorder by column totals?

Question

For example, how do I reorder each column sum and row sum in the following data with summed rows and columns?

import pandas as pd

data=[['fileA',47,15,3,5,7],['fileB',33,13,4,7,2],['fileC',25,17,9,3,5],
      ['fileD',25,7,1,4,2],['fileE',19,15,3,8,4], ['fileF',11,17,8,4,5]]

df = pd.DataFrame(data, columns=['filename','rows_cnt','cols_cnt','col_A','col_B','col_C'])
print(df)

  filename  rows_cnt  cols_cnt  col_A  col_B  col_C
0    fileA        47        15      3      5      7
1    fileB        33        13      4      7      2
2    fileC        25        17      9      3      5
3    fileD        25         7      1      4      2
4    fileE        19        15      3      8      4
5    fileF        11        17      8      4      5

df.loc[6]= df.sum(0)
    filename    rows_cnt    cols_cnt    col_A   col_B   col_C
0   fileA       47  15  3   5   7
1   fileB       33  13  4   7   2
2   fileC       25  17  9   3   5
3   fileD       25  7   1   4   2
4   fileE       19  15  3   8   4
5   fileF       11  17  8   4   5
6   fileA...    160 84  28  31  25

I made an image of the question. How do I reorder the red frame in this image by the standard?

df.reindex([2,5,0,4,1,3,6], axis='index')

Is the only way to create the index manually like this?

user_11241962 · Answer

data=[['fileA',47,15,3,5,7],['fileB',33,13,4,7,2],['fileC',25,17,9,3,5],
      ['fileD',25,7,1,4,2],['fileE',19,15,3,8,4], ['fileF',11,17,8,4,5]]

df = pd.DataFrame(data, columns=['filename','rows_cnt','cols_cnt','col_A','col_B','col_C'])
df = df.sort_values(by='cols_cnt', axis=0, ascending=False)
df.loc[6]= df.sum(0)

# to keep number original of index
df = df.reset_index(drop=False)

# need to remove this filename column, since need to sort by column (axis=1)
# unable sort with str and integer data type
df = df.set_index('filename', drop=True)
df = df.sort_values(by=df.index[-1], axis=1, ascending=False)

# set back the index of dataframe into original
df = df.reset_index(drop=False)
df = df.set_index('index', drop=True)

# try to set the fixed columns
fixed_cols = ['filename', 'rows_cnt','cols_cnt']

# try get the new order of columns by fixed the first three columns
# and then add with the remaining columns
new_cols = fixed_cols + (df.columns.drop(fixed_cols).tolist())

df[new_cols]

How do I reorder by column totals?

1 Answers