Allow some way of specifying 'isoformat' as the 'date_format' parameter in 'to_csv' methods

[nugend]

Code Sample, a copy-pastable example if possible

>>> import pandas as pd
>>> mydf = pd.DataFrame.from_dict(dict(dt=[pd.Timestamp('2000-01-01'),pd.Timestamp('2000-01-01 00:00:00.000000001')]))
>>> print(mydf.to_csv(index=False))
dt
2000-01-01 00:00:00.000000000
2000-01-01 00:00:00.000000001
>>> print(mydf.to_csv(index=False,date_format='%Y-%m-%dT%H:%M:%S.%f'))
dt
2000-01-01T00:00:00.000000
2000-01-01T00:00:00.000000

Problem description

ISO-8601 specifies that the 'T' separator is optional in contexts where there is no ambiguity with other 8601 date or time formats. I'm trying to make some CSV files and am trying to be very strict with what I emit because I don't know how they'll be consumed.

It'd be really nice to be able to preserve full precision in the data but still have that separator character present and as far as I am aware, there is no Python Datetime format string which displays nanoseconds.

The shortest alternative I can come up with for this is the following (and I am not 100% sure this will work correctly):

tmp_df = mydf
dt_cols = mydf.select_dtypes(include='datetime64').columns
tmp_df[dt_cols] = tmp_df[dt_cols].applymap(lambda x:str(x.to_datetime64()))
tmp_df.to_csv(index=False)

Expected Output

dt
2000-01-01T00:00:00.000000000
2000-01-01T00:00:00.000000001

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.6.8.final.0
python-bits: 64
OS: Linux
OS-release: 3.10.0-327.36.3.el7.x86_64
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.24.2
pytest: 3.6.4
pip: 19.1.1
setuptools: 41.0.1
Cython: None
numpy: 1.16.4
scipy: None
pyarrow: 0.13.0
xarray: None
IPython: 7.5.0
sphinx: None
patsy: None
dateutil: 2.8.0
pytz: 2019.1
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 2.2.4
openpyxl: 2.5.14
xlrd: 1.1.0
xlwt: None
xlsxwriter: None
lxml.etree: None
bs4: None
html5lib: None
sqlalchemy: 1.2.19
pymysql: None
psycopg2: None
jinja2: 2.10.1
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

[mroeschke]

We send the output to datetime.strftime, so as you alluded to, this is a limitation of the standard library and unlikely to be fixed directly in pandas.

The alternative is to cast to string before saving to csv. This could be valuable to add to our documentation.

In [8]: print(mydf.dt.astype(str).to_csv(index=False))
2000-01-01 00:00:00.000000000
2000-01-01 00:00:00.000000001

[nugend]

I don't believe that's completely correct. AFAICT, the datetime string is emitted from this function:

pandas/pandas/_libs/tslib.pyx

Lines 220 to 300 in 54c0d5a

	def format_array_from_datetime(ndarray[int64_t] values, object tz=None,
	object format=None, object na_rep=None):
	"""
	return a np object array of the string formatted values
	Parameters
	----------
	values : a 1-d i8 array
	tz : the timezone (or None)
	format : optional, default is None
	a strftime capable string
	na_rep : optional, default is None
	a nat format
	"""
	cdef:
	int64_t val, ns, N = len(values)
	ndarray[int64_t] consider_values
	bint show_ms = 0, show_us = 0, show_ns = 0, basic_format = 0
	ndarray[object] result = np.empty(N, dtype=object)
	object ts, res
	npy_datetimestruct dts
	if na_rep is None:
	na_rep = 'NaT'
	# if we don't have a format nor tz, then choose
	# a format based on precision
	basic_format = format is None and tz is None
	if basic_format:
	consider_values = values[values != NPY_NAT]
	show_ns = (consider_values % 1000).any()
	if not show_ns:
	consider_values //= 1000
	show_us = (consider_values % 1000).any()
	if not show_ms:
	consider_values //= 1000
	show_ms = (consider_values % 1000).any()
	for i in range(N):
	val = values[i]
	if val == NPY_NAT:
	result[i] = na_rep
	elif basic_format:
	dt64_to_dtstruct(val, &dts)
	res = '%d-%.2d-%.2d %.2d:%.2d:%.2d' % (dts.year,
	dts.month,
	dts.day,
	dts.hour,
	dts.min,
	dts.sec)
	if show_ns:
	ns = dts.ps // 1000
	res += '.%.9d' % (ns + 1000 * dts.us)
	elif show_us:
	res += '.%.6d' % dts.us
	elif show_ms:
	res += '.%.3d' % (dts.us /1000)
	result[i] = res
	else:
	ts = Timestamp(val, tz=tz)
	if format is None:
	result[i] = str(ts)
	else:
	# invalid format string
	# requires dates > 1900
	try:
	result[i] = ts.strftime(format)
	except ValueError:
	result[i] = str(ts)
	return result

You can see on line 269 that the datetime string is manually constructed there when the date_format parameter is None. Is there any possibility of either detecting a specific date_format with an otherwise unused control character (Ruby uses %N, for example, so in that case, only the precise literal '%Y-%m-%dT%H:%M:%S.%N'), or maybe a iso8601sep binary flag or something? I know the former is nasty and the latter involves updating a bunch of callsites to pass a boolean through, but this doesn't need to touch datetime.strftime

Also, I am not sure I am following your suggestion: the separator is still missing from the output sample.

[khalludi]

Using the template from mroeschke, you can add the letter 'T' pretty easily by replacing the space character.

>>> mydf.dt.astype(str).apply(lambda x: x.replace(' ', 'T'))
0    2000-01-01T00:00:00.000000000
1    2000-01-01T00:00:00.000000001
Name: dt, dtype: object

I'm still new here, but I think the above workaround solves the issue of the optional T without having to change the codebase. Therefore, the issue can be closed.

Please correct me if I'm wrong! Linking @mroeschke for feedback.

[nugend]

I'll have to try and remember what we did. I think it might have been something similar.

[pcoccoli]

Why does pandas.DataFrame.to_json accept 'iso' as date_format but to_csv doesn't? to_string doesn't accept a date_format arg at all. It all seems so arbitrary.

[tommyjcarpenter]

Totally agree with @pcoccoli ; to_any_media_type() should have the same args - I see no semantic difference in timestamps that to_json would support iso but to_csv would not - just doesn't make much sense.

here is our literal code with a comment pointing here lol

    if iso:        
        # see https://github.com/pandas-dev/pandas/issues/27328        
        # to_json, which the non csv methods use, take a isoformat, but to_csv doesnt        
        kwargs["date_format"] = "%Y-%m-%dT%H:%M:%S.000Z"
    
    csv = data.to_csv(**kwargs)

[Simon-Bru]

This is really annoying everytime having to come up with the date format, I would like to submit my 🆙

[andrewmwilson]

Python 3.12 adds a %:z format string which solves this issue.