python - Pandas json_normalize with timestamps as keys

Question

I try to read this JSON data

{
    "values": [
        {
            "1510122047": [
                35.7,
                256
            ]
        },
        {
            "1510125000": [
                41.7,
                7
            ]
        },
        {
            "1510129000": [
                31.7,
                0
            ]
        }
    ]
}

and normalize it into a pandas data frame of this format:

I tried it with json_normalize but I was not able to get the result I need. Here is what I tried: But it's not quite efficient. I would like to find a solution that works with pandas' built in functions to do this. I'd appreciate ideas!

import pandas
import json

s = """
{"values": [
            {
              "1510122047": [35.7, 256]
            },
            {
              "1510125000": [41.7, 7]
            },
            {
              "1510129000": [31.7, 0]
            }
          ]}
"""

data = json.loads(s)

normalized_data = []
for value in data['values']:
    timestamp = list(value.keys())[0]
    normalized_data.append({'timestamp':timestamp, 'value_1': value[timestamp][0], 'value_2': value[timestamp][1]})

pandas.DataFrame(normalized_data)

Thanks

EDIT

Thanks for your suggestions. Unfortunately none where faster than the solution of this OP. Here is what I did to generate a bigger payload and test for speed: I guess it's the nature of JSON to be slowly for this application.

import pandas
import json
import time

s1 = """{
              "1510122047": [35.7, 256]
            },
            {
              "1510125000": [41.7, 7]
            },
            {
              "1510129000": [31.7, 0]
            }"""

s = """
{"values": [
            {
              "1510122047": [35.7, 256]
            },
            {
              "1510125000": [41.7, 7]
            },
            {
              "1510129000": [31.7, 0]
            },
""" + ",".join([s1]*1000000) + "]}"

data = json.loads(s)

tic = time.time()

normalized_data = []
for value in data['values']:
    timestamp = list(value.keys())[0]
    normalized_data.append({'timestamp':timestamp, 'value_1': value[timestamp][0], 'value_2': value[timestamp][1]})

print(time.time() - tic)
pandas.DataFrame(normalized_data)

question from:https://stackoverflow.com/questions/65672141/pandas-json-normalize-with-timestamps-as-keys

Answer 1

This is one approach using a nested comprehension

Ex:

df= pd.DataFrame([[key] + value for item in data['values'] 
                            for key, value in item.items()
                 ], columns=["Timestamp", "Val_1", "Val_2"])
print(df)

Output:

    Timestamp  Val_1  Val_2
0  1510122047   35.7    256
1  1510125000   41.7      7
2  1510129000   31.7      0