←Back to the bookChapter 151 · Notebook 3 · pandas Essentials

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.

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.

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 real 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.

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.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[us]
 2   product_id    3000 non-null   str           
 3   region        3000 non-null   str           
 4   channel       3000 non-null   str           
 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[us](1), float64(2), int64(3), str(3)
memory usage: 247.0 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.

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.

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.

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
   101807  North    595.0
   100500  South    595.0
   101560   West    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.

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.

←Back to the bookChapter 151 · Notebook 3 · pandas Essentials