Lecture 6: Numerical Computing and Data Wrangling

• Lectures 1-5 covered the Python language. This lecture introduces NumPy for numerical arrays and pandas for tables of data.

What We'll Cover

• Ch 11 (NumPy): arrays vs. Python lists, indexing and slicing, vectorized arithmetic and broadcasting, aggregations, random numbers, and matrix multiplication.

• Ch 12 (Pandas): Series and DataFrames, reading and writing data, selecting data, missing data, group-by, combining DataFrames, and time series.

• Our running data in the pandas part is the Fama-French 5 Factors from Ken French's data library.

Ch 11: NumPy

• NumPy (Numerical Python) is the fundamental package for numerical computing in Python.

• It provides an N-dimensional array object, along with functions for fast operations on these arrays.

• Most data analysis libraries you'll use later (pandas, scikit-learn, statsmodels) are built on top of NumPy.

Importing NumPy

• The conventional alias for NumPy is np.

import numpy as np

Arrays vs Python Lists

• A NumPy array stores elements of the same type in a contiguous block of memory.

• Python lists store pointers to objects scattered in memory.

• This makes NumPy arrays much faster and more memory-efficient for numerical work.

Speed comparison

• We compare the time to square 1 million numbers using a Python list vs a NumPy array.

# Using a Python list
py_list = list(range(1_000_000))
%timeit [x ** 2 for x in py_list]

121 ms ± 35 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

# Using a NumPy array
np_arr = np.arange(1_000_000)
%timeit np_arr ** 2

2.07 ms ± 533 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

• NumPy is typically 10-100x faster than equivalent pure-Python code.

Creating Arrays

From a Python List

• Pass a list to np.array() to create a one-dimensional array.

arr = np.array([1, 2, 3, 4, 5])
arr

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

type(arr)

numpy.ndarray

Multidimensional Arrays

• Pass a list of lists to create a 2D array (a matrix).

matrix = np.array([[1, 2, 3],
                   [4, 5, 6]])
matrix

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

Built-in Array Creation Functions

• np.zeros(shape) creates an array filled with zeros.
• np.ones(shape) creates an array filled with ones.
• np.arange(start, stop, step) is like range() but returns an array.
• np.linspace(start, stop, num) creates num evenly-spaced values.

np.zeros(5)

array([0., 0., 0., 0., 0.])

np.zeros((2, 3))

array([[0., 0., 0.],
       [0., 0., 0.]])

np.ones((3, 3))

array([[1., 1., 1.],
       [1., 1., 1.],
       [1., 1., 1.]])

np.arange(0, 10, 2)

array([0, 2, 4, 6, 8])

np.linspace(0, 1, 5)

array([0.  , 0.25, 0.5 , 0.75, 1.  ])

• np.eye(n) creates an n x n identity matrix.

np.eye(3)

array([[1., 0., 0.],
       [0., 1., 0.],
       [0., 0., 1.]])

Array Attributes

• shape: a tuple giving the size in each dimension.
• ndim: the number of dimensions.
• size: the total number of elements.
• dtype: the data type of the elements.

matrix = np.array([[1, 2, 3],
                   [4, 5, 6]])
matrix.shape

(2, 3)

matrix.ndim

2

matrix.size

6

matrix.dtype

dtype('int64')

Data Types (dtype)

• NumPy arrays are homogeneous: all elements share one dtype.

• Common dtypes include int64, float64, bool.

• You can set the dtype when creating an array.

np.array([1, 2, 3], dtype='float64')

array([1., 2., 3.])

• If you mix types, NumPy will upcast to the more general type.

# An int and a float together become floats
np.array([1, 2.5, 3])

array([1. , 2.5, 3. ])

Reshape

• reshape() returns an array with the same data but a new shape.

• The new shape must have the same total number of elements.

arr = np.arange(12)
arr

array([ 0,  1,  2,  3,  4,  5,  6,  7,  8,  9, 10, 11])

arr.reshape(3, 4)

array([[ 0,  1,  2,  3],
       [ 4,  5,  6,  7],
       [ 8,  9, 10, 11]])

arr.reshape(2, 6)

array([[ 0,  1,  2,  3,  4,  5],
       [ 6,  7,  8,  9, 10, 11]])

arr

array([ 0,  1,  2,  3,  4,  5,  6,  7,  8,  9, 10, 11])

• Using -1 tells NumPy to infer that dimension automatically.

arr.reshape(3, -1)

array([[ 0,  1,  2,  3],
       [ 4,  5,  6,  7],
       [ 8,  9, 10, 11]])

Note: Incompatible shapes will raise an error.

# This will fail: 12 elements can't reshape into 5 x 3 = 15
arr.reshape(5, 3)

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
/tmp/ipykernel_1707/3981055620.py in <cell line: 0>()
      1 # This will fail: 12 elements can't reshape into 5 x 3 = 15
----> 2 arr.reshape(5, 3)

ValueError: cannot reshape array of size 12 into shape (5,3)

• You can also transpose an array with .T.

mat = np.arange(6).reshape(2, 3)
mat

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

mat.T

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

Indexing and Slicing

One-Dimensional Arrays

• Indexing and slicing work just like with Python lists.

arr = np.array([10, 20, 30, 40, 50])
arr[0]

np.int64(10)

arr[-1]

np.int64(50)

arr[1:4]

array([20, 30, 40])

Two-Dimensional Arrays

• Use arr[row, col] syntax (more concise and computationally efficient than arr[row][col]).

• Multidimensional slicing along a specific axis requires the arr[row, col] syntax.

mat = np.array([[1, 2, 3],
                [4, 5, 6],
                [7, 8, 9]])
mat[0, 1]

np.int64(2)

• Use : to select all elements along an axis.

# All rows, column 0
mat[:, 0]

array([1, 4, 7])

• Chained indexing does not work for this use case.

mat[:][0]

array([1, 2, 3])

mat[:]

array([[1, 2, 3],
       [4, 5, 6],
       [7, 8, 9]])

# Row 1, all columns
mat[1, :]

array([4, 5, 6])

# Top-left 2x2 sub-matrix
mat[:2, :2]

array([[1, 2],
       [4, 5]])

Boolean Indexing

• You can index with a Boolean array to filter elements.

arr = np.array([1, -2, 3, -4, 5])
arr > 0

array([ True, False,  True, False,  True])

arr[arr > 0]

array([1, 3, 5])

Vectorized Arithmetic

• Arithmetic operations on NumPy arrays apply element-wise without a for loop.

• This is called vectorization.

arr = np.array([1, 2, 3, 4])
arr + 10

array([11, 12, 13, 14])

arr * 2

array([2, 4, 6, 8])

arr ** 2

array([ 1,  4,  9, 16])

a = np.array([1, 2, 3])
b = np.array([10, 20, 30])
a + b

array([11, 22, 33])

Broadcasting

• Broadcasting allows NumPy to do arithmetic on arrays with different shapes.

• NumPy triggers broadcasting automatically during element-wise arithmetic operations by evaluating the .shape attributes of the arrays involved.

• The smaller array is "stretched" across the larger one, without actually copying memory.

# A 2x3 matrix plus a 1D array of length 3
mat = np.array([[1, 2, 3],
                [4, 5, 6]])
row = np.array([10, 20, 30])

mat + row

array([[11, 22, 33],
       [14, 25, 36]])

• The 1D array row is broadcast across each row of mat.

# Column-wise broadcasting using a 2x1 array
col = np.array([[100],
                [200]])

mat + col

array([[101, 102, 103],
       [204, 205, 206]])

Aggregations

• NumPy provides fast functions to summarize arrays: sum, mean, std, min, max, median, etc.

arr = np.array([1, 2, 3, 4, 5])
arr.sum()

np.int64(15)

arr.mean()

np.float64(3.0)

arr.std()

np.float64(1.4142135623730951)

The axis Argument

• For multi-dimensional arrays, the axis argument controls which dimension to collapse.

• axis=0 collapses rows (Vertical Sum: one value per column).

• axis=1 collapses columns (Horizontal Sum: one value per row).

mat = np.array([[1, 2, 3],
                [4, 5, 6]])
mat.sum()

np.int64(21)

# Sum down each column
mat.sum(axis=0)

array([5, 7, 9])

# Sum across each row
mat.sum(axis=1)

array([ 6, 15])

Random Numbers (np.random)

Setting a Seed

• For reproducible results, set a random seed before generating random numbers.

• Why "42"?

  • Technically, any integer works as a seed (0, 1, 12345, etc.).

  • 42 is the "Answer to the Ultimate Question of Life, the Universe, and Everything" in The Hitchhiker's Guide to the Galaxy.

np.random.seed(42)

Generating Random Numbers

• np.random.rand(d0, d1, ...): uniform on [0, 1).
• np.random.randn(d0, d1, ...): standard normal (mean 0, std 1).
• np.random.randint(low, high, size): random integers from [low, high).
• np.random.choice(a, size): sample from an array.

np.random.seed(42)
np.random.rand(3)

array([0.37454012, 0.95071431, 0.73199394])

np.random.randn(2, 3)

array([[-1.11188012,  0.31890218,  0.27904129],
       [ 1.01051528, -0.58087813, -0.52516981]])

np.random.randint(0, 100, size=5)

array([20, 32, 75, 57, 21])

# Sample 4 items from a list
np.random.choice(['AAPL', 'GOOG', 'MSFT', 'AMZN', 'META'], size=4)

array(['META', 'AMZN', 'AAPL', 'AAPL'], dtype='<U4')

Matrix Multiplication

• For element-wise multiplication, use *.

• For matrix multiplication (dot product), use the @ operator or np.dot().

A = np.array([[1, 2],
              [3, 4]])
B = np.array([[5, 6],
              [7, 8]])

# Element-wise multiplication
A * B

array([[ 5, 12],
       [21, 32]])

# Matrix multiplication
A @ B

array([[19, 22],
       [43, 50]])

# Equivalent to:
np.dot(A, B)

array([[19, 22],
       [43, 50]])

• This is essential for linear regression, portfolio math, and many other applications you'll see in econ.

In-Class Exercise

11-1. Array Creation:

• Create a 1D NumPy array containing the integers from 10 to 30 (inclusive) using np.arange().
• Print its shape, ndim, and dtype.

11-2. Reshape:

• Take your array from 11-1 and reshape it into a 3 x 7 matrix.
• Then transpose it and print the result.

11-3. Simulating Returns:

• Set the random seed to 0.
• Use np.random.randn(252) to simulate 252 daily returns (one trading year) with mean 0 and std 1.
• Compute and print the mean, standard deviation, and maximum of these simulated returns.

11-4. Boolean Filtering:

• Using your simulated returns from 11-3, count how many of the returns were negative using a Boolean filter.
• Hint: a Boolean array can be summed because True counts as 1.

11-5. Matrix Multiplication:

• Create two 2 x 2 matrices: X = [[1, 2], [3, 4]] and Y = [[2, 0], [1, 2]].
• Compute their matrix product using @.
• Then compute their element-wise product using *. Compare the two results.

Ch 12: Pandas

• pandas is the main library for working with tabular data in Python.

• It provides two main data structures: the 1D Series and the 2D DataFrame.

• Think of a DataFrame as a spreadsheet or SQL table you can manipulate with code.

• Pandas is built on NumPy, so most NumPy concepts (vectorization, broadcasting) carry over.

Importing Pandas

• The conventional alias for pandas is pd.

import numpy as np
import pandas as pd

Series

• A Series is a one-dimensional labeled array, like a single column of a spreadsheet.

• Every value has a label (its index), and all values share a dtype.

Creating a Series

• The simplest way to create a Series is from a list.

prices = pd.Series([150.2, 2800.5, 305.7, 3300.1])
prices

• The numbers on the left are the index (auto-assigned 0, 1, 2, ... by default).

• The numbers on the right are the values.

Custom Index

• You can provide a custom index when you create the Series.

prices = pd.Series([150.2, 2800.5, 305.7, 3300.1],
                   index=['AAPL', 'GOOG', 'MSFT', 'AMZN'])
prices

Creating a Series from a Dictionary

• A dictionary's keys become the index, and the values become the data.

mkt_caps = pd.Series({
    'AAPL': 2_900_000,
    'GOOG': 1_700_000,
    'MSFT': 2_500_000,
    'AMZN': 1_400_000,
})
mkt_caps

Accessing Values

• You can index a Series by label or by position.

prices['AAPL']

np.float64(150.2)

prices[['AAPL', 'MSFT']]

Series Attributes

• values: the underlying NumPy array.
• index: the labels.
• dtype: the element data type.
• name: an optional name for the Series.

prices.values

array([ 150.2, 2800.5,  305.7, 3300.1])

prices.index

Index(['AAPL', 'GOOG', 'MSFT', 'AMZN'], dtype='object')

prices.dtype

dtype('float64')

• You can give a Series a name, which becomes its column header inside a DataFrame.

prices.name = 'price'
prices

DataFrame

• A DataFrame is a two-dimensional labeled table: rows and columns, each with an index.

• Each column is a Series. Columns can have different dtypes.

Creating a DataFrame from a Dictionary of Lists

• Each key becomes a column name; each list becomes the column's values.

data = {
    'ticker': ['AAPL', 'GOOG', 'MSFT', 'AMZN'],
    'price':  [150.2, 2800.5, 305.7, 3300.1],
    'sector': ['Tech', 'Tech', 'Tech', 'Consumer'],
}
df = pd.DataFrame(data)
df

Creating a DataFrame from a List of Dictionaries

• Each dictionary becomes a row.

rows = [
    {'ticker': 'AAPL', 'price': 150.2, 'sector': 'Tech'},
    {'ticker': 'GOOG', 'price': 2800.5, 'sector': 'Tech'},
    {'ticker': 'MSFT', 'price': 305.7, 'sector': 'Tech'},
]
pd.DataFrame(rows)

Creating a DataFrame from a NumPy Array

• Useful for numerical data. Supply your own columns and (optionally) index.

np.random.seed(42)
returns = np.random.randn(5, 3) / 100  # daily returns ~1% scale

pd.DataFrame(returns,
             columns=['AAPL', 'GOOG', 'MSFT'],
             index=['Mon', 'Tue', 'Wed', 'Thu', 'Fri'])

Setting an Index Column

• Use set_index() to promote an existing column to be the row index.

df = pd.DataFrame(data)
df = df.set_index('ticker')
df

• The DataFrame is now indexed by ticker symbol.

DataFrame Attributes

• shape: (n_rows, n_cols)
• columns: column labels
• index: row labels
• dtypes: dtype of each column

df.shape

(4, 2)

df.columns

Index(['price', 'sector'], dtype='object')

df.index

Index(['AAPL', 'GOOG', 'MSFT', 'AMZN'], dtype='object', name='ticker')

df.dtypes

Reading and Writing Data

• Pandas can read and write a wide variety of file formats: CSV, Excel, JSON, Parquet, SQL, and more.

• The most common is read_csv() for comma-separated values files.

• We'll try pandas-datareader first, which provides a clean Python API to Ken French's library.

• We include a fallback that downloads the CSV directly, in case package versions don't line up.

# Install pandas-datareader if not already available (Colab usually has it)
!pip install -q pandas-datareader

import io, zipfile, urllib.request
import pandas as pd

# Try pandas-datareader first (the cleanest path)
try:
    import pandas_datareader.data as web
    ff = web.DataReader('F-F_Research_Data_5_Factors_2x3',
                        'famafrench',
                        start='1990-01-01',
                        end='2023-12-31')
    ff5 = ff[0]
    print(f"Loaded via pandas-datareader: {ff5.shape}")
except Exception as e:
    # Fallback: download the CSV zip directly from Ken French's website
    print(f"pandas-datareader failed ({type(e).__name__}); falling back to direct download.")
    url = ("https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/"
           "ftp/F-F_Research_Data_5_Factors_2x3_CSV.zip")
    req = urllib.request.Request(url, headers={'User-Agent': 'Mozilla/5.0'})
    with urllib.request.urlopen(req) as r:
        zip_bytes = r.read()
    with zipfile.ZipFile(io.BytesIO(zip_bytes)) as z:
        csv_name = z.namelist()[0]
        with z.open(csv_name) as f:
            raw = f.read().decode('latin1')

    # The file has a text header; monthly data starts after a blank-line break.
    # Find the first line that begins with a 6-digit YYYYMM and parse from there.
    lines = raw.splitlines()
    start = next(i for i, ln in enumerate(lines)
                 if len(ln) >= 6 and ln[:6].isdigit())
    end = next((i for i in range(start, len(lines))
                if not (len(lines[i]) >= 6 and lines[i][:6].isdigit())),
               len(lines))
    monthly_csv = "Date,Mkt-RF,SMB,HML,RMW,CMA,RF\n" + "\n".join(lines[start:end])
    ff5 = pd.read_csv(io.StringIO(monthly_csv), index_col='Date')
    ff5.index = pd.to_datetime(ff5.index, format='%Y%m').to_period('M')
    print(f"Loaded via direct download: {ff5.shape}")

Loaded via pandas-datareader: (408, 6)

• The download cell stores the monthly data in ff5.

type(ff5)

pandas.core.frame.DataFrame
def __init__(data=None, index: Axes | None=None, columns: Axes | None=None, dtype: Dtype | None=None, copy: bool | None=None) -> None

/usr/local/lib/python3.13/dist-packages/pandas/core/frame.pyTwo-dimensional, size-mutable, potentially heterogeneous tabular data.

Data structure also contains labeled axes (rows and columns).
Arithmetic operations align on both row and column labels. Can be
thought of as a dict-like container for Series objects. The primary
pandas data structure.

Parameters
----------
data : ndarray (structured or homogeneous), Iterable, dict, or DataFrame
    Dict can contain Series, arrays, constants, dataclass or list-like objects. If
    data is a dict, column order follows insertion-order. If a dict contains Series
    which have an index defined, it is aligned by its index. This alignment also
    occurs if data is a Series or a DataFrame itself. Alignment is done on
    Series/DataFrame inputs.

    If data is a list of dicts, column order follows insertion-order.

index : Index or array-like
    Index to use for resulting frame. Will default to RangeIndex if
    no indexing information part of input data and no index provided.
columns : Index or array-like
    Column labels to use for resulting frame when data does not have them,
    defaulting to RangeIndex(0, 1, 2, ..., n). If data contains column labels,
    will perform column selection instead.
dtype : dtype, default None
    Data type to force. Only a single dtype is allowed. If None, infer.
copy : bool or None, default None
    Copy data from inputs.
    For dict data, the default of None behaves like ``copy=True``.  For DataFrame
    or 2d ndarray input, the default of None behaves like ``copy=False``.
    If data is a dict containing one or more Series (possibly of different dtypes),
    ``copy=False`` will ensure that these inputs are not copied.

    .. versionchanged:: 1.3.0

See Also
--------
DataFrame.from_records : Constructor from tuples, also record arrays.
DataFrame.from_dict : From dicts of Series, arrays, or dicts.
read_csv : Read a comma-separated values (csv) file into DataFrame.
read_table : Read general delimited file into DataFrame.
read_clipboard : Read text from clipboard into DataFrame.

Notes
-----
Please reference the :ref:`User Guide <basics.dataframe>` for more information.

Examples
--------
Constructing DataFrame from a dictionary.

>>> d = {'col1': [1, 2], 'col2': [3, 4]}
>>> df = pd.DataFrame(data=d)
>>> df
   col1  col2
0     1     3
1     2     4

Notice that the inferred dtype is int64.

>>> df.dtypes
col1    int64
col2    int64
dtype: object

To enforce a single dtype:

>>> df = pd.DataFrame(data=d, dtype=np.int8)
>>> df.dtypes
col1    int8
col2    int8
dtype: object

Constructing DataFrame from a dictionary including Series:

>>> d = {'col1': [0, 1, 2, 3], 'col2': pd.Series([2, 3], index=[2, 3])}
>>> pd.DataFrame(data=d, index=[0, 1, 2, 3])
   col1  col2
0     0   NaN
1     1   NaN
2     2   2.0
3     3   3.0

Constructing DataFrame from numpy ndarray:

>>> df2 = pd.DataFrame(np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]]),
...                    columns=['a', 'b', 'c'])
>>> df2
   a  b  c
0  1  2  3
1  4  5  6
2  7  8  9

Constructing DataFrame from a numpy ndarray that has labeled columns:

>>> data = np.array([(1, 2, 3), (4, 5, 6), (7, 8, 9)],
...                 dtype=[("a", "i4"), ("b", "i4"), ("c", "i4")])
>>> df3 = pd.DataFrame(data, columns=['c', 'a'])
...
>>> df3
   c  a
0  3  1
1  6  4
2  9  7

Constructing DataFrame from dataclass:

>>> from dataclasses import make_dataclass
>>> Point = make_dataclass("Point", [("x", int), ("y", int)])
>>> pd.DataFrame([Point(0, 0), Point(0, 3), Point(2, 3)])
   x  y
0  0  0
1  0  3
2  2  3

Constructing DataFrame from Series/DataFrame:

>>> ser = pd.Series([1, 2, 3], index=["a", "b", "c"])
>>> df = pd.DataFrame(data=ser, index=["a", "c"])
>>> df
   0
a  1
c  3

>>> df1 = pd.DataFrame([1, 2, 3], index=["a", "b", "c"], columns=["x"])
>>> df2 = pd.DataFrame(data=df1, index=["a", "c"])
>>> df2
   x
a  1
c  3

ff5.head()

• Values are in percent (so 1.5 means a 1.5% return).

Writing to CSV

• Use to_csv() to save a DataFrame as a CSV file.

ff5.to_csv('ff5_factors.csv')

Reading from CSV

• Use read_csv() to read a CSV file back.

• The first column is treated as a regular column by default. Use index_col=0 to make it the index.

ff5 = pd.read_csv('ff5_factors.csv', index_col=0)
ff5.head()

Other File Formats

• pd.read_excel('file.xlsx') reads Excel files (requires openpyxl).

• pd.read_json('file.json') reads JSON files (recall Ch 10).

• pd.read_parquet('file.parquet') reads efficient Parquet files.

• Each has a matching to_...() writer method.

Inspecting a DataFrame

• Always start by looking at your data with a few quick diagnostic methods.

head() and tail()

# First 5 rows by default
ff5.head()

# Last 3 rows
ff5.tail(3)

shape, columns, index, dtypes

ff5.shape

(408, 6)

ff5.columns

Index(['Mkt-RF', 'SMB', 'HML', 'RMW', 'CMA', 'RF'], dtype='object')

ff5.dtypes

info()

• A compact summary: row count, column count, dtypes, and non-null counts.

ff5.info()

<class 'pandas.core.frame.DataFrame'>
Index: 408 entries, 1990-01 to 2023-12
Data columns (total 6 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   Mkt-RF  408 non-null    float64
 1   SMB     408 non-null    float64
 2   HML     408 non-null    float64
 3   RMW     408 non-null    float64
 4   CMA     408 non-null    float64
 5   RF      408 non-null    float64
dtypes: float64(6)
memory usage: 22.3+ KB

describe()

• Summary statistics for each numeric column: count, mean, std, min, quartiles, max.

ff5.describe()

value_counts()

• For a single column (Series), counts occurrences of each unique value.

# Add a 'decade' label column to demonstrate
ff5_demo = ff5.copy()
ff5_demo['decade'] = (ff5_demo.index.astype(str).str[:3] + '0s')
ff5_demo['decade'].value_counts()

Selecting Data

Selecting Columns

• Use bracket notation with the column name to get a single column (a Series).

ff5['Mkt-RF'].head()

• Pass a list of column names to get a DataFrame with multiple columns.

ff5[['Mkt-RF', 'SMB', 'HML']].head()

Note: You can also use attribute access (ff5.SMB), but it only works for valid Python identifiers, so df.Mkt-RF would fail because of the hyphen.

Selecting Rows with .loc and .iloc

• .loc[label]: select rows by label (index value).
• .iloc[position]: select rows by integer position.

# Select a specific row by its index label
ff5.loc['1990-01']

# Select by position: the first row
ff5.iloc[0]

Slicing with .loc and .iloc

# .loc slicing is INCLUSIVE of the endpoint
ff5.loc['2020-01':'2020-06']

# .iloc slicing is EXCLUSIVE of the endpoint (like Python lists)
ff5.iloc[0:5]

Selecting Rows and Columns Together

# All rows in 2020, just the market and SMB factors
ff5.loc['2020-01':'2020-12', ['Mkt-RF', 'SMB']]

# First 3 rows, first 2 columns
ff5.iloc[:3, :2]

Boolean Filtering

• A Boolean Series (one True/False per row) can be used to select rows.

# Months where the market beat the risk-free rate by more than 5%
mask = ff5['Mkt-RF'] > 5
mask.head()

ff5[mask].head()

• Combine conditions with & (and), | (or), ~ (not). Wrap each condition in parentheses.

# Up market AND value premium positive
ff5[(ff5['Mkt-RF'] > 0) & (ff5['HML'] > 0)].head()

Note: Forgetting the parentheses will raise an error because Python's and/or don't work element-wise on Series.

# This will fail
ff5[ff5['Mkt-RF'] > 0 and ff5['HML'] > 0]

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
/tmp/ipykernel_1707/2254064072.py in <cell line: 0>()
      1 # This will fail
----> 2 ff5[ff5['Mkt-RF'] > 0 and ff5['HML'] > 0]

/usr/local/lib/python3.13/dist-packages/pandas/core/generic.py in __nonzero__(self)
   1575     @final
   1576     def __nonzero__(self) -> NoReturn:
-> 1577         raise ValueError(
   1578             f"The truth value of a {type(self).__name__} is ambiguous. "
   1579             "Use a.empty, a.bool(), a.item(), a.any() or a.all()."

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

isin()

• Check whether each value is in a given list.

# Suppose we have a column with categories
ff5_demo = ff5.copy()
ff5_demo['decade'] = (ff5_demo.index.astype(str).str[:3] + '0s')

ff5_demo[ff5_demo['decade'].isin(['1990s', '2000s'])].head()

between()

• Select rows where a column's value falls in a range (inclusive on both ends).

# Months with moderate market returns (1% to 3%)
ff5[ff5['Mkt-RF'].between(1, 3)].head()

query()

• A string-based filter that can be easier to read for complex conditions.

ff5.query('`Mkt-RF` > 5 and SMB > 0').head()

• Note the backticks around Mkt-RF because of the hyphen in the column name.

Column Operations

Adding a New Column

• Assigning to df['new_col'] creates a new column.

# Compute the total of the five factors for each month
ff5_demo = ff5.copy()
ff5_demo['Total'] = ff5_demo[['Mkt-RF', 'SMB', 'HML', 'RMW', 'CMA']].sum(axis=1)
ff5_demo.head()

assign() for Chaining

• assign() returns a new DataFrame with the added column, leaving the original untouched.

• Useful when you want to chain operations.

ff5.assign(Mkt_squared=lambda d: d['Mkt-RF'] ** 2).head()

Dropping Columns

• Use drop() with columns=... to remove one or more columns.

ff5_demo = ff5.copy()
ff5_demo = ff5_demo.drop(columns=['RF'])
ff5_demo.head()

• You can also drop rows: df.drop(index=['1990-01', '1990-02']).

Renaming Columns

• Use rename(columns={...}) with a dictionary mapping old to new names.

ff5.rename(columns={'Mkt-RF': 'mkt_rf', 'RF': 'rf'}).head()

Missing Data

• Real datasets almost always have missing values.

• Pandas represents missing data as NaN (Not a Number), borrowed from NumPy.

Creating a Toy Dataset with Missing Values

toy = pd.DataFrame({
    'firm':     ['A', 'B', 'C', 'D'],
    'revenue':  [100, np.nan, 150, 200],
    'profit':   [20, 15, np.nan, 40],
    'industry': ['Tech', 'Tech', 'Auto', None],
})
toy

Detecting Missing Values

• isna() returns a Boolean DataFrame: True where the value is missing.
• notna() is the opposite.

toy.isna()

# Count missing values per column
toy.isna().sum()

Dropping Missing Values

• dropna() removes rows that contain any NaN by default.

toy.dropna()

# Only drop rows where ALL values are missing
toy.dropna(how='all')

# Drop rows where a specific column is missing
toy.dropna(subset=['revenue'])

Filling Missing Values

• fillna(value) replaces all NaN with a constant.

toy.fillna(0)

• You can fill different columns with different values.

toy.fillna({'revenue': toy['revenue'].mean(),
            'profit': 0,
            'industry': 'Unknown'})

• ffill() (forward-fill) propagates the last valid value forward.

• bfill() (backward-fill) uses the next valid value.

• These are especially useful for time series data.

toy['revenue'].ffill()

toy['profit'].bfill()

Sorting

sort_values()

• Sort rows by the values in one or more columns.

# Best market months
ff5.sort_values('Mkt-RF', ascending=False).head()

# Sort by two columns
ff5.sort_values(['Mkt-RF', 'SMB'], ascending=[False, True]).head()

sort_index()

• Sort by the index (useful after operations that shuffle rows).

ff5.sort_index().head()

Transformations: apply, map, and .str

apply() on a Series

• apply() runs a function on each element (or each row/column for a DataFrame).

# Convert factor returns from percent to decimal
ff5['Mkt-RF'].apply(lambda x: x / 100).head()

• For simple element-wise math, vectorized operations (ff5['Mkt-RF'] / 100) are faster than apply(). Use apply() for more complex logic.

apply() on a DataFrame

• By default apply() operates column-wise (each column is passed in as a Series).

# Range (max - min) of each column
ff5.apply(lambda col: col.max() - col.min())

map() on a Series

• map() substitutes each value using a dictionary or function. It is handy for recoding labels.

# Label whether each month was an 'up' or 'down' market
signs = ff5['Mkt-RF'].apply(lambda x: 'up' if x > 0 else 'down')
signs.head()

# Map labels to numeric flags
signs.map({'up': 1, 'down': 0}).head()

The .str Accessor

• The .str accessor on a string Series gives you vectorized string methods.

tickers = pd.Series(['aapl', 'goog', 'msft', 'amzn'])
tickers.str.upper()

tickers.str.startswith('a')

tickers.str.len()

GroupBy: Split-Apply-Combine

• groupby() is one of the most commonly used tools in pandas, especially for panel data.

• The pattern has three steps:

  1. Split the data into groups based on some key.
  2. Apply a function to each group.
  3. Combine the results into a new DataFrame.

A Simple Example

• We add a decade column to the FF data, then group by decade.

ff5_g = ff5.copy()
ff5_g['decade'] = (ff5_g.index.astype(str).str[:3] + '0s')
ff5_g.head()

# Group by decade
grouped = ff5_g.groupby('decade')
grouped

<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7fbb31155400>

• groupby returns a lazy object. Nothing has been computed yet.

Aggregating Groups

• Apply an aggregation like mean(), sum(), std(), or count() to compute one value per group.

# Average factor returns in each decade
grouped.mean()

# How many months in each decade?
grouped.size()

Selecting Columns in a GroupBy

# Just the market premium, by decade
ff5_g.groupby('decade')['Mkt-RF'].mean()

Multiple Aggregations with agg()

• Pass a list of functions to compute several statistics at once.

ff5_g.groupby('decade')['Mkt-RF'].agg(['mean', 'std', 'min', 'max'])

Different Aggregations for Different Columns

ff5_g.groupby('decade').agg({
    'Mkt-RF': 'mean',
    'SMB':    'std',
    'HML':    ['mean', 'std'],
})

Grouping by Multiple Columns

# Make a 'sign' column for market direction and group on (decade, sign)
ff5_g['mkt_sign'] = np.where(ff5_g['Mkt-RF'] > 0, 'up', 'down')
ff5_g.groupby(['decade', 'mkt_sign'])['Mkt-RF'].mean()

transform(): Broadcast Group Aggregates Back

• transform() returns a result with the same shape as the input: each row gets the value for its group.

• Typical uses include within-group demeaning (fixed effects) and standardizing by group.

# Compute each month's market return minus its decade's mean
decade_mean = ff5_g.groupby('decade')['Mkt-RF'].transform('mean')
ff5_g['Mkt-RF_demeaned'] = ff5_g['Mkt-RF'] - decade_mean
ff5_g[['Mkt-RF', 'decade', 'Mkt-RF_demeaned']].head()

• This is exactly the within-transformation used in panel-data fixed-effects estimation.

Combining DataFrames

concat()

• pd.concat([df1, df2]) stacks DataFrames along an axis.

• axis=0 (default) stacks vertically (more rows).

• axis=1 joins horizontally (more columns).

a = pd.DataFrame({'x': [1, 2], 'y': [3, 4]})
b = pd.DataFrame({'x': [5, 6], 'y': [7, 8]})

pd.concat([a, b])

# Reset the index to get clean row labels
pd.concat([a, b], ignore_index=True)

# Stacking horizontally
pd.concat([a, b], axis=1)

merge(): SQL-Style Joins

• merge() combines two DataFrames using one or more key columns, like a SQL JOIN.

firms = pd.DataFrame({
    'ticker': ['AAPL', 'GOOG', 'MSFT', 'TSLA'],
    'sector': ['Tech', 'Tech', 'Tech', 'Auto'],
})

returns = pd.DataFrame({
    'ticker': ['AAPL', 'GOOG', 'MSFT', 'AMZN'],
    'ret':    [0.05, 0.02, 0.03, 0.04],
})

# Inner join: only tickers in BOTH frames
pd.merge(firms, returns, on='ticker', how='inner')

# Left join: keep ALL rows from firms; fill missing with NaN
pd.merge(firms, returns, on='ticker', how='left')

# Right join: keep all rows from returns
pd.merge(firms, returns, on='ticker', how='right')

# Outer join: keep everything from both
pd.merge(firms, returns, on='ticker', how='outer')

Merging on different column names:

• Use left_on and right_on when the key columns have different names in the two frames.

# Suppose one frame calls it 'ticker' and another 'symbol'
returns_alt = returns.rename(columns={'ticker': 'symbol'})
pd.merge(firms, returns_alt, left_on='ticker', right_on='symbol', how='inner')

Time Series

• Financial and economic data is typically indexed by date.

• Pandas has rich tools for working with dates: parsing, resampling, rolling windows, and more.

Converting Strings to Dates

• The FF index is currently strings like '1990-01'. Convert it to a real DatetimeIndex.

ff5.index = pd.to_datetime(ff5.index, format='%Y-%m')
ff5.head()

ff5.index

DatetimeIndex(['1990-01-01', '1990-02-01', '1990-03-01', '1990-04-01',
               '1990-05-01', '1990-06-01', '1990-07-01', '1990-08-01',
               '1990-09-01', '1990-10-01',
               ...
               '2023-03-01', '2023-04-01', '2023-05-01', '2023-06-01',
               '2023-07-01', '2023-08-01', '2023-09-01', '2023-10-01',
               '2023-11-01', '2023-12-01'],
              dtype='datetime64[ns]', name='Date', length=408, freq=None)

Selecting by Date

• With a DatetimeIndex, you can slice with date strings.

# All of 2020
ff5.loc['2020'].head()

# A specific date range
ff5.loc['2008-01':'2009-12']

resample(): Time-Based Aggregation

• resample() is like groupby() for time series: it groups rows by a time frequency.

• Common frequencies: 'D' daily, 'W' weekly, 'ME' month-end, 'QE' quarter-end, 'YE' year-end.

• (Older pandas versions used 'M', 'Q', 'Y'. The newer aliases ending in E are explicit and forward-compatible.)

# Aggregate monthly factors up to annual averages
ff5.resample('YE').mean().head()

# Sum monthly returns within each year (approximate annual return)
ff5[['Mkt-RF', 'SMB', 'HML']].resample('YE').sum().head()

Rolling Windows

• rolling(window=k) creates a sliding window of size k for moving statistics.

# 12-month rolling mean of the market premium
ff5['Mkt-RF'].rolling(window=12).mean().head(20)

# 12-month rolling volatility
ff5['Mkt-RF'].rolling(window=12).std().tail()

• The first window-1 rows are NaN because there aren't enough prior observations.

pct_change(): Computing Returns

• pct_change() computes the percent change between consecutive rows.

• This is the standard way to turn a price series into a return series.

# Build a synthetic 'price' series by treating the risk-free rate as a deposit
# (Just for illustration of pct_change)
np.random.seed(42)
prices = pd.Series(100 * (1 + np.random.randn(10) / 100).cumprod())
prices

prices.pct_change()

• The first value is NaN because there's no prior price to compare to.

Reshape Preview: pivot and melt

• Data can be in wide format (one row per entity, columns for each variable/period) or long format (one row per observation).

• pivot and melt convert between the two.

A Long-Format Toy Dataset

long = pd.DataFrame({
    'year':   [2020, 2020, 2021, 2021, 2022, 2022],
    'firm':   ['A',  'B',  'A',  'B',  'A',  'B'],
    'profit': [10,   15,   12,   18,   14,   20],
})
long

pivot(): Long → Wide

• Spread one column's values across new columns.

wide = long.pivot(index='year', columns='firm', values='profit')
wide

melt(): Wide → Long

• Unstack columns back into rows. Useful for plotting and many statistical functions that expect long format.

wide.reset_index().melt(id_vars='year',
                          value_vars=['A', 'B'],
                          var_name='firm',
                          value_name='profit')

In-Class Exercise

12-1. Building a DataFrame:

• Create a DataFrame named portfolio with four columns:
  • ticker: ['AAPL', 'GOOG', 'MSFT', 'AMZN', 'TSLA']
  • price: [180, 140, 330, 130, 250]
  • shares: [100, 50, 30, 80, 20]
  • sector: ['Tech', 'Tech', 'Tech', 'Consumer', 'Auto']
• Print the DataFrame and use info() to inspect dtypes and non-null counts.

12-2. New Columns and Filtering:

• Add a column value to your portfolio equal to price * shares.
• Print just the rows where value > 5000.
• Sort the full DataFrame by value in descending order.

12-3. GroupBy:

• Using your portfolio from 12-2, compute the total value invested in each sector with groupby('sector')['value'].sum().
• Then use agg(['sum', 'mean', 'count']) on the same group to compute three statistics at once.

12-4. Loading Fama-French Data:

• Re-read the file ff5_factors.csv we saved earlier with pd.read_csv(..., index_col=0).
• Convert the index to datetime with pd.to_datetime(..., format='%Y-%m').
• Report the start date, end date, and number of rows.

12-5. Filtering Returns:

• Find every month in which the market premium (Mkt-RF) was greater than 10%.
• How many such months are there? Print them.

12-6. Rolling Statistics:

• Compute the 24-month rolling mean and 24-month rolling standard deviation of Mkt-RF.
• Print the most recent five values of each.

12-7. Resample to Annual:

• Use resample('YE').sum() to compute the approximate annual sum of each Fama-French factor.
• Then sort this annual DataFrame by Mkt-RF in descending order and print the top 5 best years for the market premium.

12-8. Merge and Group:

• Construct a DataFrame sectors with two columns:
  • ticker: ['AAPL', 'GOOG', 'MSFT', 'AMZN', 'TSLA', 'F', 'GM']
  • industry: ['Software', 'Software', 'Software', 'Retail', 'Auto', 'Auto', 'Auto']
• Merge it with your portfolio DataFrame on ticker using a left join.
• Group the merged frame by industry and report the total value per industry.