Excelでデータを集計するには、関数や小計、ピボットテーブル等を使用します。Python in Excel では、pandasのオブジェクトDataFrameのメソッドを使用して、グルーピングや並び替え、集計処理ができます。この記事では、DataFrameのメソッド、agg, groupby, sort_values, reindex を使った集計方法を紹介します。
出力結果例
サンプルデータ
Anaconda の公式サイトにあるサンプルデータを使用して説明します。以下のページ内の「Financial Sample Excel Workbook」のリンクからダウンロードできます。
テーブル名が「financials」、データが700件の売上データが入っています。
サンプルコード
前準備
サンプルデータのブックにシートを追加し、A1セルに以下のコードを入力します。
5行目のコードは、列名の前後にスペースが入っているので取り除いています。
例)「 Sales 」→「Sales」
# A1セル
# dataframeを定義
df0 = xl("financials[#すべて]", headers=True)
# 列名の前後のスペースを取り除く
df0.columns = [col.strip() for col in df0.columns]
Pythonの入力方法や出力種類、DataFrameの定義方法については、以下の記事で詳しく紹介しています。
集計(aggメソッド)
集計関数のみ指定
# A3セル
# カウント、合計、平均、最小値、最大値を集計する(配列で指定)
df1 = df0.agg(["count", "sum", "mean", "min", "max"])
# 数値型の列だけを出力する(コメントアウトしてもOK)
df1.select_dtypes(include="number")
結果
列名と集計関数を指定
# A10セル
# 列名と集計関数名を個別に指定(辞書型で指定)
df2 = df0.agg({
"Units Sold": ["sum", "mean", "min", "max"],
"Sale Price": ["mean", "min", "max"],
"Sales" : ["sum", "mean", "min", "max"],
})
結果 「Sale Price」のみ sum を指定していないので「#NUM!」(NaN) となる
集計関数1つを指定
集計関数を1つ指定するときは指定方法によって戻り値の型が変わります。
# O1セル 集計関数名を配列で指定
df11 = df0.agg(["count"])
# L1セル 集計関数名をスカラーで指定
sr12 = df0.agg("count")
結果 配列で指定した場合、戻り値は DataFrame オブジェクト
スカラーで指定した場合、戻り値は Series オブジェクトになる。
Series オブジェクトはラベル付きの1次元配列、DataFrame オブジェクトはラベル付きの2次元表構造です。
↓の画像の Series は2次元構造のように見えますが、左側の列はインデックス(ラベル)です。
index プロパティ、values プロパティの値も載せておきます。
グルーピング(groupbyメソッド)
グループラベルをインデックスにするか(as_index)
# groupbyメソッドのas_indexの指定によって、集計後のスピルの表示が少し変わる
# as_indexのデフォルト値はTrue
# A16セル
df3 = df0.groupby(["Country", "Segment"], as_index=False).agg({"Sales": "sum"})
# E16セル as_index=Trueと同じ結果
df4 = df0.groupby(["Country", "Segment"]).agg({"Sales": "sum"})
結果 引数 as_index が False の場合、SQLスタイルの出力結果が返り、
True(デフォルト)の場合、グループラベルがインデックスとして返される。
first, last 集計関数
first, last メソッドは、groupby メソッドと一緒に使用しないと「no results」エラーになります。
# A91セル
df9 = df0.groupby(["Product"], as_index=False).agg({"Profit": ["first", "last"]})
# E91セル
df9 = df0.agg({"Profit": ["first", "last"]}) # エラーになる
結果
並び替え(sort_valuesメソッド)
昇順 or 降順(ascending)
# A43セル
# ascendingのデフォルト値はTrue(昇順)
df5 = df0.groupby(["Country"], as_index=False).agg({"Sales": "sum"})
#「Country」項目を降順で並び替え
df5.sort_values("Country", ascending=False)
結果 引数 ascending を False にすると降順となる
複数項目の並び替え
# A50セル
df6 = df0.groupby(["Country", "Discount Band"], as_index=False).agg({"Sales": "sum"})
#「Country」項目を昇順、「Discount Band」項目を降順で並び替え
df6.sort_values(["Country", "Discount Band"], ascending=[True, False])
結果
特定の順番で並べる(key)
前述の例では「Discount Band」項目を値の降順で並び替えていますが、引数 key に式や関数を指定すると特定の順番で並び替えることができます。
# A72セル
d_order = {"None":0, "Low":1, "Medium":2, "High":3} # 並び順の定義(辞書型)
df7 = df0.groupby(["Discount Band"], as_index=False).agg({"Sales": "sum"})
df7.sort_values("Discount Band", key=lambda col: col.map(d_order))
結果
引数 key に渡す式にリストを使用する場合、リスト内にすべての値が網羅されている必要があります。リストにない値が処理対象のDataFrameにあった場合、ValueErrorが発生します。
辞書型の場合はエラーになりません。
# これはOK
l_order = ["None", "Low", "Medium", "High"] # 並び順の定義(リスト)
# これはNG(ValueErrorが発生する)
#l_order = ["None", "Low", "Medium"] #「High」が含まれていない
df7 = df0.groupby(["Discount Band"], as_index=False).agg({"Sales": "sum"})
df7.sort_values("Discount Band", key=lambda col: col.map(l_order.index))
引数 key を使って複数列の並び替えをする方法が分からない…
NaNを先頭 or 最後に並べる(na_position)
# A78セル
d_order = {"None":0, "Low":1, "High":3} # 並び順の定義(「Medium」は未定義)
df8 = df0.groupby(["Discount Band"], as_index=False).agg({"Sales": "sum"})
# na_positionを指定しない(デフォルト「last」)
df8.sort_values("Discount Band", key=lambda col: col.map(d_order))
# A84セル
# na_positionに「first」を指定
df8.sort_values("Discount Band", key=lambda col: col.map(d_order),
na_position="first")
結果
行・列の抽出(reindexメソッド)
列の抽出(columns)
# A111セル
df10 = df0.reindex(columns=["Units Sold", "Discounts", "Sales"])
df10.agg(["count", "sum", "mean", "min", "max"])
結果 SQL文のselect句的な?
# reindex使わない方法もあり
df10 = df0[["Units Sold", "Discounts", "Sales"]]
df10.agg(["count", "sum", "mean", "min", "max"])
行の抽出(labels)
# A124セル
# as_index=Trueでグループラベルをインデックスに変換
df12 = df0.groupby(["Country", "Segment"], as_index=True).agg({"Sales": "sum"})
# インデックス「Government」で抽出
df12.reindex(labels=["Government"], level="Segment")
結果 列の値ではなくインデックスを使って抽出している。(SQL文のwhere句とはちょっと違う)
ちょっとしたメモ
agg と aggregate の違い
agg メソッドは、aggregate メソッドのエイリアスです。つまり同じです。好きな方を使いましょう。
agg メソッドと他の集計関数(count, sumとか)の違い
この記事では集計する方法として agg メソッドの第1引数にcount, sum等の集計関数を指定する方法を紹介しましたが、count や sumメソッドを直接呼び出す方法もあります。戻り値はSeriesオブジェクトまたはスカラーになります。(agg メソッドの戻り値は、DataFrame、Series、スカラー)
# 戻り値は、Seriesオブジェクトかスカラー
sr1 = df0.count()
sr2 = df0.sum()
sr3 = df0.mean()
sr4 = df0.min()
sr5 = df0.max()
コメント