Mathew K AnalyticsLesson 12 · Pandas Full Course
Rename Columns in Pandas the Clean Way (One Line for Every Column) | Pandas Course #12
In this lesson you'll be able to: Rename chosen columns safely with rename(columns=...) Clean every column name at once with string methods Add prefixes and…
- CoursePandas Full Course
- Lesson12 of 65
- FormatJupyter notebook · 49 code cells
- Data1 dataset
What you'll learn
Datasets used in this lesson
Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.
- superstore.csv preview1.7 MB
📓 Full notebook
Download .ipynbPandas 12: Renaming Columns and Indexes#
In this lesson you'll be able to:
- Rename chosen columns safely with rename(columns=...)
- Clean every column name at once with string methods
- Add prefixes and suffixes, and replace all names with set_axis
- Rename index labels and name the index itself
- Turn a grouped result into a clean, report-ready table Dataset: Superstore sales, 8399 orders (data/pandas/superstore.csv)
Warm-up: an index from lesson 11#
Lesson 11 set and reset indexes. Same skill, sales data.
import pandas as pd
df = pd.read_csv("data/pandas/superstore.csv")
print(df.shape)
print(df.columns.tolist())
by_row = df.set_index("Row ID")
by_row.loc[1, ["Customer Name", "Sales", "Profit"]]
Rename chosen columns with rename#
Problem: names with spaces and hyphens are awkward in code.
df.query("`Order Quantity` > 45").shape
new_names = {"Product Sub-Category": "subcategory",
"Customer Segment": "segment"}
short = df.rename(columns=new_names)
short[["segment", "subcategory"]].head(3)
Quick check: did rename change the original df?
print("segment" in df.columns)
print("Customer Segment" in df.columns)
Common mistake: a typo in the old name
oops = df.rename(columns={"sales": "revenue"})
oops["revenue"].head(3)
fixed = df.rename(columns={"Sales": "revenue"}, errors="raise")
fixed["revenue"].head(3)
df = df.rename(columns={"Product Base Margin": "base_margin"})
print(df.columns[-2:].tolist())
Your turn#
Rename Order Priority to priority and Ship Mode to ship, then count the ship values.
quick = df.rename(columns={"Order Priority": "priority",
"Ship Mode": "ship"})
quick["ship"].value_counts()
Clean every name at once with string methods#
Problem: 21 names to fix. Typing a dictionary for each is slow.
cols = df.columns.str.lower()
cols = cols.str.replace(" ", "_").str.replace("-", "_")
print(cols[:8].tolist())
df.columns = cols
df.product_sub_category.head(3)
print(df.columns.is_unique, len(df.columns))
raw = pd.DataFrame(columns=[" Sales ", "Unit Price", "QTY"])
print(raw.rename(columns=str.lower).columns.tolist())
tidy = raw.columns.str.strip().str.lower().str.replace(" ", "_")
print(tidy.tolist())
prod = df.filter(like="product_").columns
print(prod.str.removeprefix("product_").tolist())
Quick check: how many original names contained a space?
head = pd.read_csv("data/pandas/superstore.csv", nrows=0)
print(head.columns.str.contains(" ").sum())
nice = df.columns.str.replace("_", " ").str.title()
print(nice[:4].tolist())
Common mistake: a new list of the wrong length
money = df[["sales", "profit", "discount"]].copy()
money.columns = ["revenue", "profit"]
money.columns = ["revenue", "profit", "discount"]
money.head(3)
Your turn#
Make every column name upper case, then show the first 4 names.
upper = df.rename(columns=str.upper)
print(upper.columns[:4].tolist())
Add prefixes and suffixes, or replace every name#
Problem: mark units or sources on many columns at once.
cad = df[["sales", "profit", "shipping_cost"]].add_suffix("_cad")
print(cad.columns.tolist())
tagged = df[["sales", "profit"]].add_prefix("store_")
tagged.head(3)
labels = ["Order", "Sales ($)", "Profit ($)"]
report = df[["order_id", "sales", "profit"]].set_axis(labels,
axis=1)
report.head(3)
Your turn#
Keep order_quantity and unit_price, renamed to qty and price.
pairs = {"order_quantity": "qty", "unit_price": "price"}
small = df[list(pairs)].rename(columns=pairs)
small.head(3)
Rename index labels and name the index#
Problem: a typo in the data shows up as a row label.
regions = df.groupby("region")[["sales", "profit"]].sum()
regions.round(0)
regions = regions.rename(index={"Prarie": "Prairie"})
print(regions.index.tolist())
regions.loc["Prairie"].round(0)
print(regions.index.name)
regions = regions.rename_axis("Region")
print(regions.index.name)
print(regions.rename(index=str.upper).index[:3].tolist())
regions.rename_axis(columns="Measure").head(2).round(0)
Quick check: did renaming the label fix Prarie in df?
print((df["region"] == "Prarie").sum())
df["region"] = df["region"].replace({"Prarie": "Prairie"})
print((df["region"] == "Prairie").sum())
Common mistake: renaming a row label with columns=
wrong = regions.rename(columns={"Yukon": "YT"}, errors="raise")
regions.rename(index={"Yukon": "YT"}).tail(2).round(0)
Your turn#
Shorten Northwest Territories to NWT and Nunavut to NU in regions.
short_reg = {"Northwest Territories": "NWT", "Nunavut": "NU"}
print(regions.rename(index=short_reg).index.tolist())
Flatten and rename grouped column names#
Problem: agg with a list makes two-level column names.
cat = df.groupby("product_category")["profit"].agg(["sum", "mean"])
cat = cat.rename(columns={"sum": "total", "mean": "average"})
cat.round(1)
two = df.groupby("product_category").agg({"sales": ["sum", "max"]})
print(two.columns.tolist())
two.columns = ["_".join(pair) for pair in two.columns]
two.round(0)
Quick check: which category has the biggest single sale?
print(two["sales_max"].idxmax())
print(two["sales_max"].max().round(2))
named = df.groupby("product_category").agg(
total=("profit", "sum"),
orders=("order_id", "count"))
named.round(1)
modes = df["ship_mode"].value_counts()
modes.rename("orders").rename_axis("mode")
Your turn#
Profit by customer_segment as sum and mean, renamed to total_profit and avg_profit, index named Segment.
seg = df.groupby("customer_segment")["profit"].agg(["sum", "mean"])
seg = seg.rename(columns={"sum": "total_profit",
"mean": "avg_profit"})
seg.rename_axis("Segment").round(1)
Mini-project: a report-ready profit table by region#
Plan: summarise, add margin, give friendly names, save, re-read.
rep = df.groupby("region")[["sales", "profit"]].sum()
rep["margin"] = rep["profit"] / rep["sales"] * 100
rep = rep.sort_values("margin", ascending=False)
rep.round(1)
friendly = {"sales": "Sales ($)", "profit": "Profit ($)",
"margin": "Margin (%)"}
final = rep.rename(columns=friendly).rename_axis("Region")
final.round(1)
import os
os.makedirs("output/pandas", exist_ok=True)
path = "output/pandas/l12_region_report.csv"
final.round(1).to_csv(path)
back = pd.read_csv(path, index_col="Region")
print(back.columns.tolist())
import matplotlib.pyplot as plt
ax = back["Margin (%)"].sort_values().plot(kind="barh",
figsize=(7, 4))
ax.set_title("Profit margin by region")
ax.set_xlabel("Margin (% of sales)")
ax.set_ylabel("Region")
plt.show()
total = back["Profit ($)"].sum() / back["Sales ($)"].sum() * 100
print(round(total, 1))
back.loc[["Ontario", "West"]]
west = back.loc["West"]
gap = (total - west["Margin (%)"]) / 100
print(round(west["Sales ($)"] * gap))
best = back["Margin (%)"].idxmax()
top_sales = back["Sales ($)"].idxmax()
print(best, back.loc[best, "Margin (%)"])
print(top_sales, back.loc[top_sales, "Margin (%)"])
Recap#
- rename(columns={old: new}) returns a new table; assign it back
- errors="raise" turns silent typos into errors
- df.columns.str methods clean every name at once
- add_prefix, add_suffix and set_axis tag or replace all names
- rename(index=...) and rename_axis fix row labels and the index name
Challenges#
- Rename ship_mode and order_priority to shorter names, with errors="raise".
- Build profit by customer segment and name the index "Segment".
- Add the prefix "ship_" to the shipping columns only.
What's next#
Lesson 13: Sorting and Ranking Data Sort by several columns, handle ties, and rank within groups.
Found this useful?
All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.
Related lessons
17 minLesson 13
How to Rename Columns and Indexes in pandas DataFrames for Clear Data Analysis
23 minLesson 11
Clean Missing & Dirty Operations Data in pandas
Lesson 11
Clean Stock Price Data in Python: Apple vs Tesla with Pandas | Data Analytics #11
26 minLesson 4