How to add pandas data frame column based on other rows values

Question

I am trying to add a new column and set its value based on other rows values. Lets say we have the following data frame:

    df = pd.DataFrame({
         'B':[1,2,3,4,5,6],
         'C':[7,8,9,4,2,3],
         'D':[1,3,5,7,1,0],
    })

With this corresponding output

I want to add a new column 'E', which has the following value: E = df.C value where B = B + 2.
For example, the first value of E should be 3 (we select the row where B = 0+2 = 2, and select C value from that row). I tried the following

    f['E'] = np.where(f.B == (f['B']+2))['C']

But it's not working

Quang Hoang · Accepted Answer · 2020-11-11 16:38:38Z

1

You can set B and index and use that to map the modified data:

df['E'] = df['B'].add(2).map(df.set_index('B')['C'])

Output:

   B  C  D    E
0  0  1  7  3.0
1  1  2  8  4.0
2  2  3  9  5.0
3  3  4  4  6.0
4  4  5  2  NaN
5  5  6  3  NaN

answered Nov 11, 2020 at 16:38

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

3 Comments

user3379482 Over a year ago

Thanks, never thought of that!

user3379482 Over a year ago

Is it possible to add a condition? So it becomes: where B = B + 2 and D=value?

Quang Hoang Over a year ago

@user3379482 maybe df[df['D']==value].set_index('B')['C']?

Collectives™ on Stack Overflow

How to add pandas data frame column based on other rows values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related