Replace value in Pandas Dataframe based on condition

Question

I have a dataframe column with some numeric values. I want that these values get replaced by 1 and 0 based on a given condition. The condition is that if the value is above the mean of the column, then change the numeric value to 1, else set it to 0.

Here is the code I have now:

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd

dataset = pd.read_csv('data.csv')
dataset = dataset.dropna(axis=0, how='any')

X = dataset.drop(['myCol'], axis=1)
y = dataset.iloc[:, 4:5].values

mean_y = np.mean(dataset.myCol)

The target is the dataframe y. y is like so:

and so on. mean_y is equal to 3.55. Therefore, I need that all values greater than 3.55 to become ones, and the rest 0.

I applied this loop, but without success:

for i in dataset.myCol:
    if dataset.myCol[i] > mean_y:
        dataset.myCol[i] = 1
    else:
        dataset.myCol[i] = 0

The output is the following:

What am I doing wrong? Can someone please explain me the mistake?

Thank you!

MaxU - stand with Ukraine · Accepted Answer · 2018-04-16 12:34:33Z

6

Try this vectorized approach:

dataset.myCol = np.where(dataset.myCol > dataset.myCol.mean(), 1, 0)

answered Apr 16, 2018 at 12:34

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

3 Comments

AndrewDAG Over a year ago

This worked so well! Thanks. Can you please tell me why my approach didn't work?

MaxU - stand with Ukraine Over a year ago

you are indexing your dataset.myCol series by values instead of indices. i - will contain values and dataset.myCol[i] expects indices. NOTE: loops should be used as a last resort, when nothing else helps in Pandas

AndrewDAG Over a year ago

Thank you, Max. Got it now :)

jezrael · Accepted Answer · 2018-04-16 12:40:36Z

2

Convert boolean mask to integer - Trues to 1 and Falses to 0:

print (dataset.myCol > mean_y)
0     True
1    False
2    False
3    False
Name: myCol, dtype: bool

dataset.myCol = (dataset.myCol > mean_y).astype(int)
print (dataset)
   myCol
0      1
1      0
2      0
3      0

For your aproach, not recommended because slow need iterrows for set values by columns and index values:

for i, x in dataset.iterrows():
    if dataset.loc[i, 'myCol'] > mean_y:
        dataset.loc[i, 'myCol'] = 1
    else:
        dataset.loc[i, 'myCol'] = 0

edited Apr 16, 2018 at 12:40

answered Apr 16, 2018 at 12:34

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Replace value in Pandas Dataframe based on condition

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