How to remap ids to consecutive numbers quickly
<p>I have a large csv file with lines that looks like</p>

<pre><code>stringa,stringb
stringb,stringc
stringd,stringa
</code></pre>

<p>I  need to convert it so the ids are consecutively numbered from 0. In this case the following would work</p>

<pre><code>0,1
1,2
3,0
</code></pre>

<p>My current code looks like:</p>

<pre><code>import csv
names = {}
counter = 0
with open('foo.csv', 'rb') as csvfile:
     reader = csv.reader(csvfile)
     for row in reader:
         if row[0] in names:
             id1 =  row[0]
         else:
             names[row[0]] = counter
             id1 = counter
             counter += 1
         if row[1] in names:
             id2 = row[1]
         else:
             names[row[1]] = counter
             id2 = counter
             counter += 1
     print id1, id2
</code></pre>

<p>Python dicts use a lot of memory sadly and my input is large.</p>

<blockquote>
  <p>What can I do when the input is too large for the dict to fit in memory</p>
</blockquote>

<p>I would also be interested if there is a better/faster way to solve this problem in general.</p>

<p><strong>UPDATE:</strong> here is a memory saving solution, which converts all your string to numerical categories:</p>

<pre><code>In [13]: df
Out[13]:
        c1       c2
0  stringa  stringb
1  stringb  stringc
2  stringd  stringa
3  stringa  stringb
4  stringb  stringc
5  stringd  stringa
6  stringa  stringb
7  stringb  stringc
8  stringd  stringa

In [14]: x = (df.stack()
   ....:        .astype('category')
   ....:        .cat.rename_categories(np.arange(len(df.stack().unique())))
   ....:        .unstack())

In [15]: x
Out[15]:
  c1 c2
0  0  1
1  1  2
2  3  0
3  0  1
4  1  2
5  3  0
6  0  1
7  1  2
8  3  0

In [16]: x.dtypes
Out[16]:
c1    category
c2    category
dtype: object
</code></pre>

<p><strong>OLD answer:</strong></p>

<p>I think a you can categorize your columns:</p>

<pre><code>In [63]: big.head(15)
Out[63]:
         c1       c2
0   stringa  stringb
1   stringb  stringc
2   stringd  stringa
3   stringa  stringb
4   stringb  stringc
5   stringd  stringa
6   stringa  stringb
7   stringb  stringc
8   stringd  stringa
9   stringa  stringb
10  stringb  stringc
11  stringd  stringa
12  stringa  stringb
13  stringb  stringc
14  stringd  stringa

In [64]: big.info()
&lt;class 'pandas.core.frame.DataFrame'&gt;
RangeIndex: 30000000 entries, 0 to 29999999
Data columns (total 2 columns):
c1    object
c2    object
dtypes: object(2)
memory usage: 457.8+ MB
</code></pre>

<p>So <code>big</code> DF has 30M rows and it's size is approx. 460MiB...</p>

<p>Let's categorize it:</p>

<pre><code>In [65]: cat = big.apply(lambda x: x.astype('category'))

In [66]: cat.info()
&lt;class 'pandas.core.frame.DataFrame'&gt;
RangeIndex: 30000000 entries, 0 to 29999999
Data columns (total 2 columns):
c1    category
c2    category
dtypes: category(2)
memory usage: 57.2 MB
</code></pre>

<p>It takes now only 57MiB and looks exactly the same:</p>

<pre><code>In [69]: cat.head(15)
Out[69]:
         c1       c2
0   stringa  stringb
1   stringb  stringc
2   stringd  stringa
3   stringa  stringb
4   stringb  stringc
5   stringd  stringa
6   stringa  stringb
7   stringb  stringc
8   stringd  stringa
9   stringa  stringb
10  stringb  stringc
11  stringd  stringa
12  stringa  stringb
13  stringb  stringc
14  stringd  stringa
</code></pre>

<p>let's compare it's size with similar numeric DF:</p>

<pre><code>In [67]: df = pd.DataFrame(np.random.randint(0,5,(30000000,2)), columns=list('ab'))

In [68]: df.info()
&lt;class 'pandas.core.frame.DataFrame'&gt;
RangeIndex: 30000000 entries, 0 to 29999999
Data columns (total 2 columns):
a    int32
b    int32
dtypes: int32(2)
memory usage: 228.9 MB
</code></pre>

python pandas dataframe