accy570lab_week13

Week 13 Lab ¶ RPA ¶ Robotic Process Automation (RPA), is a technology that uses software robots (or "bots") to automate repetitive, rule-based tasks within business processes. These bots are designed to mimic human interactions with digital systems and applications, such as data entry, transaction processing, and communication with other systems. RPA aims to improve efficiency, reduce errors, and free up human workers from mundane, time- consuming tasks. In [1]: import pandas as pd df_invoice = pd.DataFrame({'invoice_no':[1111,2222], 'invoice_date': ['11/13/2023','11/15/2023'], 'bill_to_name':['Kevin Lee', 'Alex Chen'], 'bill_to_address':['1 Main st., Urbana, IL 61820', '2 Main st., Urbana, IL 61820'], 'bill_to_phone':['217-1234567', '217-2345678'], 'ship_to_name':['Marco Lu', 'Jim Du'], 'ship_to_address':['1 Green st., Urbana, IL 61820', '2 Green st., Urbana, IL 61802'], 'ship_to_phone':['217-7654321', '217-8765432']}) df_invoice Out[1]: invoice_ no invoice_ date bill_to_ name bill_to_add ress bill_to_p hone ship_to _name ship_to_ad dress ship_to_p hone 0 1111 11/13/20 23 Kevin Lee 1 Main st., Urbana, IL 61820 217- 1234567 Marco Lu 1 Green st., Urbana, IL 61820 217- 7654321 1 2222 11/15/20 23 Alex Chen 2 Main st., Urbana, IL 61820 217- 2345678 Jim Du 2 Green st., Urbana, IL 61802 217- 8765432 In [2]: df_item = pd.DataFrame({'invoice_no':[1111,1111,2222,2222,2222], 'description':['ball', 'tee', 'wedge', 'putter', 'shoe'], 'quantity':[10, 10, 3, 1, 1], 'unit_price':[3, 0.1, 60, 200, 120]}) df_item Out[2]: invoice_no description quantity unit_price 0 1111 ball 10 3.0 1 1111 tee 10 0.1 2 2222 wedge 3 60.0

invoice_no description quantity unit_price 3 2222 putter 1 200.0 4 2222 shoe 1 120.0 Iterate DataFrame ¶ In [3]: for index, row in df_invoice.iterrows(): invoice_no = row.invoice_no print(invoice_no) 1111 2222 Exercise 1 Print out details of items for invoice 1111. • Select all records in df_item for invoice 1111 (use boolean mask, items = df_item[df_item.invoice_no==1111] ) • Iterate through the DataFrame items , print description, quantity and unit price of each item In [4]: df_item Out[4]: invoice_no description quantity unit_price 0 1111 ball 10 3.0 1 1111 tee 10 0.1 2 2222 wedge 3 60.0 3 2222 putter 1 200.0 4 2222 shoe 1 120.0 In [6]: items = df_item[df_item.invoice_no==1111] for index, row in items.iterrows(): print(row.description, row.quantity, row.unit_price) ball 10 3.0 tee 10 0.1 Exercise 2 Print out details of all invoices. • Loop through df_invoice • For each invoice • Print out invoice_no • Get items for the invoice • Loop though items, print description, quantity and unit price of each item In [9]:

• Ship to Address: F13 • Ship to Phone F14 • Item list starts from row 19 to row 21 • Description: column D • Quantity: column F • Unit Price: column G In [13]: df_invoice.head() Out[13]: invoice_ no invoice_ date bill_to_ name bill_to_add ress bill_to_p hone ship_to _name ship_to_ad dress ship_to_p hone 0 1111 11/13/20 23 Kevin Lee 1 Main st., Urbana, IL 61820 217- 1234567 Marco Lu 1 Green st., Urbana, IL 61820 217- 7654321 1 2222 11/15/20 23 Alex Chen 2 Main st., Urbana, IL 61820 217- 2345678 Jim Du 2 Green st., Urbana, IL 61802 217- 8765432 In [15]: for index, row in df_invoice.iterrows(): invoice_template = openpyxl.load_workbook('Invoice_Template.xlsx') sheet = invoice_template['Invoice Template'] invoice_no = row.invoice_no print(invoice_no) sheet['H5']=row.invoice_date sheet['H7']=row.invoice_no sheet['D12']=row.bill_to_name sheet['D13']=row.bill_to_address sheet['D14']=row.bill_to_phone sheet['F12']=row.ship_to_name sheet['F13']=row.ship_to_address sheet['F14']=row.ship_to_phone items = df_item[df_item.invoice_no==invoice_no] item_row=19 for i, r in items.iterrows(): print(r.description, r.quantity, r.unit_price) sheet[f'D{item_row}'] = r.description sheet[f'F{item_row}'] = r.quantity sheet[f'G{item_row}'] = r.unit_price item_row+=1 invoice_template.save(f'invoice_{invoice_no}.xlsx') 1111 ball 10 3.0 tee 10 0.1 2222 wedge 3 60.0 putter 1 200.0

shoe 1 120.0 Check Your Work ¶ Rerun the whole notebook. If you complete Ex3, the following code should print out "Invoices are created successfully!". In [6]: import os files = [f.lower() for f in os.listdir('.') if os.path.isfile(f)] if 'invoice_1111.xlsx' in files and 'invoice_2222.xlsx' in files: print('Invoices are created successfully!') else: print('Invoices not created!') Invoices not created!