Search code examples
pythonpandasdataframepyexcel

dataframe to excel with styling (background and color)

I have a data frame as below and want to apply two conditions for the styling and save into excel.

I could perform either of the condition at a time but not the both simultaneously.

input: dataframe (2 colums) and a given_list(index numbers)

condition_1: [highlight ('background-color: yellow') and red color ('color:red') but if type(column[0])!=int then blue color ('color:blue')] if row.numer in the given_list.

condition_2: if type(column[0])!=int then blue color ('color:blue')

data_frame={ 'column0': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6, 6: 7, 7: 8, 8: 9, 9: 10A, 10: 11B, 11: 12C, 12: 13, 13: 14, 14: 15, 15: 16, 16: 17, 17: 18, 18: 19A, 19: 20B, 20: 21, 21: 22, 22: 23, 23: 24, 24: 25, 25: 26, 26: 27}, 'column1': {0: 'A', 1: 'V', 2: 'T', 3: 'L', 4: 'G', 5: 'E', 6: 'S', 7: 'G', 8: 'G', 9: 'G', 10: 'L', 11: 'Q', 12: 'T', 13: 'P', 14: 'G', 15: 'G', 16: 'G', 17: 'L', 18: 'S', 19: 'L', 20: 'V', 21: 'C', 22: 'K', 23: 'A', 24: 'S', 25: 'G', 26: 'F'} }

given_list=[7,8,9,10,11,12,13,14,15,21,22] ### the index numbers of the dataframe

desired_output:enter image description here

What I tried:

def highlight(row, row_index):
    # print(type(row[0]))
    background_color = 'background-color: yellow'
    text_color='color:red'
    text_color_1='color:blue'
    
    highlited_rows=[f'{text_color}; {background_color}' if row.name in row_index else (f'{text_color_1}' if not isinstance(row[0], int) else '')for _, cell in enumerate(row)]


highlighted_df =df.style.apply(lambda row: highlight(row, row_index), axis=1) 
aligned_df=highlighted_df.set_properties(**{'text-align': 'center'})
aligned_df.to_excel('highlighted_dataframe.xlsx', engine='openpyxl', index=False)

output=enter image description here

I am not able to color the txt based on the both condition. How to apply both the conditions simultaneously so I can get the desired output? Any help will be appreciated.

Solution

  • I have got it resolved by defining the highlighting and coloring conditions more explicitly as below:

    df={ 'column0': {0: 1, 1: 2, 2: 3, 3: 4, 4: 5, 5: 6, 6: 7, 7: 8, 8: 9, 9: 10A, 10: 11B, 11: 12C, 12: 13, 13: 14, 14: 15, 15: 16, 16: 17, 17: 18, 18: 19A, 19: 20B, 20: 21, 21: 22, 22: 23, 23: 24, 24: 25, 25: 26, 26: 27}, 'column1': {0: 'A', 1: 'V', 2: 'T', 3: 'L', 4: 'G', 5: 'E', 6: 'S', 7: 'G', 8: 'G', 9: 'G', 10: 'L', 11: 'Q', 12: 'T', 13: 'P', 14: 'G', 15: 'G', 16: 'G', 17: 'L', 18: 'S', 19: 'L', 20: 'V', 21: 'C', 22: 'K', 23: 'A', 24: 'S', 25: 'G', 26: 'F'} }

first_list=[7,8,9,10,11,12,13,14,15,21,22] #row index

sec_list=df[~df.iloc[:, 0].astype(str).str.isdigit()].index.tolist() ## Makes a list of index having nonint value in column0

def highlight(row, first_list, sec_list):
background_color = ''
text_color=''

if row.name in sec_list and row.name not in first_list:
    text_color = 'color: blue'
    background_color = ''

elif row.name in first_list and row.name not in sec_list: 
    text_color = 'color: red'
    background_color = 'background-color: yellow'
    
elif row.name in first_list and row.name  in sec_list:
    text_color = 'color: blue'
    background_color = 'background-color: yellow'  
    
return [f'{text_color}; {background_color}' for _ in row]


highlighted_df =df.style.apply(lambda row: highlight(row, row_index,sec_list), axis=1) 
aligned_df=highlighted_df.set_properties(**{'text-align': 'center'})
aligned_df.to_excel('highlighted_dataframe.xlsx', engine='openpyxl', index=False)