🐍

（学習ログ）Python020：売上データと顧客台帳の前処理とピボット集計

2025/10/13に公開

プログラミング学習

idea

1. はじめに

この記事の目的

実務でよく扱う売上データと顧客台帳を題材に、pandasを使ったデータの前処理からピボット集計までの一連の流れを習得することを目指します。

この記事の概要

本記事では、以下の内容を順に解説します。

CSVとExcelファイルの読み込み
日付型への変換と月次データの作成
文字列の正規化(大文字化・スペース削除)
欠損値の補完ロジック
複数のデータフレームの結合
ピボットテーブルによる多角的な集計
離脱顧客の抽出

2. サンプルコード

コード全文

import pandas as pd

# ファイルの読み込み
sample_uriage_data = pd.read_csv("002_サンプル/sample_uriage_data.csv")
sample_customer_ledger = pd.read_excel("002_サンプル/sample_customer_ledger.xlsx")

# 売上データの日付処理
sample_uriage_data["purchase_date"] = pd.to_datetime(sample_uriage_data["purchase_date"])
sample_uriage_data["purchase_month"] = sample_uriage_data["purchase_date"].dt.strftime("%Y%m")

# 商品名の正規化
sample_uriage_data["item_name"] = sample_uriage_data["item_name"].str.upper()  # 大文字化
sample_uriage_data["item_name"] = sample_uriage_data["item_name"].str.replace("　", "")  # 全角スペース削除
sample_uriage_data["item_name"] = sample_uriage_data["item_name"].str.replace(" ", "")  # 半角スペース削除

# 欠損値の補完(同じ商品名の最大価格で埋める)
flg_is_null = sample_uriage_data["item_price"].isnull()
for NaN_item_name in list(sample_uriage_data.loc[flg_is_null, "item_name"].unique()):
    impute_price = sample_uriage_data.loc[(~flg_is_null) & (sample_uriage_data["item_name"]==NaN_item_name), "item_price"].max()
    sample_uriage_data.loc[(flg_is_null) & (sample_uriage_data["item_name"]==NaN_item_name), "item_price"] = impute_price

# データの並び替えと欠損値チェック
sample_uriage_data.sort_values(by=["item_name"], ascending=True)
sample_uriage_data.isnull().any(axis=0)

# 顧客台帳の顧客名正規化
sample_customer_ledger["顧客名"] = sample_customer_ledger["顧客名"].str.replace("　", "")  # 全角スペース削除
sample_customer_ledger["顧客名"] = sample_customer_ledger["顧客名"].str.replace(" ", "")  # 半角スペース削除

# 登録日の変換(Excelシリアル値と文字列の混在を処理)
flg_is_serial = sample_customer_ledger["登録日"].astype("str").str.isdigit()
fromSerial = pd.to_timedelta(sample_customer_ledger.loc[flg_is_serial, "登録日"].astype("float") - 2, unit="D") + pd.to_datetime('1900/1/1')
fromString = pd.to_datetime(sample_customer_ledger.loc[~flg_is_serial, "登録日"])
sample_customer_ledger["登録日"] = pd.concat([fromSerial, fromString])
sample_customer_ledger["登録年月"] = sample_customer_ledger["登録日"].dt.strftime("%Y%m")

# データの結合
sample_join_data = pd.merge(sample_uriage_data, sample_customer_ledger, left_on="customer_name", right_on="顧客名", how="left")
sample_join_data = sample_join_data.drop("customer_name", axis=1)  # 重複列の削除

# 必要な列のみ抽出してCSV出力
dump_data = sample_join_data[["purchase_date", "purchase_month", "item_name", "item_price", "顧客名", "かな", "地域", "メールアドレス", "登録日"]]
dump_data.to_csv("dump_data.csv", index=False)

# 出力したデータを再読み込みして集計
sample_import_dump = pd.read_csv("dump_data.csv")
sample_byItem = sample_import_dump.pivot_table(index="purchase_month", columns="item_name", aggfunc="size", fill_value=0)
sample_byPrice = sample_import_dump.pivot_table(index="purchase_month", columns="item_name", values="item_price", aggfunc="sum", fill_value=0)
sample_byCustomer = sample_import_dump.pivot_table(index="purchase_month", columns="顧客名", aggfunc="size", fill_value=0)
sample_byRegion = sample_import_dump.pivot_table(index="purchase_month", columns="地域", aggfunc="size", fill_value=0)

# 離脱顧客の抽出(購入履歴のない顧客)
sample_away_data = pd.merge(sample_uriage_data, sample_customer_ledger, left_on="customer_name", right_on="顧客名", how="right")
sample_away_data[sample_away_data["purchase_date"].isnull()][["顧客名", "メールアドレス", "登録日"]]

コード解説

ファイルの読み込み

sample_uriage_data = pd.read_csv("002_サンプル/sample_uriage_data.csv")
sample_customer_ledger = pd.read_excel("002_サンプル/sample_customer_ledger.xlsx")

売上データ(CSV形式)と顧客台帳(Excel形式)を読み込みます。pandasはCSVとExcelの両方を扱えるため、業務データの取り扱いに非常に便利です。

日付型への変換と月次データの作成

sample_uriage_data["purchase_date"] = pd.to_datetime(sample_uriage_data["purchase_date"])
sample_uriage_data["purchase_month"] = sample_uriage_data["purchase_date"].dt.strftime("%Y%m")

購入日を日付型に変換し、集計用に「202501」のような年月形式の列を作成します。月次集計では必須の処理です。

商品名の正規化

sample_uriage_data["item_name"] = sample_uriage_data["item_name"].str.upper()
sample_uriage_data["item_name"] = sample_uriage_data["item_name"].str.replace("　", "")
sample_uriage_data["item_name"] = sample_uriage_data["item_name"].str.replace(" ", "")

商品名を大文字に統一し、全角・半角スペースを削除します。表記揺れを防ぐための重要な前処理です。

欠損値の補完

flg_is_null = sample_uriage_data["item_price"].isnull()
for NaN_item_name in list(sample_uriage_data.loc[flg_is_null, "item_name"].unique()):
    impute_price = sample_uriage_data.loc[(~flg_is_null) & (sample_uriage_data["item_name"]==NaN_item_name), "item_price"].max()
    sample_uriage_data.loc[(flg_is_null) & (sample_uriage_data["item_name"]==NaN_item_name), "item_price"] = impute_price

価格が欠損している商品について、同じ商品名の最大価格で補完します。商品ごとにループ処理を行う実務的な手法です。

Excelシリアル値の処理

flg_is_serial = sample_customer_ledger["登録日"].astype("str").str.isdigit()
fromSerial = pd.to_timedelta(sample_customer_ledger.loc[flg_is_serial, "登録日"].astype("float") - 2, unit="D") + pd.to_datetime('1900/1/1')
fromString = pd.to_datetime(sample_customer_ledger.loc[~flg_is_serial, "登録日"])
sample_customer_ledger["登録日"] = pd.concat([fromSerial, fromString])

Excelの日付がシリアル値(数値)と文字列で混在している場合の処理です。シリアル値は1900年1月1日からの日数で表現されるため、適切な変換が必要です。

データの結合

sample_join_data = pd.merge(sample_uriage_data, sample_customer_ledger, left_on="customer_name", right_on="顧客名", how="left")
sample_join_data = sample_join_data.drop("customer_name", axis=1)

売上データと顧客台帳を顧客名で紐づけます。how="left"は左側(売上データ)を基準にした結合を意味します。

ピボットテーブルの作成

sample_byItem = sample_import_dump.pivot_table(index="purchase_month", columns="item_name", aggfunc="size", fill_value=0)
sample_byPrice = sample_import_dump.pivot_table(index="purchase_month", columns="item_name", values="item_price", aggfunc="sum", fill_value=0)

月別×商品別のクロス集計を作成します。1つ目は購入件数、2つ目は売上金額の集計です。

離脱顧客の抽出

sample_away_data = pd.merge(sample_uriage_data, sample_customer_ledger, left_on="customer_name", right_on="顧客名", how="right")
sample_away_data[sample_away_data["purchase_date"].isnull()][["顧客名", "メールアドレス", "登録日"]]

how="right"で顧客台帳を基準に結合し、購入日が欠損している(=購入履歴がない)顧客を抽出します。

実行結果例

売上データの確認

print(sample_uriage_data.head(3))

  purchase_date purchase_month item_name  item_price customer_name
0    2025-01-15         202501   APPLEXL        1500          山田太郎
1    2025-01-20         202501  BANANASM         300          佐藤花子
2    2025-02-05         202502   APPLEXL        1500          鈴木一郎

ピボットテーブル(商品別件数)

print(sample_byItem)

item_name      APPLEXL  BANANASM  ORANGEM
purchase_month                           
202501              15         8       12
202502              18        10       14
202503              12         9       10

離脱顧客の抽出結果

print(sample_away_data[sample_away_data["purchase_date"].isnull()][["顧客名", "メールアドレス", "登録日"]].head(3))

    顧客名              メールアドレス           登録日
45  田中次郎  tanaka@example.com  2024-12-01
67  高橋美咲  takahashi@example.com  2024-11-15
89  伊藤健太  ito@example.com  2025-01-10

3. エラーと確認のポイント

代表的なエラー文

`KeyError: 'column_name'`

原因: 指定した列名がデータフレームに存在しない場合に発生します。

# 確認方法
print(sample_uriage_data.columns)  # 列名の一覧を確認

`ValueError: cannot convert float NaN to integer`

原因: 欠損値を含むデータを整数型に変換しようとした場合に発生します。

# 対処方法: 欠損値を先に処理するか、floatのまま扱う
sample_uriage_data["item_price"] = sample_uriage_data["item_price"].fillna(0)

`TypeError: expected string or bytes-like object`

原因: 文字列処理を数値型に対して実行した場合に発生します。

# 対処方法: 先にstr型に変換
sample_customer_ledger["登録日"] = sample_customer_ledger["登録日"].astype("str")

よく使う確認メソッドと出力例

`.shape` - データの行数・列数を確認

print(sample_uriage_data.shape)
# 出力例: (1250, 5)  # 1250行、5列

`.columns` - 列名の一覧を確認

print(sample_uriage_data.columns)
# 出力例: Index(['purchase_date', 'purchase_month', 'item_name', 'item_price', 'customer_name'], dtype='object')

`.head()` - 先頭数行を確認

print(sample_uriage_data.head(3))
# 最初の3行が表示される

`.isnull().sum()` - 列ごとの欠損値数を確認

print(sample_uriage_data.isnull().sum())
# 出力例:
# purchase_date     0
# purchase_month    0
# item_name         0
# item_price       15
# customer_name     0

`.dtypes` - 各列のデータ型を確認

print(sample_uriage_data.dtypes)
# 出力例:
# purchase_date     datetime64[ns]
# purchase_month            object
# item_name                 object
# item_price               float64
# customer_name             object

`.unique()` - ユニークな値を確認

print(sample_uriage_data["item_name"].unique())
# 出力例: array(['APPLEXL', 'BANANASM', 'ORANGEM'], dtype=object)

4. まとめ

本記事では、売上データと顧客台帳を題材にしたデータ処理の一連の流れを学習しました。

データの前処理技術: 日付変換、文字列正規化、欠損値補完など、実務で頻出する処理パターンを理解できました
データ結合とピボット集計: pd.merge()による複数データの紐づけと、pivot_table()による多角的な集計手法を習得できました
エラーハンドリング: よくあるエラーとその対処法、データ確認の基本メソッドを身につけることができました