CODEWITHSUNDEEP
pythonregexpython-3.xpandas
Qashin
Qashin

Reputation: 125

How to extract dollar amount from pandas DataFrame column

I would to get dollar amounts from more than hundreds rows in a column, and then save the amount in a new column. The dollar amount varies in each row, like $100.01, $1,000.05, 10,000, 100,000 etc.

One of the lines looks like this:

Approving the settlement claim of Mr. X Y by payment in the amount of $120,000.65

I tried to do something like this, but it's not extracting the dollar amount:

df['amount'] = df['description'].str.extract('/(\$[0-9]+(\.[0-9]{2})?)/', expand=True)

Please help.

Upvotes: 6

Views: 1220

Answers (1)

jezrael
jezrael

Reputation: 862921

IIUC need:

df=pd.DataFrame({'description':['ss $100.01', 'dd $1,000.05', 
                                'f 10,000', 'g 100,000',
                                'yr 4,120,000.65']})

df['amount'] = df['description'].str.extract('([0-9,.]+)')
print (df)
       description        amount
0       ss $100.01        100.01
1     dd $1,000.05      1,000.05
2         f 10,000        10,000
3        g 100,000       100,000
4  yr 4,120,000.65  4,120,000.65

EDIT:

df['amount1'] = df['description'].str.extract('(\$[0-9,.]+)')
df['amount2'] = df['description'].str.extract('\$([0-9,.]+)')
print (df)

       description    amount1   amount2
0       ss $100.01    $100.01    100.01
1     dd $1,000.05  $1,000.05  1,000.05
2         f 10,000        NaN       NaN
3        g 100,000        NaN       NaN
4  yr 4,120,000.65        NaN       NaN

Upvotes: 4

Related Questions