Pandas – Convert DataFrame to JSON String

  • Post author:
  • Post category:Pandas
  • Post last modified:November 4, 2024
  • Reading time:18 mins read
You are currently viewing Pandas – Convert DataFrame to JSON String

You can convert Pandas DataFrame to JSON string by using the DataFrame.to_json() method. This method takes a very important param orient which accepts values ‘columns‘, ‘records‘, ‘index‘, ‘split‘, ‘table‘, and ‘values‘. JSON stands for JavaScript Object Notation. It is used to represent structured data. You can use it, especially for sharing data between servers and web applications.

Advertisements

In this article, I will cover how to convert Pandas DataFrame to JSON String. Pandas DataFrame.to_json() is used to convert a DataFrame to JSON string or store it to an external JSON file. The JSON format depends on what value you use for an orient parameter.

Key Points –

  • The primary method for converting a DataFrame to JSON is DataFrame.to_json().
  • to_json() supports various JSON formats like split, records, index, columns, and values.
  • The orient parameter controls the format of the JSON output, allowing flexibility in data structure based on your needs.
  • By default, to_json() includes the DataFrame’s index in the JSON output, but it can be omitted by setting index=False.
  • By default, NaN values in the DataFrame are converted to null in JSON format.

Quick Examples of Convert DataFrame To JSON String

If you are in a hurry, below are some quick examples of how to convert DataFrame to JSON String.


# Quick examples of convert DataFrame to JSON string

# Example 1: Use DataFrame.to_json() 
# To orient = 'columns' 
df2 = df.to_json(orient = 'columns')  

# Example 2: Convert Pandas DataFrame To JSON 
# Using orient = 'records' 
df2 = df.to_json(orient = 'records')

# Example 3: Convert Pandas DataFrame To JSON 
# Using orient = 'index'
df2 = df.to_json(orient ='index')

# Example 4: Convert Pandas DataFrame To JSON
# Using orient = 'split'
df2 = df.to_json(orient = 'split')

# Example 5: Convert Pandas DataFrame To JSON 
# Using orient = 'table'
df2 = df.to_json(orient = 'table')

# Example 6: Convert Pandas DataFrame To JSON 
# Using orient ='values'
df2 = df.to_json(orient ='values')

Now, let’s create a DataFrame with a few rows and columns, execute these examples, and validate the results. Our DataFrame contains column names Courses, Fee, Duration, and Discount.


import pandas as pd
technologies = [
            ("Spark", 22000,'30days',1000.0),
            ("PySpark",25000,'50days',2300.0),
            ("Hadoop",23000,'55days',1500.0)
            ]
df = pd.DataFrame(technologies,columns = ['Courses','Fee','Duration','Discount'])
print("Create DataFrame:\n", df)

Yields below output.

pandas DataFrame convert JSON

Use DataFrame.to_json() to orient = ‘columns’

orient='columns' is a default value, when not specifying the DataFrame.to_json() function uses columns as orient and returns JSON string like a dict {column -> {index -> value}} format.


# Use DataFrame.to_json() to orient = 'columns' 
df2 = df.to_json(orient = 'columns')
print("After converting DataFrame to JSONstring:\n", df2)

Yields below output.

pandas DataFrame convert JSON

Convert DataFrame to JSON Using orient = ‘records’

Use orient='records' to convert DataFrame to JSON in format  [{column -> value}, … , {column -> value}]


# Convert Pandas DataFrame To JSON 
# Using orient = 'records' 
df2 = df.to_json(orient = 'records')
print("After converting DataFrame to JSON string:\n", df2)

Yields below output.


# Output:
# After converting DataFrame to JSON string:
[{"Courses":"Spark","Fee":22000,"Duration":"30days","Discount":1000.0},{"Courses":"PySpark","Fee":25000,"Duration":"50days","Discount":2300.0},{"Courses":"Hadoop","Fee":23000,"Duration":"55days","Discount":1500.0}]

Using orient = ‘index’

Use orient='index' to get JSON string in format dict like {index -> {column -> value}}


# Convert Pandas DataFrame To JSON 
# Using orient = 'index'
df2 = df.to_json(orient ='index')
print("After converting DataFrame to JSONstring:\n", df2)

Yields below output.


# Output:
# After converting DataFrame to JSON string:
{"0":{"Courses":"Spark","Fee":22000,"Duration":"30days","Discount":1000.0},"1":{"Courses":"PySpark","Fee":25000,"Duration":"50days","Discount":2300.0},"2":{"Courses":"Hadoop","Fee":23000,"Duration":"55days","Discount":1500.0}}

Using orient = ‘split’

You can use orient='split' to convert DataFrame to JSON in format dict like {‘index’ -> [index], ‘columns’ -> [columns], ‘data’ -> [values]}.


# Convert Pandas DataFrame To JSON 
# Using orient = 'split'
df2 = df.to_json(orient = 'split')
print("After converting DataFrame to JSONstring:\n", df2)

Yields below output.


# Output:
# After converting DataFrame to JSONstring:
{"columns":["Courses","Fee","Duration","Discount"],"index":[0,1,2],"data":[["Spark",22000,"30days",1000.0],["PySpark",25000,"50days",2300.0],["Hadoop",23000,"55days",1500.0]]}

Using orient = ‘table’

You can use orient='table' to convert DataFrame to JSON with format  dict like {‘schema’: {schema}, ‘data’: {data}}.


# Convert Pandas DataFrame To JSON 
# Using orient = 'table'
df2 = df.to_json(orient = 'table')
print("After converting DataFrame to JSONstring:\n", df2)

Yields below output.


# Output:
# After converting DataFrame to JSONstring:
{"schema":{"fields":[{"name":"index","type":"integer"},{"name":"Courses","type":"string"},{"name":"Fee","type":"integer"},{"name":"Duration","type":"string"},{"name":"Discount","type":"number"}],"primaryKey":["index"],"pandas_version":"0.20.0"},"data":[{"index":0,"Courses":"Spark","Fee":22000,"Duration":"30days","Discount":1000.0},{"index":1,"Courses":"PySpark","Fee":25000,"Duration":"50days","Discount":2300.0},{"index":2,"Courses":"Hadoop","Fee":23000,"Duration":"55days","Discount":1500.0}]}

Using orient =’values’

You can also use orient ='values' to get DataFrame as an array of values.


# Convert Pandas DataFrame to JSON
# Using orient ='values'
df2 = df.to_json(orient ='values')
print("After converting DataFrame to JSONstring:\n", df2)

Yields below output.


# Output:
# After converting DataFrame to JSONstring:
[["Spark",22000,"30days",1000.0],["PySpark",25000,"50days",2300.0],["Hadoop",23000,"55days",1500.0]]

Complete Example For Convert DataFrame To JSON


import pandas as pd
technologies = [
            ("Spark", 22000,'30days',1000.0),
            ("PySpark",25000,'50days',2300.0),
            ("Hadoop",23000,'55days',1500.0)
            ]
df = pd.DataFrame(technologies,columns = ['Courses','Fee','Duration','Discount'])
print(df)
 
# Use DataFrame.to_json() to orient = 'columns' 
df2 = df.to_json(orient = 'columns')
print(df2)   

# Convert Pandas DataFrame To JSON 
# Using orient = 'records' 
df2 = df.to_json(orient = 'records')
print(df2)

# Convert Pandas DataFrame To JSON 
# Using orient = 'index'
df2 = df.to_json(orient ='index')
print(df2)

# Convert Pandas DataFrame To JSON 
# Using orient = 'split'
df2 = df.to_json(orient = 'split')
print(df2)

# Convert Pandas DataFrame To JSON
# Using orient = 'table'
df2 = df.to_json(orient = 'table')
print(df2)

# Convert Pandas DataFrame To JSON 
# Using orient ='values'
df2 = df.to_json(orient ='values')
print(df2)

Frequently Asked Questions on Convert DataFrame To JSON

How do I convert a DataFrame to JSON in Python using pandas?

You can use the to_json() method in Pandas to convert the DataFrame to JSON. For example: df.to_json('output.json', orient='records').

What is the ‘orient’ parameter in the to_json() method?

The ‘orient’ parameter specifies the format of the JSON output. Common values include ‘split’, ‘records’, ‘index’, ‘columns’, and ‘values’. The most commonly used for interoperability is ‘records’.

How can I pretty print the JSON output when converting a DataFrame?

You can use the indent parameter in the to_json() method to print the pretty JSON output while converting a DataFrame. For example: df.to_json('output.json', orient='records', indent=4).

How can I convert only a specific subset of columns to JSON?

You can pass a subset of columns to the to_json() method to convert only a specific subset of columns to JSON. For example: df[['column1', 'column2']].to_json('output.json', orient='records').

What’s the difference between ‘split’ and ‘records’ orient in to_json()?

In 'split' orient, the JSON object is split into separate parts for index, columns, and data. In 'records' orient, each row in the DataFrame becomes a separate JSON object in the output.

Conclusion

In this article, you have learned how to convert pandas DataFrame to JSON by using DataFrame.to_json() method and with more examples. For more params use to_json() method from the pandas reference.

Happy Learning !!

References

Tags: