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 6
Getting Started with
Pandas
1.
Overview
What & Why
- pandas = data structures + tools for fast, convenient data cleaning/analysis
- Works great with: NumPy, SciPy, statsmodels, scikit-learn, matplotlib
- NumPy-style array computing, vectorized operations (no Python loops)
pandas vs NumPy
- pandas: tabular / heterogeneous data
- NumPy: homogeneous numeric arrays
Community
- Open source since 2010, large & active ecosystem
- 2,500+ contributors; widely used in real-world analytics
Imports
Convention: pd. refers to pandas:
import numpy as np
import pandas as pd
Importing Series / DataFrame directly also common:
from pandas import Series, DataFrame
2.
Pandas
Data Structures
Outline
2.1.
Series ⭐
What is a Series?
- 1D, array-like values + labeled index
- Homogeneous dtype (NumPy-like)
- Think of as: ordered dict (label → value)
Create a Series
Use pd.Series to create a Series:
obj = pd.Series([4, 7, -5, 3])
obj
0 4
1 7
2 -5
3 3
dtype: int64Implicit Index
Series has an implicit index:
obj
0 4
1 7
2 -5
3 3
dtype: int64obj.indexRangeIndex(start=0, stop=4, step=1)Custom Index
obj2 = pd.Series(
[4, 7, -5, 3],
index=["d", "b", "a", "c"]
)
obj2d 4
b 7
a -5
c 3
dtype: int64obj2.indexIndex(['d', 'b', 'a', 'c'], dtype='object')Label Selection
Use labels in the index to select/assign
obj2["a"]
-5obj2["d"] = 6obj2[["c", "a", "d"]]c 3
a -5
d 6
dtype: int64Vectorized Operations
Preserve index → value linkage
obj2[obj2 > 0]
d 6
b 7
c 3
dtype: int64obj2 * 2d 12
b 14
a -10
c 6
dtype: int64Vectorized Operations
Can also apply NumPy functions
np.exp(obj2)
d 403.428793
b 1096.633158
a 0.006738
c 20.085537
dtype: float64Dict Interoperability
Construct from dict
sdata = {
"Ohio": 35000,
"Texas": 71000,
"Oregon": 16000,
"Utah": 5000
}
obj3 = pd.Series(sdata)
obj3
Ohio 35000
Texas 71000
Oregon 16000
Utah 5000
dtype: int64Dict Interoperability
Convert back with to_dict
obj3.to_dict()
{'Ohio': 35000, 'Texas': 71000, 'Oregon': 16000, 'Utah': 5000}Reindex (Explicit Order)
Provide an index; missing labels → NaN (Not a Number)
states = ["California", "Ohio", "Oregon", "Texas"]
obj4 = pd.Series(sdata, index=states)
obj4 # California is missing (see previous slide)
California NaN
Ohio 35000.0
Oregon 16000.0
Texas 71000.0
dtype: float64Detect Missing
Use isna / notna to detect missing values
pd.isna(obj4) # equals obj4.isna()
California True
Ohio False
Oregon False
Texas False
dtype: bool
pd.notna(obj4) # equals obj4.notna()
California False
Ohio True
Oregon True
Texas True
dtype: boolAutomatic Alignment
- Arithmetic aligns by label (union of labels)
- Non-overlap → NaN (like an outer join in databases)
obj3
Ohio 35000
Texas 71000
Oregon 16000
Utah 5000
dtype: int64
obj4
California NaN
Ohio 35000.0
Oregon 16000.0
Texas 71000.0
dtype: float64
obj3 + obj4 # label-wise join
California NaN
Ohio 70000.0
Oregon 32000.0
Texas 142000.0
Utah NaN
dtype: float64
Names
Use .name and .index.name to set names
obj4.name = "population"
obj4.index.name = "state"
obj4
state
California NaN
Ohio 35000.0
Oregon 16000.0
Texas 71000.0
Name: population, dtype: float64
Rename Index
Assign new labels directly (in place) using .index
obj
0 4
1 7
2 -5
3 3
dtype: int64
obj.index = ["Bob", "Steve", "Jeff", "Ryan"]
obj
Bob 4
Steve 7
Jeff -5
Ryan 3
dtype: int64
2.2.
DataFrame ⭐
What is a DataFrame?
- 2D, tabular data with labeled rows & columns
- Think of as: Dictionary of Series sharing the same index
Create a DataFrame
Most common: from a dict of equal-length lists
data = {
"state": ["Ohio", "Ohio", "Ohio", "Nevada", "Nevada", "Nevada"],
"year": [2000, 2001, 2002, 2001, 2002, 2003],
"pop": [1.5, 1.7, 3.6, 2.4, 2.9, 3.2]
}
frame = pd.DataFrame(data)
frame
state year pop
0 Ohio 2000 1.5
1 Ohio 2001 1.7
2 Ohio 2002 3.6
3 Nevada 2001 2.4
4 Nevada 2002 2.9
5 Nevada 2003 3.2Peek at Rows
For large DataFrames, use .head() and .tail() to quickly inspect first/last 5 rows.
frame.head() state year pop
0 Ohio 2000 1.5
1 Ohio 2001 1.7
2 Ohio 2002 3.6
3 Nevada 2001 2.4
4 Nevada 2002 2.9frame.tail() state year pop
1 Ohio 2001 1.7
2 Ohio 2002 3.6
3 Nevada 2001 2.4
4 Nevada 2002 2.9
5 Nevada 2003 3.2Column Order & Missing
- Use columns=[...] to specify column order
- Column names must match; if missing → NaN
frame2 = pd.DataFrame(
data,
columns=["year", "state", "pop", "debt"]
)
frame2
year state pop debt
0 2000 Ohio 1.5 NaN
1 2001 Ohio 1.7 NaN
2 2002 Ohio 3.6 NaN
3 2001 Nevada 2.4 NaN
4 2002 Nevada 2.9 NaN
5 2003 Nevada 3.2 NaN
Column Access
- Can use dict-style [...] or attribute-style . access
- Return Series, with name attribute
- ⚠️ . access: Only for valid identifiers
frame2["state"]0 Ohio
1 Ohio
2 Ohio
3 Nevada
4 Nevada
5 Nevada
Name: state, dtype: objectframe2.year0 2000
1 2001
2 2002
3 2001
4 2002
5 2003
Name: year, dtype: int64Row Selection
- loc by label, iloc by position
frame2.loc[1]
year 2001
state Ohio
pop 1.7
debt NaN
Name: 1, dtype: objectframe2.iloc[2]
year 2002
state Ohio
pop 3.6
debt NaN
Name: 2, dtype: objectAssign Columns
From Scalars
Scalar fills column
frame2["debt"] = 16.5
frame2
year state pop debt
0 2000 Ohio 1.5 16.5
1 2001 Ohio 1.7 16.5
2 2002 Ohio 3.6 16.5
3 2001 Nevada 2.4 16.5
4 2002 Nevada 2.9 16.5
5 2003 Nevada 3.2 16.5Assign Columns
From Arrays
Array must match length of DataFrame
frame2["debt"] = np.arange(6.)
frame2
year state pop debt
0 2000 Ohio 1.5 0.0
1 2001 Ohio 1.7 1.0
2 2002 Ohio 3.6 2.0
3 2001 Nevada 2.4 3.0
4 2002 Nevada 2.9 4.0
5 2003 Nevada 3.2 5.0Assign Columns
From Series
Aligns by index; others → NaN
val = pd.Series([-1.2, -1.5, -1.7], index=[2, 4, 5])
frame2["debt"] = val
frame2
year state pop debt
0 2000 Ohio 1.5 NaN
1 2001 Ohio 1.7 NaN
2 2002 Ohio 3.6 -1.2
3 2001 Nevada 2.4 NaN
4 2002 Nevada 2.9 -1.5
5 2003 Nevada 3.2 -1.7New Columns
Create new columns with assignment:
frame2["eastern"] = frame2["state"] == "Ohio"
frame2
year state pop debt eastern
0 2000 Ohio 1.5 NaN True
1 2001 Ohio 1.7 NaN True
2 2002 Ohio 3.6 -1.2 True
3 2001 Nevada 2.4 NaN False
4 2002 Nevada 2.9 -1.5 False
5 2003 Nevada 3.2 -1.7 False⚠️ Cannot create new columns with . access!
Drop Columns
Remove columns with del
del frame2["eastern"]
frame2.columns
Index(['year', 'state', 'pop', 'debt'], dtype='object')DataFrame From Nested Dict
- Outer dict keys → DataFrame columns
- Inner dict keys → DataFrame index
- Missing values → NaN
populations = {
"Ohio": {2000: 1.5, 2001: 1.7, 2002: 3.6},
"Nevada": {2001: 2.4, 2002: 2.9}
}
frame3 = pd.DataFrame(populations)
frame3
Ohio Nevada
2000 1.5 NaN
2001 1.7 2.4
2002 3.6 2.9Overriding Index
- Use index= to override inner keys
- Missing values → NaN
pd.DataFrame(populations, index=[2001, 2002, 2003])
Ohio Nevada
2001 1.7 2.4
2002 3.6 2.9
2003 NaN NaNDataFrame From Dict of Series
Dict of Series → indexes are unioned (label alignment across Series)
pdata = {
"Ohio": frame3["Ohio"][:-1],
"Nevada": frame3["Nevada"][1:]
}
pd.DataFrame(pdata)
Ohio Nevada
2000 1.5 NaN
2001 1.7 2.4
2002 NaN 2.9DataFrame Constructor Inputs
| Type | Notes |
|---|---|
| 2D ndarray | Matrix of data; optional row/column labels |
| Dict of arrays/lists/tuples | Each sequence → column; all sequences same length |
| NumPy structured/record array | Treated like “dict of arrays” |
| Dict of Series | Values → columns; row index = union of Series indexes (if no explicit index) |
| Dict of dicts | Inner dicts → columns; row index = union of inner keys |
| List of dicts/Series | Each item → row; columns = union of keys/indexes |
| List of lists/tuples | Treated like 2D ndarray |
| Another DataFrame | Reuses its indexes unless overridden |
| NumPy MaskedArray | Like 2D ndarray; masked values become missing |
Transpose
Similar to NumPy: Use .T to swap rows & columns
frame3.T 2000 2001 2002
Ohio 1.5 1.7 3.6
Nevada NaN 2.4 2.9⚠️ Mixed dtypes + transpose can coerce to object.
Names
Use .index.name/.columns.name to set names
frame3.index.name = "year"
frame3.columns.name = "state"
frame3
state Ohio Nevada
year
2000 1.5 NaN
2001 1.7 2.4
2002 3.6 2.9→ Useful in multi-indexed DataFrames
To NumPy
to_numpy() → 2D ndarray
frame3.to_numpy()array([[1.5, nan],
[1.7, 2.4],
[3.6, 2.9]])Mixed dtypes → object
frame2.to_numpy()array([[2000, 'Ohio', 1.5, nan],
[2001, 'Ohio', 1.7, nan],
[2002, 'Ohio', 3.6, -1.2],
[2001, 'Nevada', 2.4, nan],
[2002, 'Nevada', 2.9, -1.5],
[2003, 'Nevada', 3.2, -1.7]], dtype=object)2.3.
Index Objects
What is an Index?
- Holds axis labels + metadata (e.g., name)
- Inputs to Series/DataFrame are converted to an Index
Construct & Inspect
obj = pd.Series(np.arange(3), index=["a", "b", "c"])
index = obj.index
index
Index(['a', 'b', 'c'], dtype='object')
index[1:]
Index(['b', 'c'], dtype='object')
Immutability & Sharing
Immutable → safer to share across objects
# index[1] = "d" # TypeError (Indexes are immutable)
labels = pd.Index(np.arange(3))
obj2 = pd.Series([1.5, -2.5, 0], index=labels)
obj2
0 1.5
1 -2.5
2 0.0
dtype: float64
obj2.index is labels
True
Set-like Behavior
Index supports membership tests and set logic
frame3
state Ohio Nevada
year
2000 1.5 NaN
2001 1.7 2.4
2002 3.6 2.9
frame3.columns
Index(['Ohio', 'Nevada'], dtype='object', name='state')
"Ohio" in frame3.columns
True
2003 in frame3.index
False
Duplicate Labels
- Unlike sets, an Index can contain duplicates
- Selection with a duplicate label returns all occurrences
pd.Index(["foo", "foo", "bar", "bar"])
Index(['foo', 'foo', 'bar', 'bar'], dtype='object')
Index: Useful Methods
| Method/Property | Description |
|---|---|
| append() | Concatenate with additional Index objects |
| difference() | Set difference |
| intersection() | Set intersection |
| union() | Set union |
| isin() | Boolean mask for membership |
| delete(i) | New Index with position i removed |
| drop(vals) | New Index without specified labels |
| insert(i, val) | Insert at position i |
| is_monotonic | True if non-decreasing |
| is_unique | True if no duplicates |
| unique() | Array of unique labels |
3.
Essential Functionality
Outline
3.1.
Reindexing
What is Reindexing?
- Create new object with a new index / column labels
- Values are rearranged; missing labels become NaN
- Optional fill methods (e.g., method="ffill")
Series: Reindex
Reorder & introduce new labels → missing become NaN
obj = pd.Series([4.5, 7.2, -5.3, 3.6], index=["d", "b", "a", "c"])
obj
d 4.5
b 7.2
a -5.3
c 3.6
dtype: float64
obj2 = obj.reindex(["a", "b", "c", "d", "e"])
obj2
a -5.3
b 7.2
c 3.6
d 4.5
e NaN
dtype: float64Series: Fill During Reindex
Use method="ffill" to forward-fill gaps
obj3 = pd.Series(
["blue", "purple", "yellow"],
index=[0, 2, 4]
)
obj3.reindex(np.arange(6), method="ffill")
0 blue
1 blue
2 purple
3 purple
4 yellow
5 yellow
dtype: objectDataFrame: Construct
frame = pd.DataFrame(
np.arange(9).reshape((3, 3)),
index=["a", "c", "d"],
columns=["Ohio", "Texas", "California"]
)
frame
Ohio Texas California
a 0 1 2
c 3 4 5
d 6 7 8DataFrame: Reindex Rows
- Use keyword index=
- Missing row “b” filled with NaN
frame2 = frame.reindex(index=["a", "b", "c", "d"])
frame2
Ohio Texas California
a 0.0 1.0 2.0
b NaN NaN NaN
c 3.0 4.0 5.0
d 6.0 7.0 8.0DataFrame: Reindex Columns
- Use keyword columns=
- Columns not requested are dropped
- New column “Utah” filled with NaN
states = ["Texas", "Utah", "California"]
frame.reindex(columns=states)
Texas Utah California
a 1 NaN 2
c 4 NaN 5
d 7 NaN 8Axis Keyword
Equivalently specify axis with axis="columns"
frame.reindex(states, axis="columns")
Texas Utah California
a 1 NaN 2
c 4 NaN 5
d 7 NaN 8Reindex via loc
Label-based reordering when labels already exist (no new labels allowed)
frame.loc[["a", "d", "c"], ["California", "Texas"]]
California Texas
a 2 1
d 8 7
c 5 4reindex Arguments
| Argument | Description |
|---|---|
| labels | New sequence to use as an index (used as-is if an Index). |
| index / columns | New row / column labels. |
| axis | Which axis to reindex ("index" or "columns"). |
| method | Fill method ("ffill", "bfill"). |
| fill_value | Value to use for introduced missing data. |
| limit / tolerance | Max gap size (count / distance) to fill. |
| level | Match on a specific level of a MultiIndex. |
| copy | Force data copy even if new index is equivalent. |
3.2.
Dropping Entries from an Axis
Concept
- Remove labels from an axis with .drop
- Returns a new object (not in-place)
- Works on rows (index) and columns (columns or axis)
Series: Build Example
obj = pd.Series(np.arange(5.), index=["a", "b", "c", "d", "e"])
obj
a 0.0
b 1.0
c 2.0
d 3.0
e 4.0
dtype: float64Series: Drop Labels
new_obj = obj.drop("c")
new_obj
a 0.0
b 1.0
d 3.0
e 4.0
dtype: float64
obj.drop(["d", "c"])
a 0.0
b 1.0
e 4.0
dtype: float64DataFrame: Build Example
data = pd.DataFrame(
np.arange(16).reshape((4, 4)),
index=["Ohio", "Colorado", "Utah", "New York"],
columns=["one", "two", "three", "four"]
)
data
one two three four
Ohio 0 1 2 3
Colorado 4 5 6 7
Utah 8 9 10 11
New York 12 13 14 15DataFrame: Drop Rows
Use index= to remove row labels
data.drop(index=["Colorado", "Ohio"])
one two three four
Utah 8 9 10 11
New York 12 13 14 15DataFrame: Drop Columns
Use columns= or axis synonyms
data.drop(columns=["two"])
one three four
Ohio 0 2 3
Colorado 4 6 7
Utah 8 10 11
New York 12 14 15
data.drop("two", axis=1)
one three four
Ohio 0 2 3
Colorado 4 6 7
Utah 8 10 11
New York 12 14 15
data.drop(["two", "four"], axis="columns")
one three
Ohio 0 2
Colorado 4 6
Utah 8 10
New York 12 143.3.
Indexing, Selection, and Filtering ⭐
Series: Construct
obj = pd.Series(np.arange(4.), index=["a", "b", "c", "d"])
obj
a 0.0
b 1.0
c 2.0
d 3.0
dtype: float64Series: Basics
obj["b"]
1.0
obj[1]
1.0
obj[2:4]
c 2.0
d 3.0
dtype: float64
obj[["b", "a", "d"]]
b 1.0
a 0.0
d 3.0
dtype: float64
obj[[1, 3]]
b 1.0
d 3.0
dtype: float64
obj[obj < 2]
a 0.0
b 1.0
dtype: float64Prefer loc / iloc
loc uses labels; iloc uses integer positions.
obj.loc[["b", "a", "d"]]
b 1.0
a 0.0
d 3.0
dtype: float64💡 Why this matters
Plain [] with integers is label-based if your index is integers, which can be surprising.
Int Index: Different Behavior
obj1 = pd.Series([1, 2, 3], index=[2, 0, 1])
obj2 = pd.Series([1, 2, 3], index=["a", "b", "c"])
obj1
2 1
0 2
1 3
dtype: int64
obj2
a 1
b 2
c 3
dtype: int64
obj1[[0, 1, 2]]
0 2
1 3
2 1
dtype: int64
obj2[[0, 1, 2]]
a 1
b 2
c 3
dtype: int64
Int Index: Different Behavior
When use loc, it is always label-based
obj2.loc[[0, 1]]
KeyError: "[0, 1] not in index"Int Index: Different Behavior
When use iloc, it is always position-based
obj1.iloc[[0, 1, 2]]
2 1
0 2
1 3
dtype: int64
obj2.iloc[[0, 1, 2]]
a 1
b 2
c 3
dtype: int64Int Index: Different Behavior
Label slices with loc also include the stop label:
obj2.loc["b":"c"]
b 2
c 3
dtype: int64Assigning with loc
obj2.loc["b":"c"] = 5
obj2
a 1
b 5
c 5
dtype: int64💡 Reminder
loc/iloc are indexers, not functions: use square brackets [], not parentheses ().
DataFrame: Column Indexing
data = pd.DataFrame(
np.arange(16).reshape((4, 4)),
index=["Ohio", "Colorado", "Utah", "New York"],
columns=["one", "two", "three", "four"]
)
data
one two three four
Ohio 0 1 2 3
Colorado 4 5 6 7
Utah 8 9 10 11
New York 12 13 14 15
data["two"]
Ohio 1
Colorado 5
Utah 9
New York 13
Name: two, dtype: int64
data[["three", "one"]]
three one
Ohio 2 0
Colorado 6 4
Utah 10 8
New York 14 12Rows & Boolean Filtering
⚠️ Special case: Use slicing with [] or boolean arrays to select rows
data[:2]
one two three four
Ohio 0 1 2 3
Colorado 4 5 6 7
data[data["three"] > 5]
one two three four
Colorado 4 5 6 7
Utah 8 9 10 11
New York 12 13 14 15Rows & Boolean Filtering
Can also use boolean DataFrame to filter values
data < 5
one two three four
Ohio True True True True
Colorado True False False False
Utah False False False False
New York False False False False
data[data < 5] = 0
data
one two three four
Ohio 0 0 0 0
Colorado 0 5 6 7
Utah 8 9 10 11
New York 12 13 14 15DataFrame: loc / iloc
Use loc for label-based indexing
data.loc["Colorado"]
one 0
two 5
three 6
four 7
Name: Colorado, dtype: int64
data.loc[["Colorado", "New York"]]
one two three four
Colorado 0 5 6 7
New York 12 13 14 15
data.loc["Colorado", ["two", "three"]]
two 5
three 6
Name: Colorado, dtype: int64DataFrame: loc / iloc
Use iloc for position-based indexing
data.iloc[2]
one 8
two 9
three 10
four 11
Name: Utah, dtype: int64
data.iloc[[2, 1]]
one two three four
Utah 8 9 10 11
Colorado 0 5 6 7DataFrame: loc / iloc
Use iloc for position-based indexing
data.iloc[2, [3, 0, 1]]
four 11
one 8
two 9
Name: Utah, dtype: int64
data.iloc[[1, 2], [3, 0, 1]]
four one two
Colorado 7 0 5
Utah 11 8 9Slices & Boolean with loc
data.loc[:"Utah", "two"]
Ohio 0
Colorado 5
Utah 9
Name: two, dtype: int64
data.iloc[:, :3][data.three > 5]
one two three
Colorado 0 5 6
Utah 8 9 10
New York 12 13 14
data.loc[data.three >= 2] # ⚠️ doesn't work with iloc
one two three four
Colorado 0 5 6 7
Utah 8 9 10 11
New York 12 13 14 15Integer Indexing Pitfalls
ser = pd.Series(np.arange(3.))
ser
0 0.0
1 1.0
2 2.0
dtype: float64Plain [] with integers is label-based if your index is integers
ser[-1] # -1 is treated as a LABEL, not a position
KeyError: -1Integer Indexing Pitfalls
If your index is non-integer, plain [] with integers is position-based
ser2 = pd.Series(np.arange(3.), index=["a", "b", "c"])
ser2
a 0.0
b 1.0
c 2.0
dtype: float64
ser2[-1] # -1 is treated as a POSITION, not a label
2.0
Integer Indexing Pitfalls
✅ Best practice: Use .loc for labels and .iloc for positions to avoid ambiguity.
ser.iloc[-1] # position-based selection
2.0
ser[:2] # slicing with integers is by position
0 0.0
1 1.0
dtype: float64Pitfalls with Chained Indexing
Avoid assigning through chained selections; use a single loc instead.
data
one two three four
Ohio 1 0 0 0
Colorado 1 5 6 7
Utah 1 9 10 11
New York 1 13 14 15
# Good patterns
data.loc[:, "one"] = 1
data.iloc[2] = 5
data.loc[data["four"] > 5] = 3
Pitfalls with Chained Indexing
Avoid assigning through chained selections; use a single loc instead.
# Bad: chained assignment
data[data.three == 5]["three"] = 6
SettingWithCopyWarning:
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead
# Fix:
data.loc[data.three == 5, "three"] = 6
Cheat Sheet: Common Indexing
| Type | Notes |
|---|---|
| df[column] | Select column(s); special cases: boolean filter, row slice |
| df.loc[rows] | Rows by label |
| df.loc[:, cols] | Cols by label |
| df.loc[rows, cols] | Rows & cols by label |
| df.iloc[rows] | Rows by integer position |
| df.iloc[:, cols] | Cols by integer position |
| df.iloc[rows, cols] | Rows & cols by integer position |
| df.at[row, col] | Single scalar by labels |
| df.iat[row, col] | Single scalar by positions |
| reindex | Select rows or columns by labels |
3.4.
Arithmetic &
Data Alignment ⭐
Key idea: label alignment
- pandas aligns on index/column labels before doing math.
- Labels present in only one operand → result is NaN.
- Use arithmetic methods (e.g., add) with fill_value to control missing parts.
Series + Series: union of labels
s1 = pd.Series([7.3, -2.5, 3.4, 1.5], index=["a","c","d","e"])
s2 = pd.Series([-2.1, 3.6, -1.5, 4, 3.1], index=["a","c","e","f","g"])
s1
a 7.3
c -2.5
d 3.4
e 1.5
dtype: float64
s2
a -2.1
c 3.6
e -1.5
f 4.0
g 3.1
dtype: float64
Labels d/f/g only appear in one Series ⇒ NaN after alignment.
s1 + s2
a 5.2
c 1.1
d NaN
e 0.0
f NaN
g NaN
dtype: float64
DF + DF: align on rows & cols
df1 = pd.DataFrame(np.arange(9.).reshape(3,3),
columns=list("bcd"),
index=["Ohio","Texas","Colorado"])
df2 = pd.DataFrame(np.arange(12.).reshape(4,3),
columns=list("bde"),
index=["Utah","Ohio","Texas","Oregon"])
DF + DF: align on rows & cols
df1
b c d
Ohio 0.0 1.0 2.0
Texas 3.0 4.0 5.0
Colorado 6.0 7.0 8.0
df2
b d e
Utah 0.0 1.0 2.0
Ohio 3.0 4.0 5.0
Texas 6.0 7.0 8.0
Oregon 9.0 10.0 11.0
Only overlapping (row, col)'s produce numbers; else NaN.
df1 + df2
b c d e
Colorado NaN NaN NaN NaN
Ohio 3.0 NaN 6.0 NaN
Oregon NaN NaN NaN NaN
Texas 9.0 NaN 12.0 NaN
Utah NaN NaN NaN NaN
Filling gaps during arithmetic
df1 = pd.DataFrame(np.arange(12.).reshape(3,4), columns=list("abcd"))
df2 = pd.DataFrame(np.arange(20.).reshape(4,5), columns=list("abcde"))
df2.loc[1, "b"] = np.nan
df1
a b c d
0 0.0 1.0 2.0 3.0
1 4.0 5.0 6.0 7.0
2 8.0 9.0 10.0 11.0
df2
a b c d e
0 0.0 1.0 2.0 3.0 4.0
1 5.0 NaN 7.0 8.0 9.0
2 10.0 11.0 12.0 13.0 14.0
3 15.0 16.0 17.0 18.0 19.0
df1 + df2
a b c d e
0 0.0 2.0 4.0 6.0 NaN
1 9.0 NaN 13.0 15.0 NaN
2 18.0 20.0 22.0 24.0 NaN
3 NaN NaN NaN NaN NaN
Plain + (NaNs remain)
df1.add(df2, fill_value=0)
a b c d e
0 0.0 2.0 4.0 6.0 4.0
1 9.0 5.0 13.0 15.0 9.0
2 18.0 20.0 22.0 24.0 14.0
3 15.0 16.0 17.0 18.0 19.0
Using add(fill_value=0)
Reverse-op twins
Each arithmetic method has an r* variant with reversed arguments:
df1
a b c d
0 0.0 1.0 2.0 3.0
1 4.0 5.0 6.0 7.0
2 8.0 9.0 10.0 11.0
df1.rdiv(1) # same as 1 / df1
a b c d
0 inf 1.000000 0.500000 0.333333
1 0.25 0.200000 0.166667 0.142857
2 0.125 0.111111 0.100000 0.090909
Other methods: radd, rsub, rmul, rdiv, rfloordiv, rpow
DataFrame ± Series
- Series index matches DataFrame columns
- Values are broadcast down rows
frame = pd.DataFrame(np.arange(12.).reshape(4,3),
columns=list("bde"),
index=["Utah","Ohio","Texas","Oregon"])
series = frame.iloc[0] # row "Utah"
DataFrame ± Series
frame
b d e
Utah 0.0 1.0 2.0
Ohio 3.0 4.0 5.0
Texas 6.0 7.0 8.0
Oregon 9.0 10.0 11.0
series
b 0.0
d 1.0
e 2.0
Name: Utah, dtype: float64
frame - series
b d e
Utah 0.0 0.0 0.0
Ohio 3.0 3.0 3.0
Texas 6.0 6.0 6.0
Oregon 9.0 9.0 9.0
DataFrame ± Series
Columns not in the Series become NaN:
series2 = pd.Series(range(3), index=["b", "e", "f"])
series2
b 0
e 1
f 2
dtype: int64
frame + series2
b d e f
Utah 0.0 NaN 4.0 NaN
Ohio 3.0 NaN 7.0 NaN
Texas 6.0 NaN 10.0 NaN
Oregon 9.0 NaN 13.0 NaN
Broadcast across columns
Use a method and pass axis="index" to match on rows:
series3 = frame["d"] # aligns with row index
frame
b d e
Utah 0.0 1.0 2.0
Ohio 3.0 4.0 5.0
Texas 6.0 7.0 8.0
Oregon 9.0 10.0 11.0
series3
Utah 1.0
Ohio 4.0
Texas 7.0
Oregon 10.0
Name: d, dtype: float64
frame.sub(series3, axis="index")
b d e
Utah -1.0 0.0 1.0
Ohio -1.0 0.0 1.0
Texas -1.0 0.0 1.0
Oregon -1.0 0.0 1.0
“axis” here means: the axis to align/match on.
Tips
- Need zeros instead of NaN?
- Prefer df1.add(df2, fill_value=0) over filling beforehand.
- Mismatched labels ≠ error:
- They quietly become NaN and can propagate.
- reindex(..., fill_value=...) can prep shapes before arithmetic.
3.5.
Function Application
& Mapping ⭐
NumPy ufuncs on pandas
NumPy ufuncs work element-wise on Series/DataFrame
frame = pd.DataFrame(np.random.standard_normal((4,3)),
columns=list("bde"),
index=["Utah","Ohio","Texas","Oregon"])
frame
b d e
Utah -0.204708 0.478943 -0.519439
Ohio -0.555730 1.965781 1.393406
Texas 0.092908 0.281746 0.769023
Oregon 1.246435 1.007189 -1.296221
np.abs(frame)
b d e
Utah 0.204708 0.478943 0.519439
Ohio 0.555730 1.965781 1.393406
Texas 0.092908 0.281746 0.769023
Oregon 1.246435 1.007189 1.296221
Per column operation
Use DataFrame.apply to apply a 1D function to each column (default):
def f1(x):
"""Compute range (max - min) of values in Series x."""
return x.max() - x.min()
frame.apply(f1)
b 1.802165
d 1.684034
e 2.689627
dtype: float64
Per row operation
Use DataFrame.apply with axis="columns" → apply across columns (once per row):
frame.apply(f1, axis="columns")
Utah 0.998382
Ohio 2.521511
Texas 0.676115
Oregon 2.542656
dtype: float64
⚠️ Many stats (e.g., sum, mean) are built-in methods; apply isn’t always needed.
Return multiple values
Return a Series → becomes a DataFrame of results:
def f2(x):
return pd.Series(
[x.min(), x.max()],
index=["min","max"]
)
frame.apply(f2)
b d e
min -0.555730 0.281746 -1.296221
max 1.246435 1.965781 1.393406
Element-wise functions
- applymap → element-wise on a DataFrame
- Series.map → element-wise on a Series
def my_format(x):
return f"{x:.2f}"
frame.applymap(my_format)
b d e
Utah -0.20 0.48 -0.52
Ohio -0.56 1.97 1.39
Texas 0.09 0.28 0.77
Oregon 1.25 1.01 -1.30
frame["e"].map(my_format)
Utah -0.52
Ohio 1.39
Texas 0.77
Oregon -1.30
Name: e, dtype: object
3.6.
Sorting &
Ranking ⭐
Sort by labels: sort_index
- Lexicographic sort by index/column labels.
- Works on Series and DataFrame; choose axis.
obj = pd.Series(np.arange(4), index=["d","a","b","c"])
obj
d 0
a 1
b 2
c 3
dtype: int64
obj.sort_index()
a 1
b 2
c 3
d 0
dtype: int64
DataFrame: sort by index
frame = pd.DataFrame(np.arange(8).reshape((2,4)),
index=["three","one"],
columns=["d","a","b","c"])
frame
d a b c
three 0 1 2 3
one 4 5 6 7
frame.sort_index()
d a b c
one 4 5 6 7
three 0 1 2 3
frame.sort_index(axis="columns")
a b c d
three 1 2 3 0
one 5 6 7 4
frame.sort_index(axis="columns", ascending=False)
d c b a
three 0 3 2 1
one 4 7 6 5
Sort by values: sort_values
Series: order by the data (NaNs last by default).
obj = pd.Series([4, 7, -3, 2])
obj.sort_values()
2 -3
3 2
0 4
1 7
dtype: int64
obj = pd.Series(
[4, np.nan, 7, np.nan, -3, 2]
)
obj.sort_values()
4 -3.0
5 2.0
0 4.0
2 7.0
1 NaN
3 NaN
dtype: float64
obj.sort_values(
na_position="first"
)
1 NaN
3 NaN
4 -3.0
5 2.0
0 4.0
2 7.0
dtype: float64
DataFrame: sort by columns
frame = pd.DataFrame({"b":[4,7,-3,2], "a":[0,1,0,1]})
frame
b a
0 4 0
1 7 1
2 -3 0
3 2 1
frame.sort_values("b")
b a
2 -3 0
3 2 1
0 4 0
1 7 1
frame.sort_values(["a","b"])
b a
2 -3 0
0 4 0
3 2 1
1 7 1
Tips
- sort_index → sort by labels; sort_values → sort by data.
- Control NaN placement with na_position ("last" default, or "first").
- For multi-key sorts, pass a list: df.sort_values(["key1","key2"]).
Ranking: rank
- Assigns ranks 1..N after sorting.
- Default tie-break = average (ties get mean rank → floats).
- ascending=False → larger values get rank 1.
Ranking: grade leaderboard
We’ll rank students by score (higher = better).
scores = pd.Series(
{"Ava":95, "Ben":88, "Chloe":95, "Diego":76, "Elle":88, "Finn":92, "Gio":70}
)
scores
Ava 95
Ben 88
Chloe 95
Diego 76
Elle 88
Finn 92
Gio 70
dtype: int64
Leaderboard view
sort by score
Just sorting first helps see the order.
scores.sort_values(ascending=False)
Ava 95
Chloe 95
Finn 92
Ben 88
Elle 88
Diego 76
Gio 70
dtype: int64
Default ranking: average ties
rank(ascending=False) — ties get the average of their positions.
scores.rank(ascending=False)
Ava 1.5
Ben 4.5
Chloe 1.5
Diego 6.0
Elle 4.5
Finn 3.0
Gio 7.0
dtype: float64
Why 1.5 for Ava & Chloe? They tie for 1st and 2nd → average is (1+2)/2.
Tie-break by first appearance
rank(ascending=False, method="first") — earlier entry wins the lower rank.
scores.rank(ascending=False, method="first")
Ava 1.0
Ben 4.0
Chloe 2.0
Diego 6.0
Elle 5.0
Finn 3.0
Gio 7.0
dtype: float64
Among tied 95s, Ava appears before Chloe in the data → Ava gets 1, Chloe 2.
Other tie policies
min: give the whole tie the best (lowest) rank number.
scores.rank(ascending=False, method="min")
Ava 1.0
Ben 4.0
Chloe 1.0
Diego 6.0
Elle 4.0
Finn 3.0
Gio 7.0
dtype: float64
max: give the whole tie the worst (highest) rank number.
scores.rank(ascending=False, method="max")
Ava 2.0
Ben 5.0
Chloe 2.0
Diego 6.0
Elle 5.0
Finn 3.0
Gio 7.0
dtype: float64
Other tie policies
dense: like min, but ranks don’t skip numbers between distinct scores.
scores.rank(ascending=False, method="dense")
Ava 1.0
Ben 3.0
Chloe 1.0
Diego 4.0
Elle 3.0
Finn 2.0
Gio 5.0
dtype: float64
DataFrame ranking
- Across columns (per row): rank each row’s values (e.g., a student’s subjects).
- Down rows (per column): rank within each column (e.g., class leaderboard per subject).
Example data
frame = pd.DataFrame({
"Math":[88, 70, 90, 60],
"Eng":[75, 80, 85, 60],
"Sci":[92, 65, 70, 60]
}, index=["Alice","Bob","Cara","Dan"])
frame
Math Eng Sci
Alice 88 75 92
Bob 70 80 65
Cara 90 85 70
Dan 60 60 60
Rank across columns (per row)
“Which subject is each student best at?” (higher score → better rank)
# 1 = best within each row
frame.rank(axis="columns", ascending=False)
Math Eng Sci
Alice 2.0 3.0 1.0
Bob 2.0 1.0 3.0
Cara 1.0 2.0 3.0
Dan 2.0 2.0 2.0
Ties get the average rank (Dan’s three equal scores → all 2.0).
Rank down rows (per column)
“Who ranks #1 in each subject?” (higher score → better rank)
# 1 = best within each column (subject)
frame.rank(axis="index", ascending=False)
Math Eng Sci
Alice 2.0 3.0 1.0
Bob 3.0 2.0 3.0
Cara 1.0 1.0 2.0
Dan 4.0 4.0 4.0
Tie-breaking methods
For Series/DataFrame.rank
| Method | Description |
|---|---|
| "average" | Default: average rank within ties |
| "min" | All ties get the minimum rank |
| "max" | All ties get the maximum rank |
| "first" | Order of appearance decides ranks |
| "dense" | Like "min", but ranks increase by 1 per group |
3.7.
Axis Indexes with
Duplicate Labels
Key idea
duplicates are allowed
- Index (row/column labels) need not be unique.
- Some ops (e.g., reindex) expect uniqueness, but most selection & math still work.
- Behavior changes: selecting a duplicate label returns multiple rows.
Series: duplicate labels
obj = pd.Series(np.arange(5), index=["a", "a", "b", "b", "c"])
obj
a 0
a 1
b 2
b 3
c 4
dtype: int64
obj.index.is_uniqueFalseSeries selection with duplicates
Selecting a label returns:
- Series when the label appears multiple times
- Scalar when it appears once
obj["a"]
a 0
a 1
dtype: int64
obj["c"]4DataFrame: duplicate row labels
df = pd.DataFrame(
np.random.standard_normal((5, 3)),
index=["a", "a", "b", "b", "c"]
)
df
0 1 2
a 0.274992 0.228913 1.352917
a 0.886429 -2.001637 -0.371843
b 1.669025 -0.438570 -0.539741
b 0.476985 3.248944 -1.021228
c -0.577087 0.124121 0.302614
DataFrame selection with duplicates
.loc returns a DataFrame if the label is duplicated; otherwise a Series.
df.loc["b"]
0 1 2
b 1.669025 -0.438570 -0.539741
b 0.476985 3.248944 -1.021228
df.loc["c"]
0 -0.577087
1 0.124121
2 0.302614
Name: c, dtype: float64
Working safely with duplicates
- Find duplicates in the index:
dupes = df.index.duplicated(keep="first")
dupes
array([False, True, False, True, False])
- Drop later duplicates (keep first):
df_nodupe = df[~df.index.duplicated(keep="first")]
- Aggregate duplicate groups (e.g., sum or mean):
df_grouped = df.groupby(level=0).mean(numeric_only=True)
Takeaways
- Duplicates change selection return types (Series vs DataFrame).
- Use index.is_unique or index.duplicated() to check/handle duplicates.
- Pick a policy: keep first/last duplicate, or aggregate with groupby(level=0).
4.
Summarizing &
Descriptive Statistics
Outline
4.1.
Reductions ⭐
Overview
- Reductions return one value per axis: sum, mean, min/max, …
- Handle missing data by default (skipna=True).
- Axis matters: axis="index" (down rows) vs axis="columns" (across columns).
Set up a tiny DataFrame
df = pd.DataFrame([[1.4, np.nan],
[7.1, -4.5],
[np.nan, np.nan],
[0.75, -1.3]],
index=["a","b","c","d"],
columns=["one","two"])
df
one two
a 1.40 NaN
b 7.10 -4.5
c NaN NaN
d 0.75 -1.3
reductions
Column-wise vs row-wise
Default skipna=True: ignore NaNs (all-NaN → 0 for sum).
df.sum() # axis="index"
one 9.25
two -5.80
dtype: float64
df.sum(axis="columns")
a 1.40
b 2.60
c 0.00
d -0.55
dtype: float64
Missing data: skipna
When skipna=False:
- any NaN on the reduction axis → result is NaN.
df.sum(
axis="index",
skipna=False)
one NaN
two NaN
dtype: float64
df.sum(
axis="columns",
skipna=False)
a NaN
b 2.60
c NaN
d -0.55
dtype: float64
More reductions
Some methods, e.g. mean, require at least one non-NaN value to compute a result.
df.mean(axis="columns")
a 1.400
b 1.300
c NaN
d -0.275
dtype: float64
4.2.
Indirect Statistics ⭐
Indirect statistics
Some methods, like idxmin and idxmax, return indirect statistics (index/label of min/max value).
df.idxmax()
one b
two d
dtype: object
Accumulations
df.cumsum()
one two
a 1.40 NaN
b 8.50 -4.5
c NaN NaN
d 9.25 -5.8
multi-stat summary
df.describe()
one two
count 3.000000 2.000000
mean 3.083333 -2.900000
std 3.493685 2.262742
min 0.750000 -4.500000
25% 1.075000 -3.700000
50% 1.400000 -2.900000
75% 4.250000 -2.100000
max 7.100000 -1.300000
describe on non-numeric
obj = pd.Series(["a","a","b","c"] * 4)
obj.describe()
count 16
unique 3
top a
freq 8
dtype: object
Tips
- Prefer built-ins: df.mean(), df.quantile(), df.idxmax(), etc.
- Set axis explicitly when teaching/reading code.
- Control NaNs with skipna; default is True.
- See Table 5.8: Descriptive and summary statistics for more descritive and summary stats.
4.3.
Correlation & Covariance
Key idea
- Series: s1.corr(s2), s1.cov(s2) align on index first.
- DataFrame: df.corr() / df.cov() → full matrices.
- corrwith computes pairwise correlations vs another Series/DataFrame.
Setup: prices → daily returns
Real stock data: daily prices & volumes. Available here.
price = pd.read_pickle("examples/yahoo_price.pkl")
volume = pd.read_pickle("examples/yahoo_volume.pkl")
returns = price.pct_change() # daily percent change
returns.tail()
AAPL GOOG IBM MSFT
Date
2016-10-17 -0.000680 0.001837 0.002072 -0.003483
2016-10-18 -0.000681 0.019616 -0.026168 0.007690
2016-10-19 -0.002979 0.007846 0.003583 -0.002255
2016-10-20 -0.000512 -0.005652 0.001719 -0.004867
2016-10-21 -0.003930 0.003011 -0.012474 0.042096
Series ↔ Series
.corr: Pearson correlation (aligning on index)
returns["MSFT"].corr(returns["IBM"])
0.49976361144151166
.cov: covariance (aligning on index)
returns["MSFT"].cov(returns["IBM"])
8.870655479703549e-05
DataFrame ↔ DataFrame
corr / cov: full pairwise matrices
returns.corr()
AAPL GOOG IBM MSFT
AAPL 1.000000 0.407919 0.386817 0.389695
GOOG 0.407919 1.000000 0.405099 0.465919
IBM 0.386817 0.405099 1.000000 0.499764
MSFT 0.389695 0.465919 0.499764 1.000000
returns.cov()
AAPL GOOG IBM MSFT
AAPL 0.000277 0.000107 0.000078 0.000095
GOOG 0.000107 0.000251 0.000078 0.000108
IBM 0.000078 0.000078 0.000146 0.000089
MSFT 0.000095 0.000108 0.000089 0.000215
corrwith
pairwise vs Series / DataFrame
returns.corrwith(returns["IBM"])
AAPL 0.386817
GOOG 0.405099
IBM 1.000000
MSFT 0.499764
dtype: float64
returns.corrwith(volume)
AAPL -0.075565
GOOG -0.007067
IBM -0.204849
MSFT -0.092950
dtype: float64
Use axis="columns" to compute row-wise.
Takeaways
- Correlations align labels automatically before computing.
- Use corr/cov for full matrices; corrwith for pairwise columns/rows.
4.4.
Unique Values, Value Counts, Membership
Key idea
- unique → unique values (array).
- value_counts → frequencies (Series, sorted desc by default).
- isin → vectorized membership filter.
- Index.get_indexer → map values to integer positions.
Unique & value counts
obj = pd.Series(["c","a","d","a","a","b","b","c","c"])
uniques = obj.unique()
uniques
array(['c', 'a', 'd', 'b'], dtype=object)
obj.value_counts() # sort=True by default
c 3
a 3
b 2
d 1
Name: count, dtype: int64
pd.value_counts(obj.to_numpy(), sort=False) # can be used with NumPy arrays
c 3
a 3
d 1
b 2
Name: count, dtype: int64
Membership testing with isin
obj
0 c
1 a
2 d
3 a
4 a
5 b
6 b
7 c
8 c
dtype: object
mask = obj.isin(["b","c"])
mask
0 True
1 False
2 False
3 False
4 False
5 True
6 True
7 True
8 True
dtype: bool
obj[mask] # filter with boolean array
0 c
5 b
6 b
7 c
8 c
dtype: object
Map values → positions
Index.get_indexer: map values to integer positions (or -1 if not found).
to_match = pd.Series(["c","a","b","b","c","a", "f"])
unique_vals = pd.Series(["c","b","a"])
pd.Index(unique_vals).get_indexer(to_match)
array([0, 2, 1, 1, 0, 2, -1])
Counts across multiple columns
data = pd.DataFrame({"Qu1":[1,3,4,3,4], "Qu2":[2,3,1,2,3], "Qu3":[1,5,2,4,4]})
data
Qu1 Qu2 Qu3
0 1 2 1
1 3 3 5
2 4 1 2
3 3 2 4
4 4 3 4
data["Qu1"].value_counts().sort_index() # histogram of Qu1
Qu1
1 1
3 2
4 2
Name: count, dtype: int64
result = data.apply(pd.value_counts).fillna(0) # histogram of all columns
result
Qu1 Qu2 Qu3
1 1.0 1.0 1.0
2 0.0 2.0 1.0
3 2.0 2.0 0.0
4 2.0 0.0 2.0
5 0.0 0.0 1.0
DataFrame.value_counts
Counts distinct rows (tuples) in the DataFrame.
data = pd.DataFrame({"a":[1,1,1,2,2], "b":[0,0,1,0,0]})
data
a b
0 1 0
1 1 0
2 1 1
3 2 0
4 2 0
data.value_counts()
a b
1 0 2
2 0 2
1 1 1
Name: count, dtype: int64
Takeaways
- unique, value_counts for 1-D distributions.
- isin builds Boolean filters; combine with [] or loc.
- For many columns, df.apply(pd.value_counts) + fillna(0) gives a compact histogram table.
Wrap-up
- What is pandas? Labeled, columnar data (Series/DataFrame) with alignment and NaN-aware ops.
- Apply functions: per row/column or element-wise.
- Order & compare: sort/rank; relate columns with correlation/covariance.
- Summarize: reductions & accumulations; be mindful of axis and duplicate labels.
Next: Advanced Topics in Pandas