Custom sorting of df

user_7778246 0
Viewed 60

I have the following df with sector, equity and a value (MV) for each. In the lines where there only is a sector and no information under equity (like index 0 and 2), this is the sum of MV for the stocks which falls under this sector.

data = {'Sector':['Automobiles & Components','Automobiles & Components', 'Banks',  'Banks', 'Banks', 'Banks', 'Banks', 'Consumer Services', 'Consumer Services','Consumer Services', 'Materials' ,'Materials', 'Materials', 'Media & Entertainment', 'Media & Entertainment','Media & Entertainment', 'Retailing', 'Retailing', 'Transportation','Transportation'],
    'Equity':['','Autoliv Inc','', 'Nordea Bank Abp', 'Skandinaviska Enskilda Banken', 'Svenska Handelsbanken AB', 'Swedbank AB','','AcadeMedia AB','Evolution Gaming Group AB', '','Boliden AB', 'Svenska Cellulosa AB SCA', '', 'Modern Times Group MTG AB', 'Nordic Entertainment Group AB', '', 'H & M Hennes & Mauritz AB', '', 'Dfds A/S'],
    'MV': [2.345452e+07, 2.345452e+07, 8.112513e+07, 2.044148e+07,3.360675e+07,0,2.707689e+07,3.185811e+07,4.631995e+06,2.722611e+07,1.970195e+07,1.970195e+07,0,1.394064e+07,1.328866e+07,6.519856e+05,0,0,3.913005e+06,3.913005e+06]}


df = pd.DataFrame(data)

#Replace the sector with '' when it is a stock
df.loc[(df['Equity'] != '', 'Sector')] = ''

Index         Sector                         Equity          MV
0   Automobiles & Components                                 23454520.0
1                                               Autoliv Inc  23454520.0
2                      Banks                                 81125130.0
3                                           Nordea Bank Abp  20441480.0
4                             Skandinaviska Enskilda Banken  33606750.0
5                                  Svenska Handelsbanken AB         0.0
6                                               Swedbank AB  27076890.0
7          Consumer Services                                 31858110.0
8                                             AcadeMedia AB   4631995.0
9                                 Evolution Gaming Group AB  27226110.0
10                 Materials                                 19701950.0
11                                               Boliden AB  19701950.0
12                                 Svenska Cellulosa AB SCA         0.0
13     Media & Entertainment                                 13940640.0
14                                Modern Times Group MTG AB  13288660.0
15                            Nordic Entertainment Group AB    651985.6
16                 Retailing                                        0.0
17                                H & M Hennes & Mauritz AB         0.0
18            Transportation                                  3913005.0
19                                                 Dfds A/S   3913005.0

Right now, the df is sorted alphabetically according to the sector and then with the stocks in the particular sector beneath it.

However, I would like to have it sorted by sector according to the MV value in descending order and the same within each sector like a Pivot in Excel. Such that the outcome is instead become

Index         Sector                         Equity          MV
0                      Banks                                 81125130.0
1                             Skandinaviska Enskilda Banken  33606750.0
2                                                  Swedbank  27076890.0
3                                           Nordea Bank Abp  20441480.0
4                                  Svenska Handelsbanken AB         0.0
5          Consumer Services                                 31858110.0
6                                  Evolution Gaming Group A  27226110.0
7                                             AcadeMedia AB   4631995.0
8   Automobiles & Components                                 23454520.0
9                                               Autoliv Inc  23454520.0
10                 Materials                                 19701950.0
11                                               Boliden AB  19701950.0
12                                 Svenska Cellulosa AB SCA         0.0
13     Media & Entertainment                                 13940640.0
14                                Modern Times Group MTG AB  13288660.0
15                            Nordic Entertainment Group AB    651985.6
16                 Retailing                                        0.0
17                                H & M Hennes & Mauritz AB         0.0
18            Transportation                                  3913005.0
19                                                 Dfds A/S   3913005.5

I can't really figure out how to do this while also preserving the order of the totalsum for the sector and then the stocks in the sector.

pandas dataframe sorting
1 Answers

I created a new dataframe with the sector subtotal as a column, then use pivot_table and sort_values:

# Remove the Sector subtotals:
df1 = df.loc[df.Equity!='']

# Create dataframe the Sector subtotals only 
df2 = df.loc[df.Equity=='']

# Create a new dataframe with the sector subtotal as a column:
df_join = df1.merge(df2, on =['Sector']).rename(columns={"Equity_x": "Equity", "MV_x": "MV", "MV_y": "Sector_total"}).drop(columns=['Equity_y'])

# Apply pivot and sort:
df_pvt = pd.pivot_table(df_join, values=['MV','Sector_total'], index=['Sector', 'Equity'],
                    aggfunc='sum').sort_values(by=['Sector_total','MV','Sector'],ascending = False)

This is what I got as the final output:

MV         Sector_total
Sector                   Equity                                                                 
Banks                    Skandinaviska Enskilda Banken        33,606,750.00        81,125,130.00
                         Swedbank AB                          27,076,890.00        81,125,130.00
                         Nordea Bank Abp                      20,441,480.00        81,125,130.00
                         Svenska Handelsbanken AB                      0.00        81,125,130.00
Consumer Services        Evolution Gaming Group AB            27,226,110.00        31,858,110.00
                         AcadeMedia AB                         4,631,995.00        31,858,110.00
Automobiles & Components Autoliv Inc                          23,454,520.00        23,454,520.00
Materials                Boliden AB                           19,701,950.00        19,701,950.00
                         Svenska Cellulosa AB SCA                      0.00        19,701,950.00
Media & Entertainment    Modern Times Group MTG AB            13,288,660.00        13,940,640.00
                         Nordic Entertainment Group AB           651,985.60        13,940,640.00
Transportation           Dfds A/S                              3,913,005.00         3,913,005.00
Retailing                H & M Hennes & Mauritz AB                     0.00                 0.00
user_12717720
Related