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

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.

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.

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.

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.

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.

# 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.