131 lines
4.1 KiB
Python
131 lines
4.1 KiB
Python
#!/usr/bin/env python
|
|
|
|
import os,sys
|
|
from openpyxl import Workbook, load_workbook
|
|
from pprint import pprint
|
|
|
|
wb = load_workbook('example.xlsx')
|
|
ws_active = wb.active
|
|
# print(len(wb.sheetnames))
|
|
# ws_results = wb["Results"]
|
|
|
|
# print(ws_active['C5'].value)
|
|
# print(ws_results.cell(row=2, column=3).value)
|
|
|
|
|
|
# for row in ws_active.iter_rows(min_row=1, max_col=3, max_row=7):
|
|
# for cell in row:
|
|
# print(cell.value)
|
|
|
|
|
|
# for col in ws_active.iter_cols(min_row=1, max_col=3, max_row=7):
|
|
# for cell in col:
|
|
# print(cell.value)
|
|
|
|
|
|
# part3
|
|
#Edit this cell for Step 1
|
|
#(1) Import openpyxl module
|
|
# import os,sys
|
|
# from openpyxl import Workbook, load_workbook
|
|
|
|
# #(2) Read the file name of the excel file from the input (e.g., censuspopdata.xlsx, test-2-2.xlsx),
|
|
# #and store it in a string variable filename
|
|
filename = 'censuspopdata_small.xlsx'
|
|
|
|
# #(3) Create a workbook object wb_census for the workbook of the excel file with the name in filename
|
|
wb_census = load_workbook(filename)
|
|
|
|
# #(4) Create a worksheet object sheet for the active sheet of the workbook
|
|
ws_active = wb_census.active
|
|
|
|
# #(5) print the number of rows in the sheet
|
|
# num_rows = ws_active.max_row
|
|
# print(f"Number of rows: {num_rows}")
|
|
|
|
|
|
#Edit this cell for Step 2
|
|
|
|
#(1) define the dictionary variable countyData with an empty initial value.
|
|
countyData = { }
|
|
|
|
#(2) write a for loop to traverse the row id from 2, 3, 4, ..., to the last row number (included) of the sheet.
|
|
last_row = ws_active.max_row
|
|
num_cols = ws_active.max_column
|
|
for row in ws_active.iter_rows(min_row=2, max_col=num_cols, max_row=last_row):
|
|
#(3) during the iteration of the loop: extract data of state, county, and pop for each row.
|
|
|
|
state = row[1].value
|
|
country = row[2].value
|
|
pop_2010 = row[3].value
|
|
census_tract = row[0].value
|
|
|
|
#(4) update countyData to compute the total number of census tracts, and the total value of populations.
|
|
if not(state in list(countyData.keys())):
|
|
countyData[state] = {}
|
|
|
|
if not(country in list(countyData[state].keys())):
|
|
countyData[state][country] = {'pop':0, "tracts":0}
|
|
|
|
countyData[state][country]['pop'] = countyData[state][country]['pop'] + pop_2010
|
|
countyData[state][country]['tracts'] = countyData[state][country]['tracts'] + 1
|
|
|
|
#(5) print the list of counties in state AL
|
|
# print(countyData)
|
|
|
|
# #(1) print the total populations of county Anchorage of state AK:
|
|
# pprint(countyData['AL']['Autauga']['pop'])
|
|
|
|
# #(2) print the total number of census tracts of county Union of state AR
|
|
# pprint(countyData['AL']['Autauga']['tracts'])
|
|
|
|
|
|
# ws_active.title = "Results"
|
|
# ws1 = wb.create_sheet("New Results")
|
|
# ws1['A2'] = "Forecasting"
|
|
# wb.save('new_results.xlsx')
|
|
|
|
# #Edit this cell for Step 1:
|
|
# # (1) Import openpyxl module;
|
|
from openpyxl import Workbook, load_workbook
|
|
|
|
# # (2) Read file name of the excel file (e.g., produceSales.xlsx, test-3-2.xlsx)
|
|
filename = './produceSales.xlsx'
|
|
|
|
# # (3) Create a workbook object stored in a variable ``wb_update`` by loading the workbook from the excel file;
|
|
wb_update = load_workbook(filename)
|
|
|
|
# # (4) Create a sheet object stored in a variable ``sheet_update`` by assigning it to the active sheet of the workbook loaded;
|
|
sheet_update = wb_update.active
|
|
|
|
# # (5) Create a dictionary object stored in a variable PRICE_UPDATES, which maps product type to updated prices
|
|
PRICE_UPDATES = {
|
|
"Celery": 1.19,
|
|
"Garlic": 3.07,
|
|
"Lemon": 1.27,
|
|
}
|
|
|
|
|
|
# # (6) print the number of rows and the number of columns in the sheet
|
|
last_row = sheet_update.max_row
|
|
num_cols = sheet_update.max_column
|
|
# print(last_row)
|
|
# print(num_cols)
|
|
|
|
#Edit this cell for Step 2:
|
|
#(1) write a for loop to traverse the row id from 2, 3, 4, ...,
|
|
# to the last row number (included).
|
|
for row in sheet_update.iter_rows(min_row=2, max_col=num_cols, max_row=last_row):
|
|
#(2) during each iteration of the loop,
|
|
# update the cost per unit if needed,
|
|
# according to PRICE_UPDATES.
|
|
p_type = row[0].value
|
|
if(p_type in PRICE_UPDATES.keys()):
|
|
row[1].value = PRICE_UPDATES[p_type]
|
|
|
|
#Edit this cell for Step 3:
|
|
#Write the updated workbook to a new excel file named "updatedProduceSales.xlsx"
|
|
wb_update.save('updatedProduceSales.xlsx')
|
|
|
|
print("done")
|