NokkunBlog

Keep technology simple.

【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}

 

参考