Lesson 9 · Data analytics zero to hero

Pandas Data Wrangling: Reshape & Transform Data | Data Analytics #9

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…

⬇ Download notebook ⬇ Notebook + data (.zip)Open in Colab ↗

CourseData analytics zero to hero
Lesson9 of 30
Video11 min
FormatJupyter notebook · 11 code cells
Data1 dataset

pandas

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.csv132.5 KB

📓 Full notebook

Download .ipynb

Data 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)

(1394, 8)

Part 1: apply and map#

df['TotalPrice'] = df['Quantity'] * df['UnitPrice']
print(df[['Description', 'Quantity', 'UnitPrice', 'TotalPrice']].head(3))

                          Description  Quantity  UnitPrice  TotalPrice
0  WHITE HANGING HEART T-LIGHT HOLDER         6       2.55       15.30
1                 WHITE METAL LANTERN         6       3.39       20.34
2      CREAM CUPID HEARTS COAT HANGER         8       2.75       22.00

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())

PriceTier
Budget      989
Standard    372
Premium      33
Name: count, dtype: int64

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())

Region
Europe    1378
Other       16
Name: count, dtype: int64

Part 2: Splitting and Extracting Text#

df['FirstWord'] = df['Description'].str.split().str[0]
print(df[['Description', 'FirstWord']].head(5))

                           Description FirstWord
0   WHITE HANGING HEART T-LIGHT HOLDER     WHITE
1                  WHITE METAL LANTERN     WHITE
2       CREAM CUPID HEARTS COAT HANGER     CREAM
3  KNITTED UNION FLAG HOT WATER BOTTLE   KNITTED
4       RED WOOLLY HOTTIE WHITE HEART.       RED

df['DescLength'] = df['Description'].str.len()
print(df['DescLength'].describe())

count    1394.000000
mean       26.812052
std         5.095834
min         7.000000
25%        23.000000
50%        27.000000
75%        31.000000
max        35.000000
Name: DescLength, dtype: float64

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())

OrderSize
Small     729
Medium    457
Large     167
Bulk       41
Name: count, dtype: int64

df['SpendQuartile'] = pd.qcut(df['TotalPrice'], q=4, labels=['Q1', 'Q2', 'Q3', 'Q4'])
print(df['SpendQuartile'].value_counts())

SpendQuartile
Q1    351
Q3    349
Q4    348
Q2    346
Name: count, dtype: int64

Part 4: rename, assign, and query#

df = df.rename(columns={'InvoiceNo': 'InvoiceNumber', 'UnitPrice': 'Price'})
print(df.columns.tolist())

['InvoiceNumber', 'StockCode', 'Description', 'Quantity', 'InvoiceDate', 'Price', 'CustomerID', 'Country', 'TotalPrice', 'PriceTier', 'Region', 'FirstWord', 'DescLength', 'OrderSize', 'SpendQuartile']

df = df.assign(PricePerUnit=lambda d: d['Price'].round(2), IsBulk=lambda d: d['Quantity'] >= 20)
print(df[['Price', 'PricePerUnit', 'IsBulk']].head(3))

   Price  PricePerUnit  IsBulk
0   2.55          2.55   False
1   3.39          3.39   False
2   2.75          2.75   False

bulk_premium = df.query("IsBulk == True and PriceTier == 'Premium'")
print(bulk_premium.shape[0])
print(bulk_premium[['Description', 'Quantity', 'Price']].head(3))

1
                   Description  Quantity  Price
65  VICTORIAN SEWING BOX LARGE        32  10.95

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.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.

☕ Buy me a coffee Other ways to help