in Pandas, when using read_csv(), how to assign a NaN to a value that's not the dtype intended?
<p><strong>Note:</strong> Please excuse my very low skilled English, feel free to modify the question's title, or the following text to be more understandable</p>

<p>I have this line in my code:</p>

<pre><code>moto = pd.read_csv('reporte.csv')
</code></pre>

<p>It sends a <strong><code>DtypeWarning: Columns (2,3,4,5,6,7,8,9,10,12,13) have mixed types.</code></strong> warning, so I change it to</p>

<pre><code>moto = pd.read_csv('reporte.csv', dtype={'TP': np.float64})
</code></pre>

<p>Now it drops a <strong><code>ValueError: could not convert string to float: 'None'</code></strong>.</p>

<p>I checked the file (around 200K lines) with Excel, and yes, I found some cells with "<strong>None</strong>" value.</p>

<p>So my question is: <strong>Is there a way to ignore the error, or force python to fill the offending error with NaN or something else?</strong></p>

<p>I tried the solution <a href="http://stackoverflow.com/questions/30314153/python-pandas-dtypewarning-specify-dtype-option-on-import-how">here</a> but it didn't work.</p>

<p>I tried creating a csv to replicate this feedback but couldn't on pandas 0.18, so I can only recommend two methods to handle this:</p>

<p><strong>First</strong></p>

<p>If you know that your missing values are all marked by a string 'none', then do this:</p>

<pre><code>moto = pd.read_csv("test.csv", na_values=['none'])
</code></pre>

<p>You can also add, to the na_values list, other markers that should be converted to NaNs.</p>

<p><strong>Second</strong></p>

<p>Try your first line again without using the dtype option. </p>

<pre><code>moto = pd.read_csv('reporte.csv')
</code></pre>

<p>The read is successful because you are only getting a warning. Now execute <code>moto.dtypes</code> to show you which columns are objects. For the ones you want to change do the following:</p>

<pre><code>moto.test_column = pd.to_numeric(data.test_column, errors='coerce')
</code></pre>

<p>The 'coerce' option will convert any problematic entries, like 'none', to NaNs. </p>

<p>To convert the entire dataframe at once, you can use convert_objects. You could also use it on a single column, but that usage is deprecated in favor of to_numeric. The option, convert_numeric, does the coercion to NaNs:</p>

<pre><code>moto = moto.convert_objects(convert_numeric=True)
</code></pre>

<p>After any of these methods, proceed with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.fillna.html" rel="nofollow">fillna</a> to do what you need to.</p>

python pandas