Validating and Profiling a New Dataset with pandas: Row Counts, Distributions, and Outlier Checks Before You Analyze

Introduction

Picture this: you've just received a CSV export from a client's CRM system. It's supposed to contain 18 months of sales transactions — clean, complete, ready to analyze. You load it into pandas, run a few aggregations, and send over a summary report. A week later, the client calls. Turns out there were duplicate order IDs, a batch of transactions with negative revenue, and three months of data from a test environment that nobody told you about. Your beautifully formatted report is wrong, and you have to do it all over again.

This scenario plays out constantly in real data work. The instinct, especially when you're excited about a new dataset, is to jump straight into analysis. But that instinct costs you time, credibility, and sometimes real business decisions made on bad numbers. Profiling your data — systematically inspecting its shape, distributions, completeness, and quirks before you run a single analysis — is the professional habit that separates analysts who ship reliable work from analysts who are constantly firefighting.

By the end of this lesson, you'll have a repeatable profiling workflow that you can apply to any new dataset in minutes. You'll know how to assess the basic shape of your data, understand the distribution of every column, catch outliers before they corrupt your aggregations, and surface missing or duplicate data that could skew your results.

What you'll learn:

• How to assess dataset shape, data types, and basic completeness in one pass
• How to use .describe() and value counts to understand distributions
• How to detect and investigate outliers using IQR and z-score methods
• How to build a systematic profiling checklist you can reuse across projects
• How to catch duplicates, unexpected nulls, and category mismatches before they become report errors

---

Prerequisites

You should be comfortable loading a CSV or Excel file into a pandas DataFrame. If you haven't done that yet, start with Your First pandas DataFrame: Loading CSV and Excel Files and Exploring Data before continuing here. Basic familiarity with Python variables and data structures from Python Basics for Excel Users: Variables, Lists, Dictionaries, and Loops will also help. You'll also want a working Python environment — see Setting Up Python for Data Analysis: Python, VS Code, Jupyter, and Virtual Environments if you need to get that sorted first.

---

The Dataset We'll Work With

Throughout this lesson we'll use a fictional sales transactions dataset. You can create it yourself to follow along:

import pandas as pd
import numpy as np

np.random.seed(42)

n = 500

data = {
    "order_id": list(range(1001, 1481)) + list(range(1001, 1021)),  # 20 dupes
    "order_date": pd.date_range("2023-01-01", periods=480, freq="D").tolist()
                  + pd.date_range("2023-01-01", periods=20, freq="D").tolist(),
    "region": np.random.choice(["North", "South", "East", "West", "TEST"], size=500,
                                p=[0.3, 0.25, 0.2, 0.2, 0.05]),
    "product_category": np.random.choice(["Electronics", "Clothing", "Furniture", "Food", None],
                                          size=500, p=[0.3, 0.25, 0.2, 0.2, 0.05]),
    "revenue": np.concatenate([
        np.random.normal(250, 80, 480),   # normal transactions
        [-500, -300, 15000, 18000, 22000,  # outliers
         -100, -200, 14000, 19000, 16000,
         np.nan, np.nan, np.nan, np.nan, np.nan,  # missing
         300, 280, 260, 270, 290]
    ]),
    "units_sold": np.random.randint(1, 50, size=500),
}

df = pd.DataFrame(data)

This dataset has intentional problems baked in: duplicates, a test-environment region, missing values, negative revenue, and outliers on the high end. Your job — and the job of profiling — is to find all of them before you start analyzing.

---

Step 1: The First Five Seconds — Shape, Types, and a Quick Peek

The very first thing you do with any new DataFrame is understand what you're working with at the highest level. Three methods do this in sequence:

# How big is this thing?
print(df.shape)

# What do the first few rows look like?
print(df.head(10))

# What are the column names and data types?
print(df.dtypes)

Output from df.shape:

(500, 6)

Five hundred rows, six columns. That matches what you were told — or does it? If the client said "about 480 transactions," you'd already have a question to ask.

Output from df.dtypes:

order_id               int64
order_date    datetime64[ns]
region                object
product_category      object
revenue              float64
units_sold             int64

Data types matter because pandas will silently misread columns all the time. Revenue showing up as object instead of float64 would mean there are non-numeric characters hiding in that column — dollar signs, commas, or text like "N/A" that didn't parse correctly. Catching that now saves you from arithmetic that produces nonsense silently.

---

Step 2: Completeness — How Much of Your Data Is Actually There?

Missing data is one of the most common ways analysis goes wrong. A column that's 30% null will silently drag down your averages and wreck your group counts if you don't account for it.

# Null count and percentage for every column
null_counts = df.isnull().sum()
null_pct = (df.isnull().sum() / len(df) * 100).round(2)

null_summary = pd.DataFrame({
    "null_count": null_counts,
    "null_pct": null_pct
})

print(null_summary[null_summary["null_count"] > 0])

Output:

                  null_count  null_pct
product_category          25       5.0
revenue                    5       1.0

Now you know exactly what you're dealing with. Five percent of product_category is missing. One percent of revenue is missing. Those are decisions to make before analysis: do you impute? Exclude? Flag them as a separate segment? You can't make that decision if you don't know the problem exists.

---

Step 3: Duplicates — When One Row Is Actually Two

Duplicate rows are especially dangerous in transactional data. If you're summing revenue across 500 rows and 20 of them are duplicates of earlier rows, you've just inflated your total by 4%.

# Total duplicate rows (exact match on all columns)
print("Duplicate rows:", df.duplicated().sum())

# Duplicate on just the order_id column — logical duplicates
print("Duplicate order_ids:", df.duplicated(subset=["order_id"]).sum())

# Show what those duplicates look like
print(df[df.duplicated(subset=["order_id"], keep=False)].sort_values("order_id").head(10))

Output:

Duplicate rows: 20
Duplicate order_ids: 20

By passing keep=False to .duplicated(), you get every copy of a duplicated row flagged — not just the second occurrence. This lets you inspect the duplicates before deciding whether to drop them.

---

Step 4: Distributions — Understanding What "Normal" Looks Like in Your Data

Once you know the data is complete enough to work with, you want to understand the shape of each column. The starting point is .describe():

print(df.describe())

Output (approximate):

         order_id     revenue  units_sold
count  500.000000  495.000000  500.000000
mean  1240.500000  277.432000   25.340000
std     86.421000  1823.230000   14.120000
min   1001.000000  -500.000000    1.000000
25%   1121.000000  195.820000   13.000000
50%   1240.500000  249.760000   25.000000
75%   1360.000000  303.410000   38.000000
max   1480.000000  22000.000000  49.000000

Read this table carefully. A few things immediately stand out:

• Revenue min is -500. Negative revenue is possible (refunds), but you need to know whether this is expected or a data error.
• Revenue max is 22,000. The mean is 277 and the 75th percentile is only 303 — that means a very small number of values at the high end are dramatically larger than the rest.
• Revenue count is 495, not 500. Confirms the 5 missing values you found earlier.
• The standard deviation of revenue is 1,823. Compare that to a mean of 277 — a standard deviation 6x the mean is a red flag for outliers or a highly skewed distribution.

For categorical columns, .describe() on its own won't give you much. Use .value_counts() instead:

print(df["region"].value_counts())
print()
print(df["product_category"].value_counts(dropna=False))

Output:

North    152
South    125
East     102
West     100
TEST      21
Name: region, dtype: int64

Electronics    153
Clothing       127
Food           100
Furniture      100
NaN             25
None             0
Name: product_category, dtype: int64

There it is: TEST is a region value that should never appear in production data. If you'd skipped this step and gone straight to grouping by region, you'd have a "TEST" line in your pivot table that a client would absolutely notice. Adding dropna=False to value counts ensures nulls are counted too, not silently excluded.

---

Step 5: Outlier Detection — Finding Values That Don't Belong

Outliers can be real (a genuinely large order) or errors (a decimal point in the wrong place, a unit mismatch, a test record). Either way, you need to identify them before they distort your analysis. There are two standard methods.

Method 1: The IQR Method

The Interquartile Range (IQR) is the distance between the 25th and 75th percentile. Anything more than 1.5 × IQR above the 75th percentile, or more than 1.5 × IQR below the 25th percentile, is flagged as an outlier. This method doesn't assume your data is normally distributed, which makes it robust for messy real-world data.

Q1 = df["revenue"].quantile(0.25)
Q3 = df["revenue"].quantile(0.75)
IQR = Q3 - Q1

lower_bound = Q1 - 1.5 * IQR
upper_bound = Q3 + 1.5 * IQR

print(f"IQR: {IQR:.2f}")
print(f"Lower bound: {lower_bound:.2f}")
print(f"Upper bound: {upper_bound:.2f}")

outliers_iqr = df[(df["revenue"] < lower_bound) | (df["revenue"] > upper_bound)]
print(f"\nOutlier count (IQR method): {len(outliers_iqr)}")
print(outliers_iqr[["order_id", "order_date", "region", "revenue"]].sort_values("revenue"))

Output (approximate):

IQR: 107.59
Lower bound: 34.14
Upper bound: 465.03

Outlier count (IQR method): 15

Method 2: The Z-Score Method

A z-score measures how many standard deviations a value is from the mean. Values with a z-score above 3 or below -3 are typically flagged as outliers. This method works best when your data is roughly normally distributed.

from scipy import stats

# Calculate z-scores, ignoring NaN
z_scores = np.abs(stats.zscore(df["revenue"].dropna()))

# Map them back to the full index
revenue_no_null = df["revenue"].dropna()
outliers_z = revenue_no_null[z_scores > 3]

print(f"Outlier count (z-score method): {len(outliers_z)}")
print(outliers_z.sort_values())

Once you've identified outliers, don't delete them immediately. First, understand them:

# Look at the top 10 revenue values in context
print(df.nlargest(10, "revenue")[["order_id", "order_date", "region", "product_category", "revenue"]])

# Look at the bottom 10 (negative values)
print(df.nsmallest(10, "revenue")[["order_id", "order_date", "region", "product_category", "revenue"]])

Are the high-revenue orders from the TEST region? Are the negative ones all from a specific date range? Context tells you whether you're looking at a legitimate edge case or a data problem.

---

Step 6: Cross-Checks — Do the Numbers Add Up?

One final profiling step that's often skipped: sanity-checking your data against external expectations. This requires knowing something about the domain before you start.

# Expected: revenue should always be positive (for this business)
print("Negative revenue rows:", (df["revenue"] < 0).sum())

# Expected: units_sold should never be zero
print("Zero unit rows:", (df["units_sold"] == 0).sum())

# Expected: date range should be 2023 only
print("Date range:", df["order_date"].min(), "to", df["order_date"].max())

# Expected: orders per month should be roughly even
df["month"] = df["order_date"].dt.to_period("M")
print(df.groupby("month")["order_id"].count())

These checks are different from purely statistical outlier detection — they encode business rules. If you know this client doesn't process refunds through this system, negative revenue isn't a valid edge case; it's a bug. Writing these cross-checks forces you to clarify assumptions before they become buried in an analysis.

---

Putting It All Together: A Reusable Profiling Function

Rather than running these checks ad hoc every time, wrap them in a function you can call at the start of any project:

def profile_dataframe(df, id_column=None):
    """Quick profiling summary for a new DataFrame."""
    
    print("=" * 50)
    print("DATASET SHAPE")
    print(f"Rows: {df.shape[0]:,}  |  Columns: {df.shape[1]}")
    
    print("\n" + "=" * 50)
    print("DATA TYPES")
    print(df.dtypes)
    
    print("\n" + "=" * 50)
    print("MISSING VALUES")
    null_pct = (df.isnull().sum() / len(df) * 100).round(2)
    print(null_pct[null_pct > 0])
    
    print("\n" + "=" * 50)
    print("DUPLICATES")
    print(f"Exact duplicate rows: {df.duplicated().sum()}")
    if id_column:
        print(f"Duplicate '{id_column}' values: {df.duplicated(subset=[id_column]).sum()}")
    
    print("\n" + "=" * 50)
    print("NUMERIC SUMMARY")
    print(df.describe())
    
    print("\n" + "=" * 50)
    print("CATEGORICAL VALUE COUNTS (top 5 per column)")
    cat_cols = df.select_dtypes(include="object").columns
    for col in cat_cols:
        print(f"\n{col}:")
        print(df[col].value_counts(dropna=False).head(5))

profile_dataframe(df, id_column="order_id")

This function gives you a structured first-pass report every time. You can extend it with your IQR outlier check, business-rule assertions, or a row count comparison to an expected value. Once you're comfortable building reusable functions like this, explore Structuring a Reusable Data Analysis Project: Functions, Modules, Notebooks, and Scripts for how to package them into something you can share across your team.

---

Hands-On Exercise

Download any CSV dataset from Kaggle or use a dataset you have at work. Run the following sequence and write a short summary of what you find:

1. Load the file into a DataFrame and check its shape. Does it match your expectations?
2. Run .dtypes — are there any columns that should be numeric but are showing as object?
3. Run the null-count check. Which columns have missing data, and what percentage?
4. Run .duplicated().sum(). If you have an obvious ID column, check for logical duplicates on that field.
5. Run .describe() on the numeric columns. Do the min and max values look realistic for the domain?
6. For each categorical column, run .value_counts(dropna=False). Are there any unexpected values?
7. Apply the IQR method to your most important numeric column. How many rows are flagged? Pull those rows and look at them in context.

Write one sentence for each check: "I expected X, I found Y, so I need to Z."

---

Common Mistakes & Troubleshooting

"My .describe() isn't showing some columns." By default, .describe() only profiles numeric columns. To include object (string) columns, use df.describe(include="all"). To see only categorical columns, use df.describe(include="object").

".value_counts() is showing NaN as a real category — is that right?" Only if you used dropna=False. Without that flag, pandas silently drops nulls from value counts. Add dropna=False any time you want a complete picture, and you'll see nulls counted explicitly.

"I found outliers with IQR. Should I always remove them?" No — and this is a common over-correction. Outliers should be investigated, not automatically removed. A $22,000 order from a wholesale client might be completely legitimate. A $22,000 order in a dataset of consumer retail transactions probably isn't. The decision depends on context, not just the number.

"My z-score calculation is throwing an error." scipy.stats.zscore() doesn't handle NaN values by default. Either drop nulls first (as shown above) or use scipy.stats.zscore(df["column"].dropna()) and map the results back carefully. Alternatively, you can calculate z-scores manually: (df["revenue"] - df["revenue"].mean()) / df["revenue"].std() — pandas .mean() and .std() skip nulls by default.

"My dataset is huge and profiling is slow." If your dataset has millions of rows, profiling can take a while. You can profile on a sample: df.sample(50000).describe() will give you a statistically representative picture without waiting for the full dataset to compute. For advice on working with large files efficiently, see Handling Large Datasets in Python: Chunked Reading, Efficient dtypes, and When to Use Polars.

---

Summary & Next Steps

You now have a systematic, repeatable approach to profiling a new dataset before you analyze it. The sequence looks like this:

1. Shape check — row count, column count, and data types
2. Completeness check — null counts and percentages by column
3. Duplicate check — exact and logical duplicates on key fields
4. Distribution check — .describe() for numerics, .value_counts() for categoricals
5. Outlier detection — IQR and/or z-score, followed by contextual investigation
6. Business-rule cross-checks — encode domain knowledge as explicit assertions

Profiling doesn't replace analysis — it makes analysis trustworthy. A 15-minute profiling pass at the start of a project has saved analysts hours of rework and prevented real errors from reaching stakeholders.

From here, you have a few natural directions to go. If profiling reveals problems — missing values, bad data types, dirty strings — the next step is cleaning, which is covered in depth in Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types. Once your data is clean, you'll likely want to filter down to specific subsets using Selecting and Filtering Data in pandas: loc, iloc, and Boolean Masks, and then start aggregating with Grouping and Aggregating in pandas: groupby as the PivotTable Replacement. If your profiling reveals patterns worth visualizing, Visualizing Data with matplotlib and seaborn: Charts That Explain Your Analysis will help you build charts that communicate those findings clearly.