Pandas Dataframe Styles are not working with Jupyter Notebook

Question

I have a dataframe which is constructed using a list and other dataframe that i read from excel file. What I want to do is, I just have to apply the background color to first row of a dataframe which I would export in to an excel. The below code doing the job correclty as expected.(There is issue with the data)

The issue is the style which I have applied to the dataframe was not reflected in the excel sheet. I am using Jupyter Notebook. Please suggest a way to get the styles in excel.

import pandas as pd

sheet1 = r'D:\dinesh\input.xlsx'
sheet2 = "D:\dinesh\lookup.xlsx"
sheet3 = "D:\dinesh\Output.xlsx"
sheetname = 'Dashboard Índice (INPUT)'

print('Started extracting the Crime Type!')
df1 = pd.read_excel(sheet1,sheet_name = 'Dashboard Índice (INPUT)',skiprows=10, usecols = 'B,C,D,F,H,J,L,N,P', encoding = 'unicode_escape')

crime_type = list(df1.iloc[:0])[3:]
print(f'crime_types : {crime_type}')
df1  = (df1.drop(columns=crime_type,axis=1))
cols = list(df1.iloc[0])

print(f'Columns : {cols}')
df1.columns = cols
df1  = (df1[1:]).dropna()

final_data = []
for index, row in df1.iterrows():   
    sheetname   = (f'{row[cols[1]]:0>2d}. {row[cols[0]]}')    
    cnty_cd     = [row[cols[0]], row[cols[1]], row[cols[2]]]
    wb = pd.ExcelFile(sheet2)

    workbook = ''.join([workbook for workbook in wb.sheet_names if workbook.upper() == sheetname])         
    if workbook:
        df2 = pd.read_excel(sheet2, sheet_name = workbook, skiprows=7, usecols ='C,D,H:T', encoding = 'unicode_escape')

        df2_cols = list(df2.columns)  
        final_cols = cols + df2_cols
        df2 = df2.iloc[2:]        
        df2 = df2.dropna(subset=[df2_cols[1]])

        for index2, row2 in df2.iterrows():             
            if row2[df2_cols[1]].upper() in crime_type:
                s1       = pd.Series(cnty_cd)                
                df_rows  = (pd.concat([s1, row2], axis=0)).to_frame().transpose()             
                final_data.append(df_rows)
                break                                                                                 
    else:
        print(f'{sheetname} does not exists!')

df3 = pd.concat(final_data)
df3.columns = final_cols
df_cols     = (pd.Series(final_cols, index=final_cols)).to_frame().transpose()
df_final    = (pd.concat([df_cols,df3], axis=0, ignore_index=True, sort=False))
df_final.style.apply(lambda x: ['background: blue' if x.name==0 else '' for i in x], axis=1)
df_final.to_excel(sheet3, sheet_name='Crime Details',index=False,header = None)

print(f'Sucessfully created the Output file to {sheet3}!')

It's not directly related to your question, but why are you using .iterrows()? It's unidiomatic, and quite sluggish. Can you explain what operations you're performing? — AMC
– AMC, Commented Feb 5, 2020 at 19:39

Wayne · Accepted Answer · 2020-02-05 17:31:36Z

4

You need to export to excel the styled dataframe and not the unstyled dataframe and so you either need to chain your styling and sending to Excel together, similar to shown in the documentation here, or assign the styled dataframe and use that to send to Excel.
The latter could look like this based on your code:

df_styled = df_final.style.apply(lambda x: ['background: blue' if x.name==0 else '' for i in x], axis=1)
df_styled.to_excel(sheet3,, sheet_name='Crime Details',index=False, header = None, engine='openpyxl')

As described here you need either the OpenPyXL or XlsxWriter engines for export.

answered Feb 5, 2020 at 17:31

Wayne

10.7k13 gold badges79 silver badges239 bronze badges

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

2 Comments

Dinesh Kumar Over a year ago

I tried with your above code as well. but no luck. Still the styles are not applied in the sheet.

Wayne Over a year ago

Not all styles are capable of being passed to Excel. But this one should be, I thought. Not having access to your stuff I didn't test everything. Your original code wasn't going to work at all. The example I easily dug up doesn't have the going to Excel part and so I'll need to dig more into where I have actually passed formatting out to Excel and come up with a better test to merge you code into or compare to mine.

Collectives™ on Stack Overflow

Pandas Dataframe Styles are not working with Jupyter Notebook

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related