Pandas: convert dtype 'object' to int

I've read an SQL query into Pandas and the values are coming in as dtype 'object', although they are strings, dates and integers. I am able to convert the date 'object' to a Pandas datetime dtype, but I'm getting an error when trying to convert the string and integers.

Here is an example:

>>> import pandas as pd
>>> df = pd.read_sql_query('select * from my_table', conn)
>>> df
    id    date          purchase
 1  abc1  2016-05-22    1
 2  abc2  2016-05-29    0
 3  abc3  2016-05-22    2
 4  abc4  2016-05-22    0

>>> df.dtypes
 id          object
 date        object
 purchase    object
 dtype: object

Converting the df['date'] to a datetime works:

>>> pd.to_datetime(df['date'])
 1  2016-05-22
 2  2016-05-29
 3  2016-05-22
 4  2016-05-22
 Name: date, dtype: datetime64[ns] 

But I get an error when trying to convert the df['purchase'] to an integer:

>>> df['purchase'].astype(int)
 ....
 pandas/lib.pyx in pandas.lib.astype_intsafe (pandas/lib.c:16667)()
 pandas/src/util.pxd in util.set_value_at (pandas/lib.c:67540)()

 TypeError: long() argument must be a string or a number, not 'java.lang.Long'

NOTE: I get a similar error when I tried .astype('float')

And when trying to convert to a string, nothing seems to happen.

>>> df['id'].apply(str)
 1 abc1
 2 abc2
 3 abc3
 4 abc4
 Name: id, dtype: object

Documenting the answer that worked for me based on the comment by @piRSquared.

I needed to convert to a string first, then an integer.

>>> df['purchase'].astype(str).astype(int)

pandas >= 1.0

convert_dtypes

The (self) accepted answer doesn't take into consideration the possibility of NaNs in object columns.

df = pd.DataFrame({
     'a': [1, 2, np.nan], 
     'b': [True, False, np.nan]}, dtype=object) 
df                                                                         

     a      b
0    1   True
1    2  False
2  NaN    NaN

df['a'].astype(str).astype(int) # raises ValueError

This chokes because the NaN is converted to a string "nan", and further attempts to coerce to integer will fail. To avoid this issue, we can soft-convert columns to their corresponding nullable type using convert_dtypes:

df.convert_dtypes()                                                        

      a      b
0     1   True
1     2  False
2  <NA>   <NA>

df.convert_dtypes().dtypes                                                 

a      Int64
b    boolean
dtype: object

If your data has junk text mixed in with your ints, you can use pd.to_numeric as an initial step:

s = pd.Series(['1', '2', '...'])
s.convert_dtypes()  # converts to string, which is not what we want

0      1
1      2
2    ...
dtype: string 

# coerces non-numeric junk to NaNs
pd.to_numeric(s, errors='coerce')

0    1.0
1    2.0
2    NaN
dtype: float64

# one final `convert_dtypes` call to convert to nullable int
pd.to_numeric(s, errors='coerce').convert_dtypes() 

0       1
1       2
2    <NA>
dtype: Int64

It's simple

pd.factorize(df.purchase)[0]

Example:

labels, uniques = pd.factorize(['b', 'b', 'a', 'c', 'b'])`

labels
# array([0, 0, 1, 2, 0])

uniques
# array(['b', 'a', 'c'], dtype=object)

My train data contains three features are object after applying astype it converts the object into numeric but before that, you need to perform some preprocessing steps:

train.dtypes

C12       object
C13       object
C14       Object

train['C14'] = train.C14.astype(int)

train.dtypes

C12       object
C13       object
C14       int32

df['col_name'] = pd.to_numeric(df['col_name'])

This is a better option

Follow these steps:

1.clean your file -> open your datafile in csv format and see that there is "?" in place of empty places and delete all of them.

2.drop the rows containing missing values e.g.:

df.dropna(subset=["normalized-losses"], axis = 0 , inplace= True)

3.use astype now for conversion

df["normalized-losses"]=df["normalized-losses"].astype(int)

Note: If still finding erros in your program then again inspect your csv file, open it in excel to find whether is there an "?" in your required column, then delete it and save file and go back and run your program.

comment success! if it works. :)

Cannot comment so posting this as an answer, which is somewhat in between @piRSquared/@cyril's solution and @cs95's:

As noted by @cs95, if your data contains NaNs or Nones, converting to string type will throw an error when trying to convert to int afterwards.

However, if your data consists of (numerical) strings, using convert_dtypes will convert it to string type unless you use pd.to_numeric as suggested by @cs95 (potentially combined with df.apply()).

In the case that your data consists only of numerical strings (including NaNs or Nones but without any non-numeric "junk"), a possibly simpler alternative would be to convert first to float and then to one of the nullable-integer extension dtypes provided by pandas (already present in version 0.24) (see also this answer):

df['purchase'].astype(float).astype('Int64')

Note that there has been recent discussion on this on github (currently an -unresolved- closed issue though) and that in the case of very long 64-bit integers you may have to convert explicitly to float128 to avoid approximations during the conversions.

to change the data type and save it into the data frame, it is needed to replace the new data type as follows:

ds["cat"] = pd.to_numeric(ds["cat"]) or ds["cat"] = ds["cat"].astype(int)

In my case, I had a df with mixed data:

df:
                     0   1   2    ...                  242                  243                  244
0   2020-04-22T04:00:00Z   0   0  ...          3,094,409.5         13,220,425.7          5,449,201.1
1   2020-04-22T06:00:00Z   0   0  ...          3,716,941.5          8,452,012.9          6,541,599.9
....

The floats are actually objects, but I need them to be real floats.

To fix it, referencing @AMC's comment above:

def coerce_to_float(val):
    try:
       return float(val)
    except ValueError:
       return val

df = df.applymap(lambda x: coerce_to_float(x))

If these methods fail, you can try a list comprehension such as this:

df["int_column"] = [int(x) if x.isnumeric() else x for x in df["str_column"] ]

Converting object to numerical int or float.

code is:--

df["total_sqft"] = pd.to_numeric(df["total_sqft"], errors='coerce').fillna(0, downcast='infer')

This was my data

## list of columns l1 = ['PM2.5', 'PM10', 'TEMP', 'BP', ' RH', 'WS','CO', 'O3', 'Nox', 'SO2']

for i in l1:
 for j in range(0, 8431): #rows = 8431
   df[i][j] = int(df[i][j])

I recommend you to use this only with small data. This code has complexity of O(n^2).