Calculate a mean of a range of values in each row, in a vectorized way

Q&A DQ Courses
#1

Screen Link:

We need to do the following cleaning: data=[2-3, 4.0, 10-13, 40,…]. Those are years, or range of years. So, the person stayed in the enterprise between 2 and 3 years in the first case. I need to convert all of them to a single value. I could do it with a for loop, but I wonder if there is some vectorized form.

For example, if instead of the mean of each range I would just take the lowest value (in data[0] would be 2), it could be:
teste= combined_updated[‘institute_service’].astype(‘str’).str.replace(‘Less than 1 year’, ‘1’).str.replace(‘More than 20 years’, ‘20’).str.split(’-’).str[0].astype(‘float’)

So, I use str[0] to get the first value, cause I will get a list [2,3] for data[0]. is there some way to convert them to floats (inside the list) and then do the mean, in a vectorized way? …

Not even sure I was clear, sorry.

#2

@ncirauqu,

It’s me again :grinning:

You can use this:

combined_updated['institute_service'] = combined_updated['institute_service'].astype('str').str.extract(r'(\d+)', expand=True).astype('float')
#3

Hi Elena,
Thanks again :slight_smile: :slight_smile:But, if I did not understand wrong, this would take the first number from the range, isn’t it? For example, if we have ‘7-10’, it will take 7. I wonder if there is a way to do the mean instead (8.5 in the example, or 8, if we leave int).

#4

Hi @ncirauqu,

Ah, I understood before that you wanted exactly the lowest value :slightly_smiling_face:

Then try this:

combined_updated['institute_service'] = combined_updated['institute_service'].astype('str').str.findall(r'(\d+)')

Then, on the resulting column, you can find the mean by using sum(lst)/len(lst) .