How to turn API JSON into a pandas DataFrame

Data APIs almost always return JSON with a nested structure — dictionaries inside dictionaries. That format is great for moving data around, but poor for analysis: to filter, group or chart your data you need a flat table. Turning an API's JSON into a pandas DataFrame is exactly that step, and the pandas.json_normalize function does it in just a few lines. Here is how, using a real public API as the example.

Prerequisites

• Python 3.9 or later installed.
• The pandas and requests libraries (install with pip install pandas requests).
• Basic Python knowledge: variables, lists and dictionaries.
• An internet connection to call the example public API.

Step 1: Get the data from the API

As an example we use a free public API that returns a list of users, each one with nested address and company fields. We make the request with requests, check that it succeeded with raise_for_status() and turn the response into Python objects with .json().

import requests
import pandas as pd

url = "https://jsonplaceholder.typicode.com/users"
resposta = requests.get(url, timeout=10)
resposta.raise_for_status()  # lança um erro se o pedido falhar
dados = resposta.json()

print(type(dados), len(dados))
print(dados[0])

The dados variable is a list of dictionaries. Notice that fields such as address and company themselves contain more dictionaries — that is the nested structure we will flatten in the next step.

Step 2: Turn the JSON into a DataFrame

The pandas.json_normalize function walks through each record and creates a column for every field, including the nested ones. By default it joins the levels with a dot — for example, the city field inside address becomes address.city.

df = pd.json_normalize(dados)

print(df.shape)
print(df.columns.tolist())

The result has columns such as id, name, address.city, address.geo.lat and company.name. In a single step, the nested JSON became a flat table with one row per user.

Tip: json_normalize automatically flattens every level of dictionaries, no matter how deep. You do not need to walk through the JSON by hand.

Step 3: Cleaner column names

The dot in the names works, but it can cause errors when you write code like df.address.city. With the sep parameter you choose another separator, such as the underscore, and then keep only the columns that matter for your analysis.

df = pd.json_normalize(dados, sep="_")

colunas = ["id", "name", "email", "address_city", "company_name"]
print(df[colunas].head())

Now the columns are named address_city or company_name, far easier to use in the rest of your work.

Step 4: Flatten a list inside the JSON

Many APIs do not return a simple list: they return an object with some metadata and the list of records inside it. In those cases, point to the list with record_path and name the top-level fields you want to keep on each row with meta.

resposta_api = {
    "empresa": "bConcepts",
    "pais": "PT",
    "clientes": [
        {"id": 1, "nome": "Ana", "cidade": "Porto"},
        {"id": 2, "nome": "Bruno", "cidade": "Lisboa"},
    ],
}

df_clientes = pd.json_normalize(
    resposta_api,
    record_path="clientes",
    meta=["empresa", "pais"],
)
print(df_clientes)

The result has one row per client, and the empresa and pais columns repeat on every row. That is how you combine detailed records with the context information that came at the top of the response.

Check the result

To confirm the conversion went well, check the shape of the table, peek at the first rows and make sure no dictionary was left to flatten:

print(df.shape)   # (linhas, colunas)
print(df.head())

# Procurar colunas que ainda contenham dicionarios:
aninhadas = [c for c in df.columns if df[c].apply(lambda v: isinstance(v, dict)).any()]
print("Colunas ainda aninhadas:", aninhadas)

If the aninhadas list comes back empty, every field was flattened and the DataFrame is ready for analysis.

Conclusion

In just a few lines you turned an API's JSON into a ready-to-use table: you fetched the data, flattened the nested fields with json_normalize and tidied up the column names. From here you can save the result with df.to_csv("dados.csv", index=False), load it into a database or combine it with pagination to bring in every page at once. Which API will you need to turn into a DataFrame next?