Suppose we have the following dataframe:
Date Type Country Value
0 2016-04-30 A NL 1
1 2016-04-30 A BE 2
2 2016-04-30 B NL 3
3 2016-04-30 B BE 4
4 2016-04-30 C NL 5
5 2016-04-30 C BE 6
6 2016-04-30 C FR 7
7 2016-04-30 C UK 8
8 2016-05-31 A NL 9
9 2016-05-31 A BE 10
10 2016-05-31 A FR 11
11 2016-05-31 B NL 12
12 2016-05-31 B BE 13
13 2016-05-31 B FR 14
14 2016-05-31 C NL 15
15 2016-05-31 C BE 16
16 2016-05-31 C UK 17
17 2016-05-31 C SL 18
18 2016-06-30 A NL 19
19 2016-06-30 B FR 20
20 2016-06-30 B UK 21
21 2016-06-30 B SL 22
22 2016-06-30 C NL 23
23 2016-06-30 C BE 24
Which can be computed with the following code:
df = pd.DataFrame([['2016-04-30','A','NL',1], ['2016-04-30','A', "BE" ,2], ['2016-04-30', 'B', 'NL',3], ['2016-04-30','B','BE',4], ['2016-04-30','C','NL',5], ['2016-04-30','C','BE',6],['2016-04-30','C','FR', 7], ['2016-04-30','C','UK',8], ['2016-05-31','A','NL',9], ['2016-05-31','A','BE',10], ['2016-05-31','A','FR',11], ['2016-05-31','B','NL',12], ['2016-05-31','B','BE',13], ['2016-05-31','B','FR',14], ['2016-05-31','C','NL',15], ['2016-05-31','C','BE',16], ['2016-05-31','C','UK',17], ['2016-05-31','C','SL',18], ['2016-06-30','A','NL',19], ['2016-06-30','B','FR',20], ['2016-06-30','B','UK',21], ['2016-06-30','B','SL',22], ['2016-06-30','C','NL',23], ['2016-06-30','C','BE',24]], columns=['Date','Type' ,'Country' ,'Value'])
I want to add an extra column 'ValueShifted', that basically shifts the observations across time. Hence, for instance for the observation 'Date: 2016-05-31, Type: B, Country: BE', I would want to set 'ValueShifted' to 4. In case the observation is not available in the previous period, I would want to set it to NaN.
I could do it brute force, but this is gonna take too much time for my actual dataset. Is there a way to do this efficiently?
Expected df:
Date Type Country Value ValueShifted
0 2016-04-30 A NL 1 nan
1 2016-04-30 A BE 2 nan
2 2016-04-30 B NL 3 nan
3 2016-04-30 B BE 4 nan
4 2016-04-30 C NL 5 nan
5 2016-04-30 C BE 6 nan
6 2016-04-30 C FR 7 nan
7 2016-04-30 C UK 8 nan
8 2016-05-31 A NL 9 1
9 2016-05-31 A BE 10 2
10 2016-05-31 A FR 11 nan
11 2016-05-31 B NL 12 3
12 2016-05-31 B BE 13 4
13 2016-05-31 B FR 14 nan
14 2016-05-31 C NL 15 5
15 2016-05-31 C BE 16 6
16 2016-05-31 C UK 17 8
17 2016-05-31 C SL 18 nan
18 2016-06-30 A NL 19 9
19 2016-06-30 B FR 20 14
20 2016-06-30 B UK 21 nan
21 2016-06-30 B SL 22 nan
22 2016-06-30 C NL 23 15
23 2016-06-30 C BE 24 16
IIUC, you want GroupBy.shift:
#df['Date']=pd.to_datetime(df['Date'])
#df=df.sort_values(['Date','Type']) #order if necessary
df['ValueShifted']=df.groupby(['Type','Country'])['Value'].shift()
print(df)
Output
Date Type Country Value ValueShifted
0 2016-04-30 A NL 1 NaN
1 2016-04-30 A BE 2 NaN
2 2016-04-30 B NL 3 NaN
3 2016-04-30 B BE 4 NaN
4 2016-04-30 C NL 5 NaN
5 2016-04-30 C BE 6 NaN
6 2016-04-30 C FR 7 NaN
7 2016-04-30 C UK 8 NaN
8 2016-05-31 A NL 9 1.0
9 2016-05-31 A BE 10 2.0
10 2016-05-31 A FR 11 NaN
11 2016-05-31 B NL 12 3.0
12 2016-05-31 B BE 13 4.0
13 2016-05-31 B FR 14 NaN
14 2016-05-31 C NL 15 5.0
15 2016-05-31 C BE 16 6.0
16 2016-05-31 C UK 17 8.0
17 2016-05-31 C SL 18 NaN
18 2016-06-30 A NL 19 9.0
19 2016-06-30 B FR 20 14.0
20 2016-06-30 B UK 21 NaN
21 2016-06-30 B SL 22 NaN
22 2016-06-30 C NL 23 15.0
23 2016-06-30 C BE 24 16.0