Conditional replacing of column values in dataframe?

Question

I have a dataframe and I want to replace some values in that column based on a condition. My dataframe looks like this

ID    customer_name   arrival_month    leaving_month
1524   ABC              201508           201605 
1185   XYZ              201701           201801
8456   IJK              201801           201902

I am trying a simple operation here. I want to change the values in leaving_month column by currentmonth value =201802 where leaving_month>201802. I have tried by .loc and it gives the error below.

df.loc[(df['leaving_month'] > 201802)] = 201802
KeyError: 'leaving_month'

I have also tried np.where which also gives an error.

df['leaving_month']=np.where(df['leaving_month']>currentmonth, currentmonth)
KeyError: 'leaving_month'

I have also tried with brute looping

for o in range(len(df)):
            if(df.loc[o,'leaving_month']>currentmonth):
                df.loc[o,'leaving_month']=currentmonth
IndexingError: Too many indexers

Can someone please point me in the right direction or figure out what am I doing wrong or suggest a better solution? This is quite simple problem but somehow I am not getting through.

KeyError: 'leaving_month' clearly says that df does NOT have 'leaving_month' column. What is the output of df.columns.tolist()? — MaxU - stand with Ukraine
– MaxU - stand with Ukraine, Commented Feb 15, 2018 at 15:38
I don't get an error in your updated example. There is still a mistake though... see my answer. — Alex
– Alex, Commented Feb 15, 2018 at 17:11

Alex · Accepted Answer · 2018-02-15 17:12:16Z

1

You are replacing an entire row. Instead, set a specific column with the .loc. See the second indexer in the solution below.

df.loc[df['leaving_month'] > 201802, 'leaving_month'] = 201802
df

returns

     ID customer_name  arrival_month  leaving_month
0  1524           ABC         201508         201605
1  1185           XYZ         201701         201801
2  8456           IJK         201801         201802

You can read about DataFrame indexing in the Pandas docs.

edited Feb 15, 2018 at 17:12

answered Feb 15, 2018 at 15:10

Alex

19.2k9 gold badges65 silver badges82 bronze badges

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

1 Comment

N91 Over a year ago

thanks for your input @Alex. unfortunately, both mistakes that you pointed out are not in the actual code.. just typo while copy pasting here

Collectives™ on Stack Overflow

Conditional replacing of column values in dataframe?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related