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><strong><em>New Answer</em></strong></p>

<pre><code>unq = np.unique(df)
mapping = pd.Series(np.arange(unq.size), unq)

df.stack().map(mapping).unstack() \
  .groupby(df.columns.tolist()).size().reset_index(name='count')
</code></pre>

<p><a href="http://i.stack.imgur.com/VSSrX.png" rel="nofollow"><img src="http://i.stack.imgur.com/VSSrX.png" alt="enter image description here"></a></p>

<p><strong><em>Old Answer</em></strong></p>

<pre><code>df.stack().rank(method='dense').astype(int).unstack() \
  .groupby(df.columns.tolist()).size().reset_index(name='count')
</code></pre>

<p><a href="http://i.stack.imgur.com/5TDXZ.png" rel="nofollow"><img src="http://i.stack.imgur.com/5TDXZ.png" alt="enter image description here"></a></p>

python pandas