Lesson 9 · Data analytics zero to hero
Pandas Data Wrangling: Reshape & Transform Data | Data Analytics #9
Transforming columns with apply and map, splitting text, binning numbers, and filtering with query. We're continuing with the real, now-cleaned Online…
- CourseData analytics zero to hero
- Lesson9 of 30
- Video11 min
- FormatJupyter notebook · 11 code cells
- Data1 dataset
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.
- online_retail_sample.csv preview132.5 KB
📓 Full notebook
Download .ipynbData Analytics Zero to Hero, Video 9: Pandas Data Wrangling#
- Video nine of the 30-part series: transforming columns with apply and map, splitting text, binning numbers, and filtering with query.
- We're continuing with the real, now-cleaned Online Retail extract from video eight.
- Let's jump straight in.
Before You Start#
- Open a new Jupyter Notebook in VS Code and select your Python interpreter as the kernel.
- Place online_retail_sample.csv in the same folder as this notebook.
Re-Cleaning Quickly#
import pandas as pd
df = pd.read_csv('online_retail_sample.csv')
df = df.dropna(subset=['CustomerID']).drop_duplicates()
df['Description'] = df['Description'].fillna('UNKNOWN ITEM').str.strip()
df = df[(df['Quantity'] > 0) & (df['UnitPrice'] > 0)]
print(df.shape)
Part 1: apply and map#
df['TotalPrice'] = df['Quantity'] * df['UnitPrice']
print(df[['Description', 'Quantity', 'UnitPrice', 'TotalPrice']].head(3))
def price_tier(price):
if price >= 10:
return 'Premium'
elif price >= 3:
return 'Standard'
else:
return 'Budget'
df['PriceTier'] = df['UnitPrice'].apply(price_tier)
print(df['PriceTier'].value_counts())
country_region = {'United Kingdom': 'Europe', 'France': 'Europe', 'Germany': 'Europe', 'Norway': 'Europe', 'EIRE': 'Europe'}
df['Region'] = df['Country'].map(country_region).fillna('Other')
print(df['Region'].value_counts())
Part 2: Splitting and Extracting Text#
df['FirstWord'] = df['Description'].str.split().str[0]
print(df[['Description', 'FirstWord']].head(5))
df['DescLength'] = df['Description'].str.len()
print(df['DescLength'].describe())
Part 3: Binning Numbers with cut and qcut#
bins = [0, 5, 20, 50, float('inf')]
labels = ['Small', 'Medium', 'Large', 'Bulk']
df['OrderSize'] = pd.cut(df['Quantity'], bins=bins, labels=labels)
print(df['OrderSize'].value_counts())
df['SpendQuartile'] = pd.qcut(df['TotalPrice'], q=4, labels=['Q1', 'Q2', 'Q3', 'Q4'])
print(df['SpendQuartile'].value_counts())
Part 4: rename, assign, and query#
df = df.rename(columns={'InvoiceNo': 'InvoiceNumber', 'UnitPrice': 'Price'})
print(df.columns.tolist())
df = df.assign(PricePerUnit=lambda d: d['Price'].round(2), IsBulk=lambda d: d['Quantity'] >= 20)
print(df[['Price', 'PricePerUnit', 'IsBulk']].head(3))
bulk_premium = df.query("IsBulk == True and PriceTier == 'Premium'")
print(bulk_premium.shape[0])
print(bulk_premium[['Description', 'Quantity', 'Price']].head(3))
Wrap-Up: What You Learned#
- apply for custom row-by-row logic, and map for dictionary-based lookups.
- Splitting and measuring text columns with the str accessor.
- Binning numeric columns into categories with cut, for fixed bins, and qcut, for equal-sized groups.
- Renaming columns with rename, chaining new columns with assign, and filtering readably with query.
- All applied to the same real, cleaned retail data from video eight.
- Video ten goes deep on groupby and aggregation, the tool you'll reach for constantly once your data is wrangled into shape. Subscribe so it lands automatically see you there.
🧠 Test yourself
Pick an answer to check it. Your best score is saved in this browser.
What does
df['Country'].map({'France': 'Europe', 'Norway': 'Europe'})return for a country not in the dictionary?map leaves unmatched values as NaN. That's why the lesson chains .fillna('Other').
How does
pd.cutdiffer frompd.qcut?cut uses your own bin boundaries (like 0-5, 5-20), while qcut splits by quantiles so each group holds roughly the same number of rows.
What does
df['Description'].str.split().str[0]return?split() breaks text into a list of words, and .str[0] picks the first item from each list.
Which is the best way to apply your own function
price_tierto every value in a column?Series.apply runs a function on each value and returns a new Series.
What does
df.query("Quantity >= 20 and Country == 'France'")do?query lets you write a filter as a readable string expression.
💻 Practice exercises
Try each one in the notebook or in your own editor before opening the solution.
Create a DataFrame with Quantity and UnitPrice columns for 4 items and add a TotalPrice column. Print it.
💡 Hint
Multiply the two columns.
✅ Show solution
import pandas as pd df = pd.DataFrame({'Quantity': [2, 10, 1, 24], 'UnitPrice': [3.5, 1.25, 12.0, 0.85]}) df['TotalPrice'] = df['Quantity'] * df['UnitPrice'] print(df)
Write a function
price_tier(price)returning 'Premium' (10+), 'Standard' (3-9.99) or 'Budget', apply it to a UnitPrice column, and use map to turn countries into regions (others become 'Other').💡 Hint
Use apply for the function and map(...).fillna('Other').
✅ Show solution
import pandas as pd def price_tier(price): if price >= 10: return 'Premium' elif price >= 3: return 'Standard' else: return 'Budget' df = pd.DataFrame({'UnitPrice': [12.5, 4.0, 0.8, 3.0], 'Country': ['France', 'Brazil', 'Norway', 'Japan']}) df['PriceTier'] = df['UnitPrice'].apply(price_tier) regions = {'France': 'Europe', 'Norway': 'Europe'} df['Region'] = df['Country'].map(regions).fillna('Other') print(df)
Bin Quantity with pd.cut into Small (0-5), Medium (5-20), Large (20-50), Bulk (50+); split TotalPrice into 4 quartiles with qcut; then use rename, assign and query to keep only Bulk-or-Large rows. Print the result.
💡 Hint
Use bins=[0,5,20,50,float('inf')] and df.query("OrderSize in ['Large','Bulk']").
✅ Show solution
import pandas as pd df = pd.DataFrame({ 'Quantity': [2, 10, 30, 80, 4, 25, 12, 60], 'UnitPrice': [1.5, 2.0, 0.5, 1.0, 9.0, 2.5, 3.0, 0.8] }) df['TotalPrice'] = df['Quantity'] * df['UnitPrice'] df['OrderSize'] = pd.cut(df['Quantity'], bins=[0, 5, 20, 50, float('inf')], labels=['Small', 'Medium', 'Large', 'Bulk']) df['SpendQuartile'] = pd.qcut(df['TotalPrice'], q=4, labels=['Q1', 'Q2', 'Q3', 'Q4']) df = df.rename(columns={'UnitPrice': 'Price'}) df = df.assign(IsBulk=lambda d: d['Quantity'] >= 50) big = df.query("OrderSize in ['Large', 'Bulk']") print(big)
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.



