5 Best Ways to Convert a Pandas DataFrame to a JSON List

by

💡 Problem Formulation: Data scientists and engineers often face the need to convert data from a pandas DataFrame to a JSON list for API data interchange or for front-end JavaScript frameworks. For instance, one might have a DataFrame containing user data and aims to serialize it to a JSON list where each element corresponds to a user, formatted for easy web display or further processing. This article explores the various methods to perform this conversion efficiently.

Method 1: Using to_json() with orient='records'

The to_json() method with the orient='records' argument is a straightforward way to convert a DataFrame into a JSON list format. The orient='records' parameter specifies the format of the JSON string, so each row becomes a separate object in the resulting JSON array.

Here’s an example:

import pandas as pd

# Create a sample DataFrame
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [25, 30, 35],
    'city': ['New York', 'Los Angeles', 'Chicago']
})

# Convert the DataFrame to a JSON list
json_list = df.to_json(orient='records')

print(json_list)

Output:

[
    {"name":"Alice","age":25,"city":"New York"},
    {"name":"Bob","age":30,"city":"Los Angeles"},
    {"name":"Charlie","age":35,"city":"Chicago"}
]

This code snippet creates a DataFrame and uses the to_json() function with orient='records' to convert it into a JSON list. Each DataFrame row is represented as a separate JSON object within the list.

Method 2: Using to_dict() and json.dumps()

The combination of to_dict() with orient='records' and Python’s standard json.dumps() method allows more control over the serialization process, such as using custom encoders or formatting the output.

Here’s an example:

import pandas as pd
import json

# Create a sample DataFrame
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [25, 30, 35],
    'city': ['New York', 'Los Angeles', 'Chicago']
})

# Convert the DataFrame to a dictionary and then to a JSON list
dict_list = df.to_dict(orient='records')
json_list = json.dumps(dict_list, indent=2)

print(json_list)

Output:

[
  {
    "name": "Alice",
    "age": 25,
    "city": "New York"
  },
  {
    "name": "Bob",
    "age": 30,
    "city": "Los Angeles"
  },
  {
    "name": "Charlie",
    "age": 35,
    "city": "Chicago"
  }
]

In this method, the DataFrame is converted to a list of dictionaries, which is then serialized to a JSON list using json.dumps(). This approach also indents the output for better readability.

Method 3: Using to_json() Directly with a File

Another efficient way is to output the JSON list directly to a file using to_json(). This can be beneficial when dealing with large DataFrames, as it doesn’t require holding the entire JSON string in memory.

Here’s an example:

import pandas as pd

# Create a sample DataFrame
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [25, 30, 35],
    'city': ['New York', 'Los Angeles', 'Chicago']
})

# Write the DataFrame to a JSON list in a file
df.to_json('output.json', orient='records')

# Read the contents of the file to show the output
with open('output.json', 'r') as file:
    print(file.read())

Output:

[
    {"name":"Alice","age":25,"city":"New York"},
    {"name":"Bob","age":30,"city":"Los Angeles"},
    {"name":"Charlie","age":35,"city":"Chicago"}
]

This method directly writes the JSON list to a file, which is useful for large datasets or when the resultant JSON is immediately consumed by another process or service.

Method 4: Using json_normalize()

The json_normalize() function is particularly useful when dealing with nested JSON and can convert semi-structured JSON data into a flat table, after which it can be converted into a JSON list.

Here’s an example:

import pandas as pd
from pandas import json_normalize
import json

# Assuming you have nested JSON
nested_json = [
    {'name': 'Alice', 'info': {'age': 25, 'city': 'New York'}},
    {'name': 'Bob', 'info': {'age': 30, 'city': 'Los Angeles'}},
    {'name': 'Charlie', 'info': {'age': 35, 'city': 'Chicago'}}
]

# Normalize the data and convert to JSON list
df = json_normalize(nested_json)
json_list = df.to_json(orient='records')

print(json_list)

Output:

[
    {"name":"Alice","info.age":25,"info.city":"New York"},
    {"name":"Bob","info.age":30,"info.city":"Los Angeles"},
    {"name":"Charlie","info.age":35,"info.city":"Chicago"}
]

This method helps flatten the data from nested JSON structures into a DataFrame. The DataFrame is then converted into a JSON list using to_json() with orient='records'.

Bonus One-Liner Method 5: Using a List Comprehension

You can also use a Python list comprehension with the DataFrame iterrows() method for a one-liner solution. This method grants full customization on how the data is formatted into the JSON objects.

Here’s an example:

import pandas as pd

# Create a sample DataFrame
df = pd.DataFrame({
    'name': ['Alice', 'Bob', 'Charlie'],
    'age': [25, 30, 35],
    'city': ['New York', 'Los Angeles', 'Chicago']
})

# Convert DataFrame to JSON list with list comprehension
json_list = [{column: value for column, value in row.iteritems()} for index, row in df.iterrows()]

print(json_list)

Output:

[
    {"name": "Alice", "age": 25, "city": "New York"},
    {"name": "Bob", "age": 30, "city": "Los Angeles"},
    {"name": "Charlie", "age": 35, "city": "Chicago"}
]

This list comprehension iterates through each row of the DataFrame and constructs a dictionary, which is naturally converted to JSON when printed or serialized.

Summary/Discussion

Each method for converting a pandas DataFrame to a JSON list has its strengths and weaknesses:

  • Method 1: Using to_json() with orient='records'. Strengths: Simple and concise. Weaknesses: Less control over serialization.
  • Method 2: Using to_dict() and json.dumps(). Strengths: Offers customization and prettified output. Weaknesses: Slightly more verbose.
  • Method 3: Using to_json() Directly with a File. Strengths: Direct file writing, good for large DataFrames. Weaknesses: Additional IO overhead.
  • Method 4: Using json_normalize(). Strengths: Great for nested JSON data. Weaknesses: Requires an extra step of normalization.
  • Bonus Method 5: Using a List Comprehension. Strengths: Highly customizable and Pythonic. Weaknesses: Potentially less readable if overused or complex logic is in the comprehension.