Python: MS Accessのデータベースをクロス集計してPython Pandasに取り込んでMatplotlibで線グラフを作成するには【Python】(Article006)

ようこそ「Python」へ...

Python»記事(Article006) ◀前の記事次の記事▶

MS Accessのデータベースをクロス集計してPython Pandasに取り込んでMatplotlibで線グラフを作成するには【Python】

ここではMicrosoft Accessのデータベースからクロス集計クエリで人口データを抽出してPython Pandasに取り込みます。そして、PandasのDataFrameに取り込んだ人口データからMatplotlibを使用して線グラフを描画します。さらに、PandasのDataFrameに取り込んだ人口データをExcel、CSV、TXTファイルに保存する方法についても解説します。 Accessのデータベースからデータを取り込むにはPyodbcとPandasを使用します。 Pandasのデータを線グラフで描画するにはMatplotlibを使用します。

Accessのデータベースには中国、インド、日本、アメリカなど10ヶ国の人口データが年度別に格納されています。クロス集計クエリでAccessからPandasのDataFrameに取り込んだあと、DataFrameから特定の国の人口データのみ処理する方法についても解説します。

ここでは、Pyodbc、Pandas、Maptplotlib、Numpy(オプション)等のライブラリを使用しますので「記事(Article001) | 記事(Article002) | 記事(Article003) | 記事(Article004)」を参照して事前にインストールしておいてください。 Pythonのコードを入力するときにMicrosoftのVisula Studio Codeを使用します。まだ、インストールしていないときは「記事(Article001)」を参照してインストールしておいてください。

説明文の左側に図の画像が表示されていますが縮小されています。画像を拡大するにはマウスを画像上に移動してクリックします。画像が拡大表示されます。拡大された画像を閉じるには右上の[X]をクリックします。画像の任意の場所をクリックして閉じることもできます。

Accessのクロス集計クエリを作成する

Accessを起動してデータベースを開く

図1

Accessを起動したら「Stats.accdb」データベースを開きします。そして「WorldPopulation」テーブルをダブルクリックして開きます。「WorldPopulation」テーブルには年度別の各国の人口が格納されています。このテーブルは「ID」、「Country」、「SurveyYear」、「Population」のフィールドから構成されています。
クロス集計ウィザードを起動する

図2

「WorldPopulation」テーブルを閉じたら、Accessの「作成」メニューから「クエリウィザード」をクリックします。「新しいクエリ」のウィザードが起動したら一覧から「クロス集計ウィザード」を選択して[OK]ボタンをクリックします。
「WorldPopulation」テーブルを選択

図3

「クロス集計クエリウィザード」から「WorldPopulation」テーブルを選択して[次へ]のボタンをクリックします。
行見出しとして「SurveyYear」フィールドを選択

図4

「クロス集計クエリウィザード(行見出し)」の「選択可能フィールド」から「SurveyYear」フィールドを選択して[次へ]のボタンをクリックします。
列見出しとして「Country」フィールドを選択

図5

「クロス集計クエリウィザード(列見出し)」の一覧から「Country」フィールドを選択して[次へ]のボタンをクリックします。
集計するフィールドとして「Population」を選択

図6

「クロス集計クエリウィザード(集計するフィールド)」のフィールド一覧から「Population」フィールドを選択します。「集計方法」の一覧から「合計」を選択したら[次へ]のボタンをクリックします。
クロス集計クエリ名を入力して保存

図7

「クロス集計クエリウィザード(クエリ名指定)」の「クエリ名」のテキストボックスにクロス集計クエリの名前「WorldPopulation_Crosstab」を入力したら[完了]ボタンをクリックします。
クロス集計クエリが表示された

図8

クロス集計クエリが表示されたら右下の「SQL」をクリックしてSQLコマンドを表示します。

SQLコマンドを編集して保存

クロス集計クエリのSQLコマンドが表示されたら上段のコマンドを下段のようなシンプルなコマンドに編集して保存します。

TRANSFORM Sum(WorldPopulation.[Population]) AS Populationの合計
SELECT WorldPopulation.[SurveyYear], Sum(WorldPopulation.[Population]) AS [合計 Population]
FROM WorldPopulation
GROUP BY WorldPopulation.[SurveyYear]
PIVOT WorldPopulation.[Country];
　　⇓
TRANSFORM Sum(Population) AS Population_Sum
SELECT SurveyYear, Sum(Population) AS Population_Total
FROM WorldPopulation
GROUP BY SurveyYear
PIVOT Country

図9はSQLコマンドを編集後の画面です。

AccessのWorldPopulationテーブルから人口データをPandasに取り込んで線グラフを描画する

PandasにAccessのレコードを取り込む
Visual Studio Codeを起動したら以下のコードを入力して実行します。行2-6ではPythonのライブラリを取り込んでいます。行9-12ではAccessの「Stats.accdb」データベースの接続文字列を定義しています。接続文字列にはバックスラッシュ「\」などの特殊文字が含まれる可能性があるのでrow文字列として定義しています。行14ではPyodbcのconnect()メソッドを実行してAccessのデータベースを接続(開く)しています。行15-21ではAccessの「WorldPopulation」テーブルからクロス集計するSQLコマンドを記述しています。今回使用するSQLコマンドのように長い文字列をPythonで記述するときは、文字列を３個のシングルクォーテーション「'''」またはダブルクォーテーション「"""」で囲みます。行22ではPandasのread_sql()メソッドを実行してAccessの「WorldPopulation」テーブルからレコードを抽出してDataFrameに取り込んでいます。行23ではPyodbcのclose()メソッドを実行してAccessのデータベースを切断(閉じる)しています。行24ではDataFrameに取り込んだ先頭３件のレコードを表示しています。
```
### Load the necessary libraries
import pyodbc as pyo
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.style as style
import numpy as np

### Imprting Access table into Pandas
con_str = (
	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
	r'DBQ=data/db/article006/Stats.accdb;'
	)

con = pyo.connect(con_str)
crosstab_sql = '''
TRANSFORM Sum(Population) AS Population_Sum
SELECT SurveyYear, Sum(Population) AS Population_Total
FROM WorldPopulation
GROUP BY SurveyYear
PIVOT Country
'''
df = pd.read_sql(crosstab_sql, con)
con.close()
print(df.head(3))
```
図10

図10は「print(df.head(3))」の実行結果です。PandasのDataFrameの先頭３件のレコードが表示されています。
Pandasのデータを線グラフで描く
行18では線グラフに表示する国名をリスト型で定義しています。ここでは中国、インド、日本、アメリカの４ヶ国の線グラフを描きます。行19-22のforループでは、DataFrameからすべての列名を取得して変数countryに格納しています。「for country in df」という記述は、「for column in df.columns」と同等です。後者のような記述が分かりやすいかもしれません。列名が「China」「India」「Japan」「United States」のいずれかのときは、Matplotlibのplot()メソッドを実行して線グラグを描画します。 X軸には「df.SurvyYear」を指定してDataFrameの全行の年度を値の対象とします。 Y軸には「df[country]」を指定して該当する国の全行の人口を値の対象とします。「df[country]」は「df['China']」、「df['India']」のように記述したのと同等になります。行24-26はコメントにしていますが行19-22と同等の処理を行います。ただし、DataFrameのすべての列名ではなく「China, India, Japan, United States」の列名のみcountryに格納されます。行28-30では線グラフのX軸、Y軸のラベルとタイトルを設定しています。行32では凡例を表示させます。行33はコメントにしていますが線グラフを保存します。行34では線グラフを画面に表示します。
```
### Imprting Access table into Pandas
con_str = (
	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
	r'DBQ=data/db/article006/Stats.accdb;'
	)
con = pyo.connect(con_str)
crosstab_sql = '''
TRANSFORM Sum(Population) AS Population_Sum
SELECT SurveyYear, Sum(Population) AS Population_Total
FROM WorldPopulation
GROUP BY SurveyYear
PIVOT Country
'''
df = pd.read_sql(crosstab_sql, con)
con.close()
print(df.head(3))

countries_to_look_at = ['China', 'India', 'Japan', 'United States']
for country in df:    
    #print(country)
    if country in countries_to_look_at:        
        plt.plot(df.SurveyYear, df[country], label=country, marker='.')

#for country in df[countries_to_look_at]:    
#    print(country)      
#    plt.plot(df.SurveyYear, df[country], label=country, marker='.')

plt.xlabel('Year')
plt.ylabel('Population')
plt.title('Population Line Graph')

plt.legend()
#plt.savefig('World_Population_figure.png')
plt.show()
```
図11

図11は実行結果です。線グラフには中国、インド、日本、アメリカのデータが線グラフで表示されています。
Accessから取り込んだPandasのデータをExcelファイルに保存する
行15でAccessの「WorldPopulation」テーブルをPandasのDataFrameに取り込んだら、そのデータをExcel、CSV、TXTファイルに保存しておくと次回から処理を高速化することができます。 PandasのデータをExcel、CSV、TXTファイルに保存するには、 Pandasのto_excel()、to_csv()メソッドを使用します。 Pandasのデータをタブ区切りのTXTファイルで保存するときも、to_csv()メソッドを使用しますが引数に「sep='\t'」を指定する必要があります。 PandasのDataFrameにはPandas独自の「index」フィールドが追加されます。デフォルトではこの「index」フィールドも保存されるので、不要なときは引数に「index=False」を指定します。
```
### Imprting Access table into Pandas
con_str = (
	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
	r'DBQ=data/db/article006/Stats.accdb;'
	)

con = pyo.connect(con_str)
crosstab_sql = '''
TRANSFORM Sum(Population) AS Population_Sum
SELECT SurveyYear, Sum(Population) AS Population_Total
FROM WorldPopulation
GROUP BY SurveyYear
PIVOT Country
'''
df = pd.read_sql(crosstab_sql, con)
con.close()

### Save pandas dataframe to csv, excel, text file
df.to_csv('data/csv/article006/WorldPopulation.csv', index=False)  
df.to_excel('data/excel/article006/WorldPopulation.xlsx', index=False)
df.to_csv('data/txt/article006/WorldPopulation.txt', index=False, sep='\t')
```
Excelファイルに保存したデータをPandasに取り込んで高速化する
行3では項番３で保存したExcelファイル「WorldPopulation.xlsx」を取り込んでPandasのDataFrameに格納しています。行7-9のforループでは、PandasのDataFrameから中国、インド、日本、アメリカの人口データを抽出して線グラフを描画しています。「for country in df[countries_to_look_at]」のように記述すると、forループのcountryには「China, India, Japan, Unites State」の列名のみ格納されます。その他の国の列名はforループの対象外になります。参考までに「for country in df」のように記述するとDataFrameのすべての列名がcountryに格納されます。つまり、DataFrameのすべての列名が対象になります。
```
### Read an excel file
excel_path = ('data/excel/article006/WorldPopulation.xlsx')
df = pd.read_excel(excel_path)

### Draw a line graph
countries_to_look_at = ['China', 'India', 'Japan', 'United States']
for country in df[countries_to_look_at]:    
    print(country)
    plt.plot(df.SurveyYear, df[country], label=country, marker='.')

plt.xlabel('Year')
plt.ylabel('Population')
plt.title('Population Line Graph')

plt.legend()
plt.savefig('World_Population_figure.png')
plt.show()
```

Go Top

Python {Article006}

MS Accessのデータベースをクロス集計してPython Pandasに取り込んでMatplotlibで線グラフを作成するには【Python】

Accessのクロス集計クエリを作成する

Accessを起動してデータベースを開く

クロス集計ウィザードを起動する

「WorldPopulation」テーブルを選択

行見出しとして「SurveyYear」フィールドを選択

列見出しとして「Country」フィールドを選択

集計するフィールドとして「Population」を選択

クロス集計クエリ名を入力して保存

クロス集計クエリが表示された

SQLコマンドを編集して保存

AccessのWorldPopulationテーブルから人口データをPandasに取り込んで線グラフを描画する

PandasにAccessのレコードを取り込む

Pandasのデータを線グラフで描く

Accessから取り込んだPandasのデータをExcelファイルに保存する

Excelファイルに保存したデータをPandasに取り込んで高速化する