Question

Consider the list of animals and their attributes given below cell (CELL_Q3_INPUT). The objective is to obtain the print outs in the subsequent cell (CELL_Q3_OUTPUT). Note that the attributes of each animal are 'name', 'species', 'color', and 'age'. You must use collections.namedtuple to add readable attribute references, which then will be used to generate the desired output in CELL_Q3_OUTPUT.

# CELL_Q3_INPUT

lassie = ('Lassie', 'dog', 'black', 12)

buddy = ('Buddy', 'pupper', 'red', 0.5)  

astro = ('Astro', 'doggo', 'grey', 15)

mrpb = ('Mr. Peanutbutter', 'dog', 'golden', 35)

bojack = ('BoJack Horseman', 'horse', 'brown', 52)

pc = ('Princess Carolyn', 'cat', 'pink', 34)

tinkles = ('Mr. Tinkles', 'cat', 'white', 7)

pupper = ('Bella', 'pupper', 'brown', 0.5)

doggo = ('Max', 'doggo', 'brown', 5)

seuss = ('The Cat in the Hat', 'cat', 'stripey', 27)

pluto = ('Pluto (Disney)', 'dog', 'orange', 3)

plu2o = ('Pluto (space)', 'planet', 'brownish', 4500000000)

yertle = ('Yertle', 'turtle', 'green', 130)

horton = ('Horton', 'elephant', 'blue', 79)

# CELL_Q3_OUTPUT

# Lassie is an old black dog who is 12 years old.

# Buddy is a young red pupper who is 0.5 years old.

# Astro is an old grey doggo who is 15 years old.

# Mr. Peanutbutter is an old golden dog who is 35 years old.

# BoJack Horseman is a 52-year-old non-canine brown horse.

# Princess Carolyn is a 34-year-old non-canine pink cat.

# Mr. Tinkles is a 7-year-old non-canine white cat.

# Bella is a young brown pupper who is 0.5 years old.

# Max is a young brown doggo who is 5 years old.

# The Cat in the Hat is a 27-year-old non-canine stripey cat.

# Pluto (Disney) is a young orange dog who is 3 years old.

# Pluto (space) is a 4500000000-year-old non-canine brownish planet.

# Yertle is a 130-year-old non-canine green turtle.

# Horton is a 79-year-old non-canine blue elephant.

Answer 1

Pre requisites:

install the required packages

python -m pip install xlwt

python -m pip install xlrd

python -m pip install collections

Explanation : The program will read an excel file and then process all cells under a column. The values in the cells are evaluated directly in the python environment thereby autmatically creating variables like lassie in the python running env. The exec command is used for this. Now we make the necessary changes and write to the sheet. In order to preserve the content of input values we need to copy the content of input cell values and paste in the new sheet. Then we will overwrite the existing sheet.

import xlwt 
from xlwt import Workbook 
import xlrd
from collections import namedtuple
# Create a named tuple called Animal 
Animal = namedtuple('Animal',['name','species','color','age'])
# Open the work book for reading
workbook = xlrd.open_workbook("input.xlsx")
readSheet = workbook.sheet_by_index(0)
rows = []
# Execute each line in the excel file which will create variables in python env
for i in range(readSheet.nrows):
        exec(readSheet.cell(i, 0).value);
# Now we have all the variables having the tuples. Convert the tuple to Animal tuple.
lassie = Animal(*lassie)
buddy = Animal(*buddy)
astro = Animal(*astro)
mrpb = Animal(*mrpb)
bojack = Animal(*bojack)
pc = Animal(*pc)
tinkles = Animal(*tinkles)
pupper = Animal(*pupper)
doggo = Animal(*doggo)
seuss = Animal(*seuss)
pluto = Animal(*pluto)
plu2o = Animal(*plu2o) 
yertle = Animal(*yertle) 
horton = Animal(*horton) 

# Create the workbook for writing the result
wb = Workbook() 
  
# add_sheet is used to create sheet. 
writeSheet = wb.add_sheet('Sheet1',cell_overwrite_ok=True)
# Write the input cell values to the new sheet  
for i in range(readSheet.nrows):
        writeSheet.write(i, 0, readSheet.cell(i, 0).value)
# Write each output to the next column in the sheet
lassie=lassie.name +' is an old '+ lassie.color + ' ' +  lassie.species +  ' who is ' +  str(lassie.age) + ' years old.'
buddy=buddy.name + ' is a young ' + buddy.color + ' ' + buddy.species + ' who is '+ str(buddy.age) + ' years old.'
astro=astro.name + ' is an old ' + astro.color + ' ' + astro.species + ' who is ' + str(astro.age) + ' years old.'
mrpb=mrpb.name + ' is an old ' + mrpb.color + ' ' +  mrpb.species + 'who is' + str(mrpb.age) + 'years old.'
bojack=bojack.name + ' is a ' + str(bojack.age) + '-year-old non-canine' + bojack.color + ' ' + bojack.species + '.'
pc=pc.name + ' is a ' + str(pc.age) + '-year-old non-canine ' + pc.color + ' ' +  pc.species + '.'
tinkles=tinkles.name + ' is a ' + str(tinkles.age) + '-year-old non-canine ' + tinkles.color + ' ' + tinkles.species + '.'
pupper= pupper.name + ' is a young ' + pupper.color + ' ' + pupper.species + ' who is '+ str(pupper.age) + ' years old.'
doggo=doggo.name + ' is a young ' + doggo.color + ' ' + doggo.species + ' who is '+ str(doggo.age) + ' years old.'
seuss=seuss.name + ' is a ' + str(seuss.age) + '-year-old non-canine' + seuss.color + ' ' + seuss.species + '.'
pluto=pluto.name + ' is a young ' + pluto.color + ' ' + pluto.species + ' who is '+ str(pluto.age) + ' years old.'
plu2o=plu2o.name + ' is a ' + str(plu2o.age) + '-year-old non-canine' + plu2o.color + ' ' + plu2o.species + '.'
yertle=yertle.name + ' is a ' + str(yertle.age) + '-year-old non-canine ' + yertle.color + ' ' + yertle.species + '.'
horton=horton.name + ' is a ' + str(horton.age) + '-year-old non-canine ' + horton.color + ' ' + horton.species + '.'
writeSheet.write(0, 1, lassie) 
writeSheet.write(1, 1, buddy)
writeSheet.write(2, 1, astro)
writeSheet.write(3, 1, mrpb)
writeSheet.write(4, 1, bojack)
writeSheet.write(5, 1, pc)
writeSheet.write(6, 1, tinkles)
writeSheet.write(7, 1, pupper)
writeSheet.write(8, 1, doggo)
writeSheet.write(9, 1, seuss)
writeSheet.write(10, 1, pluto)
writeSheet.write(11, 1, plu2o)
writeSheet.write(12, 1, yertle)
writeSheet.write(13, 1, horton)

# Overwrite the input sheet
wb.save('input.xls') 

The input cell

lassie = ('Lassie', 'dog', 'black', 12)

buddy = ('Buddy', 'pupper', 'red', 0.5)

astro = ('Astro', 'doggo', 'grey', 15)

mrpb = ('Mr. Peanutbutter', 'dog', 'golden', 35)

bojack = ('BoJack Horseman', 'horse', 'brown', 52)

pc = ('Princess Carolyn', 'cat', 'pink', 34)

tinkles = ('Mr. Tinkles', 'cat', 'white', 7)

pupper = ('Bella', 'pupper', 'brown', 0.5)

doggo = ('Max', 'doggo', 'brown', 5)

seuss = ('The Cat in the Hat', 'cat', 'stripey', 27)

pluto = ('Pluto (Disney)', 'dog', 'orange', 3)

plu2o = ('Pluto (space)', 'planet', 'brownish', 4500000000)

yertle = ('Yertle', 'turtle', 'green', 130)

horton = ('Horton', 'elephant', 'blue', 79)

The output