Can you use the pandas df.str.replace() function for multiple values?

Question

I'm using the pandas df.str.replace() function and would like to remove multiple characters from the string.

I'm trying to clean up some transaction data in a CSV file using pandas. I have a column that is storing the amount of the transaction as an Object data type. Before I can change it to a float datatype, I need to remove the $ character and any , characters from numbers greater than 999.99. I've been able to do this one at a time; however, I'd like to know if I can pass in multiple values to clean it up.

2 8/20/2019 Utah Valley Univ UTAH VALLEY UNIV UVU PMT 1 908191 4,825.50

df['Amount'] = df['Amount'].str.replace(r',','').astype(float)

I'd like to remove the '$' and the ',' character at the same time if possible.

df['Amount'] = df['Amount'].str.replace(r'\$|\,', '').astype(float)? — Hryhorii Pavlenko
– Hryhorii Pavlenko, Commented Aug 25, 2019 at 16:24
Dear @drewipson , you can choose an answer as given below or comment if anything needed further. — Karn Kumar
– Karn Kumar, Commented Aug 25, 2019 at 17:53

Karn Kumar · Accepted Answer · 2019-08-25 17:45:52Z

1

Taking liberty to borrow the DataFrame from @Ian>

There is another way of doing it with replace method and withng replace using dict method to replace multiple value across the column..

>>> df
    amount
0  $25,000
1  $13,000
2  $65,000
3  $19,000
4  $15,000

It will simple remove the $ sign and comma with null '' values .

>>> df['amount'].replace({'\$': '', ',': ''}, regex=True)
0    25000
1    13000
2    65000
3    19000
4    15000
Name: amount, dtype: object

Just to convert value to float use astype..

>>> df['amount'].replace({'\$': '', ',': ''}, regex=True).astype(float)
0    25000.0
1    13000.0
2    65000.0
3    19000.0
4    15000.0
Name: amount, dtype: float64

edited Aug 25, 2019 at 17:45

answered Aug 25, 2019 at 17:39

Karn Kumar

8,8343 gold badges32 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ian Thompson · Accepted Answer · 2019-08-25 17:03:00Z

Going to steal @political scientist's comment and make it an answer with a little explanation.

Using some fake data:

import pandas as pd
import numpy as np

np.random.seed(1)

df = pd.DataFrame(np.random.randint(5, 100, size=(5,)), columns=['amount']).applymap(str)

df.amount = '$' + df.amount + ',' + '000'

print(df)

    amount
0  $42,000
1  $17,000
2  $77,000
3  $14,000
4  $80,000

We have $ and , in our amount column. Using

df.amount.str.replace(r'\$|\,', '').astype(float)

We get

0    42000.0
1    17000.0
2    77000.0
3    14000.0
4    80000.0
Name: amount, dtype: float64

Why? By default the .str.replace() method has the parameter regex=True which means it accepts regular expressions for pattern matching.

The r at the front of the string tells the code to read the string as "raw"
\$ says to look for a dollar sign
| is the symbol for or
\, says to look for a comma

Using the | between the \$ and the \, (without a space!) means to look for either and replace them both with what is present at the second parameter in the method (aka repl)

Here is a cheat sheet I found that explains other regex characters and how to use them: Regex tutorial — A quick cheatsheet by examples

Collectives™ on Stack Overflow

Can you use the pandas df.str.replace() function for multiple values?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related