How Do I Get This JSON Time Series Data Into A Pandas Dataframe?

I have time series data from an API I'd like to get in to a python pandas dataframe. How would you do this? The data look like this: [{'id': 38421212541, 'sensor_id': 12944473,

Solution 1:

Quick Answer

Assuming you have the latest version of pandas.

data = [{'date': x['date'], 'values': eval(x['values'])} for x in your_json_dict]
pd.DataFrame(data).explode('values')

results in

          date   values
0   2015-02-05  344.336
0   2015-02-05  306.054
0   2015-02-05  269.922
0   2015-02-05  233.984
0   2015-02-05  198.633
..         ...      ...
2   2015-02-03  514.649
2   2015-02-03  471.875
2   2015-02-03  437.304
2   2015-02-03  402.539
2   2015-02-03  369.336

Explanation

[{'date': x['date'], 'values': eval(x['values'])} for x in your_json_dict]

This is a list comprehension. It creates a new list of dictionaries where each dictionary has the keys 'date' and 'values'. It also converts 'values' from a string to a list of numbers.

pd.DataFrame(data).explode('values')

pandas is perfectly fine with accepting a list of dictionaries. The explode function is a new feature in version 0.25 that expands a column that is a list into rows for each element of that list.