【Python】エクセルのデータを計算する

エクセルに書かれたデータをPythonで読み込んで計算してみます。

使用するデータ

githubにあった、アメリカの人口データ(エクセル)を使用します。

https://github.com/oreilly-japan/automatestuff-ja/tree/master/ch12

中身はこんな感じです。

72000ほどの行で構成されています。

Stateというのが州ですね。Countyは群です。カリフォルニア州サンフランシスコ群　みたいな感じです。POP2010が人口です。Census Tractが人口を調査した地域(人口調査標準地域)ですね。

同じ群の人口の合計値を求める時に、エクセルで範囲を指定して合計値を出して〜とやっているとかなり時間がかかります。

コードを書いて自動化してみます。

コードの概要

openpyxlモジュールを使って、エクセルのデータを読み込みます。

読み込んだ後の流れは以下の通り。

1. WorkBookオブジェクトを取得する
2. WorkSheetの名前を確認する
3. WorkSheetオブジェクトを取得する
4. 各セルの値を取得＆加算して、辞書に保存する
5. 辞書をファイルに保存する
6. 保存した辞書のデータにアクセスする

エクセルシートのデータの読み込みや計算は重いので一回だけ行うことにして、結果を辞書に保存しておきます。

コード

import openpyxl
import pprint

# Workbookオブジェクトを取得する
wb = openpyxl.load_workbook('data/censuspopdata.xlsx')

# シート名を取得する
print(wb.sheetnames)

# Worksheetオブジェクトを取得する
sheet = wb['Population by Census Tract']

# シートの列と行の最大値を確認
print(sheet.max_row)
print(sheet.max_column)

# 辞書の初期化
state_dict={}

for row in range(2,sheet.max_row+1):
    # 州、群、人口を取得
    state = sheet['B'+str(row)].value
    county = sheet['C'+str(row)].value
    pop = sheet['D'+str(row)].value

    # 州のキーがなかった場合、追加する
    state_dict.setdefault(state,{})
    # 州に対応する群のキーがなかった場合、追加する
    state_dict[state].setdefault(county,{'tracts':0,'pop':0})
    # 群の人口調査地域の数をカウントする
    state_dict[state][county]['tracts'] += 1
    #群の人口を足し合わせる
    state_dict[state][county]['pop'] += int(pop)

# データの保存
with open('data/census2010.py','w') as result_file:
    result_file.write("all_data = {}".format(pprint.pformat(state_dict)))
    
print("完了")

少し長くなってしまいましたが、重要なのは三次元の辞書を作ってデータを代入するところでしょうか。州と群のキーがなかった場合にsetdefaultをして追加しておかないと、データを代入する時にキーエラーが発生します。

保存したデータを読み込んで表示してみます。

import data.census2010 as dc
print(dc.all_data['CA']['San Francisco'])

サンフランシスコの人口80万人が取得できました。

{'pop': 805235, 'tracts': 196}

参考