pandas rename() — relabel columns and index labels without retyping the header

$ raw = pd.DataFrame({
    "Order ID":        [1001, 1002, 1003, 1004, 1005],
    "CUST name ":      ["Yanbu Steel", "Jubail Pumps", "Riyadh Cement", "Yanbu Steel", "Jubail Pumps"],
    "  Revenue (SAR)": [12400, 8300, 21950, 6150, 9820],
    "Cost (SAR)":      [9100, 5200, 16800, 4400, 7300],
})
report = raw.rename(columns={
    "Order ID": "order_id", "CUST name ": "customer",
    "  Revenue (SAR)": "revenue", "Cost (SAR)": "cost"})
print(report)

   order_id       customer  revenue   cost
0      1001    Yanbu Steel    12400   9100
1      1002   Jubail Pumps     8300   5200
2      1003  Riyadh Cement    21950  16800
3      1004    Yanbu Steel     6150   4400
4      1005   Jubail Pumps     9820   7300

The workhorse: four ugly source headers in, four usable names out, and `raw` still holds the originals — rename returns a new object, so you keep the raw frame and hand the clean one downstream. Spell the mapping as a literal dict; six months from now that dict is the only record of what the ERP called things.

$ def slug(c):
    return re.sub(r"[^a-z0-9]+", "_", c.strip().lower()).strip("_")

print(raw.rename(columns=slug))

   order_id      cust_name  revenue_sar  cost_sar
0      1001    Yanbu Steel        12400      9100
1      1002   Jubail Pumps         8300      5200
2      1003  Riyadh Cement        21950     16800
3      1004    Yanbu Steel         6150      4400
4      1005   Jubail Pumps         9820      7300

The callable form: every label goes through the function, so a column that appears in next month's export is cleaned too — no dict to keep in sync. ' Revenue (SAR)' became revenue_sar because the regex eats the padding and the parentheses. This is the version that belongs inside a load_clean(path) helper every script imports.

$ sales = pd.DataFrame({"city": ["Yanbu", "Jubail", "Riyadh", "Yanbu", "Jubail"],
                      "revenue": [12400, 8300, 21950, 6150, 9820]})
per_city = sales.groupby("city")["revenue"].sum()
print(per_city.rename(index={"Yanbu": "Yanbu plant 1",
                             "Jubail": "Jubail plant 2",
                             "Riyadh": "Riyadh plant 3"}))

city
Jubail plant 2    18120
Riyadh plant 3    21950
Yanbu plant 1     18550
Name: revenue, dtype: int64

index= on a Series, right after an aggregation. The group keys became the index, so the report would ship with codes instead of site names; rename(index=...) is the fix and it keeps the object a Series — the index name `city` and the series `.name` revenue both survive the rename. pandas sorts the result alphabetically, which is why Jubail is first.

$ print(raw.rename(columns={"Order ID": "order_id", "Ordr ID": "oops"}).columns.tolist())
try:
    raw.rename(columns={"Order ID": "order_id", "Ordr ID": "oops"}, errors="raise")
except KeyError as e:
    print("KeyError:", e)

['order_id', 'CUST name ', '  Revenue (SAR)', 'Cost (SAR)']
KeyError: "['Ordr ID'] not found in axis"

The default is errors='ignore' — a typo'd key is not an error, it is a no-op. The first call renamed Order ID and quietly dropped the miss. Flip it to errors='raise' (0.25.0, July 2019) and the same code names the culprit: 'Ordr ID' not found in axis. My rule: 'ignore' while poking in a notebook, 'raise' in anything that runs on a schedule.

$ orders = pd.DataFrame({"order_id": [1001, 1002, 1003],
                       "customer": ["Yanbu Steel", "Jubail Pumps", "Riyadh Cement"],
                       "revenue": [12400, 8300, 21950]})
plan = pd.DataFrame({"customer": ["Yanbu Steel", "Jubail Pumps", "Riyadh Cement"],
                     "revenue": [13000, 8900, 21950]})
comparison = (orders.merge(plan, on="customer", suffixes=("_actual", "_target"))
                     .rename(columns={"revenue_actual": "revenue_ytd",
                                      "revenue_target": "revenue_plan"}))
print(comparison)

   order_id       customer  revenue_ytd  revenue_plan
0      1001    Yanbu Steel        12400         13000
1      1002   Jubail Pumps         8300          8900
2      1003  Riyadh Cement        21950         21950

The merge-cleanup pattern, and the rename I write most often. merge() appended the suffixes on its own; rename() takes them back off in the same expression, so no intermediate frame named tmp ever exists. Keep it inline in the chain — the day someone reorders the merge, the rename is right beside it.

$ wide = pd.DataFrame([[12400, 9800]],
                    columns=pd.MultiIndex.from_tuples([("revenue", "sum"), ("revenue", "mean")]))
print("before     :", wide.columns.tolist())
print("tuple key  :", wide.rename(columns={("revenue", "mean"): "avg"}).columns.tolist())
print("level=1    :", wide.rename(columns={"mean": "avg"}, level=1).columns.tolist())
print("level=0 fn :", wide.rename(columns=lambda x: x.upper(), level=0).columns.tolist())

before     : [('revenue', 'sum'), ('revenue', 'mean')]
tuple key  : [('revenue', 'sum'), ('revenue', 'mean')]
level=1    : [('revenue', 'sum'), ('revenue', 'avg')]
level=0 fn : [('REVENUE', 'sum'), ('REVENUE', 'mean')]

The trap that costs people an afternoon. On MultiIndex columns rename walks each level separately, so the tuple key ('revenue','mean') matches nothing and quietly returns the frame unchanged, while the bare 'mean' hits every level that carries that value. When you want one level, name it with level=: level=1 on 'mean' changed exactly one header and left ('revenue','sum') alone.