BUG: to_csv produces improper output for categorical datetimes

[qwertystop]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

>>> ser = pd.to_datetime(pd.Series(["2021-03-27"]), format="%Y-%m-%d")
>>> ser
0   2021-03-27
dtype: datetime64[ns]
>>> ser.to_csv()
',0\n0,2021-03-27\n'
>>> ser.astype("category")
0   2021-03-27
dtype: category
Categories (1, datetime64[ns]): [2021-03-27]
>>> ser.astype("category").to_csv()
',0\n0,1616803200000000000\n'
>>> ser.astype("category").to_csv(date_format="%Y-%m-%dT%H:%M:%S")
',0\n0,1616803200000000000\n'

Problem description

When a categorical datetime is written as CSV, it disregards date_format (even if given explicitly) and writes the timestamp in integer nanoseconds.

Expected Output

>>> ser.astype("category").to_csv()
',0\n0,2021-03-27\n'
>>> ser.astype("category").to_csv(date_format="%Y-%m-%dT%H:%M:%S")
',0\n0,2021-03-27T00:00:00\n'

Output of pd.show_versions()

INSTALLED VERSIONS

commit : f2c8480
python : 3.9.1.final.0
python-bits : 64
OS : Linux
OS-release : 5.11.8-arch1-1
Version : #1 SMP PREEMPT Sun, 21 Mar 2021 01:55:51 +0000
machine : x86_64
processor :
byteorder : little
LC_ALL :
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.2.3
numpy : 1.19.5
pytz : 2021.1
dateutil : 2.8.1
pip : 21.0.1
setuptools : 49.2.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : None
IPython : 7.20.0
pandas_datareader: None
bs4 : None
bottleneck : None
fsspec : 0.8.7
fastparquet : None
gcsfs : None
matplotlib : 3.3.4
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyxlsb : None
s3fs : None
scipy : 1.6.0
sqlalchemy : None
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
numba : None

[rhshadrach]

Thanks for the report @qwertystop! Confirmed on master, investigations and PR to fix are welcome.

[kyleolaughlin]

take

[AndrewEckart]

Temporary workaround is to cast the categories to strings using .strftime and then cast back after .to_csv:

>>> import pandas as pd
>>> fmt = "%Y-%m-%d"
>>> ser = pd.to_datetime(pd.Series(["2021-03-27"]), format=fmt).astype("category")
>>> ser
0   2021-03-27
dtype: category
Categories (1, datetime64[ns]): [2021-03-27]
>>> ser.cat.rename_categories(ser.cat.categories.strftime(fmt), inplace=True)
>>> ser.to_csv()
',0\n0,2021-03-27\n'
>>> ser.cat.rename_categories(pd.to_datetime(ser.cat.categories, format=fmt), inplace=True)
>>> ser
0   2021-03-27
dtype: category
Categories (1, datetime64[ns]): [2021-03-27]