Substitute values in dataframe

Question

I have a dataframe with 3 columns(A,B,C). I wanted to update column C with column A when column B=22. I have written update statement like this, but it's updating NaN for non matching rows. Could you tell me how to update data in dataframe?

df = pd.DataFrame(data=[[10,20,30],[11,21,31],[12,22,32]], columns=['A','B','C'])
df.C = df[df.B==22].A

score 3 · Answer 1 · answered Sep 20 '20 at 15:36

3

One of several ways to do this, and yes it requires an additional package, but if you aren't aware of np.where it's pretty handy.

import numpy as np

df['C'] = np.where(df['B']==22, df['A'], df['C'])

answered Sep 20 '20 at 15:36

Chris

15,819
3
24
37

Arty · Answer 2 · 2020-09-20T15:50:12.200

3

df.loc[df.B==22, 'C'] = df.loc[df.B==22, 'A']

edited Sep 20 '20 at 15:50

answered Sep 20 '20 at 15:39

Arty

14,883
6
36
69

I would probably use `.loc` on the *right-hand-side* also. – wwii Sep 20 '20 at 15:45
If you have unique indexes you can use only `df['A']` on the right-hand-side – Terry Sep 20 '20 at 19:26

score 3 · Accepted Answer · answered Sep 20 '20 at 15:45

3

Let us try mask

df.C.mask(df.B==22, df.A,inplace=True)
df
    A   B   C
0  10  20  30
1  11  21  31
2  12  22  12

answered Sep 20 '20 at 15:45

BENY

317,841
20
164
234

Series.mask is definitely more intuitive than Series.where. – wwii Sep 20 '20 at 15:56

anky · Answer 4 · 2020-09-20T15:52:10.963

Another alternative is using loc and reindex:

df['C'] = df.loc[df.B==22,'A'].reindex(df.index).fillna(df['C'])

Ideally you can use np.where for such cases, however here is why your code doesnot work:

The below

df[df.B==22].A

Returns:

2    12

you will see that the index of the returned values is 2, so when you set df.C (use bracket notation instead of a . notation) , it updates the series named C to the result of previous which does not contain the other indexes (but only 2) , hence other indexes are set as np.nan

Also it is highly discouraged to use chained indexing when assigning values as it leads to this warning.

Substitute values in dataframe

4 Answers4