4. Python のデータフレーム，集計・集約，ソート

【概要】

この資料では，Python によるデータ処理を実習する。MySQL Employees Sample Database を SQLite3 形式に変換したデータをサンプルデータセットとして使用する。学習する操作は，データベースのテーブルをデータフレームに読み込むこと，データフレームの内容を確認すること，集計・集約を行うこと，ソートを行うこと，頻度分布を求めることである。

本資料のコードは，Windows 上の Python でも Google Colaboratory（以下 Colab）でも動作する。Colab で実行する場合，ファイルはセッションのカレントディレクトリ（既定では /content）に保存される。

【目次】

• 1. Python の pandas パッケージ
• 2. Python のデータフレームの例
• 3. SQLite の GLOB
• 4. 演習準備
• 5. Employees Sample データベース（SQLite版）について
• 6. 演習1．Employees Sample データベースのダウンロード
• 7. 演習2．Employees Sample データベースの確認
• 8. 演習3．テーブルをデータフレームに読み込む
• 9. 演習4．データフレームの確認と分析

【関連する外部ページ】

• bytebase/employee-sample-database
• MySQL Employees Sample Database

【サイト内の関連ページ】

• データベース処理とデータ分析：SQL・Python・機械学習による演習のページ
• Windows環境でのPython：基本とプログラムの作成・実行
• データベース用語集のページ

1. Python の pandas パッケージ

Python の pandas パッケージは，以下の機能を持つ。

• データフレーム（DataFrame）と呼ばれる表形式データの処理
• 時系列データの処理
• 集計・集約
• CSV ファイルや Excel ファイルなどの読み書き
• リレーショナルデータベースとの連携

本資料では，このうちデータフレームへの読み込み，集計・集約，ソートを扱う。

2. Python のデータフレームの例

以下は dept_emp テーブルから読み込んだデータフレームの例である。emp_no は従業員番号，dept_no は部署番号，from_date と to_date はその部署への所属期間（開始日と終了日）を表す。to_date が 9999-01-01 の行は，現在もその部署に所属していることを示す（終了日が未定であることを表すための慣例的な値）。

3. SQLite の GLOB

SQLite の GLOB は，文字列のパターンマッチを行う演算子であり，大文字・小文字を区別する。パターンに使用する記号は以下のとおりである。

• * — 任意の長さの文字列（0文字以上）と一致する
• ? — 任意の1文字と一致する
• [a-z] — a, b, …, z のいずれか1文字と一致する

以下の SQL 文は，テーブル R の列 A の値に文字列 9999 を含む行を取得する。

SELECT * FROM R WHERE A GLOB '*9999*'

4. 演習準備

エディタで Python プログラムを作成し，コマンドプロンプトで python ファイル名.py により実行する。やり方が分からない場合は，先に「Windows環境でのPython：基本とプログラムの作成・実行」を参照すること。

この資料では Python を使用する。Python がインストールされていない場合は，下記の「Python 3.12 のインストール」を展開し，手順に従いインストールすること。続いて「必要なライブラリのインストール」を実施すること。

Python 3.12 のインストール

Pythonのインストールを行い、Pythonのプログラムを実行する環境を整える。扱う環境は、Windows搭載パソコンである。金子研究室では、Python 3.12.10を推奨する。

REM Python 3.12 をシステム領域にインストール
winget install --id Python.Python.3.12 -e --scope machine --silent --accept-source-agreements --accept-package-agreements --override "/quiet InstallAllUsers=1 PrependPath=1 Include_test=0 Include_pip=1 Include_launcher=1 InstallLauncherAllUsers=1 TargetDir=\"C:\Program Files\Python312\""

REM Python と Scripts を PATH 先頭に追加
powershell -NoProfile -Command "$p='C:\Program Files\Python312'; $s=\"$p\Scripts\"; $c=[Environment]::GetEnvironmentVariable('Path','Machine'); if((Test-Path $p) -and (';'+$c+';' -notlike \"*;$p;*\") -and (';'+$c+';' -notlike \"*;$s;*\")){[Environment]::SetEnvironmentVariable('Path',\"$p;$s;$c\",'Machine')}"

python --version

必要なライブラリのインストール

管理者権限でコマンドプロンプトを起動する （手順：Windowsキーまたはスタートメニュー → cmd と入力 → 右クリック → 「管理者として実行」）。

起動したコマンドプロンプトで以下を実行する。--no-user はユーザーディレクトリへのインストールを抑止し，システム全体（全ユーザー向け）にインストールするためのオプションである。Colab で実行する場合は --no-user を付けず，ノートブックのセル先頭に ! を付けて !pip install -U pandas matplotlib として実行する（Colab には標準でインストール済みのため通常は不要）。

pip install -U --no-user pandas matplotlib

5. Employees Sample データベース（SQLite版）について

このデータベースは，MySQL Employees Sample Database を SQLite 形式に変換したものであり，GitHub 上の bytebase/employee-sample-database リポジトリで公開されている。出典と著作権表示は以下のとおりである。

データの出典: MySQL Employees Sample Database（原著者: Fusheng Wang, Carlo Zaniolo。リレーショナルスキーマ: Giuseppe Maxia。データ変換: Patrick Crews）

This work is licensed under the Creative Commons Attribution-Share Alike 3.0 Unported License. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to Creative Commons, 171 Second Street, Suite 300, San Francisco, California, 94105, USA.

SQLite adaptation: bytebase/employee-sample-database (MIT License)

6. 演習1．Employees Sample データベースのダウンロード

手順

① 次のコードを実行（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）する。データベースファイル employee.db がカレントディレクトリにダウンロードされる。Colab で実行する場合は，このコード本体をそのままセルに貼り付けて実行する。

import urllib.request
import os

urllib.request.urlretrieve(
    'https://raw.githubusercontent.com/bytebase/employee-sample-database/main/sqlite/dataset_small/employee.db',
    'employee.db')

print('exists:', os.path.exists('employee.db'))
print('size:', os.path.getsize('employee.db'))

ヒント

• urllib.request.urlretrieve(URL, ファイル名) は，第1引数の URL からファイルを取得し，第2引数のファイル名で保存する。
• os.path.exists はファイルの有無を，os.path.getsize はファイルサイズ（バイト単位）を返す。
• カレントディレクトリ（現在の作業ディレクトリ）は，Windows ではコマンドプロンプトのプロンプト表示で，Colab では既定で /content である。

考察ポイント

• 表示された exists が True，size が正の値であることを確認する。

7. 演習2．Employees Sample データベースの確認

手順

① 次のコードを実行（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）する。このプログラムは，SQLite3 データベース employee.db に接続し，department テーブルの全行を取得して表示する。

import sqlite3

c = sqlite3.connect('employee.db')

result = c.execute("SELECT * FROM department").fetchall()
for row in result:
    print(row)

c.close()

② 次のコードを実行（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）する。このプログラムは，各テーブルの列情報を表示する。PRAGMA table_info(テーブル名) は，指定したテーブルの列名，データ型，主キーの有無などの構造情報を返す SQLite のコマンドである。

import sqlite3

c = sqlite3.connect('employee.db')

for table_name in ['department', 'dept_manager', 'dept_emp', 'employee', 'title', 'salary']:
    print(table_name)
    result = c.execute(f"PRAGMA table_info({table_name})").fetchall()
    for row in result:
        print(row)
    print()

c.close()

ヒント

• sqlite3.connect(ファイル名) は，SQLite データベースに接続し，接続オブジェクトを返す。
• execute(SQL文).fetchall() は，SQL 文を実行し，結果を全件リストとして返す。
• PRAGMA table_info の結果は，(列番号, 列名, データ型, NOT NULL 制約, デフォルト値, 主キーの有無) の形式で返される。最後の値が 1 であれば主キー，0 であれば主キーではない。

考察ポイント

• department テーブルにはどのような列が含まれ，何件の行があるか。
• 各テーブルの主キーはどの列か。
• テーブル間で共通する列名（例：emp_no，dept_no）はどれか。これらはテーブルの結合に使用できる。

8. 演習3．テーブルをデータフレームに読み込む

手順

① 次のコードを実行（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）する。このプログラムは，dept_emp テーブルの全行をデータフレーム df に読み込み，先頭の数行を表示する。

import sqlite3
import pandas

c = sqlite3.connect('employee.db')

df = pandas.read_sql("SELECT * FROM dept_emp", c)
print(df.head())

c.close()

ヒント

• pandas.read_sql(SQL文, 接続オブジェクト) は，SQL の実行結果をデータフレームとして返す。
• df.head() は，データフレームの先頭5行を返す。

考察ポイント

• データフレームの列名と表示内容が，dept_emp テーブルの構造（演習2で確認）と一致しているか。

9. 演習4．データフレームの確認と分析

以下の演習では，dept_emp テーブルを読み込んだデータフレーム df に対し，確認・集計・ソート・集約を行う。各演習のコードは単体で実行できる。

演習4-1．データフレームの確認

手順

① 次のコードを実行（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）する。describe(include='all') は，全列について基本統計量を計算する。

import sqlite3
import pandas

c = sqlite3.connect('employee.db')

df = pandas.read_sql("SELECT * FROM dept_emp", c)
print(df.describe(include='all'))

c.close()

ヒント

文字列型の列に対しては，以下の項目が表示される。

• count — 行数
• unique — 異なる値の数
• top — 最頻値
• freq — 最頻値の出現回数

数値型の列に対しては，以下の項目が表示される。

• count — 行数
• mean — 平均値
• std — 標準偏差
• min — 最小値
• 25% — 第1四分位数
• 50% — 中央値（第2四分位数）
• 75% — 第3四分位数
• max — 最大値

考察ポイント

• 各列のデータ型（文字列型か数値型か）と，表示される統計項目の対応を確認する。
• dept_no 列の unique はいくつか。これは何を意味するか。
• top と freq から，最も多くの従業員が所属する部署を読み取る。

演習4-2．集計

手順

① 次のコードを実行（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）する。

import sqlite3
import pandas

c = sqlite3.connect('employee.db')

df = pandas.read_sql("SELECT * FROM dept_emp", c)
print(df.count())
print(df['dept_no'].value_counts())

c.close()

ヒント

• df.count() は，各列の非欠損値の個数を返す。
• df['列名'].value_counts() は，指定した列の値ごとの出現回数を，多い順に並べて返す。

考察ポイント

• df.count() の結果から，全列で行数が一致しているか確認する（一致しない場合は欠損値が存在する）。
• value_counts() の結果から，最も従業員数が多い部署と最も少ない部署を読み取る。

演習4-3．ソート

手順

① 次のコードを実行（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）する。

import sqlite3
import pandas

c = sqlite3.connect('employee.db')

df = pandas.read_sql("SELECT * FROM dept_emp", c)

print("昇順:")
print(df.sort_values(by='emp_no', ascending=True).head())

print("降順:")
print(df.sort_values(by='emp_no', ascending=False).head())

print("複数列によるソート:")
print(df.sort_values(by=['dept_no', 'emp_no'], ascending=[True, True]).head())

c.close()

ヒント

• sort_values(by='列名', ascending=True) で昇順，ascending=False で降順にソートする。
• 複数列でソートする場合は，by と ascending にリストを渡す。リストの先頭の列が最優先のキーとなる。
• sort_values はソート結果を新しいデータフレームとして返し，元の df は変更しない。

考察ポイント

• 複数列ソートの結果で，dept_no が同じ行の中で emp_no が昇順になっているか確認する。

演習4-4．頻度分布

手順

① 次のコードを実行（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）する。

import sqlite3
import pandas

c = sqlite3.connect('employee.db')

df = pandas.read_sql("SELECT * FROM dept_emp", c)
print(df['dept_no'].value_counts())

c.close()

ヒント

• value_counts() の出力は，各値とその出現回数の対応であり，頻度分布に相当する。

考察ポイント

• 演習4-2 で表示した value_counts() の結果と同じ結果になることを確認する。
• 頻度の合計が，全体の行数（df.count() の結果）と一致するか確認する。

演習4-5．集約

手順

① 次のコードを実行（メモ帳を用いる場合は a.py のようなファイル名で保存して実行）する。groupby() は指定した列の値が同じ行をグループにまとめる。続けて集約関数（count() など）を呼ぶことで，グループごとの集計値を得る。

import sqlite3
import pandas

c = sqlite3.connect('employee.db')

df = pandas.read_sql("SELECT * FROM dept_emp", c)

print(df.groupby('dept_no').count())
print(df.groupby('dept_no')['emp_no'].count())

c.close()

ヒント

• df.groupby('列名').count() は，グループごとに各列の非欠損値の個数を返す。
• df.groupby('列名')['対象列'].count() は，指定した1列のみについて集計する。

考察ポイント

• groupby('dept_no').count() の結果と，演習4-2 の df['dept_no'].value_counts() の結果を比較する。両者が示す情報の対応関係を考察する。
• 2 つの groupby の出力形式の違い（データフレームか，1列分のみか）を確認する。

演習4-6．接続を閉じる

各演習のコードでは，最後に c.close() を実行してデータベース接続を閉じている。これは，使用したリソース（データベース接続）を解放するための処理である。