Data Cleaning: Handling Missing Values and Duplicates in Pandas

Unit IV · Basics of Python Programming for Pharmaceutical Sciences (BP101T) · As per PCI B.Pharmacy Syllabus, NEP 2020

Real pharmaceutical datasets are rarely perfect. Pharmacokinetic (PK) concentration readings arrive with values recorded as “BLQ” (Below Limit of Quantification), adverse drug reaction (ADR) forms are submitted with blank severity fields, and export glitches occasionally duplicate an entire row. Before any statistical analysis, dose-response calculation, or graph can be trusted, the dataset has to be cleaned. This article walks through how Pandas represents missing data, how to detect and quantify it, and the different strategies — dropping, imputing, and filling — used to handle it responsibly in a clinical or laboratory context.

Why Data Cleaning Matters in Pharmaceutical Analysis

Data-quality problems in pharmacy datasets typically originate from a handful of predictable sources: manual transcription errors during case report form entry, instrument or sensor malfunction during an assay run, inconsistent formats between multiple study centres, or a concentration falling below the analytical method’s limit of quantification. None of these are exotic edge cases — they show up in almost every real dataset a pharmacy graduate will encounter, whether in a hospital pharmacovigilance cell, a clinical research organisation, or a quality-control laboratory.

Systematic data cleaning is therefore not an optional polish step. It directly affects data quality, supports compliance with regulatory documentation standards, and improves the reliability of any scientific conclusion drawn from the dataset. An uncleaned dataset can silently bias a mean concentration, inflate an adverse-event count, or hide a genuine safety signal.

Understanding Missing Data: NaN in Pandas

Pandas represents a missing value using a special floating-point marker called NaN (Not a Number). NaN behaves in a well-defined, predictable way inside arithmetic operations — it propagates through calculations rather than being silently skipped, so a single NaN in a column can turn an entire sum or mean into NaN unless it is handled first. Pandas ships a standard toolkit of functions for detecting how much data is missing and deciding what to do about it.

Counting and Measuring Missingness

The first step in any cleaning workflow is simply finding out how much is missing, and where. Combining isnull() with sum() gives a column-wise count in a single line:

pk_data = pd.read_csv("pk_study_data.csv")
ADR_data = pd.read_csv("ADR.csv")

print("Missing values in PK data:")
print(pk_data.isnull().sum())

print("\nMissing values in ADR data:")
print(ADR_data.isnull().sum())

In a typical PK dataset, every column may show zero missing values at first glance — but that can be misleading. If a concentration below the assay’s detection limit was recorded as the text “BLQ” rather than left blank, Pandas treats it as a valid string, not a NaN, so isnull() will not flag it. The ADR dataset, on the other hand, might genuinely show gaps: two missing Adverse_Event entries, five missing Severity values, and two missing Onset_Time_h readings — real omissions from incomplete case reporting.

To express this as a proportion rather than a raw count, use mean() instead of sum():

missing_percent = ADR_data.isnull().mean() * 100
print(missing_percent.round(1))

A column that is 50% missing (as Severity might be in a small sample) needs a very different handling strategy than one that is only 20% missing — this percentage view helps decide whether dropping rows is even feasible without losing half the dataset.

Converting Disguised Missing Values: The “BLQ” Problem

“BLQ” (Below the Limit of Quantification) is a common example of a disguised missing value in pharmacokinetics — it looks like ordinary data but is stored as text, which blocks any numerical operation on that column. The fix is pd.to_numeric() with errors="coerce", which converts anything it cannot parse as a number into NaN:

# Convert Concentration_ugmL to numeric; "BLQ" becomes NaN
pk_data["Concentration_ugmL"] = pd.to_numeric(
    pk_data["Concentration_ugmL"],
    errors="coerce"
)

print(pk_data.dtypes["Concentration_ugmL"])
print(pk_data["Concentration_ugmL"])

After this conversion the column’s data type becomes float64, and the previously text-based “BLQ” entry becomes a proper NaN that participates correctly (or is correctly excluded) in later calculations like the mean or standard deviation.

Dropping Missing Values with dropna()

The dropna() method removes any row (by default) that contains at least one missing value. It is fast and gives you a dataset of complete records, but it must be used carefully in clinical and pharmaceutical contexts because deleting observations can introduce bias.

Consider a pharmacokinetic study where a late time-point concentration is reported as BLQ. That is not necessarily a data-entry mistake — it may be a genuine, scientifically meaningful observation indicating the drug has been eliminated below the assay’s detection threshold. Simply deleting that row would distort the interpretation of the concentration-time profile and could bias calculations such as the area under the curve (AUC).

# Remove rows containing at least one missing value
ADR_clean = ADR_data.dropna()

print("Rows before dropna:", len(ADR_data))
print("Rows after dropna:", len(ADR_clean))

Output: rows before dropna() = 10, rows after = 5 — half the dataset was removed because at least one field was missing in five of the ten records. This illustrates why blanket dropping is risky: a modest amount of scattered missingness across several columns can eliminate the majority of your sample.

A gentler alternative restricts the check to one column using the subset parameter, so rows are only dropped when that specific field is missing:

# Remove only rows where Severity is missing
adr_no_missing_severity = ADR_data.dropna(subset=["Severity"])
print(adr_no_missing_severity)

Imputing Missing Values with fillna()

Deleting incomplete records is not always the right call. Imputation replaces missing observations with a reasonable substitute so that the rest of the useful information in that row is retained. The substitute might be a statistical value (mean, median) for numeric columns, or a descriptive placeholder category such as “Not Recorded” for categorical columns.

# Calculate the median onset time
median_onset = ADR_data["Onset_Time_h"].median()

# Create a copy so the original dataset remains unchanged
adr_filled = ADR_data.copy()

# Fill missing onset times with the median
adr_filled["Onset_Time_h"] = adr_filled["Onset_Time_h"].fillna(median_onset)

# Replace missing severity values with a descriptive label
adr_filled["Severity"] = adr_filled["Severity"].fillna("Not Recorded")

From a pharmacovigilance perspective, replacing a missing severity grading with “Not Recorded” is usually preferable to discarding the whole adverse-event record, since the subject ID, event description, and outcome remain valuable even without a severity grade. Always work on a .copy() of the original DataFrame so the raw, as-collected data is preserved for audit purposes — a requirement that mirrors real-world Good Clinical Practice (GCP) data-handling expectations.

Forward Fill and Backward Fill for Time-Series Data

Repeated PK measurements taken over time are a special case. Here, missing values can sometimes be filled using the most recent (or next) valid observation:

• Forward fill (ffill) carries the most recent valid observation forward in time.
• Backward fill (bfill) uses the next available valid observation to fill a preceding gap.

These should only be applied when it is scientifically defensible to assume a value did not change much between time points.

# Introduce an artificial missing value at 2 hours
pk_demo.loc[3, "Concentration_ugmL"] = float("nan")

# Apply forward fill
pk_demo["Concentration_ugmL"] = pk_demo["Concentration_ugmL"].ffill()

Here, the missing 2-hour concentration is replaced with the preceding valid reading of 7.85 µg/mL. Note that the genuine “BLQ” entry at the 12-hour mark is left untouched by this operation, because it is still a text string, not a NaN — a reminder that cleaning steps must be applied in the correct order.

Removing Duplicate Records

Duplicate records commonly creep in through export glitches, merging data from multiple sources, or repeated manual entry — and in a clinical dataset, duplicates can artificially inflate the apparent sample size and skew statistical results.

# Count completely duplicated rows
n_duplicates = pk_data.duplicated().sum()
print(f"Number of duplicate rows: {n_duplicates}")

# Remove duplicate rows, keeping the first occurrence
pk_data_deduped = pk_data.drop_duplicates(keep="first")

# Remove duplicates based only on selected identifying columns
pk_data_deduped = pk_data.drop_duplicates(
    subset=["SubjectID", "Time_h"],
    keep="first"
)

duplicated() returns True for any row that exactly repeats an earlier one; summing that Boolean Series gives the duplicate count directly.

Renaming and Standardising Columns

Datasets pulled from different laboratory information systems or study sites frequently use inconsistent column names — extra spaces, different capitalisation, or abbreviations that vary between centres. Standardising these names early makes every subsequent line of analysis code easier to write correctly.

pk_data = pk_data.rename(
    columns={"Concentration_ugmL": "Conc_ugmL"}
)
print(pk_data.columns.tolist())
# ['SubjectID', 'Time_h', 'Conc_ugmL', 'Sex', 'Weight_kg', 'Age_years']

Frequently Asked Questions

Why does isnull() sometimes show zero missing values even when a dataset clearly has gaps?

Because “missing” values are sometimes disguised as valid-looking text, like “BLQ”, “N/A” typed as a literal string, or “-999” used as a placeholder code. Pandas only recognises actual NaN/None objects as missing by default. You must first convert such placeholder text to NaN using pd.to_numeric(..., errors="coerce") or replace() before isnull() can detect it.

Is it ever acceptable to just delete every row with a missing value?

Only when the proportion of affected rows is small and the missingness is genuinely random. In clinical and pharmacovigilance data, missingness is often informative (e.g., a severity field left blank because the reporting nurse was unsure) — deleting such rows can introduce systematic bias into your safety or efficacy conclusions.

What is the difference between fillna() and interpolate()?

fillna() replaces missing values with a fixed value or statistic you supply (a constant, mean, median, or forward/backward-filled value). interpolate() instead estimates missing values mathematically from the surrounding known data points, which is often more appropriate for continuous time-series measurements like PK concentrations.

Summary

Data cleaning is the foundation on which every later step of pharmaceutical data analysis rests. Pandas represents missing values as NaN and offers isnull()/isna() for detection, dropna() for removal, and fillna()/interpolate()/ffill()/bfill() for imputation. Disguised missing values such as “BLQ” must first be converted using pd.to_numeric(errors="coerce") before they can be detected as NaN. Duplicate records should be identified with duplicated() and removed with drop_duplicates(), and column names should be standardised with rename(). Every choice — to drop, fill, or flag — should be made with an awareness of the scientific and regulatory consequences of altering clinical or laboratory data.

References

• Pharmacy Council of India (PCI) — B.Pharm Regulations, NEP 2020, BP101T Syllabus
• Python Software Foundation — Official Python Documentation, docs.python.org
• Pandas Development Team — Pandas Official Documentation, pandas.pydata.org
• Wickham, H. (2011). The Split-Apply-Combine Strategy for Data Analysis. Journal of Statistical Software.