Join nested list to ID value

user_1709629 0
Viewed 46

I retrieve data from my DB for a Python app and it comes in the following format (as a list, tbl):

[
   {
      "id": "rec2fiwnTQewTv9HC",
      "createdTime": "2022-06-27T08:25:47.000Z",
      "fields": {
         "Num": 19,
         "latitude": 31.101405,
         "longitude": 36.391831,
         "State": 2,
         "Label": "xyz",
         "Red": 0,
         "Green": 255,
         "Blue": 0
      }
   },
   {
      "id": "rec4y7vhgZVDHrhrQ",
      "createdTime": "2022-06-27T08:25:47.000Z",
      "fields": {
         "Num": 30,
         "latitude": 31.101405,
         "longitude": 36.391831,
         "State": 2,
         "Label": "abc",
         "Red": 0,
         "Green": 255,
         "Blue": 0
      }
   }
]

I can retrieve the values in the fields nested list by doing this:

pd.DataFrame([d['fields'] for d in tbl])

I would like to add the id field to each row of the dataframe but I can't figure out how to do this.

dataframe list python
1 Answers

Try:

data = [
    {
        "id": "rec2fiwnTQewTv9HC",
        "createdTime": "2022-06-27T08:25:47.000Z",
        "fields": {
            "Num": 19,
            "latitude": 31.101405,
            "longitude": 36.391831,
            "State": 2,
            "Label": "xyz",
            "Red": 0,
            "Green": 255,
            "Blue": 0,
        },
    },
    {
        "id": "rec4y7vhgZVDHrhrQ",
        "createdTime": "2022-06-27T08:25:47.000Z",
        "fields": {
            "Num": 30,
            "latitude": 31.101405,
            "longitude": 36.391831,
            "State": 2,
            "Label": "abc",
            "Red": 0,
            "Green": 255,
            "Blue": 0,
        },
    },
]


df = pd.DataFrame([{"id": d["id"], **d["fields"]} for d in data])
print(df)

Prints:

                  id  Num   latitude  longitude  State Label  Red  Green  Blue
0  rec2fiwnTQewTv9HC   19  31.101405  36.391831      2   xyz    0    255     0
1  rec4y7vhgZVDHrhrQ   30  31.101405  36.391831      2   abc    0    255     0
user_100359850
Related