4
votes

I am using pandas styler to give some columns a background color, based on the name of the column header. While this works as intended, the background color of the column header doesn't change.

Here is the part in my script where thy style is applied:

def highlight_col(x):
    if x.name in added_columns:
        return ['background-color: #67c5a4']*x.shape[0]
    elif x.name in dropped_columns:
        return ['background-color: #ff9090']*x.shape[0]
    else:
        return ['background-color: None']*x.shape[0]



old = old.style.apply(highlight_col, axis=0)

Is there a way to apply the style.apply()-function not only to the cells below the column header, but the complete column including the column header?

Edit: For clarification here is a screenshot of the excel output: screenshot of excel output

"Header 2" should have the same background color as the cells below it.

2
To do the column headers look at using df.style.set_table_styles. - Scott Boston
Can show a sample image of your expected output? - Scott Boston
@ScottBoston in my undrestanding the set_table_styles changes all headers, but i only want to change specific ones. Also I added a screenshot. - Mr Curious
Okay. I added a solution. - Scott Boston

2 Answers

5
votes

Okay, I think I figured out a way to handle formatting a column header using html 'selectors':

Using much of your code as setup:

df = pd.DataFrame('some value', columns=['Header1','Header2','Header3'], index=np.arange(12))
added_columns = 'Header2'
dropped_columns = 'Header1'

def highlight_col(x):
    if x.name in added_columns:
        return ['background-color: #67c5a4']*x.shape[0]
    elif x.name in dropped_columns:
        return ['background-color: #ff9090']*x.shape[0]
    else:
        return ['background-color: None']*x.shape[0]


col_loc_add = df.columns.get_loc(added_columns) + 2
col_loc_drop = df.columns.get_loc(dropped_columns) + 2

df.style.apply(highlight_col, axis=0)\
  .set_table_styles(
     [{'selector': f'th:nth-child({col_loc_add})',
       'props': [('background-color', '#67c5a4')]},
     {'selector': f'th:nth-child({col_loc_drop})',
       'props': [('background-color', '#ff9090')]}])

Output:

enter image description here

Note: I am using f-string which is a Python 3.6+ feature.

0
votes

You can use np.vstack() to stack the column names like below and create a fresh dataframe to apply the function, then export to excel with header=False:

Using @Scott's data and piR's function, Setup:

df = pd.DataFrame('some value', columns=['Header1','Header2','Header3'], index=np.arange(12))
def f(dat, c='red'):
    return [f'background-color: {c}' for i in dat]

You can do:

pd.DataFrame(np.vstack((df.columns,df.to_numpy())),columns=df.columns).style.apply(
                  f,subset=['Header2']).to_excel('file.xlsx',header=False,index=False)

Output of excel file: enter image description here