2

I am creating an excel file with multiple sheets using xlsxwriter as engine.

In each sheet the row color is based on value of column named colour

But the color is not visible in my excel file.

import pandas as pd

def row_colour(row):
    return ['background-color:'+row.colour.lower()for i in row]


writer = pd.ExcelWriter('try.xlsx', engine='xlsxwriter')

cols = ['subject','colour']
df1 = pd.DataFrame([['Math','DarkRed'],['Science','Yellow']],columns=cols)

df2 = pd.DataFrame([['English','Orange'],['History','Green']],columns=cols)

df3 = pd.DataFrame([['Geography','DarkRed'],['Civic','Yellow']],columns=cols)

df1.style.apply(row_colour,axis=1)
df2.style.apply(row_colour,axis=1)
df3.style.apply(row_colour,axis=1)

df1.to_excel(writer, sheet_name='Sheet 1')
df2.to_excel(writer, sheet_name='Sheet 2')
df3.to_excel(writer, sheet_name='Sheet 3')

writer.save()

In output no color is visible:

enter image description here

The accepted answer is right for the above question.

I have improved the task by deleting the color column since it's only use was to color the rows.

Code for it:

import pandas as pd

def row_colour(table,color):
    print("table: \n "+str(table))
    print("table shape : "+str(table.shape))
    color_data = []

    for index,row in table.iterrows():
        color.iloc[index]
        if str(color.iloc[index]['colour']) == "DarkRed":
            c= 'background-color:red'
        else:
            c= 'background-color:'+str(color.iloc[index]['colour'])
        color_data.append([c for i in range(len(row))])


    return pd.DataFrame(color_data,index=table.index, columns=table.columns)


writer = pd.ExcelWriter('try.xlsx', engine='xlsxwriter')

cols = ['subject','colour']
df1 = pd.DataFrame([['Math','DarkRed'],['Science','Yellow']],columns=cols)

df2 = pd.DataFrame([['English','Orange'],['History','Green']],columns=cols)

df3 = pd.DataFrame([['Geography','DarkRed'],['Civic','Yellow']],columns=cols)

color = pd.DataFrame(columns=['colour'])
color['colour']=df1['colour']
df1 = df1.drop(['colour'],axis=1)
df1=df1.style.apply(row_colour,axis=None,color=color)

color = pd.DataFrame(columns=['colour'])
color['colour']=df2['colour']
df2=df2.drop(['colour'],axis=1)
df2=df2.style.apply(row_colour,axis=None,color=color)

color = pd.DataFrame(columns=['colour'])
color['colour']=df3['colour']
df3=df3.drop(['colour'],axis=1)
df3=df3.style.apply(row_colour,axis=None,color=color)

df1.to_excel(writer, sheet_name='Sheet 1')
df2.to_excel(writer, sheet_name='Sheet 2')
df3.to_excel(writer, sheet_name='Sheet 3')

writer.save()
Improve this question

2 Answers 2

Reset to default
4

The function is ok, you just have to reassign df1, df2, df3. This should work:

import pandas as pd

def row_colour(row):
    return ['background-color:'+row.colour.lower()for i in row]


writer = pd.ExcelWriter('try.xlsx', engine='xlsxwriter')

cols = ['subject','colour']
df1 = pd.DataFrame([['Math','DarkRed'],['Science','Yellow']],columns=cols)
df2 = pd.DataFrame([['English','Orange'],['History','Green']],columns=cols)
df3 = pd.DataFrame([['Geography','DarkRed'],['Civic','Yellow']],columns=cols)

df1 = df1.style.apply(row_colour,axis=1)
df2 = df2.style.apply(row_colour,axis=1)
df3 = df3.style.apply(row_colour,axis=1)

df1.to_excel(writer, sheet_name='Sheet 1')
df2.to_excel(writer, sheet_name='Sheet 2')
df3.to_excel(writer, sheet_name='Sheet 3')

writer.save()

to_excel here is a method of pandas.io.formats.style.Styler rather than the original dataframe.

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

1 Comment

Can you also help me with this: I want to drop the colour colummn after colouring but since it becomes object of pandas.io.formats.style.Styler can't drop the colummn
0

As an answer to your comment, I came up with a more complex solution. The colours are now read from the DataFrame before being dropped. Then passed as an argument to a row-colouring function. The key points are my use of zip and pd.IndexSlice for subsetting df.style.apply. I hope this suits your colouring needs.

import pandas as pd

def colour_row(row, colour):
    return ['background-color:'+ colour.lower() for i in row]

def colour_df(df, colour_col):
    colours = list(df['colour'])
    df = df.drop('colour', axis = 1)
    coloured_df = df.style
    for i, colour in zip(range(len(df)), colours):
        coloured_df = coloured_df.apply(colour_row, axis=1, subset=pd.IndexSlice[i,:], colour=colour)
    return coloured_df

writer = pd.ExcelWriter('try.xlsx', engine='xlsxwriter')

cols = ['subject','colour']
df1 = pd.DataFrame([['Math','DarkRed'],['Science','Yellow']],columns=cols)
df2 = pd.DataFrame([['English','Orange'],['History','Green']],columns=cols)
df3 = pd.DataFrame([['Geography','DarkRed'],['Civic','Yellow']],columns=cols)


sheet_num = 1
for df in [df1, df2, df3]:
    sheet_name = 'Sheet ' + str(sheet_num)
    df = colour_df(df, 'colour')
    df.to_excel(writer, sheet_name = sheet_name)
    sheet_num += 1
writer.save()
Improve this answer

1 Comment

Thanks for your attempt. I too got it. You can see bottom part of my question I have updated it with that code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.