Nested JSON to CSV python pandas

Nested JSON objects have one or more levels of additional objects or arrays. CSV, on the other hand, is a flat structure with rows and columns. The goal is to "flatten" the JSON structure, converting nested elements into a format that can be represented in columns. We can use pandas and json to flatten nested JSON and export it to a CSV file.

Example Nested JSON

[
    {
        "name": "John Doe",
        "email": "johndoe@example.com",
        "location": {
            "city": "New York",
            "country": "USA"
        },
        "skills": ["Python", "Data Analysis"]
    },
    {
        "name": "Jane Doe",
        "email": "janedoe@example.com",
        "location": {
            "city": "San Francisco",
            "country": "USA"
        },
        "skills": ["JavaScript", "React"]
    }
]

Install Required Libraries

Ensure you have pandas installed. If not, you can install it using pip:

pip install pandas

Load and flatten JSON

First, load your JSON data. You can load it from a file or directly as a string (for this example, we'll assume it's loaded into a variable).

import json
from pandas import json_normalize

with open('data.json') as file:
    data = json.load(file)

data = json.loads(json_string)

# Use pandas to normalize the nested JSON structure
flat_data = json_normalize(data, sep='_')
print(flat_data)

The sep parameter specifies the separator to use when flattening the JSON. This example uses an underscore to separate nested fields (e.g., "location_city").

Convert to CSV

Finally, convert the flattened DataFrame to a CSV file.

flat_data.to_csv('output.csv', index=False)

For deeply nested structures or arrays, you might need to preprocess the data before flattening it or use a more complex approach with custom functions to handle the specific structure of your JSON.

Nested JSON to CSV with python pandas

Comments

More from this blog

Merging CSV files in command line

Change CSV file encoding

Advantages of CSV Files

AD import users from CSV

AD group members to CSV

Example Nested JSON

Install Required Libraries

Load and flatten JSON

Convert to CSV

Command Palette

Comments

More from this blog

Example Nested JSON

Install Required Libraries

Load and flatten JSON

Convert to CSV