Pandas入門講座|06.CSV・Excelファイルの読み込み・書き出し、データベースとの接続方法【PythonのライブラリPandas】

はじめに

こんにちは。キノコードです。

この動画では、CSV・Excelファイルの読み込み・書き出し、データベースとの接続方法について説明します。初心者の方でもわかりやすいように丁寧な解説を心がけました。

この動画では、Pandasでのcsvファイル、Excelファイル、データベースからのデータの読み込み、書き出しの方法を説明します。
これらの方法を覚えると、色々なファイル形式から、Pandasのデータフレームに読み込むため、データ集計や加工などの前処理の効率化ができます。
また書き出しもできるため、自動でExcelなどでレポート作成が可能です。
さらには、データベースとの接続もできるので大量データを扱うことも可能です。
読み込む前の準備として概要欄に記載したキノコードのサイトにCSVファイルやExcelを用意しています。それをダウンロードして、学習用のJupyter Labを保存しているフォルダにに保存してください。
それではパソコン画面に切り替えてレッスンを進めていきましょう。

Pandasインポート

import pandas as pd

それでは、Pandasをインポートするところから始めていきましょう。
まず、Pandasをインポートする記述を書きます。
「import pandas as pd」を書きます。
「as」は、ライブラリ名(pandas)を好きな名前で使うことができます。
したがって、この記述により「pandas」を「pd」という名前で使うことができます。
エラーにならず、インポートが完了しました。

csvファイルを読み取る

pd.set_option(‘display.max_rows’, 10)

Pandasでは、csvファイルを読み込んだときに、表示する行や列数を指定することができます。
例えば、DataFrameのすべての行を表示させるJupyter Notebookが見づらくなります。
したがって、表示させる行数を10としましょう。
まず、「pd.set_option」を書いて、丸括弧、シングルクォーテーションを書きます。
そして、シングルクォーテーションの中に、「display」、ドット、「max_rows」を書きます。
最後に、表示させる行数を書きます。
今回は10行としましょう。
ちなみに、全ての行を表示するとしたら、行数をNoneに設定します。
実行します。
なお、表示させる行を指定する方法として、headメソッドやtailメソッドもありますが、その都度記述するのは手間な場合はset_opitionを使うといいでしょう。

df = pd.read_csv(‘data.csv’, encoding = ‘shift-jis’)
df

次に、pandasの関数を使ってcsvファイルを読み取る方法を説明します。
読み取る方法を説明する前に、このレッスンで使うcsvファイルの説明をします。
このレッスンでも、今までのレッスンで使った、政府が発表している「1920年から2015年までの全国の人口推移のデータ」を使います。
csvファイルには、和暦や西暦ごと、各都道府県ごとに人口推移のデータが記述されています。
それでは、csvファイルを読み取る方法を説明します。
read_csv関数を使って、csvファイルを読み込んでいきます。
まず、データフレームが入る変数dfを書いて、イコール、pd、read_csv関数を書きます。
丸括弧の中に、シングルクォーテーション、読み込むファイル名を書きます。
encodingの引数で、文字コードを指定することができます。
今回は、shift-jisを指定します。他にもutf-8なども指定できます。
それでは、実行してみましょう。
csvファイルを読み取ることができました。

カラム名を変更して読み取る

df = pd.read_csv(‘data01.csv’, encoding = ‘shift-jis’, names = [‘area_code’,’area’, ‘GG’, ‘gg’, ‘yyyy’, ‘population’, ‘man’, ‘woman’])
df

次に、csvファイルを読み取るときに、カラム名を指定して追加する方法を説明します。
それでは、こちらのカラム名がないdata01ファイルを読み込んで、カラム名を追加していきます。
先ほど説明した、read_csv関数を書きます。
そして、引数にnamesを書いて、カラム名のリストを代入します。
それでは、実行してみましょう。
カラム名が追加されましたね。

インデックスを指定して読み取る

df = pd.read_csv(‘data.csv’, encoding = ‘shift-jis’, index_col = 1)
df

次に、csvファイルを読み取るときに、インデックスに指定したい列を設定する方法を説明します。
インデックスを設定するときは、列番号でも列名でも指定することができます。
それでは、都道府県名をインデックスに指定してみます。
read_csv関数を書いて、読み込んでいきます。
引数にindex_col を追加して、指定したい列番号や列名を代入します。
ここでは、列番号で指定しましょう。列番号は、リストと同じように0から始まります。しがって、1番目の列番号0、2番目の列番号1、3番目の列番号2です。都道府県は2番目なので列番号は1です。したがって、index_colに1を代入します。
実行してみましょう。
都道府県名がインデックスに設定されましたね。

複数のインデックスを指定して読み取る

df01 = pd.read_csv(‘data.csv’, encoding = ‘shift-jis’, index_col = [0, 1, 2, 3, 4])
df01.head()

先ほどは、インデックスに指定する列を設定する方法を説明しました。指定した列は1つだけでしたね。今回は、インデックスに複数の列を設定する方法を説明します。
インデックスに複数の列を設定して、データ部分を人口データだけにしてみましょう。
それでは、5列目までをインデックスとして、読み込んでみます。
インデックスに複数の列を指定するときは、index_col にリストを代入します。
実行してみます。
西暦までの列が太字になり、ここまでがインデックスということがわかります。

type(df.index)
type(df01.index)

それでは、インデックスを1つ設定したときと、複数設定したときのデータ型を比較してみていきましょう。
データ型を知りたいときは、知りたいデータを丸括弧で括ってtypeです。
実行してみます。
インデックスを1つ設定したときはこのようになります。
インデックスを複数設定したときは、マルチインデックスになってますね。
なお、マルチインデックスは、複数の列をインデックスすることをいいます。

df.dtypes
df01.dtypes

次に、列ごとのデータ部分のデータ型も比較してみていきましょう。
dtypesを使うと、列ごとにデータ型を調べることができます。
dtypesについては、Pandas超入門コースのDataFrameをご覧ください。右上にカードがでているはずです。
実行してみます。
それぞれのデータ型が表示されました。
インデックスを1つ設定したときは、データ部分が7列であることがわかりますね。
インデックスを複数設定したときは、データ部分が3列になっていますね。

csvファイルに書き出し

df.to_csv(‘data_csv.csv’, encoding = ‘shift-jis’)

次に、データフレームをcsvファイルに書き込む方法を説明します。
to_csv関数を使って、csvファイルを書きこんでいきます。
まず、データフレームが入る変数dfを書いて、to_csv関数を書きます。
丸括弧の中に、シングルクォーテーション、書き込むファイル名を書きます。
read_csv関数と同様に、encodingの引数で、shift-jisを指定します。
これでcsvファイルに書き込んだときに文字化けを防ぐことができます。
それでは、実行してみましょう。
csvファイルができてます。
文字化けもしてませんね。

Excelファイル読み込み

pd.read_excel(‘data.xlsx’)

今度は、excelのデータを読み込みをしましょう。
excelのデータの読み込みは、read_excel関数でできます。
read_csvとread_excelは、使い方は非常に似ています。
読み込みを始める行を指定する方法や、カラムに指定する列を指定できたり、カラム名を指定して読み込んだりもできます。
dataというエクセルファイルを読み込んでみましょう。
実行してみます。
読み込みができています。

行をスキップしてExcelファイル読み込み

pd.read_excel(‘data01.xlsx’)

data01というエクセルファイルをご覧ください。
最初の2行が空白です。
このまま読み込んでみましょう。
最初の2行が空白なので、カラム名がUnnamedになっていたり、1行目がNaNになっています。
したがって、最初の2行をスキップしてエクセルファイルを読み取る記述をしましょう。

pd.read_excel(‘data01.xlsx’, skiprows = 2)

新しくskiprowsという引数にスキップする行数を記述します。
今回は2行スキップするので2を渡します。
実行してみます。
綺麗に読み込みができています。

カラム名を指定してExcelファイル読み込み

pd.read_excel(‘data01.xlsx’, skiprows = 2, header = [0])

エクセルファイルやCSVファイルなどにカラム名がある場合は、headerを明示的に指定する方法もあります。
1行目をカラム名にしているする場合は、角括弧の中に0をしています。
ただし、何も記載しない場合も、0と設定されるようになっているので変化がないはずです。
実行してみます。
変化ありません。

pd.read_excel(‘data01.xlsx’, skiprows = 2, header = [1])

では、headerに1を指定してみましょう。
先ほどのデータフレームの1行目がカラム名になっています。

pd.read_excel(‘data02.xlsx’, header = None)

headerがないdata02というエクセルファイルを作りました。
headerがないファイルの場合は、headerにNoneの値を渡すと、カラム名に自動的に連番を振ってくれます。
実行してみましょう。
連番が振られています。

pd.read_excel(‘data03.xlsx’, header = [0, 1])

今度は、「data03.xlsx」というファイルを開いて中身を見てみましょう。
1行目には地域。2行目には各列の名前が入っています。
したがって、1行目と2行目をカラムに設定しましょう。
header = [0, 1]と記述します。
実行します。
1行目と2行目をカラムに設定されています。

インデックスを指定してExcelファイル読み込み

pd.read_excel(‘data.xlsx’)

もう一度、ただのdataエクセルファイルを開いてみましょう。
1列目をインデックスに設定してみましょう。
設定の方法は、引数のindex_colに列数を渡します。
1行目なので0を渡します。
実行します。1列目の都道府県コードがindexに設定されています。

pd.read_excel(‘data.xlsx’, index_col = ‘都道府県コード’)

列数ではなく、列名で指定することもできます。
実行してみます。
都道府県コードがindexに設定されています。

行スキップ、インデックス指定して読み込み

df_excel = pd.read_excel(‘data03.xlsx’)
df_excel

data03のエクセルファイルをもう一度読み込んで表示させてみましょう。
このようなエクセルファイルでしたね。
地域の1行目はスキップさせて、2行目から読み込み、年月日をindexに設定してみます。

df_excel = pd.read_excel(‘data03.xlsx’, skiprows=1, index_col = ‘年月日’)
df_excel

skiprows=1として、 index_col = ‘年月日’ですね。
実行してみます。
年月日がindexとなり、2行目から読み込まれています。

type(df_excel.index)

インデックスのデータ型を確認してみます。
通常のインデックスになっているようです。

df_excel = pd.read_excel(‘data03.xlsx’, skiprows=1, index_col = ‘年月日’, parse_dates = True)
df_excel

これを先ほどの記述に、parse_datesという引数にTrueを渡してみましょう。
そうすると、インデックスで指定された列が日付型として読み込んでくれます。
実行してみます。
先ほどと見た目があまり変わらないように見えますが、日付が-ハイフン表示になりました。

type(df_excel.index)

データ型を確認してみましょう。
DatetimeIndexとなっていて、日付型になっていることが確認できます。
indexが日付型になると、特殊な集計やメソッドを使うことができます。
これはDataFrameの時系列データのところで詳しく説明しますので、そちらをご参考ください。

Excelファイルに書き込み

df_excel.to_excel(‘df_excel.xlsx’)

DataFrameをExcelデータとして書き出す方法を説明します。
エクセルデータへの書き出しは、to_excelメソッドを使います。
DataFrameドット、to_excel。丸括弧の中にエクセルとして書き出すファイル名を記述します。
実行してみます。
エクセルファイルができています。

クリップボードのデータを書き込み

df_cb = pd.read_clipboard()

次に、read_clipboard関数を使ってみましょう。
read_clipboard関数を使うと、コピーしたデータをそのままDataFrameにすることができます。
data.xlsxのエクセルファイルを開きます。
11行目までコピーをします。
その後、read_clipboard関数を実行して、df_cpの変数に代入をします。
表示させてみます。
先ほどのエクセルデータがデータフレームになっています。
さくっとDataFrameを作りたい時に便利ですよね。

データベース接続

import sqlite3

最後にPythonとデータベースを接続してみましょう。
Pythonは色々なデータベース、つまりリレーショナルデータベース管理システム (RDBMS) と接続することができます。
例えば、MySQL、Postgresql、GoogleCouldのBigQueryに接続することができます。
ここでは、非常に軽量で扱いやすいsqliteと接続をしてみましょう。
sqliteは非常に軽量ですが、立派なリレーショナルデータベース管理システムです。
csvやExcelより大量のデータを扱うことができます。
また、SQLを覚えてしまえば、大量データからデータを抽出することも一瞬でできます。
これを使って接続をしていきましょう。

df = pd.read_csv(‘data.csv’,encoding=’shift-jis’)
df

data.csvのファイルを読み込んでdfの変数に代入をします。
このようなデータでしたね。

import sqlite3

まず、sqliteを使えるように、sqlite3をインポートしましょう。

dbname = ‘TEST.db’
conn = sqlite3.connect(dbname)
conn.close()

まずデータベースを作ってみましょう。
最初に、dbnameという変数に、作成するデータベースの名前を記述します。
今回は、TEST.dbとしましょう。
ドットdbは拡張子の名前です。
したがって、ドットdbより前がデータベースの名前となります。
sqlite3.connectと書いて丸括弧。丸括弧の中に先ほど定義したdbnameを記述します。
こうすることで Connection オブジェクトを作ることができます。
作ったオブジェクトをconnに代入します。
オブジェクトについて理解を深めたい方は、Python超入門コースのクラスで説明をしています。
今、右上にカードがでています。また、概要欄にURLを貼っておきますのでそちらをご覧ください。
最後にデータベースの接続を切る「conn.close()」を記述を忘れないようにしましょう。
それでは実行します。
フォルダに「TEST.db」というファイルができています。

dbname = ‘TEST.db’
conn = sqlite3.connect(dbname)
df.to_sql(‘test_table’,conn,if_exists=’replace’)
conn.close()

データベースができましたので、
これを先ほどcsvファイルを読み込んだデータフレームを、データベースのテーブルにしてみましょう。
df.to_sqlと書いて丸括弧。丸括弧の最初の引数にテーブル名を記述します。今回はtest_tableというテーブル名にしましょう。
次に、先ほどオブジェクトを代入した変数を書きます。
そして、if_existsという引数を書きます。これは、テーブルが既に存在する場合にどのような動作をするのか指定をします。
テーブルに追加をする「append」、テーブルを削除してから新規で作成をする「replace」、エラーを発生させる「fail」があります。
今回はreplaceとしましょう。

テーブルからデータ取得

dbname = ‘TEST.db’
conn = sqlite3.connect(dbname)
df_db = pd.read_sql(‘SELECT * FROM test_table’, conn)
conn.close()

SQLのセレクト文で読み込んでみます。
SQLで読み込むためには、read_sql関数を使います。
丸括弧の中にSQL文を書いて、ドット。オブジェクトを代入した変数connを記述します。
最後にデータベースの接続を切る「conn.close()」

df_db

表示をさせてみましょう。
データベースからデータを読み込むことができ、データフレームにすることができました。

WHERE句を使ったSQL文

sql=”’
select *
from test
where 都道府県名 == 東京都
”’

SQL文が長くなったときのために、改行なしで書くとわかりにくくなります。
Pythonで改行を記述するにはシングルクオテーションを3つです。
ダブルクオテーションを3つでもできますが、私はシングルクオテーションを3つを使っています。
シングルクオテーション3つ、または、ダブルクオテーションを3つのことをトリプルクオートといいます。
このトリプルクォートで囲んだ文字列の中では、改行して記述できます。
where句で東京都だけを絞ったSQLを記述します。
それをsqlという変数に代入をします。

dbname = ‘TEST.db’
conn = sqlite3.connect(dbname)
df_db=pd.read_sql(sql, conn)
conn.close()

これをread_sqlに渡します。

df_db

実行してみます。
SQLのselect文で抽出することができました。

大量データの扱い方

dbname = ‘TEST.db’
conn = sqlite3.connect(dbname)
for i in range(1000):
df.to_sql(‘test_table’,conn,if_exists = ‘append’)
conn.close()

大量データを扱ってみましょう。
csvのファイルはだいたい1000行ありました。
これを1000回追加すれば、約100万行となります。
for文で1000回、追加してみましょう。
今回はif_existsをreplaceではなくappendにします。
実行しましょう。

sql = ”’
select count(*) as cnt
from test_table_1000
”’

SQLでレコース数を確認してみましょう。
SQLをご存知の方は、count(*) as cntでcntというカラム名で確認できますよね。
約100万行あることが確認できます。

sql = ”’
select *
from test_table_1000
where 都道府県名 == 東京都 and 西暦(年) ==1920.0
”’

これを東京都の中の1920年のデータのみ抽出してみましょう。
実行してみます。
あっという間に抽出が終わりました。
表示させてみましょう。
問題なく抽出ができているようです。
このように大量データの場合は、簡単に使えるsqliteと併用すれば便利ですよね。

確認問題

最後に確認問題をやっていきましょう。
今回のレッスンは、いろんな方法でファイルのデータを取り込んだり、書き込んだりしました。
また、データベースでテーブルを作ったり、データを取得したりしました。
これらは、よく使うので復習をしていきましょう。
①dataというcsvファイルを読み込んでください。
②df_dataというcsvファイルで書き込んでください。
③dataというExcelファイルを読み込んでください。
④df_dataというExcelファイルを書きこんでください。
⑤データベースに接続し、test_tableという新しいテーブルを作ってください。
⑥test_tableテーブルにあるデータを全件取得してください。

df_csv = pd.read_csv(‘data.csv’, encoding = ‘shift-jis’)

df_csv.to_csv(‘df_data.csv’, encoding = ‘shift-jis’)

df_excel = pd.read_excel(‘data.xlsx’)

df_excel.to_excel(‘df_excel.xlsx’)
dbname = ‘TEST.db’
conn = sqlite3.connect(dbname)

df.to_sql(‘test_table’,conn,if_exists=’replace’)

df_db = pd.read_sql(‘SELECT * FROM test_table’, conn)
conn.close()


キノコードでは、pythonやPandasの他に、データ可視化のmatplotlibや人工知能のレッスンもアップしていく予定です。
気に入ってくださった方は、チャンネル登録をお願いします。
それでは、次回のレッスンでお会いしましょう。

レッスンで使ったファイルはこちら

■保存方法
Mac:右クリック⇒「リンク先を別名で保存」
Windows:右クリック⇒「名前を付けてリンク先を保存」

CSVやExcelファイルはこちら

Jupyter Labのファイルはこちら