Color row based on a criteria and export with style

[SSMK-wq]

I have a sample dataframe like 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 as is to .xlsx file (excel file)

So, with the help of this [post][1], 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. Is there anyway to do this via StyleFrame? I also want to write the styled dataframe to an excel sheet with same format

[DeepSpace2]

Styling HTML has never been in the scope of styleframe.

However, styling the Excel file is straightforward if we keep in mind the percent column format. Here I'm using iterrows (which is usually not recommended because it is slow) but I'm sure there is a vectorized way to achieve the same:

def find_indexes(sf):
    return [index.value for index, row in sf.data_df.iterrows()
            if float(row['total'].value.split('%')[0]) > 30]

sf.apply_headers_style(Styler(bg_color='green'))
sf.apply_style_by_indexes(find_indexes(sf), Styler(bg_color='red'))
sf.to_excel('test.xlsx').save()

[SSMK-wq]

@DeepSpace2 - Thanks for your help. Is it possible to convert sf to pandas dataframe? So, I can convert df to html finally

[DeepSpace2]

The dataframe object is accessible through the data_df attribute.

[DeepSpace2]

Sorry, I fixed the example above. It should be

def find_indexes(sf):
    return [index.value for index, row in sf.data_df.iterrows()
            if float(row['total'].value.split('%')[0]) > 30]

[SSMK-wq]

Am not sure whether it is only me, who finds that non-colored rows font size seems higher when compared to colored rows. Is it expected? I didn't do any additional formatting. my code looks like below

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

from styleframe import StyleFrame, Styler, utils
default_style = Styler(font=utils.fonts.aharoni, font_size=14)
sf = StyleFrame(df, styler_obj=default_style)

def find_indexes(sf):
    return [index.value for index, row in sf.data_df.iterrows() 
            if float(row['total'].value.split('%')[0]) > 30]

sf.apply_headers_style(Styler(bg_color='green'))
sf.apply_style_by_indexes(find_indexes(sf), Styler(bg_color='red'))
sf.to_excel('test.xlsx').save()

Do you see that? but in your screenshot, it looks normal

[SSMK-wq]

I get an error TypeError: 'NoneType' object is not iterable when I try this on real dataframe. I unpacked the for loop for easy understandn=ing but not sure why it results in this error on real dataframe. Is it index issue? or due to my return statement?

def find_indexes(sf):
    for index, row in sf.data_df.iterrows():
        print("index is ", index)
        print("row is ", row)
        if float(row['total'].value.split('%')[0]) > 30:
            return index

Am looping through multiple files and getting index for each files, styling them. So, if a file doesn't have a total value > 30, would it result in this error?

[DeepSpace2]

You are returning from inside the for loop, and you are also returning the index, not a list. This will causes the function to return None if no row matches the condition.

[stale[bot]]

This issue has been automatically marked as stale because it has not had activity in the last 60 days.