Iso date timezone is not decoded in Series.read_json

[jbarotin]

Problem description

If I'm trying to load json data thought the Series.read_json considering the following command line :

pd.read_json('{"2019-01-01T11:00:00.000Z":88.352985054,"2019-01-01T12:00:00.000Z":90.091719896,"2019-01-01T13:00:00.000Z":15}', typ='series', orient='index').index

I've go the following output

DatetimeIndex(['2019-01-01 11:00:00',
               '2019-01-01 12:00:00', '2019-01-01 13:00:00'],
              dtype='datetime64[ns]', freq=None)

The timezone is contained in the 'Z' at the end of the timestamp string. It means that it's a UTC time. More information you can consult the wikipedia page for iso date https://fr.wikipedia.org/wiki/ISO_8601

Expected Output

UTC timezone should be mentionned :

DatetimeIndex(['2019-01-01 11:00:00',
               '2019-01-01 12:00:00', '2019-01-01 13:00:00'],
              dtype='datetime64[ns, UTC]', freq=None)

Output of pd.show_versions()

INSTALLED VERSIONS ------------------ commit: None python: 3.6.7.final.0 python-bits: 64 OS: Linux OS-release: 4.15.0-45-generic machine: x86_64 processor: x86_64 byteorder: little LC_ALL: None LANG: fr_FR.UTF-8 LOCALE: fr_FR.UTF-8

pandas: 0.24.1
pytest: None
pip: 19.0.3
setuptools: 40.6.2
Cython: None
numpy: 1.16.1
scipy: None
pyarrow: None
xarray: None
IPython: 7.2.0
sphinx: None
patsy: None
dateutil: 2.8.0
pytz: 2018.3
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 3.0.2
openpyxl: 2.6.0
xlrd: 1.2.0
xlwt: None
xlsxwriter: None
lxml.etree: None
bs4: 4.6.3
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

[paste the output of pd.show_versions() here below this line]

[jbarotin]

Here we go a workaround directly inspired from the Pandas source code

import pandas._libs.json as json
result = json.loads('{"2019-01-01T11:00:00.000Z":88.352985054,"2019-01-01T12:00:00.000Z":90.091719896,"2019-01-01T13:00:00.000Z":15}',
          dtype=None, numpy=True, labelled=True )
pd.Series(result[0], pd.DatetimeIndex(result[1])).index

That gave me the following output :

DatetimeIndex(['2019-01-01 11:00:00+00:00',
               '2019-01-01 12:00:00+00:00','2019-01-01 13:00:00+00:00'],
              dtype='datetime64[ns, UTC]', freq=None)

[mroeschke]

I believe this vendored cjson parser is doing the work, so I am guessing it is not aware of how to handle Z. https://github.com/pandas-dev/pandas/tree/master/pandas/_libs/src/ujson

The fix then potentially would be more involved, but investigations and PRs welcome!

[jbarotin]

json.loads did not seems to parse date type (with or without timezone)

I think, it should be handled in pd.Series(..)