Finding the moving average - what am I doing wrong?

[StarryEyedSoy]

I'm trying to find the moving average of 20 numbers at a time, being read from a list. This moving average then needs to be inserted into the list. Here is my code:

inplis = file(r'table.csv','r').readlines()
outp = file(r'avg_volume.csv','w')

import math
emptyList = []
totalVolume = 0
counter = 0

for i in inplis:
    rec = i.strip().split(",")
    if rec[0] == 'Date':
        rec.insert(1,emptyList)
        continue
    else:
        counter = counter + len(i)
        
        while counter == 20:
            volume = float(rec[5])
            totalVolume = totalVolume + volume
            break
        avgVolume = totalVolume/20
        rec.insert(1,emptyList)           
        rec[1]= avgVolume
        
        if counter!=20:
            continue
        
     
                     
       
    import string           
    newStr = string.join(map(str,rec),",")+'\n'
    outp.write(newStr)
   

outp.close()

There is obviously something wrong with the way the variable counter is being incremented but I don't know how to fix it.. I am trying to get it to count each i, and when it reaches 20, to find totalVolume and avgVolume. Volume is the 5th element of each i, where I am splitting the input file into one big list named inplis, which is then being split into invidual records named rec.

I'd appreciate any guidance, thank you!!

[jlm699]

Hmm, it's hard to say exactly what's wrong without having an example of the contents of table.csv; however upon initial inspection I see a glaring problem. Since you're not incrementing counter by 1 (rather a variable length), I would imagine you're missing the opportunity to enter the while counter == 20: loop.

Perhaps you could provide with an example of what's in that file so we can take a more complete look at the problem here.

[StarryEyedSoy]

Hi (-: Here is what my data looks like:

Date Open High Low Close Volume Adj Close
7/18/2008 19.57 20.1 17.76 19.11 50773900 19.11
7/17/2008 18.08 20.15 16.81 18.9 72725600 18.9
7/16/2008 14.27 16.85 13.47 16.65 83402200 16.65
7/15/2008 12.99 14.58 12.02 13.22 132443700 13.22
7/14/2008 16.25 16.3 12.4 12.4 107805000 12.4
7/11/2008 15.09 16.06 13.29 14.43 174238800 14.43

So basically, what I have done is with each i, I read a line of inplis, and assign it to rec. What I'm trying to do with counter is increment it by 1 each time it reads an individual list.. while counter is less than 20, I add volume, until I get total volume when counter is equal to 20. I then divide total volume by 20 to get average volume. Once average volume for the first 20 i's has been found, I would need to start from i=2 and use the next 20 entries of Volume to calculate average volume.

I figured I could do it using a moving window as well.. where I hold constant a window of 20 elements, and with each iteration, I subtract the 1st element in the window and add the 21st. But that seemed too complex for me to follow through, and hence I tried this technique out.

Also, as I typed this, I realised that I am calculating total volume in the wrong part of the code. Here is the corrected syntax:

inplis = file(r'table.csv','r').readlines()
outp = file(r'avg_volume.csv','w')

import math
emptyList = []
totalVolume = 0
counter = 0

for i in inplis:
    rec = i.strip().split(",")
    if rec[0] == 'Date':
        rec.insert(1,emptyList)
        continue
    else:
        for i in inplis:
            counter = counter + i
            volume = float(rec[5])
            totalVolume = totalVolume + volume
        
        while counter == 20:
            avgVolume = totalVolume/20
            rec.insert(1,emptyList)           
            rec[1]= avgVolume
            break
        
        if counter!=20:
            continue
        
    import string           
    newStr = string.join(map(str,rec),",")+'\n'
    outp.write(newStr)
   

outp.close()

[jlm699]

There are a number of things that I don't understand what you are trying to do in this code. First and foremost:

if rec[0] == 'Date':
        rec.insert(1,emptyList)
        continue

Why are you inserting an empty list into your data and then forcing execution to the next iteration (effectively nullifying inserting the empty list) ??

Second, why are you iterating over the same object within the first for loop? That also makes no sense to do.

Third, you don't need to import string to use join. Simply use ','.join( map( str, rec ) ) , which brings me to my next point.

What are you trying to do there? map(str,rec)? You have no object named str, and if you did it would be a bad idea since str() is a reserved method for converting objects into strings.

[bvdet]

My thought was to do something like the following:

data = '''Date Open High Low Close Volume Adj
7/18/2008 19.57 20.1 17.76 19.11 50773900 19.11
7/17/2008 18.08 20.15 16.81 18.9 72725600 18.9
7/16/2008 14.27 16.85 13.47 16.65 83402200 16.65
7/15/2008 12.99 14.58 12.02 13.22 132443700 13.22
7/14/2008 16.25 16.3 12.4 12.4 107805000 12.4
7/11/2008 15.09 16.06 13.29 14.43 174238800 14.43
7/18/2008 19.57 20.1 17.76 19.11 50773900 19.11
7/17/2008 18.08 20.15 16.81 18.9 72725600 18.9
7/16/2008 14.27 16.85 13.47 16.65 83402200 16.65
7/15/2008 12.99 14.58 12.02 13.22 132443700 13.22
7/14/2008 16.25 16.3 12.4 12.4 107805000 12.4
7/11/2008 15.09 16.06 13.29 14.43 174238800 14.43
7/18/2008 19.57 20.1 17.76 19.11 50773900 19.11
7/17/2008 18.08 20.15 16.81 18.9 72725600 18.9
7/16/2008 14.27 16.85 13.47 16.65 83402200 16.65
7/15/2008 12.99 14.58 12.02 13.22 132443700 13.22
7/14/2008 16.25 16.3 12.4 12.4 107805000 12.4
7/11/2008 15.09 16.06 13.29 14.43 174238800 14.43
7/18/2008 19.57 20.1 17.76 19.11 50773900 19.11
7/17/2008 18.08 20.15 16.81 18.9 72725600 18.9
7/16/2008 14.27 16.85 13.47 16.65 83402200 16.65
7/15/2008 12.99 14.58 12.02 13.22 132443700 13.22
7/14/2008 16.25 16.3 12.4 12.4 107805000 12.4
7/11/2008 15.09 16.06 13.29 14.43 174238800 14.43
7/18/2008 19.57 20.1 17.76 19.11 50773900 19.11
7/17/2008 18.08 20.15 16.81 18.9 72725600 18.9
7/16/2008 14.27 16.85 13.47 16.65 83402200 16.65
7/15/2008 12.99 14.58 12.02 13.22 132443700 13.22
7/14/2008 16.25 16.3 12.4 12.4 107805000 12.4
7/11/2008 15.09 16.06 13.29 14.43 174238800 14.43'''

def moving_average(dataList, elem=5, sample=5):
    # test for header as first element
    try:
        int(data[0].split()[elem])
    except:
        dataList = dataList[1:]
    return [(sum([int(dataList[i+j].split()[elem]) \
                  for j in range(sample)])/sample) \
                      for i in range(len(dataList)-sample+1)]

print moving_average(data.split('\n'), sample=20)

Output:

>>> [99383355, 101014770, 104000675, 105220815, 107310570, 104459015, 99383355, 101014770, 104000675, 105220815, 107310570]

You would need to adjust the code to suit the data.

[StarryEyedSoy]

There are a number of things that I don't understand what you are trying to do in this code. First and foremost:

if rec[0] == 'Date':
        rec.insert(1,emptyList)
        continue

Why are you inserting an empty list into your data and then forcing execution to the next iteration (effectively nullifying inserting the empty list) ??

Second, why are you iterating over the same object within the first for loop? That also makes no sense to do.

Third, you don't need to import string to use join. Simply use ','.join( map( str, rec ) ) , which brings me to my next point.

What are you trying to do there? map(str,rec)? You have no object named str, and if you did it would be a bad idea since str() is a reserved method for converting objects into strings.

Hi,

1. I'm inserting an empty list because I need to insert the values into that column, as opposed to appending it. I am checking if the element encountered is Date, in which case I insert an emptylist. In the next iteration, I say rec[1] = avgvolume, effectively inserting the values into column 1.

2. I did that because my counter was not working.. I figured if I looped over inplis again then maybe my counter would take the values of i? Apparently not, so that was wrong.

3. I used map so that I could convert the entire list into a string, so as to write it into a new file, since it needs to be in string form in order to write into outp.

[woooee]

Use a for loop to read the first 20 recs into a list, we'll call it move_list, and then remove the first item each time you read a new rec. With only 20 values you don't have to be too concerned about memory use or processing time, something like this (untested)

move_list = []
data=""
for j in range(0, 20):
   data = fp.readline()
   split_fields = data.split()
   move_list.append(split_fields[appropriate_one])
while data:
   ## sum and average the values of the list before changing
   sum_ave = 0
   for value in move_list:
      sum_ave += float(value)
   print "the moving average is", sum_ave/float(20), move_list

   del move_list[0]
   data=fp.readline()
   split_fields = data.split()
   move_list.append(split_fields[appropriate_one])

[jlm699]

Hi,

1. I'm inserting an empty list because I need to insert the values into that column, as opposed to appending it. I am checking if the element encountered is Date, in which case I insert an emptylist. In the next iteration, I say rec[1] = avgvolume, effectively inserting the values into column 1.

2. I did that because my counter was not working.. I figured if I looped over inplis again then maybe my counter would take the values of i? Apparently not, so that was wrong.

3. I used map so that I could convert the entire list into a string, so as to write it into a new file, since it needs to be in string form in order to write into outp.

1. You don't need to insert an empty list and then replace it, you could simply do it one step by rec.insert(1, avgVolume) ...

2. In your loop 'i' is not a number. It is each line (which is why you did i.strip().split() remember? ). You should simply be using counter += 1 , which is equivalent to counter = counter + 1 . That way you're incrementing by one every time.

3. I completely took the wrong meaning of that last part... I was thinking in terms of C++ maps and forgot about Python's map function! Sorry ;)

Instead of while counter == 20 you should simply check if counter == 20 .
Additionally, you should remove the import string line and replace this line: newStr = string.join(map(str,rec),",")+'\n' with this: newStr = ','.join( map(str, rec ) ) + '\n' HTH