replacing values in dataframe using Python

Question

wanted to replace values with "NaN" where percentage is greater than 100, in "ROE" column. Reading csv file. Following is the code, not sure how to assign values in 'missing' dataframe, please help

** ROE column in the dataset contains value like "25.00%", "130.00%", "50.00%". so while comparing, first need to convert values in the float by removing last character.

missing = pd.read_csv(local_path + "/Week4/Datasets_Week4/roemissing.csv")
print(missing)

for x in missing["ROE"]:
    y = float(x[:-1])
    if y>100:
        print(x.index)

Austin · Accepted Answer · 2018-02-18 04:53:22Z

1

Use the following:

missing['ROE'] = missing['ROE'].str[:-1].astype(int)
missing.loc[missing.ROE > 100, 'ROE'] = np.nan

missing.ROE > 100 will select the rows in which the ROE value is greater than 100. 'ROE' is the column name where you need to replace values of the selected rows.

All rows with ROE value greater than 100 are selected and then sets the value to NaN to the selected rows in the column ROE.

edited Feb 18, 2018 at 4:53

answered Feb 18, 2018 at 4:22

Austin

26.1k4 gold badges28 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chintan Panchal Over a year ago

In the dataset ROE column contains values like '28.00%', '120.00%' etc... not a float value. So '>' operator doesnt work. Need to first typecast to float by removing last character. Unable to do that

Chintan Panchal Over a year ago

i tried below code "missing.loc[float(missing.ROE[:-1])>100, 'ROE'] = np.nan". it throws an error "cannot convert the series to <class 'float'>"

3novak Over a year ago

I recommend replacing the first line with missing['ROE'] = missing['ROE'].str.strip('%'), and using pd.to_numeric.

piRSquared · Accepted Answer · 2018-02-18 05:25:00Z

1

Use pd.Series.mask. The following will update the dataframe df in place.

df.update(df.ROE.mask(pd.to_numeric(df.ROE.str[:-1]) > 100))

answered Feb 18, 2018 at 5:25

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Collectives™ on Stack Overflow

replacing values in dataframe using Python

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related