2

I have the following column in my dataframe:

Column1   Column2    Column3     Column4
a            1           2           a     
1            2           a           c
b            3           c           d
3            2           1           b
4            2           1           a
c            1           d           a

The type of these columns is object, I would like to convert Column1, Column2 and Column3 to numeric type int8 while keeping Column4 as type object. To do so, I had tried to use pd.to_numeric(data.Column1) (I was planning to do the same after with Column2 and Column3) but I get the following error:

ValueError: Unable to parse string "a" at position 0

Which is obvious why it is happening. I was wondering if there is any way that would allow me to get rid of of that rows formed by strings in these 3 columns so after that, I would get:

Column1    Column 2    Column 3   Column 4
3            2           1           b
4            2           1           a

Is there a way to achieve so? Or is there another way that would allow me to?

Edit: I have checked the question in Remove non-numeric rows in one column with pandas, but it didn't solve my problem, as I have more columns in my dataset than just two and one of them I don't want to convert it to numeric.

Marisa
  • 1,135
  • 3
  • 20
  • 33

1 Answers1

3

Use apply with to_numeric for replace non numeric to missing values, then remove NaNs rows by dropna and last cast to integers:

df = df.apply(lambda x: pd.to_numeric(x, errors='coerce')).dropna().astype(int)
print (df)
   Column1  Column2  Column3
3        3        2        1
4        4        2        1

Detail:

print (df.apply(lambda x: pd.to_numeric(x,errors='coerce')))
   Column1  Column2  Column3
0      NaN      1.0      2.0
1      1.0      2.0      NaN
2      NaN      NaN      NaN
3      3.0      2.0      1.0
4      4.0      2.0      1.0
5      NaN      1.0      NaN

EDIT:

Another solution is check if not missing values with DataFrame.all with boolean indexing:

cols = ['Column1','Column2','Column3']
#define columns for check numeric
mask = df[cols].apply(lambda x: pd.to_numeric(x, errors='coerce')).notnull().all(axis=1)
#filtering
df = df[mask]
#converting to integers
df[cols] = df[cols].astype(int)
print (df)
   Column1  Column2  Column3 Column4
3        3        2        1       b
4        4        2        1       a

print (df.dtypes)
Column1     int32
Column2     int32
Column3     int32
Column4    object
dtype: object
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Thank you! How could I approach it if one of my columns is formed of strings and I don't want to convert it to numeric? I have edited my question to ilustraste it. I guess I couldn't apply a lambda in that case, am I right? – Marisa Sep 05 '18 at 09:05
  • @Marisa - Better is filtering columns for check with list, answer was edited. – jezrael Sep 05 '18 at 09:09
  • Mask is returning me all the entries as False. I checked ``df.Column2[0]`` and it has returned me that is of type string ("1"). Could it be because as I have strings is taking everything as so? – Marisa Sep 05 '18 at 09:17
  • @Marisa - Maybe need `mask = df[cols].apply(lambda x: pd.to_numeric(x.str.strip('"'), errors='coerce')).notnull().all(axis=1)` ? – jezrael Sep 05 '18 at 09:20
  • Still is not working, the numbers are not between " " in my dataset. I just wrote them before to stressed the fact that they are strings. – Marisa Sep 05 '18 at 09:27
  • @Marisa - Is possible check if some whitespaces or similar in data, e.g. by `print (df[cols].to_dict('l'))` ? Because it seems data related problem. – jezrael Sep 05 '18 at 09:29
  • I get ``{'Column2': ['1', '2', '3', '2', '2', '1'], 'Column3': ['2', 'a', 'c', '1', '1', 'd'], 'Column1': ['a', '1', 'b', '3', '4', 'c']}`` – Marisa Sep 05 '18 at 09:42
  • @Marisa - I check both solutions by `df = pd.DataFrame({'Column2': ['1', '2', '3', '2', '2', '1'], 'Column3': ['2', 'a', 'c', '1', '1', 'd'], 'Column1': ['a', '1', 'b', '3', '4', 'c']})` and working nice. – jezrael Sep 05 '18 at 10:00
  • @Marisa - Problem is with real data? – jezrael Sep 05 '18 at 10:05
  • Yes, it is. Maybe I should ask to the person who sent it to send it in a different format – Marisa Sep 05 '18 at 10:21
  • @Marisa - Yes, good idea. If working nice sample data problem is in data format obviously. – jezrael Sep 05 '18 at 10:22