はじめに
前回は以下記事を書きました。今回からもう少し実践的な例を挙げたいと思います。
売上明細を売上番号で集約する例を考えてみます。実際の現場ではデータの粒度を調整することもあります。
以下のように5明細の売上データの売上金額を売上金額合計として足し込み、1つの売上レコードに集約する方法を試してみます。
ところどころにPower QueryのM式との比較をしてみます。
コード全体
コード全体は以下のとおりになります。
import pandas as pd
# 1.CSV読込
df = pd.read_csv(
r"Data\売上データ_100万件BOM付き.csv",
encoding="utf-8-sig"
)
# 2.売上金額を数値化
df["売上金額"] = pd.to_numeric(df["売上金額"])
# 3.売上データをグループ化
df_sum = (
df.groupby("売上番号", as_index=False)["売上金額"]
.sum()
.rename(columns={"売上金額": "売上金額合計"})
)
# 4.不要列削除と重複削除
df_base = df.drop(columns=["売上金額", "売上明細番号"])
.drop_duplicates(subset=["売上番号"])
# 5.売上番号でマージ
result = df_base.merge(
df_sum,
on="売上番号",
how="left"
)
1.CSVファイルの読込
売上データのCSVを読込みます。
import pandas as pd
# CSV読込（UTF-8 BOM付き対応）
df = pd.read_csv(
r"Data\売上データ_100万件BOM付き.csv",
encoding="utf-8-sig"
)
【ご参考】CSVファイルの読込（Python Pandas VS Power Query）
https://qiita.com/Masayanf/items/c7f420cab8be30871662
Power Queryでは以下のようになります。
ソース = Csv.Document(File.Contents("C:\売上データ_100万件BOM付き.csv"),
[Delimiter=",", Columns=null, Encoding=65001, QuoteStyle=QuoteStyle.Csv]),
昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])
2.売上金額を数値化
売上金額を数値に変換します。
df["売上金額"] = pd.to_numeric(df["売上金額"])
Pandasでの列の参照
df.loc[:,"売上金額"]を省略してdf["売上金額"]と簡潔に記述できます。
Power Queryでは以下のようになります。
型の変更 = Table.TransformColumnTypes(前のステップ, {{"売上金額", type number}})
3.売上データをグループ化
売上番号で集約します（売上金額は合計）。
groupby関数のas_indexはグループ化する列のインデックス化をBool型で指定します。
※True：インデックス化する。False：インデックス化しない。
df_sum = (
df.groupby("売上番号", as_index=False)["売上金額"]
.sum()
.rename(columns={"売上金額": "売上金額合計"})
)
Pandasでのグループ化
df.groupby関数を使うとDataFrameGroupbyオブジェクトが
["売上金額"]のように列を付けるとSeriesGroupByオブジェクトが
それぞれ生成されます。
SeriesGroupByオブジェクトはsumのような集約メソッドが使えます。
集約メソッドを実行すると、基準となる列、集約列のDataFrameを返しますので
renameメソッドで列名を変更します。
Power Queryでは以下のようになります。
グループ化 = Table.Group(前のステップ, {"売上番号"}, {{"売上金額合計", each List.Sum([売上金額]), type number}})
4.不要列削除と重複削除
列を削除して、売上番号の重複を削除します。
df_base = df.drop(columns=["売上金額", "売上明細番号"])
.drop_duplicates(subset=["売上番号"])
Pandasでのメソッドチェーン
dropメソッドの実行結果はDataFrameを返すため、そのままdrop_duplicatesメソッドが使えます。
Power Queryでは以下のようになります。グループ化前の「型の変更」ステップに戻る必要があります。
グループ化前のステップ = 型の変更
不要列削除 = Table.RemoveColumns(グループ化前のステップ,{"売上金額", "売上明細番号"}),
重複削除 = Table.Distinct(不要列削除, {"売上番号"})
5.売上合計金額マージ
売上番号で集約した売上金額合計をマージします。
result = df_base.merge(
df_sum,
on="売上番号",
how="left"
)
Pandasでのマージ
左側がdf_base。右側がdf_sumで、売上番号をキーに左外部結合をします。
Power Queryでは以下のようになります。
クエリのマージ = Table.NestedJoin(前のステップ, {"売上番号"}, グループ化, {"売上番号"},"グループ化",JoinKind.LeftOuter),
クエリの展開 = Table.ExpandTableColumn(クエリのマージ, "グループ化", {"売上金額合計"})
まとめ（補足）
今回は、df_baseという中間のDataFrameを使ってマージして売上明細を集約しました。
以下のようにaggメソッドを使うとグループ化と同時に、各列についてどのような集約をするか指定できるため、別途Dataframeを作成してマージする必要がありません。
result = (
df.groupby("売上番号", as_index=False)
.agg(
売上年月=("売上年月", "first"),
売上金額合計=("売上金額", "sum"),
タイトル=("タイトル", "first"),
顧客名=("顧客名", "first"),
顧客番号=("顧客番号", "first"),
契約先=("契約先", "first"),
契約先番号=("契約先番号", "first"),
)
)
Power Queryの場合は以下のようになります。
グループ化 = Table.Group(
前のステップ, {"売上番号"},
{
{"売上年月", each List.First([売上年月]), type text},
{"売上金額合計", each List.Sum([売上金額]), type number},
{"タイトル", each List.First([タイトル]), type text},
{"顧客名", each List.First([顧客名]), type text},
{"顧客番号", each List.First([顧客番号]), type text},
{"契約先", each List.First([契約先]), type text},
{"契約先番号", each List.First([契約先番号]), type text}
}
)
実際の現場では取得する列が多いこともありますので、今回は4.不要列削除と重複削除、5.売上番号でマージを行いました。