Pandas error trying to convert string into integer
<p>Requirement : </p>

<p>One particular column in a DataFrame is 'Mixed' Type. It can have values like <code>"123456"</code> or <code>"ABC12345"</code>.</p>

<p>This dataframe is being written into an Excel using xlsxwriter .</p>

<p>For values like <code>"123456"</code>, down the line Pandas converting it into <code>123456.0</code> ( Making it look like a float)</p>

<p>We need to put it into xlsx as 123456 (i.e as +integer) in case value is FULLY numeric.</p>

<p>Effort :</p>

<p>Code Snippet shown below</p>

<pre><code>import pandas as pd
import numpy as np
import xlsxwriter
import os
import datetime
import sys
excel_name = str(input("Please Enter Spreadsheet Name :\n").strip())

print("excel entered :   "   , excel_name)
df_header = ['DisplayName','StoreLanguage','Territory','WorkType','EntryType','TitleInternalAlias',
         'TitleDisplayUnlimited','LocalizationType','LicenseType','LicenseRightsDescription',
         'FormatProfile','Start','End','PriceType','PriceValue','SRP','Description',
         'OtherTerms','OtherInstructions','ContentID','ProductID','EncodeID','AvailID',
         'Metadata', 'AltID', 'SuppressionLiftDate','SpecialPreOrderFulfillDate','ReleaseYear','ReleaseHistoryOriginal','ReleaseHistoryPhysicalHV',
          'ExceptionFlag','RatingSystem','RatingValue','RatingReason','RentalDuration','WatchDuration','CaptionIncluded','CaptionExemption','Any','ContractID',
          'ServiceProvider','TotalRunTime','HoldbackLanguage','HoldbackExclusionLanguage']
first_pass_drop_duplicate = df_m_d.drop_duplicates(['StoreLanguage','Territory','TitleInternalAlias','LocalizationType','LicenseType',
                                   'LicenseRightsDescription','FormatProfile','Start','End','PriceType','PriceValue','ContentID','ProductID',
                                   'AltID','ReleaseHistoryPhysicalHV','RatingSystem','RatingValue','CaptionIncluded'], keep=False) 
# We need to keep integer AltID  as is

first_pass_drop_duplicate.loc[first_pass_drop_duplicate['AltID']] =   first_pass_drop_duplicate['AltID'].apply(lambda x : str(int(x)) if str(x).isdigit() == True else x)
</code></pre>

<p>I have tried :</p>

<pre><code>1. using `dataframe.astype(int).astype(str)` # works as long as value is not alphanumeric
2.importing re and using pure python `re.compile()` and `replace()` -- does not work
3.reading DF row by row in a for loop !!! Kills the machine as dataframe can have 300k+ records
</code></pre>

<p>Each time, error I get:</p>

<blockquote>
  <p>raise KeyError('%s not in index' % objarr[mask])<br>
  KeyError: '[ 102711.  102711.  102711.  102711.  102711.  102711.  102711.     102711.\n  102711.  102711.  102711.  102711.  102711.  102711.  102711.  102711.\n  102711.  102711.  102711.  102711.  102711.  102711.  102711.  102711.\n  102711.  102711.  102711.  102711.  102711.  102711.  102711.  102711.\n  102711.  102711.  102711.  102711.  102711.  102711.  102711.  102711.\n  102711.  102711.  102711.  102711.  102711.  102711.  102711.  102711.\n  102711.  102711.  102711.  102711.  102711.  102711.  102711.  102711.\n  102711.  102711.  102711.  102711.  102711.  102711.  102711.  102711.\n    5337.    5337.    5337.    5337.    5337.    5337.    5337.    5337.\n    5337.    5337.    5337.    5337.    5337.    5337.    5337.    5337.\n    5337.    5337.    5337.    5337.    5337.    5337.    5337.    5337.\n    5337.    5337.    5337.    5337.    5337.    5337.    5337.    5337.\n    5337.    5337.    5337.    5337.    5337.    5337.    5337.    5337.\n    5337.    5337.    2124.    2124.    2124.    2124.    2124.    2124.\n    2124.    2124.    6643.    6643.    6643.    6643.    6643.    6643.\n    6643.    6643.    6643.    6643.    6643.    6643.    6643.    6643.\n    6643.    6643.    6643.    6643.    6643.    6643.    6643.    6643.\n    6643.    6643.    6643.    6643.    6643.    6643.    6643.    6643.] not in index'</p>
</blockquote>

<p>I am newbie in python/pandas , any help, solution is much appreciated.</p>

<p>Use <code>apply</code> and <code>pd.to_numeric</code> with parameter <code>errors='ignore'</code></p>

<p>consider the <code>pd.Series</code> <code>s</code></p>

<pre><code>s = pd.Series(['12345', 'abc12', '456', '65hg', 54, '12-31-2001'])

s.apply(pd.to_numeric, errors='ignore')

0         12345
1         abc12
2           456
3          65hg
4            54
5    12-31-2001
dtype: object
</code></pre>

<hr>

<p>Notice the types</p>

<pre><code>s.apply(pd.to_numeric, errors='ignore').apply(type)

0    &lt;type 'numpy.int64'&gt;
1            &lt;type 'str'&gt;
2    &lt;type 'numpy.int64'&gt;
3            &lt;type 'str'&gt;
4            &lt;type 'int'&gt;
5            &lt;type 'str'&gt;
dtype: object
</code></pre>

python string pandas casting int