problem cleaning characters/str from a column and make it an int

user_17214450 0
Viewed 53

I wrote this function to clean the + and , out of a column:

def data_clean_installs(x):
    if '+' in x:
        return float(x.replace('+',''))
    elif ',' in x:
        return float(x.replace(',',''))
    else:
        return float(x)

I want to use the function to make a new int column called 'Clean_Installs' and ran this:

apr['Clean_Installs'] = apr.Installs.astype('str').apply(data_clean_installs).apply(int)

and get this ValueError: could not convert string to float: '10,000'

I tried everything I can think of, too much to put here and will take any inputs please... Oh, I am new and this is my first question ever. Sorry if I violated any rules... Really hope someone can help. Thanks!

pandas python
3 Answers

No need for a custom function here since you seem to already be using Pandas:

apr.Installs.str.replace("[,+]", "", regex=True).apply(int)

My only concern with using .apply(int) is that it'll fail in the case you have values in the column that won't translate to integers, like "1,000.53".

For a little bit of an explanation, regex=True is telling Pandas that the pattern (the first argument in Series.str.replace) should be treated as a regular expression.

The square brackets in the pattern [,+] form what's known as a character class. The pattern is basically telling Pandas, "use regex to match any string containing any of these characters, "," or "+" or both, and replace them with the empty string.

Regex is super powerful, but there's a time and a place for it. This is one of those times!

user_62987120

Probably the 10,000 used to be +10,000 or something like that. In the function data_clean_installs you try to convert if a + is found, but there also is a , in there. Your function should look like this:

def data_clean_installs(x):
    return float(x.replace('+', '').replace(',', ''))

You don't need to check if x has a + or a ,, the replace will already to that for you and will convert it to '' automatically.

Also if you are converting it to int afterwards you can replace the float call with an int call in the data_clean_installs if all values are guaranteed to be integers

user_103390080

You can do the following:

import re

apr['Clean_Installs'] = apr.Installs.apply(lambda x: int(re.sub('[+,]', '', x)))
user_117696370
Related