Topic 2: DataFrames

Introduction to Business Analytics — HKUST

Prof. Xuhu Wan

Recap + Today’s Roadmap

Last time (Topic 1):

• Lists, list comprehension
• pandas Series (1D)
• NumPy arrays

Today (Topic 2):

• pandas DataFrame (2D)
• The “spreadsheet” of Python
• Load, explore, select, modify, plot

Workflow:

• Series (1D) — done
• DataFrame (2D) — today
• Select & Filter
• Modify & Compute
• Analyze & Plot

Table of Contents

1. Loading and Exploring Data
2. Selecting Data
3. Creating and Modifying Data — and Method Chaining
4. Key Statistical Methods
5. Handling Missing Values
6. Plotting with Pandas
7. Standardization
8. Time Series Methods

Loading and Exploring Data

Learning Goals

• Understand what a DataFrame is
• Load data from CSV files and the isom2600 package
• Explore data with .head(), .shape, .dtypes, .describe()

What Is a DataFrame?

Why it matters

A DataFrame is like an Excel spreadsheet in Python — rows, columns, and an index. Each column is a Series.

import pandas as pd

# Load from a CSV file
df = pd.read_csv("data.csv")

# Or load from the course package
df = isom2600.data.sp500()

Schema illustration:

Index	Open	High	Low	Close
2020-01	3244	3285	3214	3258
2020-02	3248	3260	3225	3245
2020-03	3230	3251	3210	3240

Rows = observations; columns = variables (each column is a Series).

First Look at Your Data

Always do this first — it’s like meeting someone new, say hello first!

df = isom2600.data.sp500()

df.head()       # First 5 rows
df.tail()       # Last 5 rows
df.shape        # (rows, columns)
df.dtypes       # Data type of each column
df.columns      # List of column names

Key takeaway

Step 1 of every analysis: Load the data, then run .head(), .shape, and .dtypes to understand what you have.

Summary Statistics with .describe()

• count: non-missing values, mean: average, std: spread
• min/max: extremes, 25%/50%/75%: quartiles

Try It! — Load and Explore

Try it!

Load the S&P 500 data and answer:

1. How many rows and columns?
2. What are the column names?
3. What is the average closing price?
4. What is the maximum trading volume?

Hint: df.shape, df.columns, df['Close'].mean(), df['Volume'].max()

Selecting Data

Learning Goals

• Select one column (returns a Series) and multiple columns (returns a DataFrame)
• Select rows with .head(), .tail(), .iloc[]
• Filter rows using conditions

Select One Column — Returns a Series

Why it matters

Business: “I only want to see the Revenue column.” Single brackets = single column = Series.

Select Multiple Columns — Returns a DataFrame

Key takeaway

Single brackets df['col'] → Series

Double brackets df[['col1','col2']] → DataFrame

Select Rows

Key takeaway

.iloc[] selects by integer position (just like list indexing). Use it when you know the row number.

Conditional Selection — Filtering Rows

How it works:

1. df['Return'] < -2 creates a boolean mask (True/False for each row)
2. df[mask] keeps only the True rows

Multiple Conditions

Key takeaway

Use & for AND, | for OR.

Important: Each condition must be in parentheses!

Try it!

Find customers with income above median AND spending above $500.

Hint: df[(df['Income'] > df['Income'].median()) & (df['Spending'] > 500)]

Label-Based Selection with .loc[]

Key takeaway

.loc[] = label names. .iloc[] = integer positions.

When the index is dates, .loc['2020-01':'2020-03'] is far more readable than .iloc[0:65].

.loc[] is Inclusive, .iloc[] is Exclusive

Important

.loc['b':'d'] returns rows b, c, d — the end label 'd' is included.

.iloc[1:3] returns positions 1, 2 — the end index 3 is excluded (just like Python list slicing).

Train / Test Split — Time Series

df = isom2600.data.sp500()
df.head(3)
#              Open    High     Low   Close     Volume
# 2019-12-31  3215.2  3224.0  3212.1  3230.8  2.89e+09
# 2020-01-02  3244.7  3258.1  3235.5  3257.9  3.46e+09
# 2020-01-03  3226.4  3246.2  3222.3  3234.9  3.46e+09

n = len(df)
train_size = int(0.8 * n)

# Time series: first 80% train, last 20% test
train = df.iloc[:train_size]
test  = df.iloc[train_size:]

print(f"Train: {len(train)}, Test: {len(test)}")

Why it matters

For time series: never shuffle — temporal order matters. Shuffling would leak future information into the training set.

Train / Test Split — Cross-Sectional (Shuffle)

df = isom2600.data.publicCompany()
df.head(3)
#       sector              marketCap  currentPrice  forwardPE  beta
# SI    Financial Services  2.13e+09   67.17         8.46       3.04
# LESL  Consumer Cyclical   2.52e+09   13.77        13.77        NaN
# CPB   Consumer Defensive  1.51e+10   50.37        16.51       0.36

n = len(df)
train_size = int(0.8 * n)

# Cross-sectional: shuffle first, then split
df_shuffled = df.sample(frac=1, random_state=42)
train = df_shuffled.iloc[:train_size]
test  = df_shuffled.iloc[train_size:]

print(f"Train: {len(train)}, Test: {len(test)}")

Key takeaway

Cross-sectional data has no time order → shuffle with .sample(frac=1, random_state=42) before splitting. random_state makes the shuffle reproducible.

Creating and Modifying Data — and Method Chaining

Learning Goals

• Add new computed columns
• Create conditional columns with list comprehension
• Drop unwanted columns or rows

Add a New Column

Why it matters

Columns are Series, so arithmetic between columns is element-wise (row by row). No loops needed!

Conditional Column

Key takeaway

Use list comprehension to create categorical columns from numerical data. This is data labeling!

Drop Columns and Rows

Key takeaway

.drop(columns=...) removes columns by name.

.drop() returns a new DataFrame — the original is unchanged (unless you use inplace=True).

Method Chaining — Readable Pipelines

Why it matters

Method chaining reads like plain English: “take Close, compute returns, drop NaN, sort descending, show top 5.”

Each method returns a Series/DataFrame, so the next method can be called immediately. Use parentheses () to wrap multiline chains.

Key Statistical Methods

Learning Goals

• Compute column-level statistics
• Understand axis=0 (down columns) vs. axis=1 (across rows)
• Find correlations between variables

Column Statistics

Why it matters

Finding the best and worst days is one of the first things analysts do when studying market behavior.

Understanding axis — Rows vs. Columns

axis visualization:

         col A   col B
row 0 |   10  |  20
row 1 |   30  |  40
row 2 |   50  |  60

axis=0  down (collapse rows)
axis=1  right (collapse columns)

Key takeaway

axis=0 collapses rows ⇒ one result per column (default).

axis=1 collapses columns ⇒ one result per row.

axis in Action

Important

Most pandas methods default to axis=0 (operate down each column). You only need to write axis=1 when you want to compute across columns for each row.

Correlation — Which Variables Move Together?

Key takeaway

.corr() computes pairwise Pearson correlation. Values range from \(-1\) (perfectly opposite) to \(+1\) (perfectly together).

Business: Is there a correlation between ad spend and revenue?

Handling Missing Values

Learning Goals

• Detect missing values with .isna()
• Fill missing values with .fillna()
• Drop missing values with .dropna()

Why Do We Have NaN?

Why it matters

Real-world data is messy. Sensors fail, surveys get skipped, records get lost. Pandas represents missing values as NaN (Not a Number).

Fill Missing Values

Drop Missing Values

Key takeaway

When to fill: You need every row and can estimate the missing value reasonably.

When to drop: Missing values are few (\(<5\%\)) and random.

Never: Ignore NaN and hope for the best!

dropna() — Rows vs. Columns with axis

Important

dropna() defaults to axis=0 (drop rows).

dropna(axis=1) drops columns — useful when an entire column is mostly empty.

Use subset= to only check specific columns instead of all.

Plotting with Pandas

Learning Goals

• Create line charts, histograms, bar charts, and box plots
• Customize plots with titles, labels, and colors
• Combine multiple charts using subplots

One Method, Four Chart Types

Key takeaway

pandas integrates with matplotlib. The .plot() method handles most chart types. Customize with figsize, title, color, xlabel, ylabel.

Four Charts at a Glance

Chart	Code	Use for
Line	df['Close'].plot()	Trends over time
Histogram	.plot(kind='hist')	Distribution of one variable
Bar	.plot(kind='bar')	Comparing categories
Box	.plot(kind='box')	Spread, median, outliers

Tip: screenshot this reference slide.

Try It! — Plot the Data

Try it!

Using the S&P 500 data:

1. Plot the closing price as a line chart

2. Plot the distribution of daily returns as a histogram

3. Count Bull vs Bear days and plot as a bar chart

4. Bonus: Which month had the highest average closing price?

   Hint: Convert index to datetime, then use .resample('ME').mean()

Standardization

Learning Goals

• Apply min-max scaling to bound data in \([0,1]\)
• Apply z-score standardization for bell-shaped data
• Know when to use each method

Min-Max Standardization — Scale to \([0,1]\)

\[x_{\text{scaled}} = \dfrac{x - \min}{\max - \min}\]

Why it matters

Use min-max when you need bounded output in \([0,1]\) — e.g., neural network inputs, or comparing variables with very different scales (price vs. volume).

Z-Score Standardization

\[x_{\text{std}} = \dfrac{x - \mu}{\sigma} \qquad \Rightarrow \qquad \mu_{\text{new}} = 0,\; \sigma_{\text{new}} = 1\]

Key takeaway

Min-max: Use when distribution is unknown or bounded output required.

Z-score: Use when data is roughly bell-shaped — preserves relative distances.

Neither: Use when scale is meaningful (e.g., price ratios, percentages).

Time Series Methods

Learning Goals

• Convert string dates to datetime with pd.to_datetime()
• Compute lags with .shift() and returns with .pct_change()
• Compute rolling statistics and cumulative aggregates

pd.to_datetime() — Parse Date Strings

Key takeaway

Always convert date strings to datetime — it unlocks date filtering, resampling, and all time-arithmetic operations.

.shift() and .pct_change()

Key takeaway

.shift(k) lags by \(k\) periods. .pct_change(k) computes \(\frac{x_t - x_{t-k}}{x_{t-k}}\). The first \(k\) rows are NaN.

Rolling Statistics — Moving Windows

Why it matters

Moving averages smooth noise. Traders use MA-20 (short-term), MA-50 (medium), MA-200 (long-term) as trend signals.

Rolling std = realized volatility — a key risk input.

Key takeaway

.rolling(n).mean() computes the average over the last \(n\) observations.

Cumulative Methods

Key takeaway

.cumprod() compounds returns. .cummax() tracks the running peak.

Drawdown \(= \frac{\text{price} - \text{running max}}{\text{running max}}\) is a standard risk metric.

Takeaway — The DataFrame Workflow

The 6-step pipeline:

1. Load — read_csv()
2. Explore — head(), shape, dtypes
3. Select — df[cond], .loc[], .iloc[]
4. Modify — df['new'] = ...
5. Analyze — describe(), corr()
6. Plot — .plot()

Key takeaway

Every data analysis follows this pipeline. Today you learned the tools for each step. Practice on your own data!