Reading JSON Data
What You Will Learn in This Lesson
By the end of this lesson, you will be able to:
- Turn a list of dictionaries into a DataFrame.
- Say what happens to a record that is missing a key.
- Explain why the column order is not guaranteed to be the key order.
- Flatten one level of nesting.
Why This Matters
JSON is what an API gives you, and it is record-shaped rather than column-shaped. pandas bridges the two in one call — but the bridge makes decisions about missing keys and about nesting, and knowing them saves you from a column of dictionaries.
Step 1: Records in, table out
JSON from an API is usually a list of objects, and each object is a row. DataFrame takes that shape directly.
A list of dictionaries
The union of all the keys becomes the columns. Each record becomes a row.
import pandas as pd
records = [
{"name": "Ada", "score": 92},
{"name": "Grace", "score": 88},
]
print(pd.DataFrame(records))From a JSON string
When the JSON arrives as text, parse it first and build from the result. read_json exists too, but going through json.loads keeps the parsing and the table-building as two steps you can inspect separately.
import json
import pandas as pd
text = '[{"name": "Ada", "score": 92}]'
print(pd.DataFrame(json.loads(text)))Mini Practice #1: Records to table
Try It YourselfBuild a table from these records and print its shape and columns.
Step 2: Missing keys become missing values
A record without a key does not break the table. That column simply has a gap on that row.
The gap
The column exists because some record had it. The rows that did not get NaN, and the column widens to float if it was holding whole numbers.
import pandas as pd
records = [{"name": "Ada", "score": 92}, {"name": "Grace"}]
df = pd.DataFrame(records)
print(df)
print(df.dtypes)Nesting does not flatten itself
A value that is itself a dictionary becomes a column of dictionaries, which is almost never what you want. json_normalize flattens it into real columns.
import pandas as pd
records = [{"name": "Ada", "at": {"city": "Leeds", "year": 1843}}]
print(pd.DataFrame(records))
print()
print(pd.json_normalize(records))The flattened names are joined with a dot: at.city. Rename them if a dot in a column name is going to be awkward later.
Mini Practice #2: Flatten the nesting
Try It YourselfBuild this twice — with DataFrame and with json_normalize — and compare the columns.
Step 3: When to Use It
A list of records
JSON from an API is often a list of objects, which becomes a table with one call.
pd.DataFrame(records)Nested objects
json_normalize flattens nested fields into columns with dotted names.
When Not to Use It
Do not load deeply nested JSON straight into a DataFrame and expect columns. Cells full of dictionaries need flattening first.
End-of-Lesson Exercises
Exercise 1: Columns from records
Write from_records(rows) that turns a list of dictionaries into a table and returns its column names, sorted.
pd.DataFrame takes the list of dictionaries directly. sorted(df.columns) then gives plain strings in a predictable order.
Exercise 2: How complete is each record
Write completeness(rows) that builds a table from the records and returns a dictionary mapping each column name to how many rows actually have a value for it.
df.notna().sum() gives a Series of counts per column. Turn it into a dict and make the values plain ints with a comprehension.