I have a DataFrame I'd like to serialize to JSON, and be able to read it back in a DataFrame. There are 2 datetime64 columns, but one of them is being returned as an object. I'm also losing the timezone info, but I see from How do I keep the timezone of my index when serializing/deserializing a Pandas DataFrame using JSON, that I can't do that.
wxdata.info()
pd.read_json(wxdata.to_json(date_format='iso')).info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 9853 entries, 0 to 9852
Data columns (total 30 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 time_of_day 9853 non-null datetime64[ns, US/Eastern]
1 temp1 9853 non-null float64
2 wind_chill 9853 non-null float64
3 heat_index 9853 non-null float64
4 dew_point 9853 non-null float64
5 degree_day 9853 non-null float64
6 density_altitude 9853 non-null float64
7 wet_bulb_globe_temp 9853 non-null float64
8 adjusted_altitude 9853 non-null float64
9 SAE_correction_factor 9853 non-null float64
10 rel_humidity 9853 non-null int64
11 inst_wind_speed 9853 non-null float64
12 inst_wind_dir 9853 non-null float64
13 two_min_rolling_avg_wind_speed 9853 non-null float64
14 two_min_rolling_avg_wind_dir 9853 non-null float64
15 ten_min_rolling_avg_wind_speed 9853 non-null float64
16 ten_min_rolling_avg_wind_dir 9853 non-null float64
17 sixty_min_winddir_atpeak 9853 non-null int64
18 sixty_min_peak_windspeed 9853 non-null float64
19 ten_min_winddir_atpeak 9853 non-null int64
20 ten_min_peak_windspeed 9853 non-null float64
21 ten_min_wind_gust_time 9853 non-null datetime64[ns, US/Eastern]
22 rain_today 9853 non-null int64
23 rain_this_week 9853 non-null int64
24 rain_this_month 9853 non-null int64
25 rain_this_year 9853 non-null int64
26 rain_rate 9853 non-null int64
27 raw_barom_pressure 9853 non-null float64
28 barom_press 9853 non-null float64
29 solar_radiation 9853 non-null int64
dtypes: datetime64[ns, US/Eastern](2), float64(19), int64(9)
memory usage: 2.3 MB
<class 'pandas.core.frame.DataFrame'>
Int64Index: 9853 entries, 0 to 9852
Data columns (total 30 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 time_of_day 9853 non-null object
1 temp1 9853 non-null float64
2 wind_chill 9853 non-null float64
3 heat_index 9853 non-null float64
4 dew_point 9853 non-null float64
5 degree_day 9853 non-null float64
6 density_altitude 9853 non-null float64
7 wet_bulb_globe_temp 9853 non-null float64
8 adjusted_altitude 9853 non-null float64
9 SAE_correction_factor 9853 non-null float64
10 rel_humidity 9853 non-null int64
11 inst_wind_speed 9853 non-null float64
12 inst_wind_dir 9853 non-null float64
13 two_min_rolling_avg_wind_speed 9853 non-null float64
14 two_min_rolling_avg_wind_dir 9853 non-null float64
15 ten_min_rolling_avg_wind_speed 9853 non-null float64
16 ten_min_rolling_avg_wind_dir 9853 non-null float64
17 sixty_min_winddir_atpeak 9853 non-null int64
18 sixty_min_peak_windspeed 9853 non-null float64
19 ten_min_winddir_atpeak 9853 non-null int64
20 ten_min_peak_windspeed 9853 non-null float64
21 ten_min_wind_gust_time 9853 non-null datetime64[ns, UTC]
22 rain_today 9853 non-null int64
23 rain_this_week 9853 non-null int64
24 rain_this_month 9853 non-null int64
25 rain_this_year 9853 non-null int64
26 rain_rate 9853 non-null int64
27 raw_barom_pressure 9853 non-null float64
28 barom_press 9853 non-null float64
29 solar_radiation 9853 non-null int64
dtypes: datetime64[ns, UTC](1), float64(19), int64(9), object(1)
memory usage: 2.3+ MB
As you can see, the first datetime64 column returned as an object instead of a datetime64. Doing this without the date_format='iso' switch, 'time_of_day' returns as int64, instead of datetime64.
Thanks for any help.
That was it. I rename the 'time_of_day' column to 'timestamp', and both columns are now datetime64.
https://pandas.pydata.org/docs/user_guide/io.html#io-json-reader
Note
Large integer values may be converted to dates if convert_dates=True and the data and / or column labels appear ‘date-like’. The exact threshold depends on the date_unit specified. ‘date-like’ means that the column label meets one of the following criteria:
it ends with '_at'
it ends with '_time'
it begins with 'timestamp'
it is 'modified'
it is 'date'