Python Reading Data from a Spreadsheet

投稿者: | 2017年3月8日
# !/user/bin/env python
# - Tabulates population and number of census tracts for each country

import openpyxl, pprint


def main():
    print('Opening Workbook...')
    wb = openpyxl.load_workbook('censuspopdata.xlsx')
    sheet = wb.get_sheet_by_name('Population by Census Tract')
    countyData = {}

    # Fill in countyData with each country's population and tracts.
    print('Reading rows...')

    for row in range(2, sheet.get_highest_row()+1):
        # Each row in the spreadsheet has data for one census tract.
        state = sheet['B' + str(row)].value
        county = sheet['C' + str(row)].value
        pop = sheet['D' + str(row)].value

        # Make sure the key for this state exist.
        countyData.setdefault(state, {})
        # Make sure the key for this county in this state exists.
        countyData[state].setdefault(county, {'tracts':0, 'pop':0})
        # Each row represents one census tract, so increment exist.
        countyData[state][county]['tracts'] += 1
        # Increase the county pop by the pop in this census tract.
        countyData[state][county]['pop'] += int(pop)
    # open a new text file and write the contents of countyData to it.
    print('Writing results...')
    with open('census2010.py', 'w') as resultFile:
        resultFile.write('allData = ' + pprint.pformat(countyData))

    print('Done')

if __name__ == '__main__':
    main()

スプレッドシートを読み込んで、.pyに辞書型でデータを保存するスクリプト。

 

 

 

Pocket

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA