Count number of ROWS inside each COLUMN? package XLRD

Question

I started working with XLRD package for python 3.7.

I have a excel file that contains a fixed number of columns (20) but inside each column, the number of rows is changing (e.g.: first column has 21 rows, second column has 14 rows).

I wrote this:

for col in range(worksheet.ncols):
    rows_number= worksheet.nrows
    print(rows_number)

I'd like to know the number of rows for each column. With this code, I get 20 times (number of columns) the number of rows inside the first column. Actually I understand why. I'm iterating the nrows without changing the column. How to get number of rows for all the columns? If I try as follow, I get AttributeError since col doesn't have nrows attribute.

for col in range(worksheet.ncols):
    rows_number= col.nrows
    print(rows_number)

Thank you for your help!!

Answer 1

You can use list comprehension to get the non empty cells in a column, using col method in xlrd and comparing the Cell Type and then calculating its length

for colx in range(worksheet.ncols):
    non_emptycells=[i for i,x in enumerate(sheet.col(colx)) if x.ctype is not 0]
    print(len(non_emptycells))