Replacing few values in a pandas dataframe column with another value

Question

I have a pandas dataframe df as illustrated below:

BrandName Specialty
A          H
B          I
ABC        J
D          K
AB         L

I want to replace 'ABC' and 'AB' in column BrandName by 'A'. Can someone help with this?

Solution 1

The easiest way is to use the replace method on the column. The arguments are a list of the things you want to replace (here ['ABC', 'AB']) and what you want to replace them with (the string 'A' in this case):

>>> df['BrandName'].replace(['ABC', 'AB'], 'A')
0    A
1    B
2    A
3    D
4    A

This creates a new Series of values so you need to assign this new column to the correct column name:

df['BrandName'] = df['BrandName'].replace(['ABC', 'AB'], 'A')

Solution 2

Replace

DataFrame object has powerful and flexible replace method:

DataFrame.replace(
        to_replace=None,
        value=None,
        inplace=False,
        limit=None,
        regex=False, 
        method='pad',
        axis=None)

Note, if you need to make changes in place, use inplace boolean argument for replace method:

Inplace

inplace: boolean, default False If True, in place. Note: this will modify any other views on this object (e.g. a column form a DataFrame). Returns the caller if this is True.

Snippet

df['BrandName'].replace(
    to_replace=['ABC', 'AB'],
    value='A',
    inplace=True
)

Solution 3

loc method can be used to replace multiple values:

df.loc[df['BrandName'].isin(['ABC', 'AB'])] = 'A'

Solution 4

You could also pass a dict to the pandas.replace method:

data.replace({
    'column_name': {
        'value_to_replace': 'replace_value_with_this'
    }
})

This has the advantage that you can replace multiple values in multiple columns at once, like so:

data.replace({
    'column_name': {
        'value_to_replace': 'replace_value_with_this',
        'foo': 'bar',
        'spam': 'eggs'
    },
    'other_column_name': {
        'other_value_to_replace': 'other_replace_value_with_this'
    },
    ...
})

Solution 5

This solution will change the existing dataframe itself:

mydf = pd.DataFrame({"BrandName":["A", "B", "ABC", "D", "AB"], "Speciality":["H", "I", "J", "K", "L"]})
mydf["BrandName"].replace(["ABC", "AB"], "A", inplace=True)

View more solutions

Comments

[Alison S]

thanks for the snippet example, but it does not work. For one, if there is no = in the to_replace portion it errors out. For another, it is not making any replacements. Is there anyway to get a working example of the replace functionality in v 0.20.1?

[ski_squaw]

One tricky thing if your datatypes are messed up in the dataframe (ie they look like strings but are not), use: df['BrandName'] = df['BrandName'].str.replace(['ABC', 'AB'], 'A')

[guy]

Does replace not scale well? It seems to crash my machine when replacing ~5 million rows of integers. Any way around this?

[Gonçalo Peres]

I had to pass inplace=True as well, else it wasn't changing.

[NikSp]

Ty for this answer. It was exactly what I was looking for. :)

[user42]

If you would like to extend this to an entire dataframe, it will be df = df.replace(['ABC', 'AB'], 'A')

[Amarpreet Singh]

This is faster than the replace method in my case.