2

DataFrame df has a column called amount 

import pandas as pd
df = pd.DataFrame(['$3,000,000.00','$3,000.00', '$200.5', '$5.5'], columns = ['Amount'])

df:

 ID | Amount
 0  | $3,000,000.00
 1  | $3,000.00
 2  | $200.5
 3  | $5.5

I want to parse all the values in column amount and extract the amount as a number and ignore the decimal points. End result is DataFrame that looks like this:

 ID | Amount
 0  | 3000000
 1  | 3000
 2  | 200
 3  | 5

How do I do this?

Rakesh Adhikesavan
  • 11,966
  • 18
  • 51
  • 76

3 Answers3

8

You can use str.replace with double casting by astype:

df['Amount'] = (df.Amount.str.replace(r'[\$,]', '').astype(float).astype(int))
print (df)
    Amount
0  3000000
1     3000
2      200
3        5
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
3

Code -

import pandas as pd

def format_amount(x):
    x = x[1:].split('.')[0]
    return int(''.join(x.split(',')))

df = pd.DataFrame(['$3,000,000.00','$3,000.00', '$200.5', '$5.5'], columns =
        ['Amount'])

df['Amount'] = df['Amount'].apply(format_amount)

print(df)

Output -

    Amount
0  3000000
1     3000
2      200
3        5
Vedang Mehta
  • 2,214
  • 9
  • 22
3

You need to use the map function on the column and reassign to the same column:

import locale
locale.setlocale( locale.LC_ALL, 'en_US.UTF-8' )

df.Amount = df.Amount.map(lambda s: int(locale.atof(s[1:])))

PS: This uses the code from How do I use Python to convert a string to a number if it has commas in it as thousands separators? to convert a string representing a number with thousands separator to an int

Community
  • 1
  • 1
Daniel
  • 26,899
  • 12
  • 60
  • 88
  • I test it in version `pandas 0.18.2`, `python: 3.5.1`.and `ValueError: could not convert string to float: '3,000,000.00'`. I use `import locale print (df.Amount.map(lambda s: int(locale.atof(s[1:]))))`. What can be problem? Maybe pandas API was changed. Maybe someone try to test too. – jezrael May 20 '16 at 16:30
  • You need `locale.setlocale( locale.LC_ALL, 'en_US.UTF-8' )` before you try to parse the number. – Daniel May 20 '16 at 16:57
  • I try it and now `Error: unsupported locale setting`. – jezrael May 20 '16 at 16:58
  • 1
    But for me works [`locale.setlocale( locale.LC_ALL, 'english_USA' )`](http://stackoverflow.com/a/12746073/2901002). – jezrael May 20 '16 at 17:10