Pandas read_json(): Read JSON Files, Lines and API Records
Use read_json() to import JSON into a DataFrame. Match the document structure, preserve identifiers and check dates and numeric fields before analysis.
Read JSON records from a text buffer 🔝
Use a file path, accessible URL or readable buffer. Wrap JSON text in StringIO rather than passing it as a filename. A relative file path uses the current working directory. The student sample introduces the same familiar fields.
import pandas as pd
import json, tempfile
from pathlib import Path
from io import StringIO, BytesIO
students = [
{"class": "Four", "id": 1, "mark": 75, "name": "John Deo", "sex": "female"},
{"class": "Three", "id": 2, "mark": 85, "name": "Max Ruin", "sex": "male"},
{"class": "Three", "id": 3, "mark": 55, "name": "Arnold", "sex": "male"},
{"class": "Four", "id": 4, "mark": 60, "name": "Krish Star", "sex": "female"},
{"class": "Four", "id": 5, "mark": 60, "name": "John Mike", "sex": "female"}]
student_json = json.dumps(students)
student_frame = pd.read_json(StringIO(student_json), orient="records")
print(student_frame)
assert student_frame["id"].tolist() == [1, 2, 3, 4, 5]Expected output
class id mark name sex
0 Four 1 75 John Deo female
1 Three 2 85 Max Ruin male
2 Three 3 55 Arnold male
3 Four 4 60 Krish Star female
4 Four 5 60 John Mike femaleRead a file or a hosted URL 🔝
Read a local UTF-8 JSON file using its actual path. A localhost URL refers to the machine running the code: in Colab it does not reach your Windows web server. The hosted student JSON is a separate optional source.
with tempfile.TemporaryDirectory(dir=Path.cwd()) as folder:
target = Path(folder) / "student.json"
target.write_text(student_json, encoding="utf-8")
loaded = pd.read_json(target, orient="records", encoding="utf-8")
print("Rows read from file:", len(loaded))
assert loaded.equals(student_frame)
# Optional hosted source (requires internet):
# hosted = pd.read_json("https://www.plus2net.com/php_tutorial/student.json")
# Windows local server, when Python runs on the same computer:
# local = pd.read_json("http://127.0.0.1/student.json")Expected output
Rows read from file: 5Choose an orientation that matches the JSON 🔝
orient describes the document structure. The DataFrame default is columns. Match the writer and reader when using to_json(). The following examples use the original Alex, Ravi and Ron marks table.
df = pd.DataFrame({"NAME": ["Alex", "Ravi", "Ron"], "ID": [1, 2, 3],
"MATH": [50, 36, 45], "ENGLISH": [50, 48, 49]})
print(df)Expected output
NAME ID MATH ENGLISH
0 Alex 1 50 50
1 Ravi 2 36 48
2 Ron 3 45 49Read orient="split" 🔝
split stores columns, index and data separately. It retains row and column labels.
text = df.to_json(orient="split")
print(text)
loaded = pd.read_json(StringIO(text), orient="split")
print(loaded)
assert loaded.equals(df)Expected output
{"columns":["NAME","ID","MATH","ENGLISH"],"index":[0,1,2],"data":[["Alex",1,50,50],["Ravi",2,36,48],["Ron",3,45,49]]}
NAME ID MATH ENGLISH
0 Alex 1 50 50
1 Ravi 2 36 48
2 Ron 3 45 49Read orient="records" 🔝
records stores a list of row objects. It does not retain the DataFrame index.
text = df.to_json(orient="records")
print(text)
loaded = pd.read_json(StringIO(text), orient="records")
print(loaded)
assert loaded.equals(df)Expected output
[{"NAME":"Alex","ID":1,"MATH":50,"ENGLISH":50},{"NAME":"Ravi","ID":2,"MATH":36,"ENGLISH":48},{"NAME":"Ron","ID":3,"MATH":45,"ENGLISH":49}]
NAME ID MATH ENGLISH
0 Alex 1 50 50
1 Ravi 2 36 48
2 Ron 3 45 49Read orient="index" 🔝
index stores a row-label object containing field values. The source index must be unique.
text = df.to_json(orient="index")
print(text)
loaded = pd.read_json(StringIO(text), orient="index")
print(loaded)
assert loaded.equals(df)Expected output
{"0":{"NAME":"Alex","ID":1,"MATH":50,"ENGLISH":50},"1":{"NAME":"Ravi","ID":2,"MATH":36,"ENGLISH":48},"2":{"NAME":"Ron","ID":3,"MATH":45,"ENGLISH":49}}
NAME ID MATH ENGLISH
0 Alex 1 50 50
1 Ravi 2 36 48
2 Ron 3 45 49Read orient="columns" 🔝
columns stores a field-label object containing row-label/value objects. Read it with orient="columns".
text = df.to_json(orient="columns")
print(text)
loaded = pd.read_json(StringIO(text), orient="columns")
print(loaded)
assert loaded.equals(df)Expected output
{"NAME":{"0":"Alex","1":"Ravi","2":"Ron"},"ID":{"0":1,"1":2,"2":3},"MATH":{"0":50,"1":36,"2":45},"ENGLISH":{"0":50,"1":48,"2":49}}
NAME ID MATH ENGLISH
0 Alex 1 50 50
1 Ravi 2 36 48
2 Ron 3 45 49Read orient="values" 🔝
values stores only the cell arrays. Restore labels explicitly when their meaning matters.
text = df.to_json(orient="values")
print(text)
loaded = pd.read_json(StringIO(text), orient="values")
loaded.columns = df.columns
print(loaded)
assert loaded.equals(df)Expected output
[["Alex",1,50,50],["Ravi",2,36,48],["Ron",3,45,49]]
NAME ID MATH ENGLISH
0 Alex 1 50 50
1 Ravi 2 36 48
2 Ron 3 45 49Use table orientation for schema information 🔝
table includes schema information and data. It can preserve supported nullable types more faithfully than records, but check the actual types you exchange. This is not a promise that every custom dtype round-trips.
typed = pd.DataFrame({"order_id": pd.Series(["001", "002"], dtype="string"),
"quantity": pd.Series([2, None], dtype="Int64")})
table_json = typed.to_json(orient="table", index=False)
restored = pd.read_json(StringIO(table_json), orient="table")
print(restored)
print(restored.dtypes)
assert restored["order_id"].tolist() == ["001", "002"]
assert str(restored["quantity"].dtype) == "Int64"Expected output
order_id quantity
0 001 2
1 002 <NA>
order_id string
quantity Int64
dtype: objectRead one JSON object per line 🔝
JSON Lines contains a complete record on each line, rather than one enclosing list. Use lines=True. See the hosted JSON Lines sample for comparison; the practice code generates its own source.
line_text = student_frame.to_json(orient="records", lines=True)
print(line_text.rstrip())
line_frame = pd.read_json(StringIO(line_text), lines=True)
print("Rows:", len(line_frame))
assert line_frame.equals(student_frame)
try:
pd.read_json(StringIO(line_text), lines=False)
except ValueError:
print("Separate line objects need lines=True.")Expected output
{"class":"Four","id":1,"mark":75,"name":"John Deo","sex":"female"}
{"class":"Three","id":2,"mark":85,"name":"Max Ruin","sex":"male"}
{"class":"Three","id":3,"mark":55,"name":"Arnold","sex":"male"}
{"class":"Four","id":4,"mark":60,"name":"Krish Star","sex":"female"}
{"class":"Four","id":5,"mark":60,"name":"John Mike","sex":"female"}
Rows: 5
Separate line objects need lines=True.Limit or process JSON Lines in chunks 🔝
nrows and chunksize require lines=True. Accumulate useful totals rather than collecting all chunks into memory. Other document orientations cannot be streamed this way.
preview = pd.read_json(StringIO(line_text), lines=True, nrows=2)
print(preview)
count = 0
total = 0
with pd.read_json(StringIO(line_text), lines=True, chunksize=2) as reader:
for chunk in reader:
count += len(chunk)
total += chunk["mark"].sum()
print("Rows:", count, "Marks:", total)
assert count == 5 and total == 335Expected output
class id mark name sex
0 Four 1 75 John Deo female
1 Three 2 85 Max Ruin male
Rows: 5 Marks: 335Preserve identifiers and nullable values 🔝
JSON strings and numbers are different values. Store identifiers such as 001 as quoted strings and request a string dtype. null represents missing data. Omitted keys can also produce missing values in the resulting table. See dtypes and missing-value checks.
text = '[{"order_id":"001","quantity":2},{"order_id":"002","quantity":null},{"order_id":"003"}]'
typed = pd.read_json(StringIO(text), orient="records",
dtype={"order_id": "string", "quantity": "Int64"}, convert_dates=False)
print(typed)
assert typed["order_id"].tolist() == ["001", "002", "003"]
assert typed["quantity"].isna().sum() == 2Expected output
order_id quantity
0 001 2
1 002 <NA>
2 003 <NA>Control conversion of row labels 🔝
convert_axes=False preserves text labels in supported orientations. This is separate from dtype, which governs data fields. Choose the intended label type before matching or joining tables.
axis_text = '{"001":{"amount":5},"002":{"amount":8}}'
labels = pd.read_json(StringIO(axis_text), orient="index", convert_axes=False)
print(labels)
print("Labels:", labels.index.tolist())
assert labels.index.tolist() == ["001", "002"]Expected output
amount
001 5
002 8
Labels: ['001', '002']Control date conversion and timestamp units 🔝
convert_dates=False keeps date fields available for explicit validation with to_datetime(). For numeric timestamps, specify the known unit rather than guessing seconds versus milliseconds.
epoch_text = '[{"id":1,"created_at":1704067200000}]'
dated = pd.read_json(StringIO(epoch_text), orient="records",
convert_dates=["created_at"], date_unit="ms")
print(dated)
assert dated.iloc[0]["created_at"] == pd.Timestamp("2024-01-01")
raw_dates = pd.read_json(StringIO('[{"id":1,"date":"2026-01-02"},{"id":2,"date":"bad"}]'),
orient="records", convert_dates=False)
parsed = pd.to_datetime(raw_dates["date"], format="%Y-%m-%d", errors="coerce")
print("Invalid date IDs:", raw_dates.loc[parsed.isna(), "id"].tolist())
assert raw_dates.loc[parsed.isna(), "id"].tolist() == [2]Expected output
id created_at
0 1 2024-01-01
Invalid date IDs: [2]Flatten a nested API response 🔝
read_json() reads compatible tabular JSON; it does not automatically flatten every nested structure. Decode an enclosing response with json.loads(), then use pd.json_normalize() on the record list. This synthetic response has order records inside orders.
payload = {"page": 1, "orders": [
{"order_id": "001", "customer": {"name": "Ravi", "city": "Pune"}, "amount": 5.25},
{"order_id": "002", "customer": {"name": "Raju", "city": "Delhi"}, "amount": 20}]}
response = json.loads(json.dumps(payload))
flat = pd.json_normalize(response["orders"], sep=".")
print(flat)
assert flat["customer.city"].tolist() == ["Pune", "Delhi"]Expected output
order_id amount customer.name customer.city
0 001 5.25 Ravi Pune
1 002 20.00 Raju DelhiDecode UTF-8 JSON and select fields 🔝
encoding applies when decoding bytes. read_json() has no usecols parameter: select fields after importing. With a nested response, extract the intended list first. For CSV column selection, see read_csv().
binary = BytesIO(json.dumps([{"name": "José", "mark": 75}], ensure_ascii=False).encode("utf-8"))
decoded = pd.read_json(binary, orient="records", encoding="utf-8")
print(decoded.loc[:, ["name"]])
assert decoded.iloc[0]["name"] == "José"Expected output
name
0 JoséPractical example: validate imported sales 🔝
The rule for this synthetic extract is a non-blank ID, non-negative numeric amount and valid ISO date. Keep original records for review; missing or invalid amounts are not assumed to be zero.
sales_text = '[{"order_id":"001","date":"2026-01-02","amount":"5.25"},{"order_id":"002","date":"2026-01-03","amount":"20"},{"order_id":"003","date":"bad","amount":"8"},{"order_id":"004","date":"2026-01-04","amount":"bad"}]'
raw = pd.read_json(StringIO(sales_text), orient="records", dtype=False, convert_dates=False)
clean = raw.copy()
clean["order_id"] = raw["order_id"].astype("string").str.strip()
clean["date"] = pd.to_datetime(raw["date"], format="%Y-%m-%d", errors="coerce")
clean["amount"] = pd.to_numeric(raw["amount"], errors="coerce")
valid = clean["order_id"].notna() & clean["order_id"].ne("") & clean["date"].notna() & clean["amount"].ge(0).fillna(False)
accepted = clean.loc[valid].copy()
review = raw.loc[~valid].copy()
print(accepted)
print("Review IDs:", review["order_id"].tolist())
print("Accepted revenue:", accepted["amount"].sum())
assert accepted["order_id"].tolist() == ["001", "002"]
assert accepted["amount"].sum() == 25.25
assert len(accepted) + len(review) == len(raw)Expected output
order_id date amount
0 001 2026-01-02 5.25
1 002 2026-01-03 20.00
Review IDs: ['003', '004']
Accepted revenue: 25.25Export accepted records and check them again 🔝
Use matching orientations and explicit date serialization. Verify identifiers, row counts and totals when passing records to another step. See to_json() and CSV exports.
accepted_json = accepted.to_json(orient="records", date_format="iso")
checked = pd.read_json(StringIO(accepted_json), orient="records",
dtype={"order_id": "string"}, convert_dates=["date"])
print(accepted_json)
print("Checked rows:", len(checked))
assert checked["order_id"].tolist() == ["001", "002"]
assert checked["amount"].sum() == 25.25
# Optional Colab output:
# accepted.to_json("accepted_orders.json", orient="records", date_format="iso")
# from google.colab import files
# files.download("accepted_orders.json")Expected output
[{"order_id":"001","date":"2026-01-02T00:00:00.000","amount":5.25},{"order_id":"002","date":"2026-01-03T00:00:00.000","amount":20.0}]
Checked rows: 2Common questions 🔝
Why does JSON text fail as a path? Wrap text in StringIO. Which orient should I choose? Match the document structure or its writer. Can I read nested data? Extract and normalize the relevant record list. Can I select columns during reading? There is no usecols parameter; select afterwards. Why did a date convert automatically? Date-like labels can trigger default date conversion; use convert_dates=False for explicit handling. Can JSON Lines contain missing values? Yes, use null, then inspect resulting missing fields. Does precise_float give exact decimal arithmetic? No; floating-point decoding precision is distinct from exact financial arithmetic. Can I read compressed files? Supported filename extensions allow compression inference. Why does localhost fail in Colab? It identifies the Colab machine, rather than your Windows server.
Exercise: select high student marks 🔝
Import the student record list, select names and marks for students scoring at least 75, and predict the number of rows.
Exercise solution 🔝
John Deo and Max Ruin meet this rule. loc selects their rows and the two requested fields.
exercise = pd.read_json(StringIO(student_json), orient="records")
answer = exercise.loc[exercise["mark"].ge(75), ["name", "mark"]]
print(answer)
assert answer["name"].tolist() == ["John Deo", "Max Ruin"]Expected output
name mark
0 John Deo 75
1 Max Ruin 85Practice in Google Colab 🔝
Open in Google Colab View on GitHub
Run the examples in order, change JSON structures and sales values, then inspect imported records and review rows. Save your own copy to keep edits. All sample tables are included in the notebook.
Reference: Pandas read_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