Messy client Excel files: count the missing cells before pandas adds them up

A thousand orders, forty of which have the word “liên hệ” (“contact us”) in the amount column. You convert that column to numbers, forty cells become NaN, and then you call .sum(). The figure that comes back looks perfectly tidy, and it is short by exactly forty orders, without a single warning.

The scenario is hypothetical, but the mechanism is not. The pandas documentation states it plainly: when summing, NA values or empty data are treated as zero. For a Forward Deployed Engineer, this is the most dangerous trap of the first week on a client site, because the wrong number ends up in the demo.

This guide walks through a workflow you can run on a laptop tonight: read the file raw, declare the missing values, convert types under control, count the damage, then collect the errors into a report for the client. The code snippets are cut down for teaching; anything simplified is flagged.

What you will build, and what you need

Imagine a distributor sends two files: don_hang.csv (orders) and bao_cao.xlsx (report). The CSV has a ma_kh (customer ID) column with values like “00123”, a so_tien (amount) column written the Vietnamese way, such as “1.250.000,5” (wrapped in double quotes), and a few cells containing “-” or “chưa có” (“not yet”). The Excel file has three decorative header rows at the top and one sheet per branch.

You need Python 3, pandas, whichever Excel reader ships with your environment, and Pandera for the final step. Create the two sample files yourself, about 20 rows each, and deliberately plant every kind of junk in them. Dirtying data by hand is the fastest way to understand what each parameter does.

Step 1: read the CSV without letting pandas guess

The most common mistake is a bare pd.read_csv("don_hang.csv"). pandas infers types, “00123” becomes the number 123, and the customer ID is ruined for good before you have even looked at it.

import pandas as pd

df = pd.read_csv(
    "don_hang.csv",
    dtype={"ma_kh": str},
    na_values={"so_tien": ["-", "chưa có", "liên hệ"]},
    thousands=".",
    decimal=",",
    on_bad_lines="warn",
)

The read_csv documentation recommends using str or object together with suitable na_values to preserve the data rather than have pandas interpret the types. na_values accepts a dict, so you can declare missing values separately for each column, on top of the default list pandas already recognises as NaN.

The thousands and decimal parameters handle locale-specific number formats; the documentation’s example uses a comma as the decimal separator for European data, which is exactly how Vietnamese numbers are written. on_bad_lines="warn" warns about and skips lines with too many fields, instead of crashing the whole read or silently dropping lines.

Check after this step: print df.dtypes and df.head(). The ma_kh column must keep its leading zeros, and so_tien must be the float 1250000.5. Read every bad-line warning carefully and note the line numbers, because they are the first item in your report to the client.

Step 2: open each Excel sheet before merging

A client’s Excel file rarely starts on row 1. The first three rows are usually the company name, the report title and the export date.

sheets = pd.read_excel(
    "bao_cao.xlsx",
    sheet_name=None,
    skiprows=3,
    dtype=object,
)
for ten, bang in sheets.items():
    print(ten, bang.shape)

With sheet_name=None, read_excel returns a dict containing a DataFrame for each sheet, so you can see at once how many rows each branch has. dtype=object keeps the data exactly as stored in Excel, with no type inference; the price is that every column becomes object, and you convert the types yourself in the next step.

If the sheets carry different numbers of junk rows, skiprows also accepts a function: it is called on each row index, and returning True skips the row. For example, skiprows=lambda i: i in (0, 1, 2). Check: every sheet must have the same number of columns; any sheet that differs is one where the client inserted a column by hand.

Step 3: normalise numeric strings before converting

After step 2, the money column from Excel is a mix: cells the client typed as numbers are numbers, and cells typed as text are strings like “1.250.000,5”. Feed that column straight into pd.to_numeric and none of the Vietnamese-style strings will parse; with errors="coerce" they all turn into NaN. You lose perfectly valid cells along with the junk.

So you need a normalisation step: remove the thousands dots, turn the decimal comma into a dot, and do this only for cells that are strings. Leave cells that are already numbers alone, because stripping the dot from 1250000.5 would turn it into a different number.

bang = sheets["ChiNhanh_HN"]

def chuan_hoa(x):
    if isinstance(x, str):
        return x.strip().replace(".", "").replace(",", ".")
    return x

so_tien = bang["so_tien"].map(chuan_hoa)
thieu_san = so_tien.isna().sum()
bang["so_tien"] = pd.to_numeric(so_tien, errors="coerce")
mat_khi_ep = bang["so_tien"].isna().sum() - thieu_san
print("Ô trống sẵn:", thieu_san, "| Ô không đọc được:", mat_khi_ep)

(The printout reports cells that were already empty and cells that could not be read.)

By default, to_numeric raises an error at the first value it cannot parse. With errors="coerce", that value becomes NaN and the code carries on. Convenient, but this is precisely where the forty “contact us” orders from the opening vanish from the total.

The chuan_hoa (normalise) function above is a simplified version: it assumes the whole column is written the Vietnamese way. If the client also mixes in US-style “1,250,000.5”, you need extra detection rules, and it is better to ask the client before guessing.

Note how the counting works: isna().sum() on the column itself, separating cells that were empty from the start from cells newly broken by conversion. The two numbers mean different things to the client. If only 960 of 1,000 rows still have an amount, every total you report must come with the line “excluding 40 orders with no known amount”.

Step 4: deciding what to do with missing cells is the client’s call

pandas gives you two tools: dropna() removes rows or columns with missing data, and fillna() replaces NA with another value. Both run in a single line of code, and both are business decisions.

Filling a money column with 0 asserts that those orders were free. Dropping the rows asserts that those orders never existed. The advice: only use fillna on columns where the client has confirmed the rule (for example, an empty notes cell becomes an empty string), and keep NaN in the money column and put it in the error report.

Note also that pandas uses different sentinel values to represent NA depending on the data type. After fillna or a type conversion, print dtypes again to make sure no column has changed type unexpectedly.

Duplicates belong in this step too. A tutorial on KDnuggets gives the simple reason: the same record counted several times distorts the analysis. When merging several sheets, an order transferred between two branches can easily appear twice; deduplicate on the key columns the client confirms, not on the entire row.

Step 5: turn errors into a report the client can read

By now you have a nearly clean file, but the real value lies in the list of errors. Pandera, an open-source project from Union.ai, lets you declare a schema for a DataFrame and validate against it. A three-column schema for the branch file might look like this.

# Minh hoạ rút gọn; đối chiếu tên API với phiên bản Pandera bạn cài
import pandera as pa

schema = pa.DataFrameSchema({
    "ma_kh": pa.Column(str, pa.Check.str_matches(r"^\d{5}$")),
    "so_tien": pa.Column(float, pa.Check.ge(0), nullable=False),
    "ngay": pa.Column("datetime64[ns]"),
})

try:
    schema.validate(bang, lazy=True)
except pa.errors.SchemaErrors as err:
    loi = err.failure_cases
    print(loi[["column", "check", "index", "failure_case"]])
    loi.to_csv("loi_gui_khach.csv", index=False)

(The comment at the top notes that this is a simplified illustration and that API names should be checked against the Pandera version you have installed.)

The key is lazy=True: instead of stopping at the first error, Pandera collects every error into a single report. The failure_cases table shows which column, which rule, which row and which value caused each failure. You export it to one file and send it to the client once, instead of ten emails along the lines of “one more error”.

Check: deliberately change one customer ID to “123” and delete one amount cell in the sample file. Both must appear in the same report, not one per run.

The most common mistakes

The first is reading the file bare and fixing it later: once leading zeros are gone, they cannot be recovered from the DataFrame. The next is setting on_bad_lines to silently skip to cut down the noise, so nobody knows how many lines were dropped.

A subtler one is to_numeric with coerce on an Excel column that has not been normalised, as seen in step 3: the code runs smoothly, but half the column disappears. The most dangerous is fillna(0) on a money column to make the chart look complete. Combined with pandas summing NA as 0, you get a total that is certainly wrong but looks entirely convincing.

On the client site, which answer earns trust?

Picture the first day of a project: you receive a folder of Excel files by email, and at the meeting the next day the client asks: “Does this match our books?”

The weak answer is “the cleaning is done”. The good answer is: “We read 1,000 rows; 40 are missing an amount and 3 are badly formatted. Here is the list for you to fix at the source.” That answer shows the client you understand their data down to the row.

On your CV, do not write “proficient in pandas”. Describe a concrete pipeline: reading multi-sheet files with fixed dtypes, handling Vietnamese-format numbers, and reporting errors with Pandera.

When reading a job description, look for lines that mention ingesting client data; if they are there, that is your cue to tell exactly this story in the interview.

The cleanest number you can bring to a first meeting is the one that comes with a list of the rows it does not yet include.