pandasを用いた集計表の作成

前提・実現したいこと

データが膨大なため、pandasを用いて、加工前データをもとに理想図のような集計表を作りたいです。また、その集計表を用いて、基礎統計量を計算したいです。
実際のデータは行数が７５０万件あり、Excelでは対応しきれないため、pandasで実現したいと考えています。

具体的には、加工前のカラム（ヘッダ）にある"状態"を、インデックス側に持ってくることで、理想図のような表を作り、集計したいです。（データ自体はダミーですが、レイアウトが同じです。）

車の区分とは、車の大きさによって割り当てられたコードです。大きさでの割り振りなので、重複があります。
各状態は、車の故障の種類です。数字は、故障の重度レベルです（数字が大きいほど重篤な故障）。
社名は、修理に使った修理会社の名前です。数字は、サービスの利用回数です。

【加工前】

【理想図】

上記のような表を作成するためには、pandasでどのような操作を行えばよいか、ご教示いただけると幸いです。
恥ずかしながらpython,pandasはズブの素人で、自分の分かる範囲でcrosstabメソッドなどを試しましたが、ほしい表が出力されません。
必ずしも一発で上の表を出す必要はなく、別個に計算してマージなどでも構いません。
最終的に理想図の表が完成すればよい、というイメージです。

発生している問題・エラーメッセージ

いまのところ、"状態"と"社名"を表す上位のヘッダをもう一つ追加して（マルチカラム）、stackメソッドを行えばよいかと思っていますが、手元で再現できていません。
pythonの問題というより、統計の浅学が影響しているかもしれませんが、上記のような表の作成について、解決方法がありましたらご教示いただけると幸いです。

エラーメッセージ
特にございません。

該当のソースコード

ソースコード
特にございません。

試したこと

stackメソッドによる列から行への移動。全体が行側にピボットしてしまいました。
マルチカラムに加工してからstackする。マルチカラムに加工する時点で、手間取っています。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。![イメージ説明]

meg_

2020/10/14 22:39

どのぐらいの量のデータか分かりませんが、Excelのピボットテーブル機能である程度実現出来ませんか？

KroYuta

2020/10/15 00:18 編集

ご質問ありがとうございます。実際のデータは行数が750万件あり、EXCELのピボットテーブルでは読み込みきらず、対処しきれない量となってしまっています。 pandasでEXCELのピボットテーブルを模倣できればと考えています。

meg_

2020/10/15 00:20

＞実際のデータは行数が750万件ありそうなんですね。その情報も質問に追記した方が良いと思います。

KroYuta

2020/10/15 00:28

ありがとうございます。質問文にも追記させていただきました。

toast-uz

2020/10/15 03:51

理想図のインデックスは3階層にした方が、加工前の情報を使いやすいです。状態1:パンク　と　0 で別階層にするイメージです。

KroYuta

2020/10/17 10:20

ありがとうございます。故障内容とレベルを別立てにする方が作りやすいのですね。参考になります。

行動規範の内容に同意します

回答1件

以下の手順で、ご希望の形は作ることはできましたが、750万行でちゃんと動くかはわかりません。

各ステップで、データフレームがどういう形になっているか確認すれば、何をやっているかわかると思います。

python
1from io import StringIO
2import pandas as pd
3
4csv = StringIO('''\
5シリアルナンバー,車の区分コード,状態1:パンク,状態2:ライト切れ,状態3:エンジン停止,状態4:オイル漏れ,A社,B社,C社
61,10,1,0,4,2,3,1,3
72,21,3,0,0,3,1,4,2
83,20,4,1,0,4,1,0,4
94,22,0,0,3,0,1,0,0
105,11,4,3,0,2,2,4,1
116,11,2,3,1,2,2,4,1
127,21,0,4,0,1,0,2,2
138,30,0,2,2,3,0,3,0
149,22,1,3,4,3,0,2,1
1510,10,1,2,3,1,4,3,2
1611,20,1,0,4,0,3,0,0
1712,21,1,2,1,0,1,0,3
18''')
19df = pd.read_csv(csv, index_col=0)

python
1# 状態1～4を縦持ちに変換 (それぞれ個別で集計するため)
2df2 = df.melt(id_vars=['車の区分コード', 'A社', 'B社', 'C社'], value_vars=['状態1:パンク', '状態2:ライト切れ', '状態3:エンジン停止', '状態4:オイル漏れ'])
3
4# 縦持ちにしたものを一つの列に結合
5df2['状態'] = df2.variable + df2.value.astype(str)
6
7# 集計実行
8df3 = df2.groupby(['車の区分コード', '状態'])[['A社', 'B社', 'C社']].sum()
9
10# 合計、平均、偏差、中央値の計算
11total = df3.sum(axis=1).rename('合計')
12average = df3.mean(axis=1).rename('利用者平均')
13stdev = df3.std(axis=1, ddof=0).rename('利用者標準偏差')
14median = df3.median(axis=1).rename('利用者中央値')
15
16# 一つのデータフレームに結合
17df4 = pd.concat([total, df3, average, stdev, median], axis=1)
18
19# インデックスをすべての組み合わせにする
20new_index = pd.MultiIndex.from_product(df4.index.levels)
21df5 = df4.reindex(new_index, fill_value=0)