Using xlrd to read selected columns and all rows in python

Question

I would like to loop through an excel table and get the values for selected columns in each row in a set of lists or dictionary. if in a dictionary, for each row, the value in the first selected column would be the key and the values in the other selected columns would be the values (array) for that key. I cannot figure out how to tell python to read values from only selected columns...for the excel table may have 10 columns but I am only interest in three for example, and the three of interest are not contiguous. Would appreciate your insights using XLRD.

    import xlrd
    from xlrd import open_workbook
    import arcpy

    wb = open_workbook ("c:\\Users\\Admin\\Documents\\Marion\\Courses\\GEOG485\\FinalProject\\Data\\ExcelFiles\\Belize_Culvert_Nov15_V4.0.xlsx")

    sheet = wb.sheet_by_index(1)

    keys = [sheet.cell(0, 5).value for col_index in xrange(sheet.ncols)]

    dict_list = []
    for rownum in range(sheet.nrows):
        d = {keys[col_index]: sheet.cell(0, 5).value 
        for col_index in xrange(sheet.ncols)}:
            dict_list.append(d)

The field that I want to use as key is column 5 and the values are columns #16 and #17 as an array value for each key...

import xlrd from xlrd import open_workbook import arcpy wb = open_workbook ("c:\\Users\\Admin\\Documents\\Marion\\Courses\\GEOG485\\FinalProject\\Data\\ExcelFiles\\Belize_Culvert_Nov15_V4.0.xlsx") print wb.nsheets sheet = wb.sheet_by_index(1) keys = [sheet.cell(0, 5).value for col_index in xrange(sheet.ncols)] dict_list = [] for row_index in xrange(1, sheet.nrows): d = {keys[col_index]: sheet.cell(row_index, col_index).value for col_index in xrange(sheet.ncols)} dict_list.append(d) print dict_list — Marion
– Marion, Commented Jul 27, 2015 at 15:08
Please edit your question to include this code, and state what worked, and what didn't. — Grade 'Eh' Bacon
– Grade 'Eh' Bacon, Commented Jul 27, 2015 at 15:45

Zong · Accepted Answer · 2016-03-29 20:10:13Z

3

Use this command

row_slice(rowx, start_colx=0, end_colx=None)

Returns a slice of the Cell objects in the given row.

answered Mar 29, 2016 at 20:10

Zong

1,3891 gold badge8 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anand S Kumar · Accepted Answer · 2015-07-27 18:15:40Z

Some issues in your code -

keys = [sheet.cell(0, 5).value for col_index in xrange(sheet.ncols)] - This always takes the keys as the value in the first row and 6th column (Rows and columns are 0 indexed.)
d = {keys[col_index]: sheet.cell(0, 5).value - This is not even valid python syntax

You can just loop over all the rows, take column index 4 (5th column) as key and the rest in a list and add that to a dictionary, Example -

import xlrd
from xlrd import open_workbook
import arcpy

wb = open_workbook ("c:\\Users\\Admin\\Documents\\Marion\\Courses\\GEOG485\\FinalProject\\Data\\ExcelFiles\\Belize_Culvert_Nov15_V4.0.xlsx")

sheet = wb.sheet_by_index(1)
sheetdict = {}
for rownum in range(sheet.nrows):
    sheetdict[sheet.cell(rownum,4)] = [sheet.cell(rownum,15),sheet.cell(rownum,16)]

In the end, sheetdict has the required dictionary.

Collectives™ on Stack Overflow

Using xlrd to read selected columns and all rows in python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related