Data Preparation and Exploration

TL;DR

Before you can analyze data, you need to clean and transform it, a process called data preparation. After preparing your data, you'll explore it to understand its characteristics, spot patterns, and identify problems. These two steps are crucial for ensuring your analysis is accurate and insightful.

1. The Mental Model

Think of data preparation and exploration like getting ready to cook. First, you gather your ingredients and clean them (preparation). Then, you check what you have, how fresh it is, and imagine what you can make (exploration).

2. The Core Material

Data is rarely perfect when you first get it. You'll almost always need to clean, transform, and understand it before doing any serious analysis. This two-part process—preparation and exploration—lays the groundwork for everything else.

2.1 Data Preparation: Getting Your Data Ready

Data preparation involves several steps to make your data suitable for analysis. It's about ensuring quality, consistency, and usability.

• Handling Missing Values: Missing data (e.g., blank cells, NaN, Null) can mess up your calculations. You'll need to decide how to deal with it:
  • Remove: Delete rows or columns with too many missing values. Be careful not to lose too much valuable data.
  • Impute: Fill in missing values with estimated ones (e.g., using the average, median, or a predictive model).
• Dealing with Duplicates: Identical rows can skew your analysis. Identify and remove them to avoid overcounting.
• Correcting Data Types: Data might be loaded incorrectly (e.g., numbers as text). Ensure each column has the right data type (integer, float, string, date, boolean).
• Standardizing Formats: Inconsistent formatting (e.g., "NY" vs. "New York", "M" vs. "Male") needs to be unified. This also applies to dates and times.
• Handling Outliers: Outliers are data points far from most others. They can be legitimate but unusual, or they can be errors. You might remove them, transform them, or analyze them separately.
• Feature Engineering (Optional but Powerful): Creating new variables from existing ones. For example, combining "month" and "day" to create "season," or calculating "age" from "date of birth."

2.2 Data Exploration: Understanding Your Data

Once your data is prepared, you'll explore it to gain insights, identify patterns, and check for remaining issues. This is often called Exploratory Data Analysis (EDA).

• Descriptive Statistics: Summarize your data using measures like mean, median, mode, standard deviation, variance, minimum, maximum, and counts. This gives you a quick overview of each variable.
• Data Visualization: Graphs and charts help you see patterns and relationships that numbers alone might miss.
  • Histograms/Density Plots: Show the distribution of a single numerical variable.
  • Bar Charts/Pie Charts: Show the distribution of categorical variables.
  • Scatter Plots: Show the relationship between two numerical variables.
  • Box Plots: Show distribution, median, and potential outliers for numerical variables across categories.
  • Heatmaps: Show correlations between multiple numerical variables.
• Identifying Relationships: Look for connections between variables. Does one variable tend to increase when another does? Are certain categories associated with higher values of a numerical variable?

Here's a common flow for data preparation and exploration:

graph TD
    A["Raw Data Collection"] --> B{"Identify Data Source"};
    B --> C["Load Data"];
    C --> D{"Initial Data Overview (Shape, Types, Head)"};
    D --> E{"Handle Missing Values"};
    E --> F{"Remove Duplicates"};
    F --> G{"Correct Data Types"};
    G --> H{"Standardize Formats"};
    H --> I{"Address Outliers"};
    I --> J{"Feature Engineering (if needed)"};
    J --> K{"Descriptive Statistics"};
    K --> L{"Visualize Distributions (Histograms, Bar Charts)"};
    L --> M{"Visualize Relationships (Scatter Plots, Box Plots)"};
    M --> N{"Refine/Iterate Preparation (if issues found)"};
    N --> O["Clean & Explored Data Ready for Analysis"];

2.3 Tools for the Job

In Python, the pandas library is your best friend for data preparation and exploration.
* df.head(), df.info(), df.describe(): Quick summaries.
* df.isnull().sum(): Check missing values per column.
* df.dropna(), df.fillna(): Handle missing data.
* df.drop_duplicates(): Remove duplicates.
* df['column'].astype(): Change data types.
* seaborn and matplotlib.pyplot: Libraries for powerful data visualization.

3. Worked Example

Let's say you have a small dataset of customer orders.

import pandas as pd
import numpy as np

# Sample data with some issues
data = {
    'OrderID': [1, 2, 3, 4, 5, 6, 7, 8, 9, 10],
    'CustomerID': ['A101', 'A102', 'A103', 'A101', 'A104', 'A105', 'A106', 'A107', 'A108', 'A109'],
    'Item': ['Laptop', 'Mouse', 'Keyboard', 'Laptop', 'Monitor', 'Mouse', 'Laptop', 'Webcam', 'Keyboard', 'SSD'],
    'Price': [1200.00, 25.50, 75.00, 1200.00, 300.00, 25.50, 1250.00, 45.00, np.nan, 80.00],
    'Quantity': [1, 2, 1, 1, 1, 2, 1, 1, 1, 1],
    'OrderDate': ['2023-01-15', '1/16/2023', '2023-01-17', '2023-01-15', '2023-01-18', '2023-01-19', '2023-01-20', '2023-01-21', '2023-01-22', '2023-01-23'],
    'Region': ['North', 'South', 'North', 'North', 'West', 'South', 'North', 'East', 'South', 'West'],
    'Discount_Applied': [False, True, False, False, True, False, False, True, False, False]
}
df = pd.DataFrame(data)

print("--- Initial Data ---")
print(df)
print("\n--- Initial Info ---")
df.info()

# --- Data Preparation ---

# 1. Handle Missing Values: 'Price' has one NaN. Let's fill it with the median price.
median_price = df['Price'].median()
df['Price'].fillna(median_price, inplace=True)
print(f"\nFilled missing Price with median: {median_price}")

# 2. Deal with Duplicates: OrderID 1, 2, 3, 4 represent two unique orders (A101 Laptop, A102 Mouse).
# But here, OrderID 1 and 4 have the same CustomerID, Item, Quantity, Price, OrderDate.
# Let's assume (for this example) we want unique *order details*, not just unique OrderIDs.
# In a real scenario, you'd confirm if OrderID 4 is a duplicate or a distinct order.
# For simplicity, we'll check for rows with identical CustomerID, Item, Price, Quantity.
df.drop_duplicates(subset=['CustomerID', 'Item', 'Price', 'Quantity', 'OrderDate'], inplace=True)
print(f"\nRemoved duplicates. New shape: {df.shape}")

# 3. Correct Data Types: 'OrderDate' is object, should be datetime.
df['OrderDate'] = pd.to_datetime(df['OrderDate'])
print("\n--- After Data Type Correction ---")
df.info()

# 4. Feature Engineering: Create a 'TotalCost' column and 'OrderMonth'.
df['TotalCost'] = df['Price'] * df['Quantity']
df['OrderMonth'] = df['OrderDate'].dt.month
print("\n--- After Feature Engineering ---")
print(df.head())

# --- Data Exploration ---

print("\n--- Descriptive Statistics for Numerical Columns ---")
print(df.describe())

print("\n--- Value Counts for Categorical Columns ---")
print("\nItem counts:")
print(df['Item'].value_counts())
print("\nRegion counts:")
print(df['Region'].value_counts())

# A simple visualization (you'd normally use matplotlib/seaborn)
import matplotlib.pyplot as plt
import seaborn as sns

plt.figure(figsize=(10, 5))
sns.histplot(df['TotalCost'], bins=5, kde=True)
plt.title('Distribution of Total Cost')
plt.xlabel('Total Cost')
plt.ylabel('Frequency')
plt.show()

plt.figure(figsize=(8, 4))
sns.boxplot(x='Region', y='TotalCost', data=df)
plt.title('Total Cost by Region')
plt.xlabel('Region')
plt.ylabel('Total Cost')
plt.show()

This code snippet first shows you the raw data and its initial types. Then, it goes through fixing missing prices, removing duplicate order details, correcting the date format, and creating new 'TotalCost' and 'OrderMonth' columns. Finally, it uses describe() and value_counts() to summarize the data and shows two simple plots to explore the distribution of TotalCost and TotalCost by Region.

4. Key Takeaways

• Data preparation is about cleaning and transforming raw data into a usable format for analysis.
• Data exploration (EDA) is about understanding your data's characteristics, patterns, and relationships.
• pandas is the primary Python library for both preparing and exploring tabular data.
• Always check for missing values, duplicates, and correct data types as initial steps.
• Visualizations are powerful tools for uncovering insights and anomalies in your data.
• Feature engineering can create new, more informative variables from existing ones.
• These steps are iterative; you might go back to preparation after exploring.

Common mistakes you should avoid:
- Skipping steps: Thinking your data is clean enough without checking for common issues.
- Blindly deleting data: Removing rows with missing values