How to create a new list per Excel row in python3

Question

I have an Excel sheet with the data as pictured: Cisco Call Handlers

I'm working with Python 3 and reading data from this sheet to try and build a list for each row. I've tried using pandas and dictionaries and now I'm currently trying xlrd because I cant figure this out! I currently have the following code:

idx=1
i2 = 1 #skipping header row as that is where the keys are
list_of_values = []
for idx in range(sheet.nrows):
    row = sheet.row_values(idx)
    print(idx, row)
    idx += 1
    if idx == idx:
        for cell in row:
            list_of_values.append(sheet.row_values(i2))
print(list_of_values)#prints[['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'], ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3']]

The code prints as follows:

0 ['DisplayName', 'Extension', 0.0, 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0]
1 ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3']
2 ['TestDisplayName3', 6230.0, '', 3030.0, '', 4578.0, '', '', '', '', '', '']

but when printing list_of_values, it only repeatedly prints row with idx 1. I hope that makes sense. Please help as I am clearly a beginner in Python. I know my "list_of_variables" variable is wrong because I would need multiple variables to store each one of my rows, right? Is there a way to create a new variable per list/per row of the excel sheet to house the appropriate cells? such as: list_of_values1 = ['DisplayName', 'Extension', 0.0, 1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0]. list_of_values2 = ['TestDisplayName2', 3091.0, '', 8645.0, '', '', '', '', '', 'Agent007', '', 'TestDisplayName3'] list_of_values3 = ['TestDisplayName3', 6230.0, '', 3030.0, '', 4578.0, '', '', '', '', '', '']

do you really want to compare `idx` with itself in that if statement? — Anuvrat Parashar, Oct 01 '18 at 03:50
did you mean per row of the excel file? Could you please elaborate with a sample output along side the sample input? — Anuvrat Parashar, Oct 01 '18 at 03:53
I'm not sure of the best way to do it, comparing idx to itself was the best way I could think of. So ideally, if idx = 0, it would build a list for row 1, if idx = 1, it would build a list for row 2, and so on. I compared idx to itself so no data from row 0 goes to row 1 or vice versa. — Gakusei, Oct 01 '18 at 04:11
three is only one variable `idx`. so that condition will always be true, wouldn't it? — Anuvrat Parashar, Oct 01 '18 at 04:14

Anuvrat Parashar · Accepted Answer · 2018-10-01T05:45:34.500

0

Is this what you are trying to achieve?

values = dict()
for idx in range(1, sheet.nrows):
    values[idx] = sheet.row_values(idx)

for key, value in values.items():
    print(key, value)

Here every value corresponds to every row in the spreadsheet.

Original response

Perhaps you intended to write

        list_of_values.append(sheet.row_values(idx))

instead of

        list_of_values.append(sheet.row_values(i2))

edited Oct 01 '18 at 05:45

answered Oct 01 '18 at 04:16

Anuvrat Parashar

2,960
5
28
55

if I replace i2 with idx, it says "IndexError: list index out of range". – Gakusei Oct 01 '18 at 04:31
so I know my "list_of_variables" variable is wrong because I would need multiple variables to store each one of my rows, right? so is there a way to create a new variable per list/per row of the excel sheet to house the appropriate cells? – Gakusei Oct 01 '18 at 04:36
`IndexError` comes when you try to access an element of a list that does not exist. like accessing the 10 element of a list of size 5. Could you try removing the idx += 1 statement and try again? – Anuvrat Parashar Oct 01 '18 at 04:37
> new variable per row? You want a [dictionary](https://docs.python.org/2/tutorial/datastructures.html#dictionaries). – Anuvrat Parashar Oct 01 '18 at 04:38
thank you, I will try with the dictionary again. The problem with that for me is accessing the value of a key as seen here: https://stackoverflow.com/questions/52507860/accessing-dictionary-items-by-position-in-python-3-6-efficiently. The API I need this for cannot take key/value pairs. – Gakusei Oct 01 '18 at 04:44
if you are open to other libraries https://openpyxl.readthedocs.io/en/stable/ is something I have successfully used in the past. – Anuvrat Parashar Oct 01 '18 at 06:02

How to create a new list per Excel row in python3

1 Answers1