Python: 複数のExcelファイルをPython Pandasに取り込んでMS Accessのデータベースに一元化するには【Python】(Article005)

ようこそ「Python」へ...

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

複数のExcelファイルをPython Pandasに取り込んでMS Accessのデータベースに一元化するには【Python】

ここでは複数のExcelファイルをPython Pandasに取り込んでMicrosoft Accessのデータベースに一元化する方法を解説します。 PythonでExcelファイルを取り込むにはPandasのread_excel()メソッドを使用します。そしてPandasのDataFrameに取り込んだデータをAccessのデータベースに出力して一元化するにはPandasのto_sql()メソッドを使用します。ここではアメリカ、中国、インドなど国別に作成された年度別の人口統計データ(Excelで作成)を使用します。各国の人口統計データをAccessのテーブルに一元化したら、今度はそれを読み込んで線グラフを描画します。 Pythonで線グラフを描くにはMatplotlibのplot()メソッドを使用します。

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

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

前準備

国別の人口データをExcelで作成する

図1

Excelで図1のような人口データを国別に作成します。行１にはフィールド名として「Country」「SurveyYear」「Population」を入力します。行２からは年度別の人口データを入力します。ここでは日本の人口データを入力しています。データの入力が完了したら「Japan.xlsx」のファイル名で保存します。同様の手順で中国、アメリカ、インドの人口データを作成したら「United States.xlsx」「China.xlsx」「India.xlsx」の名前で保存します。

図2

ここでは10ヶ国の人口データを作成していますが、少なくとも日本、アメリカ、中国、インドの４ヶ国のデータを作成してください。
Accessに空のテーブルを作成する

図3

Accessを起動してデータベース「Stats.accdb」を作成したら、新規テーブル「PopulationByCountry_Import」を作成します。「ID」フィールドは「オートナンバー型」、「Country」フィールドは「短いテキスト型(50バイト）」、「SurveyYear」フィールドは「数値型(整数型)」、「Population」フィールドは「数値型(長整数型)」とします。図1のExcelのヘッダーと図3のAccessのテーブルのフィールド名が一致していることを確認します。フィールド名が一致しないときはExcelのデータをAccessのテーブルに追加するときエラーになります。

1個のExcelファイルを処理する

Excelの人口データをPandasに取り込む
Visual Studio Codeを起動したら以下のコードを入力して実行します。行2-10ではPaythonのライブラリを取り込んでいます。行13では日本の人口データが格納されているExcelのパスを記述しています。パス名にはバックスラッシュ「\」等の特殊文字が含まれる可能性があるのでrow文字列として定義しています。行14ではPandasのread_excel()メソッドを実行してExcelのファイルをPandasのDataFrameに取り込んでいます。行15ではDataFrameに格納されているデータの先頭から３件のデータを表示しています。
```
# Load the necessary libraries
import pyodbc as pyo
import pandas as pd
import urllib
from sqlalchemy import create_engine
import glob as gl

import matplotlib.pyplot as plt
import matplotlib.style as style
import numpy as np

# Importing an Excel file into Pandas
xlsx_path = (r'data/excel/article005/Japan.xlsx')
df = pd.read_excel(xlsx_path)
print(df.head(3))
```
図4

図4は「print(df.head(3))」の実行結果です。Excelのファイル(Japan.xlsx)から取り込んだデータが３件表示されています。
Pandasに取り込んだ人口データをAccessのテーブルに出力する
Visual Studio Codeから行6-13を入力して実行します。行7-10ではAccessの「Stats.accdb」データベースの接続文字列を定義しています。接続文字列にはバックスラッシュ「\」などの特殊文字が含まれる可能性があるのでrow文字列として定義します。行11-12ではAccessのデータベースの接続文字列をURI形式に変換してAccessのエンジンを生成しています。行13ではPandasのto_sql()メソッドを実行してPandasのDataFrameに格納されているデータをAccessのデータベースに出力しています。ここではDataFrameの「index」フィールドを出力させたくないので引数に「index=False」を指定しています。 Accessの「Stats.accdb」データベースにはすでに空のテーブル「PopulationByCountry_Import」が定義されているので「if_exists='append'」を指定してエラーとなるのを回避しています。

次のステップでは、行2-13までの処理を各国のExcelファイルに対して記述するのは効率がよくないのでPythonのforループ内で行うように改善します。
```
# Importing an Excel file into Pandas
xlsx_path = (r'data/excel/article005/Japan.xlsx')
df = pd.read_excel(xlsx_path)
print(df.head(3))

# Exporting a Pandas DataFrame to an Access Table
con_str = (
	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
	r'DBQ=data/db/article005/Stats.accdb;'
	)
con_uri = f'access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(con_str)}'
acc_engine = create_engine(con_uri)
df.to_sql('PopulationByCountry_Import', acc_engine, index=False, if_exists='append') 
```
図5

図5はAccessを起動して「PopulationByCountry_Import」テーブルを表示したものです。 ExcelのデータがAccessのテーブルに出力されていることが確認できます。

複数のExcelファイルを処理する

ファイルごとにPandasに取り込んだ人口データをAccessのテーブルに出力して一元化する
Visual Studio Codeから以下のコードを入力します。行2-5ではAccessの「Stats.accdb」の接続文字列を記述しています。行6-7では接続文字列をURIに変換してAccessのエンジンを生成しています。行9-12のforループでは「data/excel/article005」フォルダに格納されているすべてのExcelファイル(*.xlsx)のパス名を取得して、 Excelの人口データをPandasに取り込んでAccessの「Stats.accdb」データベースに出力して一元化しています。フォルダ内に格納されているすべてのExcelファイル(*.xlsx)のパスを取得するにはglobのglob()メソッドを使用します。 glob()メソッドではパス名が「data/excel/article005\Japan.xlsx」のような形式で返されます。行11ではExcelの人口データをPandasに取り込んでいます。行12ではPandasのDataFrameに格納されている人口データを「Stats.accdb」データベースの「PopulationByCountry_Import」テーブルに追加しています。

NOTE:ファイルのパスですがWindowsの環境では「data/excel/article005\Japan.xlsx」、「data/excel/article005/Japan.xlsx」とも正常に動作するようです。詳細は「ここ」を参照してください。
```
# Importing Excel files into Pandas and Exporing to an Access Table
con_str = (
	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
	r 'DBQ=data/db/article005/Stats.accdb;'
	)
con_uri = f'access+pyodbc:///?odbc_connect={urllib.parse.quote_plus(con_str)}'
acc_engine = create_engine(con_uri)

for file_path in gl.glob('data/excel/article005/*.xlsx'):
	print(file_path)    # data/excel/article005\Japan.xlsx
	df = pd.read_excel(file_path)	
	df.to_sql('PopulationByCountry_Import', acc_engine, index=False, if_exists='append')
```
図6

図6は実行結果です。「print(xlsx_path)」で表示したExcelファイルのパス名が表示されています。

図7

図7はAccessを起動して「PopulationByCountry_Import」テーブルを表示したものです。レコード件数が130件になっていますのですべてのExcelファイルの人口データが出力されたことが確認できます。

Accessから人口データを取り込んで線グラフを描く

まずはシンプルな線グラフを作成してみる
行2では線グラフのスタイルを指定しています。このスタイルの詳細については、「こちら」を参照してください。行4-5では「Line 1」のX軸、Y軸の値を定義しています。行6-7では「Line 2」のX軸、Y軸の値を定義しています。行9-10ではLine1とLine2の線グラフを描画しています。 Line 1の色は「Green」、Line 2の色は「Cyan」を指定しています。行12では凡例を表示させます。線グラフのタイトル、ラベル、凡例等を日本語で表示するには、行2のあとに「plt.rcParams['font.family'] = 'Yu Gothic'」を追加して日本語のフォント名を指定します。フォント名は各自のPCの環境に合わせて変えてください。また、線グラフの大きさを変えたいときは「plt.figure(figsize=(8,5))」を追加します。行13では線グラフを画面に表示します。
```
# Draw simple line chart
style.use('ggplot')

x = [5,8,10]
y = [12,16,6]
x2 = [6,9,11]
y2 = [6,15,7]

plt.plot(x, y,'g',label='Line 1', linewidth=5)
plt.plot(x2, y2,'c',label='Line 2', linewidth=5)

plt.legend()
plt.show()
```
図8

図8は実行結果です。「Line 1」が緑、「Line 2」がシアンで表示されています。
Accessから人口データを取り込んで線グラグを描画する
Visual Studio Codeから以下のコードを入力して実行します。行2-5ではAccessの「Stats.accdb」データベースの接続文字列を記述しています。行7ではPyodbcのconnect()メソッドでAccessのデータベースを接続(開く)しています。行8では「Stats.accdb」データベースの「PopulationByCountry_Import」テーブルからすべてのレコードを抽出するSQLコマンド(SELECT)を記述しています。行9ではPandasのread_sql()メソッドを実行してAccessの「PopulationByCountry_Import」テーブルからすべのレコードを抽出してPandasのDataFrameに格納します。行10ではPyodbcのclose()メソッドでAccessのデータベースを切断(閉じる)しています。行13ではDataFrameのloc()メソッドを実行して「China」のレコードのみ抽出してDataFrame「ch_df」に格納しています。行14ではMatplotlibのplot()メソッドを実行して中国の線グラフを描画しています。 plot()のX軸には、DataFrameの「SurveyYear」、Y軸にはDataFrameの「Population」を指定します。線の色は「Green」を指定します。行13で絞り込んだ中国の人口データをCSVファイル、Excelファイル、テキストファイル（タブ区切り）で保存したいときは、「ch_df.to_csv('China.csv', index=False)」、「ch_df.to_excel('China.xlsx', index=False)」、「ch_df.to_csv('China.txt', index=False, sep='\t')」のように記述します。 DataFrameの「index」フィールドは保存する必要がないので「index=False」を指定します。タブ区切りのテキストファイルとして保存するときは「 sep='\t'」を指定します。行13-23では、アメリカ、日本、インドの人口データを絞り込んで線グラフを描画しています。行25-26ではX軸とY軸のラベルを設定しています。行27ではグラフのタイトルを設定しています。行28ではグラフの凡例を表示させます。行30ではグラフを画面に表示します。グラフを保存したいときは「plt.savefig('Population_figure.png')」のように記述します。

次のステップでは、行13-23までの処理をforループ内で行うように改善します。
```
# Draw a line graph of the population
con_str = (
	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
	r 'DBQ=data/db/article005/Stats.accdb;'
	)

con = pyo.connect(con_str)
sql = 'SELECT * FROM PopulationByCountry_Import'
df = pd.read_sql(sql, con)
con.close()
#print(df)

ch_df = df.loc[df['Country'] == 'China']
plt.plot(ch_df.SurveyYear, ch_df.Population,'g',label='China', linewidth=5)

us_df = df.loc[df['Country'] == 'United States']
plt.plot(us_df.SurveyYear, us_df.Population,'c',label='United States', linewidth=5)

jp_df = df.loc[df['Country'] == 'Japan']
plt.plot(jp_df.SurveyYear, jp_df.Population,'r',label='Japan', linewidth=5)

in_df = df.loc[df['Country'] == 'India']
plt.plot(in_df.SurveyYear, in_df.Population,'b',label='India', linewidth=5)

plt.xlabel('Year')
plt.ylabel('Population')
plt.title('Population Line Graph')
plt.legend()
#plt.grid(True,color='k')
plt.show()
```
図9

図9は実行結果です。 2020年時点では中国の人口がもっとも多くインドが２位になっています。中国とインドの人口が急激に増えていることがわかります。アメリカはゆるやかに人口が増えています。日本はほぼ横ばいになっています。これから人口が減るので線が右下がりになります。
PandasのDataFrameを国名で絞込んで線グラフを描画する処理をforループ内で行う
項番２の行13-23までをコメントにして項番３の行13-18のコードを入力して実行します。行13では線グラフを描く国名をリスト型で定義します。ここでは中国、カナダ、インドネシア、インドを定義しています。行14には線グラフの色をリスト型で定義します。行15-18のforループでは、リスト型の変数countriesから国名を取り出して変数countryに格納します。行17ではDataFrameのloc()メッソドを実行してデータを国名で絞り込んでいます。行18では絞り込んだDataFrameを使用して線グラフを描きます。線グラフの色は「colors.pop(0)」で取得します。pop()はcolorsリストから先頭の要素を取得後リストから削除します。たとえば、countryが「China」のときはcolorsリストから「g」を取得後削除します。この時点でcolorsリストには['c','r','b']が格納されています。
```
# Draw a line graph of the population
con_str = (
	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
	r'DBQ=data/db/article005/Stats.accdb;'
	)

con = pyo.connect(con_str)
sql = 'SELECT * FROM PopulationByCountry_Import'
df = pd.read_sql(sql, con)
con.close()
#print(df)

countries = ['China','Canada','Indonesia','India']
colors = ['g','c','r','b']
for country in countries:
	#print(country, colors.pop(0))
	new_df = df.loc[df['Country'] == country]
	plt.plot(new_df.SurveyYear, new_df.Population, colors.pop(0),label=country, linewidth=5)

plt.xlabel('Year')
plt.ylabel('Population')
plt.title('Population Line Graph')
plt.legend()
#plt.grid(True,color='k')
plt.show()
```
図10

図10は実行結果です。線グラフには中国、カナダ、インドネシア、インドの線グラフが表示されています。

Accessからクロス集計クエリで人口データを取り込んで線グラフを描く

Accessのクロス集計クエリを作成する
Accessの「記事(Article002)」を参照して「PopulationByCountry_Import」テーブルのクロス集計クエリを作成します。クロス集計クエリの行見出しは「SurveyYear」、列見出しが「Country」、集計するフィールドが「Population」になっています。
```
TRANSFORM Sum(Population) AS Population_Sum
SELECT SurveyYear, Sum(Population) AS Population_Total
FROM PopulationByCountry_Import
GROUP BY SurveyYear
PIVOT Country
```
図11

図11はAccessの「PopulationByCountry_Import」テーブルをクロス集計クエリで表示したものです。
Accessからクロス集計のデータを取り込んで線グラフを作成する
行7ではAccessの「Stats.accdb」データベースを接続(開く)しています。行8-14では「PopulationByCountry_Import」テーブルのクロス集計クエリを記述したものです。 SQLコマンドが長いときはシングルクォーテーション「'''」かダブルクオーテーション「"""」を３個記述して囲むと改行させることができます。行15ではPandasのread_sql()メソッドを実行してAccessのデータを取得してPandasのDataFrameに格納しています。行16ではPyodbcのclose()メソッドでデータベースを切断(閉じる)しています。行18では線グラフを描く国名をリスト型で定義しています。行19では線グラフの色をリスト型で定義しています。行20-22のforループでは、PandasのDataFrameから中国、カナダ、インドネシア、インドの列名を取り出して変数countryに格納しています。行22ではMatplotlibのplot()メソッドを実行して線グラフを描画しています。行24-26では、X軸とY軸のラベルとタイトルを設定しています。行27では凡例を表示します。行28では線グラフを画面に表示します。
```
# Draw a line graph of the population
con_str = (
	r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};'
	r'DBQ=data/db/article005/Stats.accdb;'
	)

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

countries_to_look_at = ['China', 'Canada', 'Indonesia', 'India']
colors = ['g','c','r','b']
for country in df[countries_to_look_at]:    
    print(country)      
    plt.plot(df.SurveyYear, df[country], colors.pop(0), label=country, marker='.')

plt.xlabel('Year')
plt.ylabel('Population')
plt.title('Population Line Graph')
plt.legend()
plt.show()
```
図12

図12は実行結果です。中国、カナダ、インドネシア、インドの線グラフが描画されています。

Go Top

Python {Article005}

複数のExcelファイルをPython Pandasに取り込んでMS Accessのデータベースに一元化するには【Python】

前準備

国別の人口データをExcelで作成する

Accessに空のテーブルを作成する

1個のExcelファイルを処理する

Excelの人口データをPandasに取り込む

Pandasに取り込んだ人口データをAccessのテーブルに出力する

複数のExcelファイルを処理する

ファイルごとにPandasに取り込んだ人口データをAccessのテーブルに出力して一元化する

Accessから人口データを取り込んで線グラフを描く

まずはシンプルな線グラフを作成してみる

Accessから人口データを取り込んで線グラグを描画する

PandasのDataFrameを国名で絞込んで線グラフを描画する処理をforループ内で行う

Accessからクロス集計クエリで人口データを取り込んで線グラフを描く

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

Accessからクロス集計のデータを取り込んで線グラフを作成する