#! python3
# readCensusExcel.py - Tabulates population and number of census tracts for
# each county.

import pprint, openpyxl

print('打开工作簿...')
wb = openpyxl.load_workbook('resource/censuspopdata.xlsx')
sheet = wb['Population by Census Tract']
countyData = {}

# 填充
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

    # 确保州存在
    countyData.setdefault(state, {})
    # 确保县在州中存在
    countyData[state].setdefault(county, {'tracts': 0, 'pop': 0})

    # 每一行代表一个县某统计区的人口数
    countyData[state][county]['tracts'] += 1
    # 追加县的人口数
    countyData[state][county]['pop'] += int(pop)

# 将结果写入文件
print('写入结果...')
resultFile = open('census2010.py', 'w')
resultFile.write('allData = ' + pprint.pformat(countyData))
resultFile.close()
print('完成.')