Python Forum

Hi,

I am very new to python and decided to learn so I could automate my very time-consuming excel data analysis. I have managed to write a simple script to take care of most of my needs, but there is still one aspect that I can't figure out.

In my excel file, I have a column (Target Reward) that contains values (either 1, 0 or -1).

I need my script to determine the value of cell in the Target Reward column and create a column and add a 1 each time a 1 is followed by a 1 or a -1 in the Target Reward column and a 0 if not.

Here is an example dataframe

Trial      Target Reward    New Column
1_________ 1              
2_________ 0 _________ 0
3_________ 1 _________ 0
4_________ 1_________  1
5_________ 0 _________ 0
6_________ 1 _________ 0
7_________ -1 _________1

Does anyone have any idea how I can solve this problem?

Thanks!

my code here

what does your code look like now?
what packages are you using?

df.loc[(df['TargetReward'] == 1), "TargetRew"] = 1
df.loc[(df['TargetReward'] != 1), "TargetRew"] = 0

for i in df['TargetReward']:
if (df['TargetReward'].irow(i)) == abs((df['TargetReward'].irow(i+1))):
df['TargetWinStay'] = 1

This adds a new column, TargetRew, and adds a 1 to the column if TargetReward contains a 1.

The second part is meant to scan TargetRew and add a 1 if a 1 is followed by a 1 or -1 in TargetRew, but it just adds 1's in every cell.

Iterating over rows in dataframe / numpy array is very often a bad idea, using pandas/numpy vectorized methods/functions is preferred (morever your i variable in your for loop has values only 0, 1, -1, so you are ignoring anything except first two lines and last line in dataframe).

Pandas builtin .shift() can be used to "shift" ("lag") entire column and after that you can use some logic on your shifted column and original one. Adding new column with value 1 when target is -1 or 1 and is preceded by 1, with value 0 otherwise can be done with

df["new_col"] = np.where((df.target.abs() == 1)  & (df.target.shift(1) == 1), 1, 0) 

With such command you will get 0 for first line ( "shifted" target starts with NA, that leads to false condition).