How to recode and count efficiently
<p>I have a large csv with three strings per row in this form:</p>

<pre><code>a,c,d
c,a,e
f,g,f
a,c,b
c,a,d
b,f,s
c,a,c
</code></pre>

<p>I read in the first two columns recode the strings  to integers and then remove duplicates counting how many copies of each row there were as follows:</p>

<pre><code>import pandas as pd
df = pd.read_csv("test.csv", usecols=[0,1], prefix="ID_", header=None)
letters = set(df.values.flat)
df.replace(to_replace=letters, value=range(len(letters)), inplace=True)
df1 = df.groupby(['ID_0', 'ID_1']).size().rename('count').reset_index()
print df1
</code></pre>

<p>This gives:</p>

<pre><code>   ID_0  ID_1  count
0     0     1      2
1     1     0      3
2     2     4      1
3     4     3      1
</code></pre>

<p>which is exactly what I need.</p>

<p>However as my data is large I would like to make two improvements.</p>

<ul>
<li>How can I do the groupby and then recode instead of the other way round? The problem is that I can't do df1[['ID_0','ID_0']].replace(to_replace=letters, value=range(len(letters)), inplace = True). This gives the error "A value is trying to be set on a copy of a slice from a DataFrame"</li>
<li>How can I avoid creating df1? That is do the whole thing inplace.</li>
</ul>

<p>I like to use <a href="http://scikit-learn.org/stable/modules/generated/sklearn.preprocessing.LabelEncoder.html" rel="nofollow"><code>sklearn.preprocessing.LabelEncoder</code></a> to do the letter to digit conversion:</p>

<pre><code>from sklearn.preprocessing import LabelEncoder

# Perform the groupby (before converting letters to digits).
df = df.groupby(['ID_0', 'ID_1']).size().rename('count').reset_index()

# Initialize the LabelEncoder.
le = LabelEncoder()
le.fit(df[['ID_0', 'ID_1']].values.flat)

# Convert to digits.
df[['ID_0', 'ID_1']] = df[['ID_0', 'ID_1']].apply(le.transform)
</code></pre>

<p>The resulting output:</p>

<pre><code>   ID_0  ID_1  count
0     0     2      2
1     1     3      1
2     2     0      3
3     3     4      1
</code></pre>

<p>If you want to convert back to letters at a later point in time, you can use <code>le.inverse_transform</code>:</p>

<pre><code>df[['ID_0', 'ID_1']] = df[['ID_0', 'ID_1']].apply(le.inverse_transform)
</code></pre>

<p>Which maps back as expected:</p>

<pre><code>  ID_0 ID_1  count
0    a    c      2
1    b    f      1
2    c    a      3
3    f    g      1
</code></pre>

<p>If you just want to know which digit corresponds to which letter, you can look at the <code>le.classes_</code> attribute.  This will give you an array of letters, which is indexed by the digit it encodes to:</p>

<pre><code>le.classes_ 

['a' 'b' 'c' 'f' 'g']
</code></pre>

<p>For a more visual representation, you can cast as a Series:</p>

<pre><code>pd.Series(le.classes_)

0    a
1    b
2    c
3    f
4    g
</code></pre>

<p><strong>Timings</strong></p>

<p>Using a larger version of the sample data and the following setup:</p>

<pre><code>df2 = pd.concat([df]*10**5, ignore_index=True)

def root(df):
    df = df.groupby(['ID_0', 'ID_1']).size().rename('count').reset_index()
    le = LabelEncoder()
    le.fit(df[['ID_0', 'ID_1']].values.flat)
    df[['ID_0', 'ID_1']] = df[['ID_0', 'ID_1']].apply(le.transform)
    return df

def pir2(df):
    unq = np.unique(df)
    mapping = pd.Series(np.arange(unq.size), unq)
    return df.stack().map(mapping).unstack() \
      .groupby(df.columns.tolist()).size().reset_index(name='count')
</code></pre>

<p>I get the following timings:</p>

<pre><code>%timeit root(df2)
10 loops, best of 3: 101 ms per loop

%timeit pir2(df2)
1 loops, best of 3: 1.69 s per loop
</code></pre>

python pandas