Apply background color to a row in excel using pandas in python-CodePudding

I have a simple test sheet and I want to give the title row a grey background color using pandas. After the change, save the result as a new file.

I tried to use the following code.

df_test = pd.read_excel(r'...\test.xlsx')
df_test = df_test.loc[1:1].style.apply('background-color: grey', axis = 1)
df_test.to_excel(r'...\test_1.xlsx', sheet_name='asdf', index = False)

But I only receive AttributeError: 'background-color : grey' is not a valid function for 'DataFrame' object. I already tried a few other variations of this to no avail.

CodePudding user response：

You can use

CodePudding user response：

Please try this ( tested ) :

Main point is, you need to use a styler and expect that is something like dataframe.

import pandas as pd
import numpy as np

df = pd.DataFrame(np.random.randn(10, 4), columns=list('ABCD'))

# see the data
display(df)

#pasted directly from : https://pandas.pydata.org/docs/reference/api/pandas.io.formats.style.Styler.apply.html
def highlight_max(x, color):
    return np.where(x == np.nanmax(x.to_numpy()), f"color: {color};", None)
df = pd.DataFrame(np.random.randn(5, 2), columns=["A", "B"])
df.style.apply(highlight_max, color='red')  
df.style.apply(highlight_max, color='blue', axis=1)  
styler = df.style.apply(highlight_max, color='green', axis=None)  

#see the styled result 
display(styler)

#to excel ( styler not df )
styler.to_excel("output.xlsx")

working example:

https://colab.research.google.com/drive/1zg0zpV7P67P7nrW8lG6uLenQ3N2EKWSF?usp=sharing

( it is not intuitive - and much likely you're not the only one , who stuck at this point , this is the curse of mixed , non OO, OO style coding of python ML ecosytem , none of numpy, pandas, ... are intuitive for beginners, they're for scientist ,who get used to formal tortures of academic hiearcy)