Why does Excel styling not work in Pandas?

import pandas as pd
import xlsxwriter
from datetime import datetime
import sys

path = sys.argv[1]
xl = pd.ExcelFile(path)
df = xl.parse("Sheet1")
df.columns = ['Nume', 'Tip de', 'Unit', 'Speciale Price', 'Suma de', 'Suma']


def highlight_max(x):
    return ['background-color: yellow' if v == x.max() else ''
        for v in x]

df.style.apply(highlight_max)
df.loc[-1] = ['Totul', '', '', '', df['Suma de'].sum(), df['Suma'].sum()]

I tried to apply highlight_max to columns

Answer

So I’m going to assume you want to apply your style change to your dataframe in python the way it is designed to be implemented and display the color modifications when you print your dataframe in console. The issue is (I’m assuming) that you are not using the IDE Jupyter Notebook(or any other web based IDE), where it is designed to be utilized. See comparison below.

Source: Pandas Styling Documentation

The style object is rendered in html where a web based IDE like Jupyter Notebook can read in and display the modifications.

This is running a simplification taken from documentation on Spyder IDE. Notice the output is a style object. When rendered you can see in the second output the html/css that Spyder IDE can’t interpret.

import pandas as pd

data1 = {'Nume': [1,-2,-3],
         'Tip de':[4,-5,-6],
         'Unit':[-7,-8,9],
         'Speciale Price': [10,11,12],
         'Suma de': [13,14,15],
         'Suma':[16,17,18]}

df1 = pd.DataFrame(data1)
print(df1)

def color_negative_red(val):
    """
    Takes a scalar and returns a string with
    the css property `'color: red'` for negative
    strings, black otherwise.
    """
    color = 'red' if val < 0 else 'black'
    return 'color: %s' % color

s = df1.style.applymap(color_negative_red)
s

Output[] = <pandas.io.formats.style.Styler at 0x18aaa2a6c50>

s.render()

Output[] = '<style  type="text/css" >n    #T_db7ac00c_6244_11e9_887c_74d4355eed37row0_col0 {n            color:  black;n            color:  black;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row0_col1 {n            color:  black;n            color:  black;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row0_col2 {n            color:  black;n            color:  black;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row0_col3 {n            color:  black;n            color:  black;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row0_col4 {n            color:  black;n            color:  black;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row0_col5 {n            color:  red;n            color:  red;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row1_col0 {n            color:  red;n            color:  red;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row1_col1 {n            color:  black;n            color:  black;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row1_col2 {n            color:  black;n            color:  black;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row1_col3 {n            color:  black;n            color:  black;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row1_col4 {n            color:  red;n            color:  red;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row1_col5 {n            color:  red;n            color:  red;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row2_col0 {n            color:  red;n            color:  red;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row2_col1 {n            color:  black;n            color:  black;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row2_col2 {n            color:  black;n            color:  black;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row2_col3 {n            color:  black;n            color:  black;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row2_col4 {n            color:  red;n            color:  red;n        }    #T_db7ac00c_6244_11e9_887c_74d4355eed37row2_col5 {n            color:  black;n            color:  black;n        }</style>  n<table id="T_db7ac00c_6244_11e9_887c_74d4355eed37" > n<thead>    <tr> n        <th class="blank level0" ></th> n        <th class="col_heading level0 col0" >Nume</th> n        <th class="col_heading level0 col1" >Speciale Price</th> n        <th class="col_heading level0 col2" >Suma</th> n        <th class="col_heading level0 col3" >Suma de</th> n        <th class="col_heading level0 col4" >Tip de</th> n        <th class="col_heading level0 col5" >Unit</th> n    </tr></thead> n<tbody>    <tr> n        <th id="T_db7ac00c_6244_11e9_887c_74d4355eed37level0_row0" class="row_heading level0 row0" >0</th> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row0_col0" class="data row0 col0" >1</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row0_col1" class="data row0 col1" >10</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row0_col2" class="data row0 col2" >16</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row0_col3" class="data row0 col3" >13</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row0_col4" class="data row0 col4" >4</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row0_col5" class="data row0 col5" >-7</td> n    </tr>    <tr> n        <th id="T_db7ac00c_6244_11e9_887c_74d4355eed37level0_row1" class="row_heading level0 row1" >1</th> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row1_col0" class="data row1 col0" >-2</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row1_col1" class="data row1 col1" >11</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row1_col2" class="data row1 col2" >17</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row1_col3" class="data row1 col3" >14</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row1_col4" class="data row1 col4" >-5</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row1_col5" class="data row1 col5" >-8</td> n    </tr>    <tr> n        <th id="T_db7ac00c_6244_11e9_887c_74d4355eed37level0_row2" class="row_heading level0 row2" >2</th> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row2_col0" class="data row2 col0" >-3</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row2_col1" class="data row2 col1" >12</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row2_col2" class="data row2 col2" >18</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row2_col3" class="data row2 col3" >15</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row2_col4" class="data row2 col4" >-6</td> n        <td id="T_db7ac00c_6244_11e9_887c_74d4355eed37row2_col5" class="data row2 col5" >9</td> n    </tr></tbody> n</table> '

The second example is running the exact same simplification on the web based IDE Jupyter Notebooks. Notice the resulting output from s displays the modified styling desired.