Python Pandas highlight row in Dataframe

Question

I have a mysql query which returns a results table. Such as the below:

---

What I'm trying to achieve is to highlight the whole row when the last column's value is 'MM'.

I've used in the past a small snippet of JS code which would only work for individual cells, not the whole row.

---

My code is as such:

import pandas as pd
from pandas import DataFrame
from pandas.tseries.offsets import BDay
import sys
import mysql.connector
import time
import datetime
from datetime import date,timedelta
import os


## testing dates
currentdate = pd.datetime.today()
today = currentdate.strftime("%Y-%m-%d")
yesterday = currentdate - BDay(1)
yest = yesterday.strftime("%Y-%m-%d")
#print today
#print yest

## where to dump the file
develop_path = "/var/www/html/exDiv/"
report_file = "exDivReport." + today + ".html"

## function to get data and print out
def get_data_from_hk():
    cnx = mysql.connector.connect(
        user='some_user', 
        password='some_pass',
        host='some_host',
        database='some_database'
    )

    cursor = cnx.cursor()
    query = ("SELECT c.description, c.feedcode, date(exdividenddate), dividend, (case when c.metadata like '%3=MM%' then 'MM' when c.metadata is NULL then 'NM' when c.metadata not like '%3=MM%' then 'NM' else c.metadata end) as metadata FROM dividends d join contracts c using(contracttag) where d.kind=0 and d.exdividenddate=getNextTradingDate(1, curdate());")

    cursor.execute(query)
    dbdata_hk=cursor.fetchall()
    cnx.close()
    print dbdata_hk
    return dbdata_hk


def generate_report():
    report_colnames = ['description', 'feedcode', 'date(exdividenddate)', 'dividend', 'metadata'] 

    dbdata_list_hk = get_data_from_hk()
    df = pd.DataFrame(dbdata_list_hk, columns=report_colnames)

    ##test: if the last column contains 'MM' then highlight table row
    ##highlight = lambda x:  '<span class="market_make">{:,.0f}</span>'.format(x) if x = 'MM' else '{:,.0f}'.format(x)

    with open(os.path.join(develop_path,report_file), 'w') as f:
        f.write("<b>Stocks with ex-dividend on next trading day<b/><br/><br/>")
        f.write(df.to_html(
            index=False,
            ##the whole row needs to be highlighted
            ##formatters={('description', 'metadata'): highlight}, 
            escape=False,
            justify='center'
        ))
        f.write(style_text)

    return df

## colouring js
style_text = '''
<style>
.market_make {
    background-color: #FBB117;
}
</style>
<script>
var elements = document.getElementsByClassName('market_make');

for(var i=0; i<elements.length; i++) {
    elements[i].parentElement.setAttribute("bgcolor", "#FBB117");
}
</script>
'''

# run the report
d=generate_report()
print d

---

The html that pandas creates is partly this:

<b>Stocks with ex-dividend on next trading day<b/><br/><br/><table border="1" class="dataframe">
  <thead>
    <tr style="text-align: right;">
      <th>description</th>
      <th>feedcode</th>
      <th>date(exdividenddate)</th>
      <th>dividend</th>
      <th>metadata</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td>HHI Div</td>
      <td>HHI</td>
      <td>2015-07-02</td>
      <td>34.793976</td>
      <td>NM</td>
    </tr>
    <tr>
      <td>CCB</td>
      <td>939</td>
      <td>2015-07-02</td>
      <td>0.000000</td>
      <td>MM</td>
    </tr>
    <tr>
      <td>null Combo + Stock - Legs</td>
      <td>FXI-H</td>
      <td>2015-07-02</td>
      <td>35.327749</td>
      <td>NM</td>
    </tr>
    <tr>
      <td>CSE</td>
      <td>1088</td>
      <td>2015-07-02</td>
      <td>0.000000</td>
      <td>MM</td>
    </tr>
    <tr>
      <td>PIN</td>
      <td>1339</td>
      <td>2015-07-02</td>
      <td>0.011800</td>
      <td>NM</td>
    </tr>

---

Is there a more efficient way apart from JS to do this?

Can I combine it with some CSS maybe?

Any ideas please?

Answer 1

This can be done with pd.DataFrame.style.apply and a custom style function!

For example, to highlight the 'MM' rows yellow:

def custom_style(row):

    color = 'white'
    if row.values[-1] == 'MM':
        color = 'yellow'

    return ['background-color: %s' % color]*len(row.values)

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

Answer 2

Rewrite of what alexg said

color = (df.meta == 'MM').map({True: 'background-color: yellow', False: ''}) df.style.apply(lambda s: color)