3
$\begingroup$

Here's a code I wrote for pre-processing a data set. It works

import numpy as np
import pandas as pd
from sklearn import svm

%matplotlib inline
import matplotlib.pyplot as plt


from sklearn.impute import SimpleImputer
import seaborn as sns; sns.set(font_scale=1.2)

stock=pd.read_csv("C:/Users/Dulangi/Downloads/winequality-red.csv")
stock.head()
X= stock.iloc[:,0:5].values
y= stock.iloc[:,5].values

g=sns.lmplot('alcohol','quality',data=stock,height=7, truncate=True, scatter_kws={"s":100})
imputer = SimpleImputer( strategy = "mean")

imputer = imputer.fit(X[:,1:2])
imputer.fit_transform(X[:,1:2])

imputer = imputer.fit(X[:,4:5])
imputer.fit_transform(X[:,4:5])

I want to know what if i have both strings and numeric data in one column, how to pre-process such data to have all numeric data?

$\endgroup$
3
  • 1
    $\begingroup$ what do you want to do with the string data? Turn it to NaNs, give it an identifying number (e.g. -1), or something else? $\endgroup$ Sep 12, 2019 at 18:41
  • $\begingroup$ In the data set I have in one column both numeric data and string data, i want to specifically locate the rows in which string data is there and convert them to numeric form $\endgroup$ Sep 15, 2019 at 8:59
  • $\begingroup$ will df[0] = df[0].apply(lambda x: int(x)) work for you? 0 is the name of the column $\endgroup$ Sep 16, 2019 at 16:05

1 Answer 1

2
$\begingroup$

Normally pandas should identify numeric types automatically. If it doesn't in your case, there seems to be a formatting issue.

Instead of reading everything into string, I'd rather try to enable pandas to read the types directly in the correct form. First I'd try to pass the column types in a dict to dtype, as in:

pd.read_csv(file_name, dtype={'int_column_name': 'int32', 'float_column_name': 'float32'})

If that is not applicable, because the format is not recognized by pandas automatically, you might want to try some other options.

E.g. if it's just the decimal point or the thousand separator that is different in your data, you can set it over the corresponding keywords in read_csv (thousands and/or decimal).

If that isn't sufficient because you have some special formatting etc., you can also pass your own converter to parse the string into the data type you require for the column, like this as an example of how you could parse numeric values with a currency unit:

import re
mon_re=re.compile('(?P<value>[0-9.]*)([^0-9].*)?')
def strip_off_currency(currency_string):
    m=mon_re.match(currency_string)
    if m is not None:
        return np.float32(m.group('value'))
    else:
        return np.NaN

pd.read_csv(file_name, dtype={'int_column_name': 'int32', 'float_column_name': 'float32'}, converters={'monetary_ammount': strip_off_currency})

The reason why I would prefer this way over reading everything into memory and processing it there is, that it needs less memory and probably also is faster if you process the values just once (at least if you don't need to pass converters).

$\endgroup$

Not the answer you're looking for? Browse other questions tagged or ask your own question.