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