Create pandas dataframe column containing list of previous values

Question

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:

• a list of three elements
• each element is the last value of the column Trend (which we get by iterating through each record)

The resulting dataframe would look like this:

Can someone help me, please?

Answer 1

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]

Answer 2

You could use incremental groupby.shift operations:

# number of columns in the output array
N = 3

# initial value
out = [df['Cycle']]
# only keep last value per group
m = df['Trend'].duplicated(keep='last')

# ffill and add as new column
for _ in range(N-1):
    out.append(out[-1].mask(m).shift().groupby(df['Trend']).ffill())

df['ArrayToBeMade'] = np.vstack(out[::-1]).T.tolist()

Output:

    Trend  Cycle        ArrayToBeMade
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]