SQLiteを使ってCSVファイルを集約して効率よくグラフを作成する
公開日:2024/10/21
はじめに
日々のログデータを保存したファイルがあります。このファイルの各行には「ログの種類」と1時間ごとのログ件数が記録されており、ファイルは1日ごとに出力されています（例: 2024-09-01.csv）。
具体例として、ウェブサイトのアクセスログを集約した以下のようなCSVファイルが考えられます。このファイルでは、categoryにログの種類（200のレスポンスや、拡張子.htmlのファイルへのアクセス数など）が記録されており、各時間帯にどれだけアクセスがあったかが表示されています。
category,00,01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23
response:200,120,98,110,105,115,95,130,145,160,155,140,135,150,165,170,180,175,190,200,205,195,135,145
response:404,15,20,17,10,8,9,7,6,5,12,14,10,11,15,13,17,18,20,22,18,14,10,8
response:500,5,3,4,2,1,0,0,1,2,1,3,4,3,2,5,6,8,7,6,4,3,2,1
ext:html,100,85,90,95,100,80,115,120,130,125,110,105,110,120,130,135,140,145,150,160,165,155,110,120
ext:jpg,30,25,28,30,25,20,30,35,40,45,40,35,40,45,50,55,60,65,70,80,75,70,60,65
ext:css,10,8,9,5,4,3,2,5,5,6,7,6,5,4,4,6,7,8,9,10,8,7,5,4
…
様々なデータが混在しているため、特定の視点から視覚化を行い、現状を分析するニーズがあります。また、これは1日分のデータに過ぎないため、適切な範囲でデータを集約・視覚化する必要があることが一般的です。
本記事では、こうしたデータを視覚化する際、単にスクリプトを記述するだけでなく、SQLite（オンメモリデータベース）を利用することで、より効率的にデータ処理を行う方法について説明します。
動的なSQLiteデータベースの作成
SQLiteを利用すると、メモリ上にRDB（リレーショナルデータベース）を構築することができます。CSVファイルが大きい場合には、データベースをファイルとして保存することも検討すべきですが、ファイルサイズが十分に小さい場合は、メモリ上にデータベースを作成することで処理を高速化できます。
例えば、日付ごとに異なるCSVファイルを動的に読み込み、SQLiteデータベースを構築することで、SQLを利用した柔軟な集計処理が可能となります。具体的には、GROUP BYやSUM、AVG（AVERAGE）を用いてデータを集約したり、WHERE句を使って対象データを絞り込むことができます。
MatplotlibとNumPyを利用したグラフの作成
今回は python で SQL集計を行ったデータを利用してグラフを作成します。 この時、グラフの作成には matplotlib を利用します。 通常、グラフの各項目を表示するためにはデータのリストを準備する必要がありますが、SQLを使用することで、その大半の値を自動的に生成できます。SQLを利用しない場合は、CSVファイルから直接データを読み取り、手作業で集約を行う必要があり、これに伴うプログラムが複雑化する傾向にあります。
簡単なグラフの場合はこれだけで良いのですが、複雑なグラフを描画する場合はSQLで得られたデータを別途計算する必要があります。 例えば、積み上げ棒グラフのような場合です。
積み上げデータが2つ程度であれば問題ありませんが、データ数が3つ以上の場合、例えば以下のようにNumPyを利用して積み上げデータを計算し、利用することができます。
# r200, r404, r500 = [], [], []
plt.bar(x, r500, label=’500’, color=’orange’, bottom=np.array(r200) + np.array(r404))
plt.bar(x, r404, label=’404′, color=’red’, bottom=r200)
plt.bar(x, r200, label=’200′, color=’green’)
このように、
- データの集計はデータベースおよびSQLを利用することで簡素化・効率化し、
- 集計済みデータの加工（スケール調整、データセット間の加算など）はプログラムを使うことで柔軟に対応できます。
これにより、効率よくグラフ画像を生成し、視覚的に情報を表現することが可能です。
まとめ
本記事では、CSVファイルのデータを効率よく集約し、グラフを作成するために、SQLiteとPython（Matplotlib、NumPy）を組み合わせて活用する方法を紹介しました。データの集計はSQLで行い、データの加工はプログラム（NumPyやMatplotlib）で処理するという役割分担により、プログラムの簡素化と効率化を実現できます。
SQLを実行するためには通常、各種ミドルウェアのインストールやセットアップなど大規模な準備が必要と考えがちですが、SQLiteを利用することで、プログラム内で完結してSQLを実行することが可能です。これにより、手軽にSQLの利便性を活かしたデータ処理ができ、効率的な分析や視覚化を実現できるため、データ処理の選択肢の1つとして活用する価値があります。
