CODEWITHSUNDEEP
pythonpandasdataframe
Evy555
Evy555

Reputation: 229

looping through a pandas dataframe

I am attempting to back-test an investment strategy. I am having trouble looping through the DataFrame to "re-create" how the strategy would have done starting 15 years ago. When I try to loop through the df['Average_Diff'] I keep getting the error "list indices must be integers or slices, not numpy.float64". I've struggled dealing with the nan that would occur in the beginning of the column due to how the values for ['Average_Diff'] were calculated, but once I fixed that I ran into this other problem. So how can I loop through the df['Average_Diff'] to create the "Buy or Sell" Signal and also loop through to indicate whether I'm in the market or out of the market based on the "Signals"? 

import pandas as pd 
import pandas.io.data 
from pandas import Series, DataFrame
import datetime
from pandas import ExcelWriter 
import os 
import matplotlib.pyplot as plt 
import math
import numpy as np 
from numpy import *
now = datetime.datetime.now()


start_of_interval = datetime.datetime(now.year - 15, now.month, now.day)
end_of_interval = datetime.datetime(now.year, now.month, now.day)       
df = pd.io.data.get_data_yahoo("Spy", start = start_of_interval, end = end_of_interval, interval = "d")['Adj Close']

df = DataFrame(df) 
df['Returns'] = df.pct_change()
df['Average_200'] = pd.rolling_mean(df['Adj Close'],200)
df['Average_50'] = pd.rolling_mean(df['Adj Close'],50) 
df['Date'] = df.index 

df['Average_Diff'] = df['Average_50'] - df['Average_200']  
df['Average_Diff'] = df['Average_Diff'].fillna(int(2)) 
print(df) 
for i in df['Average_Diff']:
    if df['Average_Diff'][i] == int(2):
    df["Signal"] = "Hold"
    df["Market"] = 1
if df['Average_Diff'][i-1] > 0 and ['Average_Diff'][i] < 0:
    df["Signal"] = "Buy"
    df['Market'] = 1
elif df['Average_Diff'][i-1] < 0 and ['Average_Diff'][i] > 0:
    df["Signal"] = "Sell"
    df["Signal"] = 0 
else:
    df["Signal"] = "Hold" 

for i in df["Market"]:
    if df["Signal"][i] == "Sell":
        df["Market2"] = 0
    elif df['Signal'][i] == "Hold" and df['Market'][i-1] == 0:
        df['Market2'] = 0
    elif df['Signal'][i] == "Hold" and df['Market'][i-1] == 1:  
        df['Market2'] = 1 
    elif df['Signal'][i] == "Buy":
        df['Market2'] = 1 
    else:
        df["Market2"] = 1

Upvotes: 0

Views: 6796

Answers (1)

Kartik
Kartik

Reputation: 8683

Here are a couple of alternatives you can try:

l = len(df)
for i in range(len):
    if df.loc[i, 'Average_Diff'] == int(2):
        df.loc[i, 'Signal'] = 'Hold'
        df.loc[i, 'Market'] = 1

Or (prefer this, over the one above)

for i in df.index.values:
    if df.loc[i, 'Average_Diff'] == int(2):
        df.loc[i, 'Signal'] = 'Hold'
        df.loc[i, 'Market'] = 1

EDIT

l = df.index.values
for i in range(1, len(l)):
    if df.loc[l[i], 'Average_Diff'] == int(2):
        df.loc[l[i], 'Signal'] = 'Hold'
        df.loc[l[i], 'Market'] = 1
    # Even i-1 will work in the same way: l[i-1]

Contrary to the comments:

You should never modify something you are iterating over. This is not guaranteed to work in all cases. Depending on the data types, the iterator returns a copy and not a view, and writing to it will have no effect. 1

Upvotes: 1

Related Questions