Using Python to Generate CSV Redirect Maps

Problem Statement

Large-scale migrations break when redirect maps are hand-built: legacy CMS exports inject tracking parameters and trailing-slash mismatches, byte-order marks corrupt RewriteMap ingestion, and unescaped regex produces chained or looping rules. At tens of thousands of rows, manual mapping wastes crawl budget, dilutes link equity, and throws 500 errors when the server parses the map. Unpinned Python environments add UnicodeDecodeError and out-of-memory failures on top. This page sits under CSV Mapping Workflows and turns a raw legacy export into a deterministic, server-ready redirect map.

Python redirect-map generation pipeline A four-stage pipeline that ingests a legacy export, normalises paths, applies regex transforms, validates targets, and emits a server-ready CSV map. CSV Redirect-Map Pipeline 1. Ingest 2. Normalise 3. Transform 4. Export read_csv utf-8-sig strip params regex + status validate + write HEAD-check failures feed back for re-mapping before deploy
Each row flows ingest to export; a failed target HEAD check loops back for re-mapping before the file is deployed.

When to Use This Approach

  • You have more than a few hundred URL changes and manual mapping is impractical or error-prone.
  • Legacy exports carry tracking parameters, mixed encodings, or inconsistent trailing slashes that need normalising before rules are generated.
  • You want one version-controlled CSV that feeds both Apache RewriteMap and Nginx map directives.
  • You need to programmatically verify that every redirect target actually resolves before deployment.
  • The migration spans repeated dry-runs and you want a repeatable, scriptable build rather than a one-off spreadsheet edit.

Step-by-Step Instructions

1. Pin an Isolated Python Environment

Unpinned dependencies are the most common cause of encoding and memory failures on bulk transforms. Create a virtual environment and pin exact versions before touching legacy data.

# Isolate and pin to avoid UnicodeDecodeError / OOM on large maps
python -m venv redirect_env && source redirect_env/bin/activate
pip install pandas==2.2.3 regex==2024.11.6 requests==2.32.3
export PYTHONIOENCODING=utf-8

Pin the environment because the output is an artefact you will need to reproduce. A mapping generated by whatever library versions happened to be installed is a mapping nobody can regenerate identically six months later, which matters the first time somebody needs to correct one row and re-emit the file. A lockfile and an explicit Python version cost nothing and make the generation step deterministic.

2. Ingest and Normalise the Legacy Export

Read the export with utf-8-sig so a leading BOM is consumed, then strip tracking parameters and normalise trailing slashes so source paths are canonical.

import re
import pandas as pd
from urllib.parse import urlparse, urlunparse

def clean_url(raw: str) -> str:
    """Strip tracking params and normalise the path."""
    url = urlparse(raw)
    path = re.sub(r'[?&](?:sid|session|utm_[a-z_]+)=[^&]*', '', url.path)
    return urlunparse(('', '', path.rstrip('/'), '', '', ''))

df = pd.read_csv('legacy_export.csv', encoding='utf-8-sig')  # utf-8-sig eats the BOM
df['clean_source'] = df['url'].apply(clean_url)

3. Apply Vectorised Regex Transforms

Build PCRE2-compatible capture groups and apply them with vectorised pandas replacement, then drop rows where no transform fired and assign the status code.

# Map /legacy/<slug>/article/<id> -> /new/category/<slug>/post/<id>
df['target'] = df['clean_source'].str.replace(
    r'^/legacy/([a-z0-9-]+)/article/(\d+)$',
    r'/new/category/\1/post/\2',
    regex=True
)
df = df[df['target'] != df['clean_source']].copy()  # keep only transformed rows
df['status_code'] = 301

Keep the transforms declarative and ordered. A sequence of narrow, individually-inspectable operations — each with a selector saying which rows it applies to — is far easier to debug than one clever expression that handles every case, and it lets you print the row count affected by each step. A transform that reports affecting far more or far fewer rows than expected is the earliest possible warning that a pattern is wrong.

4. Export a Server-Ready Map

Write UTF-8 with BOM so Excel opens it cleanly while Apache and Nginx strip the BOM on ingestion. Quote non-numeric fields to keep commas in paths intact.

import csv

df[['clean_source', 'target', 'status_code']].rename(
    columns={'clean_source': 'old_url', 'target': 'new_url'}
).to_csv(
    'redirect_map.csv',
    index=False,
    encoding='utf-8-sig',
    quoting=csv.QUOTE_NONNUMERIC   # protect commas inside path values
)

Load the resulting file as an O(1) hash lookup on the server. Tune Nginx hash sizing for large maps and reference the Apache RewriteMap directly:

# Nginx — size the hash tables for a large map file
map_hash_max_size 10000;
map_hash_bucket_size 128;
# Apache — load the redirect map as an O(1) hash lookup
RewriteMap redirect txt:/etc/apache2/redirects.map
RewriteCond ${redirect:$1|NOTFOUND} !NOTFOUND
RewriteRule ^(.*)$ ${redirect:$1} [R=301,L]
Normalisation steps before any transform runs Five normalisation operations applied to the legacy export before regex transforms, each with the duplication or mismatch it prevents. Normalise first, or the transforms operate on noise Operation Prevents Lowercase the path the same page appearing two or three times Settle trailing slashes rules that match one variant and not the other Strip tracking parameters thousands of near-duplicate rows Resolve protocol and host joins to analytics silently missing Drop in-page fragments rows the server will never see anyway Record the rules you applied alongside the output, since the next person cannot infer them from the result.
Every one of these is trivial and every one of them, skipped, inflates the eventual rule set several-fold.

Worked Example

An enterprise news site exports 48,000 legacy article URLs. A row reads https://old.example.com/legacy/world-news/article/8821?utm_source=newsletter. After step 2 the source normalises to /legacy/world-news/article/8821; after step 3 the target becomes /new/category/world-news/post/8821 with status 301. The exported row is:

"old_url","new_url",301
"/legacy/world-news/article/8821","/new/category/world-news/post/8821",301

A request to the legacy path now resolves in a single hop:

GET /legacy/world-news/article/8821 HTTP/1.1
Host: example.com

HTTP/1.1 301 Moved Permanently
Location: https://example.com/new/category/world-news/post/8821

Run chain detection across the generated set with Redirect Chain Elimination so no transformed row points at another redirected source.

Vectorised transforms are fast and unforgiving: a pattern applied across a whole column produces a result for every row, including the rows it was never meant to touch. Guard each transform with an explicit selector — apply it only to rows matching a condition you can state — rather than applying it universally and relying on the pattern to not match. The difference shows up as a destination column that looks plausible and contains a few hundred rows transformed by a rule intended for a different section.

Validate the output as data before treating it as a mapping. Check that no source appears twice, that no source also appears as a destination, that every destination is well-formed, and that the row count matches what you expect after deduplication. All four are a few lines each and they catch the errors that are invisible once the file has become server configuration.

Emit the server-ready file with the ordering the target expects rather than sorting for human convenience. Exact matches must precede patterns and catch-alls must come last, because the server stops at the first match — so a file sorted alphabetically for readability will behave differently from one sorted for correctness. Have the exporter enforce the ordering, and make the human-readable view a separate output.

Verification

HEAD-check every target before deployment so chained or missing destinations are caught while still in CSV form. Keep allow_redirects=False so a chained target does not report a false 200.

import requests

session = requests.Session()
session.headers.update({'User-Agent': 'RedirectValidator/1.0'})

results = []
for _, row in df.iterrows():
    try:
        r = session.head(row['new_url'], allow_redirects=False, timeout=5)
        results.append({'url': row['new_url'], 'status': r.status_code})
    except requests.RequestException as e:
        results.append({'url': row['new_url'], 'status': 'ERROR', 'error': str(e)})

pd.DataFrame(results).to_csv('validation_results.csv', index=False)

Deploy the map with an atomic swap and keep a timestamped backup so a 5xx spike can be reverted in seconds:

# Backup, validate config, reload atomically
cp /etc/nginx/redirects.map /etc/nginx/redirects.map.bak.$(date +%s)
rsync --backup redirect_map.csv /etc/nginx/redirects.map
nginx -t && systemctl reload nginx          # rollback: copy the .bak.* file back, then reload

Validation checklist:

Structural checks the generator should run before writing anything Four validations applied to the resolved mapping — duplicate sources, sources appearing as destinations, malformed destinations, and unresolved retirements — each with the defect it prevents reaching the server config. Fail the build rather than emit a broken map Assertion Prevents No source appears twice two rules claiming one URL, order-dependent No source is also a destination a chain shipped as two correct rules Every destination is well-formed redirects into 404s and malformed Location headers Every row has an explicit status provisional mappings silently made permanent All four are a few lines each and run in milliseconds — they belong in the generator, not in a review checklist.
The second and fourth are the ones with no cheap remedy once the config has shipped.

FAQ

How do I process 50,000+ URL rows without a Python memory overflow? Iterate with pandas.read_csv('source.csv', chunksize=10000) to process memory-safe blocks, or switch to polars for lazy, out-of-core evaluation on larger datasets.

What prevents catastrophic regex backtracking on malformed legacy URLs? Use the regex module rather than the built-in re and apply possessive quantifiers (*+, ++) or atomic groups (?>...) on ambiguous path segments. Test against adversarial inputs — very long strings and deeply nested paths — before production.

What is the exact emergency rollback if load spikes after deployment? Restore the timestamped backup: cp /etc/nginx/redirects.map.bak.TIMESTAMP /etc/nginx/redirects.map && nginx -t && systemctl reload nginx, which restores baseline routing in seconds. If nginx -t fails, the backup itself is corrupt — restore from Git instead.

Why Python rather than a spreadsheet? Because the operation has to be repeatable and reviewable. A spreadsheet transformation is a sequence of actions somebody performed once, leaving no record of what was done and no way to re-run it when the inventory changes — and it will change. A script is a diffable artefact that produces the same output from the same input, which is what lets the map be regenerated confidently after a correction. The secondary reason is that spreadsheet applications silently rewrite URLs on open, which is a category of corruption a script simply does not have.

How do I handle an export too large to hold in memory? Process in chunks and write incrementally, which most dataframe libraries support directly. Past a few million rows it is worth switching the intermediate format to something columnar rather than CSV, keeping a sampled CSV alongside for human inspection. What does not work is splitting the input by section and processing each independently, because the deduplication and the chain resolution both need to see the whole map at once.

Should the script emit server config directly, or a normalised CSV? Both, from the same run. The CSV is the reviewable artefact — a human can read it, diff it and reason about it — while the server config is the compiled output nobody edits. Emitting both from one execution guarantees they agree, which is the property that breaks first when the two are produced by separate steps run at different times by different people. Commit both, and have CI fail if regenerating produces a config that differs from the committed one.

How do I test the generator itself? With a small fixture inventory covering the awkward cases rather than with production data. A dozen rows containing a mixed-case path, a trailing-slash pair, a URL with parameters, a chain, a duplicate source and a retirement will exercise every branch that matters, run in milliseconds, and fail loudly when somebody changes a transform. Testing against the real export is slow, and it passes right up until the production data contains a case the fixtures would have caught.

Related

← Back to CSV Mapping Workflows