How to speed LabelEncoder up recoding a categorical variable into integers
<p>I have a large csv with two strings per row in this form:</p>

<pre><code>g,k
a,h
c,i
j,e
d,i
i,h
b,b
d,d
i,a
d,h
</code></pre>

<p>I read in the first two columns and recode the strings  to integers as follows:</p>

<pre><code>import pandas as pd
df = pd.read_csv("test.csv", usecols=[0,1], prefix="ID_", header=None)
from sklearn.preprocessing import LabelEncoder

# Initialize the LabelEncoder.
le = LabelEncoder()
le.fit(df.values.flat)

# Convert to digits.
df = df.apply(le.transform)
</code></pre>

<p>This  code is from <a href="http://stackoverflow.com/a/39419342/2179021">http://stackoverflow.com/a/39419342/2179021</a>.</p>

<p>The code works very well but is slow when df is large. I timed each step and the result was surprising to me.</p>

<ul>
<li><code>pd.read_csv</code> takes about 40 seconds. </li>
<li><code>le.fit(df.values.flat)</code> takes about 30 seconds</li>
<li><code>df = df.apply(le.transform)</code> takes about 250 seconds.</li>
</ul>

<p>Is there any way to speed up this last step? It feels like it should be the fastest step of them all!</p>

<hr>

<p><strong>More timings for the recoding step on a computer with 4GB of RAM</strong></p>

<p>The answer below by maxymoo is fast but doesn't give the right answer. Taking the example csv from the top of the question, it translates it to:</p>

<pre><code>   0  1
0  4  6
1  0  4
2  2  5
3  6  3
4  3  5
5  5  4
6  1  1
7  3  2
8  5  0
9  3  4
</code></pre>

<p>Notice that 'd' is mapped to 3 in the first column  but 2 in the second.</p>

<p>I tried the solution from <a href="http://stackoverflow.com/a/39356398/2179021">http://stackoverflow.com/a/39356398/2179021</a> and get the following.</p>

<pre><code>df = pd.DataFrame({'ID_0':np.random.randint(0,1000,1000000), 'ID_1':np.random.randint(0,1000,1000000)}).astype(str)
df.info()
memory usage: 7.6MB
%timeit x = (df.stack().astype('category').cat.rename_categories(np.arange(len(df.stack().unique()))).unstack())
1 loops, best of 3: 1.7 s per loop
</code></pre>

<p>Then I increased the dataframe size by a factor of 10.</p>

<pre><code>df = pd.DataFrame({'ID_0':np.random.randint(0,1000,10000000), 'ID_1':np.random.randint(0,1000,10000000)}).astype(str) 
df.info()
memory usage: 76.3+ MB
%timeit x = (df.stack().astype('category').cat.rename_categories(np.arange(len(df.stack().unique()))).unstack())
MemoryError                               Traceback (most recent call last)
</code></pre>

<p>This method appears to use so much RAM trying to translate this relatively small dataframe that it crashes.</p>

<p>I also timed LabelEncoder with the larger dataset with 10 millions rows. It runs without crashing but the fit line alone took 50 seconds. The df.apply(le.transform) step took about 80 seconds.</p>

<p>How can I:</p>

<ol>
<li>Get something of roughly the speed of maxymoo's answer and roughly the memory usage of LabelEncoder but that gives the right answer when the dataframe has two columns. </li>
<li>Store the mapping so that I can  reuse it for different data (as in the way LabelEncoder allows me to do)?</li>
</ol>

<p>I tried this with the DataFrame:</p>

<pre><code>In [xxx]: import string
In [xxx]: letters = np.array([c for c in string.ascii_lowercase])
In [249]: df = pd.DataFrame({'ID_0': np.random.choice(letters, 10000000), 'ID_1':np.random.choice(letters, 10000000)})
</code></pre>

<p>It looks like this:</p>

<pre><code>In [261]: df.head()
Out[261]: 
  ID_0 ID_1
0    v    z
1    i    i
2    d    n
3    z    r
4    x    x

In [262]: df.shape
Out[262]: (10000000, 2)
</code></pre>

<p>So, 10 million rows. Locally, my timings are:</p>

<pre><code>In [257]: % timeit le.fit(df.values.flat)
1 loops, best of 3: 17.2 s per loop

In [258]: % timeit df2 = df.apply(le.transform)
1 loops, best of 3: 30.2 s per loop
</code></pre>

<p>Then I made a dict mapping letters to numbers and used pandas.Series.map:</p>

<pre><code>In [248]: letters = np.array([l for l in string.ascii_lowercase])
In [263]: d = dict(zip(letters, range(26)))

In [273]: %timeit for c in df.columns: df[c] = df[c].map(d)
1 loops, best of 3: 1.12 s per loop

In [274]: df.head()
Out[274]: 
   ID_0  ID_1
0    21    25
1     8     8
2     3    13
3    25    17
4    23    23
</code></pre>

<p>So that might be an option. The dict just needs to have all of the values that occur in the data.   </p>

<p>EDIT: The OP asked what timing I have for that second option, with categories. This is what I get:   </p>

<pre><code>In [40]: %timeit   x=df.stack().astype('category').cat.rename_categories(np.arange(len(df.stack().unique()))).unstack()
1 loops, best of 3: 13.5 s per loop
</code></pre>

<p>EDIT: per the 2nd comment:</p>

<pre><code>In [45]: %timeit uniques = np.sort(pd.unique(df.values.ravel()))
1 loops, best of 3: 933 ms per loop

In [46]: %timeit  dfc = df.apply(lambda x: x.astype('category', categories=uniques))
1 loops, best of 3: 1.35 s per loop
</code></pre>

python pandas scikit-learn