Scenario

A JSON file contains nested user data that needs to be converted to a flat CSV format for analysis. Nested objects should be flattened using dot-notation column names.

Task

Write a Python script at /home/interview/flatten_json.py that reads /home/interview/users.json, flattens the nested structure using dot-notation for column names, converts arrays to comma-separated strings, and saves the result to /home/interview/flat_data.csv.

Example

Input JSON:

{
  "id": 1,
  "name": "John Doe",
  "contact": {
    "email": "[email protected]",
    "phone": "555-1234"
  },
  "hobbies": ["reading", "gaming"]
}

Expected output CSV columns:

id,name,contact.email,contact.phone,hobbies
1,John Doe,[email protected],555-1234,"reading,gaming"

Step 1: Examine the JSON structure

head -n 30 /home/interview/users.json

Review the nested structure to understand the levels of nesting and data types.

Step 2: Create the Python script

nano /home/interview/flatten_json.py

Write a script to flatten nested JSON and convert to CSV:

import json
import pandas as pd
from pandas import json_normalize

# Read JSON file
with open('/home/interview/users.json', 'r') as f:
    data = json.load(f)

# Flatten nested JSON using json_normalize
df = json_normalize(data, sep='.')

# Convert array columns to comma-separated strings
for col in df.columns:
    if df[col].dtype == 'object':
        # Check if column contains lists
        if isinstance(df[col].iloc[0], list):
            df[col] = df[col].apply(lambda x: ','.join(map(str, x)) if isinstance(x, list) else x)

# Save to CSV
df.to_csv('/home/interview/flat_data.csv', index=False)

print(f"Flattened {len(df)} records to CSV with {len(df.columns)} columns")

The json_normalize() function automatically flattens nested objects using dot-notation. Arrays are then converted to comma-separated strings.

Step 3: Run the script

python3 /home/interview/flatten_json.py

Step 4: Verify the output

head /home/interview/flat_data.csv

Should show flattened columns with dot-notation (e.g., contact.email, contact.address.city, metadata.settings.theme) and arrays as comma-separated values.