Chapter 151 · Tools & Workflow · Notebook 3 of 5
pandas: The DataFrame
pandas is the analyst's workhorse: a spreadsheet you can program. This notebook covers the essentials you use every single day, loading data, looking at it, selecting and filtering rows and columns, adding columns, and handling the missing values that real data always has.
In [1]:
import numpy as np, pandas as pd
import matplotlib.pyplot as plt
plt.rcParams.update({"figure.dpi":110,"axes.grid":True,"grid.alpha":0.25,"font.size":11})
TL, TL2, AM, RD = "#0f766e", "#2dd4bf", "#d97706", "#dc2626"
BASE = "https://raw.githubusercontent.com/johnfisher-ai/Statistics-Data-Science-AI-Visual-Book/main/data/"
fn = "python-for-data-analysis--store-orders.xlsx"
def load(sheet="Orders"):
try: return pd.read_excel("../../data/" + fn, sheet_name=sheet)
except FileNotFoundError: return pd.read_excel(BASE + fn, sheet_name=sheet)
DEMO 1 · Series and DataFrame: the two core objects¶
A Series is one labeled column. A DataFrame is a table of Series that share an index. Almost everything in pandas is one of these two.
In [2]:
s = pd.Series([39, 12, 119], index=["Mug set", "Mug", "Grinder"], name="price")
print(s, "\n")
df = pd.DataFrame({"product": ["Mug", "Grinder", "Beans"],
"price": [12, 119, 22],
"in_stock": [True, False, True]})
print(df)
print("\ncolumns:", list(df.columns), "| shape:", df.shape)
Mug set 39 Mug 12 Grinder 119 Name: price, dtype: int64 product price in_stock 0 Mug 12 True 1 Grinder 119 False 2 Beans 22 True columns: ['product', 'price', 'in_stock'] | shape: (3, 3)
DEMO 2 · Load the orders data and look at it first¶
The first thing you do with any dataset: read it, then inspect. head shows the top rows, shape gives dimensions, info lists columns and types, describe summarizes the numbers. Never analyze data you have not looked at.
In [3]:
orders = load()
print("shape:", orders.shape)
print(orders.head(3).to_string(), "\n")
orders.info()
print()
print(orders.describe().round(2).to_string())
shape: (3000, 9)
order_id order_date product_id region channel quantity discount_pct revenue rating
0 100001 2024-02-05 P12 West web 1 0 45.0 5.0
1 100002 2024-02-25 P03 East store 1 0 22.0 5.0
2 100003 2024-11-04 P03 West web 5 0 110.0 3.0
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3000 entries, 0 to 2999
Data columns (total 9 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 order_id 3000 non-null int64
1 order_date 3000 non-null datetime64[ns]
2 product_id 3000 non-null object
3 region 3000 non-null object
4 channel 3000 non-null object
5 quantity 3000 non-null int64
6 discount_pct 3000 non-null int64
7 revenue 3000 non-null float64
8 rating 2832 non-null float64
dtypes: datetime64[ns](1), float64(2), int64(3), object(3)
memory usage: 211.1+ KB
order_id order_date quantity discount_pct revenue rating
count 3000.00 3000 3000.00 3000.00 3000.00 2832.00
mean 101500.50 2024-07-01 08:38:52.800000 3.00 1.89 78.70 4.34
min 100001.00 2024-01-01 00:00:00 1.00 0.00 4.80 2.00
25% 100750.75 2024-03-30 00:00:00 2.00 0.00 28.00 4.00
50% 101500.50 2024-07-05 00:00:00 3.00 0.00 56.00 4.00
75% 102250.25 2024-09-30 00:00:00 4.00 0.00 110.00 5.00
max 103000.00 2024-12-31 00:00:00 5.00 20.00 595.00 5.00
std 866.17 NaN 1.41 4.51 77.19 0.65
DEMO 3 · Selecting columns, rows, loc and iloc¶
Grab a column with brackets. Select by label with loc and by integer position with iloc. Getting this pair straight removes most early pandas confusion.
In [4]:
print("one column (a Series):"); print(orders["revenue"].head(3), "\n")
print("two columns (a DataFrame):"); print(orders[["region", "revenue"]].head(3), "\n")
print("loc by label (row index 0, three columns):")
print(orders.loc[0, ["order_id", "region", "revenue"]], "\n")
print("iloc by position (first 2 rows, first 3 columns):")
print(orders.iloc[:2, :3])
one column (a Series): 0 45.0 1 22.0 2 110.0 Name: revenue, dtype: float64 two columns (a DataFrame): region revenue 0 West 45.0 1 East 22.0 2 West 110.0 loc by label (row index 0, three columns): order_id 100001 region West revenue 45.0 Name: 0, dtype: object iloc by position (first 2 rows, first 3 columns): order_id order_date product_id 0 100001 2024-02-05 P12 1 100002 2024-02-25 P03
DEMO 4 · Filtering with boolean conditions¶
The same boolean-mask idea from NumPy, now with labels. Write a condition inside the brackets to keep matching rows, and combine conditions with & (and) and | (or), each wrapped in parentheses.
In [5]:
web = orders[orders.channel == "web"]
print("web orders:", len(web), "of", len(orders))
big_web = orders[(orders.channel == "web") & (orders.revenue > 100)]
print("web orders over 100 dollars:", len(big_web), "| their revenue:", round(big_web.revenue.sum(), 2))
print("\nregions present:", orders.region.unique().tolist())
print("orders per channel:\n" + orders.channel.value_counts().to_string())
web orders: 1497 of 3000 web orders over 100 dollars: 420 | their revenue: 73128.85 regions present: ['West', 'East', 'South', 'North', 'Central'] orders per channel: channel web 1497 mobile 1046 store 457
DEMO 5 · Creating and transforming columns¶
Assign a new column from a calculation on existing ones, vectorized across every row at once. Sorting and renaming round out the everyday edits.
In [6]:
orders["net_after_disc"] = (orders.revenue).round(2)
orders["big_order"] = orders.revenue > 100
orders["revenue_per_unit"] = (orders.revenue / orders.quantity).round(2)
print(orders[["order_id", "quantity", "revenue", "revenue_per_unit", "big_order"]].head(4).to_string(index=False))
print("\ntop 3 orders by revenue:")
print(orders.sort_values("revenue", ascending=False)[["order_id", "region", "revenue"]].head(3).to_string(index=False))
order_id quantity revenue revenue_per_unit big_order 100001 1 45.0 45.0 False 100002 1 22.0 22.0 False 100003 5 110.0 22.0 True 100004 5 110.0 22.0 True top 3 orders by revenue: order_id region revenue 100419 East 595.0 101067 North 595.0 101068 East 595.0
DEMO 6 · Missing data is normal; handle it deliberately¶
Real data has holes. Here the rating column has blanks (not everyone rates an order). Find them with isna, then decide: drop those rows, or fill the gap. The right choice depends on the question, but the choice must be conscious.
In [7]:
print("missing values per column:"); print(orders.isna().sum().to_string())
print("\nrating: {} missing of {}".format(int(orders.rating.isna().sum()), len(orders)))
print("mean rating, ignoring blanks:", round(orders.rating.mean(), 3)) # pandas skips NaN by default
filled = orders.rating.fillna(orders.rating.median())
print("mean rating, blanks filled with the median:", round(filled.mean(), 3))
print("rows left if we drop unrated orders:", len(orders.dropna(subset=["rating"])))
missing values per column: order_id 0 order_date 0 product_id 0 region 0 channel 0 quantity 0 discount_pct 0 revenue 0 rating 168 net_after_disc 0 big_order 0 revenue_per_unit 0 rating: 168 missing of 3000 mean rating, ignoring blanks: 4.34 mean rating, blanks filled with the median: 4.321 rows left if we drop unrated orders: 2832
Wrap-up¶
The daily toolkit: load, inspect (head, info, describe), select (brackets, loc, iloc), filter with boolean masks, build new columns from old, and deal with missing values on purpose. These five verbs cover most of what analysis actually is. Next we go from describing a table to reshaping and summarizing it.