Example 1: Simple JSON to CSV Conversion
Let’s imagine you have a list of users in JSON format. Each user has details such as name, age, and city.
[
{"name": "Alice", "age": 30, "city": "New York"},
{"name": "Bob", "age": 25, "city": "Los Angeles"},
{"name": "Charlie", "age": 35, "city": "Chicago"}
]
To convert this JSON data into a CSV file, you can follow these steps:
1. Import the Required Libraries
Start by importing the json module to handle JSON data and pandas to work with tabular data:
import json
import pandas as pd
2. Load the JSON Data
Next, the JSON data needs to be loaded into Python as a structured object. We can use the json.loads() method to achieve this:
json_data = '[{"name": "Alice", "age": 30, "city": "New York"}, {"name": "Bob", "age": 25, "city": "Los Angeles"}, {"name": "Charlie", "age": 35, "city": "Chicago"}]'
data = json.loads(json_data)
The structured JSON data should be transformed into a table-like format. This involves making sure that each key in the JSON object becomes a column in the CSV file, while each value becomes a row entry.
df = pd.DataFrame(data)
4. Save the Data as a CSV File
Once the data is in tabular format, you can write it into the CSV file. This file can then be opened in tools like Excel or imported into a database for further analysis.
df.to_csv('users.csv', index=False)
Complete code
import pandas as pd
import json
json_data = '[{"name": "Alice", "age": 30, "city": "New York"}, {"name": "Bob", "age": 25, "city": "Los Angeles"}, {"name": "Charlie", "age": 35, "city": "Chicago"}]'
data = json.loads(json_data)
df = pd.DataFrame(data)
df.to_csv('users.csv', index=False)
After running this code, you’ll have a users.csv file with the following content:
name,age,city
Alice,30,New York
Bob,25,Los Angeles
Charlie,35,Chicago
By following these steps, the JSON data is transformed into a simple, structured format that is easier to work with.
Example 2: Handling Nested JSON
Sometimes, JSON data contains nested structures where one object holds another, creating a hierarchy.
For example, in an order system, each order might contain customer details and a list of purchased items. This type of data cannot be directly converted into a CSV file because of its nested structure.
[
{
"order_id": 101,
"customer": {"name": "Alice", "email": "[email protected]"},
"items": [
{"product": "Laptop", "price": 1200},
{"product": "Mouse", "price": 25}
]
},
{
"order_id": 102,
"customer": {"name": "Bob", "email": "[email protected]"},
"items": [
{"product": "Keyboard", "price": 50},
{"product": "Monitor", "price": 300}
]
}
]
To handle this, follow these steps:
1. Import Required Libraries
Like the first example, we’ll use the json module to parse JSON and pandas to handle tabular data:
import json
import pandas as pd
2. Load the JSON Data
If the JSON data is stored as a string, we can load it using json.loads(). If it’s in a file, we’d use json.load().
json_data = '''
[
{
"order_id": 101,
"customer": {"name": "Alice", "email": "[email protected]"},
"items": [
{"product": "Laptop", "price": 1200},
{"product": "Mouse", "price": 25}
]
},
{
"order_id": 102,
"customer": {"name": "Bob", "email": "[email protected]"},
"items": [
{"product": "Keyboard", "price": 50},
{"product": "Monitor", "price": 300}
]
}
]
'''
data = json.loads(json_data)
3. Flatten the Structure
Instead of keeping the original nested structure, create a format where every relevant detail appears in a single row.
Since each order contains multiple items, we need to expand the list of items so that each item appears as a separate row while maintaining the corresponding order and customer details.
flattened_data = []
for order in data:
for item in order["items"]:
flattened_data.append({
"order_id": order["order_id"],
"customer_name": order["customer"]["name"],
"customer_email": order["customer"]["email"],
"product": item["product"],
"price": item["price"]
})
4. Convert the Flattened Data into a Table
The newly structured data is then transformed into a tabular format where each column represents an attribute (such as order ID, customer name, product, and price).
df = pd.DataFrame(flattened_data)
5. Save the Data as a CSV File
When the data is properly structured, it can be written into a CSV file. This ensures that even complex JSON data can be stored in a simple, easy-to-read format.
df.to_csv('orders.csv', index=False)
Complete Code
import pandas as pd
import json
json_data = '[{"order_id": 101, "customer": {"name": "Alice", "email": "[email protected]"}, "items": [{"product": "Laptop", "price": 1200}, {"product": "Mouse", "price": 25}]}, {"order_id": 102, "customer": {"name": "Bob", "email": "[email protected]"}, "items": [{"product": "Keyboard", "price": 50}, {"product": "Monitor", "price": 300}]}]'
data = json.loads(json_data)
flattened_data = []
for order in data:
for item in order['items']:
flattened_data.append({
'order_id': order['order_id'],
'customer_name': order['customer']['name'],
'customer_email': order['customer']['email'],
'product': item['product'],
'price': item['price']
})
df = pd.DataFrame(flattened_data)
df.to_csv('orders.csv', index=False)
Output (orders.csv):
order_id,customer_name,customer_email,product,price
101,Alice,[email protected],Laptop,1200
101,Alice,[email protected],Mouse,25
102,Bob,[email protected],Keyboard,50
102,Bob,[email protected],Monitor,300
In the above, we were able to extract nested customer details (name, email), expand the items list, so each product appears in a separate row, convert the structured data into a DataFrame and then save the DataFrame as a CSV file.
Example 3: Converting JSON with Mixed Data Types
Let’s look at a more advanced scenario in which the JSON data contains mixed data types (a mixed data type is a dataset or column that contains values of different data types), such as strings, numbers, arrays, and nested objects.
This type of JSON is common in real-world applications, such as e-commerce platforms, where product listings might include details like product names, prices, categories, and customer reviews.
Imagine you have the following JSON data representing a list of products
[
{
"product_id": 1,
"name": "Wireless Headphones",
"price": 99.99,
"category": "Electronics",
"features": ["Noise Cancellation", "Bluetooth 5.0", "20-hour Battery"],
"ratings": {
"average": 4.5,
"reviews": [
{"user": "Alice", "rating": 5, "comment": "Great sound quality!"},
{"user": "Bob", "rating": 4, "comment": "Good value for money."}
]
}
},
{
"product_id": 2,
"name": "Smartwatch",
"price": 149.99,
"category": "Wearables",
"features": ["Heart Rate Monitor", "Step Counter", "Water Resistant"],
"ratings": {
"average": 4.2,
"reviews": [
{"user": "Charlie", "rating": 4, "comment": "Comfortable to wear."},
{"user": "Diana", "rating": 5, "comment": "Love the design!"}
]
}
}
]
This JSON includes:
Basic fields like product_id, name, price, and category.
An array of features for each product.
A nested ratings object containing an average rating and a list of reviews, where each review is another nested object with user details, a rating, and a comment.
Step 1: Load JSON Data
The json.loads() function is used to parse the JSON string into a Python list of dictionaries. Each dictionary represents a product.
data = json.loads(json_data)
Step 2: Flatten the Data
flattened_data = []
for product in data:
product_id = product["product_id"]
name = product["name"]
price = product["price"]
category = product["category"]
features = ", ".join(product["features"])
average_rating = product["ratings"]["average"]
reviews = []
for review in product["ratings"]["reviews"]:
review_str = f"{review['user']}: {review['rating']} - {review['comment']}"
reviews.append(review_str)
reviews_combined = "; ".join(reviews)
From the above code,
We iterate through each product in the JSON data.
For each product, we extract the basic fields (product_id, name, price, and category).
The features array is combined into a single string using ", ".join().
The average rating is extracted from the nested ratings object.
The reviews array is processed to combine each review into a formatted string (e.g., "Alice: 5 - Great sound quality!"). These strings are then joined into a single string using "; ".join().
Step 3: Create a DataFrame
The flattened data is stored in a list of dictionaries, where each dictionary represents a row in the CSV. The pandas.DataFrame() function is used to convert this list into a DataFrame.
flattened_data.append({
"product_id": product_id,
"name": name,
"price": price,
"category": category,
"features": features,
"average_rating": average_rating,
"reviews": reviews_combined
})
df = pd.DataFrame(flattened_data)
Step 4: Save as CSV
The to_csv() method saves the DataFrame as a CSV file named products.csv. The index=False parameter ensures that the DataFrame index is not included in the CSV.
df.to_csv("products.csv", index=False)
Complete Code
import pandas as pd
import json
json_data = '''
[
{
"product_id": 1,
"name": "Wireless Headphones",
"price": 99.99,
"category": "Electronics",
"features": ["Noise Cancellation", "Bluetooth 5.0", "20-hour Battery"],
"ratings": {
"average": 4.5,
"reviews": [
{"user": "Alice", "rating": 5, "comment": "Great sound quality!"},
{"user": "Bob", "rating": 4, "comment": "Good value for money."}
]
}
},
{
"product_id": 2,
"name": "Smartwatch",
"price": 149.99,
"category": "Wearables",
"features": ["Heart Rate Monitor", "Step Counter", "Water Resistant"],
"ratings": {
"average": 4.2,
"reviews": [
{"user": "Charlie", "rating": 4, "comment": "Comfortable to wear."},
{"user": "Diana", "rating": 5, "comment": "Love the design!"}
]
}
}
]
'''
data = json.loads(json_data)
flattened_data = []
for product in data:
product_id = product["product_id"]
name = product["name"]
price = product["price"]
category = product["category"]
features = ", ".join(product["features"])
average_rating = product["ratings"]["average"]
reviews = []
for review in product["ratings"]["reviews"]:
review_str = f"{review['user']}: {review['rating']} - {review['comment']}"
reviews.append(review_str)
reviews_combined = "; ".join(reviews)
flattened_data.append({
"product_id": product_id,
"name": name,
"price": price,
"category": category,
"features": features,
"average_rating": average_rating,
"reviews": reviews_combined
})
df = pd.DataFrame(flattened_data)
df.to_csv("products.csv", index=False)
Pro Tips for Handling Mixed Data
Here are some tips that will help you handle mixed data:
Avoid overcomplicating the CSV structure. Combine nested data into single columns if it makes the file easier to work with.
Use clear separators (like commas or semicolons) when combining arrays or nested objects into strings.
Always check the CSV file to ensure the data is correctly formatted and no information is lost.
Always inspect your JSON data structure to be sure before converting to understand its hierarchy.
Use Pandas’ fillna() method to manage null values.
Use to_csv() parameters like sep to change the delimiter or columns to select specific columns.
By following this approach, even the most complex JSON structures can be converted into a clean and usable CSV file.
