Pandas to_json(): Export and Verify Structured Data
DataFrame.to_json() encodes a table as a JSON string or writes it to a file. Choose an orientation that fits the consumer, define date and missing-value representations, then decode the output to check that the intended records and fields survived.
Create a JSON String from a DataFrame
With no output path, to_json() returns a string. For a DataFrame, the default orientation is columns. A Series has a different default, index. Use json.loads() to parse the JSON string into Python objects and inspect it.
import pandas as pd
import json
from io import StringIO, BytesIO
from pathlib import Path
export_dir = Path("pandas_json_practice")
export_dir.mkdir(exist_ok=True)
def student_data():
return pd.DataFrame({"NAME": ["Ravi", "Raju", "Alex"],
"ID": [1, 2, 3], "MATH": [30, 40, 50],
"ENGLISH": [20, 30, 40]})
df = student_data()
payload = df.to_json()
print(payload)
print("Returned type:", type(payload).__name__)
assert json.loads(payload)["MATH"]["0"] == 30Expected output
{"NAME":{"0":"Ravi","1":"Raju","2":"Alex"},"ID":{"0":1,"1":2,"2":3},"MATH":{"0":30,"1":40,"2":50},"ENGLISH":{"0":20,"1":30,"2":40}}
Returned type: strWrite a JSON File Instead of Returning a String
Supplying a path writes the JSON file and returns None. A new write replaces an existing file at that path. Read the file back to check it. In Colab this file belongs to the runtime filesystem; use the Files sidebar to download it.
df = student_data()
path = export_dir / "students.json"
result = df.to_json(path, orient="records", indent=2)
records = json.loads(path.read_text(encoding="utf-8"))
print("Return value:", result)
print("Records saved:", len(records))
assert result is None
assert records[0]["NAME"] == "Ravi"Expected output
Return value: None
Records saved: 3orient="columns": Column Names Then Row Labels
The outer keys are column names and the inner keys identify rows. JSON object keys are strings, so integer index labels become string keys. Choose this format when your consumer expects column-oriented access.
df = student_data()
payload = df.to_json(orient="columns", indent=2)
print(payload)
assert json.loads(payload)["ID"]["2"] == 3Expected output
{
"NAME":{
"0":"Ravi",
"1":"Raju",
"2":"Alex"
},
"ID":{
"0":1,
"1":2,
"2":3
},
"MATH":{
"0":30,
"1":40,
"2":50
},
"ENGLISH":{
"0":20,
"1":30,
"2":40
}
}orient="records": One Object per Row
Records produces a JSON array of row objects. The DataFrame index is omitted; ID is kept here because it is a column. This is often convenient for record-processing code, but an external API still requires its own field schema and validation. Compare to_dict() when you need Python objects rather than an encoded string.
df = student_data()
payload = df.to_json(orient="records", indent=2)
print(payload)
records = json.loads(payload)
assert isinstance(records, list)
assert records[1]["ID"] == 2Expected output
[
{
"NAME":"Ravi",
"ID":1,
"MATH":30,
"ENGLISH":20
},
{
"NAME":"Raju",
"ID":2,
"MATH":40,
"ENGLISH":30
},
{
"NAME":"Alex",
"ID":3,
"MATH":50,
"ENGLISH":40
}
]orient="index": One Object per Index Label
The outer object uses row labels as keys. Select a unique business key as the index when that is the intended lookup. JSON keys are strings. Duplicate labels are unsuitable for this orientation; validate uniqueness before export.
df = student_data().set_index("ID", verify_integrity=True)
payload = df.to_json(orient="index", indent=2)
print(payload)
assert json.loads(payload)["2"]["NAME"] == "Raju"Expected output
{
"1":{
"NAME":"Ravi",
"MATH":30,
"ENGLISH":20
},
"2":{
"NAME":"Raju",
"MATH":40,
"ENGLISH":30
},
"3":{
"NAME":"Alex",
"MATH":50,
"ENGLISH":40
}
}orient="split": Separate Columns, Index and Data
Split keeps the axis labels separate from the row values. The receiving code needs all three parts to interpret the table. Use the same orientation when reading the JSON back with read_json().
df = student_data()
payload = df.to_json(orient="split", indent=2)
print(payload)
restored = pd.read_json(StringIO(payload), orient="split")
assert restored.equals(df)Expected output
{
"columns":[
"NAME",
"ID",
"MATH",
"ENGLISH"
],
"index":[
0,
1,
2
],
"data":[
[
"Ravi",
1,
30,
20
],
[
"Raju",
2,
40,
30
],
[
"Alex",
3,
50,
40
]
]
}orient="values": Values Without a Schema
Values exports only a nested array. It omits column names and index labels, so the consumer must know the column order separately. Use a labelled orientation unless this compact format fits an agreed schema.
df = student_data()
payload = df.to_json(orient="values")
print(payload)
assert json.loads(payload)[0] == ["Ravi", 1, 30, 20]Expected output
[["Ravi",1,30,20],["Raju",2,40,30],["Alex",3,50,40]]orient="table": Schema Plus Records
Table includes a schema and data records, and can reconstruct supported types through read_json with the same orientation. The schema pandas_version field identifies a table-schema revision; it is not the installed runtime version. Validate any required types after a round trip.
df = student_data()
payload = df.to_json(orient="table", indent=2)
data = json.loads(payload)
print("Top-level keys:", list(data))
print("Schema fields:", [field["name"] for field in data["schema"]["fields"]])
print("Data records:", data["data"])
restored = pd.read_json(StringIO(payload), orient="table")
assert restored.equals(df)Expected output
Top-level keys: ['schema', 'data']
Schema fields: ['index', 'NAME', 'ID', 'MATH', 'ENGLISH']
Data records: [{'index': 0, 'NAME': 'Ravi', 'ID': 1, 'MATH': 30, 'ENGLISH': 20}, {'index': 1, 'NAME': 'Raju', 'ID': 2, 'MATH': 40, 'ENGLISH': 30}, {'index': 2, 'NAME': 'Alex', 'ID': 3, 'MATH': 50, 'ENGLISH': 40}]Dates and Missing Values: Use an Explicit Policy
Missing values become JSON null. Use date_format="iso" for readable date strings and specify the timestamp precision with date_unit. Epoch-date encoding is deprecated in Pandas 3.0; explicit ISO formatting also makes the intended representation clear. A null amount means unknown, not automatically zero.
df = pd.DataFrame({"order_id": [1001, 1002],
"amount": [40.0, float("nan")],
"date": pd.to_datetime(["2026-01-01", "2026-01-02"])})
payload = df.to_json(orient="records", date_format="iso", date_unit="s", indent=2)
print(payload)
records = json.loads(payload)
assert records[1]["amount"] is None
assert records[0]["date"] == "2026-01-01T00:00:00"Expected output
[
{
"order_id":1001,
"amount":40.0,
"date":"2026-01-01T00:00:00"
},
{
"order_id":1002,
"amount":null,
"date":"2026-01-02T00:00:00"
}
]Unicode, Indentation and Floating-Point Precision
force_ascii=False keeps characters readable rather than escaping them. indent makes output easier to inspect. double_precision controls float encoding and accepts values up to 15; it is not a complete currency-rounding policy. For exact money values, define decimal or integer-minor-unit rules before export.
df = pd.DataFrame({"city": ["München", "東京"], "amount": [12.3456789, 4.2]})
payload = df.to_json(orient="records", force_ascii=False,
indent=2, double_precision=4)
print(payload)
records = json.loads(payload)
assert records[0]["city"] == "München"
assert records[0]["amount"] == 12.3457Expected output
[
{
"city":"München",
"amount":12.3457
},
{
"city":"東京",
"amount":4.2
}
]Write JSON Lines for Row-by-Row Records
With records and lines=True, each line is an independent JSON object. This is JSON Lines/NDJSON, not one JSON array. Parse each line separately or use read_json with lines=True. It is useful for tools that process one record at a time.
df = student_data()
path = export_dir / "students.jsonl"
df.to_json(path, orient="records", lines=True)
lines = path.read_text(encoding="utf-8").splitlines()
print("Lines written:", len(lines))
print("First record:", json.loads(lines[0]))
restored = pd.read_json(path, lines=True)
assert restored.equals(df)Expected output
Lines written: 3
First record: {'NAME': 'Ravi', 'ID': 1, 'MATH': 30, 'ENGLISH': 20}Append Only with records and lines=True
Append mode is supported for record-oriented JSON Lines. Appending ordinary JSON arrays would not create one valid JSON document. This example recreates a practice file before appending so repeated runs do not duplicate records.
df = student_data()
path = export_dir / "append_students.jsonl"
df.iloc[:2].to_json(path, orient="records", lines=True, mode="w")
df.iloc[2:].to_json(path, orient="records", lines=True, mode="a")
restored = pd.read_json(path, lines=True)
print(restored)
assert len(restored) == 3
assert restored["ID"].is_uniqueExpected output
NAME ID MATH ENGLISH
0 Ravi 1 30 20
1 Raju 2 40 30
2 Alex 3 50 40Convert CSV Data to JSON
Read the source, select the fields and choose an orientation. This in-memory CSV is immediately runnable. Replace StringIO with your own filename when importing a real file. Validate inferred types and missing inputs before export; see read_csv().
csv_text = "id,name,mark\n1,John Deo,75\n2,Max Ruin,85\n3,Arnold,55\n"
df = pd.read_csv(StringIO(csv_text))
payload = df.to_json(orient="records", indent=2)
print(payload)
assert json.loads(payload)[2]["mark"] == 55Expected output
[
{
"id":1,
"name":"John Deo",
"mark":75
},
{
"id":2,
"name":"Max Ruin",
"mark":85
},
{
"id":3,
"name":"Arnold",
"mark":55
}
]Convert Excel Data to JSON
After read_excel(), JSON export works the same way. This example creates an in-memory workbook for practice and requires openpyxl. Real spreadsheets may need explicit dtype or date parsing before generating the payload.
workbook = BytesIO()
student_data().to_excel(workbook, index=False, engine="openpyxl")
workbook.seek(0)
df = pd.read_excel(workbook, engine="openpyxl")
payload = df.to_json(orient="records")
records = json.loads(payload)
print("Excel records converted:", len(records))
print("First record:", records[0])
assert len(records) == 3Expected output
Excel records converted: 3
First record: {'NAME': 'Ravi', 'ID': 1, 'MATH': 30, 'ENGLISH': 20}Database to JSON: Runnable SQLite Practice
Read a query into a DataFrame, then export it. Select only required fields and specify ORDER BY for a predictable order. This in-memory SQLite example requires no credentials. See read_sql() for the import step.
import sqlite3
with sqlite3.connect(":memory:") as connection:
student_data().to_sql("student", connection, index=False)
df = pd.read_sql_query("SELECT ID, NAME, MATH FROM student ORDER BY ID", connection)
payload = df.to_json(orient="records", indent=2)
print(payload)
assert json.loads(payload)[0]["ID"] == 1Expected output
[
{
"ID":1,
"NAME":"Ravi",
"MATH":30
},
{
"ID":2,
"NAME":"Raju",
"MATH":40
},
{
"ID":3,
"NAME":"Alex",
"MATH":50
}
]Optional MySQL Query Export
Use an authorized reachable MySQL server. Set MYSQL_HOST, MYSQL_USER and MYSQL_DATABASE, and install SQLAlchemy and PyMySQL if needed. The notebook disables this cell so the main examples run without credentials. Colab localhost is not your XAMPP computer. Follow MySQL with SQLAlchemy and the sample student SQL dump.
RUN_MYSQL = False
if RUN_MYSQL:
import os
from getpass import getpass
from sqlalchemy import create_engine, text
from sqlalchemy.engine import URL
url = URL.create("mysql+pymysql", username=os.environ["MYSQL_USER"],
password=getpass("MySQL password: "),
host=os.environ["MYSQL_HOST"], database=os.environ["MYSQL_DATABASE"])
engine = create_engine(url)
try:
with engine.connect() as connection:
df = pd.read_sql_query(text(
"SELECT id, name, mark FROM student ORDER BY id LIMIT 10"), connection)
df.to_json(export_dir / "mysql_students.json", orient="records", indent=2)
finally:
engine.dispose()Practical Payload: Select Fields and Verify Records
Choose the fields required by the consumer before serializing. This synthetic order table contains an internal note that does not belong in the exported records. Verify row count, field names and missing-value handling. Generating JSON does not send it to an API or validate an API schema.
df = pd.DataFrame({"order_id": [1001, 1002, 1003],
"category": ["Office", "Books", "Stationery"],
"amount": [550.0, 400.0, float("nan")],
"internal_note": ["checked", "checked", "needs review"]})
selected = df[["order_id", "category", "amount"]]
payload = selected.to_json(orient="records", indent=2)
records = json.loads(payload)
print(payload)
assert len(records) == 3
assert all(set(row) == {"order_id", "category", "amount"} for row in records)
assert records[2]["amount"] is NoneExpected output
[
{
"order_id":1001,
"category":"Office",
"amount":550.0
},
{
"order_id":1002,
"category":"Books",
"amount":400.0
},
{
"order_id":1003,
"category":"Stationery",
"amount":null
}
]Common JSON Export Problems
Wrong default orientation: DataFrame defaults to columns, while Series defaults to index. Lost index labels: records omits them; use reset_index when they represent an important field. Duplicate labels: verify uniqueness for object-key orientations such as index and columns. JSON wrapped in extra quotes: do not encode an already encoded JSON string again; use json.loads to inspect it. Read-back types differ: orientation and inferred types affect reconstruction; validate schema requirements. Unknown values: null is not automatically zero. Repeated append: JSON Lines append does not deduplicate records. Large tables: avoid exporting unnecessary fields and consider chunked import or a format suited to the consumer.
Exercise: Export Only Students with MATH at Least 40
Select ID, NAME and MATH for matching students. Generate record-oriented JSON, parse it and check that it has two records and a combined MATH score of 90. Predict the JSON fields before running the solution.
Exercise Solution
Filter before serialization and check the decoded records, not merely that a string was produced.
df = student_data()
selected = df.loc[df["MATH"].ge(40), ["ID", "NAME", "MATH"]]
payload = selected.to_json(orient="records", indent=2)
records = json.loads(payload)
print(payload)
assert len(records) == 2
assert sum(row["MATH"] for row in records) == 90
assert [row["ID"] for row in records] == [2, 3]Expected output
[
{
"ID":2,
"NAME":"Raju",
"MATH":40
},
{
"ID":3,
"NAME":"Alex",
"MATH":50
}
]Review Questions
Can you explain the difference between a JSON string and parsed Python objects? Which orientations retain labels? Why must JSON Lines be parsed differently from a JSON array? What does null mean in your exported report?
- What is the purpose of the
to_json()function in Pandas? - How do you use the
to_json()function to convert a Pandas DataFrame to a JSON string? - Can the
to_json()function handle complex data structures, such as nested dictionaries or lists? - What are the available options for the
orientparameter in theto_json()function? - How can you specify the output file path when using the
to_json()function? - Is it possible to customize the formatting of the JSON string generated by the
to_json()function? - Can the
to_json()function handle missing or NaN values in the DataFrame? - What is the difference between using
to_json()with theorientparameter set to "columns" versus "index"? - How does the
date_formatparameter work in theto_json()function? - Is it possible to exclude certain columns or rows from the JSON output using the
to_json()function?
Practice in Google Colab
Open in Google Colab View on GitHub
Run the examples, compare orientations and download the generated JSON files using the Files sidebar. All datasets are included. See the Colab guide for file handling.
Continue with Pandas Input and Output, read_json(), to_dict(), to_csv() and to_excel(). Reference: Pandas to_json documentation.
Subhendu Mohapatra
Author & Instructor at plus2net
I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.
Subscribe to our YouTube Channel here
Python Video Tutorials