How can I put two values in one index of list to store it in excel file on row + cellvalue?

Question

I want to add two values in one index of list like

['18' 'PASS','19' 'FAIL','20' 'PASS','21' '' FAIL] 

and then put this value in excel sheet like 18th row put value 'PASS'.

Answer 1

If you want to have multiple values in "one index of a list" you can simply use a list of tuples:

myTupleList= [(1,"one"),(2,"two"),(3,"three")]

If you want to store something as excel table (which is in and of itself 2 dimensional), you would need at least 3 things:

myData = [(row_1,column_1,data_1), ..., (row_n,col_n,data_n)]

to enable arbitary placement inside a excel workboot sheet.

Lets create some demo data:

header = [ (0,idx,n) for idx,n in enumerate(["number","number^2","number^3"])] 

data = [ (n, 0, n) for n in range(0,30,3)]
data += [ (n, 1, n ** 2) for n in range(0,30,3)]
data += [ (n, 2, n ** 3) for n in range(0,30,3)]

# header: [(0, 0, 'number'), (0, 1, 'number^2'), (0, 2, 'number^3')]

# data: [(0, 0, 0),    (3, 0, 3),   (6, 0, 6),   (9, 0, 9),   (12, 0, 12), (15, 0, 15), 
#        (18, 0, 18),  (21, 0, 21), (24, 0, 24), (27, 0, 27), (0, 1, 0),   (3, 1, 9), 
#        (6, 1, 36),   (9, 1, 81),  (12, 1, 144),(15, 1, 225),(18, 1, 324),(21, 1, 441), 
#        (24, 1, 576), (27, 1, 729), (0, 2, 0),  (3, 2, 27),   (6, 2, 216), (9, 2, 729), 
#        (12, 2, 1728),(15, 2, 3375),(18, 2, 5832),(21, 2, 9261),(24, 2, 13824), 
#        (27, 2, 19683)]

And then write the data into workbooks:

writeXlsWorkbook("text.xls","computed Numbers", header, data)
writeXlsxWorkbook("text.xlsx","computed Numbers", header, data)

Writing old excel files (*.xls):

import xlwt    
def writeXlsWorkbook(filename:str, sheet:str, header:list, tupleData:list):
    """Write xls to filename, place data on sheet with sheet as name.

    'header' and 'tupleData' are 3 dimensional zero based tuples of (row,column, data). 
    If 'header' is given, 'tupleData' will be placed in the row below the header-row."""
    book = xlwt.Workbook()
    sh = book.add_sheet(sheet)

    addToRowsNr = 0
    if header:
        for row, col, data in header:
            sh.write(row , col , data)
        addToRowsNr = 1

    for row, col, data in tupleData:
        sh.write(row + addToRowsNr, col , data)

    book.save(filename)

Writing new excel files (*.xlsx):

import openpyxl 
def writeXlsxWorkbook(filename:str, sheet:str, header:list, tupleData:list):
    """Write xlsx to filename, place data on sheet with sheet as name.

    'header' and 'tupleData' are 3 dimensional zero based tuples of (row,column, data). 
    If 'header' is given, 'tupleData' will be placed in the row below the header-row."""
    book = openpyxl.Workbook()
    sh = book.active # get the one default sheet
    sh.title = sheet # rename it

    # sh.cell( ..) is 1-based, data is 0 based so we add one to row and col
    addToRowsNr = 0
    if header:
        for row, col, data in header:
            sh.cell(row=row+1, column=col+1, value=data) # ws['B4'] = "42" would work
        addToRowsNr = row+2

    for row, col, data in tupleData:
        sh.cell(row = row + addToRowsNr, column = col + 1, value = data)

    book.save(filename)

---

To write your data you would supply:

# zero based data, hence -1 on the row
writeXlsWorkbook("somename.xls","someSheetName",None, [(18-1,0, 'PASS'),
                          (19-1,0,'FAIL'),(20-1,0,'PASS'),(21-1,0,'FAIL')] )

writeXlsxWorkbook("somename.xlsx","someSheetName",None, [(18-1,0, 'PASS'),
                           (19-1,0,'FAIL'),(20-1,0,'PASS'),(21-1,0,'FAIL')] )

Links:

• openpyxl
• xlwt

Disclaimer: I took a peek at this answer for the old style excel writing and heavily adapted it.

Answer 2

You can make a list of tuples

For example

lst = [(18, 'Pass'), (19, 'Fail')]

You can access the values as

lst[1] --> (19, 'Fail')
lst[1][0] --> 19
lst[1][1] --> 'Fail'

Please Note:

• Tuples are immutable, so cannot add new elements
• Merging of tuples with the + operator you can add an element and it will create a new tuple

You can read more about this over here

Answer 3

Possible options are

1. One string with seperator

>>> list = ['18 PASS', '19 FAIL', '20 PASS']

>>> list[0].split()
['18', 'PASS']

>>> list[0].split()[0]
'18'

>>> list[0].split()[1]
'PASS'

>>> list = ['18|PASS', '19|FAIL', '20|PASS']

>>> list[0].split('|')
['18', 'PASS']

>>> list[0].split('|')[0]
'18'

>>> list[0].split('|')[1]
'PASS'

Here you store the information as 1 string with a separator, usually space. And whenever needed you parse it.

Caution: Though the separator can e any character or string, you should be extra careful that you separator should not appear in the information string.

For example, lets go out of your example and take age and name

>>> list = ['48 Torvalds', '62 Rossum', '62 Grady Booch']

>>> list[0].split()[1]
'Torvalds'

>>> list[1].split()[1]
'Rossum'

>>> list[2].split()[1]
'Grady'

Ouch!! you missed the last name Booch.

2. List of lists or list of tuples

As explained in previous answer by https://stackoverflow.com/users/3287355/user3287355

Remember tuples are immutable, once created you cannot alter the contents.

3. Recommended to use Python Dictionary

For key-value pairs Python has a built-in structure called Dictionary which analogous to Map, JSON etc.

>>> dict = {'18':'PASS', '19':'FAIL', '20':'PASS'}

>>> dict.get('18')
'PASS'

>>> dict.get('19')
'FAIL'

>>> dict.get('20')
'PASS'

Caution: Beware of duplicate keys. Also you can't expect the dictionary to preserve the order of elements as you entered. However its completely based on key-value, we don't get values by index.