Mathew K Analytics

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…

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.

📓 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.

🧠 Test yourself

Pick an answer to check it. Your best score is saved in this browser.

  1. What does df['Country'].map({'France': 'Europe', 'Norway': 'Europe'}) return for a country not in the dictionary?

  2. How does pd.cut differ from pd.qcut?

  3. What does df['Description'].str.split().str[0] return?

  4. Which is the best way to apply your own function price_tier to every value in a column?

  5. What does df.query("Quantity >= 20 and Country == 'France'") do?

💻 Practice exercises

Try each one in the notebook or in your own editor before opening the solution.

  1. 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)
    
  2. 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)
    
  3. 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.