Messy Spreadsheet?
Data Guide.

Who This Is For

This guide is for people who regularly deal with data — in CSVs, spreadsheets, JSON files, database exports — and who need to clean it, transform it, analyze it, or hand it to something that will do one of those things.

You don't need to be a data scientist. You need to understand how data actually works well enough to stop fighting it.

By the end, you'll have the mental model that makes most data problems make sense, the vocabulary to describe what's wrong with a dataset, working knowledge of the formats you'll encounter constantly, and enough pandas literacy to read, write, and evaluate the code that AI will help you generate.

Why This Matters Now

Data used to be a specialist skill. You handed the messy spreadsheet to the analyst, waited a week, and got a chart back. Now there's an LLM that will write pandas code on demand, generate formulas, clean your CSV, and explain what a pivot table does. The bottleneck moved — again — from production to evaluation.

The problem is that data errors are insidious. A code error crashes. A data error produces a confident, completely wrong answer. A calculation that silently excludes 15% of your rows because of a format mismatch, a join that creates duplicate rows because the key wasn't unique, a date comparison that fails because one column is text and another is a real date — these produce numbers that look exactly right until someone catches the discrepancy six months later.

LLMs are good at generating data manipulation code for well-described, well-formed datasets. Real datasets are neither. The gap between the demo and your actual spreadsheet is where this guide lives.

The Mental Model: Data Has Shape

Most people approach data problems by looking at the values — is this number right? is this field filled in? why is this cell empty? But the problems that actually cause failures are almost always shape problems. The values are fine; the structure is wrong.

Data shape is the combination of: how many rows and columns you have, what those columns represent, whether each row represents one thing or multiple things, and how tables relate to each other. When a transformation fails, a join produces nonsense, or a chart looks wrong, the first question should be: is the shape what I think it is?

Shape Problems in the Wild

When a pandas operation fails with a confusing error, it's usually because the data isn't shaped the way the operation expects.

When a join creates more rows than either original table, it's because the key column wasn't actually unique — the shape was wrong.

When a sum gives you a wildly incorrect number, it's often because a text column full of numbers isn't actually a number column — the type is wrong, which is a shape problem.

When you're asked to produce a chart and it looks scrambled, it's usually because the data is in wide format and the charting library expected long.

The Formats: CSV, JSON, and Spreadsheets

Before you can work with data, you have to understand the container it came in. These three formats have different strengths, different failure modes, and different things they can't express.

CSV

A CSV (comma-separated values) file is the simplest possible tabular format: rows of text, with values separated by commas, and typically a header row at the top. It's universally readable, tiny, and can represent exactly one thing: a flat table.

name,age,city,signup_date
Alice,34,New York,2023-01-15
Bob,28,Chicago,2023-02-01
Carol,,Austin,2023-02-14

Commas in values break naive CSV parsing. If Alice's city is "New York, NY," that comma looks like a delimiter. Proper CSVs quote values that contain delimiters — "New York, NY" — but not all CSV generators do this consistently. When you open a CSV and the columns are misaligned in some rows, this is almost always why.

The header row may not be row 1. Many exports prepend metadata — a title, a report date, a filter description — before the actual headers. pandas and other tools assume row 1 is headers by default. If your data comes out scrambled, check whether there are preamble rows.

Encoding. CSVs don't carry encoding information. When you open a CSV and see garbled characters (names with accents become é), you have an encoding mismatch. UTF-8 is the right default. When that fails, try latin-1 or cp1252 for files that originated on Windows.

JSON

JSON (JavaScript Object Notation) is the lingua franca of APIs and web data. It can represent nested structures that CSV cannot — an order with multiple line items, a user with a list of tags, a response that contains a status and an array of results.

{
  "orders": [
    {
      "id": "ord-001",
      "customer": "Alice",
      "items": [
        {"sku": "A100", "qty": 2, "price": 29.99},
        {"sku": "B200", "qty": 1, "price": 49.99}
      ]
    }
  ]
}

This is where the shape issue becomes immediately visible. This JSON has a nested structure: orders contain items. To turn it into a flat table you'd actually analyze, you have to decide what the rows represent. A row per order? A row per item? A row per order-item combination? Each produces a different table with a different shape.

Spreadsheets

Excel and Google Sheets are not just fancy CSVs. They contain multiple sheets, formulas, formatting, merged cells, pivot tables, and charts. When you export a spreadsheet to CSV, most of this disappears — including formulas, which become their last-calculated values.

Merged cells are the enemy of programmatic data processing. A merged cell spanning rows 2-5 in column A looks like data in cell A2, and empty in A3-A5 — but the intent is that all four rows share the same value. You'll need to unmerge and fill down before any analysis.

Multiple header rows are common in reports: a main category in row 1, subcategories in row 2, actual column names in row 3. Readable as a human document; a nightmare as a data table.

Totals rows embedded in data. A "Total" row at the bottom of a dataset, indistinguishable from data rows except that its values are sums of the rows above. Load this into pandas and compute statistics — that total row is included in your calculations and corrupts everything.

Tidy Data: The Shape That Tools Expect

There's a concept called tidy data, formalized by statistician Hadley Wickham, that names the shape most analysis tools are built around. Understanding it makes the difference between fighting your tools and working with them.

The Three Properties

1. Each variable is a column.

2. Each observation is a row.

3. Each type of observational unit is a table.

Wide vs. Long

Region  | Jan | Feb | Mar
--------|-----|-----|----
North   | 120 | 135 | 141
South   |  98 | 102 | 115
East    | 211 | 198 | 230

Region | Month | Sales
-------|-------|------
North  | Jan   | 120
North  | Feb   | 135
North  | Mar   | 141
South  | Jan   |  98
...    | ...   | ...

The wide format is intuitive to read as a human. The long format is what pandas, R, most charting libraries, and most statistical operations expect. The wide format has a hidden structural problem: the months are encoded as column names, not as values. When you want to "filter to February" or "plot sales over time," the wide format requires logic that knows about specific column names. The long format treats month as data.

The operation that converts wide to long is called melting (or unpivoting). The reverse — long to wide — is called pivoting. These are among the most important transformations in data work.

Pandas Fundamentals: The Minimum You Need

pandas is the standard Python library for tabular data manipulation. You don't need to memorize its API, but you need to understand its structure well enough to read code, direct AI generation, and evaluate output.

Core Objects

DataFrame — a table. Rows and columns, both with labels. This is where you'll spend most of your time.

Series — a single column. When you select one column from a DataFrame, you get a Series. Most column operations work on Series objects.

Loading Data

import pandas as pd

df = pd.read_csv("orders.csv")
df = pd.read_excel("report.xlsx", sheet_name="Q3", skiprows=2)
df = pd.read_json("response.json")

The skiprows=2 parameter handles the preamble rows problem. sheet_name specifies which tab in a multi-sheet workbook. These are the parameters you'll need most often when real data doesn't load cleanly.

First Look at a Dataset

df.shape          # (rows, columns)
df.dtypes         # column types — critical for catching silent failures
df.head(10)       # first 10 rows
df.isnull().sum() # count of missing values per column
df.describe()     # summary statistics for numeric columns

Selecting and Filtering

# Select a column (returns Series)
df["price"]

# Select multiple columns (returns DataFrame)
df[["name", "price", "date"]]

# Filter rows where a condition is true
df[df["status"] == "pending"]

# Multiple conditions
df[(df["status"] == "pending") & (df["price"] > 100)]

The double brackets in df[["name", "price"]] are not a typo — you're passing a list of column names, which requires its own brackets.

Transformations

# Add a new column
df["total"] = df["price"] * df["quantity"]

# Apply a function to a column
df["name_upper"] = df["name"].str.upper()

# Map values
df["status_label"] = df["status"].map({1: "active", 0: "inactive"})

# Group and aggregate
df.groupby("region")["sales"].sum()
df.groupby(["region", "month"])["sales"].agg(["sum", "mean", "count"])

groupby is the pandas equivalent of SQL's GROUP BY and Excel's pivot table. The pattern is always: specify what to group by, select the column to aggregate, call an aggregation function.

Reshaping

# Wide to long (melt)
df_long = df.melt(
    id_vars=["region"],                    # columns to keep as-is
    value_vars=["Jan", "Feb", "Mar"],      # columns to melt into rows
    var_name="month",                      # name for the new column
    value_name="sales"                     # name for the values
)

# Long to wide (pivot)
df_wide = df_long.pivot(
    index="region",
    columns="month",
    values="sales"
)

Merging

# Inner join: only rows that match in both tables
merged = pd.merge(orders, customers, on="customer_id", how="inner")

# Left join: all rows from left, matching rows from right
merged = pd.merge(orders, customers, on="customer_id", how="left")

The how parameter is the join type — directly analogous to SQL joins. "left" is usually what you want when you want to preserve all records from your primary table and add information from a lookup table.

Data Types and Why They Matter More Than You Think

Types in data work are different from types in code, but the stakes are the same: operations that work on one type silently fail or produce wrong results on another.

The Types You'll Deal With

Numeric (int64, float64) — actual numbers. Arithmetic works. Comparisons work. Missing values in integer columns force pandas to use float (because integers can't represent NaN). This is why a column of whole numbers shows up as float64 — it has at least one missing value.

Object — pandas-speak for "string, or mixed, or something I couldn't figure out." This is the catch-all type and it's often a warning sign. A column that should be numeric but shows object dtype has non-numeric values mixed in.

datetime64 — actual date/time values. Date arithmetic works. Filtering by date range works. This is what you want for any date or timestamp column.

bool — true/false. Often stored as integers (1/0) and needs explicit conversion.

category — an efficient encoding for columns with a small number of repeated values. Not important to use, important to recognize.

The Silent Failure Pattern

This is the most important thing to understand about data types:

# Data loaded from CSV
df.dtypes
# signup_date    object   <- problem: should be datetime
# revenue        object   <- problem: should be numeric

# This appears to work
df["revenue"].sum()   # returns "10020304050" <- string concatenation!

# This throws no error but filters wrong
df[df["signup_date"] > "2023-06-01"]  # string comparison, not date

Both of these run without errors and produce confident output that's completely wrong. The sum concatenated strings. The date filter used alphabetical comparison.

The Fix

df["revenue"] = pd.to_numeric(df["revenue"], errors="coerce")
df["signup_date"] = pd.to_datetime(df["signup_date"])

errors="coerce" turns any values that can't be converted to NaN rather than crashing — which means you can convert and then check what failed.

Missing Data: Several Problems in One

Missing data is not one problem, it's several, and treating them the same way causes errors.

Three Kinds of Missing

True missing — the value genuinely doesn't exist. A person with no middle name. An order with no discount code. These should be NaN/null and that's correct.

Unknown — the value exists but wasn't recorded. A survey response that was skipped. A field that wasn't collected before a certain date.

Placeholder values masquerading as real data — -1, 0, 999, "N/A", "--", "none", "NULL". These appear in data exported from systems that couldn't represent true null. They're the most dangerous category because they look like values.

# Identify placeholder patterns before cleaning
df["age"].value_counts().tail(20)   # look for suspicious frequencies
df["revenue"].describe()            # is the minimum suspiciously negative?

If age has 847 occurrences of 0 and the next smallest value is 18, those zeros are placeholders. If revenue has a minimum of -999, that's a sentinel value.

Strategies

Dropping: Remove rows or columns with missing values. Simple, loses data.

df.dropna()                    # drop any row with any null
df.dropna(subset=["email"])    # drop only rows where email is null

Filling: Replace nulls with a value. The choice of fill value depends on context.

df["discount"].fillna(0)                        # no discount if null
df["status"].fillna("unknown")                  # explicit unknown
df["revenue"].fillna(df["revenue"].median())    # imputation

Imputation — filling with statistical estimates — is a whole field. Median is more robust than mean for skewed distributions. For time series, forward-fill is often appropriate. For categorical data, the mode makes sense.

Data Cleaning: The Real Work

Cleaning is the part that takes 80% of the time and rarely appears in tutorials. It's also where AI is most useful and most likely to hallucinate confident solutions to problems it doesn't understand.

Standardizing Strings

# Trim whitespace (extremely common)
df["name"] = df["name"].str.strip()

# Normalize case
df["status"] = df["status"].str.lower()

# Remove non-numeric characters from a phone field
df["phone"] = df["phone"].str.replace(r"[^0-9]", "", regex=True)

# Standardize category values
df["country"] = df["country"].str.strip().str.lower()
df["country"] = df["country"].replace({
    "us": "united states",
    "u.s.": "united states",
    "usa": "united states",
    "u.s.a.": "united states",
})

The country standardization is called normalization, and it's often the most time-consuming part of cleaning. You can't automate this without first looking at your data: df["country"].value_counts() to see all the variants humans actually used. Then you write the mapping.

Dealing With Duplicate Rows

df.duplicated().sum()          # count duplicates
df[df.duplicated(keep=False)]  # show all duplicate rows

df.drop_duplicates()                           # drop exact duplicates
df.drop_duplicates(subset=["order_id"])        # drop on specific column

"Duplicate" is context-dependent. Two rows with the same order_id are probably duplicates regardless of other fields. Two rows with the same customer email might be intentional if they represent different orders. subset= lets you define what counts as a duplicate for your context.

Date Parsing and Timezones

# Parse dates — specify the format if you know it (faster, unambiguous)
df["date"] = pd.to_datetime(df["date"], format="%Y-%m-%d")

# Handle multiple formats with dayfirst/yearfirst hints
df["date"] = pd.to_datetime(df["date"], dayfirst=True)  # European format

# Once parsed, extract components
df["year"] = df["date"].dt.year
df["month"] = df["date"].dt.month
df["day_of_week"] = df["date"].dt.day_name()
df["quarter"] = df["date"].dt.quarter

Timezones are where dates become genuinely hard. If your data has timestamps from multiple timezones and you don't account for this, your time-of-day analysis will be wrong. The safest practice is to store everything in UTC and convert only for display.

def clean_date(date_str):
    return pd.to_datetime(date_str)

df["date"] = df["date"].apply(clean_date)

df["date"] = pd.to_datetime(df["date"], errors="coerce")

# Then audit what failed
failed = df[df["date"].isnull() & df["raw_date"].notna()]
print(f"{len(failed)} dates failed to parse:")
print(failed["raw_date"].value_counts())

Joining and Merging Data

Most interesting data work involves combining multiple tables. This is where shape problems become most dangerous, because the wrong join can create rows that shouldn't exist, lose rows that should, or both.

Join Types

Inner join — returns only rows that match in both tables. If a customer_id exists in orders but not in customers, that order is dropped. Use when you need data from both tables and can afford to lose unmatched rows.

Left join — returns all rows from the left table, with matching data from the right where available. Unmatched rows get NaN for the right table's columns. Use when your primary table defines the rows you want and you're adding information from a lookup table.

Right join — the reverse of left. Rarely useful — just flip your tables and use a left join.

Outer join — all rows from both tables, NaN where there's no match. Use when you want to see everything and identify what's missing on either side.

The Key Uniqueness Problem

The most common join failure that produces wrong results without errors:

orders.merge(customers, on="customer_id", how="left")

This looks right. But if customers has duplicate customer_id rows — say, because a customer appears once per product they own — then every order for that customer joins to every matching customer row, multiplying your order rows. A 10,000-row order table might produce a 47,000-row merged table.

The result doesn't error. The row count is wrong. Any aggregation on the merged table is wrong. And if you don't know to check, you don't know anything is wrong.

assert customers["customer_id"].nunique() == len(customers), \
    "customer_id is not unique in customers table"

Diagnosing a Bad Join

# Before merge
print(f"Orders: {len(orders)} rows")
print(f"Customers: {len(customers)} rows")

merged = orders.merge(customers, on="customer_id", how="left")

# After merge
print(f"Merged: {len(merged)} rows")
# If len(merged) > len(orders), your join created duplicates

If the merged table has more rows than your primary table, you have a duplicate key problem. If it has significantly fewer, you have unmatched keys. Both are worth understanding before proceeding.

Aggregation and Grouping

Grouping is how you answer "how much per category" questions. The pattern is always: split into groups, apply an operation to each group, combine the results.

# Total sales by region
df.groupby("region")["sales"].sum()

# Multiple metrics at once
df.groupby("region")["sales"].agg({
    "total": "sum",
    "average": "mean",
    "transactions": "count"
})

# Multiple grouping dimensions
df.groupby(["region", "product_category"])["sales"].sum().reset_index()

reset_index() at the end promotes the group keys back from the index to regular columns, which is usually what you want for further processing or display.

Gotchas

Count vs. count of non-null: df.groupby("region")["sales"].count() counts non-null values. df.groupby("region").size() counts all rows including null. For record counts you almost always want size().

Sum of nulls: pandas treats NaN as 0 in sum operations by default — sum() skips nulls and returns the sum of non-null values. Whether this is correct depends on context.

Filtering After Grouping

# Keep only regions with more than 1000 total sales
region_sales = df.groupby("region")["sales"].sum()
region_sales[region_sales > 1000]

Working With AI on Data Tasks

AI is genuinely useful for data work. It can generate pandas code faster than you can look up syntax, explain what unfamiliar code does, and suggest cleaning strategies you might not have thought of. It also has specific, predictable failure modes.

What Works Well

Generating standard transformations when you describe your data clearly. Explaining what a piece of code does. Suggesting approaches for a cleaning problem. Generating the repetitive parts of a cleaning script — string normalization, column renaming, type conversion boilerplate.

What Fails in Non-Obvious Ways

The shape assumption problem. When you describe your data, the model forms a mental picture of its shape. If that picture is wrong — your "date" column has five different formats across 80,000 rows, your "region" column has 23 variants of three regions, your "order_id" is not actually unique — the generated code will work perfectly on a clean example and fail in production.

The hallucinated pandas method. pandas is large and its API has changed across versions. Models will generate code using methods that don't exist or have been removed. Common tells: DataFrame.append() (removed in pandas 2.0), certain groupby patterns that changed behavior, and any use of .ix (removed years ago).

The silent wrong answer. Missing type conversions, joins that create duplicate rows, sums of string columns, date comparisons on text — all run without errors. The model won't tell you to check for these.

Using AI Well for Data Work

Give context about what the data represents, not just its column names. "A CSV with customer orders where customer_id is not guaranteed to be unique because customers can have multiple accounts" is much more useful than "a CSV called orders.csv."

Ask explicitly for defensive code: "Include dtype checks at the start, validate that the join doesn't create duplicate rows, and audit what gets dropped by any dropna calls."

After getting code, ask: "What assumptions does this code make about the input data? What would cause it to fail or produce wrong results?" Models are better at identifying their assumptions when asked directly.

Use AI for the explanation pass: paste the code back and ask "walk me through what each section does and what it assumes about the data." This surfaces assumptions faster than reading the code yourself.

Spreadsheets vs. Code: When to Use Which

Spreadsheets are not inferior pandas — they're a different tool with a different design point. Understanding when each is right prevents a lot of friction.

Spreadsheets Are Better For

Human-readable reporting. Sharing with people who need to interact with data, not just consume it. Ad hoc exploration where visual feedback matters. One-off calculations where maintaining a script isn't justified. Data entry and collection.

Code Is Better For

Data that will be updated — code can be rerun; formulas can't reliably handle structural changes. Datasets over ~100K rows. Operations that need to be auditable, version-controlled, or reproduced. Any analysis you might need to explain or validate in detail.

The Formula vs. Code Distinction

Excel formulas are code in a limited sense — they transform inputs to outputs. But they're embedded in specific cells, reference other cells by position, and can't be unit-tested or version-controlled. When a business relies on complex Excel formulas built by someone who left two years ago, those formulas are a risk.

VLOOKUP / INDEX-MATCH / XLOOKUP

These are the spreadsheet equivalents of joins. The same key uniqueness problem applies: if the lookup column isn't unique in the reference table, the formula returns the first match silently. For anyone working extensively in spreadsheets, learning XLOOKUP (the modern version) is worth the time.

Common Patterns: Named Moves for Data Work

Like code patterns, naming these lets you recognize them and communicate about them.

ETL (Extract, Transform, Load)

The fundamental data pipeline pattern. Extract data from a source, transform it into the shape and quality you need, load it into a destination. Almost all data work follows this pattern, even informally.

Slowly Changing Dimension (SCD)

When a reference entity changes over time. A customer changes their address. If you join orders to customers, which version do you use — current or historical? This is a named problem with named solutions (Type 1: overwrite, Type 2: version with dates).

Explode

Converting a single row with a list value into multiple rows. An order with multiple items represented as a list in one cell becomes one row per item. pandas has a native explode() method for this.

Deduplicate on Key

Keeping only one row per unique identifier. The question is always which row to keep — the first? the last? the one with the most recent date? drop_duplicates(subset=["id"], keep="last") keeps the last occurrence.

Pivot / Unpivot (Melt)

Reshaping between wide and long format. Synonyms: unpivot = melt = stack. Pivot = unstack = cast.

Window Functions

An aggregation computed over a sliding window of rows, maintaining the original row structure. A rolling 7-day average. A running total. A rank within a group. These are rolling(), cumsum(), and rank() in pandas.

Fill Forward / Backfill

Propagating the last known value forward (or next known value backward) to fill gaps. Common in time series.

df["status"] = df["status"].ffill()   # forward fill
df["status"] = df["status"].bfill()   # backward fill

Where This Breaks Down

The honest accounting of what this guide doesn't cover and where data work genuinely gets hard.

Statistical validity. This guide covers mechanics, not methodology. Whether it's valid to impute missing values by median, whether your sample is representative, whether the relationship you're plotting is meaningful — these are statistics questions that require more depth.

Scale. pandas is excellent up to a few million rows. Beyond that, you need different tools: Dask, Polars, Spark, or a proper database. The concepts carry over; the syntax changes. If your operations take longer than a few minutes, you've likely outgrown pandas. See the Performance field guide.

Real-time and streaming data. This guide assumes batch data — a file, a snapshot, an API response. Continuous data is a different architecture problem.

The organizational problem. Data cleaning is often blocked not by technical complexity but by the fact that the people who created the messy data don't know it's messy, or the authoritative source of truth is disputed. This guide can teach you how to standardize "united states" vs. "us" vs. "usa" — it can't tell you who decides which is correct.

Data modeling. Once your data is clean, how you organize it — what tables you create, what relationships you define — is a discipline unto itself. The SQL field guide covers the mechanics; data modeling is the strategy layer above that.

Quick Reference

A cheat sheet for the operations covered in this guide.

Load Data

pd.read_csv("file.csv")
pd.read_csv("file.csv", skiprows=2, encoding="latin-1")
pd.read_excel("file.xlsx", sheet_name="Data")
pd.read_json("file.json")

First Look

df.shape          # (rows, cols)
df.dtypes         # column types — always check
df.head()         # first 5 rows
df.isnull().sum() # nulls per column
df.describe()     # summary stats
df["col"].value_counts()  # frequency table

Type Conversion

pd.to_numeric(df["col"], errors="coerce")
pd.to_datetime(df["col"])
df["col"].astype(str)
df["col"].astype("category")

Select and Filter

df["col"]                          # one column (Series)
df[["col1", "col2"]]               # multiple columns (DataFrame)
df[df["col"] == value]             # filter rows
df[(condition1) & (condition2)]    # AND — use & not 'and'
df[(condition1) | (condition2)]    # OR — use | not 'or'

Clean Strings

df["col"].str.strip()
df["col"].str.lower()
df["col"].str.replace("old", "new")
df["col"].replace({"variant1": "canonical", "variant2": "canonical"})

Missing Data

df.dropna(subset=["col"])          # drop rows where col is null
df["col"].fillna(0)                # fill with value
df["col"].ffill()                  # forward fill

Reshape

df.melt(id_vars=["key"], value_vars=["A","B","C"],
        var_name="category", value_name="value")    # wide to long
df.pivot(index="key", columns="category",
         values="value")                            # long to wide

Group and Aggregate

df.groupby("col")["val"].sum()
df.groupby("col")["val"].agg(["sum","mean","count"])
df.groupby(["col1","col2"])["val"].sum().reset_index()

Merge

pd.merge(left, right, on="key", how="left")
pd.merge(left, right, left_on="l_key", right_on="r_key", how="inner")

AI Data Review Checklist

What You Can Do Now

Find any dataset you've worked with recently — a CSV export, a spreadsheet, anything with real data.

import pandas as pd
df = pd.read_csv("your_file.csv")
print(df.dtypes)
print(df.isnull().sum())

Look at the output. Ask: which columns have object dtype when they should be numeric or datetime? Which columns have more nulls than you expected? Are there columns with zero nulls that seem unlikely — meaning nulls might be encoded as placeholder values like 0 or "N/A"?

You're looking for shape problems. They're almost always there in real data. Finding one, tracing it, and understanding what it would have silently broken downstream is worth more than any tutorial exercise on a pre-cleaned dataset.