title: Python Pandas converting column type to numeric
body: <p>I’m new to using python/pandas to clean up data from the UK national pupil database in an organised, repeatable, deterministic way. The full datasets are available here <a href="https://stackoverflow.com/questions/48279405/converting-object-types-columns-into-numeric-type-using-pandas">https://www.compare-school-performance.service.gov.uk/download-data</a>
  I can't find how to add a table to this question but with headings; RECTYPE,    PCODE, TOTPUPS, KS2APS the dataframe <code>&gt;&gt;&gt; np.asanyarray(df)</code> looks something like this.  </p>
  
  <pre><code>    array([[1, 'NW1 1TA', '642', '27.6'],
      [1, 'NW1 1RX', '881', '26.5'],
      [1, 'NW5 1RN', 'NEW', '29.5'],
      [1, 'BN6 8LP', '1046', 'SUPP']], dtype=object)
  </code></pre>
  
  <p>Rectype always contains integers allowing me to successfully filter on that numerically. The problems are total pupils and KS2 average prior attainment (TOTPUPS, KS2APS) should be type integer and float but contain frequent strings. </p>
  
  <p>I've successfully filtered these out with <code>df = df[df.KS2APS != "SUPP"]</code>.  </p>
  
  <p>The final step is to filter numerically based on TOTPUPS <code>df = df[df.TOTPUPS &gt; 199]</code>.  This fails with <code>TypeError: '&gt;' not supported between instances of 'str' and 'int'</code> 
  Because the column contained non-numeric data is was cast as such so I'm using .to_numeric </p>
  
  <pre><code>&gt;&gt;&gt; pd.to_numeric(df.TOTPUPS)
  ... 
  5552    1046
  Name: TOTPUPS, Length: 3735, dtype: int64
  </code></pre>
  
  <p>This tells me the type is now int64 ... but still fails when I try to filter numerically.  <a href="https://stackoverflow.com/questions/48279405/converting-object-types-columns-into-numeric-type-using-pandas">this stack overflow post</a> suggests if this is failing it's "because you've non-convertible objects".  To check I've written the output to a csv file and opened in Excel (not so useful!) and Notepad which showed the values are all integers <em>or</em> empty strings so I've also tried removing empty strings, then converting .to_numeric and filtering but still no joy.  </p>
  
  <p>I have a truly abysmal but perfectly effective workaround, to write my dataframe to a file and read it back in, but I'd still like to understand where I'm going wrong. </p>
  
  <p>UPDATED 
  My solution (ie workaround!) creating a new column does work ... </p>
  
  <pre><code>df['TOTPUPSint'] = pd.to_numeric(df.TOTPUPS)
  df = df[df.TOTPUPSint &gt; 199]
  len(df)
  3113
  np.asanyarray(df)
  array([[1, 'NW1 1TA', '642', '27.6', 642],
      [1, 'NW1 1RX', '881', '26.5', 881],
      [1, 'NW5 1RN', '805', '29.5', 805],
      ...,
      [1, 'RH19 3TY', '1616', '28.1', 1616],
      [1, 'RH14 9RY', '1621', '28.3', 1621],
      [1, 'BN6 8LP', '1046', '29.4', 1046]], dtype=object)
  </code></pre>
  
  <p>Also validated that the result are as expected, but this is still arguably a workaround rather than properly understanding the problem. </p>
  
  <p>Full marks to Dark for suggesting the <em>correct</em> way is <code>df['TOTPUPS'] = pd.to_numeric(df['TOTPUPS'],errors='coerce')</code>  My dataframe now looks like </p>
  
  <pre><code>array([[1, 'NW1 1TA', 642, '27.6', 642],
      [1, 'NW1 1RX', 881, '26.5', 881],
      [1, 'NW5 1RN', 805, '29.5', 805],
      ...,
      [1, 'RH19 3TY', 1616, '28.1', 1616],
      [1, 'RH14 9RY', 1621, '28.3', 1621],
      [1, 'BN6 8LP', 1046, '29.4', 1046]], dtype=object)
  </code></pre>
  
  <p>No quotes on both third and fifth column.  Am new to this forum but if there's some way of marking Dark's helpful answer as correct please let me know.  Thanks.</p>