Data Sources & Formats

Jr Codex Data Science Notes

Level: Beginner–Intermediate Prerequisites: Module 1: Statistics & Probability Foundations Time to complete: ~25 minutes

Table of Contents

1. Where Real Data Comes From
2. Flat Files: CSV, JSON, Excel
3. Databases
4. APIs
5. Web Scraping
6. Choosing the Right Source
7. Summary & Next Steps

1. Where Real Data Comes From

Every dataset in a textbook exercise arrives pre-cleaned in a tidy CSV. Real data science work starts before that point — figuring out where the data lives and how to pull it out.

Common Data Sources
─────────────────────────────────────────
  Flat files    → CSV, JSON, Excel exports from other systems
  Databases     → SQL databases (PostgreSQL, MySQL), internal company data
  APIs           → Live data from external services (weather, stock prices, social media)
  Web scraping    → Extracting data from web pages with no API available
  Sensors/logs     → IoT devices, application logs, clickstream data
─────────────────────────────────────────

2. Flat Files: CSV, JSON, Excel

Covered mechanically in the Python Notes (CSV & JSON) — pandas wraps that same functionality with far less code:

import pandas as pd
 
df_csv = pd.read_csv("sales_data.csv")
df_json = pd.read_json("api_response.json")
df_excel = pd.read_excel("quarterly_report.xlsx", sheet_name="Q1")
 
# Reading directly from a URL — no manual download step needed
df_remote = pd.read_csv("https://example.com/data/dataset.csv")

# Common gotchas worth checking immediately after loading any flat file
print(df_csv.dtypes)          # did numeric columns get parsed as numbers, or stuck as strings?
print(df_csv.head())             # does the header row look right, or did parsing go wrong?
print(df_csv.shape)                 # sanity check: is the row/column count what you expected?

3. Databases

Most real company data lives in a relational database, queried with SQL (Chapter 2 covers the query language itself in depth).

import sqlite3
import pandas as pd
 
connection = sqlite3.connect("company.db")
 
query = "SELECT * FROM customers WHERE signup_date >= '2024-01-01'"
df = pd.read_sql(query, connection)
 
connection.close()

# For production systems (PostgreSQL, MySQL), sqlalchemy provides a consistent interface
from sqlalchemy import create_engine
import pandas as pd
 
engine = create_engine("postgresql://user:password@localhost:5432/company_db")
df = pd.read_sql("SELECT * FROM orders", engine)

Why Databases Instead of Files?
─────────────────────────────────────────
  Files:      simple, but no built-in way to query/filter WITHOUT loading everything
  Databases:  can filter/aggregate BEFORE the data ever reaches Python —
               critical once a table has millions of rows
─────────────────────────────────────────

4. APIs

An API (Application Programming Interface) lets you request data from an external service over the internet, typically returning JSON.

import requests
 
response = requests.get("https://api.exchangerate-api.com/v4/latest/USD")
data = response.json()          # parses the JSON response directly into a Python dict
 
print(data["rates"]["EUR"])       # e.g. 0.92 — current USD to EUR exchange rate
 
# Turning an API response into a DataFrame
import pandas as pd
rates_df = pd.DataFrame(list(data["rates"].items()), columns=["currency", "rate"])
print(rates_df.head())

# Most real APIs require authentication via an API key
import requests
 
api_key = "YOUR_API_KEY"
headers = {"Authorization": f"Bearer {api_key}"}
response = requests.get("https://api.example.com/v1/data", headers=headers)
 
if response.status_code == 200:
    data = response.json()
else:
    print(f"Request failed: {response.status_code}")     # error handling, Python Notes Module 3

Practical concerns with APIs: rate limits (most APIs cap how many requests you can make per minute), pagination (large result sets are split across multiple requests), and authentication — all standard parts of working with any real external API.

5. Web Scraping

When no API exists, web scraping extracts data directly from a website's HTML — the last resort, used only when the other options aren't available.

import requests
from bs4 import BeautifulSoup
 
response = requests.get("https://example.com/products")
soup = BeautifulSoup(response.text, "html.parser")
 
product_names = [tag.text.strip() for tag in soup.find_all("h2", class_="product-title")]
prices = [tag.text.strip() for tag in soup.find_all("span", class_="price")]
 
import pandas as pd
df = pd.DataFrame({"product": product_names, "price": prices})

Web Scraping — Practical & Ethical Considerations
─────────────────────────────────────────
  1. Check the site's robots.txt and Terms of Service — many sites
     explicitly disallow scraping.
  2. Prefer an official API if one exists — it's more stable and
     less likely to break when the site's HTML changes.
  3. Add delays between requests — hammering a server with rapid
     requests can get your IP blocked, and is inconsiderate.
  4. Scraped HTML structure changes without notice — expect to
     maintain scraping code more than API-based code.
─────────────────────────────────────────

6. Choosing the Right Source

Decision Guide
─────────────────────────────────────────
  Data already sitting in a company database?      → SQL query (Chapter 2)
  Data available from a documented public/paid API?  → requests + API (Section 4)
  Small, one-off dataset, already exported?           → CSV/JSON/Excel (Section 2)
  No API, but publicly viewable on a webpage?          → Web scraping (Section 5), as a last resort
─────────────────────────────────────────

7. Summary & Next Steps

Key Takeaways

• Real datasets come from flat files, databases, APIs, and (as a last resort) web scraping — each with different reliability and effort trade-offs.
• Databases let you filter/aggregate before pulling data into Python — essential once tables reach millions of rows.
• APIs typically return JSON and require handling authentication, rate limits, and pagination.
• Web scraping should be a last resort — check robots.txt/Terms of Service first, and prefer an official API whenever one exists.

Concept Check

1. Why is querying a database directly often better than pulling an entire table into pandas first?
2. What are two practical concerns to watch for when working with a third-party API?
3. Why is web scraping considered the least reliable data source, long-term?

Next Chapter

→ Chapter 2: SQL for Data Science

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index