Formatting column in pandas to decimal places using table.style based on value

user_10145772 0
Viewed 742

I am trying to format a column in a dataframe using style. So far I successfully used the styling for a fixed number of decimals:

mytable.style.format('{:,.2f}', pd.IndexSlice[:, ['Price']])

but I need to expand this to formatting based on value as this:

  • if value is >=1000, then format to zero decimal places
  • if value is between 1000 and 1, then format to two decimal places
  • if value is < 1, then format to five decimal places

Does anyone have a solution for this? Thank you!

pandas dataframe
3 Answers

Building upon @Code_beginner's answer – the callable should return formatted string as output:

def my_format(val):
    if val >= 1000:
        return f"{val:,.0f}"
    if val >= 1:
        return f"{val:,.2f}"
    return f"{val:,.5f}"

mytable.style.format({'Price': my_format})
user_57267680

What you are looking for is called "conditional formatting". In which you set the conditions like you described and return the right format. There are examples in the documentation, they used a lambda function there. But you can also create a normal function which might look something like this:

def customfunc(val):
    if val>=1000:
        format='{:,.0f}'
    if val<1000 and val>=1:
        format='{:,.2f}'
    if val<1:
        format='{:,.5f}'
    return format

df.style.format({0:customfunc})

This should style your first column like described in your problem. If the columns has a name you have to adjust it accordingly. If you have trouble see the documentation linked abve there are more examples.

user_148424680

Just to have it visually clear, this is how it looks now: enter image description here

That's my line of code:

df.style.format({'Price': customfunc})
user_101457720
Related