Pandas: Style column header

Question

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 the 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.

Scott Boston · Accepted Answer · 2019-03-20 15:11:30Z

10

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:

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

edited Mar 20, 2019 at 15:11

answered Mar 20, 2019 at 15:00

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

2 Comments

Mr Curious Over a year ago

Thank you, but sadly it doesn't work. The exported excel file still only colors the column without the header. Do you know about any other way?

Scott Boston Over a year ago

Yep, this appears to be bug.

anky · Accepted Answer · 2019-10-30 07:14:05Z

1

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:

edited Oct 30, 2019 at 7:14

answered Oct 30, 2019 at 7:08

anky

75.3k11 gold badges46 silver badges76 bronze badges

Collectives™ on Stack Overflow

Pandas: Style column header

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related