Remove characters from a cell and divide remaining float by 2 in python pandas

user_19230313 0
Viewed 60

I have a dataframe with multiple columns where some cells contain the characters "DL" and a float. The other cells contain floats only.

For example:

Column1 Column2
row1 DL10.4 5.6
row2 4.7 DL8.8

I want use python to remove the characters "DL" and divide the remaining floats by 2. The cells without characters should be unchanged and not divided by 2.

Expected result:

Column1 Column2
row1 5.2 5.6
row2 4.7 4.4
pandas python string
4 Answers

Use Series.str.extractSeries.str.extractall for values after DL, divide by 2 and replace non DL valeus by original DataFrame:

df1 = df.apply(lambda x: x.str.extract('DL(\d+\.\d+)', expand=False))
df = df1.astype(float).div(2).fillna(df).astype(float)
print (df)
      Column1  Column2
row1      5.2      5.6
row2      4.7      4.4
user_29010020

You could use applymap() method on the original DF and store the result in a new DF:

df2 = df.applymap(lambda x: float(x[2:])/2 if isinstance(x, str) else x)

This checks if the value is a string then slices all characters except the first two before converting to a float and dividing by two.

And if you want to keep the values rounded to 1.d.p you can just use the modified version below:

 df2 = df.applymap(lambda x: round(float(x[2:])/2, 1) if isinstance(x, str) else x)
user_120627090

like this:

df.applymap(lambda x: float(x.replace('DL', ''))/2 if 'DL' in x else x)
'''
     Column1 Column2
row1     5.2     5.6
row2     4.7     4.4
'''
user_192366870

I will assume you have a way of looping through each row in the dataset. With that in mind, something like this should work for you:

import re
for row in df.iterrows():
    for col_value in row:
        reg_match = re.match("^DL([0-9]+\.[0-9])", col_value)
        if reg_match:
            num = float(reg_match.group(1))
            col_value = num

I'm not sure whether or not Python will complain about modifying the col_value in this case. If it does, you probably want to save the new values into a new data frame instead.

user_124095720
Related