Pandas taking value based on previous value in a different column

Question

It may be simple, but for some reason I can't think of a good way to solve it.

So, let's say this is my DataFrame:

     a    b 
0   99    3
1   99    56
2    1    7
3    1    80  
4    1    55
5    2    601
6    2    -4
7    1    33
8    1    22

I would like to create a new column, c, where the values will be the last value of b in the previous value of a, so the output should be:

     a    b      c
0   99    3     nan
1   99    56    nan
2    1    7     56
3    1    80    56  
4    1    55    56
5    2    601   55
6    2    -4    55
7    1    33    -4
8    1    22    -4

Any help will be appreciated!

Are a values always increasing?

borjasanlei
– borjasanlei

2020-06-24 12:35:43 +00:00
Commented Jun 24, 2020 at 12:35 — borjasanlei
– borjasanlei, Commented Jun 24, 2020 at 12:35
No, I'll edit the example

Binyamin Even
– Binyamin Even

2020-06-24 12:37:44 +00:00
Commented Jun 24, 2020 at 12:37 — Binyamin Even
– Binyamin Even, Commented Jun 24, 2020 at 12:37

Zero · Accepted Answer · 2020-06-24 12:55:22Z

3

You could use also where with diff


In [73]: df['b'].where(df['a'].diff(-1).ne(0)).shift().ffill()
Out[73]: 
0     NaN
1     NaN
2    56.0
3    56.0
4    56.0
5    55.0
6    55.0
7    -4.0
8    -4.0
Name: b, dtype: float64

In [74]: df['c'] = df['b'].where(df['a'].diff(-1).ne(0)).shift().ffill()

In [75]: df
Out[75]: 
    a    b     c
0  99    3   NaN
1  99   56   NaN
2   1    7  56.0
3   1   80  56.0
4   1   55  56.0
5   2  601  55.0
6   2   -4  55.0
7   3   33  -4.0
8   3   22  -4.0

answered Jun 24, 2020 at 12:55

Zero

77.4k22 gold badges153 silver badges153 bronze badges

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

Comments

Quang Hoang · Accepted Answer · 2020-06-24 12:46:39Z

1

I would compare df['a'] to its shift to identify value changes, and ffill() with shift():

df['c'] = df.loc[df['a'] != df['a'].shift(-1),'b']
df['c'] = df['c'].ffill().shift()

Output:

    a    b     c
0  99    3   NaN
1  99   56   NaN
2   1    7  56.0
3   1   80  56.0
4   1   55  56.0
5   2  601  55.0
6   2   -4  55.0
7   1   33  -4.0
8   1   22  -4.0

edited Jun 24, 2020 at 12:46

answered Jun 24, 2020 at 12:42

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

1 Comment

Binyamin Even Over a year ago

Same groups of a can occur multiple times, I edited my example to emphasize that.

Collectives™ on Stack Overflow

Pandas taking value based on previous value in a different column

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related