Re-indexing in Pandas Pivot Table lose margins function?-CodePudding

 ```
    import numpy as np 
    import pandas as pd 
    import matplotlib.pyplot as plt
    import seaborn as sns
    import datetime
        
    
    
    df = pd.read_excel("Baltimore Towing Division.xlsx",sheet_name="TowingData")
    
   
    df['Month'] = pd.DatetimeIndex(df['TowedDate']).strftime("%b")
    df['Week day'] = pd.DatetimeIndex(df['TowedDate']).strftime("%a")
        
   
    monthOrder = ['Jan', 'Feb', 'Mar', 'Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']
    dayOrder = ['Mon','Tue','Wed','Thu','Fri','Sat','Sun']
    
       
    Pivotdf = pd.pivot_table(df, values='TowedDate',index='Month',
                            columns='Week day',
                            fill_value=0,
                            aggfunc= 'count', 
                            margins = True, margins_name='Total')
  
    print(Pivotdf)
```

Adding a total row and total column in pivot table but Months and Week days disorganized.

If I add any type of re-order function for month and week days, for some reason the Pivot table It loses the margins but does the correct orders of the months and week days.

The Pivot code:

    Pivotdf = pd.pivot_table(df, values='TowedDate',index='Month',
                            columns='Week day',
                            fill_value=0,
                            aggfunc= 'count', 
                            margins = True, margins_name='Total').loc[monthOrder,dayOrder]

CodePudding user response：

You lost Total because it's not included in monthOrder and dayOrder:

Pivotdf = pd.pivot_table(df, values='TowedDate',index='Month',
                        columns='Week day',
                        fill_value=0,
                        aggfunc= 'count', 
                        margins = True, margins_name='Total') \
            .loc[monthOrder   ['Total'], dayOrder   ['Total']]
print(Pivotdf)

# Output
Week day  Mon  Tue  Wed  Thu  Fri  Sat  Sun  Total
Month                                             
Jan         0    0    1    0    0    0    0      1
Feb         1    1    0    0    1    2    1      6
Mar         0    1    0    0    2    1    1      5
Apr         0    0    1    1    0    0    0      2
May         0    0    1    0    0    1    1      3
Jun         0    1    0    0    0    0    1      2
Jul         0    1    2    1    1    0    1      6
Aug         1    0    1    1    0    0    2      5
Sep         2    0    1    0    1    0    0      4
Oct         2    1    0    0    0    1    0      4
Nov         1    2    0    0    2    1    1      7
Dec         0    1    1    0    2    1    0      5
Total       7    8    8    3    9    7    8     50