dataframe to excel with styling (background and color)

Question

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:

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=

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.

Corralien · Answer 1 · 2023-05-23T05:13:25.487

2

You can chain style operations (take care to sort your conditions in the right order) and use sbuset keyword to limit the styling applied to the correct cells/rows/columns:

given_list = [7, 8, 9, 10, 11, 12, 13, 14, 15, 21, 22]
given_list2 = df.columns[df.iloc[0].map(lambda x: isinstance(x, str))]


(df.style.set_properties(**{'background-color': 'yellow', 'color': 'red'}, subset=given_list)
         .set_properties(**{'color': 'blue'}, subset=pd.IndexSlice['column0', given_list2])
         .set_properties(**{'text-align': 'center'})
         .to_excel('highlighted_dataframe.xlsx', engine='openpyxl', index=False))

edited May 23 '23 at 05:13

answered May 22 '23 at 09:08

Corralien

109,409
8
28
52

Thanks. from your output, still it doesn't blue the non integer part i.e. index(9,10,11) should be blue with yellow background – shivam May 23 '23 at 00:10
@shivam. Sorry I confused the 2 images. I fixed the problem, can you check please? – Corralien May 23 '23 at 05:14
Thanks. I got it done by myself. Lengthy though :) Please have a look below – shivam May 24 '23 at 00:25
I prefer use `subset` to limit the applied style ;) – Corralien May 24 '23 at 08:37

shivam · Accepted Answer · 2023-05-23T03:09:41.527

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)

score 0 · Answer 3 · answered May 23 '23 at 03:28

import pandas as pd
from openpyxl.styles import PatternFill
from openpyxl import Workbook

# create a dataframe
df = pd.DataFrame({
    'Fruit': ['Apple', 'Banana', 'Cherry', 'Dates'],
    'Quantity': [10, 20, 30, 40],
    'Price': [1.5, 2, 3, 4]
})

# create a workbook and worksheet
wb = Workbook()
ws = wb.active

# add data to the worksheet
for r in dataframe_to_rows(df, index=False, header=True):
    ws.append(r)

# highlight cells with prices greater than 2
for row in range(2, ws.max_row + 1):
    if ws.cell(row=row, column=3).value > 2:
        cell = ws.cell(row=row, column=3)
        cell.fill = PatternFill(start_color='FAC090', end_color='FAC090', fill_type='solid')

# save the workbook
wb.save('example.xlsx')

# This code will create an Excel file named example.xlsx with the dataframe data, and the cells with prices greater than 2 will be highlighted with an orange color using the PatternFill object.

dataframe to excel with styling (background and color)

3 Answers3