Realtime JSON data to panda dataframe-CodePudding

I have been trying to normalize my JSON file which I retrieved from Firebase Realtime Database, and turn it into a python panda data-frame but I keep getting everything as a row.

my JSON file is structured as following:

{
    “Device 1” {
        “ID-1”{
            “key”:  value
            “time”: xxx
        }
        “ID-2”{
            “key”:  value
            “time”: xxx
        }
        “ID-3”{
            “key”:  value
            “time”: xxx
        }
        “ID-4”{
            “key”:  value
            “time”: xxx
        }
    }
    “Device 2" {
        “ID-1”{
            “key”:  value
            “key”:  value
            “time”: xxx
        }
        “ID-2”{
            “key”:  value
            “key”:  value
            “time”: xxx
        }
        “ID-3”{
            “key”:  value
            “key”:  value
            “time”: xxx
        }
        “ID-4”{
            “key”:  value
            “key”:  value
            “time”: xxx
        }
    }
    “Device 3" {
        “ID-1”{
            “key”:  value
            “key”:  value
            “time”: xxx
        }
        “ID-2”{
            “key”:  value
            “key”:  value
            “time”: xxx
        }
        “ID-3”{
            “key”:  value
            “key”:  value
            “time”: xxx
        }
        “ID-4”{
            “key”:  value
            “key”:  value
            “time”: xxx
        }
    }
}

what I am trying to do is have each device in a separate table, with the ID as a column alongside the values listed below it, like this:

Device 1 table:

ID	key 1	key 2
id value	value	value

Device 2 table:

ID	key 1	key 2	key 3
id value	value	value	value

Device 3 table:

ID	key 1	key 2	key 3
id value	value	value	value

CodePudding user response：

Here's how I would do it. Since each Device is a table, I'll split them into individual dataframes. We can always unite them later if need be.

I'll suppose you have your JSON data in a dict called devices

import pandas as pd

dataframes = []
for device, id_data in devices.items():
  rows = []
  for row_id, values in id_data.items():
    values["id"] = row_id
    rows.append(values)
  dataframes.append(pd.DataFrame(rows))