Automate the boring stuff excel, cannot get code to pull census2010 ['AK']

Question

I am following the example project under Chapter 13 (Reading Data from a spreadsheet). I have created the census2010.py code and save it in my documents. When I run the quote, I'm getting this key error.

KeyError                                  Traceback (most recent call last)
~\AppData\Local\Temp/ipykernel_35244/1301298642.py in <module>
     26 
     27 import census2010
---> 28 census2010.allData['AK']['Anchorage']
     29 anchoragePop = census2010.allData['AK']['Anchorage']['pop']
     30 print('The 2010 population of Anchorage was ' + str(anchoragePop))

KeyError: 'AK'

This is the current code that I have running on the interactive shell.

import openpyxl, pprint
import os
os.chdir('C:\\users\\brian\\documents')
print('Opening workbook...')

wb = openpyxl.load_workbook('censuspopdata.xlsx')
sheet = wb['Population by Census Tract']
countyData = {}

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

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('Writing results...')
resultFile = open('census2010.py', 'w')
resultFile.write('allData = ' + pprint.pformat(countyData))
resultFile.close()
print('Done.')

import census2010
census2010.allData['AK']['Anchorage']
anchoragePop = census2010.allData['AK']['Anchorage']['pop']
print('The 2010 population of Anchorage was ' + str(anchoragePop))

Not sure how I can get my data from the excel file that I saved also in my documents.

the error is that there isn't a key called 'AK' in census2010.allData -- would you mind posting what allData is or checking that there is a key labeled 'AK' in allData? — Andrew Ryan, Feb 04 '22 at 04:56
I don’t believe I have a file specifically call allData. It was something I was trying to add on the census2010 file — Bwong02, Feb 04 '22 at 23:19
You are using allData in `census2010.allData['AK']['Anchorage']` which is where you error is coming from. There must be some dictionary or value type inside of census2010 that you are trying to access called allData? — Andrew Ryan, Feb 04 '22 at 23:28
Thanks Andrew! I’m going to review the census2010 to look for the allData file. — Bwong02, Feb 05 '22 at 04:40
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Feb 14 '22 at 17:09

Andrew Ryan · Answer 1 · 2022-02-05T05:06:42.777

Is it possible that you changed the current directory to

os.chdir('C:\users\brian\documents')

to download the load the excel data into in which case the script will write a file called census2010.py to that path. But you also have a file called census2010.py in the same directory that you are using the interactive shell in. In which case you need to use

import sys
sys.path.insert(1, 'C:\\users\\brian\\documents')

import census2010

instead of just

import census2010

Importing files from different folder

Automate the boring stuff excel, cannot get code to pull census2010 ['AK']

1 Answers1