Could you help me to insert a background color for a specific row?

Question

This is my data frame: My dataframe

I want at the column endingBalanceLC the total which corespond to the account 121(I have to make synt of 3 because I need just 3 digits) from accountNumber column to be colored in yellow. The problem is that I don't know exactly where this account and the aferent total are in my dataframe, because the position can always change,depends the excel.

This is my current code:

df=pd.read_excel("GLAB.xlsx")
left=df['accountNumber'].str[:3]
account=(left=='121')
def color(val):
    if val==account:
        color = 'yellow'
    elif val!=account:
        color = 'white'
   
    return 'background-color: %s' % color

df.style.applymap(color, subset=['endingBalanceLC'])

I didn't receive errors but I can colored the cell with this condition

Answer 1

Styling DataFrame HTML representation

I would divide your task into two steps: first make a helper column for marking rows to highlight, second format your dataframe based on the helper column and the column to highlight.

df = pd.read_excel("GLAB.xlsx")

# helper column to show wheather 'accountNumber' fulfills the specified condition
df['isAccount'] = df['accountNumber'].apply(lambda v: v.startswith('121'))

def highlight(x):
    # temporary empty dataframe with same columns and index
    _df = pd.DataFrame('', index=x.index, columns=x.columns)
    # fill columns filtered by isAccount value
    _df.loc[x['isAccount'], :] = ['', 'background-color: yellow']
    _df.loc[~x['isAccount'], :] = ['', 'background-color: white']
    return _df

df.style.apply(highlight, subset=['isAccount', 'endingBalanceLC'], axis=None)

This will create a styled HTML representation of your DataFrame.

Styling Excel output file

If however you need to save the DataFrame to xlsx format and format the output file, you need something different. Instead of a styler object, which works in editors that handle HTML tables, you need to format the Excel file itself, and use python code only as an interface. One module than is integrated with pandas and allows to format xlsx files is xlsxwriter. So you need something like the code below, though you'll probably need to modify the formula variable to fit your use case.

df = pd.DataFrame({
    'accountNumber': ['1012', '1012000', '105', '121'],
    'endingBalanceLC': [0.00, -102297590.00, 0.00, 6000.00],
    'financialPeriod': [12,12,12,12],
    'debitCreditIndicator': ['D', 'C', 'D','D']
})
df['isAccount'] = df['accountNumber'].apply(lambda v: v.startswith('121'))

writer = pd.ExcelWriter('final.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
workbook  = writer.book
worksheet = writer.sheets['Sheet1']
yellow = workbook.add_format({'bg_color': 'yellow'})
worksheet.conditional_format('C1:C250', 
{'type':'formula', 
'criteria': '=F1=TRUE',  
'format': yellow
})
writer.save()