Convert string time to timestamp.

[Milad137]

I have a csv file with datetime formatted as "%d/%m/%Y %I:%M %p". I can convert them to pandas datetime but when I use pyarrow's strptime I get the following error.
ArrowInvalid: Failed to parse string: '9/03/2043 12:35 AM' as a scalar of type timestamp[s]

Code to reproduce:

import pandas as pd
import pyarrow.compute as pc

# pd.__version__ == '1.3.4'
# pyarrow.__version__ == '8.0.0'

_ts = ["9/03/2043 12:35 AM"]
_format = "%d/%m/%Y %I:%M %p"

pd.to_datetime(_ts, format=_format)
pc.strptime(_ts, format= _format, unit='s')

[AlenkaF]

Unfortunately I wasn't able to reproduce locally:

>>> import pyarrow
>>> import pandas as pd
>>> pyarrow.__version__
'8.0.0'
>>> pd.__version__
'1.4.2'

>>> import pyarrow.compute as pc
>>> _ts = ["9/03/2043 12:35 AM"]
>>> _format = "%d/%m/%Y %I:%M %p"

>>> pd.to_datetime(_ts, format=_format)
DatetimeIndex(['2043-03-09 00:35:00'], dtype='datetime64[ns]', freq=None)
>>> pc.strptime(_ts, format= _format, unit='s')
<pyarrow.lib.TimestampArray object at 0x7fcae0078e80>
[
  2043-03-09 00:35:00
]

[rok]

@Milad137 what OS are you on?

[Milad137]

@rok

@Milad137 what OS are you on?

I'm on windows.
I just ran it on a Linux machine, and it worked fine. It must be an OS issue, then.

[rok]

@Milad137 Arrow is using arrow_strptime on windows and system strptime on other systems. There might be a feature (available strptime flags) gap.
Could you please test a subset of flags and see if a particular one is missing for you? E.g.:

_ts = ["9/03/2043 12:35"]
_format = "%d/%m/%Y %I:%M"

_ts = ["9/03/2043"]
_format = "%d/%m/%Y"

_ts = ["12:35 AM"]
_format = "%I:%M %p"

[Milad137]

@rok The first 2 are working fine, it's just the last one, when the "%p" tag gets added, it throws an error.
I ran it on two windows machines, still getting the same error.

>>> import pandas as pd
>>> import pyarrow.compute as pc

>>> # pd.__version__ == '1.3.4'
>>> # pyarrow.__version__ == '8.0.0'

>>> _ts = ["9/03/2043 12:35"]
>>> _format = "%d/%m/%Y %I:%M"
>>> print(pc.strptime(_ts, format= _format, unit='s'))
[
  2043-03-09 12:35:00
]

>>> _ts = ["9/03/2043"]
>>> _format = "%d/%m/%Y"
>>> print(pc.strptime(_ts, format= _format, unit='s'))
[
  2043-03-09 00:00:00
]

>>> _ts = ["12:35 AM"]
>>> _format = "%I:%M %p"
>>> print(pc.strptime(_ts, format= _format, unit='s'))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "~\anaconda3\lib\site-packages\pyarrow\compute.py", line 250, in wrapper
    return func.call(args, options, memory_pool)
  File "pyarrow\_compute.pyx", line 341, in pyarrow._compute.Function.call
  File "pyarrow\error.pxi", line 144, in pyarrow.lib.pyarrow_internal_check_status
  File "pyarrow\error.pxi", line 100, in pyarrow.lib.check_status
pyarrow.lib.ArrowInvalid: Failed to parse string: '12:35 AM' as a scalar of type timestamp[s]

[jorisvandenbossche]

That seems a limitation of the vendored strptime implementation then. @rok do you want to open a JIRA for this?

[pitrou]

The vendored strptime implementation does support %p, but we have to disable it on Windows because the behavior is locale-dependent and it's not easy to support there.

(of course, we may support it in a non-locale-dependent way, i.e. always use the same values as a "C" Unix locale)

[rok]

Sorry I lost sight on this one.
Opened ARROW-16618. Similarly we have ARROW-16443 reported from R.
Is there an easy way for users to change locale to "C" as a workaround @pitrou?

[RandomFractals]

I ran into the same issue while trying to convert string dates in Chicago crimes datatset.

@pitrou What was the resolution of this ticket, or a workaround to convert date strings with AM/PM strings on Windows? I am on win10.

Data repo: https://github.com/RandomFractals/chicago-crimes

Error pic with more info:

[Milad137]

My workaround was to use VSCode dev container.

[rok]

Hey @RandomFractals! There was no work done on ARROW-16618 as there wasn't much expressed interest. Right now @Milad137's workaround might be the best good idea.

[RandomFractals]

My workaround was to use VSCode dev container.

not an option. seems like a very basic Python date conversion issue. I'll see if maybe Py 3.11 fixes that.

[Milad137]

My workaround was to use VSCode dev container.

not an option. seems like a very basic Python date conversion issue. I'll see if maybe Py 3.11 fixes that.

It's not a python issue, even if you wait til python 4 comes out still this issue won't be fixed.
if you're keen on fixing it, correct this file arrow_strptime and then recompile arrow.

[RandomFractals]

@Milad137 I am not so sure about that. I could not get those timestamps converted with Polars either:

https://twitter.com/TarasNovak/status/1584884282518306817

Probably b/c Polars uses arrow a lot under the hood too. 😄