Format pandas dataframe row wise

Question

I have the following dataframe and want to convert it to HTML

            Limit        Status     Warning      3M AVG
VAR1        1.20         1.21216    1.11         1.21235
VAR2        0.82         0.63075    0.75         0.593295
VAR3        0.38         0.376988   0.35         0.376988
VAR4        0.17         0.126987   0.14         0.12461

I want to format this dataframe row-wise such that:

1. If Status exceeds Warning the whole row becomes highlighted yellow and if it exceeds Limit the whole row becomes highlighted red
2. row VAR2 and VAR3 have "{:.2%}" format and VAR1 and VAR4 have "{:.2f}"

I've dug into pandas documentation and tried a couple of methods but I couldn't do all of the above tasks

I would appreciate if you could help since I believe it's a challenge for many pandas users to format a dataframe row wise.

Edit 1: I have tried the following code:

df=df.transpose()    
df.style.format("{:.2%}").format({"VAR1":"{:.2f},"VAR4":"{:.2f}"})

Note: by transposing the dataframe it is much easier to do all tasks but I cannot transpose it back to its original shape because it is styler.

Answer 1

I think you can do what you want with a custom Styling function:

def color(row):
    if row.Status >= row.Limit:
        return ['background-color: red'] * len(row)
    elif row.Status >= row.Warning:
        return ['background-color: yellow'] * len(row)
    return [''] * len(row)

df.style.apply(color, axis=1)

You will still have to add the custom number formats to this, though.

To get the HTML code for this, use the render method:

df.style.apply(color, axis=1).render()

<style  type="text/css" >
    #T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow0_col0 {
            background-color:  red;
        }    #T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow0_col1 {
            background-color:  red;
        }    #T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow0_col2 {
            background-color:  red;
        }    #T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow0_col3 {
            background-color:  red;
        }    #T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow2_col0 {
            background-color:  yellow;
        }    #T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow2_col1 {
            background-color:  yellow;
        }    #T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow2_col2 {
            background-color:  yellow;
        }    #T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow2_col3 {
            background-color:  yellow;
        }</style>  
<table id="T_e61b55e0_cef5_11e8_9f07_68f72880acdc" > 
<thead>    <tr> 
        <th class="blank level0" ></th> 
        <th class="col_heading level0 col0" >Limit</th> 
        <th class="col_heading level0 col1" >Status</th> 
        <th class="col_heading level0 col2" >Warning</th> 
        <th class="col_heading level0 col3" >3M AVG</th> 
    </tr></thead> 
<tbody>    <tr> 
        <th id="T_e61b55e0_cef5_11e8_9f07_68f72880acdclevel0_row0" class="row_heading level0 row0" >VAR1</th> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow0_col0" class="data row0 col0" >1.2</td> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow0_col1" class="data row0 col1" >1.21216</td> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow0_col2" class="data row0 col2" >1.11</td> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow0_col3" class="data row0 col3" >1.21235</td> 
    </tr>    <tr> 
        <th id="T_e61b55e0_cef5_11e8_9f07_68f72880acdclevel0_row1" class="row_heading level0 row1" >VAR2</th> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow1_col0" class="data row1 col0" >0.82</td> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow1_col1" class="data row1 col1" >0.63075</td> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow1_col2" class="data row1 col2" >0.75</td> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow1_col3" class="data row1 col3" >0.593295</td> 
    </tr>    <tr> 
        <th id="T_e61b55e0_cef5_11e8_9f07_68f72880acdclevel0_row2" class="row_heading level0 row2" >VAR3</th> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow2_col0" class="data row2 col0" >0.38</td> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow2_col1" class="data row2 col1" >0.376988</td> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow2_col2" class="data row2 col2" >0.35</td> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow2_col3" class="data row2 col3" >0.376988</td> 
    </tr>    <tr> 
        <th id="T_e61b55e0_cef5_11e8_9f07_68f72880acdclevel0_row3" class="row_heading level0 row3" >VAR4</th> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow3_col0" class="data row3 col0" >0.17</td> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow3_col1" class="data row3 col1" >0.126987</td> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow3_col2" class="data row3 col2" >0.14</td> 
        <td id="T_e61b55e0_cef5_11e8_9f07_68f72880acdcrow3_col3" class="data row3 col3" >0.12461</td> 
    </tr></tbody> 
</table>

Answer 2

I had the same problem and looked into implementation of the format function in pandas.io.formats.style.Styler class and implemented a similar row-wise function:

def format_row_wise(styler, formatter):
    for row, row_formatter in formatter.items():
        row_num = styler.index.get_loc(row)

        for col_num in range(len(styler.columns)):
            styler._display_funcs[(row_num, col_num)] = row_formatter
    return styler

Example:

df = pandas.DataFrame(
    {
        'Limit': [1.20, 0.82, 0.38, 0.17], 
        'Status': [1.21216, 0.63075, 0.376988, 0.126987], 
        'Warning': [1.11, 0.75, 0.35, 0.14], 
        '3M AVG': [1.21235, 0.593259, 0.376988, 0.12461]
    }, 
    index=['VAR1', 'VAR2', 'VAR3', 'VAR4']
)
formatters = {"VAR1":lambda x: f"{x:.2f}", "VAR4": lambda x: f"{x:.2f}"}
styler = format_row_wise(df.style, formatters)
styler.render()

This works for me :)

Note:

• I only implemented the dict-formatter!
• Format has to be given as function (here: lambda)

Hopefully, this gets you on the right way...