Skip to main content

Manage Raw JSON Lines - Pandas

Calculating read time…

Working with nested JSON data can be tricky! JSON often contains lists inside objects, objects inside lists, and multiple levels of nesting. This comprehensive tutorial shows you how to handle complex JSON structures in Pandas using lines=True, explode(), json_normalize(), and max_level parameter.

What is JSON Lines Format?

Before we dive in, let's understand JSON Lines (JSONL) format. Unlike regular JSON where everything is in one array, JSON Lines has one JSON object per line. This format is extremely popular for:

  • Streaming data (logs, events, API responses)
  • Large datasets (easier to process line-by-line)
  • Database exports
  • Machine learning training data

Regular JSON:

[
  {"name": "Alice", "age": 25},
  {"name": "Bob", "age": 30}
]

JSON Lines Format:

{"name": "Alice", "age": 25}
{"name": "Bob", "age": 30}

Sample Dataset - E-commerce Orders

Let's create a realistic e-commerce dataset with nested customer information and multiple items per order. This is what you'll commonly see in real-world applications!

import pandas as pd
import json

# JSON Lines format - one JSON object per line
json_lines = """
{"OrderID": 101, "Customer": {"Name": "Alice", "Country": "India"}, "Items": ["Keyboard", "Mouse"], "Total": 1200}
{"OrderID": 102, "Customer": {"Name": "Bob", "Country": "USA"}, "Items": ["Laptop"], "Total": 75000}
{"OrderID": 103, "Customer": {"Name": "Charlie", "Country": "UK"}, "Items": ["Monitor", "Mouse"], "Total": 15000}
"""

print("Raw JSON Lines Data:")
print(json_lines)

💡 Notice: Each line is a complete JSON object - no commas between lines, no square brackets wrapping everything!

1. Understanding lines=True Parameter

The lines=True parameter tells Pandas: "Hey, this is JSON Lines format, read each line as a separate JSON object!"

Why is this important?

  • Memory efficient: Processes one line at a time instead of loading entire JSON into memory
  • Faster: Can read huge files that won't fit in memory
  • Standard format: Many APIs and databases export in this format
  • Streaming friendly: Can process data as it arrives
# Read JSON Lines format
df_lines = pd.read_json(json_lines, lines=True)
print("DataFrame from lines=True:")
print(df_lines)
print("\nData types:")
print(df_lines.dtypes)

Output:

OrderID Customer Items Total
101 {'Name': 'Alice', 'Country': 'India'} ['Keyboard', 'Mouse'] 1200
102 {'Name': 'Bob', 'Country': 'USA'} ['Laptop'] 75000
103 {'Name': 'Charlie', 'Country': 'UK'} ['Monitor', 'Mouse'] 15000

⚠️ Notice the Problem: Customer is still a dictionary, and Items is still a list! We need to flatten these nested structures.

2. Understanding explode() Function

The explode() function is magic for lists! It takes a column containing lists and creates a new row for each item in the list.

Think of it like this: You have a shopping cart with multiple items. explode() creates a separate row for each item, duplicating all other information.

# Explode the Items column
df_explode = df_lines.explode('Items')
print("Exploded DataFrame with Items:")
print(df_explode)
print(f"\nOriginal rows: {len(df_lines)}")
print(f"After explode: {len(df_explode)}")
print(f"New rows created: {len(df_explode) - len(df_lines)}")

Output:

OrderID Customer Items Total
101 {'Name': 'Alice', 'Country': 'India'} Keyboard 1200
101 {'Name': 'Alice', 'Country': 'India'} Mouse 1200
102 {'Name': 'Bob', 'Country': 'USA'} Laptop 75000
103 {'Name': 'Charlie', 'Country': 'UK'} Monitor 15000
103 {'Name': 'Charlie', 'Country': 'UK'} Mouse 15000

Result: Alice's order with 2 items became 2 rows! Charlie's order with 2 items became 2 rows! Each item now has its own row.

Real-World Use Case: Analyze which individual products are most popular, calculate inventory needs per product, or create product-level reports!

3. Expanding Nested Dictionaries with apply(pd.Series)

Now we need to flatten the Customer dictionary into separate columns. The apply(pd.Series) technique converts dictionary keys into DataFrame columns!

How it works: Takes a column of dictionaries and spreads each key-value pair into its own column.

# Step-by-step approach:
# 1. Extract Customer column and convert to separate columns
customer_expanded = df_lines['Customer'].apply(pd.Series)
print("Customer column expanded:")
print(customer_expanded)

# 2. Drop original Customer column from main DataFrame
df_without_customer = df_lines.drop(['Customer'], axis=1)

# 3. Concatenate them side by side
df_customer_explode = pd.concat([df_without_customer, customer_expanded], axis=1)
print("\nDataFrame with Customer details expanded:")
print(df_customer_explode)

Output:

OrderID Items Total Name Country
101 ['Keyboard', 'Mouse'] 1200 Alice India
102 ['Laptop'] 75000 Bob USA
103 ['Monitor', 'Mouse'] 15000 Charlie UK

Success: Customer dictionary is now separate columns (Name and Country)!

💡 Understanding axis=1: In pd.concat(), axis=1 means "concatenate side by side" (add columns), while axis=0 would mean "stack on top" (add rows).

4. Complete Flattening - Explode Items Too!

Now let's combine everything: expand Customer AND explode Items to get a completely flat DataFrame!

# Now explode the Items column from our customer-expanded DataFrame
explode_items = df_customer_explode.explode('Items')
print("Final Exploded DataFrame with Items:")
print(explode_items)

print("\n Summary:")
print(f"Started with: {len(df_lines)} orders")
print(f"Ended with: {len(explode_items)} item-level records")
print(f"Columns before: {list(df_lines.columns)}")
print(f"Columns after: {list(explode_items.columns)}")

Final Output:

OrderID Items Total Name Country
101 Keyboard 1200 Alice India
101 Mouse 1200 Alice India
102 Laptop 75000 Bob USA
103 Monitor 15000 Charlie UK
103 Mouse 15000 Charlie UK

🎉 Perfect! Now we have a completely flat DataFrame where:

  • Each row represents one item from one order
  • Customer info (Name, Country) is in separate columns
  • No more nested dictionaries or lists!
  • Ready for analysis, filtering, grouping, etc.

5. Alternative Method - Using join()

There's another elegant way to achieve the same result using join(). This method is often cleaner and more readable!

# Read JSON Lines
df_lines = pd.read_json(json_lines, lines=True)

# Step 1: Explode Items first
df_explode = df_lines.explode('Items')

# Step 2: Join with expanded Customer column
df_flat = df_explode.join(df_explode['Customer'].apply(pd.Series))

# Step 3: Drop original Customer column
df_final = df_flat.drop(columns=['Customer'])

print("Final DataFrame using join() method:")
print(df_final)

Why use join() instead of concat()?

  • Cleaner syntax: One line instead of multiple steps
  • Automatic alignment: Joins by index, no need to worry about matching rows
  • More readable: Intent is clearer - "join these new columns to existing DataFrame"

💡 Pro Tip: Use join() when adding columns to the same DataFrame, use concat() when combining completely separate DataFrames!

6. Understanding json_normalize() for Deep Nesting

When you have multiple levels of nesting (dictionaries inside dictionaries), json_normalize() is your best friend! It automatically flattens nested structures with dot notation.

Sample deeply nested JSON:

# Complex nested structure - 3 levels deep!
data = {
    "id": 1,
    "name": "Alice",
    "address": {
        "street": "123 Main St",
        "city": "Wonderland",
        "contacts": {
            "email": "alice@example.com",
            "phone": "1234567890"
        }
    }
}

print("Original nested JSON:")
print(json.dumps(data, indent=2))

Output shows the nesting:

{
  "id": 1,
  "name": "Alice",
  "address": {                    ← Level 1 nesting
    "street": "123 Main St",
    "city": "Wonderland",
    "contacts": {                 ← Level 2 nesting
      "email": "alice@example.com",
      "phone": "1234567890"
    }
  }
}

7. Understanding max_level Parameter

The max_level parameter controls how deep json_normalize() should go when flattening nested structures. This is crucial for controlling your output!

What max_level does:

  • max_level=0: Don't flatten anything, keep all nested structures as-is
  • max_level=1: Flatten only the first level of nesting
  • max_level=2: Flatten first and second levels
  • max_level=None (default): Flatten everything completely
# Flatten with max_level=0 (no flattening)
df_level0 = pd.json_normalize(data, max_level=0)
print("max_level=0 (No flattening):")
print(df_level0)
print(df_level0.columns.tolist())

# Flatten with max_level=1
df_level1 = pd.json_normalize(data, max_level=1)
print("\nmax_level=1 (Flatten first level only):")
print(df_level1)
print(df_level1.columns.tolist())

# Flatten with max_level=2
df_level2 = pd.json_normalize(data, max_level=2)
print("\nmax_level=2 (Flatten two levels):")
print(df_level2)
print(df_level2.columns.tolist())

# Flatten completely (default)
df_complete = pd.json_normalize(data)
print("\nmax_level=None (Complete flattening - DEFAULT):")
print(df_complete)
print(df_complete.columns.tolist())

Output Comparison:

max_level Columns Created Explanation
0 id, name, address address is still a dict
1 id, name, address.street, address.city, address.contacts contacts is still a dict
2 id, name, address.street, address.city, address.contacts.email, address.contacts.phone Everything flattened!
None id, name, address.street, address.city, address.contacts.email, address.contacts.phone Same as max_level=2 (all levels)

When to use different max_level values:

  • max_level=1: When you want to keep some nested structure for later processing
  • max_level=2 or 3: When you know exact nesting depth and want control
  • max_level=None: When you want everything completely flat (most common!)

8. Real-World Example - Complete Workflow

Let's combine everything we learned into a complete real-world workflow for processing e-commerce data!

import pandas as pd
import json

# Real-world JSON Lines data (like from an API or log file)
json_data = """
{"OrderID": 101, "Customer": {"Name": "Alice", "Country": "India", "Email": "alice@email.com"}, "Items": ["Keyboard", "Mouse"], "Total": 1200, "Date": "2024-01-15"}
{"OrderID": 102, "Customer": {"Name": "Bob", "Country": "USA", "Email": "bob@email.com"}, "Items": ["Laptop"], "Total": 75000, "Date": "2024-01-16"}
{"OrderID": 103, "Customer": {"Name": "Charlie", "Country": "UK", "Email": "charlie@email.com"}, "Items": ["Monitor", "Mouse", "Keyboard"], "Total": 15000, "Date": "2024-01-17"}
"""

# Step 1: Read JSON Lines
print("Step 1: Reading JSON Lines format...")
df = pd.read_json(json_data, lines=True)
print(f"Loaded {len(df)} orders")

# Step 2: Explode Items
print("\nStep 2: Exploding Items column...")
df_items = df.explode('Items')
print(f"Expanded to {len(df_items)} item-level records")

# Step 3: Flatten Customer dictionary
print("\nStep 3: Flattening Customer information...")
df_flat = df_items.join(df_items['Customer'].apply(pd.Series))
df_final = df_flat.drop(columns=['Customer'])
print(f"Created {len(df_final.columns)} columns")

# Step 4: Data analysis!
print("\n" + "="*50)
print("FINAL RESULT - Ready for Analysis!")
print("="*50)
print(df_final)

print("\nNow you can answer business questions like:")
print(f"1. Most popular item: {df_final['Items'].value_counts().index[0]}")
print(f"2. Total unique customers: {df_final['Name'].nunique()}")
print(f"3. Countries represented: {df_final['Country'].unique().tolist()}")
print(f"4. Average order value: ${df_final.groupby('OrderID')['Total'].first().mean():,.2f}")

Output:

Step 1: Reading JSON Lines format...
Loaded 3 orders

Step 2: Exploding Items column...
Expanded to 6 item-level records

Step 3: Flattening Customer information...
Created 7 columns

==================================================
FINAL RESULT - Ready for Analysis!
==================================================
   OrderID   Items  Total        Date    Name Country           Email
0      101  Keyboard   1200  2024-01-15   Alice   India  alice@email.com
0      101     Mouse   1200  2024-01-15   Alice   India  alice@email.com
1      102    Laptop  75000  2024-01-16     Bob     USA    bob@email.com
2      103   Monitor  15000  2024-01-17 Charlie      UK charlie@email.com
2      103     Mouse  15000  2024-01-17 Charlie      UK charlie@email.com
2      103  Keyboard  15000  2024-01-17 Charlie      UK charlie@email.com

 Now you can answer business questions like:
1. Most popular item: Mouse
2. Total unique customers: 3
3. Countries represented: ['India', 'USA', 'UK']
4. Average order value: $30,400.00
Labels:

Comments

Post a Comment