Mathew K AnalyticsLesson 1 · Python for Data Analysts
Pandas Zero to Hero: The Complete Beginner-to-Advanced Course
One notebook, start to finish: from your first DataFrame to groupby, merging, pivot tables, and rolling windows. No prior pandas experience needed. Let's…
- CoursePython for Data Analysts
- Lesson1 of 12
- Video57 min
- FormatJupyter notebook · 52 code cells
- Data3 datasets
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- sales.csv8.5 KB
- products.csv682 B
- quarterly_report.xlsx5.8 KB
📓 Full notebook
Download .ipynbPandas Zero to Hero: The Complete Beginner-to-Advanced Course#
- One notebook, start to finish: from your first DataFrame to groupby, merging, pivot tables, and rolling windows.
- No prior pandas experience needed. Let's get straight into it.
Before You Start#
- Open a new Jupyter Notebook in VS Code and select your Python interpreter as the kernel.
- If pandas isn't installed yet, open a terminal in VS Code and run: pip install pandas
- Place sales.csv and products.csv in the same folder as your notebook so pandas can find them with just their file names.
Part 1: Fundamentals#
import pandas as pd
print(pd.__version__)
Series: A Single Labeled Column of Data#
prices = pd.Series([19.99, 59.99, 24.99], index=['Mouse', 'Keyboard', 'Hub'])
print(prices)
print(prices['Keyboard'])
DataFrames: Rows and Columns Together#
data = {
'product': ['Mouse', 'Keyboard', 'Hub'],
'price': [19.99, 59.99, 24.99],
'in_stock': [True, False, True]
}
df = pd.DataFrame(data)
print(df)
Loading Real Data From CSV#
sales = pd.read_csv('sales.csv')
sales.head()
print(sales.shape)
print(sales.columns)
print(sales.dtypes)
sales.info()
sales.describe()
Selecting Columns and Rows#
print(sales['region'].head())
print(sales[['region', 'quantity']].head())
print(sales.loc[0])
print(sales.loc[0:2, ['product_id', 'quantity']])
print(sales.iloc[0])
print(sales.iloc[0:2, 0:3])
Filtering With Boolean Masks#
big_orders = sales[sales['quantity'] >= 5]
print(big_orders.shape)
big_orders.head()
west_big_orders = sales[(sales['quantity'] >= 5) & (sales['region'] == 'West')]
print(west_big_orders.shape)
Sorting#
print(sales.sort_values('quantity', ascending=False).head(3))
print(sales.sort_values(['region', 'quantity']).head(3))
Part 2: Cleaning and Transforming Data#
Finding and Handling Missing Data#
print(sales.isna().sum())
missing_price_rows = sales[sales['unit_price'].isna()]
print(missing_price_rows[['order_id', 'product_id', 'unit_price']])
sales['region'] = sales['region'].fillna('Unknown')
print(sales['region'].isna().sum())
sales_clean = sales.dropna(subset=['unit_price'])
print(sales.shape, sales_clean.shape)
Finding and Removing Duplicates#
print(sales_clean.duplicated().sum())
sales_clean = sales_clean.drop_duplicates()
print(sales_clean.duplicated().sum())
Changing Data Types#
sales_clean['order_date'] = pd.to_datetime(sales_clean['order_date'])
print(sales_clean['order_date'].dtype)
print(sales_clean['order_date'].head())
Creating New Columns#
sales_clean['line_total'] = sales_clean['quantity'] * sales_clean['unit_price']
sales_clean['discounted_total'] = sales_clean['line_total'] * (1 - sales_clean['discount_pct'] / 100)
sales_clean[['quantity', 'unit_price', 'line_total', 'discount_pct', 'discounted_total']].head()
apply() and Lambda Functions#
def size_label(qty):
if qty >= 5:
return 'Bulk'
elif qty >= 2:
return 'Multi'
return 'Single'
sales_clean['order_size'] = sales_clean['quantity'].apply(size_label)
sales_clean['order_size'].value_counts()
sales_clean['is_discounted'] = sales_clean['discount_pct'].apply(lambda pct: pct > 0)
print(sales_clean['is_discounted'].sum())
String Methods With .str#
print(sales_clean['region'].str.upper().head())
print(sales_clean['region'].str.contains('est').head())
Working With Dates Using .dt#
sales_clean['order_month'] = sales_clean['order_date'].dt.month
sales_clean['order_weekday'] = sales_clean['order_date'].dt.day_name()
sales_clean[['order_date', 'order_month', 'order_weekday']].head()
Part 3: Aggregating and Reshaping#
groupby: Splitting, Applying, Combining#
region_totals = sales_clean.groupby('region')['discounted_total'].sum()
print(region_totals)
region_summary = sales_clean.groupby('region').agg(
total_revenue=('discounted_total', 'sum'),
avg_order_size=('quantity', 'mean'),
order_count=('order_id', 'count')
)
print(region_summary)
multi_group = sales_clean.groupby(['region', 'order_size'])['discounted_total'].sum()
print(multi_group.head(8))
Pivot Tables#
pivot = sales_clean.pivot_table(
values='discounted_total',
index='region',
columns='order_size',
aggfunc='sum',
fill_value=0
)
print(pivot)
Merging DataFrames#
products = pd.read_csv('products.csv')
products.head()
sales_with_products = sales_clean.merge(products, on='product_id', how='left')
sales_with_products[['order_id', 'product_id', 'product_name', 'category']].head()
Concatenating DataFrames#
north = sales_with_products[sales_with_products['region'] == 'North']
south = sales_with_products[sales_with_products['region'] == 'South']
combined = pd.concat([north, south])
print(north.shape, south.shape, combined.shape)
Reshaping With melt#
wide = pivot.reset_index()
print(wide.head())
long = wide.melt(id_vars='region', var_name='order_size', value_name='revenue')
print(long.head(6))
Part 4: Advanced Pandas#
MultiIndex: Hierarchical Row Labels#
multi = sales_with_products.groupby(['category', 'region'])['discounted_total'].sum()
print(multi)
print(multi.loc['Accessories'])
print(multi.loc[('Accessories', 'North')])
Method Chaining#
top_categories = (
sales_with_products
.groupby('category')['discounted_total']
.sum()
.sort_values(ascending=False)
.head(3)
)
print(top_categories)
Rolling and Expanding Windows#
daily_revenue = sales_with_products.groupby('order_date')['discounted_total'].sum().sort_index()
daily_revenue = daily_revenue.asfreq('D', fill_value=0)
rolling_7d = daily_revenue.rolling(window=7).mean()
print(rolling_7d.tail(5))
cumulative_revenue = daily_revenue.expanding().sum()
print(cumulative_revenue.tail(3))
Categorical Data for Speed and Memory#
before_memory = sales_with_products['category'].memory_usage(deep=True)
sales_with_products['category'] = sales_with_products['category'].astype('category')
after_memory = sales_with_products['category'].memory_usage(deep=True)
print(f'Before: {before_memory} bytes, After: {after_memory} bytes')
Vectorization vs. apply: Why Speed Matters#
import time
start = time.time()
_ = sales_with_products['quantity'].apply(lambda q: q * 2)
apply_time = time.time() - start
start = time.time()
_ = sales_with_products['quantity'] * 2
vectorized_time = time.time() - start
print(f'apply: {apply_time:.6f}s, vectorized: {vectorized_time:.6f}s')
Exporting Data#
region_summary.to_csv('region_summary.csv')
sales_with_products.to_excel('sales_report.xlsx', index=False, sheet_name='Sales')
print('Files saved.')
Part 5: More Advanced Pandas#
Cross-Tabulation With pd.crosstab#
cross = pd.crosstab(sales_with_products['region'], sales_with_products['category'])
print(cross)
cross_with_totals = pd.crosstab(
sales_with_products['region'],
sales_with_products['category'],
margins=True,
margins_name='Total'
)
print(cross_with_totals)
Binning Continuous Data With cut and qcut#
sales_with_products['price_tier'] = pd.cut(
sales_with_products['unit_price'],
bins=[0, 25, 75, 250],
labels=['Budget', 'Mid-range', 'Premium']
)
print(sales_with_products['price_tier'].value_counts())
sales_with_products['quantity_quartile'] = pd.qcut(sales_with_products['quantity'], q=4, labels=['Q1', 'Q2', 'Q3', 'Q4'])
print(sales_with_products['quantity_quartile'].value_counts())
One-Hot Encoding With pd.get_dummies#
region_dummies = pd.get_dummies(sales_with_products['region'], prefix='region')
print(region_dummies.head())
encoded = pd.concat([sales_with_products[['order_id', 'region']], region_dummies], axis=1)
encoded.head()
groupby().transform(): Broadcasting a Group Result Back#
sales_with_products['region_avg_total'] = sales_with_products.groupby('region')['discounted_total'].transform('mean')
sales_with_products[['region', 'discounted_total', 'region_avg_total']].head()
sales_with_products['above_region_avg'] = sales_with_products['discounted_total'] > sales_with_products['region_avg_total']
print(sales_with_products['above_region_avg'].sum())
groupby().filter(): Keeping or Dropping Entire Groups#
print(sales_with_products['category'].value_counts())
large_categories = sales_with_products.groupby('category', observed=True).filter(lambda g: len(g) >= 100)
print(large_categories['category'].value_counts())
set_index and reindex#
products_indexed = products.set_index('product_id')
print(products_indexed.head())
print(products_indexed.loc['P3'])
wishlist_ids = ['P1', 'P2', 'P9', 'P10']
wishlist = products_indexed.reindex(wishlist_ids)
print(wishlist)
Index Alignment in Arithmetic#
q1_revenue = pd.Series({'North': 4200, 'South': 3100, 'East': 2600})
q2_revenue = pd.Series({'North': 4600, 'South': 3300, 'West': 2900})
print(q1_revenue + q2_revenue)
combined_revenue = (q1_revenue + q2_revenue).fillna(0)
print(combined_revenue)
Capstone Project: A Reusable Sales Report Pipeline#
- Let's wrap nearly everything from this lesson into one function: loading, cleaning, merging, aggregating, and exporting, all in a single reusable call.
- A real analyst rarely runs these steps by hand more than once; they build a pipeline like this and reuse it every time fresh data arrives.
def build_sales_report(sales_path, products_path, output_path):
sales = pd.read_csv(sales_path)
products = pd.read_csv(products_path)
sales['region'] = sales['region'].fillna('Unknown')
sales = sales.dropna(subset=['unit_price'])
sales = sales.drop_duplicates()
sales['order_date'] = pd.to_datetime(sales['order_date'])
sales['line_total'] = sales['quantity'] * sales['unit_price']
sales['discounted_total'] = sales['line_total'] * (1 - sales['discount_pct'] / 100)
merged = sales.merge(products, on='product_id', how='left')
summary = merged.groupby(['category', 'region']).agg(
total_revenue=('discounted_total', 'sum'),
order_count=('order_id', 'count')
).reset_index()
summary.to_excel(output_path, index=False, sheet_name='Summary')
return summary
report = build_sales_report('sales.csv', 'products.csv', 'quarterly_report.xlsx')
report.sort_values('total_revenue', ascending=False).head(10)
Wrap-Up: What You Learned#
- Fundamentals: Series, DataFrames, reading CSV, inspecting data, selecting with loc/iloc, filtering with boolean masks, and sorting.
- Cleaning: missing data, duplicates, dtype conversion, new columns, apply/lambda, string methods, and datetime handling.
- Aggregating and reshaping: groupby, agg, pivot tables, merge, concat, and melt.
- Advanced: MultiIndex, method chaining, rolling and expanding windows, categorical dtype, vectorization versus apply, and exporting to CSV and Excel.
- More advanced: crosstab, cut/qcut binning, get_dummies one-hot encoding, groupby transform and filter, set_index/reindex, and index alignment.
- A capstone pipeline combining nearly all of it into one reusable, real-world function.
- You went from your first Series to a working, reusable data pipeline in one sitting. If you want the next build to land in your feed automatically, subscribing is the move see you in the next one.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
36 minLesson 69
Comprehensive Pandas Tutorial in Python: Data Analysis from Beginner to Advanced
20 minLesson 41
Master Pandas Series: Advanced Slicing & Cross-Section Techniques for Data Analysis
15 minLesson 36
4 - Advanced Dash - Layouts, Callbacks, Hosting
18 minLesson 1