Skip to content

Merging Datasets

Many empirical projects require data drawn from more than one source. A researcher might combine stock returns with accounting data, macroeconomic indicators, ESG scores, or management survey variables. An investment analyst might merge a stock's returns with factor returns, benchmark returns, or risk-free rates.

Merging is a powerful tool, but it's also genuinely risky if done carelessly. Before merging, it's essential to identify the correct key, the shared column used to line up rows correctly, such as a date, firm identifier, ticker, or country code. After merging, an analyst should always check the resulting number of rows, look for missing values, watch for duplicated columns, and confirm that the merged dataset has the structure they actually expected.

F.1 Merge Operations and Post-Merge Checks

This subsection demonstrates a date-based merge. The parameter on identifies the shared key column, while how controls the type of merge performed. The validate option is an important, but sometimes overlooked, tool, since it explicitly checks whether the merge relationship actually matches what the analyst expected.

After merging, the example code removes unnecessary duplicate index columns and creates a return-difference variable. A variable like this is especially useful when comparing an individual stock's performance against a benchmark index.

Python Code Examples: Merging and Validation

import pandas as pd

# Read two previously processed datasets
msft = pd.read_csv("MSFT_Returns.csv")
sp500 = pd.read_csv("SP_500_Returns.csv")

# Convert merge keys to datetime
msft["Date"] = pd.to_datetime(msft["Date"])
sp500["Date"] = pd.to_datetime(sp500["Date"])

# Merge on Date
dataset = pd.merge(
    left=msft,
    right=sp500,
    on="Date",
    how="inner",
    validate="one_to_one"
)

# Drop duplicated index columns, if they exist
drop_cols = [c for c in ["Unnamed: 0_x", "Unnamed: 0_y", "Unnamed: 0"] if c in dataset.columns]
dataset.drop(columns=drop_cols, inplace=True)

# Inspect and validate
print(dataset.head())
print(dataset.shape)
print(dataset.isna().sum())

# Create a simple stock-vs-benchmark comparison variable
dataset["return_difference"] = dataset["Return_MSFT"] - dataset["Return_SP500"]

print(dataset[["Date", "Return_MSFT", "Return_SP500", "return_difference"]].head())

Expected output / what to inspect

Inspect the merged shape, the missing-value counts, and the new return_difference column.

Breaking down the key decisions in this merge:

  • Converting Date to a proper datetime type on both DataFrames before merging is essential. If one file's dates were read in as text and the other's as genuine datetimes, pandas would treat them as fundamentally different values, silently produce an empty or partially matched merge, and give no obvious error to warn you.
  • on="Date" tells pandas exactly which column to use to line up rows between the two DataFrames, matching each MSFT observation to the SP500 observation recorded on the very same date.
  • how="inner" keeps only the dates that appear in both datasets, discarding any date present in only one. This is usually the safest default when comparing two return series, since a date missing from either source generally means you can't meaningfully compute a comparison for it anyway. (The alternatives, "left", "right", and "outer", keep progressively more unmatched rows, at the cost of introducing missing values where no match exists.)
  • validate="one_to_one" is a safety check worth using far more often than it typically is. It explicitly confirms that each date appears exactly once in both datasets before merging. If either dataset accidentally contained a duplicated date (perhaps from the duplicate-checking step back in Section C being skipped), this option raises a clear, immediate error instead of silently producing a merge with unexpectedly duplicated rows, exactly the kind of quiet, hard-to-detect bug that's far cheaper to catch here than to discover several analysis steps later.
  • Cleaning up "Unnamed: 0"-style columns is a common piece of post-merge housekeeping. These columns typically reappear because each source CSV was originally saved with index=True (writing pandas' own row index out as an extra column), and pandas automatically appends _x and _y suffixes to any columns with matching names from the left and right DataFrames, respectively, hence the need to check for and remove all three possible variants.
  • The final return_difference column is a direct, practical illustration of why merging matters in the first place: only once both return series sit side by side in a single DataFrame does a comparison like "how did this stock perform relative to the overall market?" become a simple one-line calculation, rather than something requiring two separate datasets to be manually cross-referenced by hand.