PANDAS TO_HTML conditional FORMATTING

Question

I have a daily Pivot Table that I export to HTML

The code I use to create it is

HEADER = '''
<html>
    <head>
        <style>
            .df tbody tr:last-child { background-color: #FF0000; }
        </style>
    </head>
    <body>
'''
FOOTER = '''
    </body>
</html>
'''

with open("/home/testing_libs/htmlex.html", 'w') as f:
    f.write(HEADER)
    f.write(pivot1.to_html(classes='pivot1'))
    f.write(FOOTER)

The source is a PANDAs dataframe. I would like to create logic with PANDAs to have a column which does NOT appear in the Pivot table, but determines the color of the Pivot cells for Units. In other words, if I compare today's Pivot, or even its precursor Dataframe in terms of 'units', from the previous day, and the current day's 'units' are smaller, then I want that CELL in HTML 'RED' and not black. I don't know whether I can have HTML colored red based on NOT the 'units' values, but the associated positive/negative value comparing the same GROUP's 'units' values. Here is the dataframe that creates the above PIVOT TABLE

    widget  region  industry    units
0   oldschool   middle  tech    10
1   newschool   west    manufacturing   40
2   upandcomer  east    manufacturing   50
3   oldschool   west    manufacturing   40
4   newschool   east    manufacturing   30
5   upandcomer  middle  manufacturing   20
6   oldschool   middle  manufacturing   10
7   newschool   east    tech    30
8   upandcomer  east    tech    30

THEN to create PIVOT

pivot1 = pd.pivot_table(frame1,index=['region','widget','industry'])

Zedelghem · Accepted Answer · 2016-12-15 19:54:19Z

1

You could use np.where(condition, ifConditionTrue, ifConditionFalse) from numpy to update pivot1['units'] depending on the comparison against another column (or series) where you store previous day's units and .map() to colour the output with ANSI colour codes, eg.:

pivot1['units'] = np.where( (pivot1['units'] < pivot0['units']), (pivot1['units'] = pivot1['units'].map(lambda x: "\x1b[41m"+str(x)+"\x1b[m")), (pivot1['units'].map(lambda x: str(x)))

where pivot1 is the current day's dataframe, pivot0 is the previous day's dataframe, and the strange prefix and suffix are the ANSI colour code for black text on red background.

This, however, will make your 'units' integer values intro strings and thus make comparison between days impossible unless you convert them back to integers – so that's not the best strategy to use on your primary dataframe. I suggest regenerating it under a different name for HTML export.

pivot1_export = pivot1
pivot1_export['units'] = np.where( (pivot1['units'] < pivot0['units']), (pivot1_export['units'] = pivot1['units'].map(lambda x: "\x1b[41m"+str(x)+"\x1b[m")), (pivot1_export['units'].map(lambda x: str(x)))

You can repeat this strategy to add more colours for higher, unchanged, or specific values.

answered Dec 15, 2016 at 19:54

Zedelghem

111 bronze badge

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

2 Comments

PR102012 Over a year ago

Thanks this is good. But I've just also realized that in order to do the compare, the dataframes from today/yesterday must be same size. And so the groupings will differ by day; yesterday for instance, the East Region may have had no 'newschool' widget. So currently I get an Error, ValueError: Series lengths must match to compare

Zedelghem Over a year ago

In this setup I would keep the df dimensions stable, eg. filling rows for empty widgets with zeroes – unless they differ over time, ie. new come and old go away.

Collectives™ on Stack Overflow

PANDAS TO_HTML conditional FORMATTING

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