ENH: implement timeszones support for read_json(orient='table') and astype() from 'object'
State: closed

- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

Currently timezones raise a `NotImplementedError` when using the `read_json(orient='table')` method.

This PR aims to fix what I believe is a fairly common request (numerous workarounds and questions exist on StackOverflow).

The PR aims to reconstitute DataFrames via json columns with timezones, Index with timezones or MultiIndex with timezones and/or combinations.

Labels:
name: IO JSON
description: read_json, to_json, json_normalize
name: Timezones
description: Timezone data dtype

Comments:
Hello @attack68! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:











There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2020-10-31 19:15:33 UTC
I have retried the merge, but I agree my changes do not seem to be the factor.
@attack68 could you merge master one more time? I've tried to restart that failing built multiple times but for some reason its still segfaulting 
There was a comment referencing a github issue that discussed a strange seg fault in python 3.7.0. The solution was to patch out the test. I have tried the same solution this time...
cc @jbrockmendel 
the tz change needs review itself, this needs to be a separate PR. don't couple changes like this directly in the same PR, makes for harder review.  I guess since you did it already its ok. Though this may block the json changes (which i am not sure actually need this tz change). So splitting might be a good option.
> the tz change needs review itself, this needs to be a separate PR. don't couple changes like this directly in the same PR, makes for harder review. I guess since you did it already its ok. Though this may block the json changes (which i am not sure actually need this tz change). So splitting might be a good option.

This PR originally started as just a json fix but @mroeschke suggested the better and more general fix was via astype(), so it just evolved and was re-written. The JSON part was left in since it was a descendant and the tests would have needed to be corrected anyway.

I'll look at the other comments
@attack68 ok can you run timezone benchmarks and post here.
Sure, are there some standard commands to execute (can you advise), or do you mean basic info?

FWIW

```
def astype(df):
    df.astype({'tz': 'datetime64[ns, Europe/Berlin]'})
    
def convert(df):
    df['tz'].dt.tz_convert('Europe/Berlin')

val = {"tz": pd.date_range("2020-08-30", freq="d", periods=2, tz="Europe/London")}
df = pd.DataFrame(val)
df_obj = df.astype({'tz': 'object'})
```

>%timeit astype(df)
>633 µs ± 11.5 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

>timeit convert(df)
>113 µs ± 3.9 µs per loop (mean ± std. dev. of 7 runs, 10000 loops each)

>%timeit astype(df_obj)
>759 µs ± 9.22 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)
https://pandas.pydata.org/docs/development/contributing.html?highlight=asv#running-the-performance-test-suite

you need to compare vs the prior version which is what asv does

run all timezone 
Are these the right bechmarks: 

    >>> asv continuous -f 1.1 upstream/master HEAD -b ^tslibs
      
```
 before              after          ratio
     [5a75b1d8]       [5a077367]
     <master>         <enh_timezones_json>
+     3.59±0.01μs       4.28±0.3μs     1.19  tslibs.period.TimeDT64ArrToPeriodArr.time_dt64arr_to_periodarr(1, 4000, tzlocal())
+      20.5±0.3μs       23.6±0.6μs     1.15  tslibs.timestamp.TimestampOps.time_normalize(datetime.timezone(datetime.timedelta(seconds=3600)))
-      10.3±0.6μs       9.31±0.2μs     0.91  tslibs.timestamp.TimestampProperties.time_month_name(tzfile('/usr/share/zoneinfo/US/Central'), 'B')
-     4.29±0.03μs      3.89±0.07μs     0.91  tslibs.fields.TimeGetTimedeltaField.time_get_timedelta_field(100, 'microseconds')
-      3.86±0.1μs       3.49±0.1μs     0.91  tslibs.period.TimeDT64ArrToPeriodArr.time_dt64arr_to_periodarr(0, 9000, tzlocal())
-      8.02±0.5μs       7.26±0.2μs     0.91  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(100, 'start', 'year', 'B', 12)
-      3.92±0.2μs      3.55±0.08μs     0.91  tslibs.period.TimeDT64ArrToPeriodArr.time_dt64arr_to_periodarr(0, 6000, tzlocal())
-      11.5±0.3μs      10.5±0.06μs     0.91  tslibs.period.TimeDT64ArrToPeriodArr.time_dt64arr_to_periodarr(0, 2000, datetime.timezone(datetime.timedelta(seconds=3600)))
-      9.34±0.3μs       8.46±0.5μs     0.91  tslibs.tslib.TimeIntsToPydatetime.time_ints_to_pydatetime('timestamp', 0, <DstTzInfo 'US/Pacific' LMT-1 day, 16:07:00 STD>)
-        35.8±1μs       32.4±0.4μs     0.90  tslibs.tz_convert.TimeTZConvert.time_tz_localize_to_utc(100, datetime.timezone(datetime.timedelta(seconds=3600)))
-     4.49±0.05μs       4.06±0.2μs     0.90  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(0, 'start', 'month', None, 3)
-      11.5±0.4μs      10.4±0.07μs     0.90  tslibs.tz_convert.TimeTZConvert.time_tz_convert_from_utc(1, tzfile('/usr/share/zoneinfo/Asia/Tokyo'))
-      3.32±0.2μs      3.00±0.05μs     0.90  tslibs.fields.TimeGetTimedeltaField.time_get_timedelta_field(0, 'ms')
-      13.8±0.3μs       12.4±0.1μs     0.90  tslibs.offsets.OffestDatetimeArithmetic.time_subtract_10(<BusinessMonthEnd>)
-      4.53±0.1μs       4.09±0.1μs     0.90  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(1, 'start', 'year', 'QS', 3)
-      3.57±0.1μs      3.22±0.04μs     0.90  tslibs.fields.TimeGetDateField.time_get_date_field(0, 'q')
-      4.63±0.2μs       4.16±0.1μs     0.90  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(0, 'end', 'quarter', 'B', 5)
-     4.30±0.04μs      3.86±0.02μs     0.90  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(1, 'end', 'year', None, 12)
-      9.46±0.5μs       8.49±0.2μs     0.90  tslibs.period.TimeDT64ArrToPeriodArr.time_dt64arr_to_periodarr(1, 2000, <DstTzInfo 'US/Pacific' LMT-1 day, 16:07:00 STD>)
-      4.43±0.2μs      3.97±0.09μs     0.90  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(0, 'start', 'year', 'QS', 5)
-     4.43±0.03μs       3.96±0.1μs     0.89  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(1, 'start', 'year', 'B', 12)
-      4.57±0.1μs      4.09±0.05μs     0.89  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(1, 'end', 'year', 'QS', 3)
-      7.01±0.2μs       6.26±0.1μs     0.89  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(100, 'end', 'year', None, 3)
-     3.46±0.03μs      3.09±0.05μs     0.89  tslibs.fields.TimeGetTimedeltaField.time_get_timedelta_field(0, 'microseconds')
-      8.60±0.1μs      7.66±0.05μs     0.89  tslibs.timestamp.TimestampProperties.time_is_month_start(<DstTzInfo 'Europe/Amsterdam' LMT+0:20:00 STD>, 'B')
-      10.3±0.3μs       9.21±0.2μs     0.89  tslibs.period.TimeDT64ArrToPeriodArr.time_dt64arr_to_periodarr(1, 7000, tzfile('/usr/share/zoneinfo/Asia/Tokyo'))
-      3.10±0.1μs      2.76±0.02μs     0.89  tslibs.timestamp.TimestampOps.time_tz_localize(None)
-         307±5ns          273±9ns     0.89  tslibs.timestamp.TimestampProperties.time_microsecond(<DstTzInfo 'Europe/Amsterdam' LMT+0:20:00 STD>, None)
-      4.18±0.1μs       3.71±0.2μs     0.89  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(1, 'end', 'quarter', None, 5)
-        471±50ns          418±5ns     0.89  tslibs.period.PeriodProperties.time_property('M', 'minute')
-      7.06±0.1μs       6.25±0.1μs     0.89  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(100, 'start', 'year', None, 12)
-      9.30±0.4μs      8.24±0.07μs     0.89  tslibs.period.TimeDT64ArrToPeriodArr.time_dt64arr_to_periodarr(0, 7000, <DstTzInfo 'US/Pacific' LMT-1 day, 16:07:00 STD>)
-      3.90±0.2μs      3.46±0.03μs     0.89  tslibs.period.TimeDT64ArrToPeriodArr.time_dt64arr_to_periodarr(0, 4006, tzlocal())
-     3.46±0.06μs      3.06±0.05μs     0.89  tslibs.fields.TimeGetDateField.time_get_date_field(0, 's')
-      3.30±0.2μs      2.92±0.05μs     0.88  tslibs.fields.TimeGetTimedeltaField.time_get_timedelta_field(1, 'seconds')
-     3.33±0.03μs      2.93±0.02μs     0.88  tslibs.tz_convert.TimeTZConvert.time_tz_convert_from_utc(0, <DstTzInfo 'US/Pacific' LMT-1 day, 16:07:00 STD>)
-      8.66±0.6μs       7.63±0.1μs     0.88  tslibs.timestamp.TimestampProperties.time_is_month_end(tzfile('/usr/share/zoneinfo/US/Central'), 'B')
-      4.64±0.1μs       4.09±0.1μs     0.88  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(0, 'start', 'quarter', 'B', 5)
-     1.76±0.07ms      1.55±0.01ms     0.88  tslibs.tz_convert.TimeTZConvert.time_tz_convert_from_utc(1000000, datetime.timezone.utc)
-      4.28±0.4μs      3.77±0.04μs     0.88  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(1, 'start', 'quarter', 'QS', 5)
-      3.81±0.2μs       3.35±0.1μs     0.88  tslibs.fields.TimeGetDateField.time_get_date_field(1, 'ns')
-      9.03±0.5μs       7.93±0.2μs     0.88  tslibs.timestamp.TimestampProperties.time_is_month_start(tzlocal(), 'B')
-      10.2±0.2μs       8.91±0.2μs     0.88  tslibs.tslib.TimeIntsToPydatetime.time_ints_to_pydatetime('datetime', 1, <DstTzInfo 'US/Pacific' LMT-1 day, 16:07:00 STD>)
-      4.62±0.1μs       4.04±0.1μs     0.88  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(0, 'start', 'quarter', 'QS', 12)
-      3.39±0.1μs      2.97±0.05μs     0.88  tslibs.fields.TimeGetTimedeltaField.time_get_timedelta_field(0, 's')
-      11.5±0.8μs       10.1±0.1μs     0.87  tslibs.tz_convert.TimeTZConvert.time_tz_convert_from_utc(1, <DstTzInfo 'US/Pacific' LMT-1 day, 16:07:00 STD>)
-     3.87±0.08μs      3.37±0.04μs     0.87  tslibs.period.TimeDT64ArrToPeriodArr.time_dt64arr_to_periodarr(1, 2000, None)
-        96.0±4μs       83.6±0.5μs     0.87  tslibs.offsets.OffestDatetimeArithmetic.time_add_10(<CustomBusinessMonthEnd> (0))
-      3.49±0.4μs      3.04±0.07μs     0.87  tslibs.tslib.TimeIntsToPydatetime.time_ints_to_pydatetime('time', 0, datetime.timezone.utc)
-        34.1±3μs       29.6±0.2μs     0.87  tslibs.tz_convert.TimeTZConvert.time_tz_localize_to_utc(1, datetime.timezone(datetime.timedelta(seconds=3600)))
-     4.60±0.06μs      3.99±0.04μs     0.87  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(1, 'start', 'month', 'B', 12)
-      8.54±0.4μs       7.33±0.2μs     0.86  tslibs.timestamp.TimestampProperties.time_is_quarter_end(<UTC>, 'B')
-      4.12±0.4μs      3.53±0.08μs     0.86  tslibs.period.TimeDT64ArrToPeriodArr.time_dt64arr_to_periodarr(1, 1011, tzlocal())
-     4.00±0.09μs      3.42±0.05μs     0.86  tslibs.tslib.TimeIntsToPydatetime.time_ints_to_pydatetime('time', 1, None)
-     4.04±0.09μs       3.44±0.2μs     0.85  tslibs.tslib.TimeIntsToPydatetime.time_ints_to_pydatetime('date', 1, tzlocal())
-      9.42±0.5μs       8.02±0.5μs     0.85  tslibs.timestamp.TimestampProperties.time_is_month_start(tzfile('/usr/share/zoneinfo/US/Central'), 'B')
-      4.40±0.2μs       3.74±0.1μs     0.85  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(1, 'start', 'month', None, 3)
-      4.12±0.3μs      3.42±0.09μs     0.83  tslibs.period.TimePeriodArrToDT64Arr.time_periodarray_to_dt64arr(0, 2000)
-      10.6±0.3μs       8.69±0.2μs     0.82  tslibs.timestamp.TimestampProperties.time_weekday_name(tzutc(), 'B')
-      4.73±0.2μs       3.88±0.1μs     0.82  tslibs.fields.TimeGetStartEndField.time_get_start_end_field(1, 'start', 'month', 'B', 5)
-      4.03±0.4μs      3.25±0.07μs     0.81  tslibs.period.TimeDT64ArrToPeriodArr.time_dt64arr_to_periodarr(1, 9000, datetime.timezone.utc)
-      1.19±0.3μs          946±4ns     0.80  tslibs.tz_convert.TimeTZConvert.time_tz_localize_to_utc(0, datetime.timezone.utc)
```

SOME BENCHMARKS HAVE CHANGED SIGNIFICANTLY.
PERFORMANCE DECREASED.

cc @jbrockmendel @WillAyd  ok here?
@jreback pinging.. this is green except for unrelated test failure in 'pandas/tests/plotting/test_frame.py' on py38_np18. 
merged again and all green now
@jreback @jbrockmendel I believe this is in a state where can go in now..
thanks @attack68