Read drop down values from excel using pandas

Question

I have a excel with a drop down cell. I have been trying to read the excel dropdown list but it only reads the selected option.

import pandas

df = pandas.read_excel("BQA.xlsx", header=0)
df.columns = df.columns.str.strip()

print(df)

Output:

 Empty DataFrame
 Columns: [Column 1, Column 2, Column 3, Column 4, yes]
 Index: []

Expected Output:

Empty DataFrame
Columns: [Column 1, Column 2, Column 3, Column 4, [yes, no, yes1, no1]]
Index: []

This is an interesting question, hopefully someone will have answer. My gut says it not be available in python with xlrd etc. In .NET with excel-interop you want the cell validation property. see :stackoverflow.com/questions/25659888/… . One suggestion which may still be fruitless would be to look at xlwings. — Dickster
– Dickster, Commented Jun 18, 2018 at 23:12
Storing a list in a cell is usually a bad enough idea, but it's really hard to work with one in a column. Is there a saner storage method you'd settle for? — DSM
– DSM, Commented Jun 19, 2018 at 18:14
Ok, give me any method wherein I can read all the options of that cell — ReKx
– ReKx, Commented Jun 19, 2018 at 18:18
@ReKx not sure if you still care about this but what I did was store the lists of options in a dictionary then stored the keys to the options in the data frame. — Nathan Tew
– Nathan Tew, Commented Sep 30, 2019 at 8:42

DSM · Accepted Answer · 2018-06-19 18:32:12Z

5

You could use openpyxl to extract the dropdown information: it's stored in the data_validations for a given sheet. For example (newlines inserted for readability):

>>> wb = openpyxl.load_workbook("dropdown.xlsx")
>>> ws = wb["Sheet1"]
>>> ws.data_validations
<openpyxl.worksheet.datavalidation.DataValidationList object>
Parameters:
disablePrompts=None, xWindow=None, yWindow=None, count=1, 
dataValidation=[<openpyxl.worksheet.datavalidation.DataValidation object>
Parameters:
sqref=<MultiCellRange [E1]>, showErrorMessage=True, showDropDown=None, showInputMessage=True, 
allowBlank=False, errorTitle=None, error=None, promptTitle=None, prompt=None,
type='list', errorStyle=None, imeMode=None, operator=None, formula1='$L$4:$L$7', formula2=None]

I'm not going to handle all the possible cases, so this is just an example of the sorts of things you could do, but something like

def read_with_dropdown(book_name, sheet_name, range_str):
    wb = openpyxl.load_workbook(book_name)
    ws = wb[sheet_name]
    data = [[cell.value for cell in row] for row in ws[range_str]]

    validations = ws.data_validations.dataValidation
    for validation in validations:
        ranges = validation.sqref.ranges
        if len(ranges) != 1:
            raise NotImplementedError
        if validation.type == 'list':
            list_cells = ws[validation.formula1]
            values = [cell.value for cell_row in list_cells for cell in cell_row]
        else:
            raise NotImplementedError
        bounds = ranges[0].bounds
        try:
            data[bounds[1]-1][bounds[0]-1] = values
        except IndexError:
            pass
    return data

gives me (again, newlines inserted):

>>> data = read_with_dropdown("dropdown.xlsx", "Sheet1", "A1:E5")
>>> data
[['Column 1', 'Column 2', 'Column 3', 'Column 4', ['yes', 'no', 'yes1', 'no1']],
 [None, None, None, None, None],
 [None, None, None, None, None],
 [None, None, None, None, None],
 [None, None, None, None, None]]

answered Jun 19, 2018 at 18:32

DSM

355k67 gold badges606 silver badges504 bronze badges

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

4 Comments

ReKx Over a year ago

I get an error: ----> ranges = validation.sqref.ranges AttributeError: 'str' object has no attribute 'ranges'

Nathan Tew Over a year ago

Great answer, worked for me. Just had to remove your first if, else statement because my sheet had multiple ranges where there were drop down menus.

m_ocean Over a year ago

In my case all "list" type validations have such formula1 as "name6", "name8", "name19", etc...

m_ocean Over a year ago

Never mind! I've figured they're Defined Names. They define ranges in that case

Collectives™ on Stack Overflow

Read drop down values from excel using pandas

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related