7

I have the following data frame (consisting of both negative and positive numbers):

df.head()
Out[39]: 
    Prices
0   -445.0
1  -2058.0
2   -954.0
3   -520.0
4   -730.0

I am trying to change the 'Prices' column to display as currency when I export it to an Excel spreadsheet. The following command I use works well:

df['Prices'] = df['Prices'].map("${:,.0f}".format)

df.head()
Out[42]: 
    Prices
0    $-445
1  $-2,058
2    $-954
3    $-520
4    $-730

Now my question here is what would I do if I wanted the output to have the negative signs BEFORE the dollar sign. In the output above, the dollar signs are before the negative signs. I am looking for something like this:

  • -$445
  • -$2,058
  • -$954
  • -$520
  • -$730

Please note there are also positive numbers as well.

Kevin
  • 1,659
  • 5
  • 16
  • 22

2 Answers2

5

You can use the locale module and the _override_localeconv dict. It's not well documented, but it's a trick I found in another answer that has helped me before. 

import pandas as pd
import locale

locale.setlocale( locale.LC_ALL, 'English_United States.1252')
# Made an assumption with that locale. Adjust as appropriate.
locale._override_localeconv = {'n_sign_posn':1}

# Load dataframe into df
df['Prices'] = df['Prices'].map(locale.currency)

This creates a dataframe that looks like this:

      Prices
0   -$445.00
1  -$2058.00
2   -$954.00
3   -$520.00
4   -$730.00
Community
  • 1
  • 1
Andy
  • 49,085
  • 60
  • 166
  • 233
4

You can use np.where and test whether the values are negative and if so prepend a negative sign in front of the dollar and cast the series to a string using astype:

In [153]:
df['Prices'] = np.where( df['Prices'] < 0, '-$' + df['Prices'].astype(str).str[1:], '$' + df['Prices'].astype(str))
df['Prices']

Out[153]:
0     -$445.0
1    -$2058.0
2     -$954.0
3     -$520.0
4     -$730.0
Name: Prices, dtype: object
EdChum
  • 376,765
  • 198
  • 813
  • 562
  • Works. Also, you could change the column from float to integer using .astype(int) before running the command if you want your final result to be to the nearest whole number. – Kevin Apr 13 '16 at 17:47
  • Sure but your intial dtype was float and it was unclear if you would have some values that were not whole dollars – EdChum Apr 13 '16 at 17:52