Matching two spreadsheets on a URL column is the same job as matching a stock list to a price list by product code. It goes wrong in the same two places: how you clean the code, and how you count what didn’t match.
www., tracking parameters, a trailing slash), keep the path’s case and its real query parameters, set aside rows with no URL, and let the join itself record what matched. Our own merge tool guessed the misses from empty cells and reported 5 unmatched rows out of 546. All 546 had matched.
Table of Contents
Why we had to merge spreadsheets by URL
In March 2026 we scraped an online course catalog in two passes. The first pass read the listing pages and captured each course’s title, page URL, image, rating, level and duration. The second pass opened every course page and captured the details: description, instructors, prerequisites and so on. Two spreadsheets, 546 rows each, and the only thing they had in common was the page URL.
A small Python script merged them. It cleaned each URL into a match key, joined the detail rows onto the listing rows, and wrote three files: the merged sheet, a sheet of unmatched rows, and a duplicate-key report if it found any.
It reported 5 unmatched rows. We re-read the same input and output files this month to see why.
How the tool decided a row was unmatched
It didn’t ask the join. After merging, it looked for rows where every column that had come from the second file was empty, and called those unmatched.
The flaw is in which columns it looked at. When both files have a column with the same name, the merge keeps both and renames the second one with a suffix, _from_3b in this script. The check only looked at suffixed columns. Our two files shared exactly one column name, duration; they also both had a level and a rating, but spelled level and Level, Rating and rating, and pandas compares names exactly. So “unmatched” really meant “the second file’s duration is blank.”
The 5 reported rows had matched. Each one had 5 to 7 other columns filled in from the second file. They were simply the 5 courses whose duration the listing page didn’t show.
The report was answering a different question: is this one cell empty?
It can fail the other way too. If the two files share no column names at all, there are no suffixed columns, and a check that “every one of zero columns is empty” is true for every row. We tried it on a three-row example: 3 of 3 rows reported unmatched, all of them matched.
The match key: what to normalize, and what to leave alone
The tool’s cleaning did four things: lowercase the whole URL, remove http:// or https://, cut everything from the ?, and remove one trailing slash. On this catalog none of it caused harm. All 546 URLs used the same host, none had a query string, and both files wrote every path with the same capitals. On other data, three of those steps merge different pages or miss real matches:
| URLs | The tool’s key | Problem |
|---|---|---|
…/item?id=101 and …/item?id=102 | both …/item | two products become one key |
…/course/Intro-to-SQL and …/course/intro-to-sql | both lowercased | paths are case-sensitive, so these can be different pages |
www.example.com/a and example.com/a | different keys | the same page never matches |
| two rows with no URL | both the empty key | blank rows match each other |
The standard for URLs agrees on the first two: the scheme and host are case-insensitive, the path is not. And query parameters are often the page’s identity. Only the tracking ones (utm_source, fbclid, gclid and similar) are safe to drop.
This key normalizes only what can’t change which page you get:
from urllib.parse import urlsplit, urlunsplit, parse_qsl, urlencode
import pandas as pd
TRACKING = ("utm_", "fbclid", "gclid", "ref", "mc_")
def match_key(url):
if pd.isna(url) or not str(url).strip():
return None # no URL: never a key that matches other blanks
p = urlsplit(str(url).strip())
host = p.netloc.lower().removeprefix("www.")
query = urlencode(sorted((k, v) for k, v in parse_qsl(p.query)
if not k.lower().startswith(TRACKING)))
path = p.path.rstrip("/") or "/"
return urlunsplit(("", host, path, query, ""))
Code language: Python (python)https://www.example.com/course/Intro-to-SQL/?utm_source=news and http://example.com/course/Intro-to-SQL get the same key. /item?id=101 and /item?id=102 stay apart, and so do the two capitalizations of the path.
Normalize only what can’t change which page you get.
Let the join report its own misses
pandas can tell you, for every row, whether the key was found on both sides. Ask for it with indicator=True, and add validate="one_to_one" so a duplicate key stops the merge instead of silently copying rows:
def merge_on_url(left, right, left_col, right_col):
l = left.assign(match_key=left[left_col].map(match_key))
r = right.assign(match_key=right[right_col].map(match_key)).drop(columns=[right_col])
# pandas joins missing keys to each other, so rows without a URL are set aside, not merged
no_url = pd.concat([l[l["match_key"].isna()].assign(side="left"),
r[r["match_key"].isna()].assign(side="right")])
merged = l.dropna(subset=["match_key"]).merge(
r.dropna(subset=["match_key"]), on="match_key", how="outer",
suffixes=("", "_right"), indicator=True, validate="one_to_one")
return merged, no_url
Code language: Python (python)The new _merge column says both, left_only or right_only for every row. That’s the unmatched report, recorded by the join rather than guessed from blank cells. An outer join also shows rows that exist only in the second file, which a left join hides.
Setting blanks aside matters more than it looks. We checked: with two URL-less rows in each file, pandas paired every blank with every other blank and produced four false matches. The original script had the same problem with its empty "" key.
On our March files the new merge gave 546 both, 0 left_only, 0 right_only, and no rows without a URL. The 5 “misses” were gone because they had never been misses.
The same mistake in a spreadsheet
You don’t need Python to make it, or to avoid it. With a lookup formula such as XLOOKUP or VLOOKUP, a key that isn’t found returns #N/A, while a key that is found but points at an empty cell returns an empty or zero value. Count the #N/A results to find real misses; never count blanks. Build the cleaned key in its own helper column first, so you can see exactly what each URL became.
This is the same lesson as a success message that isn’t confirmation: a report is a claim, and the join’s own record is the evidence. Deciding whether two records describe the same thing when there’s no shared URL at all is a harder problem.
The short version
To merge spreadsheets by URL, normalize the scheme, host case, www., tracking parameters and trailing slash, and leave the path’s case and real query parameters alone. Set aside rows with no URL, because pandas pairs missing keys with each other. Then let the join record what matched with indicator=True, and refuse duplicate keys with validate="one_to_one". Our old tool inferred misses from blank cells and reported 5 of 546 rows as unmatched; the join’s own indicator showed all 546 had matched.
Frequently Asked Questions
Should I lowercase URLs before matching them?
Lowercase the scheme and the host, not the path. Host names are case-insensitive, but paths can be case-sensitive, so /Intro-to-SQL and /intro-to-sql may be two different pages. If both of your files come from the same export and write paths the same way, you won’t need to lowercase them at all.
Is it safe to remove query strings from URLs before a merge?
Only the tracking parameters, such as utm_source, fbclid and gclid. Parameters like ?id=101 or ?product=blue-mug often identify the page, and removing them merges different items into one key.
How do I find rows that didn’t match in a pandas merge?
Pass indicator=True to merge. The result gets a _merge column with both, left_only or right_only for each row; filter on left_only and right_only. Use how="outer" if you want to see rows missing from either side.
Why did my merge produce more rows than my input?
A key that appears more than once on either side matches every copy, so one row can become several. Blank keys are a common cause, because pandas treats missing keys as equal to each other. Add validate="one_to_one" to stop the merge when that happens, and set rows without a key aside before merging.
Can I do this in Excel or Google Sheets?
Yes. Put the cleaned key in a helper column in both sheets, look it up with XLOOKUP or VLOOKUP, and treat #N/A as “not found”. An empty result means the row was found and the cell was empty, which is a different thing.
What if the two files share no URL at all?
Then you’re deciding whether two records describe the same thing from their content: titles, codes, prices. That’s record matching rather than a join, and it needs rules and review rather than a key.
