Introduction

In this lesson, we delve into handling JSON and HTML data formats with Pandas. While CSV and Excel files are commonly used in data manipulation, JSON and HTML are equally important, especially when dealing with web data and APIs. We'll explore how to efficiently load JSON data, read HTML tables into DataFrames, export DataFrames to JSON, and process nested JSON structures. By mastering these operations, you can expand your data processing capabilities and work more effectively with diverse data sources.

Understanding JSON and HTML Data Structures

JSON (JavaScript Object Notation) is a lightweight data-interchange format that's easy for humans to read and write. It is characterized by its key-value pair structure, making it ideal for representing complex data hierarchies.

HTML (HyperText Markup Language) is a standard markup language used to create web pages. Data from web pages can often be extracted from HTML tables.

Loading JSON Data with Pandas

Pandas simplifies the process of reading JSON files with the read_json function. This functionality can handle both local files and URLs, making it versatile for API data retrieval.

import pandas as pd

# Load a JSON file
json_df = pd.read_json('data.json')

# Load JSON from a URL
url = 'https://api.example.com/data.json'
json_url_df = pd.read_json(url)

Handling Nested JSON Data in Pandas

Nested JSON structures require special attention. Pandas can automatically flatten simple, non-nested JSON objects. For deeply nested data, the json_normalize function is useful.

import json
from pandas import json_normalize

# Example of nested JSON data
nested_json = {
    "name": "John",
    "details": {
        "age": 30,
        "city": "New York"
    }
}

# Flatten the nested JSON
flat_df = json_normalize(nested_json)

Reading HTML Tables into DataFrames

Pandas offers a straightforward method to read HTML tables using the read_html function. This function retrieves all tables in a page and returns a list of DataFrames.

url = 'https://example.com/data_tables.html'
html_tables = pd.read_html(url)

# Access the first table
first_table_df = html_tables[0]

Exporting DataFrames to JSON Format

Exporting DataFrame objects to JSON format is efficiently managed by the to_json method, offering flexibility in formatting the output JSON.

exported_json = json_df.to_json('output.json', orient='records', lines=True)

• orient='records' creates a list-like structure for each row in the DataFrame
• lines=True outputs the JSON in a line-delimited format, useful for streaming data

Summary / Key Takeaways

• Pandas' read_json function efficiently loads JSON data from files and URLs.
• Use json_normalize to handle and flatten nested JSON structures.
• The read_html function extracts HTML tables into DataFrames, suitable for web scraping.
• DataFrames can be exported to JSON format using to_json with customizable options.
• Working with JSON and HTML extends your ability to handle diverse data sources in Pandas.