Extracting Live API Data into a Pandas DataFrame

To fetch live JSON data from a public Representational State Transfer Application Programming Interface (REST API) and convert it directly into a Pandas DataFrame, use the Python requests library combined with pd.json_normalize(). Run this snippet instantly inside Google Colab:

import requests
import pandas as pd

# Fetch live public exchange rate data endpoint
url = "https://api.exchangerate-api.com/v4/latest/USD"
response = requests.get(url)

# Check for a successful HTTP response
if response.status_code == 200:
    data = response.json()
    
    # Extract rates dictionary and convert to DataFrame
    df = pd.DataFrame(list(data['rates'].items()), columns=['Currency', 'Rate'])
    
    # Display the first few rows
    print(df.head())
else:
    print(f"Error fetching data: {response.status_code}")

Prerequisites & Required Libraries Top ↑

Before running data pipelines in Google Colab, ensure you have the required libraries installed. Colab comes pre-installed with Pandas, but you may need requests or specialized financial wrappers if working with restricted endpoints.

!pip install --upgrade requests pandas

Handling Nested JSON Structures Top ↑

Real-world financial and economic APIs often return multi-level nested dictionaries or lists. Standard pd.DataFrame(data) will fail or produce messy columns when faced with nested structures. Use pd.json_normalize() to flatten them automatically.

# Example parsing a nested JSON payload
nested_data = [
    {"market": {"region": "US", "code": "NYSE"}, "asset": "AAPL", "price": 185.5},
    {"market": {"region": "US", "code": "NASDAQ"}, "asset": "MSFT", "price": 420.2}
]

df_flat = pd.json_normalize(nested_data)
print(df_flat)

HTTP Error Handling & Edge Cases Top ↑

Production scripts must handle network failures, timeouts, and malformed API responses gracefully using try-except blocks:

import requests
import pandas as pd

url = "https://api.exchangerate-api.com/v4/latest/USD"

try:
    response = requests.get(url, timeout=10)
    response.raise_for_status()  # Raises HTTPError for bad responses
    data = response.json()
    df = pd.DataFrame(list(data['rates'].items()), columns=['Currency', 'Rate'])
except requests.exceptions.HTTPError as err:
    print(f"HTTP error occurred: {err}")
except requests.exceptions.Timeout:
    print("The request timed out.")
except Exception as err:
    print(f"An error occurred: {err}")

Saving Extracted Data (SQLite, MySQL, CSV, Excel) Top ↑

Once your live API data is successfully parsed and loaded into a Pandas DataFrame, you can persist the records across multiple storage formats and database backends:

• SQLite Database: Use pandas to_sql() with SQLAlchemy or SQLite connections to store tables locally.
• MySQL Database: Connect and write tables using pandas MySQL to_sql() or MySQL insert guides.
• CSV Files: Export tabular data to flat files using pandas to_csv().
• Excel Spreadsheets: Save formatted workbooks using pandas to_excel().

# Example: Exporting DataFrame to CSV and SQLite
import sqlite3

# 1. Save to CSV
df.to_csv("exchange_rates.csv", index=False)

# 2. Save to SQLite database
conn = sqlite3.connect("financial_data.db")
df.to_sql("usd_rates", conn, if_exists="replace", index=False)
conn.close()
print("Data successfully saved to CSV and SQLite!")

Interactive Companion Jupyter Notebook

Explore live API extraction code cells, test error-handling edge cases, and run self-validating data cleaning assertions in Google Colab.

View on GitHub

Frequently Asked Questions Top ↑

Related Python & Pandas Tutorials

• Pandas Input and Output Guide
• Pandas Live Weather data
• Github Live API Data — Create and save from DataFrames.
• Pandas DataFrame Basics & Indexing
• Working with Python Datetime & Relativedelta
• Getting Started with Google Colab Notebooks

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.

About the author   Watch on YouTube

Connect: