Programming for Data Processing

Lectures: Nguyễn Tuấn Phong, Đào Việt Anh

Labs: Trịnh Ngọc Huỳnh, Phạm Tiến Du

Semester: 2025-2026-1

Institute for AI, VNU-UET

Lecture 7
Data Loading & Cleaning

Today

Data Loading and Storage →
Data Cleaning and Preparation →

1.
Data Loading and Storage

Overview

Loading data from various file formats
Storing data in different formats
Best practices for data loading and storage

Outline

CSV/TSV — delimited text tables ⭐️ →
JSON — web/APIs; can be nested ⭐️ →
Excel (.xlsx) — spreadsheets →
Some notes on data loading →

1.1.
CSV:
Comma-Separated Values ⭐️

CSV — The Workhorse

Read delimited text into a DataFrame


!cat examples/ex1.csv

a,b,c
1,2,3
4,5,6


import pandas as pd
pd.read_csv("examples/ex1.csv")

   a  b  c
0  1  2  3
1  4  5  6

CSV — Common Options

names=/header= for column labels
index_col= sets the row index
usecols= selects columns early
dtype=, parse_dates=, na_values=


!cat examples/ex2.csv

id,date,val,extra
1,2024-01-01,10,foo
2,2024-01-02,NA,bar


pd.read_csv(
    "examples/ex2.csv", usecols=["id", "date", "val"], index_col="id",
    parse_dates=["date"], dtype={"val": "Int64"}, na_values=["NA", "NULL"]
)

            date   val
id                      
1   2024-01-01    10
2   2024-01-02   NaN

CSV — Write to File

Use DataFrame.to_csv(..., index=False, na_rep="NULL")


df = pd.DataFrame({"id": [1,  2], "val": [10,11]})
df.to_csv("examples/out.csv", index=False, na_rep="NULL")


!cat examples/out.csv

id,val
1,10
2,11

TSV — Tab Separated Values

Set sep="\t"
Writing: to_csv(..., sep="\t")


!cat examples/data.tsv

id\tname
1\tAlice
2\tBob


pd.read_csv("examples/data.tsv", sep="\t")

   id   name
0   1  Alice
1   2    Bob

CSV — Scale Tips

Quick sample with nrows=
Thin data with skiprows=


!cat examples/ex1.csv

a,b,c,d,message
1,2,3,4,hello
5,6,7,8,world
9,10,11,12,foo


pd.read_csv("examples/ex1.csv", nrows=2)

   a  b  c  d message
0  1  2  3  4   hello
1  5  6  7  8   world


# Skip the first data row (header kept)
pd.read_csv("examples/ex1.csv", skiprows=[1])

   a   b   c   d message
0  5   6   7   8   world
1  9  10  11  12     foo

CSV — Scale Tips

Use chunksize= to iterate
Stream large files in pieces; constant memory
Aggregate per chunk, then combine


total = 0
for i, chunk in enumerate(pd.read_csv("examples/ex1.csv", chunksize=2), start=1):
    print(f"chunk {i} shape:", tuple(chunk.shape))
    total += chunk["a"].sum()
total

chunk 1 shape: (2, 5)
chunk 2 shape: (1, 5)
15

1.2.
JSON:
JavaScript Object Notation ⭐️

JSON — Records

Arrays of objects map cleanly to rows


!cat examples/records.json

[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]


pd.read_json("examples/records.json")

   id   name
0   1  Alice
1   2    Bob


df = pd.DataFrame([{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}])
df.to_json("examples/out.json", orient="records")
!cat examples/out.json


[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]

JSON — Nested → Flat

Normalize nested structures with pd.json_normalize


!cat examples/nested.json

{"items":[
  {"id": 1, "user": {"name": "Alice", "age": 30}},
  {"id": 2, "user": {"name": "Bob", "age": 25}}
]}


import json
with open("examples/nested.json", "r") as f:
    obj = json.load(f)
pd.json_normalize(obj["items"])


    id user.name  user.age
0    1     Alice        30
1    2       Bob        25

1.3.
Excel:
Spreadsheets

Excel: Reading

Use ExcelFile to inspect sheet names before reading
Install dependencies: conda install openpyxl xlrd


xlsx = pd.ExcelFile("examples/ex1.xlsx")
xlsx.sheet_names

['Sheet1']


# Read a sheet
xlsx.parse(sheet_name="Sheet1")

   Unnamed: 0  a   b   c   d message
0           0  1   2   3   4   hello
1           1  5   6   7   8   world
2           2  9  10  11  12     foo


# With an index column
xlsx.parse(sheet_name="Sheet1", index_col=0)

   a   b   c   d message
0  1   2   3   4   hello
1  5   6   7   8   world
2  9  10  11  12     foo

Excel: Reading

Use pd.read_excel for convenience


# Convenience reader
frame = pd.read_excel("examples/ex1.xlsx", sheet_name="Sheet1")
frame

   Unnamed: 0  a   b   c   d message
0           0  1   2   3   4   hello
1           1  5   6   7   8   world
2           2  9  10  11  12     foo

Excel: Writing


# Writer API (explicit)
writer = pd.ExcelWriter("examples/ex2.xlsx")
frame.to_excel(writer, "Sheet1")
writer.close()


# One-liner (implicit writer)
frame.to_excel("examples/ex2.xlsx")

1.4.
Notes on Data Loading

Common Arguments

Argument	Used In	Description
header / names	CSV, Excel	Row to use as header or explicit list of names.
index_col	CSV, Excel	Column(s) to set as row index.
usecols	CSV, Excel	Select a subset of columns at read time.
dtype	CSV, Excel	Force or preserve data types.
parse_dates	CSV	Parse date-like strings into datetime.
na_values	CSV	Custom strings to treat as NaN.
chunksize / nrows	CSV	Iterate or sample large inputs.

Pick the Right Format

Format	Pros	Cons	Typical Use
CSV/TSV	Human-readable, ubiquitous	No types; big files are slow	Interchange, quick inspection
JSON	Flexible, web-native	Nesting; need normalize	APIs, logs, config
Excel	Multiple sheets; stakeholders	Heavier, slower; binary	Business reporting

Other Formats

There are many more formats Pandas can read and write. Please refer to the IO documentation for details.

2.
Data Cleaning and Preparation

Overview

Why Cleaning Matters

Most time goes to preparation: load → clean → transform → rearrange
Raw formats rarely fit the task → need reshaping & type fixes
pandas + Python make data analysis-ready

Outline

Handling Missing Data ⭐️ →
Data Transformation ⭐️ →
Extension Data Types ⭐️ →
String Manipulation ⭐️ →
Categorical Data ⭐️ →

2.1.
Handling Missing Data

Overview

Missing values are common
We call missing values NA (not available)
Many stats exclude NA by default
float64 uses NaN as a sentinel
Detect with isna / notna (or pandas.isna)

Outline

Filtering Out Missing Data ⭐️ →
Filling In Missing Data ⭐️ →

NaN as Sentinel

NaN marks missing values in float64


float_data = pd.Series([1.2, -3.5, np.nan, 0])
float_data


0    1.2
1   -3.5
2    NaN
3    0.0
dtype: float64


float_data.isna()


0    False
1    False
2     True
3    False
dtype: bool

None is Also NA

None is treated as NA (Not Available) in pandas


string_data = pd.Series(["aardvark", np.nan, None, "avocado"])
string_data


0    aardvark
1         NaN
2        None
3     avocado
dtype: object


string_data.isna()


0    False
1     True
2     True
3    False
dtype: bool

None is Also NA

None is treated as NA (Not Available) in pandas


float_data = pd.Series([1, 2, None], dtype='float64')
float_data


0    1.0
1    2.0
2    NaN
dtype: float64


float_data.isna()


0    False
1    False
2     True
dtype: bool

2.1.1.
Filtering Out Missing Data ⭐️

Series

Use dropna() or Boolean index with notna()


data = pd.Series([1, np.nan, 3.5, np.nan, 7])
data.dropna()


0    1.0
2    3.5
4    7.0
dtype: float64


data[data.notna()]


0    1.0
2    3.5
4    7.0
dtype: float64

DataFrame

dropna() drops rows with any NA by default


data = pd.DataFrame([[1., 6.5, 3.],
                     [1., np.nan, np.nan],
                     [np.nan, np.nan, np.nan],
                     [np.nan, 6.5, 3.]])
data


     0    1    2
0  1.0  6.5  3.0
1  1.0  NaN  NaN
2  NaN  NaN  NaN
3  NaN  6.5  3.0


data.dropna()


     0    1    2
0  1.0  6.5  3.0

All-NA Rows

Only drop rows of all NA: dropna(how="all")


data.dropna(how="all")


     0    1    2
0  1.0  6.5  3.0
1  1.0  NaN  NaN
3  NaN  6.5  3.0

All-NA Columns

Use axis="columns"


data[4] = np.nan
data


     0    1    2   4
0  1.0  6.5  3.0 NaN
1  1.0  NaN  NaN NaN
2  NaN  NaN  NaN NaN
3  NaN  6.5  3.0 NaN


data.dropna(axis="columns", how="all")


     0    1    2
0  1.0  6.5  3.0
1  1.0  NaN  NaN
2  NaN  NaN  NaN
3  NaN  6.5  3.0

Keep Rows with ≥ N Non-NA


df = pd.DataFrame(np.random.standard_normal((7, 3)))
df.iloc[:4, 1] = np.nan
df.iloc[:2, 2] = np.nan
df


          0         1         2
0 -0.204708       NaN       NaN
1 -0.555730       NaN       NaN
2  0.092908       NaN  0.769023
3  1.246435       NaN -1.296221
4  0.274992  0.228913  1.352917
5  0.886429 -2.001637 -0.371843
6  1.669025 -0.438570 -0.539741


df.dropna()


          0         1         2
4  0.274992  0.228913  1.352917
5  0.886429 -2.001637 -0.371843
6  1.669025 -0.438570 -0.539741


df.dropna(thresh=2)


          0         1         2
2  0.092908       NaN  0.769023
3  1.246435       NaN -1.296221
4  0.274992  0.228913  1.352917
5  0.886429 -2.001637 -0.371843
6  1.669025 -0.438570 -0.539741

2.1.2.
Filling In Missing Data ⭐️

Fill Missing Values

Constant fill: fillna(0)


df.fillna(0)


          0         1         2
0 -0.204708  0.000000  0.000000
1 -0.555730  0.000000  0.000000
2  0.092908  0.000000  0.769023
3  1.246435  0.000000 -1.296221
4  0.274992  0.228913  1.352917
5  0.886429 -2.001637 -0.371843
6  1.669025 -0.438570 -0.539741

Fill Missing Values

Per-column fill: fillna({col: value})


df.fillna({1: 0.5, 2: 0})


          0         1         2
0 -0.204708  0.500000  0.000000
1 -0.555730  0.500000  0.000000
2  0.092908  0.500000  0.769023
3  1.246435  0.500000 -1.296221
4  0.274992  0.228913  1.352917
5  0.886429 -2.001637 -0.371843
6  1.669025 -0.438570 -0.539741

Fill by Propagation

Forward/backward fill: method="ffill" / "bfill"


df = pd.DataFrame(np.random.standard_normal((6, 3)))
df.iloc[2:, 1] = np.nan
df.iloc[4:, 2] = np.nan

df


          0         1         2
0  0.476985  3.248944 -1.021228
1 -0.577087  0.124121  0.302614
2  0.523772       NaN  1.343810
3 -0.713544       NaN -2.370232
4 -1.860761       NaN       NaN
5 -1.265934       NaN       NaN


df.fillna(method="ffill")


          0         1         2
0  0.476985  3.248944 -1.021228
1 -0.577087  0.124121  0.302614
2  0.523772  0.124121  1.343810
3 -0.713544  0.124121 -2.370232
4 -1.860761  0.124121 -2.370232
5 -1.265934  0.124121 -2.370232

Fill by Propagation

Limit runs with limit=


df.fillna(method="ffill", limit=2)


          0         1         2
0  0.476985  3.248944 -1.021228
1 -0.577087  0.124121  0.302614
2  0.523772  0.124121  1.343810
3 -0.713544  0.124121 -2.370232
4 -1.860761       NaN -2.370232
5 -1.265934       NaN -2.370232

Simple Imputation

Can use statistics like mean / median for filling


data = pd.Series([1., np.nan, 3.5, np.nan, 7])
data.fillna(data.mean())


0    1.000000
1    3.833333
2    3.500000
3    3.833333
4    7.000000
dtype: float64

2.2.
Data Transformation

Overview

Beyond missing values → clean & reshape data
Topics:
- Remove duplicates
- Map / transform values
- Replace codes
- Rename labels
- Bin continuous data
- Handle outliers & sample randomly
- Create dummy variables

Outline

Removing Duplicates ⭐️ →
Transforming Data Using a Func or Mapping ⭐️ →
Replacing Values ⭐️ →
Renaming Axis Indexes 💨 →
Discretization and Binning 💨 →
Detecting and Filtering Outliers 💨 →
Permutation and Random Sampling 💨 →
Computing Indicator / Dummy Variables ⭐️ →

2.2.1.
Removing Duplicates ⭐️

Overview

Detect dupes: duplicated()
Drop dupes: drop_duplicates()
Default uses all columns and keeps the first occurrence

Creating dummy dataframe


data = pd.DataFrame({
    "k1": ["one", "two"] * 3 + ["two"],
    "k2": [1, 1, 2, 3, 3, 4, 4]
})
data


    k1  k2
0  one   1
1  two   1
2  one   2
3  two   3
4  one   3
5  two   4
6  two   4

Detecting Duplicates

Detect dup rows with .duplicated()
Default uses all columns


data


    k1  k2
0  one   1
1  two   1
2  one   2
3  two   3
4  one   3
5  two   4
6  two   4


data.duplicated()


0    False
1    False
2    False
3    False
4    False
5    False
6     True
dtype: bool

Removing Duplicates

Basics

Drop dup rows with .drop_duplicates()
Default keeps the first occurrence


data


    k1  k2
0  one   1
1  two   1
2  one   2
3  two   3
4  one   3
5  two   4
6  two   4


data.drop_duplicates()


   k1   k2
0  one   1
1  two   1
2  one   2
3  two   3
4  one   3
5  two   4

Removing Duplicates

Subset

Use subset=[...] to check only specific columns


data["v1"] = range(7)
data.drop_duplicates(subset=["k1"])


    k1  k2  v1
0  one   1   0
1  two   1   1

Removing Duplicates

Keep

Control which duplicate to keep via keep="first" (default) or keep="last"


data.drop_duplicates(["k1", "k2"], keep="last")


    k1  k2  v1
0  one   1   0
1  two   1   1
2  one   2   2
3  two   3   3
4  one   3   4
6  two   4   6

2.2.2.
Transforming Data Using a Function or Mapping ⭐️

Overview

Series.map() applies a function or mapping to elements
Great for feature engineering / adding derived columns

Transforming Values

with dict


data = pd.DataFrame({
    "food": ["bacon","pulled pork","bacon","pastrami","corned beef",
             "bacon","pastrami","honey ham","nova lox"],
    "ounces": [4,3,12,6,7.5,8,3,5,6]
})
meat_to_animal = {
    "bacon": "pig",
    "pulled pork": "pig",
    "pastrami": "cow",
    "corned beef": "cow",
    "honey ham": "pig",
    "nova lox": "salmon"
}
data["animal"] = data["food"].map(meat_to_animal)
data


          food  ounces  animal
0        bacon     4.0     pig
1  pulled pork     3.0     pig
2        bacon    12.0     pig
3     pastrami     6.0     cow
4  corned beef     7.5     cow
5        bacon     8.0     pig
6     pastrami     3.0     cow
7    honey ham     5.0     pig
8     nova lox     6.0  salmon

Transforming Values

with function

Pass a custom callable for flexible logic
Equivalent result to dict mapping above


def get_animal(x):
    return meat_to_animal[x]

data["food"].map(get_animal)


0       pig
1       pig
2       pig
3       cow
4       cow
5       pig
6       cow
7       pig
8    salmon
Name: food, dtype: object

2.2.3.
Replacing Values ⭐️

Replacing Values

General-purpose replacement with .replace
Use it for sentinel values → NaN or other codes


data = pd.Series([1., -999., 2., -999., -1000., 3.])
data.replace(-999, np.nan) # -999 likely a sentinel value


0       1.0
1       NaN
2       2.0
3       NaN
4   -1000.0
5       3.0
dtype: float64

Replacing Multiple Values

Replace many-to-one: pass a list of targets


data.replace([-999, -1000], np.nan)


0    1.0
1    NaN
2    2.0
3    NaN
4    NaN
5    3.0
dtype: float64

Replacing Multiple Values

Replace many-to-many: pass lists or a dict


data.replace({-999: np.nan, -1000: 0})
# same as: data.replace([-999, -1000], [np.nan, 0])


0    1.0
1    NaN
2    2.0
3    NaN
4    0.0
5    3.0
dtype: float64

Note: Series.str.replace is for string substitution (different API)

2.2.4.
Renaming Axis Indexes 💨

Transform labels

Use .map() with index / columns to modify labels


data = pd.DataFrame(
    np.arange(12).reshape((3, 4)),
    index=["Ohio", "Colorado", "New York"],
    columns=["one", "two", "three", "four"]
)

def transform(x):
    return x[:4].upper()

data.index.map(transform)


Index(['OHIO', 'COLO', 'NEW '], dtype='object')

Modify in place

Assign back to .index or .columns


data.index = data.index.map(transform)
data


      one  two  three  four
OHIO    0    1      2     3
COLO    4    5      6     7
NEW     8    9     10    11

Copy data then modify

Use .rename() to create a modified copy of the dataframe


data.rename(index=str.title, columns=str.upper)


      ONE  TWO  THREE  FOUR
Ohio    0    1      2     3
Colo    4    5      6     7
New     8    9     10    11


data.rename(index={"OHIO": "INDIANA"}, 
            columns={"three": "peekaboo"})


         one  two  peekaboo  four
INDIANA    0    1         2     3
COLO       4    5         6     7
NEW        8    9        10    11

2.2.5.
Discretization & Binning 💨

Overview

Goal: Bin continuous data.
Why: Simpler analysis (histograms, features).
Tools:
- pd.cut = fixed edges
- pd.qcut = quantiles (equal counts)

Example: Bin Ages

Goal: group ages into buckets (18–25, 26–35, 36–60, 61+)
pd.cut assigns each value to an interval


ages = [20, 22, 25, 27, 21, 23, 37, 31, 61, 45, 41, 32]
bins = [18, 25, 35, 60, 100]
age_categories = pd.cut(ages, bins)
age_categories


[(18, 25], (18, 25], (18, 25], (25, 35], (18, 25], (18, 25], (35, 60],
(25, 35], (60, 100], (35, 60], (35, 60], (25, 35]]
Categories (4, interval[int64, right]): [(18, 25] < (25, 35] < (35, 60] < (60, 100]]

What pd.cut Returns

A Categorical array with Interval labels
Use .codes for bin indices and .categories for interval metadata


age_categories.codes


array([0, 0, 0, 1, 0, 0, 2, 1, 3, 2, 2, 1], dtype=int8)


age_categories.categories


IntervalIndex([(18, 25], (25, 35], (35, 60], (60, 100]], dtype='interval[int64, right]')


age_categories.categories[0]


Interval(18, 25, closed='right')

Bin Counts

Categorical.value_counts gives bin frequencies


age_categories.value_counts()


(18, 25]      5
(25, 35]      3
(35, 60]      3
(60, 100]     1
Name: count, dtype: int64

Open vs Closed Intervals

(a, b]: left open (exclusive), right closed (inclusive)
Set right=False for [a, b) (left-closed, right-open)


pd.cut(ages, bins, right=False)


[[18, 25), [18, 25), [25, 35), [25, 35), [18, 25), [18, 25), [35, 60),
[25, 35), [60, 100), [35, 60), [35, 60), [25, 35)]
Categories (4, interval[int64, left]): [[18, 25) < [25, 35) < [35, 60) < [60, 100)]

Custom Bin Labels

Use labels=... to name intervals


group_names = ["Youth", "YoungAdult", "MiddleAged", "Senior"]
pd.cut(ages, bins, labels=group_names)


['Youth', 'Youth', 'Youth', 'YoungAdult', 'Youth', 'Youth', 'MiddleAged',
'YoungAdult', 'Senior', 'MiddleAged', 'MiddleAged', 'YoungAdult']
Categories (4, object): ['Youth' < 'YoungAdult' < 'MiddleAged' < 'Senior']

Equal-Width Bins by Count

Pass an int to pd.cut(data, k) for k equal-width bins
Use precision to limit decimal digits in labels


data = np.random.uniform(size=20)
pd.cut(data, 4, precision=2)


[(0.12, 0.34], (0.34, 0.55], ..., (0.76, 0.97]]
Categories (4, interval[float64, right]): [(0.12, 0.34] < (0.34, 0.55] < (0.55, 0.76] < (0.76, 0.97]]

Quantile Bins

Splits by sample quantiles → roughly equal counts per bin
Great for balancing groups (e.g., quartiles, deciles)


data = np.random.standard_normal(1000)
quartiles = pd.qcut(data, 4, precision=2)
quartiles


[(-2.96, -0.68], (-0.68, -0.026], (-0.026, 0.62], (0.62, 3.93], ...]
Categories (4, interval[float64, right]): [(-2.96, -0.68] < (-0.68, -0.026] < (-0.026, 0.62] < (0.62, 3.93]]


quartiles.value_counts()


(-2.96, -0.68]     250
(-0.68, -0.026]    250
(-0.026, 0.62]     250
(0.62, 3.93]       250
Name: count, dtype: int64

Custom Quantiles

Provide explicit probabilities in [0, 1]
Example: 10%, 50%, 90% breakpoints


pd.qcut(data, [0, 0.1, 0.5, 0.9, 1.0]).value_counts()


(-2.95, -1.187]     100
(-1.187, -0.0265]   400
(-0.0265, 1.286]    400
(1.286, 3.928]      100
Name: count, dtype: int64

When to Use cut vs qcut

cut: fixed-width or domain-specific edges (e.g., age groups)
qcut: balanced group sizes for fair comparisons
Always inspect: .categories, .codes, and value_counts
Be explicit about interval closure with right=...

2.2.6.
Detecting & Filtering Outliers 💨

Overview

Goal: Identify and handle extreme values.
Why: Robust analysis & stable models.
How:
- Boolean masks with .abs(), comparisons
- Row filtering via .any(axis="columns")
- Capping (winsorizing) with np.sign

Setup: Simulated Data


data = pd.DataFrame(np.random.standard_normal((1000, 4)))
data.describe()


                 0            1            2            3
count  1000.000000  1000.000000  1000.000000  1000.000000
mean      0.049091     0.026112    -0.002544    -0.051827
std       0.996947     1.007458     0.995232     0.998311
min      -3.645860    -3.184377    -3.745356    -3.428254
25%      -0.599807    -0.612162    -0.687373    -0.747478
50%       0.047101    -0.013609    -0.022158    -0.088274
75%       0.756646     0.695298     0.699046     0.623331
max       2.653656     3.525865     2.735527     3.366626

Find Outliers in One Column

Example: values in column 2 with |x| > 3
Use .abs() and Boolean indexing


col = data[2]
col[col.abs() > 3]


41    -3.399312
136   -3.745356
Name: 2, dtype: float64

Filter Rows with Any Outlier

Create a Boolean mask: data.abs() > 3
Reduce across columns with .any(axis="columns")


data[(data.abs() > 3).any(axis="columns")]


             0         1         2         3
41    0.457246 -0.025907 -3.399312 -0.974657
60    1.951312  3.260383  0.963301  1.201206
136   0.508391 -0.196713 -3.745356 -1.520113
235  -0.242459 -3.056990  1.918403 -0.578828
258   0.682841  0.326045  0.425384 -3.428254
322   1.179227 -3.184377  1.369891 -1.074833
544  -3.548824  1.553205 -2.186301  1.277104
635  -0.578093  0.193299  1.397822  3.366626
782  -0.207434  3.525865  0.283070  0.544635
803  -3.645860  0.255475 -0.549574 -1.907459

Cap Extreme Values

Replace |x| > 3 with 3 * np.sign(x)
Keeps direction; limits magnitude to threshold


data[data.abs() > 3] = np.sign(data) * 3
data.describe()


                 0            1            2            3
count  1000.000000  1000.000000  1000.000000  1000.000000
mean      0.050286     0.025567    -0.001399    -0.051765
std       0.992920     1.004214     0.991414     0.995761
min      -3.000000    -3.000000    -3.000000    -3.000000
25%      -0.599807    -0.612162    -0.687373    -0.747478
50%       0.047101    -0.013609    -0.022158    -0.088274
75%       0.756646     0.695298     0.699046     0.623331
max       2.653656     3.000000     2.735527     3.000000

What np.sign Does

Returns -1, 0, or 1 elementwise
Used to preserve the sign when capping


np.sign(data).head()


     0    1    2    3
0 -1.0  1.0 -1.0  1.0
1  1.0 -1.0  1.0 -1.0
2  1.0  1.0  1.0 -1.0
3 -1.0 -1.0  1.0 -1.0
4 -1.0  1.0 -1.0 -1.0

Tips & Variations

Try other thresholds (e.g., z-score > 3)
Alternative to capping: drop rows with outliers
Document decisions; outlier handling affects downstream analysis

2.2.7.
Permutation & Random Sampling 💨

Overview

Goal: Randomly reorder or select data.
Key tools:
- np.random.permutation → shuffle order
- take / iloc → apply permutation
- .sample() → draw random subset
Sampling: without replacement (default); set replace=True to allow repeats

Why Random Sampling?

Shuffle for unbiased train/test splits
Random folds in cross-validation
Bootstrapping with replace=True
Quick Exploratory Data Analysis (EDA) with a random subset
Downsample large data with sample(n | frac)

Setup: Simple DataFrame

A 5×7 grid of integers 0…34


df = pd.DataFrame(np.arange(5 * 7).reshape((5, 7)))
df


    0   1   2   3   4   5   6
0   0   1   2   3   4   5   6
1   7   8   9  10  11  12  13
2  14  15  16  17  18  19  20
3  21  22  23  24  25  26  27
4  28  29  30  31  32  33  34

Row Permutation

Generate random row order with np.random.permutation
Apply to dataframe with take or iloc


sampler = np.random.permutation(5)
sampler


array([3, 1, 4, 2, 0])


df.take(sampler)


    0   1   2   3   4   5   6
3  21  22  23  24  25  26  27
1   7   8   9  10  11  12  13
4  28  29  30  31  32  33  34
2  14  15  16  17  18  19  20
0   0   1   2   3   4   5   6


df.iloc[sampler]


    0   1   2   3   4   5   6
3  21  22  23  24  25  26  27
1   7   8   9  10  11  12  13
4  28  29  30  31  32  33  34
2  14  15  16  17  18  19  20
0   0   1   2   3   4   5   6

Column Permutation

Use take(axis="columns") to permute columns


column_sampler = np.random.permutation(7)
column_sampler


array([4, 6, 3, 2, 1, 0, 5])


df.take(column_sampler, axis="columns")


    4   6   3   2   1   0   5
0   4   6   3   2   1   0   5
1  11  13  10   9   8   7  12
2  18  20  17  16  15  14  19
3  25  27  24  23  22  21  26
4  32  34  31  30  29  28  33

Random Subset without Replacement: sample

Select n random rows; no repeats by default


df.sample(n=3)


    0   1   2   3   4   5   6
2  14  15  16  17  18  19  20
4  28  29  30  31  32  33  34
0   0   1   2   3   4   5   6

Sampling With Replacement

Allow repeated draws using replace=True
Works on Series and DataFrame


choices = pd.Series([5, 7, -1, 6, 4])
choices.sample(n=10, replace=True)


2   -1
0    5
3    6
1    7
4    4
0    5
4    4
0    5
4    4
4    4
dtype: int64

Tips & Notes

Use random_state=... in .sample for reproducibility
Weighted sampling: .sample(weights=...) (probability-proportional)
Permute columns: take(..., axis="columns"); permute rows: take(...) or iloc

2.2.8.
Computing Indicator / Dummy Variables ⭐️

Overview

Goal: Convert a categorical field into an indicator matrix of 0/1 columns.
Why: Required by many ML/statistical models; enables grouping & aggregation.
Key tool: pd.get_dummies (or Series.str.get_dummies for multi-membership)
Output type: recent pandas → boolean by default; set dtype=float if needed.

Example DataFrame

Make a small dataset with a categorical key


df = pd.DataFrame({"key": ["b", "b", "a", "c", "a", "b"],
                   "data1": range(6)})
df


  key  data1
0   b      0
1   b      1
2   a      2
3   c      3
4   a      4
5   b      5

Basic One-Hot Encoding

Turn categories into indicator columns


pd.get_dummies(df["key"], dtype=float)


     a    b    c
0  0.0  1.0  0.0
1  0.0  1.0  0.0
2  1.0  0.0  0.0
3  0.0  0.0  1.0
4  1.0  0.0  0.0
5  0.0  1.0  0.0

Add Prefix & Join

Name columns with prefix=...
Combine with original data using DataFrame.join


dummies = pd.get_dummies(df["key"], prefix="key", dtype=float)
df_with_dummy = df[["data1"]].join(dummies)
df_with_dummy


   data1  key_a  key_b  key_c
0      0    0.0    1.0    0.0
1      1    0.0    1.0    0.0
2      2    1.0    0.0    0.0
3      3    0.0    0.0    1.0
4      4    1.0    0.0    0.0
5      5    0.0    1.0    0.0

Multi-Membership Categories

When rows belong to multiple categories in a delimited string, use Series.str.get_dummies


mnames = ["movie_id", "title", "genres"]
movies = pd.read_table("datasets/movielens/movies.dat", sep="::",
                       header=None, names=mnames, engine="python")
movies[:10]


   movie_id                               title                        genres
0         1                    Toy Story (1995)   Animation|Children's|Comedy
1         2                      Jumanji (1995)  Adventure|Children's|Fantasy
2         3             Grumpier Old Men (1995)                Comedy|Romance
3         4            Waiting to Exhale (1995)                  Comedy|Drama
4         5  Father of the Bride Part II (1995)                        Comedy
5         6                         Heat (1995)         Action|Crime|Thriller
6         7                      Sabrina (1995)                Comedy|Romance
7         8                 Tom and Huck (1995)          Adventure|Children's
8         9                 Sudden Death (1995)                        Action
9        10                    GoldenEye (1995)     Action|Adventure|Thriller

Multi-Membership Categories

When rows belong to multiple categories in a delimited string, use Series.str.get_dummies


dummies = movies["genres"].str.get_dummies("|")
dummies.iloc[:10, :6]


   Action  Adventure  Animation  Children's  Comedy  Crime
0       0          0          1           1       1      0
1       0          1          0           1       0      0
2       0          0          0           0       1      0
3       0          0          0           0       1      0
4       0          0          0           0       1      0
5       1          0          0           0       0      1
6       0          0          0           0       1      0
7       0          1          0           1       0      0
8       1          0          0           0       0      0
9       1          1          0           0       0      0

Join with Prefixed Columns

Add indicators to the original table with a clear prefix


movies_windic = movies.join(dummies.add_prefix("Genre_"))
movies_windic.iloc[0]


movie_id                                      1
title                          Toy Story (1995)
genres              Animation|Children's|Comedy
Genre_Action                                  0
Genre_Adventure                               0
Genre_Animation                               1
Genre_Children's                              1
Genre_Comedy                                  1
Genre_Crime                                   0
Genre_Documentary                             0
Genre_Drama                                   0
Genre_Fantasy                                 0
Genre_Film-Noir                               0
Genre_Horror                                  0
Genre_Musical                                 0
Genre_Mystery                                 0
Genre_Romance                                 0
Genre_Sci-Fi                                  0
Genre_Thriller                                0
Genre_War                                     0
Genre_Western                                 0
Name: 0, dtype: object

Recipe: Bin then One-Hot

Discretize a numeric variable with pd.cut, then encode with pd.get_dummies


np.random.seed(12345)  # to make the example repeatable
values = np.random.uniform(size=10)
values


array([0.9296, 0.3164, 0.1839, 0.2046, 0.5677, 0.5955, 0.9645, 0.6532,
       0.7489, 0.6536])


bins = [0, 0.2, 0.4, 0.6, 0.8, 1]
pd.get_dummies(pd.cut(values, bins))


   (0.0, 0.2]  (0.2, 0.4]  (0.4, 0.6]  (0.6, 0.8]  (0.8, 1.0]
0       False       False       False       False        True
1       False        True       False       False       False
2        True       False       False       False       False
3       False        True       False       False       False
4       False       False        True       False       False
5       False       False        True       False       False
6       False       False       False       False        True
7       False       False       False        True       False
8       False       False       False        True       False
9       False       False       False        True       False

2.3.
Extension Data Types

Overview

Goal: Support richer, nullable, and non-NumPy types in pandas
Motivation:
- NumPy lacked full support for missing integers & booleans
- String & object arrays used excessive memory
- Time zones, intervals, etc. needed specialized storage
Solution: pandas ExtensionDtypes — fully integrated custom array types

Legacy vs Extension Behavior

Missing integers default to float64 with NaN:


s = pd.Series([1, 2, 3, None])  # int becomes float
s, s.dtype


0    1.0
1    2.0
2    3.0
3    NaN
dtype: float64

→ Use Int64Dtype for a proper nullable integer type:


s = pd.Series([1, 2, 3, None], dtype="Int64")
s


0       1
1       2
2       3
3    <NA>
dtype: Int64

Handling Missing Values

Extension arrays use <NA> (the pd.NA sentinel)


s.iloc[3], s.iloc[3] is pd.NA


<NA>
True

String Extension Type

Use pd.StringDtype() for efficient, nullable string arrays (requires pyarrow)


s = pd.Series(["one", "two", None, "three"], dtype="string")
s


0      one
1      two
2     <NA>
3    three
dtype: string

Converting Columns with astype

Extension dtypes integrate with .astype() for consistent null handling


df = pd.DataFrame({
    "A": [1, 2, None, 4],
    "B": ["one", "two", "three", None],
    "C": [False, None, False, True]
})

df["A"] = df["A"].astype("Int64")
df["B"] = df["B"].astype("string")
df["C"] = df["C"].astype("boolean")
df


      A      B      C
0     1    one  False
1     2    two   <NA>
2  <NA>  three  False
3     4   <NA>   True

Common Extension Types

Int/UInt* – nullable integers ("Int64", "UInt32", etc.)
Float* – nullable floats ("Float32", "Float64")
boolean – nullable booleans
string – efficient string arrays
category – categorical data
datetime[tz] – datetimes with timezone

Key Takeaways

Extension types fix NumPy’s null handling and memory limits
Use pd.NA and new dtypes (Int64, boolean, string) for clean pipelines
Convert easily with .astype()
Widely used in modern pandas versions for robust data cleaning

2.4.
String Manipulation

Overview

Goal: Clean and transform text data.
Python: Built-ins (split, strip, join, find, replace).
Regex: Flexible patterns via re (findall, search, sub).
pandas: Vectorized Series.str, NA-safe, regex-aware.

Outline

Python Built-In String Object Methods 💨 →
Regular Expressions 💨 →
String Functions in pandas ⭐️ →

2.4.1.
Python Built-In String Methods 💨

Overview

Use core split / strip / join to normalize tokens.
in / find / index / count for lookups & positions.
replace to swap or remove characters.

Split → Clean → Join

Split on commas, trim whitespace, then rejoin with "::" — a common normalize step.


val = "a,b,  guido"
pieces = [x.strip() for x in val.split(",")]
pieces


['a', 'b', 'guido']


"::".join(pieces)


'a::b::guido'

Find vs Index vs Count

Choose the right lookup: in (boolean), find (pos or −1), index (raises), count (frequency).


val = "a,b,  guido"
"guido" in val


True


val.find(":")

-1


val.count(",")
# val.index(":")  # would raise ValueError if not found

Replace

Substitute delimiters or delete characters entirely during cleanup.


val.replace(",", "::")


'a::b::  guido'


val.replace(",", "")


'ab  guido'

2.4.2.
Regular Expressions 💨

Overview

Regex = flexible patterns for split, search, extract, replace.
Use raw strings r"...\" to avoid escapes.
re.compile for repeated use (faster).

Whitespace Split

Split on any run of spaces/tabs/newlines with \s+ — robust tokenization.


import re
text = "foo    bar\t baz  \tqux"
re.split(r"\s+", text)


['foo', 'bar', 'baz', 'qux']

Email Pattern: Find & Redact

findall extracts all emails; sub safely redacts them in text.


import re

text = """Dave dave@google.com
Steve steve@gmail.com
Rob rob@gmail.com
Ryan ryan@yahoo.com"""

pat = r"[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}"
regex = re.compile(pat, flags=re.IGNORECASE)

hits = regex.findall(text)
hits


['dave@google.com', 'steve@gmail.com', 'rob@gmail.com', 'ryan@yahoo.com']


redacted = regex.sub("REDACTED", text)
redacted.splitlines()[:2]


['Dave REDACTED', 'Steve REDACTED']

Capture Groups

Parentheses segment matches (user, domain, suffix) for structured reuse in code or replacements.


pat = r"([A-Z0-9._%+-]+)@([A-Z0-9.-]+)\.([A-Z]{2,4})"
regex = re.compile(pat, flags=re.IGNORECASE)

m = regex.match("wesm@bright.net").groups()
m


('wesm', 'bright', 'net')


tuples = regex.findall(text)[:2]
tuples


[('dave', 'google', 'com'), ('steve', 'gmail', 'com')]


labeled = regex.sub(r"User:\1 Domain:\2 Suffix:\3", text).splitlines()[:2]
labeled


['Dave User:dave Domain:google Suffix:com',
 'Steve User:steve Domain:gmail Suffix:com']

2.4.3.
String Functions in pandas ⭐️

Overview

Use Series.str for vectorized, NA-safe string ops.
Prefer extension dtypes (string, boolean) for nullable results.
Regex integrates via contains, findall, extract.

NA-Safe Contains

Series.str.contains works across the column and preserves NA with nullable booleans.


data = pd.Series({
    "Dave": "dave@google.com",
    "Steve": "steve@gmail.com",
    "Rob": "rob@gmail.com",
    "Wes": np.nan
})
data.astype("string").str.contains("gmail")


Dave     False
Steve     True
Rob       True
Wes       <NA>
dtype: boolean

Regex: vectorized findall

Match emails per row; each cell holds a list of (user, domain, suffix) tuples.


pat = r"([A-Z0-9._%+-]+)@([A-Z0-9.-]+)\.([A-Z]{2,4})"
found = data.str.findall(pat, flags=re.IGNORECASE)
found


Dave     [(dave, google, com)]
Steve    [(steve, gmail, com)]
Rob        [(rob, gmail, com)]
Wes                        NaN
dtype: object

Pick & transform to domains

To get the domain of each email: Pull group 2 with .str.get(1)—no loops.


matches = found.str[0]  # take the first match per cell
matches


Dave     (dave, google, com)
Steve    (steve, gmail, com)
Rob        (rob, gmail, com)
Wes                      NaN
dtype: object


domains = matches.str.get(1)
domains


Dave     google
Steve     gmail
Rob       gmail
Wes         NaN
dtype: object

Regex → DataFrame

str.extract returns one column per capture group — great for parsing fields.


data.str.extract(pat, flags=re.IGNORECASE)


           0       1    2
Dave    dave  google  com
Steve  steve   gmail  com
Rob      rob   gmail  com
Wes      NaN     NaN  NaN

💡 Combine with DataFrame.columns= to name the extracted fields.

Quick Slicing

Vectorized preview/truncation without Python loops.


data.str[:5]


Dave     dave@
Steve    steve
Rob      rob@g
Wes        NaN
dtype: object

Tips & Notes

Prefer re.compile(...) for repeated use
Use raw strings r"...\" for regex patterns
Adopt string/boolean dtypes for cleaner NA handling
Leverage str.extract for structured parsing

2.5.
Categorical Data

Overview

Goal: Introduce pandas Categorical type for efficient label storage.
Why: Boost performance and reduce memory use in repeated-value columns.
Use: Simplify handling of categorical data in stats and ML workflows.

Outline

Background & Motivation ⭐️ →
Categorical Extension Type in pandas ⭐️ →
Computations with Categoricals 💨 →
Categorical Methods ⭐️ →

2.5.1.
Background & Motivation ⭐️

Overview

Why Categorical Data?

Many columns repeat a small set of labels (low cardinality).
Idea: store labels once; reference by integer codes.
Benefits: smaller memory, faster groupby/joins, clear semantics.

Distinct Values & Counts

Find unique labels and their frequencies.


values = pd.Series(['apple', 'orange', 'apple', 'apple'] * 2)


pd.unique(values)


array(['apple', 'orange'], dtype=object)


pd.value_counts(values)


apple     6
orange    2
Name: count, dtype: int64

Dictionary Encoding

Store labels once (dimension); store integer codes in main column.


codes = pd.Series([0, 1, 0, 0] * 2)
codes


0    0
1    1
2    0
3    0
4    0
5    1
6    0
7    0
dtype: int64


dim = pd.Series(['apple', 'orange'])
dim


0     apple
1    orange
dtype: object

→ Less memory: store integers (codes) instead of full strings (labels).

Restore Labels with take

Map codes back to labels via the dimension table.


dim.take(codes)


0     apple
1    orange
0     apple
0     apple
0     apple
1    orange
0     apple
0     apple
dtype: object

Terminology & Why It Matters

Categories: the distinct labels (e.g., ['apple','orange']).
Codes: integer indices into categories.
Perks: rename categories or add new ones without rewriting codes; speed & memory wins.

2.5.2.
Categorical Extension Type in pandas ⭐️

Overview

What: pandas Categorical stores integer codes + shared categories.
Why: lower memory, faster groupby/joins, clearer semantics.
How: astype('category'), pd.Categorical(...), from_codes.

Example DataFrame

Small fruit basket with repeated labels.


fruits = ['apple', 'orange', 'apple', 'apple'] * 2
N = len(fruits)
rng = np.random.default_rng(seed=12345)
df = pd.DataFrame({
    'basket_id': np.arange(N),
    'fruit': fruits,
    'count': rng.integers(3, 15, size=N),
    'weight': rng.uniform(0, 4, size=N)})
df


   basket_id   fruit  count    weight
0          0   apple     11  1.564438
1          1  orange      5  1.331256
2          2   apple     12  2.393235
3          3   apple      6  0.746937
4          4   apple      5  2.691024
5          5  orange     12  3.767211
6          6   apple     10  0.992983
7          7   apple     11  3.795525

Convert to Categorical

Swaps Python strings for compact category codes.


fruit_cat = df['fruit'].astype('category')
fruit_cat


0     apple
1    orange
2     apple
3     apple
4     apple
5    orange
6     apple
7     apple
Name: fruit, dtype: category
Categories (2, object): ['apple', 'orange']

What's Inside: Categorical

Access the underlying categorical array and its pieces.


c = fruit_cat.array # the Categorical object underneath
type(c)


pandas.core.arrays.categorical.Categorical


c.categories


Index(['apple', 'orange'], dtype='object')


c.codes


array([0, 1, 0, 0, 0, 1, 0, 0], dtype=int8)

Codes ↔ Labels Mapping

Quick lookup table for interpreting codes.


dict(enumerate(c.categories))


{0: 'apple', 1: 'orange'}

In-Place on a DataFrame

Assign the converted column back to df.


df['fruit'] = df['fruit'].astype('category')
df['fruit']


0     apple
1    orange
2     apple
3     apple
4     apple
5    orange
6     apple
7     apple
Name: fruit, dtype: category
Categories (2, object): ['apple', 'orange']

Create a Categorical Directly

From any sequence of values.


my_categories = pd.Categorical(['foo','bar','baz','foo','bar'])
my_categories


['foo', 'bar', 'baz', 'foo', 'bar']
Categories (3, object): ['bar', 'baz', 'foo']

Construct from Codes

When you already have integer codes and a category list.


categories = ['foo','bar','baz']
codes = [0, 1, 2, 0, 0, 1]
my_cats_2 = pd.Categorical.from_codes(codes, categories)
my_cats_2


['foo', 'bar', 'baz', 'foo', 'foo', 'bar']
Categories (3, object): ['foo', 'bar', 'baz']

Ordered Categoricals

Specify an order for comparisons, sorting, and bins.


ordered_cat = pd.Categorical.from_codes(codes, categories, ordered=True)
ordered_cat


['foo', 'bar', 'baz', 'foo', 'foo', 'bar']
Categories (3, object): ['foo' < 'bar' < 'baz']


my_cats_2.as_ordered()


['foo', 'bar', 'baz', 'foo', 'foo', 'bar']
Categories (3, object): ['foo' < 'bar' < 'baz']

Notes & Takeaways

Not just strings: categories can be any immutable values.
Performance: fewer bytes → faster ops on repeated labels.
Accessors: use .cat for category-aware methods (next).

2.5.3.
Computations with Categoricals 💨

Overview

Same APIs: Categoricals behave like strings/objects in most ops.
Faster ops: groupby, value_counts leverage integer codes.
Ordered use: Some functions respect categorical ordering.

Make Quartile Bins with qcut

Vectorized binning returns an ordered Categorical.


rng = np.random.default_rng(seed=12345)
draws = rng.standard_normal(1000)
draws[:5]


array([-1.4238,  1.2637, -0.8707, -0.2592, -0.0753])


bins = pd.qcut(draws, 4)
bins


[(-3.121, -0.675], (0.687, 3.211], (-3.121, -0.675], (-0.675, 0.0134], (-0.675, 0.0134], ...
Length: 1000
Categories (4, interval[float64, right]): [(-3.121, -0.675] < (-0.675, 0.0134] < (0.0134, 0.687] < (0.687, 3.211]]

Label Bins

Human-friendly quartile names keep the categorical ordering.


bins = pd.qcut(draws, 4, labels=['Q1','Q2','Q3','Q4'])
bins


['Q1', 'Q4', 'Q1', 'Q2', 'Q2', ..., 'Q3', 'Q3', 'Q2', 'Q3', 'Q2']
Length: 1000
Categories (4, object): ['Q1' < 'Q2' < 'Q3' < 'Q4']


bins.codes[:10]


array([0, 3, 0, 1, 1, 0, 0, 2, 2, 0], dtype=int8)

GroupBy with Categoricals

Use labels to aggregate numeric data by quartile.


bins = pd.Series(bins, name='quartile')
results = (pd.Series(draws)
           .groupby(bins)
           .agg(['count','min','max'])
           .reset_index())
results


  quartile  count       min       max
0       Q1    250 -3.119609 -0.678494
1       Q2    250 -0.673305  0.008009
2       Q3    250  0.018753  0.686183
3       Q4    250  0.688282  3.211418


results['quartile']


0    Q1
1    Q2
2    Q3
3    Q4
Name: quartile, dtype: category
Categories (4, object): ['Q1' < 'Q2' < 'Q3' < 'Q4']

Performance & Memory

Large repeated-label Series benefit from categorical encoding.


N = 10_000_000
labels = pd.Series(['foo','bar','baz','qux'] * (N // 4))
categories = labels.astype('category')


labels.memory_usage(deep=True)

520000132


categories.memory_usage(deep=True)

10000512

→ Huge memory savings: 520 MB → 10 MB

Performance & Memory

Conversion is not free. But it is a one-time cost.


%time _ = labels.astype('category')


CPU times: user 475 ms, sys: 9.04 ms, total: 484 ms
Wall time: 483 ms

value_counts Speedup

Counts use integer codes under the hood → big win.


%timeit labels.value_counts()


382 ms ± 1.47 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)


%timeit categories.value_counts()


17.5 ms ± 373 μs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Takeaways

qcut/cut: produce ordered Categorical bins.
GroupBy: faster on categoricals; ordered labels keep sort semantics.
Scale: huge memory savings; big speedups for counting/aggregation.

2.5.4.
Categorical Methods ⭐️

Overview

Accessor: Use .cat for codes & categories.
Manage: Set/rename/reorder/add/remove categories.
Modeling: Create dummy (one-hot) variables.

Create & Inspect

Convert labels to category


s = pd.Series(['a','b','c','d'] * 2)
cat_s = s.astype('category')
cat_s


0    a
1    b
2    c
3    d
4    a
5    b
6    c
7    d
dtype: category
Categories (4, object): ['a', 'b', 'c', 'd']

Create & Inspect

Explore codes and category names via attribute .cat


cat_s.cat.codes


0    0
1    1
2    2
3    3
4    0
5    1
6    2
7    3
dtype: int8


cat_s.cat.categories


Index(['a', 'b', 'c', 'd'], dtype='object')

Set Categories

Declare the full universe of categories (even if absent in data).


actual = ['a','b','c','d','e']
cat_s2 = cat_s.cat.set_categories(actual)
cat_s2


0    a
1    b
2    c
3    d
4    a
5    b
6    c
7    d
dtype: category
Categories (5, object): ['a', 'b', 'c', 'd', 'e']

value_counts Respects Categories

Unseen categories appear with zero count when defined.


cat_s.value_counts()


a    2
b    2
c    2
d    2
Name: count, dtype: int64


cat_s2.value_counts()


a    2
b    2
c    2
d    2
e    0
Name: count, dtype: int64

Remove Unused Categories

After filtering, drop categories not present in the data.


cat_s3 = cat_s[cat_s.isin(['a','b'])]
cat_s3


0    a
1    b
4    a
5    b
dtype: category
Categories (4, object): ['a', 'b', 'c', 'd']


cat_s3.cat.remove_unused_categories()


0    a
1    b
4    a
5    b
dtype: category
Categories (2, object): ['a', 'b']

Other Handy Methods

Method	What it does
add_categories	Append new categories.
rename_categories	Relabel without changing count.
reorder_categories	Change order (can set ordered).
as_ordered / as_unordered	Toggle the ordering flag.
remove_categories	Drop categories (values → NA).

Dummy Variables (One-Hot)

One-hot encoding is common in stats and machine learning.
pd.get_dummies creates binary indicator columns for each category.
Each row is now a one-hot vector.


cat_s = pd.Series(['a','b','c','d'] * 2, dtype='category')
pd.get_dummies(cat_s, dtype=float)


     a    b    c    d
0  1.0  0.0  0.0  0.0
1  0.0  1.0  0.0  0.0
2  0.0  0.0  1.0  0.0
3  0.0  0.0  0.0  1.0
4  1.0  0.0  0.0  0.0
5  0.0  1.0  0.0  0.0
6  0.0  0.0  1.0  0.0
7  0.0  0.0  0.0  1.0

Takeaways

.cat gives direct access to codes & categories.
Define category universe early to control downstream behavior.
Trim unused categories post-filter for clarity & memory wins.
get_dummies bridges to stats/ML pipelines.

Wrap-up

Loading: Efficiently load data from various sources.
Cleaning: Handle missing data, duplicates, and outliers.
Transformation: Map, replace, bin, and sample efficiently.
Strings: Use Series.str for fast text ops.
Extension dtypes: Nullable Int64, boolean, string.
Categoricals: Encode labels for speed, memory, and modeling.

Next: Time Series Data Processing

Programming for Data Processing

Lecture 7 Data Loading & Cleaning

Today

1.Data Loading and Storage

Overview

Outline

1.1. CSV: Comma-Separated Values ⭐️

CSV — The Workhorse

CSV — Common Options

CSV — Write to File

TSV — Tab Separated Values

CSV — Scale Tips

CSV — Scale Tips

1.2. JSON: JavaScript Object Notation ⭐️

JSON — Records

JSON — Nested → Flat

1.3. Excel: Spreadsheets

Excel: Reading

Excel: Reading

Excel: Writing

1.4. Notes on Data Loading

Common Arguments

Pick the Right Format

Other Formats

2.Data Cleaning and Preparation

Overview

Why Cleaning Matters

Outline

2.1. Handling Missing Data

Overview

Outline

NaN as Sentinel

None is Also NA

None is Also NA

2.1.1. Filtering Out Missing Data ⭐️

Series

DataFrame

All-NA Rows

All-NA Columns

Keep Rows with ≥ N Non-NA

2.1.2. Filling In Missing Data ⭐️

Fill Missing Values

Fill Missing Values

Fill by Propagation

Fill by Propagation

Simple Imputation

2.2.Data Transformation

Overview

Outline

2.2.1. Removing Duplicates ⭐️

Overview

Creating dummy dataframe

Detecting Duplicates

Removing Duplicates

Basics

Removing Duplicates

Subset

Removing Duplicates

Keep

2.2.2. Transforming Data Using a Function or Mapping ⭐️

Overview

Transforming Values

with dict

Transforming Values

with function

2.2.3. Replacing Values ⭐️

Replacing Values

Replacing Multiple Values

Replacing Multiple Values

2.2.4. Renaming Axis Indexes 💨

Transform labels

Modify in place

Copy data then modify

2.2.5. Discretization & Binning 💨

Overview

Example: Bin Ages

What pd.cut Returns

Bin Counts

Open vs Closed Intervals

Custom Bin Labels

Lecture 7
Data Loading & Cleaning

1.
Data Loading and Storage

1.1.
CSV:
Comma-Separated Values ⭐️

1.2.
JSON:
JavaScript Object Notation ⭐️

1.3.
Excel:
Spreadsheets

1.4.
Notes on Data Loading

2.
Data Cleaning and Preparation

2.1.
Handling Missing Data

2.1.1.
Filtering Out Missing Data ⭐️

2.1.2.
Filling In Missing Data ⭐️

2.2.
Data Transformation

2.2.1.
Removing Duplicates ⭐️

2.2.2.
Transforming Data Using a Function or Mapping ⭐️

2.2.3.
Replacing Values ⭐️

2.2.4.
Renaming Axis Indexes 💨

2.2.5.
Discretization & Binning 💨

2.2.6.
Detecting & Filtering Outliers 💨

2.2.7.
Permutation & Random Sampling 💨

2.2.8.
Computing Indicator / Dummy Variables ⭐️

2.3.
Extension Data Types

2.4.
String Manipulation

2.4.1.
Python Built-In String Methods 💨

2.4.2.
Regular Expressions 💨

2.4.3.
String Functions in pandas ⭐️

2.5.
Categorical Data

2.5.1.
Background & Motivation ⭐️

2.5.2.
Categorical Extension Type in pandas ⭐️