When creating a pivot table that includes a date field in the index I get a FutureWarning error:
<ipython-input-22-330e19e65ef9>:1: FutureWarning: Inferring datetime64[ns] from
data containing strings is deprecated and will be removed in a future version.
To retain the old behavior explicitly pass Series(data, dtype={value.dtype})
pivot_report = pd.pivot_table(
Here's the code that triggers it:
pivot_report = pd.pivot_table(
data.loc[(data["year"] == year_to_report)],
index = ["Category", "Subcategory", "date"],
values = [ "amount"],
aggfunc = [np.sum],
margins = True)
pivot_report
The data was read from csv with explicit types, and the date field appears to have been correctly converted to datetime64 before it hits the pivot_table call, so I'm not clear why it is complaining about type inference.
data['date']
0 2021-09-10
1 2021-09-08
2 2021-09-08
3 2021-09-08
4 2021-09-08
...
37299 2008-07-31
37300 2008-07-31
37301 2008-07-31
37302 2008-07-31
37303 2008-07-31
Name: date, Length: 37304, dtype: datetime64[ns]
However I note that while the margins parameter adds an All row at the bottom as expected, showing the grand total for the amount field, it also shows what looks like a NaT total for the date field.
sum
amount
Category Subcategory date
Auto & Transport Advertising 2021-01-01 0.00
2021-01-02 0.00
2021-01-03 0.00
2021-01-04 0.00
2021-01-05 0.00
... ...
Vacation Withdrawal 2021-09-06 0.00
2021-09-07 0.00
2021-09-08 0.00
2021-09-10 0.00
All NaT 134.31
[928450 rows x 1 columns]
Is this expected? Why would margins be trying to provide totals for a date field? And would this explain the type inference FutureWarning message?