課題
AccessのSQLではグループごとにソートする機能(ランク付け)が備わっていない。
Pythonの groupby() と rank() を活用し、AccessのDBに接続することで解決する。
現状
積上げ式のDBに下記のようなデータが入っているとする。
(履歴を残すために、1つのIDに対して複数データを積み上げる仕様になっている。)
顧客ID | 顧客名 | 開発担当 | 進捗状況 | 更新日 |
---|---|---|---|---|
1 | A会社 | 青木 | 1.コーディング | 2024/04/04 |
1 | A会社 | 井上 | 2.テスト | 2024/05/05 |
1 | A会社 | 上野 | 3.リリース | 2024/06/06 |
2 | B会社 | 青木 | 1.コーディング | 2024/05/05 |
2 | B会社 | 伊藤 | 2.テスト | 2024/07/07 |
3 | C会社 | 相川 | 1.コーディング | 2024/06/06 |
目標
1つのIDに対して最新データのみを抽出したい。
キーは「顧客ID」、最新データは「更新日」で判別する。
顧客ID | 顧客名 | 開発担当 | 進捗状況 | 更新日 |
---|---|---|---|---|
1 | A会社 | 上野 | 3.リリース | 2024/06/06 |
2 | B会社 | 伊藤 | 2.テスト | 2024/07/07 |
3 | C会社 | 相川 | 1.コーディング | 2024/06/06 |
作成したコード
Python
import pyodbc
import pandas as pd
# Accessデータベースに接続
con = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:/Python/testDB.accdb;')
sql = 'SELECT * FROM table_A'
df = pd.read_sql(sql, con)
# キー項目でグループ化
# 更新日順でランク付け
df['ランク'] = df.groupby(['顧客ID'])['更新日'].rank(ascending=False)
# ランク=1だけ抽出
df = df.query('ランク == 1')
# 結果を表示
print(df)
コード解説(抜粋)
# キー項目でグループ化
# 更新日順でランク付け
df['ランク'] = df.groupby(['顧客ID'])['更新日'].rank(ascending=False)
df['ランク']
DataFrameに項目「ランク」として追加。
df.groupby(['顧客ID'])
「顧客ID」でグループ化。
['更新日'].rank()
グループ化したものを「更新日」でランク付け。
(ascendin=False)
降順にランク付け。つまり「更新日」が大きいものから順に「ランク=1」になる。
# ランク=1だけ抽出
df = df.query('ランク == 1')
上で加工したデータのうち「ランク=1」のみを抽出。
つまり、グループごとに「更新日」が最新のデータを取得できた。