Remove erroneous values from column in Pandas

user_5942100 0
Viewed 120

I have a dataframe, df, where I am seeing duplicate or unwanted values within my column. I would like to remove any numerical values that come before the #T ex. hi 1 1.92T,I wish to remove the single '1' to create: hi 1.92T

Data

type            value
hi 1 1.92T      5
hello 6 6.4T    5
yy16 1 6 12T    6
free 1 1 12T    7
Gal 0 0 0T      7
ex 8 8 8T       8

Desired

 type             value
    hi    1.92T   5
    hello 6.4T    5
    yy16  12T     6
    free  12T     7
    Gal   0T      7
    ex   8T       8

Doing

import re
df.type = re.sub("\d+\.?\d+?", "",  df.type)

I am still researching this. Any suggestion is appreciated.

pandas numpy python
3 Answers

Let's use replace with the following pattern, details here:

pat = r'\b([\d\.]+)([^\d\.T])'
df['type'] = df['type'].str.replace(pat, r'\2')

Output:

         type  value
0    hi 1.92T      5
1  hello 6.4T      5
2    yy16 12T      6
3    free 12T      7
4      Gal 0T      7
5       ex 8T      8

If you want to keep the spaces, use this pattern instead:

pat = r'\b([\d\.]+)([^\d\.T])'
df['type'] = df['type'].str.replace(pat, r'\2')

Output:

          type  value
0    hi  1.92T      5
1  hello  6.4T      5
2   yy16   12T      6
3   free   12T      7
4     Gal   0T      7
5      ex   8T      8
user_42384080

How about replacing the spaces and everything between? Is T ever not in the last group of characters in the string? Is that the the best way to define the pattern? How about:

df['type'] = df['type'].str.replace('(\s+.*\s+)', ' ')
df

         type  value
0    hi 1.92T      5
1  hello 6.4T      5
2    yy16 12T      6
3    free 12T      7
4      Gal 0T      7
5       ex 8T      8
user_63667700

How about:

df['filtered_type']=\
df.type.apply(lambda row: ' '.join([row.split(' ')[0],row.split(' ')[-1]]))

This will give you:

           type  value filtered_type
0    hi 1 1.92T      5      hi 1.92T
1  hello 6 6.4T      5    hello 6.4T
2  yy16 1 6 12T      6      yy16 12T
3  free 1 1 12T      7      free 12T
4    Gal 0 0 0T      7        Gal 0T
5     ex 8 8 8T      8         ex 8T

Or, following suggestion in the comments, in the same spirit but without lambda function, can do:

s = df['type'].str.split(' ').str[0]
df['type'] = ' '.join(s.str[0], s.str[-1])
user_85654380
Related