Pandasを用いた通話明細の集計

11696 ワード
pandas Python Jupyter-notebook 請求 電話 Python テキストリンク

会社の電話費用を部署別に集計したり、費用振り分けしたいというケースがあります。小規模な会社であればExcelを使うことが多いと思いますが、大規模になってくるとExcelだけでは管理しきれなくなります。その場合、課金装置を使うケースもありますが、導入・運用費用がかかってきます。

「金は掛けたくないけどExcelはちょっと限界」という方のために、Jupyter NotebookとPandasを使った通話明細の集計方法をまとめていきます。

事前準備

※通話明細をWebポータルからダウンロードできることを前提にしています。

事前に必要なファイルは以下の2つです。

  • 通話明細 - 電話会社のWebポータルなどからダウンロードします
  • 電話番号マスタ - 電話番号と利用部署が組になった表を予め用意しておきます

中身はCSV形式で保管してください。

通話明細の例
ご利用電話番号,月日,時刻,通話先電話番号,通話時間,通話種別,通話料
050-0000-1111,1月10日,09:00:00,090-4000-5000,01:00:00,国内,8
050-0000-1111,1月12日,09:00:00,090-4000-4999,01:00:00,国内,8
050-0000-1111,1月15日,09:00:00,1-111-0000-0000,01:00:00,国際,100
050-0000-1111,1月15日,10:00:00,090-4000-4997,00:30:00,携帯,16
050-0000-1111,1月15日,11:00:00,090-5000-0001,00:30:00,国内,8
050-0000-1111,1月21日,09:00:00,090-5000-0001,00:30:00,国内,8
050-0000-1111,1月21日,10:00:00,090-6000-0001,00:30:00,国内,8
050-0000-1111,1月21日,11:00:00,090-6000-0001,00:30:00,国内,8
050-0000-1111,1月21日,15:00:00,090-6000-0001,00:30:00,国内,8
050-0000-2222,1月10日,12:00:00,1-111-0000-0000,04:00:00,国際,400
050-0000-2222,1月11日,09:00:00,090-6000-0001,00:30:00,国内,8
050-0000-2222,1月21日,13:00:00,090-6000-0001,00:30:00,国内,8
050-0000-4444,1月21日,09:00:00,090-8000-0001,00:30:00,IP電話,8
050-0000-5555,1月15日,09:00:00,090-6000-0001,00:30:00,国内,8
050-0000-5555,1月16日,09:00:00,090-6000-0001,00:30:00,国内,8
050-0000-6666,1月25日,09:00:00,090-4000-4997,02:00:00,携帯,32
050-0000-6666,1月25日,11:00:00,090-4000-4997,01:30:00,携帯,32
050-0000-7777,1月28日,09:00:00,090-6000-0001,00:30:00,国内,8
050-0000-7777,1月28日,20:00:00,090-6000-0001,00:30:00,国内,8
050-0000-8888,1月5日,09:00:00,090-6000-0001,00:30:00,国内,8
050-0000-8888,1月6日,12:00:00,090-6000-0001,00:30:00,国内,8
電話番号マスタの例
ご利用電話番号,部門
050-0000-1111,総務部
050-0000-2222,営業部
050-0000-3333,営業部
050-0000-4444,営業部
050-0000-5555,ITシステム部
050-0000-6666,ITシステム部
050-0000-7777,製造部
050-0000-8888,製造部
050-0000-9999,総務部

Jupyter NotebookとPandasを用いた集計

通話明細の読み込み

まずはpandasを用いてCSV形式の通話明細を読み込みます（ファイルの拡張子はtxtになっていますが、中身はCSV形式です）

通話明細読み込み
import pandas as pd
df_meisai = pd.read_csv("通話明細.txt")
df_meisai

実行結果

割引通話料の算出

通話明細に表示される金額が請求金額とイコールではない時があります（例えば、通話明細は契約約款に従った金額で計算されるのに対して、実際の請求ではキャンペーンや個別契約などで割引が適用される場合）。ここでは、請求時に国内向け通話料金が10%割引となるとして、割引後の通話料を表示する列を新たに追加します。

割引通話料の算出
df_meisai['割引通話料'] = df_meisai['通話料']
df_meisai.loc[df_meisai.通話種別 == '国内', '割引通話料'] = df_meisai['通話料'] * 0.9
df_meisai

実行結果

電話番号ごとの通話料集計

pandasを用いると集計の操作を簡単に行うことができます。groupbyで電話番号ごとに割引通話料の合計値を求めていきます。

番号ごとの通話料の集計
df_bangou_tuuwaryou = df_meisai.groupby('ご利用電話番号')['割引通話料'].sum().reset_index()
df_bangou_tuuwaryou

groupbyを用いると列名の表示が乱れます。reset_index()を最後につけると、列名をうまいこと表示してくれます。

実行結果

電話番号ごとの基本料設定

ここまでは通話料の計算でしたが、そのほかに基本料がかかってきます。ここでは1番号ごとに300円の基本料がかかる想定とします。具体的には、pandasを用いて電話番号マスタを読み込み、基本料（300円）の列を追加します。

電話番号マスタ読み込み、基本料追加
df_bangou_master = pd.read_csv("電話番号マスタ.txt")
df_bangou_master['基本料'] = 300
df_bangou_master

実行結果

電話番号ごとの基本料＋通話料

電話番号ごとの基本料、通話料の表が揃いましたので、2つの表をマージして電話番号ごとの基本料＋通話料の表を作ります。

表のマージ
df_bangou_hiyou = pd.merge(df_bangou_master, df_bangou_tuuwaryou, how='outer').fillna(0)
df_bangou_hiyou

通話明細上では、通話実績がなかった電話番号が表示されていない可能性があります。inner joinだと情報が欠落する場合があるため、outer joinを使ってください（上記の場合はleft joinでもOK）。また、outer joinを用いると、通話実績のない番号の通話料がnullになりますので、fillna(0)でnullを0に書き換えます。

実行結果

部署ごとの基本料＋通話料

最後は部署ごとにgroupbyを用いるだけです。

部署ごとの費用集計
df_bangou_hiyou.groupby('部門')[['基本料','割引通話料']].sum().reset_index()

実行結果

まとめ

細かく見ていくと結構面倒なことを行っているように見えますが、ソースコードをまとめると以下のとおり大したことはやってません。

import pandas as pd

df_meisai = pd.read_csv("通話明細.txt")
df_meisai['割引通話料'] = df_meisai['通話料']
df_meisai.loc[df_meisai.通話種別 == '国内', '割引通話料'] = df_meisai['通話料'] * 0.9

df_bangou_tuuwaryou = df_meisai.groupby('ご利用電話番号')['割引通話料'].sum().reset_index()

df_bangou_master = pd.read_csv("電話番号マスタ.txt")
df_bangou_master['基本料'] = 300

df_bangou_hiyou = pd.merge(df_bangou_master, df_bangou_tuuwaryou, how='outer').fillna(0)
df_bangou_hiyou.groupby('部門')[['基本料','割引通話料']].sum().reset_index()

これだけだとExcelに対するメリットが見えにくいですが、契約番号数が膨大であればExcelよりもずっと早くミスなく計算できます。また、Web上から閲覧できたり他のシステムと連携できるという点がメリットだと思います。