Python Pandas Dataframe calculating new row value based on previous row value within same column

user_13092564 0
Viewed 751

It's a bit hard to explain, so I'll start off with what I'm trying to achieve using excel.

Excel example

Basically, the value of the "Active" column is based on values of the same row different column values (columns 'Act Count' and 'De Count'), as well as the value in the previous row of the "Active" column.

From the excel formula, if 'Act Count' < 4 and 'De Count' < 4, 'Active' = the previous row's 'Active' value.

I want to transition this to Python pandas dataframe.

Here is the sample data:

import pandas as pd

df = pd.DataFrame({'Act Count':[1,2,3,4,0,0,0,0,0,0,0,0,0,0],
              'De Count':[0,0,0,0,0,0,0,0,1,2,3,4,5,6]})

You can assume the first row value of 'Active' = 0.

I know of .shift() function, however I feel like I can't use it because I can't shift a column that doesn't exist yet.

pandas dataframe python
2 Answers

This is not an elegant solution, but it will do the job.

import pandas as pd

act_count = [1,2,3,4,0,0,0,0,0,0,0,0,0,0]
de_count = [0,0,0,0,0,0,0,0,1,2,3,4,5,6]

active = [0]
for i in range(1:len(act_count)):
    if act_count[i] >= 4:
       active.append(100)
    elif de_count[i] >= 4 :
       active.append(0)
    else:
       active.append(active[i-1])

df = pd.DataFrame({'Act Count': act_count, 'De Count' : de_count, 
              'Active' : active})
user_140907010

You can use this method:

##Adding an empty column named Active to the existing dataframe
df['Active'] = np.nan

##putting the first value as 0
df['Active'].loc[0] = 0 

for index in range(1,df.shape[0]):
    if df['Act Count'].iloc[index]>=4:
        df['Active'].iloc[index]=100
    elif df['De Count'].iloc[index]>=4:
        df['Active'].iloc[index]=0
    else:
        df['Active'].iloc[index]=df['Active'].iloc[index-1]
print(df)

Output:

   Act Count    De Count    Active
0   1           0           0.0
1   2           0           0.0
2   3           0           0.0
3   4           0           100.0
4   0           0           100.0
5   0           0           100.0
6   0           0           100.0
7   0           0           100.0
8   0           1           100.0
9   0           2           100.0
10  0           3           100.0
11  0           4           0.0
12  0           5           0.0
13  0           6           0.0
user_82573590
Related