Use value in another column's previous row by group

Question

DATA

Division    Name      start_date  
A           apple     2001-01-05
A           banana    2001-03-06
A           apple     2001-06-08
A           orange    2001-07-09
B           peach     2001-01-10
B           melon     2001-06-02
B           berry     2001-08-19

I need to create an end_date, which is the start_date of the next person in the same division. For the last person observed, there is no end_date, so I would just put today's date 2019-04-06.

GOAL

Division    Name      start_date    end_date
A           apple     2001-01-05    2001-03-06
A           banana    2001-03-06    2001-06-08
A           apple     2001-06-08    2001-07-09
A           orange    2001-07-09    2019-04-06
B           peach     2001-01-10    2001-06-02
B           melon     2001-06-02    2001-08-19
B           berry     2001-08-19    2019-04-06

I tried

data['end_date'] = data.groupby('Division')['start_date'].index+1

But got an error message:

AttributeError: Cannot access attribute 'index' of 'SeriesGroupBy' objects, try using the 'apply' method

Does anyone know how to fix this?

Many thanks!

rafaelc · Accepted Answer · 2019-04-06 18:51:08Z

1

You can groupby division and shift one row up.

df['end_date'] = df.groupby('Division').start_date.shift(-1)

Then just fillna() with today's date

df = df.fillna(datetime.date.today())

    Division    Name    start_date  end_date
0   A           apple   2001-01-05  2001-03-06
1   A           banana  2001-03-06  2001-06-08
2   A           apple   2001-06-08  2001-07-09
3   A           orange  2001-07-09  2019-04-06
4   B           peach   2001-01-10  2001-06-02
5   B           melon   2001-06-02  2001-08-19
6   B           berry   2001-08-19  2019-04-06

answered Apr 6, 2019 at 18:51

rafaelc

59.4k15 gold badges64 silver badges87 bronze badges

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

1 Comment

IceAloe Over a year ago

This works perfectly!! Thank you so much @RafaelC!!

Collectives™ on Stack Overflow

Use value in another column's previous row by group

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related