input_date_format for importing data to PST

[BorderCollieLover]

The code I grabbed from the PR didn't work. The error message is:

Traceback (most recent call last):
File "/mnt/sda1/pysystemtrade/venv/3.10.13/lib/python3.10/site-packages/pysystemtrade-1.80-py3.10.egg/syscore/pandas/pdutils.py", line 113, in pd_readcsv
df = add_datetime_index(
File "/mnt/sda1/pysystemtrade/venv/3.10.13/lib/python3.10/site-packages/pysystemtrade-1.80-py3.10.egg/syscore/pandas/pdutils.py", line 140, in add_datetime_index
df.index = pd.to_datetime(date_index, format=date_format).values
File "/mnt/sda1/pysystemtrade/venv/3.10.13/lib/python3.10/site-packages/pandas/core/tools/datetimes.py", line 1112, in to_datetime
values = convert_listlike(arg._values, format)
File "/mnt/sda1/pysystemtrade/venv/3.10.13/lib/python3.10/site-packages/pandas/core/tools/datetimes.py", line 488, in _convert_listlike_datetimes
return _array_strptime_with_fallback(arg, name, utc, format, exact, errors)
File "/mnt/sda1/pysystemtrade/venv/3.10.13/lib/python3.10/site-packages/pandas/core/tools/datetimes.py", line 519, in _array_strptime_with_fallback
result, timezones = array_strptime(arg, fmt, exact=exact, errors=errors, utc=utc)
File "strptime.pyx", line 534, in pandas._libs.tslibs.strptime.array_strptime
File "strptime.pyx", line 355, in pandas._libs.tslibs.strptime.array_strptime
ValueError: time data "2011-02-01T10:00:00" doesn't match format "%Y-%m-%dT%H:%M:%S%z", at position 0. You might want to try:
- passing format if your strings have a consistent format;
- passing format='ISO8601' if your strings are all ISO8601 but not necessarily in exactly the same format;
- passing format='mixed', and the format will be inferred for each element individually. You might want to use dayfirst alongside this.

The issue seems to have been caused by the pd_readcsv function in syscore.pandas.pdutils.

Specifically, when PST tries to convert the Time column to a datetime index, it truncates the input to a string of length 19 by default. This truncation would result in a conflict with the input datetime format string: input_date_format="%Y-%m-%dT%H:%M:%S%z" and cause the above error.

My solution is to remove the timezone modifier %z in input_date_format, so it matches the truncated datetime string. The code then runs smoothly.

Maybe it's just me but hopefully it will be helpful in case people run into similar issues.

Again, many thanks to Andy for sharing his excellent work,

[BorderCollieLover]

Of course remove %z only works if all that data -- bcutils and PST -- are using UTC time.

[bug-or-feature]

Weird. I don't see any errors, and I'm not doing anything out of the ordinary. The original price files downloaded from Barchart by bc-utils look like:

Time,Open,High,Low,Close,Volume
1993-07-05T05:00:00+0000,17.21,17.3,17.2,17.27,0
...

Are yours the same?

What version of pandas do you use?

[BorderCollieLover]

My data is in the same format as yours.
My pandas is 2.1.3

[bug-or-feature]

Hmm I'm using pandas 1.5.3. Maybe more recent pandas is more strict about datetime format

[BorderCollieLover]

Could be, thanks for looking into it.

[davewsail]

I know this thread is a year old now but I am new to PST and bc-utils and hit this issue yesterday also with newer pandas. I simply changed the code to the following to fix it:

BARCHART_CONFIG = ConfigCsvFuturesPrices(
input_date_index_name="Time",
input_skiprows=0,
input_skipfooter=0,
#input_date_format="%Y-%m-%dT%H:%M:%S%z",
input_date_format='ISO8601',
input_column_mapping=dict(
OPEN="Open", HIGH="High", LOW="Low", FINAL="Close", VOLUME="Volume"
),
)

[bug-or-feature]

the lib was updated to support newer pandas a long time ago