I have created a pandas dataframe (called df
) as follows:
import pandas as pd
import numpy as np
ds = {'Trend' : [1,1,1,1,1,
2,2,2,2,2,
3,3,3,3,3,
4,4,4,4,4,
5,5,5,5,5],
'Cycle' : [
14,88,50,119,107,
32,111,41,88,38,
37,72,79,98,69,
62,38,86,26,30,
52,91,122,90,127
]
}
df = pd.DataFrame(data=ds)
The dataframe looks like this:
print(df)
Trend Cycle
0 1 14
1 1 88
2 1 50
3 1 119
4 1 107
5 2 32
6 2 111
7 2 41
8 2 88
9 2 38
10 3 37
11 3 72
12 3 79
13 3 98
14 3 69
15 4 62
16 4 38
17 4 86
18 4 26
19 4 30
20 5 52
21 5 91
22 5 122
23 5 90
24 5 127
I need to create a new column (called ArrayToBeMade
) which contains:
Trend
(which we get by iterating through each record)The resulting dataframe would look like this:
Can someone help me, please?
Use GroupBy.last
with mapping columns with Series.shift
, last convert 3 columns DataFrame to numpy array and then to lists:
s = df.groupby('Trend')['Cycle'].last()
df['Arr'] = (df.assign(t1 = df.Trend.map(s.shift(2)),
t2 = df.Trend.map(s.shift()))[['t1','t2','Cycle']]
.to_numpy().tolist())
print (df)
Trend Cycle Arr
0 1 14 [nan, nan, 14.0]
1 1 88 [nan, nan, 88.0]
2 1 50 [nan, nan, 50.0]
3 1 119 [nan, nan, 119.0]
4 1 107 [nan, nan, 107.0]
5 2 32 [nan, 107.0, 32.0]
6 2 111 [nan, 107.0, 111.0]
7 2 41 [nan, 107.0, 41.0]
8 2 88 [nan, 107.0, 88.0]
9 2 38 [nan, 107.0, 38.0]
10 3 37 [107.0, 38.0, 37.0]
11 3 72 [107.0, 38.0, 72.0]
12 3 79 [107.0, 38.0, 79.0]
13 3 98 [107.0, 38.0, 98.0]
14 3 69 [107.0, 38.0, 69.0]
15 4 62 [38.0, 69.0, 62.0]
16 4 38 [38.0, 69.0, 38.0]
17 4 86 [38.0, 69.0, 86.0]
18 4 26 [38.0, 69.0, 26.0]
19 4 30 [38.0, 69.0, 30.0]
20 5 52 [69.0, 30.0, 52.0]
21 5 91 [69.0, 30.0, 91.0]
22 5 122 [69.0, 30.0, 122.0]
23 5 90 [69.0, 30.0, 90.0]
24 5 127 [69.0, 30.0, 127.0]