I am trying to aggregate a dataframe using pandas.pivot_table and find it behaves differently when multiple lines are aggregated on a categorical series.
Code from this issue helps explain (though the issue is different from mine).
Setting up a dataframe with a categorical column:
import pandas as pd
stations = ['Kings Cross Station', 'Newtown Station', 'Parramatta Station',
'Town Hall Station', 'Central Station', 'Circular Quay Station',
'Martin Place Station', 'Museum Station', 'St James Station',
'Bondi Junction Station', 'North Sydney Station']
df1 = pd.DataFrame({'Station': ['Kings Cross Station', 'Newtown Station', 'Parramatta Station',
'Kings Cross Station', 'Newtown Station', 'Parramatta Station',
'Kings Cross Station', 'Newtown Station', 'Parramatta Station'],
'Date': pd.DatetimeIndex(['1/1/2017', '1/1/2017', '1/1/2017',
'2/1/2017', '2/1/2017', '2/1/2017',
'3/1/2017', '3/1/2017', '3/1/2017',]),
'Exit': range(0, 9)})
df1.Station = df1.Station.astype(pd.CategoricalDtype(stations, ordered=True))
If I pivot the dataframe with
df1.pivot_table(index = 'Date', columns= 'Station', values = 'Exit',
dropna=False, observed=False, aggfunc=len, fill_value=0)
I get a dataframe with all the categories with the stations that had no data in the dataframe as columns filled with 0s, which is what I want:
Station Kings Cross Station ... North Sydney Station
Date ...
2017-01-01 1 ... 0
2017-02-01 1 ... 0
2017-03-01 1 ... 0
[3 rows x 11 columns]
However, if I add some rows with repeated values:
df2 = pd.DataFrame({'Station': ['Kings Cross Station', 'Newtown Station', 'Parramatta Station',
'Kings Cross Station', 'Newtown Station', 'Parramatta Station'],
'Date': pd.DatetimeIndex(['1/1/2017', '1/1/2017', '1/1/2017',
'2/1/2017', '2/1/2017', '2/1/2017']),
'Exit': range(0, 6)})
df3 = pd.concat([df1, df2])
... and pivot
df3.pivot_table(index = 'Date', columns= 'Station', values = 'Exit',
dropna=False, observed=False, aggfunc=len, fill_value=0)
Now the stations not represented in df3
do not apear in the pivot:
Station Kings Cross Station Newtown Station Parramatta Station
Date
2017-01-01 2 2 2
2017-02-01 2 2 2
2017-03-01 1 1 1
I can add the missing categories by iterating over the categories and add a column of 0s if not in the pivot table, but it should be done with pandas, surely?!
I hope that is clear, first question!
Thank you