How to create a tabular data structure from a list of sublists with Gpread or XLWT?

Question

How do you create a tabular data structure form this list of sublists using GSpread or XLWT in which every item at index 0 is in the first column, every item at index 1 is in the second column, etc.?

For example, I'd like to put all the a's in the following list into column1, all the b's into column2, etc. In other words, I only want one value to one cell, so I'd like 'a' in the first cell in the first column, 'aa1' in the second cell of the first column, 'aa2' in the third cell of the first column, etc.

 lst = [[['a','b','c'],['aa1','bb1','cc1'],['aaa2','bbb2','ccc2']],[['a','b','c'],['aa1','bb1','cc1'],['aaa2','bbb2','ccc2']]]

This is what I have, which is using a for-loop, but I'm wondering if there is a different method where I could create one for loop that way I wouldn't have to manually create a for loop for every extra column.

    gc = gspread.login('username', 'password')
    sheet = gc.open("Curalate").sheet1

    row = 1
    for subsublist in lst[0]:
         sheet.update_cell(1,row,subsublist[0])
         row = row + 1
    row = 1 
    for subsublist in lst[0]:
         sheet.update_cell(2,row,subsublist[1])
         row = row + 1
             row = 1 
    for subsublist in lst[0]:
         sheet.update_cell(,row,subsublist[2])
         row = row + 13

also, if this were xlwt, it's exactly the same except sheet.udpate_cell would be replaced with sheet.write and it's organized sheet.write(row,column,datapoint) instead of (column, row, datapoint).

I'm not sure I grasp the structure of your initial list. Is the multiple nesting significant? In particular, are you trying to accumulate multiple values for each cell? — Peter DeGlopper, Feb 03 '14 at 01:05
The multiple nesting is significant only because I'd like to carry this out for multiple sheets in which each sublist would have its own sheet, and each sublists within a sublist would have its own column. I don't get into sheets here though, because I first one solve for the multiple column issue. I only want one value to one cell, so I'd like 'a' in the first cell in the first column, 'aa1' in the second cell of the first column, 'aa2' in the third cell of the first column, etc. I'll clarify above as well — Chris, Feb 03 '14 at 01:11

Peter DeGlopper · Accepted Answer · 2014-02-03T02:15:21.417

Based on the comment: "each sublist with have its own sheet", I think you can do this quite simply with a nested loop. I'm familiar with xlwt but not gspread, so I'll demonstrate with xlwt syntax:

# assume worksheet is an xlwt worksheet object
lst = [['a','b','c'],['aa1','bb1','cc1'],['aaa2','bbb2','ccc2']]
for (rownum, rowlist) in enumerate(lst):
    for (colnum, value) in enumerate(rowlist):
        worksheet.write(rownum, colnum, value)

Both enumerate and xlwt are 0-indexed (at least in the Python interface) so you don't need to change that. If gspread is 1-indexed (as the Excel human interface is), adding 1 to rownum and/or colnum as needed should correct that.

It sounds like you'll have more than one worksheet, depending on the number of two-dimensional sublists in your main list. Again, you can nest for that:

wb = xlwt.Workbook()
for (sheet_number, sublist) in enumerate(lst):
    ws = wb.add_sheet('sheet_%s', sheet_number + 1)
    for (rownum, rowlist) in enumerate(sublist):
        for (colnum, value) in enumerate(rowlist):
            ws.write(rownum, colnum, value)

How to create a tabular data structure from a list of sublists with Gpread or XLWT?

1 Answers1