Format numbers in a Python pandas DataFrame as currency in thousands or millions

Question

I have a dataframe: pd.DataFrame({"Amount":[19000000, 9873200, 823449242]}), and I need to convert the numbers into currency ($) in millions. i.e. $19.00MM, $9.88MM, and $823.45MM.

Does anyone know a quick way to do this?

Thanks!

Solution 1

I think the following should work:

df['($) millions'] = '$' + (df['Amount'].astype(float)/1000000).astype(str) + 'MM'

In [3]:
df['($) millions'] = '$' + (df['Amount'].astype(float)/1000000).astype(str) + 'MM'
df

Out[3]:
      Amount   ($) millions
0   19000000        $19.0MM
1    9873200      $9.8732MM
2  823449242  $823.449242MM

if needed you can also round:

In [5]:
df['($) millions'] = '$' + (df['Amount'].astype(float)/1000000).round(2).astype(str) + 'MM'
df

Out[5]:
      Amount ($) millions
0   19000000      $19.0MM
1    9873200      $9.87MM
2  823449242    $823.45MM

Another method is to apply a format on each value using apply:

In [15]:
df['($) millions'] = (df['Amount']/1000000).apply(lambda x: '${:,.2f}MM'.format(x))
df

Out[15]:
      Amount ($) millions
0   19000000     $19.00MM
1    9873200      $9.87MM
2  823449242    $823.45MM

However, I expect the first method to scale better for large datasets, although sometimes list comprehensions are faster when it comes to strings

Here is the list comprehension method:

In [17]:
df['($) millions'] = ['${:,.2f}MM'.format(x) for x in df['Amount']/1000000]
df

Out[17]:
      Amount ($) millions
0   19000000     $19.00MM
1    9873200      $9.87MM
2  823449242    $823.45MM

Solution 2

This simply divides the values - it does not add the $ sign etc. (it's only a matter of changing the lambda function), but Amount is still dtype float so you can treat it as numbers.

 In [41]: df = pd.DataFrame({"Amount":[19000000, 9873200, 823449242]})

In [42]: df['MillionsAsFloat'] = df.apply(lambda row: row['Amount'] / 1000000, axis=1
    ...: )

In [43]: df['MillionsAsString'] = df.apply(lambda row: '$' + str(round(row['Amount'] 
    ...: / 1000000,2)) + 'MM', axis=1)

In [44]: df
Out[44]: 
      Amount  MillionsAsFloat MillionsAsString
0   19000000        19.000000          $19.0MM
1    9873200         9.873200          $9.87MM
2  823449242       823.449242        $823.45MM

Comments

[quapka]

I like this answer more!:)

[DanZimmerman]

I like the quick solution with the option to round number, thanks for the quick reply!

[DanZimmerman]

I think your solution not only changes the way we visualize the numbers and it empowers us to do calculations on the numbers. Brilliant!!!

[quapka]

Consider accepting the answer, so to show, this Q is resolved.:)

[Tony Brand]

@EdChum How can I do more options like thousands, billions and so on?

[Tony Brand]

@quapka How can I do more options like thousands, billions and so on?

[quapka]

@TonyBrand you just have to specify the divisor in row['Amount'] / <divisor> accordingly.