PythonでExcel操作

2020年12月25日

PythonでExcelを操作する方法ついて勉強したことをまとめていこうと思います。

Anacondaで環境作成

AnacondaでPython3.9の環境を作成し、Excelを操作するためのライブラリとしてopenpyxlをインストールします。

#AnacondaでPython3.9の環境を作成
$ conda create -n python39 python

#作成した環境を起動
$ conda activate python39

# openpyxlをインストール
(python39)$ conda install -c anaconda openpyxl

# xlrdをインストール
(python39)$ conda install -c anaconda xlrd

# pandasをインストール
(python39)$ conda install -c anaconda pandas

テスト用のExcel

Excel操作の第一歩としてExcelを読み込みこむプログラムを作成します。Sheet1は以下のようにA列とB列に値が入っています。

AB
1test12
2test25
Sheet1

「テスト結果」シートには以下のように生徒名と国語と数学のテストの点数が入っています。

生徒名国語数学
生徒19070
生徒28790
テスト結果

openpyxlでExcelを読み込み列の値を合計する

Sheet1のB列の値を合計するプログラムを以下のように作成します。

import openpyxl
 
wb = openpyxl.load_workbook("Book1.xlsx")
ws = wb["Sheet1"]

s = 0
for r in range(1, 3):
    s += ws.cell(row=r, column=2).value
    
print(s)

実行すると2+7の計算結果7が出力されます。

$  python test1.py
7

PandasでExcelを読み込む

read_excelメソッドを使ってExcelからデータを読み込みます。

ヘッダが無いシートを読み込む

ヘッダが無いシートを読み込む場合はheaderにnoneを指定します。

$ python
>>> import pandas as pd
>>> df = pd.read_excel('Book1.xlsx', sheet_name='Sheet1', header=none)
>>> print(df)
       0  1
0  test1  2
1  test2  5

# 列指定
>>> df[[0]]
       0
0  test1
1  test2
>>> df[[1]]
   1
0  2
1  5

# 統計量表示
>>> df[[1]].describe()
             1
count  2.00000
mean   3.50000
std    2.12132
min    2.00000
25%    2.75000
50%    3.50000
75%    4.25000
max    5.00000

ヘッダがあるシートを読み込む

$ python
>>> import pandas as pd
>>> df = pd.read_excel('Book1.xlsx', sheet_name='テスト結果')
>>> print(df)
     生徒名  国語  数学
0    生徒1  90  70
1    生徒2  87  90
...
19   生徒20 55  57

平均、標準偏差、偏差値、相関係数を求める

# 平均を求める
>>> df.mean()
国語    69.45
数学    70.30
dtype: float64

#　標準偏差を求める
>>> df.std(ddof=0)
国語    18.092747
数学    15.687893
dtype: float64

# 生徒の国語の偏差値を求める
>>> ave1 = df['国語'].mean()
>>> std1 = df['国語'].std(ddof=0)
>>> for i in df['国語']:
...     print((i - ave1)/std1 *10 + 50)
61.35814247104469
59.700019482571015

# ラムダを使って国語の偏差値を求めてDataFrameの列へ追加
>>> df['国語の偏差値'] =  df['国語'].apply(lambda x: (x - ave1)/std1 * 10 + 50)
>>> df
     生徒名  国語  数学     国語の偏差値
0    生徒1  90  70  61.358142
1    生徒2  87  90  59.700019

# 国語の得点と数学の得点の相関係数を求める
>>> df[['国語','数学']].corr()
          国語        数学
国語  1.000000  0.303396
数学  0.303396  1.000000

# 「偏差値」シートつ追加して結果を書き込む
>>> with pd.ExcelWriter('Book1.xlsx', mode = 'a') as writer:
...     df.to_excel(writer, sheet_name='偏差値')

Excel,Python

Posted by fanfanta