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

aggメソッドで集計した例 DataFrame
この記事は約13分で読めます。

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

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

出力結果例

aggメソッドで集計した例

サンプルデータ

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]

集計(aggメソッド)

集計関数のみ指定

# A3セル
# カウント、合計、平均、最小値、最大値を集計する(配列で指定)
df1 = df0.agg(["count", "sum", "mean", "min", "max"])
# 数値型の列だけを出力する(コメントアウトしてもOK)
df1.select_dtypes(include="number")

結果

aggメソッドで集計した例
aggメソッドで各種集計関数を指定

列名と集計関数を指定

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

aggメソッドで集計した例
aggメソッドで列と集計関数を個別に指定

集計関数1つを指定

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

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

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

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

aggメソッドの例(DataFrame)
1つの集計関数名を配列で指定した例
aggメソッドの例(指定方法によってはSeriesが返る)
1つの集計関数名をスカラーで指定した例

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

Seriesのポップアップ表示
Seriesのポップアップ表示

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

Series.indexのポップアップ表示
Series.indexのポップアップ表示
Series.valuesのポップアップ表示
Series.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(デフォルト)の場合、グループラベルがインデックスとして返される。

groupbyメソッドの例
as_index=False の例
groupbyメソッドの例
as_index=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"]}) # エラーになる

結果 

groupbyメソッドの例
first, last がOKの例
groupbyメソッドの例
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 にすると降順となる

sort_valuesメソッドの例
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])

結果

sort_valuesメソッドの例
複数項目の並び替え

特定の順番で並べる(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))

結果

sort_valuesメソッドの例
引数keyにlambdaを指定

引数 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")

結果

sort_valuesメソッドの例
na_position=”last”なので「Medium」が最後に並ぶ
sort_valuesメソッドの例
na_position=”first”なので「Medium」が先頭に並ぶ

行・列の抽出(reindexメソッド)

列の抽出(columns)

# A111セル
df10 = df0.reindex(columns=["Units Sold", "Discounts", "Sales"])
df10.agg(["count", "sum", "mean", "min", "max"])

結果 SQL文のselect句的な?

reindex メソッドの例
引数columnsを指定して列を抽出
# 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句とはちょっと違う)

reindex メソッドの例
引数labelsを指定して行を抽出

ちょっとしたメモ

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()

参考

コメント

タイトルとURLをコピーしました