0

I am trying to format a pandas series: for all values, I want to add thousands separators and limit decimal places to two. In addition, for negative values, instead of a - sign, I want to enclose it with parentheses. So output should look like '20,000.00', '(152,111.12)'etc.

I know the f-string method works because when I run

val = 20000
f'{val:,.2f}'

It gives me the correct result '20,000.00'. But when I tried to create a function and apply it to an entire column in Pandas:

def format_pnl(value):
    # Format the value as a string with thousand separators and 2 decimal places
    formatted_value = f"{value:, .2f}"
    # If the value is negative, wrap it in parentheses
    if value < 0:
        formatted_value = f"({formatted_value})"
    return formatted_value

I got the ValueError: Invalid format specifier. This is really mind boggling to me since the format specifier absolutely works. What am I missing here?

Bowen Liu
  • 1,065
  • 1
  • 11
  • 24

2 Answers2

1

You can do this quite easily and apply it to your specific column

import pandas as pd

df = pd.DataFrame([100000, 1113, 510101.05, 6456464, -846464.12])

def format_pnl(value):
    return f"{value:,.2f}" if value >= 0 else f"({abs(value):,.2f})"

# Specify the specific column you want here
df[0] = df[0].apply(format_pnl)

display(df)

    0
0   100,000.00
1   1,113.00
2   510,101.05
3   6,456,464.00
4   (846,464.12)
SimonT
  • 974
  • 1
  • 5
  • 9
1

You added a space between the comma and period.

Scott Hunter
  • 48,888
  • 12
  • 60
  • 101
  • My goodness. So that's it. Thanks Scott, It solved the problem. I was under the wrong impression that spaces don't matter in the formatting. Is space forbidden in python string formatting? I thought since I have `':,'` and `'.2f'` then it should work. Thanks again. – Bowen Liu Dec 23 '22 at 20:14