Search code examples
pythonexceldataframeconditional-formattingstyleframe

Filter on conditional formatting in excel sheet using pandas

I have an excel file that has conditional formatting with red color in certain rows one of the columns. So my file looks like this

enter image description here

Now I have to apply the filter on the "College" column to delete all the rows that have a red-colored background.

enter image description here

And save it back to the file.

The code I wrote for this is:

dataFrame_file = pd.read_excel(util.comcastFile2Path(), sheet_name='Sheet1')  //comcastFile2Path() gives path of file
def only_cells_with_red_background(cell):
    return cell if cell.style.bg_color in {utils.colors.red, 'FFFF0000'} else np.nan

df=dataFrame_file.style.applymap(only_cells_with_red_background,subset=['College'])
util.mergeFile(dataFrame_file,df,util.comcastFile2Path)

And my util class method for merging and saving file looks like this

def mergeFile(dataFrame_file, delete_frame, comcastFileName):
    dataFrame_file = dataFrame_file.merge(delete_frame, how='left', indicator=True).query(
        '_merge == "left_only"').drop('_merge', 1)
    saveFile(dataFrame_file,comcastFileName)

When I do this the error I get is:

TypeError: Can only merge Series or DataFrame objects, a <class 'pandas.io.formats.style.Styler'> was passed

How can I move further with this?

Thanks in advance.

Solution

  • pd.read_excel does not read the style from the Excel file.

    Since you tagged the question with I believe you meant to read the file with StyleFrame.read_excel(util.comcastFile2Path(), sheet_name='Sheet1', read_style=True).

    Then you also don't need to use df.merge. You can just select the rows with no red background and save the new StyleFrame object:

    from StyleFrame import StyleFrame, utils

def no_red_background_cell(cell):
    return cell if cell.style.bg_color not in {utils.colors.red, 'FFFF0000'} else np.nan

sf = StyleFrame.read_excel(util.comcastFile2Path(), sheet_name='Sheet1', read_style=True)
sf = StyleFrame(sf.applymap(no_red_background_cell).dropna(axis=(0, 1), how='all'))
sf.to_excel('output.xlsx').save()