I've a pandas dataset with open, high, low, close, key1 and key2 column. Now I want to group the dataset by key1 and key2 and calculate pivot with the formula - (high + low + close) / 3. Upto this I'm able to do. But the requirement is to shift the calculated data to next group which I'm unable to code.
I'm able to group the dataset by key1 and key2 column and able to calculate pivot data by below code but unable to shift values on next group.
import pandas as pd
data = pd.DataFrame([[110, 115, 105, 111, 1, 2],[11, 16, 6, 12, 1, 2],[12, 17, 7, 13, 1, 3],[22, 25, 17, 20, 1, 3],[12, 16, 6, 11, 2, 4],[32, 36, 26, 28, 2, 4],[9, 13, 4, 13, 2, 5],[49, 53, 40, 45, 2, 5],[13, 18, 9, 12, 3, 6],[14, 16, 10, 13, 3, 6]], columns=["open","high","low","close","key1", "key2"])
s = (data.high.groupby([data.key1, data.key2]).max() + data.low.groupby([data.key1, data.key2]).min() + data.close.groupby([data.key1, data.key2]).last()) / 3
#data['pivot'] = data['key1', 'key2'].map(s.shift())
print(data)
When I use below code,
import pandas as pd
data = pd.DataFrame([[110, 115, 105, 111, 1, 2],[11, 16, 6, 12, 1, 2],[12, 17, 7, 13, 1, 3],[22, 25, 17, 20, 1, 3],[12, 16, 6, 11, 2, 4],[32, 36, 26, 28, 2, 4],[9, 13, 4, 13, 2, 5],[49, 53, 40, 45, 2, 5],[13, 18, 9, 12, 3, 6],[14, 16, 10, 13, 3, 6]], columns=["open","high","low","close","key1", "key2"])
data['pivot'] = (data.high.groupby([data.key1, data.key2]).transform('max') + data.low.groupby([data.key1, data.key2]).transform('min') + data.close.groupby([data.key1, data.key2]).transform('last')) / 3
print(data)
I get below output.
open high low close key1 key2 pivot
0 110 115 105 111 1 2 44.333333
1 11 16 6 12 1 2 44.333333
2 12 17 7 13 1 3 17.333333
3 22 25 17 20 1 3 17.333333
4 12 16 6 11 2 4 23.333333
5 32 36 26 28 2 4 23.333333
6 9 13 4 13 2 5 34.000000
7 49 53 40 45 2 5 34.000000
8 13 18 9 12 3 6 13.333333
9 14 16 10 13 3 6 13.333333
But Expected Output:
open high low close key1 key2 pivot
0 110 115 105 111 1 2 NaN
1 11 16 6 12 1 2 NaN
2 12 17 7 13 1 3 44.333333
3 22 25 17 20 1 3 44.333333
4 12 16 6 11 2 4 17.333333
5 32 36 26 28 2 4 17.333333
6 9 13 4 13 2 5 23.333333
7 49 53 40 45 2 5 23.333333
8 13 18 9 12 3 6 34.000000
9 14 16 10 13 3 6 34.000000
First use aggregation function with dictionary and GroupBy.agg and then for new column DataFrame.join with shift:
s = data.groupby(['key1','key2']).agg({'low':'min','high':'max','close':'last'}).sum(axis=1)/3
data = data.join(s.rename('pivot').shift(), on=['key1','key2'])
print (data)
open high low close key1 key2 pivot
0 110 115 105 111 1 2 NaN
1 11 16 6 12 1 2 NaN
2 12 17 7 13 1 3 44.333333
3 22 25 17 20 1 3 44.333333
4 12 16 6 11 2 4 17.333333
5 32 36 26 28 2 4 17.333333
6 9 13 4 13 2 5 23.333333
7 49 53 40 45 2 5 23.333333
8 13 18 9 12 3 6 34.000000
9 14 16 10 13 3 6 34.000000