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, index=['Month'],
values=['TowedDate'],
columns=['Week day'],
fill_value=0,
aggfunc='count').reindex(monthOrder,axis=0).reindex(dayOrder,axis=1)
print(df)
I use the .reindex function at the end of the pivot table for reindex the Months and the columns 'Week day', it returns a NaN in the results.
Using .reindex in axis=1
Not doing the .reindex at the column of the days the Pivot table brings me the results, but with the days of the week disorganized. I need them to appear in the table in order like this: Mon, Tue, Wed, Thu, Fri, Sat, Sun
Whitout using .reindex in axis=1
CodePudding user response:
Maybe using loc
:
# with values=['TowedDate'] -> MultiIndex
Pivotdf = pd.pivot_table(df, index=['Month'],
values=['TowedDate'],
columns=['Week day'],
fill_value=0,
aggfunc='count').loc[monthOrder, (slice(None), dayOrder)]
# OR
# with values='TowedDate' -> Index
Pivotdf = pd.pivot_table(df, index=['Month'],
values='TowedDate',
columns=['Week day'],
fill_value=0,
aggfunc='count').loc[monthOrder, dayOrder)]
Output:
>>> Pivotdf
TowedDate
Week day Mon Tue Wed Thu Fri Sat Sun
Month
Jan 1 0 1 0 1 0 0
Feb 2 0 1 0 1 0 0
Mar 1 0 0 0 0 0 0
Apr 0 0 0 1 0 1 0
May 0 1 1 3 1 1 2
Jun 1 0 0 0 0 1 2
Jul 0 1 0 0 2 0 0
Aug 3 0 0 0 1 2 1
Sep 0 0 1 1 0 1 0
Oct 3 0 0 0 1 0 1
Nov 1 0 0 0 1 2 3
Dec 0 1 1 0 0 0 0
CodePudding user response:
Corralien's method solved the problem.
# with values=['TowedDate'] -> MultiIndex
Pivotdf = pd.pivot_table(df, index=['Month'],
values=['TowedDate'],
columns=['Week day'],
fill_value=0,
aggfunc='count').loc[monthOrder, (slice(None), dayOrder)]
Millions of thanks to all the members who answered and assisted me in this question