Reading JSON with read_json in Pandas - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of pandas' read_json function?

The read_json function reads JSON formatted data and converts it into a pandas DataFrame for easy data analysis.

intermediate

Which parameter in read_json helps to specify the orientation of the JSON data?

The orient parameter specifies the expected format of the JSON string, such as 'records', 'split', 'index', or 'columns'.

beginner

True or False: read_json can read JSON data directly from a URL.

True. read_json can read JSON data from a local file path, a URL, or a JSON string.

intermediate

What does the lines=True argument do in read_json?

It tells pandas to treat each line in the file as a separate JSON object, useful for reading JSON data in 'JSON Lines' format.

advanced

How does read_json handle nested JSON objects by default?

By default, nested JSON objects are loaded as dictionaries or lists inside DataFrame cells; further processing is needed to flatten them.

What does pd.read_json('data.json') return?

A A list of JSON objects

B A JSON string

C A Python dictionary

D A pandas DataFrame

read_json reads JSON data and returns a pandas DataFrame for analysis.

Which orient value is used when JSON data is a list of records (dictionaries)?

A 'records'

B 'index'

C 'split'

D 'columns'

The 'records' orient expects a list of dictionaries, each representing a row.

How do you read a JSON file where each line is a separate JSON object?

A Use <code>lines=True</code>

B Use <code>orient='split'</code>

C Use <code>header=None</code>

D Use <code>index_col=0</code>

The lines=True option reads JSON Lines format correctly.

Can read_json read JSON data directly from a web URL?

A No

B Only if saved locally first

C Yes

D Only with extra libraries

read_json supports URLs as input sources.

What happens if JSON data contains nested objects when using read_json?

A Nested objects are automatically flattened

B Nested objects remain as dictionaries or lists inside cells

C An error is raised

D Nested objects are ignored

Nested JSON objects stay nested inside DataFrame cells; you must flatten them manually if needed.

Explain how to use pandas read_json to load JSON data from a file and what parameters you might adjust.

Think about the source of JSON and its structure.

Describe how pandas handles nested JSON objects when using read_json and how you might deal with them.

Consider what happens inside the DataFrame cells.

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