Python Pandas highlight row in Dataframe
12,722
Solution 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)
Solution 2
Rewrite of what alexg said
color = (df.meta == 'MM').map({True: 'background-color: yellow', False: ''})
df.style.apply(lambda s: color)
Author by
Admin
Updated on June 09, 2022Comments
-
Admin almost 2 years
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?