そもそもピボットテーブルとは
ピボットテーブルは、クロス集計表のことらしいです。
[クロス集計表]
2つのカテゴリのデータを同時に集計したもの。
例えば、売上管理表の「氏名」と「商品分類」のカテゴリをもとに、売上を合計したもの。
クロス集計表のことをエクセルやPandasではピボットテーブルといいます。。
ピボットとは軸という意味で、テーブルは表という意味です。
とりあえずは、いつも通りpandasをインポート、表示領域の設定、
エクセルファイルの読み込みをします。
import pandas as pd
pd.options.display.max_rows = 10
pd.options.display.max_columns = None
df = pd.read_excel('sample.xlsx',sheet_name='実績管理表')
df
売上日 社員ID 氏名 性別 商品分類 商品名 単価 数量 売上金額
0 2020-01-04 a023 河野 利香 女 ボトムス ロングパンツ 7000 8 56000
1 2020-01-05 a003 石崎 和香菜 女 ボトムス ジーンズ 6000 10 60000
2 2020-01-05 a052 井上 真 女 アウター ジャケット 10000 7 70000
3 2020-01-06 a003 石崎 和香菜 女 ボトムス ロングパンツ 7000 10 70000
4 2020-01-07 a036 西尾 謙 男 ボトムス ロングパンツ 7000 2 14000
... ... ... ... ... ... ... ... ... ...
219 2020-12-26 a052 井上 真 女 アウター ダウン 18000 4 72000
220 2020-12-28 a036 西尾 謙 男 アウター ダウン 18000 3 54000
221 2020-12-30 a003 石崎 和香菜 女 アウター ダウン 18000 4 72000
222 2020-12-30 a047 上瀬 由和 男 ボトムス ハーフパンツ 3000 3 9000
223 2020-12-31 a036 西尾 謙 男 ボトムス ロングパンツ 7000 10 70000
224 rows × 9 columnsピボットテーブルの書き方
df_pivot = df.pivot_table(index='氏名', columns='商品分類',values='売上金額', aggfunc='sum')
df_pivot
商品分類 アウター トップス ボトムス
氏名
上瀬 由和 636000 296000 416000
井上 真 560000 200000 176000
宮瀬 尚紀 464000 340000 883000
河野 利香 918000 424000 458000
石崎 和香菜 1022000 564000 883000
西尾 謙 786000 292000 361000氏名と商品分類で売上金額の合計を算出します。
引数indexに氏名、coluumnsに商品分類、
valuesに集計するデータの売上金額を指定します。
そしてaggfuncという引数が集計方法です。sumを渡して合計を算出します。
これでクロス集計表、ピボットテーブルができあがりました。
平均算出
sum合計 の部分を mean平均 にかえると平均を算出できます。
df_pivot = df.pivot_table(index='氏名', columns='商品分類',values='売上金額', aggfunc='mean')
df_pivot小数点以下を省略
前回の講義でも出てきた小数点以下を省略する方法です。
こういうコードがあるだということで、丸呑みします(笑)
df_pivot = df.pivot_table(index='氏名', columns='商品分類',values='売上金額')
df_pivot.applymap('{:,.0f}'.format)
商品分類 アウター トップス ボトムス
氏名
上瀬 由和 106,000 42,286 29,714
井上 真 70,000 25,000 22,000
宮瀬 尚紀 77,333 42,500 35,320
河野 利香 76,500 35,333 26,941
石崎 和香菜 63,875 37,600 31,536
西尾 謙 78,600 26,545 27,769複数データの集計
複数のデータの平均を同時に算出することもできます。
引数valuesに、集計したいデータをリストで渡します。
df_pivot = df.pivot_table(index='氏名', columns='商品分類',values=['単価','数量','売上金額'], aggfunc='mean')
df_pivot.applymap('{:,.0f}'.format)
商品分類 アウター トップス ボトムス アウター トップス ボトムス アウター トップス ボトムス
氏名
上瀬 由和 14,000 6,286 5,429 106,000 42,286 29,714 8 7 6
井上 真 13,000 4,500 4,875 70,000 25,000 22,000 6 5 4
宮瀬 尚紀 12,667 6,500 5,840 77,333 42,500 35,320 6 6 6
河野 利香 14,000 6,333 5,529 76,500 35,333 26,941 5 5 5
石崎 和香菜 13,500 6,667 5,607 63,875 37,600 31,536 5 5 5
西尾 謙 14,800 5,455 5,231 78,600 26,545 27,769 5 5 5indexやcolumnsに複数のカテゴリを設定することができます。
indexにリストで渡します。
df_pivot = df.pivot_table(index=['氏名','売上日'], columns='商品分類',values='売上金額', aggfunc='sum')
df_pivot.applymap('{:,.0f}'.format)
商品分類 アウター トップス ボトムス
氏名 売上日
上瀬 由和 2020-01-10 126,000 nan nan
2020-01-11 nan nan 7,000
2020-01-22 nan 4,000 nan
2020-02-03 144,000 nan nan
2020-02-07 nan nan 27,000
... ... ... ... ...
西尾 謙 2020-11-10 10,000 nan nan
2020-11-11 70,000 nan nan
2020-12-22 nan 24,000 nan
2020-12-28 54,000 nan nan
2020-12-31 nan nan 70,000
212 rows × 3 columnsNan置き換え
nanの部分がそのままですと、データ上見栄えが悪いので
0に変えます。
df_pivot = df.pivot_table(index=['氏名','売上日'], columns='商品分類v,values='売上金額', aggfunc='sum',fill_value=0)
df_pivot.applymap('{:,.0f}'.format)
商品分類 アウター トップス ボトムス
氏名 売上日
上瀬 由和 2020-01-10 126,000 0 0
2020-01-11 0 0 7,000
2020-01-22 0 4,000 0
2020-02-03 144,000 0 0
2020-02-07 0 0 27,000
... ... ... ... ...
西尾 謙 2020-11-10 10,000 0 0
2020-11-11 70,000 0 0
2020-12-22 0 24,000 0
2020-12-28 54,000 0 0
2020-12-31 0 0 70,000
212 rows × 3 columns合計列追加
この機能は実務で結構役立ちそうです。
合計列を追加します。
df_pivot = df.pivot_table(index='氏名', columns='商品分類',values='売上金額', aggfunc='sum', margins=True)
df_pivot.applymap('{:,.0f}'.format)
商品分類 アウター トップス ボトムス All
氏名
上瀬 由和 636,000 296,000 416,000 1,348,000
井上 真 560,000 200,000 176,000 936,000
宮瀬 尚紀 464,000 340,000 883,000 1,687,000
河野 利香 918,000 424,000 458,000 1,800,000
石崎 和香菜 1,022,000 564,000 883,000 2,469,000
西尾 謙 786,000 292,000 361,000 1,439,000
All 4,386,000 2,116,000 3,177,000 9,679,000合計列に名前をつけることもできます。
df_pivot = df.pivot_table(index='氏名', columns='商品分類',values='売上金額', aggfunc='sum', margins=True, margins_name='合計')
df_pivot.applymap('{:,.0f}'.format)
商品分類 アウター トップス ボトムス 合計
氏名
上瀬 由和 636,000 296,000 416,000 1,348,000
井上 真 560,000 200,000 176,000 936,000
宮瀬 尚紀 464,000 340,000 883,000 1,687,000
河野 利香 918,000 424,000 458,000 1,800,000
石崎 和香菜 1,022,000 564,000 883,000 2,469,000
西尾 謙 786,000 292,000 361,000 1,439,000
合計 4,386,000 2,116,000 3,177,000 9,679,000複数の集計方法
aggfunにリストで渡すと複数の集計を行うこともできます。
df_pivot = df.pivot_table(index='氏名', columns='商品分類',values='売上金額', aggfunc=['sum','mean','count'])
df_pivot.applymap('{:,.0f}'.format)
sum mean count
商品分類 アウター トップス ボトムス アウター トップス ボトムス アウター トップス ボトムス
氏名
上瀬 由和 636,000 296,000 416,000 106,000 42,286 29,714 6 7 14
井上 真 560,000 200,000 176,000 70,000 25,000 22,000 8 8 8
宮瀬 尚紀 464,000 340,000 883,000 77,333 42,500 35,320 6 8 25
河野 利香 918,000 424,000 458,000 76,500 35,333 26,941 12 12 17
石崎 和香菜 1,022,000 564,000 883,000 63,875 37,600 31,536 16 15 28
西尾 謙 786,000 292,000 361,000 78,600 26,545 27,769 10 11 13
独自の集計方法
前回同様、defで定義した独自の関数も利用可能です。
消費税を掛ける計算を入れます。
import numpy as np
def cal_tax(s):
return np.sum(s)*1.10
df_pivot = df.pivot_table(index='氏名', columns='商品分類',values='売上金額', aggfunc=cal_tax)
df_pivot.applymap('{:,.0f}'.format)pivotメソッドの使い方
pivotにはメソッドもあります。
説明するために簡単な表を作成します。
df = pd.DataFrame({'col01':['A','A','B','B'], 'col02':['a','b','a','b'],'col03':[1, 2, 3, 4]})
df
col01 col02 col03
0 A a 1
1 A b 2
2 B a 3
3 B b 4indexとcolumnsとvaluesにそれぞれカラム名を渡しましょう。
df_pivot = df.pivot(index='col01', columns='col02',values='col03')
df_pivot
col02 a b
col01
A 1 2
B 3 4
コメント