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

datevalues02015-02-05  344.33602015-02-05  306.05402015-02-05  269.92202015-02-05  233.98402015-02-05  198.633........22015-02-03  514.64922015-02-03  471.87522015-02-03  437.30422015-02-03  402.53922015-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.