Vikash B Vikash B - 3 months ago 20
Python Question

Pandas replace with default value

I have a pandas dataframe I want to replace a certain column conditionally.

eg:

col

0 Mr
1 Miss
2 Mr
3 Mrs
4 Col.


I want to map them as

{'Mr': 0, 'Mrs': 1, 'Miss': 2}


If there are other titles now available in the dict then I want them to have a default value of
3


The above example becomes

col

0 0
1 2
2 0
3 1
4 3


Can I do this with pandas.replace() without using regex ?

Answer

You can use map rather as replace, because faster, then fillna by 3 and cast to int by astype:

df['col'] = df.col.map({'Mr': 0, 'Mrs': 1, 'Miss': 2}).fillna(3).astype(int)

print (df)
   col
0    0
1    2
2    0
3    1
4    3

Another solution with numpy.where and condition with isin:

d = {'Mr': 0, 'Mrs': 1, 'Miss': 2}
df['col'] = np.where(df.col.isin(d.keys()), df.col.map(d), 3).astype(int)
print (df)
   col
0    0
1    2
2    0
3    1
4    3

Solution with replace:

d = {'Mr': 0, 'Mrs': 1, 'Miss': 2}
df['col'] = np.where(df.col.isin(d.keys()), df.col.replace(d), 3)
print (df)
   col
0    0
1    2
2    0
3    1
4    3

Timings:

df = pd.concat([df]*10000).reset_index(drop=True)

d = {'Mr': 0, 'Mrs': 1, 'Miss': 2}
df['col0'] = df.col.map(d).fillna(3).astype(int)
df['col1'] = np.where(df.col.isin(d.keys()), df.col.replace(d), 3)
df['col2'] = np.where(df.col.isin(d.keys()), df.col.map(d), 3).astype(int)
print (df)

In [447]: %timeit df['col0'] = df.col.map(d).fillna(3).astype(int)
100 loops, best of 3: 4.93 ms per loop

In [448]: %timeit df['col1'] = np.where(df.col.isin(d.keys()), df.col.replace(d), 3)
100 loops, best of 3: 14.3 ms per loop

In [449]: %timeit df['col2'] = np.where(df.col.isin(d.keys()), df.col.map(d), 3).astype(int)
100 loops, best of 3: 7.68 ms per loop

In [450]: %timeit df['col3'] = df.col.map(lambda L: d.get(L, 3))
10 loops, best of 3: 36.2 ms per loop