How to one-hot-encode from a csv file input
<p>I have a csv file which I read in with</p>

<pre><code>import pandas
df = pd.read_csv("inputfile")
</code></pre>

<p>Some of the columns are numerical and some are strings.  Let's call one of the numerical columns <code>'num'</code> and one of the string ones <code>'col'</code>. I would like do the following:</p>

<ol>
<li>I would like to be able to one-hot-encode a string column called <code>'col'</code> and result in a sparse matrix with all the features in it.</li>
<li>I would like to one-hot-encode <code>df['num']</code> but only when <code>df['num'] &lt; 100</code>.</li>
</ol>

<p>This is easy to do if the input were in a list of dictionaries. </p>

<p><em>Step 1.</em> happens automatically when you run DictVectorizer</p>

<p><em>Step 2.</em> just need me to iterate over the dictionaries adding a key/value pair for a new string feature <code>'num_cat'</code> when necessary and then run  DictVectorizer on the whole new list of dictionaries.</p>

<p>I am stuck on following:</p>

<ul>
<li>I need the matrix that results from the one-hot-encoding to be sparse. pd.get_dummies is very very slow if there are a large number of categories. Does it create a dense matrix and then make it sparse?  As a result I don't feel I can use that.</li>
<li>Can I do what I need without first converting the data frame to a list of dictionaries and then running DictVectorizer? If not, is there an easy way to do the conversion?</li>
</ul>

<p>Say you start with</p>

<pre><code>In [31]: df = pd.DataFrame({'col': ['foo', 'foo', 'bar', 'bar'], 'num': [1, 1, 3, 213]})

In [32]: df
Out[32]: 
   col  num
0  foo    1
1  foo    1
2  bar    3
3  bar  213
</code></pre>

<p>First, let's take care of <code>col</code>:</p>

<p>If we define</p>

<pre><code>In [33]: d = dict([e[:: -1] for e in enumerate(df.col.unique())])
</code></pre>

<p>Then we can use it to "numerify" <code>col</code>:</p>

<pre><code>In [34]: df.col = df.col.map(d)

In [35]: df
Out[35]: 
   col  num
0    0    1
1    0    1
2    1    3
3    1  213
</code></pre>

<p>Now let's deal with <code>num</code>:</p>

<pre><code>In [36]: import numpy as np
</code></pre>

<p>We'll just make everything over 100 into 100:</p>

<pre><code>In [37]: df.num = np.minimum(df.num.values, 100)

In [38]: df
Out[38]: 
   col  num
0    0    1
1    0    1
2    1    3
3    1  100
</code></pre>

<p>Now for the encoding:</p>

<pre><code>In [49]: from sklearn import preprocessing

In [50]: enc = preprocessing.OneHotEncoder()

In [51]: enc.fit(df.as_matrix()).transform(df.as_matrix()).toarray()
Out[51]: 
array([[ 1.,  0.,  1.,  0.,  0.],
       [ 1.,  0.,  1.,  0.,  0.],
       [ 0.,  1.,  0.,  1.,  0.],
       [ 0.,  1.,  0.,  0.,  1.]])
</code></pre>

<p>Two things to note:</p>

<ol>
<li><p><code>toarray()</code> makes the matrix dense again; its use is optional, of course.</p></li>
<li><p>By construction, the last column is necessarily the "100 and over" category of <code>num</code>. You can retain it or drop this column, as needed.</p></li>
</ol>

python csv pandas scikit-learn