Reading JSON with read_json in Pandas - Mini Project: Build & Apply

Reading JSON with read_json

📖 Scenario: You work in a small bookstore. You receive book data from your supplier in JSON format. You want to load this data into Python to analyze it easily.

🎯 Goal: Load the JSON data into a pandas DataFrame using read_json and display it.

📋 What You'll Learn

Use pandas library

Create a JSON string variable with book data

Use pandas read_json to load the JSON string

Print the resulting DataFrame

💡 Why This Matters

🌍 Real World

Many data sources provide data in JSON format. Loading JSON into pandas helps analyze and visualize data easily.

💼 Career

Data scientists and analysts often need to read JSON data from APIs or files to prepare data for analysis.

1

Create JSON string with book data

Create a variable called json_data and assign it this exact JSON string: '[{"title": "Python Basics", "author": "John Doe", "price": 29.99}, {"title": "Data Science 101", "author": "Jane Smith", "price": 39.99}, {"title": "Machine Learning", "author": "Tom Brown", "price": 49.99}]'

Pandas

# Create the JSON string variable json_data
# Your code here

Hint

Use single quotes outside and double quotes inside for the JSON string.

2

Import pandas library

Import the pandas library with the alias pd by writing import pandas as pd

Pandas

json_data = '[{"title": "Python Basics", "author": "John Doe", "price": 29.99}, {"title": "Data Science 101", "author": "Jane Smith", "price": 39.99}, {"title": "Machine Learning", "author": "Tom Brown", "price": 49.99}]'
# Import pandas as pd
# Your code here

Hint

Use the standard import statement for pandas.

3

Load JSON string into DataFrame

Use pd.read_json with the variable json_data to create a DataFrame called df

Pandas

import pandas as pd
json_data = '[{"title": "Python Basics", "author": "John Doe", "price": 29.99}, {"title": "Data Science 101", "author": "Jane Smith", "price": 39.99}, {"title": "Machine Learning", "author": "Tom Brown", "price": 49.99}]'
# Load json_data into a DataFrame called df
# Your code here

Hint

Use pd.read_json(json_data) to read the JSON string.

4

Print the DataFrame

Print the DataFrame df to see the loaded book data

Pandas

import pandas as pd
json_data = '[{"title": "Python Basics", "author": "John Doe", "price": 29.99}, {"title": "Data Science 101", "author": "Jane Smith", "price": 39.99}, {"title": "Machine Learning", "author": "Tom Brown", "price": 49.99}]'
df = pd.read_json(json_data)
# Print the DataFrame df
# Your code here

Hint

Use print(df) to display the DataFrame.

Practice

1. What does the pandas function read_json do?

easy

A. It loads JSON data into a pandas DataFrame for analysis.

B. It converts a DataFrame into a JSON string.

C. It saves a DataFrame as a CSV file.

D. It reads CSV files into a DataFrame.

Solution

1. Step 1: Understand the purpose of read_json

   The function read_json is designed to load JSON formatted data into a pandas DataFrame.

2. Step 2: Compare with other pandas functions

   Functions like to_json convert DataFrames to JSON, and read_csv reads CSV files, so they do not match the description.

3. Final Answer:

   It loads JSON data into a pandas DataFrame for analysis. -> Option A

4. Quick Check:

   read_json loads JSON to DataFrame = C [OK]

Hint: Remember: read_json reads JSON into DataFrame [OK]

Common Mistakes:

• Confusing read_json with to_json
• Thinking it reads CSV files
• Assuming it saves data instead of loading

2. Which of the following is the correct syntax to read a JSON file named data.json using pandas?

easy

A. pd.read_json(data.json)

B. pd.read_json('data.json')

C. pd.readjson('data.json')

D. pd.read_json['data.json']

Solution

1. Step 1: Check the function name and usage

   The correct pandas function is read_json with an underscore, called as a function with parentheses.

2. Step 2: Validate the argument format

   The file name must be a string inside quotes, so 'data.json' is correct.

3. Final Answer:

   pd.read_json('data.json') -> Option B

4. Quick Check:

   Correct function call with string filename = B [OK]

Hint: Use parentheses and quotes for file name in read_json [OK]

Common Mistakes:

• Missing quotes around filename
• Using wrong function name like readjson
• Using square brackets instead of parentheses

3. Given the JSON string '[{"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}]', what will be the output of pd.read_json(json_str) where json_str holds this string?

medium

A. An error because the JSON string is not a file path.

B. A Series object with the JSON string as a single entry.

C. A DataFrame with columns 'name' and 'age' and two rows for Alice and Bob.

D. A DataFrame with one column containing the entire JSON string.

Solution

1. Step 1: Understand input format

   The JSON string is a list of objects, each with 'name' and 'age' keys, suitable for conversion to a DataFrame.

2. Step 2: Behavior of pd.read_json on JSON string

   Passing a JSON string with list of dicts creates a DataFrame with columns matching keys and rows for each dict.

3. Final Answer:

   A DataFrame with columns 'name' and 'age' and two rows for Alice and Bob. -> Option C

4. Quick Check:

   List of dicts JSON string -> DataFrame rows and columns = A [OK]

Hint: List of dicts JSON string becomes DataFrame rows [OK]

Common Mistakes:

• Expecting error for string input
• Thinking it returns Series instead of DataFrame
• Assuming entire JSON is one column

4. What is wrong with this code snippet?

import pandas as pd
json_data = '{"name": "Alice", "age": 30}\n{"name": "Bob", "age": 25}'
df = pd.read_json(json_data)

medium

A. The function read_json cannot read JSON strings.

B. The JSON string is invalid because it uses double quotes.

C. The variable json_data should be a file path, not a string.

D. The JSON string is multiline but lines=True is missing in read_json.

Solution

1. Step 1: Identify JSON format

   The JSON string contains multiple JSON objects separated by newlines, which is a JSON lines format.

2. Step 2: Check pandas parameter for JSON lines

   To read JSON lines, read_json requires lines=True to parse each line as a JSON object.

3. Final Answer:

   The JSON string is multiline but lines=True is missing in read_json. -> Option D

4. Quick Check:

   Multiline JSON needs lines=True in read_json = D [OK]

Hint: Use lines=True for newline-separated JSON objects [OK]

Common Mistakes:

• Ignoring the need for lines=True with JSON lines
• Thinking double quotes are invalid in JSON
• Assuming read_json only reads files

5. You have a JSON file where data is stored as a dictionary of lists like this:

{"name": ["Alice", "Bob"], "age": [30, 25]}

Which read_json parameter should you use to correctly load this into a DataFrame?

hard

A. orient='columns'

B. orient='index'

C. orient='records'

D. orient='split'

Solution

1. Step 1: Identify JSON structure

   The JSON is a dictionary with keys as column names and values as lists of column data, matching the 'columns' orientation.

2. Step 2: Match pandas orient parameter

   Using orient='columns' tells pandas to interpret keys as columns and values as column data lists.

3. Final Answer:

   orient='columns' -> Option A

4. Quick Check:

   Dict of lists JSON needs orient='columns' = A [OK]

Hint: Use orient='columns' for dict of lists JSON format [OK]

Common Mistakes:

• Using orient='records' which expects list of dicts
• Using orient='index' which expects dict of dicts
• Not specifying orient and getting wrong DataFrame