Datetimes are inconsistently truncated in to_csv

[JacobHayes]

Code Sample, a copy-pastable example if possible

import pandas as pd
df = pd.DataFrame({
    'date': pd.to_datetime('1970-01-01'),
    'datetime': pd.date_range('1970-01-01', periods=5, freq='H')
})
df['date'].to_csv('/dev/stdout', index=False)
df[['date', 'datetime']].to_csv('/dev/stdout', index=False)

date
1970-01-01
1970-01-01
1970-01-01
1970-01-01
1970-01-01

date,datetime
1970-01-01 00:00:00,1970-01-01 00:00:00
1970-01-01 00:00:00,1970-01-01 01:00:00
1970-01-01 00:00:00,1970-01-01 02:00:00
1970-01-01 00:00:00,1970-01-01 03:00:00
1970-01-01 00:00:00,1970-01-01 04:00:00

Problem description

datetime64 series with all 00:00:00 time components are written out in inconsistent formats depending on the inclusion of time in other datetime64 series within the dataframe. I expected series with 00:00:00 times to be trimmed on a per-series basis, regardless of the inclusion of other Series with populated times. Per-series formatting would be better because the results become more independent and intuitive.

This makes testing more brittle because changes in one series (or adding a timed series to a df) have side effects for other series' output.

I have output CSVs that are then loaded into another system that expects date types in some columns and datetimes in others, which then fails to load because the date only columns have 00:00:00 included. Currently, I track internal column metadata and .dt.strftime('%Y-%m-%d').map(lambda x: x != 'NaT' and x or None) the date columns to get around the formatting.

Expected Output

# df[['date', 'datetime']].to_csv('/dev/stdout', index=False)
date,datetime
1970-01-01,1970-01-01 00:00:00
1970-01-01,1970-01-01 01:00:00
1970-01-01,1970-01-01 02:00:00
1970-01-01,1970-01-01 03:00:00
1970-01-01,1970-01-01 04:00:00

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.6.5.final.0
python-bits: 64
OS: Darwin
OS-release: 17.6.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.23.1
pytest: None
pip: 10.0.1
setuptools: 39.1.0
Cython: None
numpy: 1.14.3
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.7.3
pytz: 2018.4
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 2.2.2
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 1.0.1
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.8.1
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[gfyoung]

@JacobHayes : Thanks for reporting this! Do you mind also providing (i.e. copy/paste into this issue) what you're seeing currently?

[JacobHayes]

Sure!

1970-01-01
1970-01-01
1970-01-01
1970-01-01
1970-01-01

date,datetime
1970-01-01 00:00:00,1970-01-01 00:00:00
1970-01-01 00:00:00,1970-01-01 01:00:00
1970-01-01 00:00:00,1970-01-01 02:00:00
1970-01-01 00:00:00,1970-01-01 03:00:00
1970-01-01 00:00:00,1970-01-01 04:00:00

[gfyoung]

Hmm...that does look a little strange. Investigation and PR are welcome!

[jbrockmendel]

@mroeschke I know you've been looking at csvs+datetimes recently. Any suggestions for OP on where to start on this?

[mroeschke]

Just to clarify OP's ask, Pandas doesn't not support a date data type. pd.to_datetime('1970-01-01') will return a datetime.

In [8]: df.dtypes
Out[8]:
date        datetime64[ns]
datetime    datetime64[ns]
dtype: object

So trying to determine whether a column has date vs datetime data based on a repr is fragile.

I don't think this is a CSV issue, but that being said, maybe the datetime repr may be a bit confusing and or inconsistent between dates vs datetime and Series vs DataFrames respectively.

[JacobHayes]

@mroeschke I know pandas uses datetimes in all cases and I'm not trying to determine if the data is a date or datetime based on the repr. Instead, I'm loading data from CSV into more strictly typed columns (BigQuery in this case). The problem is that BigQuery (and I'd guess many other systems) validates the data matches the column type and errors (instead of truncating the datetime to date).

As I said originally, the problem is that the formatting of a "date" Series/datetime without time (within a DataFrame) is variable depending on the inclusion of other columns in the DataFrame. So even more subtly, the repr (aka the output that is fed to other systems and is considered more than just python/pandas implementation details IMO) is inconsistent even between DataFrames.

As an alternative to fixing the output format inference to be per-Series (with the current result seemingly being: "datetime repr if any Series has time, else date repr"), what do people think about changing the to_csv date_format argument to allow a mapping of Series name to format?

Edit:

I think I see where things may have gotten mixed up. @mroeschke - note that in my example I'm using to_csv, but explicitly writing to stdout, hence I'm not showing the in-python repr but the CSV output there. It is worth noting however that the DataFrame repr does have the "proper" output in all cases - Series without times are formatted without times even if there are other Series with times in the same DataFrame, as show in this example building from the one above:

>>> df
        date            datetime
0 1970-01-01 1970-01-01 00:00:00
1 1970-01-01 1970-01-01 01:00:00
2 1970-01-01 1970-01-01 02:00:00
3 1970-01-01 1970-01-01 03:00:00
4 1970-01-01 1970-01-01 04:00:00

[mroeschke]

Okay I better understand your point now, thanks! It makes sense that DataFrame.to_csv and Series.to_csv should write datetime columns consistently and in a similar way.

As a side note: You can export pandas objects directly to BigQuery instead of going to CSV and then BigQuery

https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_gbq.html

[agilevic]

It appears that if there is at least one datetime value within DataFrame (or slice thereof), which has a non-zero time component, dates in not only that but also other date columns would print out with the time component.

In [106]: print(ds.repo.loc[20:25, ['TradeDate', 'repo.CloseDate', 'Investment']].to_csv())
,TradeDate,repo.CloseDate,Investment
20,2020-08-31 00:00:00,2020-09-30 11:34:27,LOANDEPOT WAREHOUSE SENIOR
21,2020-08-31 00:00:00,2020-09-30 11:34:41,LOANDEPOT WAREHOUSE SENIOR
22,2020-04-20 00:00:00,,KIMI 8 D
23,2020-09-01 00:00:00,2020-10-02 00:00:00,SLMA 0 12/15/39 REGS
24,2020-09-09 00:00:00,2020-10-09 00:00:00,SLMA 0 12/15/39 REGS
25,2020-09-16 00:00:00,2020-10-19 00:00:00,SLMA 0 12/15/39 REGS

Conversely, if no non-zero time is present (from omitting the trouble column), a short date format prints.

In [107]: print(ds.repo.loc[20:25, ['TradeDate', 'Investment']].to_csv())
,TradeDate,Investment
20,2020-08-31,LOANDEPOT WAREHOUSE SENIOR
21,2020-08-31,LOANDEPOT WAREHOUSE SENIOR
22,2020-04-20,KIMI 8 D
23,2020-09-01,SLMA 0 12/15/39 REGS
24,2020-09-09,SLMA 0 12/15/39 REGS
25,2020-09-16,SLMA 0 12/15/39 REGS

The same effect as the above when we omit the trouble rows.

In [108]: print(ds.repo.loc[22:25, ['TradeDate', 'repo.CloseDate', 'Investment']].to_csv())
,TradeDate,repo.CloseDate,Investment
22,2020-04-20,,KIMI 8 D
23,2020-09-01,2020-10-02,SLMA 0 12/15/39 REGS
24,2020-09-09,2020-10-09,SLMA 0 12/15/39 REGS
25,2020-09-16,2020-10-19,SLMA 0 12/15/39 REGS

This does not fully explain another odd behavior where only some of the dates within in one column in one DataFrame would print with the time component but dates in the same column print without. I suspect this has to do with DataFrame’s internals and how data is stored in blocks of array of the same type. My DataFrame was a result of concat(), so guess is that after that operation the resulting DataFrame internally has data blocks from source DataFrames stored in disjoint blocks and that the above observation on the to_csv() behavior applies only within one block.

[phofl]

This happens because the datetime columns are a single block which is converted to the native format consistently

[sgusch-bn]

I do think that cause regression here (1.1.5 vs 1.4.3). In my case if all values in datetime field have time component set to 00:00 - in to_csv() output it's got truncated to pure date.

[phofl]

Can you provide something reproducible?

[sgusch-bn]

I just modified above example:

import pandas as pd
df = pd.DataFrame({
    'date': pd.to_datetime('1970-01-01 00:00:00'),
    'datetime': pd.date_range('1970-01-01', periods=5, freq='H')
})
print(df.dtypes)
df['date'].to_csv('/dev/stdout', index=False)
df[['date', 'datetime']].to_csv('/dev/stdout', index=False)

output from 1.1.5

date        datetime64[ns]
datetime    datetime64[ns]
dtype: object
date
1970-01-01
1970-01-01
1970-01-01
1970-01-01
1970-01-01
date,datetime
1970-01-01 00:00:00,1970-01-01 00:00:00
1970-01-01 00:00:00,1970-01-01 01:00:00
1970-01-01 00:00:00,1970-01-01 02:00:00
1970-01-01 00:00:00,1970-01-01 03:00:00
1970-01-01 00:00:00,1970-01-01 04:00:00

output from 1.4.3

date        datetime64[ns]
datetime    datetime64[ns]
dtype: object
date
1970-01-01
1970-01-01
1970-01-01
1970-01-01
1970-01-01
date,datetime
1970-01-01,1970-01-01 00:00:00
1970-01-01,1970-01-01 01:00:00
1970-01-01,1970-01-01 02:00:00
1970-01-01,1970-01-01 03:00:00
1970-01-01,1970-01-01 04:00:00

[phofl]

Thanks!

This is exactly what the issue was about? The new behavior is the expected behavior.

In case you require a specific format, you can specify a date_format explicitly

[sgusch-bn]

Unfortunately, date_format will cause all columns to be out in same form, instead of variations.

[phofl]

This is exactly what this fix avoided. Every column is treated individually now without a date_format

[sgusch-bn]

I see one inconsistency here: imagine you generate data on schedule, run at exact midnight will produce date, but run one minute later will have datetime. It will not be an issue in pandas data processing, but downstream systems will need to accept this variations.

[phofl]

In this case you can set the format manually? I can’t see a situation where you always want seconds precision but setting a format would not work.

Again, we are now treating datetime columns independently. This is the expected behavior. You could disagree with the removal of the 00:00:00 components in general, but this is not relevant to this fix

[sgusch-bn]

Yes, i will set it manually before dumping to CSV:
df["datetime"] =df["datetime"].dt.strftime("%Y-%m-%d %H:%M:%S")

I agree, that not relevant discussion for this fix as it actually add consistency in different form.