How can I add a new column to a dataframe with a lookup to the same column? (1 rows above)-CodePudding

I've created this dataframe -

Range = np.arange(1,101,1)

A={
0:-1,
1:0,
2:4
}

Table = pd.DataFrame({"Row": Range})
Table["Intervals"]=np.where(Table["Row"]==1,0,(Table["Row"]%3).map(A))

Table

    Row Intervals
0   1   0
1   2   4
2   3   -1
3   4   0
4   5   4
... ... ...
95  96  -1
96  97  0
97  98  4
98  99  -1
99  100 0

I'd like to add a new column that the first cell will contain the number -25 and the second number will be -25 4, the third number will be -25 4 (-1)...and so on.

I've tried to use shift but no luck -

Table["X"]=np.where(Table["Row"]==1,-25,Table["X"].shift(1))

Will appreciate any help!

CodePudding user response：

We'll start by adding the value -25 to the 0th row in a new column, NewColumn

Table.loc[0, "NewColumn"] = -25

Then we fill the nulls with the Intervals column and convert back to int (they were floats)

Table["NewColumn"] = Table["NewColumn"].fillna(Table["Intervals"]).astype(int)

And last cumulative sum the NewColumn

Table["NewColumn"] = Table["NewColumn"].cumsum()

All together

Range = np.arange(1,101,1)

A={
0:-1,
1:0,
2:4
}

Table = pd.DataFrame({"Row": Range})
Table["Intervals"]=np.where(Table["Row"]==1,0,(Table["Row"]%3).map(A))

Table.loc[0, "NewColumn"] = -25

Table["NewColumn"] = Table["NewColumn"].fillna(Table["Intervals"]).astype(int)

Table["NewColumn"] = Table["NewColumn"].cumsum()

print(Table)

    Row  Intervals  NewColumn
0     1          0        -25
1     2          4        -21
2     3         -1        -22
3     4          0        -22
4     5          4        -18
..  ...        ...        ...
95   96         -1         71
96   97          0         71
97   98          4         75
98   99         -1         74
99  100          0         74

CodePudding user response：

You're looking for cumulative sum.

>>> Table['n'] = np.concatenate([[-25], Table.Intervals[1:]])
>>> Table['cum'] = Table.n.cumsum()
>>> Table
    Row  Intervals   n  cum
0     1          0 -25  -25
1     2          4   4  -21
2     3         -1  -1  -22
3     4          0   0  -22
4     5          4   4  -18
..  ...        ...  ..  ...
95   96         -1  -1   71
96   97          0   0   71
97   98          4   4   75
98   99         -1  -1   74
99  100          0   0   74

[100 rows x 4 columns]