python: convert pandas categorical values to integer when reading csv in chunks
<p>I have large csv file with 1000 columns, column 0 is an id, the other columns are categorical. I would like to convert them to integer values in order to use them for data analysis. First "dummy" way would work if I had enough memory:</p>

<pre><code>filename_cat_train = "../input/train_categorical.csv"
df = pd.read_csv(filename_cat_train, dtype=str)

for column in df.columns[1:]:
    df[column] = df[column].astype('category')

columns = df.select_dtypes(['category']).columns
df[columns] = df[columns].apply(lambda x: x.cat.codes)

df.to_csv("../input/train_categorical_rawconversion.csv", index=False)
</code></pre>

<p>but it lasts very long, and definitely not a smart way to solve the task.</p>

<p>I could just load the data file in chunks and then combine after converting to int values using the approach above. However when loading in chunks (even 100k large), not all categories are present in my data. This means, having values T10, T11, T13 in the first chunk, and T10, T11, T12 in the second, different values appear for categories in chunks. </p>

<p>The optimal way for me would be:
0. create the list of categorical and corresponding int values (there are only like 100, and it is easy to retrieve them all from the data)
1. Load data in chunks
2. substitute the values from the list
3. save each chunk and them combine them.</p>

<p>How could I perform such steps efficiently? Maybe better approach exists?
Thanks!</p>

<p><strong>Update1:</strong> the categorical data in of the same 'type. They are keys like T12, T45689, A3333 etc. the csv file is like that:
4,,,,,T12,,,,,,A44,,,,,,B3333, </p>

<p>In this case, it indeed seems that a two-pass scheme might be effective.</p>

<p>Starting with</p>

<pre><code>import pandas as pd
data=pd.read_csv(my_file_name, chunksize=my_chunk_size)
</code></pre>

<p>You could do:</p>

<pre><code>import collections
uniques = collections.defaultdict(list)
for chunk in data: 
    for col in chunk:
        uniques[col].update(chunk[col].unique())
</code></pre>

<p>At this point, uniques should map each column name to the unique items appearing in it. To translate to a map, you can now use</p>

<pre><code>for col in uniques:
   uniques[col] = dict((e[1], e[0]) for e in enumerate(uniques[col]))
</code></pre>

<p>Now read the file again, and translate each column using the map corresponding to it (see <a href="http://stackoverflow.com/questions/20250771/remap-values-in-pandas-column-with-a-dict">here</a>.)</p>

<hr>

<p>If your columns all contain keys from "the same dictionary", you can do the following:</p>

<p>Starting with the following</p>

<pre><code>import pandas as pd
data=pd.read_csv(my_file_name, chunksize=my_chunk_size)
</code></pre>

<p>You could do:</p>

<pre><code>uniques = set([])
for chunk in data: 
    for col in cols:
        uniques.update(chunk[col].unique())
</code></pre>

<p>At this point, uniques should contain the unique items appearing in the DataFrame. To translate to a map, you can now use</p>

<pre><code>uniques = dict((e[1], e[0]) for e in enumerate(uniques))
</code></pre>

<p>Now, load the DataFrame again, and use <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.replace.html" rel="nofollow">pd.DataFrame.replace</a>.</p>

python pandas data-conversion