How to style the pandas dataframe as an excel table (alternate row colour)?

Sample style:

Sample data:

import pandas as pd
import seaborn as snsdf = sns.load_dataset("tips")

If your final goal is to save to_excel, the only way to retain the styling after export is using the apply-based methods:

• df.style.apply / df.style.applymap are the styling counterparts to df.apply / df.applymap and work analogously
• df.style.apply_index / df.style.applymap_index are the index styling counterparts (requires pandas 1.4.0+)

For the given sample, use df.style.apply to style each column with alternating row colors and df.style.applymap_index to style all row/col indexes:

css_alt_rows = 'background-color: powderblue; color: black;'
css_indexes = 'background-color: steelblue; color: white;'(df.style.apply(lambda col: np.where(col.index % 2, css_alt_rows, None)) # alternating rows.applymap_index(lambda _: css_indexes, axis=0) # row indexes (pandas 1.4.0+).applymap_index(lambda _: css_indexes, axis=1) # col indexes (pandas 1.4.0+)
).to_excel('styled.xlsx', engine='openpyxl')

If you only care about the appearance in Jupyter, another option is to set properties for targeted selectors using df.style.set_table_styles (requires pandas 1.2.0+):

# pandas 1.2.0+
df.style.set_table_styles([{'selector': 'tr:nth-child(even)', 'props': css_alt_rows},{'selector': 'th', 'props': css_indexes},
])