```
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]
>Solution :
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