CODEWITHSUNDEEP
pythonpandaspython-requests
Luiz
Luiz

Reputation: 361

How to get data from nested list in response.json()

There is a json response from an API request in the following schema:

[
  {
    "id": "1",
    "variable": "x",
    "unt": "%",
    "results": [
      {
        "classification": [
          {
            "id": "1",
            "name": "group",
            "category": {
              "555": "general"
            }
          }
        ],
        "series": [
          {
            "location": {
              "id": "1",
              "level": {
                "id": "n1",
                "name": "z"
              },
              "name": "z"
            },
            "serie": {
              "202001": "0.08",
              "202002": "0.48",
              "202003": "0.19"
            }
          }
        ]
      }
    ]
  }
]

I want to transform the data from the "serie" key into a pandas DataFrame.

I can do that explicitly:

content = val[0]["results"][0]["series"][0]["serie"]
df = pd.DataFrame(content.items())
df
        0     1
0  202001  0.08
1  202002  0.48
2  202003  0.19

But if there is more than one record, that would get only the data from the first element because of the positional arguments [0].

Is there a way to retrieve that data not considering the positional arguments?

Upvotes: 1

Views: 264

Answers (1)

Andrej Kesely
Andrej Kesely

Reputation: 195408

Try:

val = [
    {
        "id": "1",
        "variable": "x",
        "unt": "%",
        "results": [
            {
                "classification": [
                    {"id": "1", "name": "group", "category": {"555": "general"}}
                ],
                "series": [
                    {
                        "location": {
                            "id": "1",
                            "level": {"id": "n1", "name": "z"},
                            "name": "z",
                        },
                        "serie": {"202001": "0.08", "202002": "0.48", "202003": "0.19"},
                    }
                ],
            }
        ],
    },
    {
        "id": "2",
        "variable": "x",
        "unt": "%",
        "results": [
            {
                "classification": [
                    {"id": "1", "name": "group", "category": {"555": "general"}}
                ],
                "series": [
                    {
                        "location": {
                            "id": "1",
                            "level": {"id": "n1", "name": "z"},
                            "name": "z",
                        },
                        "serie": {"202001": "1.08", "202002": "1.48", "202003": "1.19"},
                    }
                ],
            }
        ],
    },
]

df = pd.DataFrame(
    [k, v]
    for i in val
    for ii in i["results"]
    for s in ii["series"]
    for k, v in s["serie"].items()
)
print(df)

Prints:

        0     1
0  202001  0.08
1  202002  0.48
2  202003  0.19
3  202001  1.08
4  202002  1.48
5  202003  1.19

Upvotes: 1

Related Questions