よちよちpython

独習 python/Qpython/Pydroid3/termux/Linux

PandasでExcelファイルを読む

PythonExcelファイル(.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 とし、同じディレクトリに保存してあるとします。

f:id:chayarokurokuro:20190820163316j:plain

f:id:chayarokurokuro:20190820163433j:plain


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。

ということは、valuesitemsも使えるはず...



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 = 0sheet_name = None(全シート)や、存在しないシート番号、シート名で指定するとエラーが出ました。
全てのシートをSQLiteに書き込むには一工夫いるようです。


・「SQLiteへの書込み」
Excelの読込」で読み込んだデータフレームdfdf.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)



一応データベースに書き込めたことを確認できたので今回はここまで。