PandasでExcelファイルを読む
PythonでExcelファイル（.xlsx、.xls）を読むにはいくつか方法がありますが、今回はPandasの pandas.read_excel() 関数を使います。
作業環境
Windows10
Anaconda
Jupyter Notebook
Python3.7 （Pandasをインストール済み）
目次
準備
Excelシートをpandasで読むにはpandas.read_excel() を使います。
xlrdというExcelを読むためのパッケージが必要なので先にインストールします。
pip install xlrd
Anacondaの場合は
conda install xlrd
または Anaconda Navigator の Environmentsからインストール。
読ませるExcelファイル名を sample.xlsx とし、同じディレクトリに保存してあるとします。
read_excelでシートをシンプルに読み込む
- ファイル名を指定するだけの簡単な書き方
import pandas as pd df = pd.read_excel('sample.xlsx') print(df)
1 ささパンダ 株式会社竹富 いかした商事 250 50 1.1
0 2 しーしーオマル (有)白鳥 (有)白鳥 8500 8 3
1 3 スースー冷却枕 北枕社 仏霊園 980 45 3
↑ JupyterNotebook の 実行結果です。
Excelファイルに複数のシートがある時は、先頭のシートだけがPandas.DataFrameとして読み込まれます。
上記はindexやheaderへの指示を何も書いていません。
1行目がヘッダーとして読まれたようです。
1行目に項目名を入れていないシートなので、ヘッダーとして読まないように指示するにはheader=None をつけてやります。
- 1行目をヘッダーとして読まない書き方
import pandas as pd df = pd.read_excel('sample.xlsx' , header = None) print(df)
0 1 2 3 4 5 6
0 1 ささパンダ 株式会社竹富 いかした商事 250 50 1
1 2 しーしーオマル (有)白鳥 (有)白鳥 8500 8 3
2 3 スースー冷却枕 北枕社 仏霊園 980 45 3
header = None をつけたので、自動でヘッダーに数字が割り振られました。
- ヘッダーを指定して読み込み表示する
列の項目名が数字だと味気ないのでdf.columnsの行を追加しました。
import pandas as pd df = pd.read_excel('sample.xlsx' , header = None) df.columns = ["商品番号","商品名","製造メーカー","卸元","単価","在庫数量","倉庫番号"] print(df)
商品番号 商品名 製造メーカー 卸元 単価 在庫数量 倉庫番号
0 1 ささパンダ 株式会社竹富 いかした商事 250 50 1
1 2 しーしーオマル (有)白鳥 (有)白鳥 8500 8 3
2 3 スースー冷却枕 北枕社 仏霊園 980 45 3
なんのデータなのかが分かりやすい表示にできました。
read_excelでシート番号を指定して読む
sheet_name = 1 のようにしてシート番号を指定するとそのシートを読むことができます。
import pandas as pd df = pd.read_excel('sample.xlsx', sheet_name = 1 , header = None) print(df)
0 1 2 3 4 5 6
0 1 せせらぎの歌 癒し音響工房 ABCミュージック 1850 27 2
1 2 そそがる君 (有)白鳥 (有)白鳥 140 12 3
2 3 多々財閥の歴史 印録出版 ABCブック 2540 2 2
シート番号は先頭シートを順に0から始まることに注意。
この場合は「1」を指定しているので、左から2枚目のシートが表示されました。
read_excelでシート名を指定して読む
シート名「Sheet1」を読み込んで表示させてみます。
import pandas as pd df = pd.read_excel('sample.xlsx', sheet_name = 'Sheet1', header = None) print(df)
0 1 2 3 4 5 6
0 1 ささパンダ 株式会社竹富 いかした商事 250 50 1
1 2 しーしーオマル (有)白鳥 (有)白鳥 8500 8 3
2 3 スースー冷却枕 北枕社 仏霊園 980 45 3
read_excelで全てのシートを読む
sheet_name = None で全シートを一括表示します。
import pandas as pd df = pd.read_excel('sample.xlsx', sheet_name = None , header = None) print(df)
OrderedDict([('Sheet1', 0 1 2 3 4 5 6
0 1 ささパンダ 株式会社竹富 いかした商事 250 50 1
1 2 しーしーオマル (有)白鳥 (有)白鳥 8500 8 3
2 3 スースー冷却枕 北枕社 仏霊園 980 45 3), ('Sheet2', 0 1 2 3 4 5 6
0 1 せせらぎの歌 癒し音響工房 ABCミュージック 1850 27 2
1 2 そそがる君 (有)白鳥 (有)白鳥 140 12 3
2 3 多々財閥の歴史 印録出版 ABCブック 2540 2 2)])
先頭に「OrderedDict」と書いてあります。 OrderedDictは順番を保持する辞書型らしいです。
通常のdict型は順番を保持しません。
試しに1万9千行ほどある商品コードの載ったシートを1枚だけ読ませてみましたが、自分のPCでは表示に20秒ちかくかかりました。
全ての行が表示されるわけではなく、1番上と1番下の数行のみ表示で、中間部分は省略されます。
上記の実行結果を見てわかるように、Excelで読んだ方が見やすいので、
Pandasを使う目的を、シート表示以外の、検索だとか本来の目的であるデータ分析だとかに絞った方がよいように思います。
read_excelで全てのシート名を取得
Excelファイルのシート名をread_excelで取得するには、一旦すべてのシートを読み込んだ後にlist(df.keys())を使います。
list関数でリスト化して取得しています。
import pandas as pd df = pd.read_excel('sample.xlsx', sheet_name = None , header = None) sheetname_list = list(df.keys()) print("・シート名のリスト",sheetname_list) # ちなみに list を使わなければ print("・listを外すと ",df.keys())
・シート名のリスト ['Sheet1', 'Sheet2']
・listを外すと odict_keys(['Sheet1', 'Sheet2'])
上でシート名を取り出す際に df.keys() を使いました。
keysは辞書型で使いますよね。Pandas.DataFrameの実行結果もOrderedDict。
ということは、valuesやitemsも使えるはず．．．
df.values()を使ってread_excelで全てのシートを取得
import pandas as pd df = pd.read_excel('sample.xlsx', sheet_name = None , header = None) sheetvalues_list = list(df.values()) print(sheetvalues_list) print("\n") print(type(sheetvalues_list)) # 型 print(len(sheetvalues_list)) # リスト要素の個数 print("\nリストの1つ目\n") print(sheetvalues_list[0]) # リストの1つ目 print("\nリストの2つ目\n") print(sheetvalues_list[1]) # リストの2つ目
[ 0 1 2 3 4 5 6
0 1 ささパンダ 株式会社竹富 いかした商事 250 50 1
1 2 しーしーオマル (有)白鳥 (有)白鳥 8500 8 3
2 3 スースー冷却枕 北枕社 仏霊園 980 45 3, 0 1 2 3 4 5 6
0 1 せせらぎの歌 癒し音響工房 ABCミュージック 1850 27 2
1 2 そそがる君 (有)白鳥 (有)白鳥 140 12 3
2 3 多々財閥の歴史 印録出版 ABCブック 2540 2 2]
<class 'list'>
2
リストの1つ目
0 1 2 3 4 5 6
0 1 ささパンダ 株式会社竹富 いかした商事 250 50 1
1 2 しーしーオマル (有)白鳥 (有)白鳥 8500 8 3
2 3 スースー冷却枕 北枕社 仏霊園 980 45 3
リストの2つ目
0 1 2 3 4 5 6
0 1 せせらぎの歌 癒し音響工房 ABCミュージック 1850 27 2
1 2 そそがる君 (有)白鳥 (有)白鳥 140 12 3
2 3 多々財閥の歴史 印録出版 ABCブック 2540 2 2
df.items()を使ってread_excelで全てのシートを取得
import pandas as pd df = pd.read_excel('sample.xlsx', sheet_name = None , header = None) sheetitems_list = list(df.items()) print(sheetitems_list)
[('Sheet1', 0 1 2 3 4 5 6
0 1 ささパンダ 株式会社竹富 いかした商事 250 50 1
1 2 しーしーオマル (有)白鳥 (有)白鳥 8500 8 3
2 3 スースー冷却枕 北枕社 仏霊園 980 45 3), ('Sheet2', 0 1 2 3 4 5 6
0 1 せせらぎの歌 癒し音響工房 ABCミュージック 1850 27 2
1 2 そそがる君 (有)白鳥 (有)白鳥 140 12 3
2 3 多々財閥の歴史 印録出版 ABCブック 2540 2 2)]
dfか、df.values()か、df.items()かでprintしたときの表示が若干違うことがわかりました。
read_excelで読んたExcelシートをSQLiteに書き込む
前回の投稿でSQLiteをやったので、応用をやってみます。
sheet_name = 0 で先頭シートのみを読み込み、SQLiteに書き込みを行い、それを読み出してみます。
Excelの先頭シートだけをSQLiteに書き込む
import pandas as pd import sqlite3 ########### Excelの読込 ################ df = pd.read_excel('sample.xlsx', sheet_name = 0 , header = None) ########### SQLiteへの書込み ########### dbname = "sample_excel.sqlite" conn = sqlite3.connect(dbname) cur = conn.cursor() cur.execute("DROP TABLE IF EXISTS shouhin_ichiran") df.columns = ["商品番号","商品名","製造メーカー","卸元","単価","在庫数量","倉庫番号"] df.to_sql('shouhin_ichiran' , conn, index = False) conn.commit() ########### SQLiteの読込 ############# df = pd.read_sql('SELECT *FROM shouhin_ichiran',conn) print(df) conn.close()
商品番号 商品名 製造メーカー 卸元 単価 在庫数量 倉庫番号
0 1 ささパンダ 株式会社竹富 いかした商事 250 50 1
1 2 しーしーオマル (有)白鳥 (有)白鳥 8500 8 3
2 3 スースー冷却枕 北枕社 仏霊園 980 45 3
・「Excelの読込」部分
先頭シートだけを、1行目をヘッダーとせずに読み込み。
sheet_name = 0 を sheet_name = None（全シート）や、存在しないシート番号、シート名で指定するとエラーが出ました。
全てのシートをSQLiteに書き込むには一工夫いるようです。
・「SQLiteへの書込み」
「Excelの読込」で読み込んだデータフレームdfにdf.columnsでヘッダーをつけ、to_sqlでデータベースファイルに書き込みます。
・「SQLiteへの読込」
書き込まれたデータベースを読んで表示させています。
一旦Excelの全シートを読み込んだ後、list(df.keys())でシート名を取り出してfor文で回し、追記させてみます。
Excelの全シートをSQLiteに書き込む
import pandas as pd import sqlite3 ########### Excelの読込 ################ df = pd.read_excel('sample.xlsx', sheet_name = None , header = None) # シート名をリストで取得 sheetname_list = list(df.keys()) print(sheetname_list) ########### SQLiteへの書込み ########### dbname = "sample_excel.sqlite" conn = sqlite3.connect(dbname) cur = conn.cursor() cur.execute("DROP TABLE IF EXISTS shouhin_ichiran") # シート名リストの要素ごとに書き込む for i in sheetname_list: df = pd.read_excel('sample.xlsx', sheet_name = i , header = None) df.columns = ["商品番号","商品名","製造メーカー","卸元","単価","在庫数量","倉庫番号"] df.to_sql('shouhin_ichiran' , conn , if_exists = 'append' , index = False) conn.commit() ########### SQLiteへの読込 ############# df = pd.read_sql('SELECT *FROM shouhin_ichiran',conn) cur.execute("SELECT* FROM shouhin_ichiran") item_list = cur.fetchall() for i in item_list: print(i) conn.close()
['Sheet1', 'Sheet2']
(1, 'ささパンダ', '株式会社竹富', 'いかした商事', 250, 50, 1)
(2, 'しーしーオマル', '(有)白鳥', '(有)白鳥', 8500, 8, 3)
(3, 'スースー冷却枕', '北枕社', '仏霊園', 980, 45, 3)
(1, 'せせらぎの歌', '癒し音響工房', 'ABCミュージック', 1850, 27, 2)
(2, 'そそがる君', '(有)白鳥', '(有)白鳥', 140, 12, 3)
(3, '多々財閥の歴史', '印録出版', 'ABCブック', 2540, 2, 2)
一応データベースに書き込めたことを確認できたので今回はここまで。
