Get & Prep Data

By the end of this lesson, you will be able to import raw data files into a Python environment and perform basic cleaning steps to prepare them for analysis.

What it is

Getting and preparing data, often called "data wrangling" or "ETL" (Extract, Transform, Load), is the process of converting raw information from sources like CSVs, Excel sheets, or databases into a structured format suitable for analysis. The primary mental model is that raw data is rarely ready for use; it contains inconsistencies, missing values, and incorrect types that must be resolved before insights can be drawn.

Key related terms include Data Ingestion (loading data), Data Cleaning (fixing errors), and Data Transformation (reshaping data).

Why it matters

• Accuracy: Garbage in, garbage out. Poorly prepared data leads to misleading conclusions.
• Efficiency: Clean data structures allow analytical tools to run faster and more reliably.
• Consistency: Standardizing formats ensures that comparisons across different datasets are valid.
• Interpretability: Properly labeled and typed columns make data easier for humans and algorithms to understand.

Syntax or steps

The most common workflow in Python uses the pandas library. The standard steps are:

1. Import: Use pd.read_csv() or similar functions to load data.
2. Inspect: Check the structure using .head(), .info(), and .describe().
3. Clean: Handle missing values with .dropna() or .fillna().
4. Transform: Change data types or rename columns as needed.

Example

import pandas as pd

# 1. Create a sample raw dataset with issues
raw_data = {
    'Name': ['Alice', 'Bob', None, 'Charlie'],
    'Age': [30, 'twenty-five', 45, 35],
    'Salary': [50000, 60000, 70000, None]
}
df = pd.DataFrame(raw_data)

print("Original Data:")
print(df)

# 2. Inspect data types
print("\nData Types:")
print(df.dtypes)

# 3. Clean: Convert Age to numeric, forcing errors to NaN
df['Age'] = pd.to_numeric(df['Age'], errors='coerce')

# 4. Clean: Fill missing Salary with the mean
mean_salary = df['Salary'].mean()
df['Salary'] = df['Salary'].fillna(mean_salary)

# 5. Clean: Drop rows where Name is missing
df = df.dropna(subset=['Name'])

print("\nCleaned Data:")
print(df)
print("\nFinal Data Types:")
print(df.dtypes)

Explanation:

• pd.to_numeric(..., errors='coerce') attempts to convert strings like 'twenty-five' into numbers. If it fails, it replaces the value with NaN instead of crashing.
• df['Salary'].fillna(mean_salary) replaces missing salary values with the average salary, preserving the row count.
• df.dropna(subset=['Name']) removes any row where the 'Name' column is empty, ensuring every record has an identifier.

Common mistakes

• Ignoring Data Types: Leaving numbers stored as text prevents mathematical operations. Always check .dtypes.
• Deleting Missing Data Blindly: Using .dropna() on all columns might remove valuable records. Target specific columns or impute values instead.
• Hardcoding Values: Manually fixing typos in code is not scalable. Use automated rules or lookup tables for corrections.
• Not Backing Up Raw Data: Always keep a copy of the original file. Preparing data should be a non-destructive process until verified.

When to use it

This approach is best for small-to-medium datasets that fit in memory. For larger datasets, consider database-level processing or distributed frameworks.

Practice

Guided Exercise: Load a CSV file named sales.csv. Check if the 'Date' column is recognized as a datetime object. If not, convert it using pd.to_datetime().

Challenge: Find all rows where the 'Product' name has leading or trailing whitespace. Remove the whitespace using .str.strip() and verify the change.

Quick check

Question: What does the parameter errors='coerce' do in pd.to_numeric()?

Answer: It converts invalid parsing attempts to NaN instead of raising an exception.

Summary

Preparing data is a critical foundation for reliable analytics. By systematically importing, inspecting, and cleaning data using tools like pandas, you ensure that your subsequent analysis is based on accurate and consistent information.