Merging and Joining Tables Without a Database

Every join hinges on a key: a column that identifies the same thing in both tables. It might be a customer id, an email, a SKU or an order number. The join matches a row from the first table to a row from the second whenever their keys are equal, then stitches their columns together. Before joining, make sure the key really is unique and consistent — trim whitespace and fix case first, because " A1 " and "a1" will not match even though they mean the same record. A join is only as good as the key it runs on.

A key is not always one column. An order line is identified by an order id together with a line number; a price list by product and region. When that is the case, match on the combination — either by choosing several key columns or by building one combined key first, joined with a separator that cannot occur inside the values. Watch the type as well as the text: an id written 007 in one export and 7 in another will never match, and neither will the same address in different capitalisation. Normalise both sides the same way before you start.

An inner join keeps only rows whose key appears in both tables — the intersection. Use it when you want records that are complete on both sides, such as customers who actually placed an order. A left join keeps every row from the first table and attaches matching data from the second where it exists, leaving blanks where it does not. Use it when the first table is your master list and you want to enrich it without dropping anyone. A full-outer join keeps everything from both sides, matching where it can and padding with blanks elsewhere — the choice when no row should be lost, and you want to see the gaps.

Two further shapes are worth knowing. A right join is a left join with the tables swapped, which is why many tools do not bother to offer it. An anti-join — the rows on one side that found no partner on the other — is rarely a menu item either, but it is the most useful diagnostic available: run a left join, keep only the rows where the second table's columns came back empty, and you have an exact list of the keys that are missing, misspelled or newly created. That list is often more interesting than the join itself.

Before joining, ask how many times a key can appear on each side. One to one — two exports of the same entity — gives one row per key. One to many, customers against orders, repeats the customer details on every order line: correct, but it means customer-level values must never be summed afterwards. Many to many multiplies: a key appearing three times on the left and four times on the right produces twelve rows, and a join meant to add a column has instead invented data that was never in either file.

When all you need from the many side is a total, aggregate first and join the summary — one row per customer carrying an order count and a sum — rather than joining the raw lines and trying to undo the multiplication later. Comparing the number of distinct keys with the number of rows in each file takes a moment and predicts the outcome before you run anything.

After a join, sanity-check the row count. An inner join should usually produce no more rows than the smaller table — if it produces many more, your key is not unique and each duplicate on one side multiplies against the other. Blank cells in a left or full-outer join are informative, not errors: they mark exactly the keys that had no match, which is often the very thing you were trying to find. In DataForge's Merge tab, load both files, pick the key column in each, choose the join type, and the stats report how many keys matched so you can confirm the result before downloading.

Watch the columns as well as the rows. When both tables carry a column of the same name — name, amount, updated_at — something has to give: some tools suffix the duplicates, pandas adding _x and _y by default, while others silently keep one and discard the other. Decide which side is authoritative before you join, or rename the columns beforehand, so that the result can be read without guessing which amount belongs to which file.

Not every combination is a join. When two files describe the same kind of thing — a January export and a February one, two regions, two systems with the same columns — what you want is to stack them: keep the columns as they are and put the rows of the second file underneath the rows of the first. Joining on a key here is simply the wrong operation, and it shows: you get either an almost empty result or a multiplication of rows.

Stacking has checks of its own. The column names must match exactly — order does not matter, spelling does — and a column present in only one file leaves blanks across the other file's rows. Duplicates appear wherever the two exports overlap in time, so run the deduplication after stacking rather than before. Add a column recording which file each row came from before you combine them: it costs nothing and turns an unexplained total into a question one filter can answer.