Displaying next value on a column considering groups in Pandas Dataframe

Question

I'm having this example dataframe and I need to display the next delivery Date for a specific client-region group. Date could be either coded as a string or datetime, I'm using a string in this example.

# Import pandas library 
import pandas as pd 
import numpy as np
data = [['NY', 'A','2020-01-01', 10], ['NY', 'A','2020-02-03', 20], ['NY', 'A','2020-04-05', 30], ['NY', 'A','2020-05-05', 25],
       ['NY', 'B','2020-01-01', 15], ['NY', 'B','2020-02-02', 10], ['NY', 'B','2020-02-10', 20],
       ['FL', 'A','2020-01-01', 15], ['FL', 'A','2020-02-01', 10], ['FL', 'A','2020-03-01', 12], ['FL', 'A','2020-04-01', 25], ['FL', 'A','2020-05-01', 20]
       ] 

# Create the pandas DataFrame 
df = pd.DataFrame(data, columns = ['Region', 'Client', 'deliveryDate', 'price']) 
  
# print dataframe. 
df 

Region  Client  deliveryDate    price
0   NY  A   2020-01-01  10
1   NY  A   2020-02-03  20
2   NY  A   2020-04-05  30
3   NY  A   2020-05-05  25
4   NY  B   2020-01-01  15
5   NY  B   2020-02-02  10
6   NY  B   2020-02-10  20
7   FL  A   2020-01-01  15
8   FL  A   2020-02-01  10
9   FL  A   2020-03-01  12
10  FL  A   2020-04-01  25
11  FL  A   2020-05-01  20

Desired output:

data2 = [['NY', 'A','2020-01-01', '2020-02-03', 10], ['NY', 'A','2020-02-03', '2020-04-05', 20], ['NY', 'A','2020-04-05', '2020-05-05', 30], ['NY', 'A','2020-05-05', float('nan'), 25],
       ['NY', 'B','2020-01-01', '2020-02-02', 15], ['NY', 'B','2020-02-02','2020-02-10', 10], ['NY', 'B','2020-02-10', float('nan'), 20],
       ['FL', 'A','2020-01-01', '2020-02-01', 15], ['FL', 'A','2020-02-01', '2020-03-01', 10], ['FL', 'A','2020-03-01', '2020-04-01', 12], ['FL', 'A','2020-04-01', '2020-05-01', 25], ['FL', 'A','2020-05-01', float('nan'), 20]
       ] 

# Create the pandas DataFrame 
df2 = pd.DataFrame(data2, columns = ['Region', 'Client', 'deliveryDate', 'nextDelivery', 'price']) 

Region  Client  deliveryDate    nextDelivery    price
0   NY  A   2020-01-01  2020-02-03  10
1   NY  A   2020-02-03  2020-04-05  20
2   NY  A   2020-04-05  2020-05-05  30
3   NY  A   2020-05-05  NaN 25
4   NY  B   2020-01-01  2020-02-02  15
5   NY  B   2020-02-02  2020-02-10  10
6   NY  B   2020-02-10  NaN 20
7   FL  A   2020-01-01  2020-02-01  15
8   FL  A   2020-02-01  2020-03-01  10
9   FL  A   2020-03-01  2020-04-01  12
10  FL  A   2020-04-01  2020-05-01  25
11  FL  A   2020-05-01  NaN 20

Thanks in advance.

Answer 1

Assuming the delivery dates are ordered, how about grouping by region & client, then applying a shift?

df['nextDelivery'] = df.groupby(['Region','Client']).shift(-1)['deliveryDate']

Output:

   Region Client deliveryDate  price nextDelivery
0      NY      A   2020-01-01     10   2020-02-03
1      NY      A   2020-02-03     20   2020-04-05
2      NY      A   2020-04-05     30   2020-05-05
3      NY      A   2020-05-05     25          NaN
4      NY      B   2020-01-01     15   2020-02-02
5      NY      B   2020-02-02     10   2020-02-10
6      NY      B   2020-02-10     20          NaN
7      FL      A   2020-01-01     15   2020-02-01
8      FL      A   2020-02-01     10   2020-03-01
9      FL      A   2020-03-01     12   2020-04-01
10     FL      A   2020-04-01     25   2020-05-01
11     FL      A   2020-05-01     20          NaN