pandas - Replaceing values in a column

Question

I have a dataframe df like this:

            ID_USER  CODE
0          433805  11.0
24          5448   44.0
48          3434   11.0
72          34434  11.0
96          3202   33.0
120         23766  33.0
153         39457  44.0
168         4113   33.0
172         3435   13.0
374         34093  11.0

And I try to replace the values from the 'CODE' column with other values.

11.0 and 44.0 -> 1
33.0 -> 0
all other -> 5

So I did among others the following:

df['CODE'] = df.apply(lambda s:func1(s))


def func1(x):
    if (x['CODE'] == 11.0) or (x['CODE'] == 44.0):
        return 1
    elif (x['CODE'] == 33.0):
        return 0
    else:
        return 5

And I get this error:

KeyError: ('NTL', u'occurred at index ID_UC')

How can I solve my problem?

Vaishali · Accepted Answer · 2017-03-30 19:03:11Z

3

You can use np.where

df1.CODE = np.where((df1.CODE == 11.0) | (df1.CODE == 44.0), 1, np.where((df1.CODE == 33.0), 0, 5))


    ID_USER CODE
0   433805  1
24  5448    1
48  3434    1
72  34434   1
96  3202    0
120 23766   0
153 39457   1
168 4113    0
172 3435    5
374 34093   1

answered Mar 30, 2017 at 19:03

Vaishali

38.5k5 gold badges62 silver badges88 bronze badges

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

Comments

piRSquared · Accepted Answer · 2017-03-30 21:37:56Z

2

Short answer is that you forgot to specify the axis over which to apply. By default apply will iterate over every column. Your function is looking for x['CODE'] and therefore it's safe to assume you meant this to iterate over rows

df.apply(lambda s:func1(s), axis=1)

0      1
24     1
48     1
72     1
96     0
120    0
153    1
168    0
172    5
374    1
dtype: int64

You can shorten this up with

df.apply(func1, 1)

That said, I'd improve your function to assume you are iterating over a pd.Series and not rows of a pd.DataFrame and apply it to the targeted column.

def func2(x):
    return 1 if (x == 11) or (x == 44) else 0 if (x == 33) else 5

df.CODE.apply(func2)

Even better, I like using map + lambda

m = {11: 1, 44: 1, 33: 0}

df.CODE.map(lambda x: m.get(x, 5))

All together

df.assign(CODE=df.CODE.map(lambda x: m.get(x, 5)))

     ID_USER  CODE
0     433805     1
24      5448     1
48      3434     1
72     34434     1
96      3202     0
120    23766     0
153    39457     1
168     4113     0
172     3435     5
374    34093     1

edited Mar 30, 2017 at 21:37

answered Mar 30, 2017 at 21:28

piRSquared

296k68 gold badges509 silver badges654 bronze badges

1 Comment

Vaishali Over a year ago

Map/replace was my first attempt but couldn't come up with how to handle the "else" condition. Thanks for this solution

Collectives™ on Stack Overflow

pandas - Replaceing values in a column

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related