9 minutes·Lesson 6 of 4

Working with Real Datasets

From Practice Datasets to Real Data

Learning pandas on clean, small datasets is very different from working with real data in a professional environment. Real datasets come from multiple sources, have inconsistent formats, contain hidden quality issues, and are often much larger than practice datasets.

This lesson covers the practical skills needed to work effectively with real-world data.

Finding and Loading Real Data

Public Datasets for Practice

Kaggle (kaggle.com/datasets): Thousands of real datasets across every domain. Free account required.

Our World in Data (ourworldindata.org): High-quality global datasets on health, economics, education, and environment.

Google Dataset Search (datasetsearch.research.google.com): Search across all publicly available datasets.

Data.gov / Data.gov.uk: Government data for US and UK respectively.

The World Bank / IMF / UN: Economic and development data.

Twitter/X API, Reddit API: Social media data (requires API access).

Loading Data from Multiple Sources

import pandas as pd
import requests

# From a URL directly
df = pd.read_csv('https://example.com/data.csv')

# From a zip file
df = pd.read_csv('data.zip')  # pandas handles zip automatically

# From JSON (common API response format)
df = pd.read_json('data.json')
df = pd.json_normalize(nested_json_data)  # for nested JSON

# From a database (requires sqlalchemy)
from sqlalchemy import create_engine
engine = create_engine('postgresql://user:password@host:5432/database')
df = pd.read_sql('SELECT * FROM orders WHERE order_date >= 2024-01-01', engine)

# From an API
response = requests.get('https://api.example.com/data', headers={'Authorization': 'Bearer TOKEN'})
data = response.json()
df = pd.DataFrame(data['results'])

# From multiple CSV files (combine into one DataFrame)
import glob
files = glob.glob('data/monthly_*.csv')
df = pd.concat([pd.read_csv(f) for f in files], ignore_index=True)

Handling Large Datasets

When data is too large to fit in memory:

# Read in chunks
chunk_size = 100000
chunks = []
for chunk in pd.read_csv('large_file.csv', chunksize=chunk_size):
    # Process each chunk (e.g., filter or aggregate)
    filtered = chunk[chunk['amount'] > 100]
    chunks.append(filtered)
df = pd.concat(chunks, ignore_index=True)

# Read only specific columns (reduces memory usage)
df = pd.read_csv('large_file.csv', usecols=['order_id', 'date', 'amount'])

# Use efficient data types
df = pd.read_csv('data.csv', dtype={'status': 'category', 'order_id': 'int32'})

# Check memory usage
df.info(memory_usage='deep')
df.memory_usage(deep=True).sum() / 1024**2  # MB

Working with APIs

Many real-world datasets come from APIs. Here is a practical example:

import requests
import pandas as pd

def fetch_data_from_api(endpoint, api_key, params=None):
    """Fetch data from a REST API and return as DataFrame."""
    headers = {'Authorization': f'Bearer {api_key}'}
    response = requests.get(endpoint, headers=headers, params=params)
    response.raise_for_status()  # raises an error for 4xx/5xx status codes
    data = response.json()
    return pd.DataFrame(data.get('results', data))

# Example: fetch and process
df = fetch_data_from_api(
    'https://api.example.com/orders',
    api_key='your_api_key',
    params={'start_date': '2024-01-01', 'limit': 1000}
)

Working with Messy Real Data: A Case Study

Real data typically has multiple simultaneous issues. Here is a realistic cleaning workflow:

import pandas as pd
import numpy as np

# Load the raw data
df = pd.read_csv('raw_orders.csv')
initial_rows = len(df)
print(f"Loaded {initial_rows} rows")

# Document all issues found
issues = {}

# 1. Check for and remove duplicates
dupes = df.duplicated(subset=['order_id']).sum()
issues['duplicates'] = dupes
df = df.drop_duplicates(subset=['order_id'])

# 2. Fix data types
df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')
df['amount'] = pd.to_numeric(df['amount'], errors='coerce')

# 3. Count and handle missing values
missing = df.isnull().sum()
issues['missing_values'] = missing.to_dict()

df['region'] = df['region'].fillna('Unknown')
df = df.dropna(subset=['order_id', 'order_date', 'amount'])

# 4. Remove invalid records
invalid_amounts = (df['amount'] <= 0).sum()
issues['invalid_amounts'] = invalid_amounts
df = df[df['amount'] > 0]

# 5. Standardise text
df['country'] = df['country'].str.strip().str.title()
df['status'] = df['status'].str.lower().str.strip()

# 6. Add derived columns
df['year'] = df['order_date'].dt.year
df['month'] = df['order_date'].dt.month
df['quarter'] = df['order_date'].dt.quarter

# Cleaning summary
print(f"\nCleaning Summary:")
print(f"Original rows: {initial_rows}")
print(f"Final rows: {len(df)}")
print(f"Rows removed: {initial_rows - len(df)} ({(initial_rows - len(df))/initial_rows*100:.1f}%)")
print(f"\nIssues found: {issues}")

Combining Data from Multiple Sources

Real analysis often requires combining data from different systems:

# Load data from multiple sources
orders = pd.read_csv('orders.csv')
customers = pd.read_sql('SELECT * FROM customers', engine)
products = pd.read_excel('product_catalogue.xlsx')

# Join them together
analysis = (orders
    .merge(customers[['customer_id', 'country', 'segment']], on='customer_id', how='left')
    .merge(products[['product_id', 'category', 'brand']], on='product_id', how='left')
)

# Validate the join: check for unexpected nulls after join
for col in ['country', 'segment', 'category']:
    nulls = analysis[col].isnull().sum()
    if nulls > 0:
        print(f"Warning: {nulls} rows have null {col} after join")

Automating Recurring Analysis

For analysis that runs regularly, wrap everything in a parameterised function:

def run_monthly_analysis(year, month):
    """Run monthly sales analysis for the specified period."""
    # Load data
    df = load_and_clean_data('orders.csv')

    # Filter to the specified month
    monthly = df[
        (df['order_date'].dt.year == year) &
        (df['order_date'].dt.month == month)
    ]

    # Produce summary
    summary = monthly.groupby('country').agg(
        revenue=('amount', 'sum'),
        orders=('order_id', 'count'),
        avg_order=('amount', 'mean')
    ).round(2)

    # Export
    filename = f'analysis_{year}_{month:02d}.xlsx'
    summary.to_excel(filename)
    print(f"Analysis saved to {filename}")

    return summary

# Run for the current month
import datetime
today = datetime.date.today()
run_monthly_analysis(today.year, today.month)

Key Takeaways

Real datasets require loading from diverse sources: CSV, JSON, APIs, databases, and multiple files combined with pd.concat().
For large files, use chunksize parameter and usecols to limit memory usage; specify efficient dtypes on load.
Always write a cleaning summary: how many rows were in the raw data, how many were removed and why, what quality issues were found.
Validate joins after merging: check for unexpected nulls in columns from the joined table that indicate unmatched records.
Wrap recurring analysis in parameterised functions for reproducibility and automation.

Practice Exercise

Find a real dataset on Kaggle or Our World in Data. Perform a complete end-to-end analysis:

Load the data and document its source, size, and time period
Perform a full cleaning workflow and document every issue found
Perform EDA with at least 4 visualisations
Answer one specific analytical question from the data
Export your results to an Excel file with a summary sheet and a detail sheet
Write a 200-word summary of your findings

Submit the Jupyter notebook and the Excel output.

Try it yourself

Preview

Key Takeaways

Real data comes from diverse sources: CSV files, APIs, databases, and multiple files that must be combined with pd.concat().
Use chunksize in pd.read_csv() and usecols to handle files too large to load into memory completely.
Always write a cleaning summary documenting original row count, rows removed, and quality issues found and resolved.
Validate joins after merging by checking for unexpected null values in joined columns -- unmatched records silently distort analysis.
Wrap recurring analysis in parameterised functions for reproducibility, maintainability, and the ability to run the same analysis for any period.

Quick Quiz

1.What does pd.concat([df1, df2, df3], ignore_index=True) do?

2.What is the purpose of the chunksize parameter in pd.read_csv()?

3.After merging two DataFrames, why should you validate the join by checking for unexpected null values?

4.What is the key advantage of wrapping recurring analysis in a parameterised Python function?

← Data Visualisation with Python Next: Principles of Data Visualisation →

Ready to go further?

CareerEx gives you structured 12-week training, live classes every Saturday and Sunday, real tutor feedback, and a certificate. Join the next cohort.

Join CareerEx

9 minutes·Lesson 6 of 4

Working with Real Datasets

From Practice Datasets to Real Data

This lesson covers the practical skills needed to work effectively with real-world data.

Finding and Loading Real Data

Public Datasets for Practice

Kaggle (kaggle.com/datasets): Thousands of real datasets across every domain. Free account required.

Our World in Data (ourworldindata.org): High-quality global datasets on health, economics, education, and environment.

Google Dataset Search (datasetsearch.research.google.com): Search across all publicly available datasets.

Data.gov / Data.gov.uk: Government data for US and UK respectively.

The World Bank / IMF / UN: Economic and development data.

Twitter/X API, Reddit API: Social media data (requires API access).

Loading Data from Multiple Sources

import pandas as pd
import requests

# From a URL directly
df = pd.read_csv('https://example.com/data.csv')

# From a zip file
df = pd.read_csv('data.zip')  # pandas handles zip automatically

# From JSON (common API response format)
df = pd.read_json('data.json')
df = pd.json_normalize(nested_json_data)  # for nested JSON

# From a database (requires sqlalchemy)
from sqlalchemy import create_engine
engine = create_engine('postgresql://user:password@host:5432/database')
df = pd.read_sql('SELECT * FROM orders WHERE order_date >= 2024-01-01', engine)

# From an API
response = requests.get('https://api.example.com/data', headers={'Authorization': 'Bearer TOKEN'})
data = response.json()
df = pd.DataFrame(data['results'])

# From multiple CSV files (combine into one DataFrame)
import glob
files = glob.glob('data/monthly_*.csv')
df = pd.concat([pd.read_csv(f) for f in files], ignore_index=True)

Handling Large Datasets

When data is too large to fit in memory:

# Read in chunks
chunk_size = 100000
chunks = []
for chunk in pd.read_csv('large_file.csv', chunksize=chunk_size):
    # Process each chunk (e.g., filter or aggregate)
    filtered = chunk[chunk['amount'] > 100]
    chunks.append(filtered)
df = pd.concat(chunks, ignore_index=True)

# Read only specific columns (reduces memory usage)
df = pd.read_csv('large_file.csv', usecols=['order_id', 'date', 'amount'])

# Use efficient data types
df = pd.read_csv('data.csv', dtype={'status': 'category', 'order_id': 'int32'})

# Check memory usage
df.info(memory_usage='deep')
df.memory_usage(deep=True).sum() / 1024**2  # MB

Working with APIs

Many real-world datasets come from APIs. Here is a practical example:

import requests
import pandas as pd

def fetch_data_from_api(endpoint, api_key, params=None):
    """Fetch data from a REST API and return as DataFrame."""
    headers = {'Authorization': f'Bearer {api_key}'}
    response = requests.get(endpoint, headers=headers, params=params)
    response.raise_for_status()  # raises an error for 4xx/5xx status codes
    data = response.json()
    return pd.DataFrame(data.get('results', data))

# Example: fetch and process
df = fetch_data_from_api(
    'https://api.example.com/orders',
    api_key='your_api_key',
    params={'start_date': '2024-01-01', 'limit': 1000}
)

Working with Messy Real Data: A Case Study

Real data typically has multiple simultaneous issues. Here is a realistic cleaning workflow:

import pandas as pd
import numpy as np

# Load the raw data
df = pd.read_csv('raw_orders.csv')
initial_rows = len(df)
print(f"Loaded {initial_rows} rows")

# Document all issues found
issues = {}

# 1. Check for and remove duplicates
dupes = df.duplicated(subset=['order_id']).sum()
issues['duplicates'] = dupes
df = df.drop_duplicates(subset=['order_id'])

# 2. Fix data types
df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')
df['amount'] = pd.to_numeric(df['amount'], errors='coerce')

# 3. Count and handle missing values
missing = df.isnull().sum()
issues['missing_values'] = missing.to_dict()

df['region'] = df['region'].fillna('Unknown')
df = df.dropna(subset=['order_id', 'order_date', 'amount'])

# 4. Remove invalid records
invalid_amounts = (df['amount'] <= 0).sum()
issues['invalid_amounts'] = invalid_amounts
df = df[df['amount'] > 0]

# 5. Standardise text
df['country'] = df['country'].str.strip().str.title()
df['status'] = df['status'].str.lower().str.strip()

# 6. Add derived columns
df['year'] = df['order_date'].dt.year
df['month'] = df['order_date'].dt.month
df['quarter'] = df['order_date'].dt.quarter

# Cleaning summary
print(f"\nCleaning Summary:")
print(f"Original rows: {initial_rows}")
print(f"Final rows: {len(df)}")
print(f"Rows removed: {initial_rows - len(df)} ({(initial_rows - len(df))/initial_rows*100:.1f}%)")
print(f"\nIssues found: {issues}")

Combining Data from Multiple Sources

Real analysis often requires combining data from different systems:

# Load data from multiple sources
orders = pd.read_csv('orders.csv')
customers = pd.read_sql('SELECT * FROM customers', engine)
products = pd.read_excel('product_catalogue.xlsx')

# Join them together
analysis = (orders
    .merge(customers[['customer_id', 'country', 'segment']], on='customer_id', how='left')
    .merge(products[['product_id', 'category', 'brand']], on='product_id', how='left')
)

# Validate the join: check for unexpected nulls after join
for col in ['country', 'segment', 'category']:
    nulls = analysis[col].isnull().sum()
    if nulls > 0:
        print(f"Warning: {nulls} rows have null {col} after join")

Automating Recurring Analysis

For analysis that runs regularly, wrap everything in a parameterised function:

def run_monthly_analysis(year, month):
    """Run monthly sales analysis for the specified period."""
    # Load data
    df = load_and_clean_data('orders.csv')

    # Filter to the specified month
    monthly = df[
        (df['order_date'].dt.year == year) &
        (df['order_date'].dt.month == month)
    ]

    # Produce summary
    summary = monthly.groupby('country').agg(
        revenue=('amount', 'sum'),
        orders=('order_id', 'count'),
        avg_order=('amount', 'mean')
    ).round(2)

    # Export
    filename = f'analysis_{year}_{month:02d}.xlsx'
    summary.to_excel(filename)
    print(f"Analysis saved to {filename}")

    return summary

# Run for the current month
import datetime
today = datetime.date.today()
run_monthly_analysis(today.year, today.month)

Key Takeaways

Real datasets require loading from diverse sources: CSV, JSON, APIs, databases, and multiple files combined with pd.concat().
For large files, use chunksize parameter and usecols to limit memory usage; specify efficient dtypes on load.
Always write a cleaning summary: how many rows were in the raw data, how many were removed and why, what quality issues were found.
Validate joins after merging: check for unexpected nulls in columns from the joined table that indicate unmatched records.
Wrap recurring analysis in parameterised functions for reproducibility and automation.

Practice Exercise

Find a real dataset on Kaggle or Our World in Data. Perform a complete end-to-end analysis:

Load the data and document its source, size, and time period
Perform a full cleaning workflow and document every issue found
Perform EDA with at least 4 visualisations
Answer one specific analytical question from the data
Export your results to an Excel file with a summary sheet and a detail sheet
Write a 200-word summary of your findings

Submit the Jupyter notebook and the Excel output.

Try it yourself

Preview

Key Takeaways

Real data comes from diverse sources: CSV files, APIs, databases, and multiple files that must be combined with pd.concat().
Use chunksize in pd.read_csv() and usecols to handle files too large to load into memory completely.
Always write a cleaning summary documenting original row count, rows removed, and quality issues found and resolved.
Validate joins after merging by checking for unexpected null values in joined columns -- unmatched records silently distort analysis.
Wrap recurring analysis in parameterised functions for reproducibility, maintainability, and the ability to run the same analysis for any period.

Quick Quiz

1.What does pd.concat([df1, df2, df3], ignore_index=True) do?

2.What is the purpose of the chunksize parameter in pd.read_csv()?

3.After merging two DataFrames, why should you validate the join by checking for unexpected null values?

4.What is the key advantage of wrapping recurring analysis in a parameterised Python function?

← Data Visualisation with Python Next: Principles of Data Visualisation →

Ready to go further?

CareerEx gives you structured 12-week training, live classes every Saturday and Sunday, real tutor feedback, and a certificate. Join the next cohort.

Join CareerEx