I can't seem to update the style in a dataframe in Python

Question

enter code hereFirst, I don't know Python but am using it for a team project. I followed a plaid quickstart tutorial. I was able to create a dataframe with the enter code heredata I got back, use it to create an html and generate a pdf from that. It took me HOURS! Now I want to apply some basic formatting to the data, like making amounts red if they are negative, right aligning the amount field. I have the code written, but I am not using Jupyter Lab, I am using atom. I have seen a reference to needing style.render() in order to get the changes but don't know how to do it.

This is my code which shows no changes in color in the pdf

# Execute query selecting Transactions table in Plaid database
cur.execute("SELECT description, date, amount FROM transactions")

# Fetch rows
rows = cur.fetchall()

# Close cursor
cur.close()

# Close connection
con.close

# set negative amounts to red
def color_negative_red(amount):
    color = 'red' if amount < 0 else 'black'
    return 'color: %s' % color

# dataframe
df = pd.DataFrame (rows, columns = ["Transaction Description", "Date", "Amount"])
df.style.applymap(color_negative_red)

# convert data frame to <table> and display
html = df.to_html()

text_file = open("test.html", "w")
text_file.write(html)
text_file.close()

# output to a pdf
import pdfkit
pdfkit.from_file('test.html', 'out.pdf')

# open report and pdf in a browser
import webbrowser as wb
wb.open_new_tab('file://C:/Users/Carole/dev/Plaid/quickstart/python/out.pdf')

pdf generated from test sandbox environment:

Last changes: # set negative amounts to red def color_negative_red(s): color = 'red' if s < 0 else 'black' return 'color: %s' % color html = df.style.applymap(color_negative_red).render()

pd.set_option('display.max_rows', 500)

# print DataFrame
print (df)

text_file = open("test.html", "w")
text_file.write(html)
text_file.close()

# output to a pdf
import pdfkit
pdfkit.from_file('test.html', 'out.pdf')

This was my final change to get the code to work converting the amount string to a decimal

    from re import sub
    from decimal import Decimal

    new = s.replace("$","").replace(",","")
    value = Decimal(new)

    color = 'red' if value < 0 else 'black'
    return 'color: %s' % color

jkr · Accepted Answer · 2021-02-23 14:58:17Z

0

You evaluate the style but then don't do anything with it. You can use the .render() method of the styler to get HTML output.

html = df.style.applymap(color_negative_red).render()

Note that this is not a full HTML page. It includes an HTML table and style information, but no html, head, or body tags.

edited Feb 23, 2021 at 14:58

answered Feb 22, 2021 at 22:13

jkr

19.6k5 gold badges48 silver badges78 bronze badges

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

2 Comments

Carole Tierney Over a year ago

Thank you so much! That helped. Apparently my amount field is being treated as a string, so it doesn't like the conditional expression. When I changed it to color = 'red' if s == '$500.00' else 'black' I got some red values!!!

Carole Tierney Over a year ago

After figuring out how to strip the $ and commas out of the string and convert it to a decimal, my command worked and I am all set! def color_negative_red(s):

Collectives™ on Stack Overflow

I can't seem to update the style in a dataframe 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