How do I style an HTML pandas dataframe by cell in python

Question

I'm emailing myself a table of stocks and data for those stocks each morning using a python code, and want to style the table to show a green or red value depending on certain parameters. for example

columns = ['Name', 'Ticker', 'Quarter', 'EPS Estimate', 'Actual EPS', 'Suprise %', "Yearly PE", "P/S Ratio", "P/B Ratio", "Quarterly PE Ratio", "Price"]
newinfolist = [['American International Group Inc.', 'AIG', '12/31/2021', '1.19', '1.58', '+0.39 (32.94%)', 'N/A', '0.75', '0.50', '9.49', '59.95'], ['Albemarle Corp.', 'ALB', '12/31/2021', '1.00', '1.01', '+0.01 (1.35%)', '65.63', '5.04', '3.69', '54.08', '218.49'], ['American Water Works Co.', 'AWK', '12/31/2021', '0.86', '1.04', '+0.18 (21.33%)', '38.37', '7.40', '4.31', '35.56', '147.92'], ['Analog Devices Inc.', 'ADI', '1/31/2022', '1.80', '1.94', '+0.14 (7.87%)', '44.42', '9.51', '2.40', '21.41', '166.13'], ['Applied Materials Inc.', 'AMAT', '1/31/2022', '1.86', '1.89', '+0.03 (1.87%)', '20.68', '5.45', '9.95', '18.89', '142.83'], ['Charles River Laboratories International Inc.', 'CRL', '12/31/2021', '2.43', '2.49', '+0.06 (2.39%)', '45.98', '4.36', '5.92', '30.72', '306.0'], ['Cisco Systems Inc.', 'CSCO', '1/31/2022', '0.81', '0.84', '+0.03 (3.70%)', '21.56', '4.71', '5.66', '16.52', '55.5'], ['Copart Inc.', 'CPRT', '1/31/2022', '1.07', '1.10', '+0.03 (2.38%)', '31.12', '13.12', '9.87', '28.53', '125.52'], ['Equinix Inc.', 'EQIX', '12/31/2021', '1.40', '1.36', '-0.04 (-3.08%)', '160.55', '10.56', '5.99', '131.09', '713.14'], ['Eversource Energy', 'ES', '12/31/2021', '0.93', '0.91', '-0.02 (-1.63%)', '23.72', '3.30', '2.11', '22.09', '80.42'], ['Garmin Ltd.', 'GRMN', '12/31/2021', '1.44', '1.55', '+0.11 (7.66%)', '23.83', '5.51', '4.17', '18.96', '117.56'], ['Generac Holdings Inc.', 'GNRC', '12/31/2021', '2.42', '2.51', '+0.09 (3.83%)', '50.23', '5.83', '10.28', '31.03', '311.56'], ['Hilton Worldwide Holdings Inc.', 'HLT', '12/31/2021', '0.72', '0.72', '0.00 (-0.02%)', 'N/A', '7.11', 'N/A', '53.52', '154.13'], ['Host Hotels & Resorts Inc.', 'HST', '12/31/2021', '-0.08', '0.45', '+0.53 (-659.61%)', 'N/A', '6.37', '1.63', '10.83', '19.5'], ['Kraft Heinz Co.', 'KHC', '12/31/2021', '0.63', '0.79', '+0.16 (25.25%)', '119.80', '1.64', '0.85', '11.56', '36.53'], ['Marathon Oil Corp.', 'MRO', '12/31/2021', '0.55', '0.77', '+0.22 (38.91%)', 'N/A', '1.71', '0.50', '6.89', '21.23'], ['NVIDIA Corp.', 'NVDA', '1/31/2022', '1.23', '1.32', '+0.09 (7.70%)', '138.87', '19.57', '19.07', '48.54', '256.3'], ['Pioneer Natural Resources Co.', 'PXD', '12/31/2021', '3.99', '4.58', '+0.59 (14.84%)', 'N/A', '2.68', '1.62', '12.25', '224.35'], ['Synopsys Inc.', 'SNPS', '1/31/2022', '2.38', '2.40', '+0.02 (0.84%)', '61.33', '12.48', '9.63', '33.19', '318.59'], ['Tyler Technologies Inc.', 'TYL', '12/31/2021', '1.74', '1.75', '+0.01 (0.48%)', '99.68', '16.23', '8.91', '63.5', '444.51'], ['Vulcan Materials Co.', 'VMC', '12/31/2021', '1.17', '1.25', '+0.08 (6.44%)', '42.63', '4.07', '3.26', '38.21', '191.06'], ['Wabtec', 'WAB', '12/31/2021', '1.17', '1.18', '+0.01 (1.09%)', '40.47', '1.84', '1.37', '20.13', '95.0']]
table = pd.DataFrame(data=newinfolist, columns=columns)
htmltable = table.to_html()

the first three rows of the table looks like:

   Name    Ticker  Quarter EPS Estimate    Actual EPS  Suprise %   Yearly PE   P/S Ratio   P/B Ratio   Quarterly PE Ratio  Price
0   American International Group Inc.   AIG 12/31/2021  1.19    1.58    +0.39 (32.94%)  N/A 0.75    0.50    9.49    59.95
1   Albemarle Corp. ALB 12/31/2021  1.00    1.01    +0.01 (1.35%)   65.63   5.04    3.69    54.08   218.49
2   American Water Works Co.    AWK 12/31/2021  0.86    1.04    +0.18 (21.33%)  38.37   7.40    4.31    35.56   147.92

how would I format the Yearly PE row to be green if the cell value is less than 20? and then how would i do that for every other column in the table with other parameters?

If you have any solutions id be grateful for the help thanks

NotAName · Accepted Answer · 2022-02-22 02:22:32Z

2

When converting dataframe to html you can use optional "formatters" parameter (https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_html.html) and in your case you could, for example do something like:

df.to_html('111.html', formatters={
    'Yearly PE': lambda x: '<p style="color:green">' + x + '</p>' if x < 20 else x
    }, escape=False)

But here you need to ensure that your column is numerical, otherwise errors will happen. You can also manually define a function that will return the color if you want something more complicated:

def get_cell_color(val):
    if val < 20:
        return '<p style="color:green">' + val + '</p>'
    elif 20 < val < 40:
        return '<p style="color:red">' + val + '</p>'
    else:
        return val

In that case export will be simplified:

df.to_html('111.html', formatters={
    'Yearly PE': lambda x: get_cell_color(x)
    }, escape=False)

edited Feb 22, 2022 at 2:22

answered Feb 22, 2022 at 2:11

NotAName

4,4744 gold badges39 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SpeedyLinguine Over a year ago

Oh Thank you so much! Do you know how i would use that formatting to have different styles and different formatters for each column in the dataframe?

NotAName Over a year ago

@SpeedyLinguine, in the formatters dictionary you need to have the column name as the key and a formatting function as the value just like in the example.

Collectives™ on Stack Overflow

How do I style an HTML pandas dataframe by cell in python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related