How to extract and transform JSON columns in Python for data

João Barros 30 de October de 2026 5 min read

Extracting and transforming columns containing JSON is a common task when working with APIs, logs or dumps that mix text and structure. Knowing how to normalize nested fields in a pandas DataFrame saves time and enables more reliable analysis and modeling. Additionally, when JSONs are embedded as strings in a column, it is important to systematize these steps to ensure reproducibility and performance, especially when processing thousands to millions of records.

Prerequisites

Python 3.8+ installed
pandas and json installed (pip install pandas)
Basic knowledge of DataFrame and Python dictionaries

Step 1: Identify the JSON column and the format

First it is important to see how the JSONs are stored: JSON strings, already converted dictionaries or nested lists. Open a few rows and check types and edge examples (rows with null values or invalid JSON). For larger datasets, sample 100–1,000 rows to understand variability. Deciding the correct strategy (json.loads, ast.literal_eval or leaving as dict) depends on the format: if the column is object type with strings, you need to deserialize; if it is already dict, you can go straight to normalization.

import pandas as pd
import json

# Exemplo de DataFrame com coluna JSON como string
df = pd.DataFrame({
    'id': [1, 2, 3],
    'payload': [
        '{"user": {"name": "Ana", "age": 30}, "tags": ["a","b"]}',
        '{"user": {"name": "Bruno", "age": 25}, "tags": ["b"]}',
        '{"user": {"name": "Carla", "age": null}, "tags": []}'
    ]
})

# Verificar primeiras linhas
print(df.head())

Step 2: Convert JSON strings to dictionaries

If the column is text, convert to dict with json.loads. Handle common errors: null values, invalid JSON or unexpected encoding (e.g., single quotes). For production, create a robust function that logs failures and returns None on error. In larger scenarios (10k+ rows), process in batches to reduce memory and record the deserialization success rate (e.g.: 99% valid, 1% invalid).

def safe_load(s):
    if pd.isna(s):
        return None
    try:
        return json.loads(s)
    except Exception:
        # Pode registar a linha problemática aqui
        return None

# Aplicar a conversão
df['payload_json'] = df['payload'].apply(safe_load)
print(df['payload_json'])

Step 3: Normalize nested fields with pandas.json_normalize

pandas.json_normalize is ideal to expand dictionaries into flat columns. For structures with lists of objects you can use record_path and meta to control the expansion. For medium datasets (e.g. 50k records) direct normalization creates a DataFrame with columns like user.name and user.age; check dtypes and rename columns as needed. If you expect an average of 3 tags per record, exploding will multiply the number of rows — plan memory and indexes.

from pandas import json_normalize

# Normalizar a coluna de dicionários
normalized = json_normalize(df['payload_json'])
# Se o resultado tiver colunas como 'user.name', renomear ou juntar ao original
print(normalized)

# Juntar ao DataFrame original
df2 = pd.concat([df.drop(columns=['payload_json']), normalized], axis=1)
print(df2)

Step 4: Handle lists and optional fields

Lists (e.g.: tags) can be converted to a comma-separated string, counted or exploded into multiple rows. Choose according to the analysis: if you need metrics per user, count; if you need frequency analysis, explode. Missing fields (None) should receive default values or coherent types to avoid issues in aggregations (e.g., fill null age with the mean or with a sentinel like -1). For numeric columns, convert explicitly with astype or pd.to_numeric with errors='coerce'.

# Converter lista em string e criar contagem
df2['tags_str'] = df2['tags'].apply(lambda x: ','.join(x) if isinstance(x, list) else '')
df2['tags_count'] = df2['tags'].apply(lambda x: len(x) if isinstance(x, list) else 0)

# Tratar age nulo: preencher com média ou valor sentinel
df2['user.age'] = df2['user.age'].fillna(df2['user.age'].mean())
print(df2[['id','user.name','user.age','tags_str','tags_count']])

Step 5: Explode lists into multiple rows (when needed)

If you need one row per tag, use explode. This is useful for frequency analyses, maps or subsequent joins with reference tables. Note: exploding converts empty lists into NaN; fill or filter as needed. Practical examples: 3 records with an average of 2 tags → after explode you will have ~6 rows; in production, estimate the multiplication factor to predict final size.

# Explodir para uma linha por tag
exploded = df2.explode('tags')
# Se existirem entradas vazias, cuidado com NaN
exploded['tags'] = exploded['tags'].fillna('')
print(exploded[['id','user.name','tags']])

Verify the result

Confirm that the expected columns exist, dtypes are correct and counts match the original. Do simple checks: compare the sum of tags_count with the total number of tags before exploding, check percentage of null values per column and view samples of problematic rows. In pipelines, write unit tests that validate these invariants (e.g.: total_tags before == total_tags after).

# Verificações rápidas
print(df2.info())
print(df2[['id','user.name','user.age']].head())

# Conferir soma das tags_count com número total de tags antes de explodir
print('total tags (contagem):', df2['tags_count'].sum())
print('total tags (explodidas):', exploded['tags'].shape[0])

Conclusion

Extracting and transforming JSON columns in Python with pandas allows you to go from semi-structured data to tables ready for analysis or modeling. For production, integrate this process into an ETL, validate schemas with jsonschema, log parsing errors and use batch processing when dealing with large volumes. Practical tip: always keep a validation step and a few examples of real rows (10–20 representative lines) to avoid surprises in the data.