You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@arrow.apache.org by "jimwhite (via GitHub)" <gi...@apache.org> on 2024/09/09 21:20:18 UTC
[I] How do I read an integer Unix timestamp from a CSV file? [arrow]
jimwhite opened a new issue, #44030:
URL: https://github.com/apache/arrow/issues/44030
### Describe the usage question you have. Please include as many useful details as possible.
I have data from a commercial vendor that is delivered in compressed CSV files (.csv.gz). The timestamps are Unix timestamps as decimal integer and come in two flavors, either millisecond (`pa.timestamp('ms', tz='UTC')`) or nanosecond (`pa.timestamp('ns', tz='UTC')`). I've learned that the CSV conversion for these integer timestamps doesn't work because strptime(3) does not have a format option for them.
My workaround is to cast after reading:
```python
table.set_column(
table.column_names.index('window_start'),
'window_start',
table.column("window_start").cast(pa.timestamp('ns', tz='UTC'))
)
```
My question is whether I'm missing something about how to do this during the `pa.csv.read_csv` (these files are large and I want/need to process them incrementally) and if not whether I should raise this an enhancement request (I've looked at many issues around timestamps and haven't found any about this kind of format).
### Component(s)
Python
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] How do I read an integer Unix timestamp from a CSV file? [arrow]
Posted by "jimwhite (via GitHub)" <gi...@apache.org>.
jimwhite commented on issue #44030:
URL: https://github.com/apache/arrow/issues/44030#issuecomment-2339326582
> You can map the cols in question to these types explicitly in the conversion options via `column_types`: https://arrow.apache.org/docs/python/csv.html#customized-conversion
That's what I tried first but it doesn't work because that requires a [csv.ConvertOptions.timestamp_parsers](https://arrow.apache.org/docs/python/generated/pyarrow.csv.ConvertOptions.html#pyarrow.csv.ConvertOptions.timestamp_parsers) strptime(3) format string and there isn't one afaict for Unix timestamps in integer format (the strptime in Python does have one for Unix timestamps at second resolution but that isn't available in the C version - https://github.com/apache/arrow/issues/39839#issuecomment-1915981816 - and even if it were I need millisecond and nanosecond).
Or am I missing something? Do you have working example code?
I'm also wondering whether there is a way to apply the cast conversion during csv_read.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] How do I read an integer Unix timestamp from a CSV file? [arrow]
Posted by "assignUser (via GitHub)" <gi...@apache.org>.
assignUser commented on issue #44030:
URL: https://github.com/apache/arrow/issues/44030#issuecomment-2340297488
Hm, I would have expect that to work if the cast works... sorry than I can't really help I am more used to the R package and that handles csv read options a bit different.
@jorisvandenbossche what do you think? It seems to me if I can cast a col after reading it should be possible to do it while reading the file no?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Python] How do I read an integer Unix timestamp from a CSV file? [arrow]
Posted by "jimwhite (via GitHub)" <gi...@apache.org>.
jimwhite commented on issue #44030:
URL: https://github.com/apache/arrow/issues/44030#issuecomment-2341747496
Thanks everyone for the feedback!
I'm a total PyArrow noob and will put off the FR. My thinking is that this casting thing might have more use cases but since doing it in a second step is zero copy that may not really be worth adding another feature.
I do think this warrants a note in the docs and I'll add that to my (impossibly long) TODO list.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] How do I read an integer Unix timestamp from a CSV file? [arrow]
Posted by "jorisvandenbossche (via GitHub)" <gi...@apache.org>.
jorisvandenbossche commented on issue #44030:
URL: https://github.com/apache/arrow/issues/44030#issuecomment-2340565121
> My question is whether I'm missing something about how to do this during the `pa.csv.read_csv`
And your code looks good. An alternative way to do this is to cast the full table to a schema where the one column you want to change has the timestamp type (the `set_column` API is a bit awkward to use I find, casting the full table _can_ be easier if you that that schema at hand)
> we would need to have some way to distinguish the cases of parsing something as a string vs parsing it as a unix integer, because you can have values that could match both.
To illustrate this:
```
In [16]: from pyarrow import csv
In [17]: data = """a
...: 20240101"""
In [18]: import io
In [19]: csv.read_csv(io.BytesIO(data.encode()), convert_options=csv.ConvertOptions(
...: column_types={"a": pa.timestamp("ms")}, timestamp_parsers=["%Y%m%d"])
...: )
Out[19]:
pyarrow.Table
a: timestamp[ms]
----
a: [[2024-01-01 00:00:00.000]]
In [20]: csv.read_csv(io.BytesIO(data.encode())).cast(pa.schema([("a", pa.timestamp("s"))]))
Out[20]:
pyarrow.Table
a: timestamp[s]
----
a: [[1970-08-23 06:15:01]]
```
So we would need to "UnixTimestamp" placeholder to indicate in the parsers argument that something is to be treated as integer instead of being parsed as string ..
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Python] How do I read an integer Unix timestamp from a CSV file? [arrow]
Posted by "jimwhite (via GitHub)" <gi...@apache.org>.
jimwhite closed issue #44030: [Python] How do I read an integer Unix timestamp from a CSV file?
URL: https://github.com/apache/arrow/issues/44030
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] How do I read an integer Unix timestamp from a CSV file? [arrow]
Posted by "assignUser (via GitHub)" <gi...@apache.org>.
assignUser commented on issue #44030:
URL: https://github.com/apache/arrow/issues/44030#issuecomment-2339252148
You can map the cols in question to these types explicitly in the conversion options: https://arrow.apache.org/docs/python/csv.html#customized-conversion
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] How do I read an integer Unix timestamp from a CSV file? [arrow]
Posted by "jorisvandenbossche (via GitHub)" <gi...@apache.org>.
jorisvandenbossche commented on issue #44030:
URL: https://github.com/apache/arrow/issues/44030#issuecomment-2340548822
Yes, as you have noticed, parsing string values directly as timestamps while reading the CSV file is only supported for actual strptime-like string representations of timestamps, not for integer unix timestamps (because during the CSV parsing, it parses the value directly as a string, it does not first convert it to an integer and then cast that integer to timestamp).
It can certainly be a valid feature request to allow such cast as well, although: 1) given you can easily do it with a (zero-copy) cast afterwards, I would say it is probably not a priority to add, and 2) we would need to have some way to distinguish the cases of parsing something as a string vs parsing it as a unix integer, because you can have values that could match both.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] How do I read an integer Unix timestamp from a CSV file? [arrow]
Posted by "assignUser (via GitHub)" <gi...@apache.org>.
assignUser commented on issue #44030:
URL: https://github.com/apache/arrow/issues/44030#issuecomment-2340318111
Though the error also happens in R so it's a libarrow issue.
```
r$> read_csv_arrow('test.csv', col_types = schema(col_a = timestamp('ns','UTC')))
Error:
! Invalid: In CSV column #0: CSV conversion error to timestamp[ns, tz=UTC]: invalid value '1602076140000000000'
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org