【Python in Excel】DataFrameを使って集計する方法（agg, groupby, sort_values, reindex）

Excelでデータを集計するには、関数や小計、ピボットテーブル等を使用します。Python in Excel では、pandasのオブジェクトDataFrameのメソッドを使用して、グルーピングや並び替え、集計処理ができます。この記事では、DataFrameのメソッド、agg, groupby, sort_values, reindex を使った集計方法を紹介します。

2023年10月現在、Python in Excel は Microsoft 365 Insider Program のベータチャネルを選択することによって使用できるベータ版のExcelでのみ利用可能です。
ベータ版のExcelのインストール・初期設定方法は以下で詳しく紹介しています。

Python in Excel が使えるExcelをインストール・初期設定する方法（画像付き）

［Python in Excel］が使えるベータ版Excelをインストールする方法、Microsoft 365 Insider プログラムの参加方法、Python in Excel で Hello World! を出力する方法をご紹介します

出力結果例

サンプルデータ

Anaconda の公式サイトにあるサンプルデータを使用して説明します。以下のページ内の「Financial Sample Excel Workbook」のリンクからダウンロードできます。

From Notebook to Workbook: Power Up Your Data Science Skills with Python in Excel | Anaconda

In this post, we will explore how Python in Excel empowers Excel users to work with Python and Anaconda Distribution directly within the Excel spreadsheets.

テーブル名が「financials」、データが700件の売上データが入っています。

サンプルコード

前準備

サンプルデータのブックにシートを追加し、A1セルに以下のコードを入力します。
５行目のコードは、列名の前後にスペースが入っているので取り除いています。
例）「 Sales 」→「Sales」

# A1セル
# dataframeを定義
df0 = xl("financials[#すべて]", headers=True)
# 列名の前後のスペースを取り除く
df0.columns = [col.strip() for col in df0.columns]

Pythonの入力方法や出力種類、DataFrameの定義方法については、以下の記事で詳しく紹介しています。

【超基本】Python in Excel の使い方とルール

Python in Excel の入力方法は難しくはありませんが、Excelのワークシート関数の入力の仕方とは少し勝手が違います。この記事では、Python in Excel の入力の仕方と出力の種類、計算順序やパフォーマンスを向上させるた...

[DataFrame] xl関数の指定方法いろいろ

この記事では、DataFrameの定義、xl関数の使い方や注意点について説明します。DataFrameを使うと、Excelのセル範囲や名前、テーブル、Power Queryのデータを Python in Excel で扱えるようになります。...

集計（aggメソッド）

pandas.DataFrame.agg — pandas 2.1.3 documentation

集計関数のみ指定

# 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) となる

集計関数１つを指定

集計関数を１つ指定するときは指定方法によって戻り値の型が変わります。

# O1セル　集計関数名を配列で指定
df11 = df0.agg(["count"])

# L1セル　集計関数名をスカラーで指定
sr12 = df0.agg("count")

結果　配列で指定した場合、戻り値は DataFrame オブジェクト
　　　スカラーで指定した場合、戻り値は Series オブジェクトになる。

１つの集計関数名を配列で指定した例

aggメソッドの例（指定方法によってはSeriesが返る） — １つの集計関数名をスカラーで指定した例

Series オブジェクトはラベル付きの１次元配列、DataFrame オブジェクトはラベル付きの２次元表構造です。
↓の画像の Series は２次元構造のように見えますが、左側の列はインデックス（ラベル）です。

index プロパティ、values プロパティの値も載せておきます。

グルーピング（groupbyメソッド）

pandas.DataFrame.groupby — pandas 2.1.3 documentation

グループラベルをインデックスにするか（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メソッド）

pandas.DataFrame.sort_values — pandas 2.1.3 documentation

昇順 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メソッド）

pandas.DataFrame.reindex — pandas 2.1.3 documentation

列の抽出（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 メソッドの第１引数にcount, sum等の集計関数を指定する方法を紹介しましたが、count や sumメソッドを直接呼び出す方法もあります。戻り値はSeriesオブジェクトまたはスカラーになります。（agg メソッドの戻り値は、DataFrame、Series、スカラー）

# 戻り値は、Seriesオブジェクトかスカラー
sr1 = df0.count()
sr2 = df0.sum()
sr3 = df0.mean()
sr4 = df0.min()
sr5 = df0.max()

参考