<jupyter_start><jupyter_text>Step 3<jupyter_code>from urllib import request
import json
from pprint import pprint
import xlwt
census_api_key = '54d47f0a6d4a13edccfc82e1e50164d068a05b3f'
url_str = 'https://api.census.gov/data/2017/acs/acs5?get=B01001_001E,B01001_002E,NAME&for=county:*&in=state:51&key='+census_api_key
print(url_str)
response = request.urlopen(url_str) # read the response into computer

html_str = response.read().decode("utf-8")
book = xlwt.Workbook() # create a new excel file
sheet = book.add_sheet('va_pop') # add a new sheet named test
i=0
        
if html_str:
    json_data = json.loads(html_str)
    for record in json_data:
        total_pop, male_pop, name, state, count_num=record
        sheet.write(i,0,total_pop)
        sheet.write(i,1,male_pop)
        i +=1
book.save('census.xls')<jupyter_output><empty_output><jupyter_text>Step 4<jupyter_code>import xlrd
book = xlrd.open_workbook('census.xls')
sheet = book.sheet_by_name("va_pop")
for i in range(sheet.nrows):
    row = sheet.row_values(i)
    if i<10:
        print(row)<jupyter_output>['B01001_001E', 'B01001_002E']
['7022', '3437']
['49795', '25057']
['28033', '13849']
['19213', '9387']
['103218', '50653']
['12369', '6367']
['35265', '17341']
['5495', '2488']
['29048', '14200']<jupyter_text>Step 5<jupyter_code>from xlutils.copy import copy
read_book = xlrd.open_workbook('census.xls')
my_sheet = read_book.sheet_by_name('va_pop')
num_rows = my_sheet.nrows
write_book = copy(read_book)

write_sheet = write_book.get_sheet(0)

for i in range(num_rows):
    row = my_sheet.row_values(i)
    B01001_001E, B01001_002E=row
    if i ==0:
        write_sheet.write(i,2,'male/total ratio')
    else:
        write_sheet.write(i,2,int(B01001_001E)/int(B01001_002E))
        
write_book.save('census.xls')<jupyter_output><empty_output><jupyter_text>Step 6<jupyter_code>import xlrd
book = xlrd.open_workbook('census.xls')
sheet = book.sheet_by_name("va_pop")
for i in range(sheet.nrows):
    row = sheet.row_values(i)
    if i<10:
        print(row)<jupyter_output>['B01001_001E', 'B01001_002E', 'male/total ratio']
['7022', '3437', 2.0430608088449227]
['49795', '25057', 1.987269026619308]
['28033', '13849', 2.024189472164055]
['19213', '9387', 2.046766805156067]
['103218', '50653', 2.03774702386828]
['12369', '6367', 1.9426731584733783]
['35265', '17341', 2.0336197451127385]
['5495', '2488', 2.2086012861736335]
['29048', '14200', 2.0456338028169014]
{'fraction_non_alphanumeric': 0.15996718621821165, 'fraction_numerical': 0.18252666119770303, 'mean_word_length': 5.609756097560975, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 15, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '386697', 'license': 'MIT', 'n_tokens_mistral': 1307, 'n_tokens_neox': 1046, 'n_words': 170, 'path': 'Lab6.ipynb', 'repo_name': 'carlsondorman/IA241', 'size': '2438'}