# File Excel bẩn của khách: đếm ô mất trước khi pandas cộng tổng

> Một ô "chưa có" trong cột tiền có thể làm doanh thu báo cáo thấp hơn thực tế mà không ai hay, vì pandas tính ô thiếu như số 0.

Bản gốc: https://fdetimes.net/vi/bach-khoa/thuc-hanh-lam-sach-file-excel-csv-ban-cua-khach-bang-pandas/

Một nghìn đơn hàng, bốn mươi đơn có chữ "liên hệ" trong cột số tiền. Bạn ép cột đó sang số, bốn mươi ô biến thành NaN, rồi gọi `.sum()`. Con số trả ra trông rất gọn gàng, và thiếu đúng bốn mươi đơn mà không có cảnh báo nào.

Tình huống trên là giả định, nhưng cơ chế thì không. Tài liệu pandas ghi rõ: khi cộng tổng, giá trị NA hoặc dữ liệu rỗng được coi như bằng 0. Với một FDE, đây là cái bẫy nguy hiểm nhất của tuần đầu ở chỗ khách, vì con số sai sẽ được trình lên trong buổi demo.

Bài này đi qua một quy trình bạn có thể chạy trên laptop ngay tối nay: đọc file thô, khai báo giá trị thiếu, ép kiểu có kiểm soát, đếm thiệt hại, rồi gom lỗi thành báo cáo gửi khách. Các đoạn code là bản rút gọn để học; phần nào đơn giản hoá sẽ được ghi chú.

## Bạn sẽ dựng gì, và cần chuẩn bị gì?

Thử hình dung một nhà phân phối gửi hai file: `don_hang.csv` và `bao_cao.xlsx`. File CSV có cột `ma_kh` dạng "00123", cột `so_tien` viết kiểu Việt Nam như "1.250.000,5" (được bọc trong ngoặc kép), và vài ô ghi "-" hoặc "chưa có". File Excel có ba dòng tiêu đề trang trí ở đầu, mỗi chi nhánh một sheet.

Bạn cần Python 3, pandas, thư viện đọc Excel đi kèm môi trường của bạn và Pandera cho bước cuối. Hãy tự tạo hai file mẫu như trên, khoảng 20 dòng, cố tình cài vào đó đủ loại rác. Làm bẩn dữ liệu bằng tay là cách nhanh nhất để hiểu từng tham số làm gì.

## Bước 1: đọc CSV mà không để pandas đoán

Lỗi phổ biến nhất là gọi `pd.read_csv("don_hang.csv")` trần. pandas sẽ suy kiểu, "00123" thành số 123, và mã khách hàng hỏng vĩnh viễn trước cả khi bạn kịp nhìn.

```python
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",
)
```

Tài liệu `read_csv` khuyên dùng `str` hoặc `object` kèm `na_values` phù hợp để giữ nguyên dữ liệu, không để pandas diễn giải kiểu. `na_values` nhận dict để khai báo giá trị thiếu riêng cho từng cột, bổ sung vào danh sách mặc định pandas đã hiểu là NaN.

Hai tham số `thousands` và `decimal` xử lý số theo định dạng địa phương; tài liệu lấy ví dụ dùng dấu phẩy làm dấu thập phân cho dữ liệu châu Âu, cũng chính là cách người Việt viết số. Còn `on_bad_lines="warn"` sẽ cảnh báo và bỏ qua dòng có quá nhiều trường, thay vì làm sập cả lần đọc hoặc lặng lẽ bỏ dòng.

Kiểm tra sau bước này: in `df.dtypes` và `df.head()`. Cột `ma_kh` phải còn số 0 đứng đầu, `so_tien` phải là số thực 1250000.5. Đọc kỹ từng cảnh báo bad line in ra và ghi lại số dòng, vì đó là mục đầu tiên trong báo cáo cho khách.

## Bước 2: mở từng sheet Excel trước khi gộp

File Excel của khách hiếm khi bắt đầu ở dòng 1. Ba dòng đầu thường là tên công ty, tên báo cáo và ngày xuất.

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

Với `sheet_name=None`, `read_excel` trả về một dict chứa DataFrame cho từng sheet, nên bạn thấy ngay chi nhánh nào có bao nhiêu dòng. `dtype=object` giữ dữ liệu đúng như lưu trong Excel, không suy kiểu; cái giá là mọi cột đều thành `object`, và bạn sẽ tự ép kiểu ở bước sau.

Nếu các sheet có số dòng rác khác nhau, `skiprows` còn nhận một hàm: hàm được gọi trên chỉ số dòng, trả `True` thì dòng bị bỏ. Ví dụ `skiprows=lambda i: i in (0, 1, 2)`. Kiểm tra: số cột của mọi sheet phải bằng nhau; sheet nào lệch là sheet khách đã chèn thêm cột bằng tay.

## Bước 3: chuẩn hoá chuỗi số trước khi ép kiểu

Sau bước 2, cột tiền trong Excel là một mớ lẫn lộn: ô nào khách gõ dạng số thì là số, ô nào gõ dạng chữ thì là chuỗi "1.250.000,5". Nếu đưa thẳng cột này vào `pd.to_numeric`, mọi chuỗi kiểu Việt Nam đều không parse được, và với `errors="coerce"` chúng sẽ thành NaN hết. Bạn sẽ mất cả những ô hoàn toàn hợp lệ.

Vì thế cần một bước chuẩn hoá: bỏ dấu chấm hàng nghìn, đổi dấu phẩy thập phân thành dấu chấm, và chỉ làm vậy với ô là chuỗi. Ô đã là số thì để nguyên, vì xoá dấu chấm của 1250000.5 sẽ biến nó thành một số khác.

```python
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)
```

Mặc định `to_numeric` sẽ raise lỗi ở giá trị đầu tiên không parse được. Với `errors="coerce"`, giá trị đó thành NaN và code chạy tiếp. Tiện, nhưng chính đây là chỗ bốn mươi đơn "liên hệ" ở đầu bài biến mất khỏi tổng.

Hàm `chuan_hoa` ở trên là bản rút gọn: nó giả định cả cột viết theo kiểu Việt Nam. Nếu khách trộn cả "1,250,000.5" kiểu Mỹ, bạn cần thêm luật nhận diện, và tốt nhất là hỏi khách trước khi đoán.

**Điểm mấu chốt:** Ép kiểu bằng coerce mà không đếm số ô bị mất thì chỉ là giấu lỗi đi.

Hãy để ý cách đếm: `isna().sum()` trên chính cột, tách riêng ô trống từ đầu và ô mới hỏng khi ép kiểu. Hai con số này có ý nghĩa khác nhau với khách. Nếu một nghìn dòng chỉ còn chín trăm sáu mươi dòng có số tiền, mọi con số tổng bạn báo cáo phải đi kèm câu "chưa tính 40 đơn không rõ số tiền".

## Bước 4: quyết định với ô thiếu là việc của khách

pandas cho bạn hai công cụ: `dropna()` bỏ hàng hoặc cột có dữ liệu thiếu, `fillna()` thay NA bằng giá trị khác. Cả hai đều chạy được trong một dòng code, và cả hai đều là quyết định nghiệp vụ.

Điền 0 vào cột tiền nghĩa là khẳng định đơn hàng đó miễn phí. Bỏ dòng nghĩa là khẳng định đơn đó không tồn tại. Lời khuyên: chỉ `fillna` với cột mà khách đã xác nhận quy tắc (ví dụ ô ghi chú trống thì để chuỗi rỗng), còn cột tiền thì giữ NaN và đưa vào báo cáo lỗi.

Lưu ý thêm rằng pandas dùng các giá trị sentinel khác nhau để biểu diễn NA tùy kiểu dữ liệu. Sau khi `fillna` hay ép kiểu, hãy in lại `dtypes` để chắc cột không bị đổi kiểu ngoài ý muốn.

Trùng lặp cũng cần xử lý ở bước này. Một hướng dẫn trên KDnuggets nhắc lý do đơn giản: cùng một bản ghi bị đếm nhiều lần sẽ làm sai phân tích. Khi gộp nhiều sheet, đơn hàng chuyển giữa hai chi nhánh rất dễ xuất hiện hai lần; hãy khử trùng lặp theo cột khóa mà khách xác nhận, không phải theo toàn bộ dòng.

## Bước 5: biến lỗi thành báo cáo khách đọc được

Đến đây bạn đã có file gần sạch, nhưng giá trị thật nằm ở danh sách lỗi. Pandera, dự án mã nguồn mở của Union.ai, cho phép khai báo schema cho DataFrame rồi validate nó. Một schema ba cột cho file chi nhánh có thể trông như sau.

```python
# 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)
```

Điểm mấu chốt là `lazy=True`: thay vì dừng ở lỗi đầu tiên, Pandera gom mọi lỗi thành một báo cáo. Bảng `failure_cases` cho biết cột nào, luật nào, dòng nào và giá trị gây lỗi. Bạn xuất nó ra một file và gửi khách một lần, thay vì mười email kiểu "còn một lỗi nữa".

Kiểm tra: cố tình sửa một mã khách thành "123" và xoá một ô tiền trong file mẫu. Cả hai phải hiện trong cùng một báo cáo, không phải lần lượt từng lần chạy.

## Những lỗi hay gặp nhất

Lỗi đầu tiên là đọc trần rồi sửa sau: số 0 đứng đầu đã mất thì không lấy lại được từ DataFrame. Lỗi kế tiếp là đặt `on_bad_lines` sang chế độ bỏ im lặng cho đỡ ồn, và thế là không ai biết bao nhiêu dòng đã rơi mất.

Một lỗi tinh vi hơn là `to_numeric` với `coerce` trên cột Excel chưa chuẩn hoá, như đã thấy ở bước 3: code chạy êm, nhưng nửa cột biến mất. Còn lỗi nguy hiểm nhất là `fillna(0)` trên cột tiền để biểu đồ trông đầy đủ. Kết hợp với việc pandas cộng NA như 0, bạn có một con số tổng chắc chắn sai nhưng trông rất thuyết phục.

## Ở chỗ khách, câu trả lời nào đáng tin?

Thử hình dung ngày đầu của một dự án: bạn nhận một thư mục Excel qua email, và buổi họp hôm sau khách hỏi: "Số này khớp với sổ sách của chúng tôi chưa?"

Câu trả lời yếu là "đã làm sạch xong". Câu trả lời tốt là: "Đọc được 1.000 dòng, 40 dòng thiếu số tiền, 3 dòng sai định dạng, đây là danh sách để anh chị sửa ở nguồn." Câu đó cho khách thấy bạn hiểu dữ liệu của họ đến từng dòng.

Khi viết CV, đừng ghi "thành thạo pandas". Hãy mô tả một pipeline cụ thể: đọc file đa sheet với dtype cố định, xử lý số định dạng Việt Nam, và báo cáo lỗi bằng Pandera.

Khi đọc job description, hãy để ý những dòng nhắc đến việc tiếp nhận dữ liệu từ khách; nếu có, đó là chỗ để bạn kể lại đúng ví dụ này trong buổi phỏng vấn.

Con số sạch nhất bạn mang đến buổi họp đầu tiên là con số đi kèm danh sách những dòng nó chưa tính.

**Thử ngay tuần này:**

- Tự tạo một file CSV 20 dòng có mã khách hàng bắt đầu bằng 00, số tiền dạng 1.250.000,5 và vài ô '-', rồi đọc nó lần lượt với và không có dtype, thousands, decimal để thấy khác biệt.
- Viết một hàm chuẩn hoá chuỗi số kiểu Việt Nam, chạy pd.to_numeric(errors='coerce') trên cột tiền và in ra số ô mới bị biến thành NaN bằng isna().sum().
- Cài Pandera, viết schema ba cột cho file trên, chạy validate với lazy=True và in failure_cases để xem báo cáo lỗi gom lại trông thế nào.

## Nguồn

- [Working with missing data — pandas 3.0.6 documentation](https://pandas.pydata.org/docs/user_guide/missing_data.html)

- [pandas.read_csv — pandas documentation](https://pandas.pydata.org/docs/reference/api/pandas.read_csv.html)

- [pandas.read_excel — pandas documentation](https://pandas.pydata.org/docs/reference/api/pandas.read_excel.html)

- [pandas.to_numeric — pandas documentation](https://pandas.pydata.org/docs/reference/api/pandas.to_numeric.html)

- [How to Clean Messy CSV Files with Python: A Beginner's Guide](https://kdnuggets.com/how-to-clean-messy-csv-files-with-python-a-beginners-guide)

- [Pandera documentation](https://pandera.readthedocs.io/en/stable/)