By the end of this lesson, you will be able to create and manipulate Pandas DataFrames to clean, merge, and group data for analysis.
What it is
Pandas is a powerful Python library for data manipulation and analysis. Its core structures are the Series (a one-dimensional labeled array) and the DataFrame (a two-dimensional table with rows and columns). Think of a DataFrame as an Excel spreadsheet or SQL table within Python. Key operations include filtering, cleaning missing values, merging datasets, and grouping data for aggregation.
Why it matters
- Efficiency: Vectorized operations make data processing significantly faster than pure Python loops.
- Flexibility: Handles mixed data types and complex indexing seamlessly.
- Integration: Works well with NumPy, Matplotlib, and scikit-learn.
- Cleaning: Provides robust tools for handling missing data and outliers.
- Analysis: Simplifies exploratory data analysis through built-in statistical methods.
Syntax or steps
- Import pandas:
import pandas as pd. - Create a DataFrame from a dictionary or load from CSV.
- Clean data using
.dropna(),.fillna(), or type casting. - Merge DataFrames using
.merge()on common keys. - Group data using
.groupby()followed by an aggregation function like.mean()or.sum().
Example
import pandas as pd
# 1. Create sample data
sales_data = {
'date': ['2023-01-01', '2023-01-02', '2023-01-03'],
'product': ['A', 'B', 'A'],
'amount': [100, None, 150]
}
df_sales = pd.DataFrame(sales_data)
inventory_data = {
'product': ['A', 'B', 'C'],
'stock': [50, 30, 20]
}
df_inventory = pd.DataFrame(inventory_data)
# 2. Clean data: Fill missing amounts with 0
df_sales['amount'] = df_sales['amount'].fillna(0)
# 3. Merge sales with inventory on 'product'
df_merged = pd.merge(df_sales, df_inventory, on='product')
# 4. Group by product and calculate total sales amount
df_grouped = df_merged.groupby('product')['amount'].sum().reset_index()
print(df_grouped)This code creates two tables, cleans missing sales figures, joins them based on product name, and calculates total sales per product. The output shows each unique product and its aggregated sales amount.
Common mistakes
- Chained assignment warnings: Avoid modifying slices directly; use
.loc[]or assign new columns explicitly. - Ignoring index alignment: When merging or adding series, ensure indices match or reset them to avoid unexpected NaNs.
- Using loops instead of vectorization: Iterating over rows with
.iterrows()is slow; prefer built-in DataFrame methods. - Forgetting to handle duplicates: Merging can create duplicate rows if keys are not unique; check with
.duplicated().
When to use it
| Scenario | Pandas | SQL/NumPy |
|---|---|---|
| Small-Medium Tabular Data | Ideal for quick exploration and transformation. | Overkill for simple tasks. |
| Large-Scale Distributed Data | Limited by memory. | Use Spark or Dask. |
| Complex Mathematical Ops | Uses NumPy under the hood. | Use NumPy directly for raw arrays. |
Practice
Guided Exercise: Modify the example above to filter only products with stock greater than 25 before grouping.
Challenge: Add a new column 'avg_price' calculated as amount / stock in the merged DataFrame. Handle division by zero safely.
Hint: Use df.loc[df['stock'] > 25] for filtering and np.where or .replace(np.inf, np.nan) for safe division.
Quick check
Q: What method aggregates data after grouping?
A: Methods like .sum(), .mean(), or .agg() are applied to the GroupBy object.
Summary
Pandas provides essential tools for structuring, cleaning, and analyzing tabular data in Python. Mastering Series, DataFrames, merging, and grouping allows for efficient data preparation and insight generation.