How to subtract two columns given one lag differences and group by multiple columns in Python

Question

I have a data with two ID columns and two date columns as below:

import numpy as np
import pandas as pd

mydata = {'ID1': [1,1,2,3,3,4],
    'ID2': [1,2,3,4,5,6],
    'Date1': ['2011-04-23','2011-05-13','2012-04-23','2012-05-13','2011-08-23','2011-08-26'],
    'Date2': ['2011-04-25','2011-05-23','2012-04-1','2011-05-18','2011-08-24','2011-08-29']
     }
    mydata = pd.DataFrame(mydata)

I would like to create a new column, e.g. days, as below: if ID1 is unique, then -1; if ID1 is not unique, calculate differences between Date1 (with lag) and Date2. The below code somehow works; it does not produce -1 for unique ID1. It is also a bit odd. Appreciate your help for any alternative solutions.

mydata['Date1'] = pd.to_datetime(mydata['Date1'])
mydata['Date2'] = pd.to_datetime(mydata['Date2'])

mydata = mydata.sort_values(['ID1', 'Date1'], ascending=[True, True])

diff_time = mydata['Date2'].rsub(mydata['Date1'].shift(-1), axis=0)
mydata['days'] = np.where(mydata['ID1']==mydata['ID1'].shift(-1),
                      (diff_time.dt.days*24+diff_time.astype(str).str.split('[ :]').str[2].astype(float))/24,0)

Output:

    ID1 ID2  Date1       Date2      days
0   1   1   2011-04-23  2011-04-25  18.0
1   1   2   2011-05-13  2011-05-23  0.0
2   2   3   2012-04-23  2012-04-01  0.0      # 0.0 here should be -1 as ID1 is unique
4   3   5   2011-08-23  2011-08-24  263.0
3   3   4   2012-05-13  2011-05-18  0.0
5   4   6   2011-08-26  2011-08-29  0.0     # 0.0 here should be -1 as ID1 is unique

Answer 1

You can use DataFrameGroupBy.shift and for duplicated ID with Series.duplicated else -1 in numpy.where:

mydata['Date1'] = pd.to_datetime(mydata['Date1'])
mydata['Date2'] = pd.to_datetime(mydata['Date2'])

mydata = mydata.sort_values(['ID1', 'Date1'], ascending=[True, True])

mask = mydata['ID1'].duplicated(keep=False)
diff_time = mydata['Date2'].rsub(mydata.groupby('ID1')['Date1'].shift(-1))
mydata['days'] =  np.where(mask, diff_time.dt.days, -1)
print (mydata)
   ID1  ID2      Date1      Date2   days
0    1    1 2011-04-23 2011-04-25   18.0
1    1    2 2011-05-13 2011-05-23    NaN
2    2    3 2012-04-23 2012-04-01   -1.0
4    3    5 2011-08-23 2011-08-24  263.0
3    3    4 2012-05-13 2011-05-18    NaN
5    4    6 2011-08-26 2011-08-29   -1.0