1

I have a dataframe like as below

test_id,status,total,cnt_days,age     
1,passed,234%,3,21          
2,passed,54%,5,29
11,failed,21%,4,35
15,failed,20%.21,6,57             
51,passed,23%,21,80     
75,failed,12%,32,43

df1 = pd.read_clipboard(sep=',')

My objective is to

a) Have dark border lines between rows and column using black color

b) Use Green color for header

c) Use Red color for rows where Total > 30%

d) convert the styled dataframe to a html object

e) Export the styled dataframe to a .xlsx excel file

So, with the help of this post, I tried the below

def highlight(row):
    if row['total'] > 30:
        return ['background-color: red'] * len(row)
    else:
        return [''] * len(row)

s = data.style.apply(highlight, axis=1)
    #data['Total'] = data['Total'].astype(str) + "%"
    s = s.set_properties(
    **{'border': '1px black solid !important'}).set_table_styles([{
        'selector': '.col_heading',
        'props': 'background-color: green; color: black;'
    }])
    output = s.to_html(index=False)

But this produces incorrect output with gaps between different cells and borders. Another problem is my Total column has % symbol in it. How can I use that to do > 30% check and finally also display the % symbol in output table.

So, I expect my output to be like as below. you can see how there are no gap in borders between each cell and rows. I want the output to be like an excel table.

enter image description here

The Great
  • 7,215
  • 7
  • 40
  • 128
  • I replied in the Github issue regarding the styling of the xlsx file: https://github.com/DeepSpace2/StyleFrame/issues/121. Styling HTML has never been in the scope of `styleframe` – DeepSpace May 23 '22 at 09:27

1 Answers1

3
s = s.set_properties(
    **{'border': '1px black solid !important'}).set_table_attributes(
    'style="border-collapse:collapse"').set_table_styles([{
        'selector': '.col_heading',
        'props': 'background-color: green; color: black; border-collapse: collapse; border: 1px black solid !important;'
    }])

output

enter image description here

Ze'ev Ben-Tsvi
  • 1,174
  • 1
  • 3
  • 7
  • great. how did you find out that `border-collapse` will give us borders between cells and rows? because I tried with borders forever but couldn't get the layout – The Great May 23 '22 at 12:05
  • how can we reduce the font size though? – The Great May 23 '22 at 12:06
  • I tried this `'font-size': '9pt'` but it changes only column values. How can change the size of dataframe columns/headers? – The Great May 23 '22 at 12:17
  • About the 'border-collapse' property, I found it on w3schools. I manage to set the fonts to the headers and table by adding them right after the 'border-collapse' property in both places('set_table_attributes' and 'set_table_styles'). If you want to style other elements, the default CSS classes (for the different selectors) can be found here: https://pandas.pydata.org/docs/reference/api/pandas.io.formats.style.Styler.set_table_styles.html – Ze'ev Ben-Tsvi May 23 '22 at 12:45