Cleaning & transforming data
Missing data
Finding, reporting, filling and dropping the gaps in a sheet.
Start with the full guides
The long-form treatment of missing data, with the reasoning the shorter pages assume.
Step-by-step how-tos
One task each, with code you can copy, run and adapt.
- How-toDrop Rows with Missing Required Fields in pandasdropna() with no arguments removes almost everything. Name the required columns, normalise blank-looking placeholders first, and keep the rejected rows with a reason and a source row number.
- How-toFill Gaps in Per-Item Time Series with a Staleness LimitCarry the last known value forward for each item in a sparse Excel time series without letting old numbers pose as current: build a complete item-by-period grid, forward-fill within each item only, cap how many periods a value may be carried, record each value's age and source period, flag stale cells, and write a price matrix to Excel that shows which figures are estimates.
- How-toFill Missing Values in Excel with Pandas fillnaFill missing values in an Excel file with pandas fillna: normalize blanks to NaN, then apply a scalar, a per-column dictionary, forward/back fill, or interpolation, and export.
- How-toFind and Report Missing Values in an Excel FileAudit an Excel file for gaps before you use it — per-column null counts, disguised blanks like n/a and dashes, missing-value patterns, and a formatted report of what is absent.
- How-toFlag Rows with Too Many Missing ValuesFind the incomplete records in an Excel sheet before they distort a report: count missing values per row with pandas, treat placeholders like "n/a", "-" and "TBC" as missing, weight required fields more than optional ones, choose a threshold, group the flagged rows by who entered them, and write a workbook where incomplete rows are highlighted and listed for follow-up.
- How-toForward-Fill Blank Cells in Grouped Excel Reports with pandasGrouped Excel reports show each region or category once and leave the rows below it blank, often in merged cells. Fill those labels down with pandas without leaking a label into the next group, filling data columns by mistake, or carrying labels into total rows.
- How-toHow Missing Values Change Sums, Averages and CountsBlank cells quietly change summary figures, and pandas and Excel do not always agree: what SUM, AVERAGE, COUNT and COUNTA do with blanks, text placeholders and zeros, how pandas sum, mean, count and size compare, why an all-blank group sums to 0, min_count, averages over blanks versus zeros, and a reconciliation that proves both tools give the same answers.
- How-toImpute Missing Values by Group with pandasFill gaps in Excel data with values that make sense for each row's group rather than one column-wide average: group means and medians with groupby transform, a fallback chain when a whole group is empty, minimum group sizes, flag columns that record what was filled, and a check that imputation did not distort the totals you report.
- How-toInterpolate Missing Numeric Values in Excel DataFill gaps in an ordered series without inventing nonsense — linear and time interpolation in pandas, limits and directions, group-wise fills, and flagging what was estimated.
- How-toReplace Placeholder Text with NaN in pandasExcel sheets mark missing data with "n/a", "-", "TBC", -999 or 31/12/9999. Turn those placeholders into real missing values with read_excel's na_values, per-column rules and sentinel masks — without losing genuine values such as the country code NA.
- How-toTell Blank Cells, Empty Strings and Zeros Apart in Excel DataA cell that looks empty in Excel can be truly empty, a formula returning "", a typed space, a zero hidden by its number format, or a placeholder like N/A — and pandas collapses most of them into NaN. See what read_excel and openpyxl actually return for each, classify every cell, and choose a policy before you count or fill blanks.
- How-toVisualise Missing Data Patterns in an Excel FileSee where and why data is missing, not just how much: a missingness map sheet that shades every blank cell, the most common combinations of missing columns, missing rates by month and by source, columns that tend to go missing together, and a one-sheet Excel report built with pandas and openpyxl that shows a data owner where to look.
- How-toHighlight Blank Cells in Excel with openpyxlMake missing values impossible to overlook: highlight empty required fields, whole rows with a gap, or blanks only inside the data. See why ISBLANK misses cells that look empty, how LEN(TRIM()) catches spaces and formulas returning "", and how to stop the rule from colouring every empty row below the table.
- How-toCoalesce Several Excel Columns into One with pandasTake the first non-empty value from several columns — Mobile, else Work phone, else Home phone — the way SQL's COALESCE or a chain of nested IFs in Excel does: bfill across columns, combine_first for two columns, treating blanks and placeholder text as missing, recording which column supplied each value, and flagging rows where the sources disagree.
- How-toIFERROR, IFNA, ISNA and ISNUMBER Equivalents in pandasIn pandas, a division by zero is inf, a failed lookup is NaN and bad text fails to_numeric — there are no error cells to catch. Translate IFERROR, IFNA, ISNA, ISBLANK, ISNUMBER and ISTEXT into the pandas operations that match each case, and keep IFERROR's habit of hiding real problems out of your pipeline.
- How-toProfile an Excel Dataset with pandasGet to know a workbook before cleaning or loading it: one row per column with its type, blanks, distinct values, most common values, ranges and text lengths; columns that mix numbers and text; value patterns such as "AA-9999"; columns that could be a unique key; and the whole profile written to a workbook people can read.
- How-toFill Missing Dates in a Time Series from Excel with pandasDaily exports only have rows for days when something happened, so charts skip the quiet days and averages come out too high. Add the missing dates with reindex and date_range, fill counts with zero and balances with the last known value, do it per store, and aggregate duplicate dates first.
- How-toUse Nullable Data Types When Reading Excel with pandasStop blank cells turning whole integer and TRUE/FALSE columns into floats: read Excel with dtype_backend="numpy_nullable" or "pyarrow", set Int64 and boolean per column, see why convert_dtypes after the read is too late for booleans, and write the NA values back as empty cells.
- How-toControl How NaN, None and Infinity Appear in Excel Outputpandas writes NaN, None, pd.NA and NaT as empty cells and infinity as the text "inf". Learn what each choice does to Excel formulas, when na_rep and inf_rep help or hurt, and how to handle division-by-zero results and placeholders column by column.
Follow it through a track
Prefer an ordered route? These tracks cover missing data along the way, in the order that builds on itself.
Related topics
Subjects that keep turning up on the same pages as this one, most overlap first.
Missing data is part of Cleaning & transforming data.