Pandas DataFrames¶
The DataFrame is the analyst's spreadsheet with superpowers. It's a labelled, two-dimensional table — rows and columns — but one you can manipulate programmatically, join to other DataFrames, reshape, and feed directly into visualisation or machine learning code.
Learning Objectives¶
- Create DataFrames from CSV files and dictionaries
- Inspect a DataFrame:
.shape,.info(),.describe(),.head(),.tail() - Select columns and rows by label and position
- Add, rename, and drop columns
- Work with data types
Creating a DataFrame¶
From a CSV file (most common)¶
import pandas as pd
df = pd.read_csv("sales_data.csv", parse_dates=["order_date"])
print(df.shape) # (1000, 8) — 1000 rows, 8 columns
print(df.head(3)) # first 3 rows
From a dictionary¶
data = {
"order_id": ["O001", "O002", "O003", "O004"],
"customer_id":["C001", "C003", "C002", "C001"],
"product": ["Headphones", "Standing Desk", "Book", "Yoga Mat"],
"quantity": [2, 1, 3, 1],
"unit_price": [149.00, 389.00, 49.99, 35.99],
"status": ["completed", "completed", "pending", "completed"],
}
df = pd.DataFrame(data)
print(df)
Output:
order_id customer_id product quantity unit_price status
0 O001 C001 Headphones 2 149.00 completed
1 O002 C003 Standing Desk 1 389.00 completed
2 O003 C002 Book 3 49.99 pending
3 O004 C001 Yoga Mat 1 35.99 completed
Inspecting a DataFrame¶
Always run these when you first load a new dataset:
# Shape: (rows, columns)
print(df.shape) # (4, 6)
# Column names and dtypes
print(df.info())
# <class 'pandas.core.frame.DataFrame'>
# RangeIndex: 4 entries, 0 to 3
# Data columns (total 6 columns):
# # Column Non-Null Count Dtype
# 0 order_id 4 non-null object
# 1 customer_id 4 non-null object
# 2 product 4 non-null object
# 3 quantity 4 non-null int64
# 4 unit_price 4 non-null float64
# 5 status 4 non-null object
# Summary statistics for numeric columns
print(df.describe())
# First / last N rows
print(df.head(3))
print(df.tail(2))
# Count missing values
print(df.isnull().sum())
# Unique values in a column
print(df["status"].unique()) # ['completed' 'pending']
print(df["status"].value_counts()) # completed 3 / pending 1
Selecting Columns¶
# Single column → returns a Series
orders = df["order_id"]
print(type(orders)) # <class 'pandas.core.series.Series'>
# Multiple columns → returns a DataFrame
subset = df[["order_id", "customer_id", "unit_price"]]
print(type(subset)) # <class 'pandas.core.frame.DataFrame'>
Series vs DataFrame
A Series is a single column with an index. A DataFrame is a collection of Series sharing the same index. Both have labels, not just positions.
Selecting Rows¶
.loc — label-based selection¶
# Single row by index label
print(df.loc[0]) # first row (index 0)
print(df.loc[1:3]) # rows with labels 1, 2, 3 (inclusive on both ends)
# Rows and columns together
print(df.loc[0:2, ["order_id", "unit_price"]])
.iloc — position-based selection¶
print(df.iloc[0]) # first row (position 0)
print(df.iloc[1:3]) # rows at positions 1 and 2 (stop is exclusive)
print(df.iloc[0:2, 0:3]) # first 2 rows, first 3 columns
print(df.iloc[-1]) # last row
loc is inclusive, iloc is exclusive at the stop
df.loc[1:3] returns rows with labels 1, 2, AND 3 (three rows).
df.iloc[1:3] returns rows at positions 1 and 2 (two rows). Same as Python slice syntax.
Adding and Modifying Columns¶
# Add a calculated column
df["total"] = df["quantity"] * df["unit_price"]
# Add a conditional column
df["order_size"] = df["total"].apply(
lambda x: "Large" if x >= 200 else ("Medium" if x >= 50 else "Small")
)
# Rename columns
df = df.rename(columns={"unit_price": "price", "customer_id": "cid"})
# Drop columns
df = df.drop(columns=["order_size"])
# Reorder columns
cols = ["order_id", "cid", "product", "quantity", "price", "total", "status"]
df = df[cols]
Data Types¶
# Check types
print(df.dtypes)
# Convert types
df["quantity"] = df["quantity"].astype(int)
df["price"] = df["price"].astype(float)
df["status"] = df["status"].astype("category") # saves memory for low-cardinality strings
# Convert to datetime
df["order_date"] = pd.to_datetime(df["order_date"])
df["year"] = df["order_date"].dt.year
df["month"] = df["order_date"].dt.month
df["month_name"] = df["order_date"].dt.strftime("%b")
df["day_of_week"] = df["order_date"].dt.day_name()
Sorting¶
# Sort by one column
df_sorted = df.sort_values("total", ascending=False)
# Sort by multiple columns
df_sorted = df.sort_values(["status", "total"], ascending=[True, False])
# Reset index after sorting
df_sorted = df_sorted.reset_index(drop=True)
Summary Statistics¶
# Per column
print(df["total"].mean()) # mean
print(df["total"].median()) # median
print(df["total"].std()) # std deviation
print(df["total"].sum()) # total
print(df["total"].min()) # minimum
print(df["total"].max()) # maximum
# Count non-null values
print(df["delivery_date"].count()) # excludes NaN
# Value counts (distribution of a categorical column)
print(df["status"].value_counts())
print(df["status"].value_counts(normalize=True)) # as proportions
Reading CSV — Common Options¶
df = pd.read_csv(
"sales_data.csv",
parse_dates=["order_date", "delivery_date"], # convert to datetime
dtype={"customer_id": str, "product_id": str}, # force string type
na_values=["N/A", "NULL", "-", ""], # treat as NaN
usecols=["order_id", "customer_id", "total"], # only load these columns
nrows=1000, # read only first 1000 rows
encoding="utf-8",
)
print(df.shape)
print(df.head(3))
Practice Exercises¶
# Load this sample CSV (create it first):
data = {
"order_id": ["O001","O002","O003","O004","O005","O006","O007","O008"],
"customer_id": ["C001","C003","C002","C001","C004","C003","C005","C002"],
"product": ["Headphones","Desk","Book","Yoga Mat","Headphones","Book","Coffee Maker","Desk"],
"category": ["Electronics","Furniture","Books","Sports","Electronics","Books","Kitchen","Furniture"],
"quantity": [2,1,3,1,1,5,2,1],
"unit_price": [149.00,389.00,49.99,35.99,149.00,49.99,89.99,389.00],
"status": ["completed","completed","pending","completed","cancelled","completed","completed","pending"],
"order_date": ["2024-01-05","2024-01-07","2024-01-09","2024-01-10","2024-01-11","2024-01-14","2024-01-14","2024-01-18"],
}
df = pd.DataFrame(data)
df["order_date"] = pd.to_datetime(df["order_date"])
df["total"] = df["quantity"] * df["unit_price"]
Warm-up
1. Print the shape, info, and describe of the DataFrame.
2. Select only the order_id, customer_id, and total columns.
3. Print the value counts of the status column.
Main
4. Add a column order_month that extracts the month name from order_date.
5. Add a column order_size: 'Small' if total < £100, 'Medium' if £100-£300, 'Large' if > £300.
6. Sort the DataFrame by total descending and display the top 3 rows.
7. Count missing values in each column.
Stretch
8. Convert the status column to a categorical type. Check the memory usage before and after using df.memory_usage(deep=True).
9. Select all orders from January 2024 (use df["order_date"].dt.month == 1). From those, select only order_id, product, and total.
Interview Questions¶
[Beginner] What is the difference between a Pandas Series and a DataFrame?
[Beginner] What does df.describe() show? What does it NOT show?
[Mid-level] What is the difference between df.loc and df.iloc?
[Mid-level] A DataFrame has 500,000 rows and 50 string columns. It uses 2GB of memory. How would you reduce memory usage?
[Senior] What does df.copy() do and when is it important? Explain chained assignment and why it's dangerous.