# Null and Duplicate Handling

Managing absent values and repeated records, the two most common structural defects in real datasets.

**Rests on:** Data Cleaning, Handling Missing Data

*Data Engineering and Analytics · Data Quality*


## Why learn this

This is the first gate you must pass before any of the interesting AI work can happen. Every dataset you will ever touch — whether it comes from a company's internal records, a government database, or a scraping script — arrives with missing cells and repeated rows. If you do not handle these two defects, every number you compute afterwards is distorted: averages are wrong, models learn from data they should never have seen, and evaluations look far better than real-world performance. Learning this concept unlocks the rest of the Data Quality track: Handling Missing Data (where you decide whether to drop, fill in, or model the absence itself), Outlier Handling (identifying extreme values), and Constraint Generation (automatically discovering rules to keep future data clean). It also builds the foundation for building any model — because training a model on a dataset with duplicates is like teaching a student using the same book twice while skipping the others.


## The intuition

Think of a dataset as a table with rows and columns, like an Excel sheet. Each row is one record — one customer, one transaction, one patient visit. Each column is one attribute — age, income, date-of-purchase. Now imagine you've been given a spreadsheet of sales transactions from a small shop. You open it and notice two things: some cells are empty (maybe the customer didn't provide their phone number), and some rows look identical (maybe the same sale was recorded twice by mistake). Both defects are not just cosmetic — they corrupt what you can learn from the data. An empty cell means you have less information about that customer; if you want to compute the average purchase amount, an empty amount forces you to decide whether to ignore that row, fill in a guess, or treat it as zero. A duplicate row means that customer is counted twice — inflating their apparent buying behaviour, and if you split your data into training and testing sets carelessly, the same customer can appear in both, letting the model 'cheat' by memorising rather than learning.

The key intuition is that both defects are structural: they are not about the data's content being wrong, but about its shape being wrong. Handling them is the first step in any data-cleaning process, and it happens before any analysis or model-building. The skill is not just about running a command to delete rows — it's about understanding what each empty cell and each repeated row means in your specific context, because the right action differs: sometimes you drop, sometimes you fill, sometimes you keep the duplicate on purpose.


## An analogy

Imagine you are a librarian given a box of index cards, each card describing a book in the library: title, author, publication year, shelf number, and a checkout count. But the previous librarian was sloppy. You find two sets of problems. First, some cards have a blank space for the shelf number — maybe the book is lost, maybe the librarian forgot to write it, maybe the card itself is from a time when the book wasn't in the library yet. You cannot put a book away without a shelf number, so you must decide: do you set the card aside, do you write 'UNKNOWN' on it and hope someone finds the book later, or do you guess a shelf number based on similar books? Each choice has consequences: setting it aside loses access to that book's checkout history; guessing might put it on the wrong shelf, making it unfindable. Second, you find that some cards are exact duplicates — the same book described twice, perhaps written on two different days. If you count the duplicate, the library's total book count is inflated, and if you use the checkout counts to decide which books are popular, the duplicate doubles the apparent popularity of that title. So you must decide: do you merge the two cards, keep one and discard the other, or — if you know the duplicate is real, because the book somehow has two physical copies — keep both? The analogy breaks down when you realise that in a library, you can physically inspect the cards and the books to resolve ambiguity. In a dataset, you often have no way to know why a cell is empty or why a row is duplicated — and the decision you make changes the outcome of every analysis downstream. That is why null and duplicate handling is not a mechanical task; it is a judgement call, informed by your understanding of where the data came from.


## Definition

Null and duplicate handling is the set of decisions and actions taken to manage absent values (nulls) and repeated records (duplicates) in a dataset — determining what each absence and each repetition means, and then dropping, filling, merging, or keeping rows and cells accordingly, so that downstream analysis and modelling rest on data free of these two structural defects.


## How it connects

This concept sits at the very start of the Data Quality track. Before you can do any exploratory data analysis, you must know which cells are empty and which rows are repeated — otherwise your first plot or first average is already wrong. The actions you choose here feed directly into Handling Missing Data (which you'll study next), because null handling is usually the first half of that topic: you decide whether to drop, impute, or model the missingness. Duplicate handling connects to Constraint Generation, because after you've fixed a dataset once, you can generate rules ('no duplicate customer IDs', 'no empty price cells') to prevent the same defects from recurring in future data. And it connects to Data Validation Constraints, because those constraints are the automated enforcement of the decisions you make here. The learner's library notes also mention that duplicates leak between train and test splits — a key reason to deduplicate before splitting, which you'll develop further when you study model evaluation.


## The mathematics

The mathematics behind null and duplicate handling is not a single heavy operation — it's a branch of mathematics called statistics, which deals with collecting, analysing, and interpreting data. Within statistics, this concept touches on descriptive statistics (summarising a dataset with a few numbers like the mean or count) and on probability (deciding how likely it is that an empty cell means something specific). To handle nulls, you need to count how many missing values there are per column, and compute what fraction of the data is missing. To handle duplicates, you need to compare rows to find identical ones, which involves counting and set theory (a branch that studies what it means for two things to be the same or different). When you fill a null with an average, you are adding a number to the dataset that wasn't there — and that changes the distribution, affecting every statistic you compute afterwards. So the mathematical content here is mostly about counting and about understanding how missing and repeated values distort the numbers you compute. There's no derivative or algebra required; the core is logical: 'how many times does this value appear?' and 'which rows are identical?'.

No single formula captures null and duplicate handling; it is a set of logical checks and counting operations. But two key calculations are:\n\n**Missingness fraction** for a column: (number of nulls in column) ÷ (total rows) × 100%.\n\n**Duplicate count** : (total rows) − (number of unique rows).\n\nWhere 'unique rows' means rows that are not identical to any other row.\n\nSymbol glossary: 'n' = number of rows, 'null_count' = number of empty cells in a column, 'unique_count' = number of distinct rows.


## A worked example

```
Imagine a small dataset of customer purchases in a shop, with three columns: Customer ID, Purchase Amount (in rupees), and City. The data has 6 rows, but some cells are empty and some rows are duplicated.

Step 1: Row 1: [101, 500, 'Mumbai']   # A complete record.
Step 2: Row 2: [102, null, 'Delhi']   # The purchase amount is missing (null).
Step 3: Row 3: [103, 300, 'Bangalore']   # A complete record.
Step 4: Row 4: [101, 500, 'Mumbai']   # An exact duplicate of row 1.
Step 5: Row 5: [104, 250, 'Pune']   # A complete record.
Step 6: Row 6: [105, null, 'Chennai']   # Another missing purchase amount.

Result: There are 2 missing values out of 6 purchase amounts, so 33% of that column is missing. There are 2 identical rows (row 1 and row 4), so the dataset has 1 duplicate pair, meaning the true number of unique records is 5, not 6.
```


## From scratch, in Python

```python
# Step 1: Create a simple dataset as a list of rows.
# A row is a list of values. In real life, this might come from a CSV file.
data = [
    [101, 500, 'Mumbai'],
    [102, None, 'Delhi'],
    [103, 300, 'Bangalore'],
    [101, 500, 'Mumbai'],  # duplicate of row 0
    [104, 250, 'Pune'],
    [105, None, 'Chennai']
]

# Step 2: Count missing values in the second column (purchase amount).
# We'll loop over each row and check if the value at index 1 is None.
missing_count = 0
for row in data:
    if row[1] is None:
        missing_count += 1
print("Missing purchase amounts:", missing_count)

# Step 3: Find duplicate rows by comparing each row with every other row.
# This is a simple O(n^2) approach - good for small data.
unique_rows = []
duplicate_count = 0
for row in data:
    if row not in unique_rows:
        unique_rows.append(row)
    else:
        duplicate_count += 1
print("Duplicate rows found:", duplicate_count)

# Step 4: Remove duplicates by building a new list of unique rows.
cleaned_data = []
for row in data:
    if row not in cleaned_data:
        cleaned_data.append(row)
print("Cleaned data (no duplicates):")
for row in cleaned_data:
    print(row)

```


## The same thing, with a framework

```python
import pandas as pd

# Load data into a DataFrame (a table with named columns)
df = pd.DataFrame({
    'customer_id': [101, 102, 103, 101, 104, 105],
    'purchase_amount': [500, None, 300, 500, 250, None],
    'city': ['Mumbai', 'Delhi', 'Bangalore', 'Mumbai', 'Pune', 'Chennai']
})

# Check for missing values per column
print(df.isnull().sum())

# Remove duplicate rows (keeping the first occurrence)
df_cleaned = df.drop_duplicates()
print(df_cleaned)
```


## What the code does

First, we define 'data' as a list of rows, each row a list. We use 'None' to represent a missing value (that's Python's way of saying 'nothing'). To count missing values, we loop over each row, look at the element at index 1 (the purchase amount), and increment a counter if it is None. Then we find duplicates by building a new list called 'unique_rows'. For each row in the original data, we check whether it's already in unique_rows using the 'in' operator — this checks equality of lists. If it's not there, we add it; if it is, we increment a duplicate counter. Finally, we build 'cleaned_data' in a similar way, but instead of counting, we simply append rows that are not already present. The result is a list with one copy of each unique row, which we print.


## Common misconceptions

**Null and zero are the same thing** — If a cell is empty, it just means the value is 0, right?

Zero is a real value: it means the purchase amount is zero. Null means we don't know the value at all. Mixing them up leads to treating 'unknown' as 'no money spent', which biases averages and models. In fact: Null is absence of information; zero is a specific number. They must be handled differently.

**Removing duplicates is always safe** — Duplicates are always mistakes, so just delete them all.

Some duplicates are genuine: two different transactions might have identical rows by chance, or the same customer might have placed two identical orders. Deleting them loses real data. In fact: Before dropping duplicates, understand why duplicates exist. Only remove them if you're confident they are errors.

**Nulls are always a problem to fix by removing rows** — The easiest thing is to just delete any row with a missing value.

Deleting rows with nulls can remove a significant portion of your data and introduce bias — for example, if only older customers leave the phone number blank, you'll lose all older customers. In fact: Evaluate how many nulls exist and whether missingness is random. Sometimes imputation (filling in) or modelling the missingness is better.

**A duplicate row in training data doesn't matter** — The model will just learn the same example twice — what's the harm?

Duplicates inflate the weight of that example, so the model overfits to it. Worse, if the same row appears in both training and test sets, the test accuracy is overestimated. In fact: Deduplicate before splitting data. Duplicates on purpose (e.g., in class imbalance) should be done deliberately, not accidentally.

**Handling nulls and duplicates is a one-time task** — Just clean the dataset once and it's done forever.

Data changes over time — new rows come in, new missing values appear, new duplicates emerge from upstream systems. In fact: Set up automated checks and constraints that run on every new data batch, not just once.


## In short

Nulls are missing values — absence of information, not zero. Duplicates are repeated rows that inflate counts and leak between train and test sets. Both must be detected early, understood in context, and handled deliberately — by dropping, filling, or keeping — and the decisions should be encoded as automated constraints so future data is checked automatically. Without this step, every downstream number is suspect.


## Where this is used

In real AI work, null and duplicate handling is the very first step in any data pipeline, and it is so common that every serious framework provides built-in tools for it. The Python library **pandas** is the standard for tabular data; its `isnull()`, `dropna()`, and `drop_duplicates()` functions are used daily by data scientists. Many organisations build automated pipelines that run these checks before any model training, using tools like **Great Expectations** or **Deequ** to define constraints like 'no nulls in the ID column' or 'this column must be unique'.\n\n**In large language models**: The training data for models like GPT is web text. That text is cleaned by removing duplicate paragraphs and near-duplicate documents, because repeated text inflates the model's memorisation of specific phrases. Missing values are less common in text (a sentence is either there or not), but a missing token in the training stream can corrupt the model's prediction, so tokenisers often use a special token to represent missing input.\n\n**In small language models run on a phone or laptop**: These models are trained on curated, heavily deduplicated data to keep them small. If a small model sees duplicate examples, it wastes its limited capacity memorising duplicates instead of learning general patterns.\n\n**In retrieval-augmented generation (RAG)**: RAG systems retrieve documents to answer a query. If the document store contains duplicates, the retriever may return the same passage multiple times, wasting context window space and potentially biasing the answer. Deduplicating the document store before indexing is a common preprocessing step.\n\n**In agentic systems that plan and call tools**: Agents often maintain a log of tool calls and results. If the same tool call is logged twice due to a retry, the agent might act on duplicate results, causing repeated actions. Handling duplicates in the agent's memory is a practical issue.\n\n**In applied machine learning (recommendation, vision, forecasting, fraud detection, speech)**:\n- **Recommendation**: User-item interaction logs frequently contain duplicate rating events (a user clicks 'like' twice). Counting each duplicate inflates the user's preference strength, so deduplication is standard.\n- **Vision**: Image datasets often have duplicate images (e.g., the same photo uploaded multiple times). If those duplicates appear in both training and test sets, the model's apparent accuracy is inflated — it's memorising, not generalising. Removing near-duplicates is a known data-cleaning step.\n- **Forecasting**: Time series data may have missing timestamps (nulls). A common approach is to fill them with interpolation, but doing so without understanding the pattern can distort seasonality.\n- **Fraud detection**: Transaction data may have duplicate rows from system retries. If a duplicate is treated as two transactions, the model may flag a normal transaction as fraudulent. Deduplication before training is essential.\n- **Speech**: Audio datasets may have duplicate samples or missing transcripts; handling these ensures the model doesn't overfit to repeated audio.\n\nIn practice, a developer writes code to first profile the data (count nulls and duplicates), then decide the action: drop rows with nulls (if few), fill with a default or mean, or model the missingness. For duplicates, they usually drop exact duplicates but be cautious with near-duplicates, which may carry unique information. The failure mode of doing this badly is silent degradation: the pipeline runs, but every downstream number is subtly wrong. A model trained on a dataset with duplicates leaking between train and test reports a validation accuracy of 98%, but in production drops to 60% — because the model never learned to generalise, it learned to memorise. The learner's library notes emphasise that both defects should be caught by pipeline constraints, not spotted by eye — meaning you write automated tests that fail the pipeline if nulls exceed a threshold or duplicates appear.


---

Read this on the web: https://basavapurushottam.com/learn-ai/null-and-duplicate-handling
