-
Notifications
You must be signed in to change notification settings - Fork 96
TableTools.readCsv should be able to read the timestamp string representation Pandas uses (seems to be RFC3339). #1571
A file generated in python pandas using .to_csv(...) for a dataframe that was loaded from a parquet file with a timestamp(nanos) type column:
cfs@erke 16:55:20 ~/dh/oss1/deephaven-core/data
$ head -n 10 /a1/tmp/workqueue-no-nulls-100m-no-index.csv
workqueue_id,user_id,timestamp
1,1001,2021年11月07日 14:00:00.011463807+00:00
2,1003,2021年11月07日 14:00:00.080748962+00:00
3,1002,2021年11月07日 14:00:00.114976256+00:00
4,1004,2021年11月07日 14:00:00.118275772+00:00
5,1003,2021年11月07日 14:00:00.131173174+00:00
6,1001,2021年11月07日 14:00:00.174901899+00:00
7,1001,2021年11月07日 14:00:00.194499326+00:00
8,1002,2021年11月07日 14:00:00.201754097+00:00
9,1004,2021年11月07日 14:00:00.237870788+00:00
The timestamp column formatted as above is not recognized by DHC when using TableTools.readCsv; the column is read as String.
(削除) More generally, we should accept valid ISO 8601 strings as timestamps, any of them. (削除ここまで)
As Ryan points out below, this is not valid ISO 8601; it seems to be valid RFC3339 but is not necessarily what Pandas is even trying to do here; read the sequence of comments that follows.
All reactions
Replies: 7 comments 3 replies
@devinrsmith did add support for ISO 8601 timestamps. I think we're looking for 'T' as a separator. Wikipedia claims space is not an acceptable separator:
https://en.wikipedia.org/wiki/ISO_8601
A single point in time can be represented by concatenating a complete date expression, the letter "T" as a delimiter, and a valid time expression. For example, "2007-04-05T14:30". In ISO 8601:2004 it was permitted to omit the "T" character by mutual agreement as in "200704051430",[33] but this provision was removed in ISO 8601-1:2019. Separating date and time parts with other characters such as space is not allowed in ISO 8601, but allowed in its profile RFC 3339.[34]
All reactions
We should consider whether to be more permissive than the ISO standard.
All reactions
We should consider whether to be able to load csv files that pandas produces by default?
All reactions
Right now, the code is effectively using:
java.time.format.DateTimeFormatter#ISO_INSTANT
This looks like it's quite authoritatively parsing strict ISO 8601. We might be able to use java.time.format.DateTimeFormatterBuilder to build something more lenient.
@jcferretti Does Pandas publish a discussion for what kind of permissive formats they allow outside of the ISO standard?
All reactions
This seems relevant:
The RFC also allows the "T" to be replaced by a space
All reactions
More context:
This mentions Postgres does the same on output (space).
All reactions
So, maybe we turn this discussion into a feature request to support RFC-3339 timestamps. What I don't want is an open-ended "support whatever Pandas does" ticket without more research.
All reactions
After reading a bit more, my guess is, pandas dataframe.to_csv() (without further date format specification, ie, by default) is doing what python does for converting its datetime type to string.
This is a guess, it should be confirmed.
https://docs.python.org/3/library/datetime.html
datetime.__str__()¶
For a datetime instance d, str(d) is equivalent to d.isoformat(' ').
All reactions
This may shed a bit more light:
cfs@erke 17:24:13 ~/dh/oss1/deephaven-core/data
$ /l/parquet-cli/1.12.1/bin/parquet-cli meta ~/dh/oss1/deephaven-core/data/workqueue-no-nulls-100m.parquet | head -n 10
File path: /a0/h/cfs/dh/oss1/deephaven-core/data/workqueue-no-nulls-100m.parquet
Created by: parquet-mr version 1.12.0 (build db75a6815f2ba1d1ee89d1a90aeb296f1f3a8f20)
Properties: (none)
Schema:
message MyMessage {
optional int32 workqueue_id;
optional int32 user_id;
optional int64 timestamp (TIMESTAMP(NANOS,true));
}
(pyarrow)
cfs@erke 17:24:26 ~/dh/oss1/deephaven-core/data
$ python3
Python 3.8.10 (default, Sep 28 2021, 16:10:42)
[GCC 9.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyarrow.parquet as pq
>>> t = pq.read_table('/a0/h/cfs/dh/oss1/deephaven-core/data/workqueue-no-nulls-100m.parquet')
>>> print(t.schema)
workqueue_id: int32
user_id: int32
timestamp: timestamp[ns, tz=UTC]
>>> df = t.to_pandas()
>>> print(df.dtypes)
workqueue_id int32
user_id int32
timestamp datetime64[ns, UTC]
dtype: object
All reactions
Happy to support the common date-time formats that python/pandas produces by default.