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

Questions

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

November 30, 2022

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!

>Solution :

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