Bỏ qua đến nội dung chính
Mục lục

Dự án pandas: phân tích dữ liệu khách hàng theo tỉnh

AIDev Team · duyệt: Nguyễn Việt Chung

Bài 13/13 · Chương 7

Ghép và đổi hình bảng

  • 10 phút
  • Trung cấp
  • Python 3.14
  • Cập nhật

Dự án này phân tích dữ liệu khách hàng của một cửa hàng theo tỉnh bằng pandas. Chương trình đọc ba bảng từ cơ sở dữ liệu SQLite, ghép chúng, kiểm số dòng sau mỗi lần ghép, rồi tìm danh mục mang về nhiều tiền ở từng tỉnh và so nó với danh mục thứ hai. Bạn đi qua năm bước tới chương trình hoàn chỉnh, rồi tự viết một hàm hỏi ngược lại: một danh mục mạnh ở tỉnh nào.

Cần biết trước: merge bị tăng số dòng, agg() và transform().

Chương trình sẽ làm gì

Cửa hàng mẫu AIDev lưu dữ liệu bán hàng trong file cua_hang.db của bộ dữ liệu thực hành. Trong đó, bảng don_hang ghi mã khách và mã sản phẩm, bảng khach_hang ghi tỉnh, bảng san_pham ghi danh mục. Chủ cửa hàng muốn biết, trong các đơn đã hoàn thành, ở mỗi tỉnh danh mục nào mang về nhiều doanh thu. Ông cũng muốn biết vị trí đó vững hay mong manh: hơn danh mục thứ hai bao nhiêu điểm phần trăm, dựa vào nhiều khách hay chỉ một người. Chương trình in số dòng sau từng lần ghép, rồi in các tỉnh mua nhiều cùng danh mục dẫn đầu của chúng.

Thứ tự dựng: đọc từ SQLite, ghép hai lần mà mỗi lần đều kiểm, tính tổng, số khách và tỉ trọng theo tỉnh và danh mục, rồi xếp hạng trong tỉnh để đặt hạng nhất cạnh hạng nhì. Bước 3 và 4 chạy trên một bảng nhỏ gõ sẵn để bạn đối chiếu được từng con số bằng tay; chương trình hoàn chỉnh ở bước 5 chạy cả hai bước đó trên dữ liệu thật.

Bước 1: Đọc ba bảng từ SQLite

Mỗi câu SELECT gọi tên từng cột chương trình dùng tới, nên đọc ba câu này là biết bảng đã ghép sẽ có những cột nào. Điều kiện trạng thái đi qua params, đúng cách của bài read_sql().

python
import sqlite3
import pandas as pd

TRANG_THAI = "Hoàn thành"
con = sqlite3.connect("cua_hang.db")
don = pd.read_sql("SELECT ma_don, ma_kh, ma_sp, so_luong, don_gia "
                  "FROM don_hang WHERE trang_thai = ?", con,
                  params=(TRANG_THAI,))
kh = pd.read_sql("SELECT ma_kh, tinh FROM khach_hang", con)
sp = pd.read_sql("SELECT ma_sp, danh_muc FROM san_pham", con)
con.close()
print(len(don), len(kh), len(sp))
print(kh["ma_kh"].is_unique, sp["ma_sp"].is_unique)
print(don.head(3))

KẾT QUẢ

1313 400 60
True True
    ma_don   ma_kh ma_sp  so_luong  don_gia
0  DH00001  KH0174  0040         2    78000
1  DH00002  KH0168  0048         2    25000
2  DH00003  KH0108  0037         3    18000

Có 1.313 đơn hoàn thành, 400 khách và 60 sản phẩm. Dòng thứ hai là phép kiểm trước khi ghép: mã khách và mã sản phẩm không lặp, nên mỗi đơn khớp với tối đa một khách và một sản phẩm. Mã ở cả ba bảng là chuỗi ("0040"), vì cơ sở dữ liệu lưu chúng dạng TEXT.

Bước 2: Ghép đơn với khách và sản phẩm, kiểm số dòng

Mỗi lần ghép giữ trọn bảng đơn (how="left") và khai validate="many_to_one": nhiều đơn được phép khớp một khách, nhưng một mã khách không được khớp hai dòng. Dòng print ngay sau phép ghép cho thấy số dòng có đổi hay không.

python
import sqlite3
import pandas as pd
TRANG_THAI = "Hoàn thành"
con = sqlite3.connect("cua_hang.db")
don = pd.read_sql("SELECT ma_don, ma_kh, ma_sp, so_luong, don_gia "
                  "FROM don_hang WHERE trang_thai = ?", con,
                  params=(TRANG_THAI,))
kh = pd.read_sql("SELECT ma_kh, tinh FROM khach_hang", con)
sp = pd.read_sql("SELECT ma_sp, danh_muc FROM san_pham", con)
con.close()
bang = don.merge(kh, on="ma_kh", how="left", validate="many_to_one")
print("Đơn:", len(don), "| ghép khách:", len(bang))
bang = bang.merge(sp, on="ma_sp", how="left",
                  validate="many_to_one")
print("ghép sản phẩm:", len(bang))

KẾT QUẢ

Đơn: 1313 | ghép khách: 1313
ghép sản phẩm: 1313

Số dòng giữ nguyên 1.313 qua cả hai lần ghép, và validate không báo gì. Với dữ liệu sạch như cua_hang.db, hai dòng print này trông thừa; chúng có ích vào ngày bảng nguồn bị nạp trùng, như khung dưới.

Bước 3: Doanh thu, số khách và tỉ trọng theo tỉnh và danh mục

Bảng nhỏ dưới đây có đúng các cột của bảng đã ghép, đủ ít dòng để tính tay. agg() tính hai thứ cho mỗi cặp tỉnh và danh mục: tổng doanh thu, và số khách khác nhau đã mua ("nunique" trên ma_kh). transform("sum") trả tổng doanh thu của cả tỉnh về từng dòng, nên phép chia ra phần trăm trong tỉnh.

python
import pandas as pd
bang = pd.DataFrame({
    "ma_kh": ["K1", "K2", "K1", "K3", "K3"],
    "tinh": ["Huế", "Huế", "Huế", "Nghệ An", "Nghệ An"],
    "danh_muc": ["Gia dụng", "Gia dụng", "Đặc sản khô",
                 "Gia dụng", "Cà phê & trà"],
    "so_luong": [3, 1, 2, 1, 4],
    "don_gia": [150000, 120000, 165000, 90000, 52000]})
bang["doanh_thu"] = bang["so_luong"] * bang["don_gia"]
theo_cap = bang.groupby(["tinh", "danh_muc"], as_index=False).agg(
    doanh_thu=("doanh_thu", "sum"), so_khach=("ma_kh", "nunique"))
tong_tinh = theo_cap.groupby("tinh")["doanh_thu"].transform("sum")
theo_cap["phan_tram"] = (theo_cap["doanh_thu"] / tong_tinh
                         * 100).round(1)
print(theo_cap)

KẾT QUẢ

      tinh      danh_muc  doanh_thu  so_khach  phan_tram
0      Huế      Gia dụng     570000         2       63.3
1      Huế   Đặc sản khô     330000         1       36.7
2  Nghệ An  Cà phê & trà     208000         1       69.8
3  Nghệ An      Gia dụng      90000         1       30.2

Huế có doanh thu 900.000 đ, trong đó Gia dụng 570.000 đ, tức 63,3%, do hai khách mua. tong_tinh có cùng index với theo_cap, nên mỗi dòng chia cho tổng của chính tỉnh nó, và phần trăm trong một tỉnh cộng lại được 100. Dùng groupby().sum() thay cho transform() thì kết quả chỉ còn hai dòng, một dòng mỗi tỉnh, và phép chia không còn khớp dòng.

Bước 4: Đặt danh mục dẫn đầu cạnh danh mục thứ hai

Một con số 63,3% chưa nói được danh mục dẫn đầu vững tới đâu: nó cần đứng cạnh danh mục thứ hai. rank() của bài rank() và top N đánh số hạng trong từng tỉnh, rồi một lần merge() nữa đặt hạng nhì vào cùng dòng với hạng nhất; suffixes đặt tên cho cột trùng.

python
import pandas as pd
theo_cap = pd.DataFrame({
    "tinh": ["Huế", "Huế", "Nghệ An", "Nghệ An", "Đà Nẵng"],
    "danh_muc": ["Gia dụng", "Đặc sản khô", "Cà phê & trà",
                 "Gia dụng", "Gia dụng"],
    "phan_tram": [63.3, 36.7, 69.8, 30.2, 100.0]})
theo_cap["hang"] = (theo_cap.groupby("tinh")["phan_tram"]
                    .rank(method="first", ascending=False))
dau = theo_cap[theo_cap["hang"] == 1]
nhi = theo_cap.loc[theo_cap["hang"] == 2, ["tinh", "phan_tram"]]
so_sanh = dau.merge(nhi, on="tinh", how="left",
                    suffixes=("", "_nhi"))
so_sanh["cach"] = (so_sanh["phan_tram"]
                   - so_sanh["phan_tram_nhi"]).round(1)
print(so_sanh.drop(columns="hang").to_string(index=False))

KẾT QUẢ

   tinh     danh_muc  phan_tram  phan_tram_nhi  cach
    Huế     Gia dụng       63.3           36.7  26.6
Nghệ An Cà phê & trà       69.8           30.2  39.6
Đà Nẵng     Gia dụng      100.0            NaN   NaN

Cột cach là số điểm phần trăm mà hạng nhất hơn hạng nhì. Đà Nẵng chỉ có một danh mục nên không có hạng nhì: how="left" vẫn giữ tỉnh này, và cach để trống thay cho một con số bịa ra.

Bước 5: Ghép thành chương trình hoàn chỉnh

Chương trình hoàn chỉnh nối phần đọc của bước 1, hai lần ghép của bước 2, phép tính của bước 3 và phép xếp hạng của bước 4, nay chạy trên dữ liệu thật. Dòng cuối đếm các tỉnh mà danh mục dẫn đầu chỉ do một khách mua.

python
import sqlite3
import pandas as pd

TRANG_THAI = "Hoàn thành"
con = sqlite3.connect("cua_hang.db")
don = pd.read_sql("SELECT ma_don, ma_kh, ma_sp, so_luong, don_gia "
                  "FROM don_hang WHERE trang_thai = ?", con,
                  params=(TRANG_THAI,))
kh = pd.read_sql("SELECT ma_kh, tinh FROM khach_hang", con)
sp = pd.read_sql("SELECT ma_sp, danh_muc FROM san_pham", con)
con.close()

bang = don.merge(kh, on="ma_kh", how="left", validate="many_to_one")
print("Đơn:", len(don), "| ghép khách:", len(bang))
bang = bang.merge(sp, on="ma_sp", how="left",
                  validate="many_to_one")
print("ghép sản phẩm:", len(bang))
bang["doanh_thu"] = bang["so_luong"] * bang["don_gia"]

theo_cap = bang.groupby(["tinh", "danh_muc"], as_index=False).agg(
    doanh_thu=("doanh_thu", "sum"), so_khach=("ma_kh", "nunique"))
tong_tinh = theo_cap.groupby("tinh")["doanh_thu"].transform("sum")
theo_cap["phan_tram"] = (theo_cap["doanh_thu"] / tong_tinh
                         * 100).round(1)
theo_cap["hang"] = (theo_cap.groupby("tinh")["phan_tram"]
                    .rank(method="first", ascending=False))
dau = theo_cap[theo_cap["hang"] == 1]
nhi = theo_cap.loc[theo_cap["hang"] == 2, ["tinh", "phan_tram"]]
so_sanh = dau.merge(nhi, on="tinh", how="left",
                    suffixes=("", "_nhi"))
so_sanh["cach"] = (so_sanh["phan_tram"]
                   - so_sanh["phan_tram_nhi"]).round(1)
so_sanh = so_sanh.sort_values("doanh_thu", ascending=False)
print(len(so_sanh), "tỉnh")
cot = ["tinh", "danh_muc", "so_khach", "phan_tram", "cach"]
print(so_sanh[cot].head(5).to_string(index=False))
mot_khach = so_sanh["so_khach"] == 1
print(mot_khach.sum(), "tỉnh có danh mục dẫn đầu do một khách mua")

KẾT QUẢ

Đơn: 1313 | ghép khách: 1313
ghép sản phẩm: 1313
31 tỉnh
           tinh     danh_muc  so_khach  phan_tram  cach
TP. Hồ Chí Minh  Đặc sản khô        29       28.9   9.0
         Hà Nội     Gia dụng        19       31.2  11.1
      Hải Phòng Cà phê & trà        13       33.6  12.0
        Đà Nẵng  Đặc sản khô        12       30.7  10.3
     Quảng Ngãi     Gia dụng         1       54.4  34.6
5 tỉnh có danh mục dẫn đầu do một khách mua

Đơn hoàn thành đến từ 31 tỉnh, xếp theo doanh thu của danh mục dẫn đầu. Ở bốn thành phố đầu bảng, danh mục dẫn đầu do 12 đến 29 khách mua và hơn hạng nhì 9 đến 12 điểm phần trăm: một vị trí có thật. Quảng Ngãi thì khác: 54,4% doanh thu của tỉnh nằm ở Gia dụng, nhưng chỉ một khách mua nó, và có 5 tỉnh như vậy. Ở những tỉnh đó, "danh mục mạnh" là sở thích của một người, chưa phải điều để đặt hàng theo. Đổi "Hoàn thành" thành "Hoàn trả" rồi chạy lại: chỉ còn 87 đơn, chương trình in 21 tỉnh và 16 tỉnh có danh mục dẫn đầu do một khách mua; với vài đơn trả mỗi tỉnh, bảng này chưa nói được gì về chất lượng nhóm hàng.

Bài tập

Tỉnh dẫn đầu của một danh mục

Chủ cửa hàng hỏi ngược lại: một danh mục bán mạnh ở tỉnh nào? Viết thân hàm tinh_dan_dau(bang, danh_muc): trong các dòng của danh mục danh_muc, cộng doanh_thu theo từng tỉnh, rồi trả tên tỉnh có tổng lớn hơn cả. Hai tỉnh bằng nhau thì lấy tỉnh có tên đứng trước khi sắp chuỗi bằng sorted().

bang là bảng đã ghép như ở bước 5, có ít nhất các cột tinh, danh_muc, doanh_thu, và danh mục được hỏi có ít nhất một dòng. Hàm không sửa bảng của nơi gọi.

Trả về: tên tỉnh, một str.

Ôn lại: agg().

python
import pandas as pd

def tinh_dan_dau(bang, danh_muc):
    pass  # Viết thân hàm thay cho dòng này

Thử mở rộng

Hướng đầu làm được ngay với chương trình trên:

  • Gọi tinh_dan_dau(bang, "Gia dụng") trên bảng thật của bước 5, rồi lặp qua năm danh mục để biết mỗi danh mục mạnh ở tỉnh nào.

  • Chỉ giữ các tỉnh có danh mục dẫn đầu từ ba khách trở lên (so_sanh[so_sanh["so_khach"] >= 3]), rồi xem cach của chúng còn lớn như ở các tỉnh một khách không.

  • Xếp hạng theo so_khach thay cho phan_tram và so xem danh mục dẫn đầu có đổi ở những tỉnh nào.

  • Đếm số đơn theo tỉnh và danh mục thành một bảng chéo bằng crosstab().

Chủ đề

phân tích dữ liệu khách hàng

Dữ liệu của bài

Bài này có hữu ích?

Hỏi đáp

Đang tải câu hỏi…

Tham vấn chuyên môn

Nguyễn Việt Chung

Nhà sáng lập AIDev và VietPlatform · Thạc sĩ AI MSE

  • Tốt nghiệp thủ khoa xuất sắc Thạc sĩ AI MSE.
  • Đồng sáng lập, CTO E-University, SVOnline: số hóa 10+ đại học, 40.000+ sinh viên.
  • Xây hệ thống AI cho doanh nghiệp tại VietPlatform.
Biên soạn: AIDev Team · Rà soát · Quy trình biên tập