Hi,
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}!â) `