Data Wrangling

CMSC 178DA - Week 03

Noel Jeffrey Pinton
Department of Computer Science
University of the Philippines Cebu

When Bad Data Costs $327 Million

Source: Wikipedia · IEEE Spectrum (Oberg, 1999)

Learning Objectives

Why Data Wrangling?

The Data Wrangling Pipeline

Every analytics project follows this sequence

Data Collection Methods

Philippine Data Sources 🇵🇭

The Philippine Data Challenge

Source: PIDS Press Release on Census Harmonization

SQL Fundamentals

-- Basic SELECT statement
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;

SQL is essential for extracting data from databases in organizations

SELECT: Choosing Columns

-- Select all columns
SELECT * FROM employees;

-- Select specific columns
SELECT first_name, last_name, salary
FROM employees;

-- Select with alias
SELECT first_name AS name, 
       salary * 12 AS annual_salary
FROM employees;

WHERE: Filtering Rows

-- Comparison operators
SELECT * FROM products WHERE price > 1000;

-- Multiple conditions
SELECT * FROM employees 
WHERE department = 'IT' 
  AND salary >= 50000;

-- IN operator
SELECT * FROM regions 
WHERE province IN ('Cebu', 'Bohol', 'Leyte');

-- LIKE for pattern matching
SELECT * FROM customers 
WHERE name LIKE 'Maria%';

ORDER BY & LIMIT

-- Sort ascending (default)
SELECT * FROM products 
ORDER BY price;

-- Sort descending
SELECT * FROM employees 
ORDER BY salary DESC;

-- Multiple columns
SELECT * FROM sales 
ORDER BY region, date DESC;

-- Limit results (top N)
SELECT * FROM products 
ORDER BY price DESC 
LIMIT 10;

Aggregate Functions

-- Common aggregates
SELECT 
    COUNT(*) AS total_employees,
    AVG(salary) AS avg_salary,
    SUM(salary) AS total_payroll,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary
FROM employees;

GROUP BY: Aggregating Groups

-- Group by single column
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- Group by multiple columns
SELECT region, product_category, 
       SUM(sales) AS total_sales
FROM transactions
GROUP BY region, product_category;

-- Filter groups with HAVING
SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

JOINs: Combining Tables

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d
  ON e.dept_id = d.id;

JOIN Example: Philippine Regions

-- Combine province data with regional info
SELECT 
    p.province_name,
    r.region_name,
    p.population,
    p.land_area
FROM provinces p
LEFT JOIN regions r 
    ON p.region_code = r.region_code
WHERE r.island_group = 'Visayas'
ORDER BY p.population DESC;

What is a Subquery?

Subqueries

-- Subquery in WHERE
SELECT * FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

-- Subquery in FROM
SELECT dept, avg_sal
FROM (
    SELECT department AS dept, 
           AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
) AS dept_averages
WHERE avg_sal > 50000;

Case Study: Querying Regional Data

SELECT r.region_name, s.median_salary
FROM salary_data s
JOIN regions r ON s.region_code = r.region_code
WHERE s.median_salary > (
    SELECT AVG(median_salary) FROM salary_data
)
AND r.island_group = 'Visayas'
ORDER BY s.median_salary DESC;

Think About It

Python + SQL Integration

import pandas as pd
import sqlite3

# Connect to database
conn = sqlite3.connect('ph_data.db')

# Execute query and load to DataFrame
query = """
SELECT region, province, population
FROM census_2020
WHERE population > 1000000
ORDER BY population DESC
"""
df = pd.read_sql(query, conn)

# Close connection
conn.close()
print(df.head())

API Data Collection

import requests
import pandas as pd

# PSA OpenSTAT API example
url = "https://openstat.psa.gov.ph/api/data"
params = {
    'indicator': 'population',
    'year': 2020,
    'format': 'json'
}

response = requests.get(url, params=params)
data = response.json()

# Convert to DataFrame
df = pd.DataFrame(data['results'])
print(df.head())

Reading Various File Formats

import pandas as pd

# CSV files
df_csv = pd.read_csv('ph_population.csv')

# Excel files
df_excel = pd.read_excel('census_data.xlsx', 
                          sheet_name='2020')

# JSON files
df_json = pd.read_json('api_response.json')

# Multiple files
import glob
files = glob.glob('data/*.csv')
df = pd.concat([pd.read_csv(f) for f in files])

Lecture 5 Summary

Part 2

Data Cleaning & Transformation

Turning messy reality into analysis-ready data

Learning Objectives

Common Data Quality Issues

Spot the Data Quality Issues

Exploring Data Quality

import pandas as pd

# Load Philippine economic data
df = pd.read_csv('ph_economic_indicators.csv')

# Basic info
print(df.info())

# Missing values
print(df.isnull().sum())

# Unique values per column
print(df.nunique())

# Statistical summary
print(df.describe())

Handling Missing Values

# Check missing values
print(df.isnull().sum())

# Drop rows with any missing
df_clean = df.dropna()

# Drop rows where specific column is missing
df_clean = df.dropna(subset=['income'])

# Fill with constant
df['income'].fillna(0, inplace=True)

# Fill with mean/median
df['income'].fillna(df['income'].median(), inplace=True)

# Forward/backward fill (time series)
df['price'].fillna(method='ffill', inplace=True)

Worked Example: Missing Values

Why Median Beats Mean (Usually)

Philippine Monthly Salary Data (PHP): [8K, 10K, 12K, 13K, 14K, 15K, 18K, 20K, 50K, 500K]

What is Imputation?

Forward Fill: Visual Example

When to Use Each Strategy

Handling Duplicates

# Check for duplicates
print(f"Duplicates: {df.duplicated().sum()}")

# View duplicate rows
print(df[df.duplicated(keep=False)])

# Remove duplicates
df_clean = df.drop_duplicates()

# Remove based on specific columns
df_clean = df.drop_duplicates(
    subset=['id', 'date'], 
    keep='last'
)

Data Type Conversion

# Check current types
print(df.dtypes)

# Convert to numeric
df['price'] = pd.to_numeric(df['price'], errors='coerce')

# Convert to datetime
df['date'] = pd.to_datetime(df['date'], format='%Y-%m-%d')

# Convert to category (memory efficient)
df['region'] = df['region'].astype('category')

# Convert to string
df['code'] = df['code'].astype(str)

String Cleaning

# Remove whitespace
df['name'] = df['name'].str.strip()

# Standardize case
df['city'] = df['city'].str.title()  # "cebu" → "Cebu"
df['code'] = df['code'].str.upper()  # "ph" → "PH"

# Replace values
df['province'] = df['province'].str.replace(
    'Cebu City', 'Cebu'
)

# Extract patterns
df['area_code'] = df['phone'].str.extract(r'\((\d+)\)')

Understanding Outliers

Quantiles, Percentiles, and IQR

Understanding Z-Scores

Outlier Detection

Q1 = df['salary'].quantile(0.25)
Q3 = df['salary'].quantile(0.75)
IQR = Q3 - Q1

lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR

outliers = df[
    (df['salary'] < lower) | 
    (df['salary'] > upper)
]

from scipy import stats

z_scores = stats.zscore(df['salary'])
outliers = df[abs(z_scores) > 3]

Worked Example: IQR Method

Worked Example: Z-Score Method

Handling Outliers

# Option 1: Remove outliers
df_clean = df[
    (df['salary'] >= lower) & 
    (df['salary'] <= upper)
]

# Option 2: Cap outliers (winsorization)
df['salary'] = df['salary'].clip(lower=lower, upper=upper)

# Option 3: Transform (log)
import numpy as np
df['log_salary'] = np.log1p(df['salary'])

# Option 4: Keep but flag
df['is_outlier'] = (df['salary'] < lower) | (df['salary'] > upper)

Binning: Continuous to Categorical

One-Hot & Label Encoding

Data Transformation

# Create new columns
df['gdp_per_capita'] = df['gdp'] / df['population']

# Binning continuous variables
df['age_group'] = pd.cut(
    df['age'], 
    bins=[0, 18, 35, 50, 65, 100],
    labels=['Child', 'Young Adult', 'Adult', 'Middle Age', 'Senior']
)

# One-hot encoding
df_encoded = pd.get_dummies(df, columns=['region'])

# Label encoding
df['region_code'] = df['region'].astype('category').cat.codes

Pivot vs Melt: Visual

Reshaping: Pivot & Melt

# Wide to long (melt)
df_long = pd.melt(
    df, 
    id_vars=['region', 'year'],
    value_vars=['q1_sales', 'q2_sales', 'q3_sales', 'q4_sales'],
    var_name='quarter',
    value_name='sales'
)

# Long to wide (pivot)
df_wide = df_long.pivot_table(
    index='region',
    columns='year',
    values='sales',
    aggfunc='sum'
)

Merging DataFrames

# Inner merge (SQL INNER JOIN)
df_merged = pd.merge(df1, df2, on='id', how='inner')

# Left merge (SQL LEFT JOIN)
df_merged = pd.merge(
    population_df, 
    income_df,
    left_on='province_code',
    right_on='prov_id',
    how='left'
)

# Concatenate (stack)
df_combined = pd.concat([df_2020, df_2021, df_2022])

Complete Cleaning Pipeline

def clean_philippine_data(df):
    """Standard cleaning pipeline for PH datasets"""
    # 1. Remove duplicates
    df = df.drop_duplicates()
    
    # 2. Standardize column names
    df.columns = df.columns.str.lower().str.replace(' ', '_')
    
    # 3. Handle missing values
    df['income'] = df['income'].fillna(df['income'].median())
    
    # 4. Standardize text
    df['province'] = df['province'].str.title().str.strip()
    
    # 5. Convert types
    df['date'] = pd.to_datetime(df['date'])
    
    # 6. Remove outliers
    df = df[df['age'].between(0, 120)]
    
    return df

Your Turn: Write the Pipeline

Timeline of Philippine Data Challenges

Why Philippine datasets are harder to clean than most

Case Study: Cleaning PSA Data

# 1. Region names inconsistent (abbreviations vs full names)
region_mapping = {
    'NCR': 'National Capital Region',
    'CAR': 'Cordillera Administrative Region',
    'BARMM': 'Bangsamoro Autonomous Region'
}
df['region'] = df['region'].replace(region_mapping)

# 2. Numbers stored with commas as strings
df['population'] = df['population'].str.replace(',', '').astype(int)

# 3. Date formats vary (day-first is common in PH)
df['census_date'] = pd.to_datetime(df['date'], dayfirst=True)

Data Validation

def validate_data(df):
    """Validate cleaned data"""
    assert df.duplicated().sum() == 0, "Duplicates found!"
    assert df.isnull().sum().sum() == 0, "Missing values found!"
    assert df['age'].between(0, 120).all(), "Invalid ages!"
    assert df['population'].ge(0).all(), "Negative population!"
    
    print("✅ All validations passed!")
    return True

# Run validation
validate_data(df_clean)

Lecture 6 Summary

Week 3 Complete!

Remember: NASA lost $327M because someone didn't convert units. Samsung lost $105B because someone picked the wrong dropdown. The skills you learned this week prevent those disasters.

Next Week: Exploratory Data Analysis

Lab 3: Data Wrangling Practice