Skip to content

Preparing Data

Raw data usually needs preparation before it can be properly analysed. Preparation can involve removing unnecessary columns, renaming variables, converting dates, checking for missing values, and saving cleaned versions of the dataset for later use. These steps are not minor housekeeping details, they're central to producing reliable, trustworthy analysis.

In business contexts especially, small data-preparation mistakes can snowball into large analytical errors. If dates are stored as plain text rather than genuine dates, time-series operations may silently fail or behave unexpectedly. If missing values go unnoticed, return calculations further down the line may produce misleading results without any obvious warning. And if columns are poorly named, later code becomes considerably harder for you, or anyone else, to interpret and maintain.

C.1 Cleaning, Validating and Exporting Data

This subsection demonstrates a compact but representative preparation workflow. It covers renaming variables, dropping unnecessary columns, converting dates, removing missing observations, checking for duplicated dates, validating missing values, and finally exporting the cleaned dataset.

These operations come up constantly in business, economics, and management work, since datasets typically need to be saved at several distinct stages: raw data as originally downloaded, cleaned data ready for analysis, analytical data with derived variables added, and final outputs ready to share or publish.

Python Code Examples: Data Cleaning and Validation

# Standard data-preparation operations

# Rename columns
sp500.rename(columns={"Price_SP500": "sp500_price"}, inplace=True)

# Drop unnecessary columns, if they exist
columns_to_drop = ["Unnamed: 0"]
sp500.drop(columns=[c for c in columns_to_drop if c in sp500.columns], inplace=True)

# Convert dates
sp500["Date"] = pd.to_datetime(sp500["Date"])

# Remove missing observations
sp500.dropna(inplace=True)

# Check for duplicated dates
duplicates = sp500.duplicated(subset=["Date"]).sum()
print("Duplicated dates:", duplicates)

# Check missing values by column
print(sp500.isna().sum())

# Save outputs
sp500.to_csv("SP_500_Clean.csv", index=False)
sp500.to_excel("SP_500_Clean.xlsx", index=False)

# Reload a saved file, as a sanity check
sp500_reloaded = pd.read_csv("SP_500_Clean.csv")
sp500_reloaded["Date"] = pd.to_datetime(sp500_reloaded["Date"])

print(sp500_reloaded.head())

Expected output / what to inspect

Inspect the duplicated-dates count, the missing-values summary, the cleaned column names, and the exported file itself, ideally by opening it directly, to confirm it looks the way you expect.

Walking through why each step earns its place:

  • Renaming columns early (Price_SP500 → sp500_price) establishes a clear, consistent naming convention before any further variables are built on top of it. Trying to rename columns after a dozen other calculations reference the old name is a common, entirely avoidable source of frustration.
  • The conditional column-drop pattern — [c for c in columns_to_drop if c in sp500.columns] — is a small but genuinely useful defensive habit. Directly writing sp500.drop(columns=["Unnamed: 0"]) would raise an error if that column happened not to exist (for example, if the raw file changed slightly). Checking membership first means the code runs safely either way.
  • Re-converting dates, even if they were already converted at the importing stage, is cheap insurance. If this file is ever reloaded from a saved CSV (as demonstrated at the end of this example), dates are read back in as plain text by default, so re-applying pd.to_datetime() is a habit worth repeating at the start of any script that loads data from disk.
  • Checking duplicated dates matters especially for time-series data, since a single date accidentally appearing twice can silently distort later calculations like rolling averages, growth rates, or merges. A .duplicated() count of zero is a small, reassuring checkpoint before proceeding.
  • .isna().sum() gives a quick, per-column count of missing values, an essential validation step. Investigating why a value is missing (a public holiday? a data provider outage? a genuine gap?) is usually more informative than automatically dropping it, though dropping is a reasonable default when the missing count is small.
  • Saving to both CSV and Excel reflects a common real-world need: CSV is a lightweight, universal format ideal for further code-based processing, while Excel is often more convenient for sharing a cleaned dataset with colleagues who prefer to work in a spreadsheet.
  • Reloading the saved file as a final step is a good habit in itself, it confirms that what was actually written to disk matches what you intended, rather than assuming the export succeeded without checking.