Using replace efficiently in pandas
use map
to perform a lookup:
In [46]:
df['1st'] = df['1st'].map(idxDict)
df
Out[46]:
1st 2nd
0 a 2
1 b 4
2 c 6
to avoid the situation where there is no valid key you can pass na_action='ignore'
You can also use df['1st'].replace(idxDict)
but to answer you question about efficiency:
timings
In [69]:
%timeit df['1st'].replace(idxDict)
%timeit df['1st'].map(idxDict)
1000 loops, best of 3: 1.57 ms per loop
1000 loops, best of 3: 1.08 ms per loop
In [70]:
%%timeit
for k,v in idxDict.items():
df ['1st'] = df ['1st'].replace(k, v)
100 loops, best of 3: 3.25 ms per loop
So using map
is over 3x faster here
on a larger dataset:
In [3]:
df = pd.concat([df]*10000, ignore_index=True)
df.shape
Out[3]:
(30000, 2)
In [4]:
%timeit df['1st'].replace(idxDict)
%timeit df['1st'].map(idxDict)
100 loops, best of 3: 18 ms per loop
100 loops, best of 3: 4.31 ms per loop
In [5]:
%%timeit
for k,v in idxDict.items():
df ['1st'] = df ['1st'].replace(k, v)
100 loops, best of 3: 18.2 ms per loop
For 30K row df, map
is ~4x faster so it scales better than replace
or looping
While map
is indeed faster, replace
was updated in version 19.2 (details here) to improve its speed making the difference significantly less:
In [1]:
import pandas as pd
df = pd.DataFrame([[1,2],[3,4],[5,6]], columns = ['1st', '2nd'])
df = pd.concat([df]*10000, ignore_index=True)
df.shape
Out [1]:
(30000, 2)
In [2]:
idxDict = {1:'a', 3:"b", 5:"c"}
%timeit df['1st'].replace(idxDict, inplace=True)
%timeit df['1st'].update(df['1st'].map(idxDict))
Out [2]:
100 loops, best of 3: 12.8 ms per loop
100 loops, best of 3: 7.95 ms per loop
Additionally, I modified EdChum's code for map to include update
, which, while slower, prevents values not included in an incomplete map from being changed to nans.