calculate average of column 2
Hi I have fakebook.csv as following:
F1(current date) F2(popularity) F3(name of book) F4(release date of book)
2006-06-21,6860,"Harry Potter",2006-12-31
2006-06-22,,"Harry Potter",2006-12-31
2006-06-23,7120,"Harry Potter",2006-12-31
2006-06-24,,"Harry Potter",2006-12-31
2006-06-25,7498,"Harry Potter",2006-12-31
2006-06-26,7500,"Harry Potter",2006-12-31
2006-06-27,7750,"Harry Potter",2006-12-31
2006-06-28,7860,"Harry Potter",2006-12-31
2006-06-29,,"Harry Potter",2006-12-31
2006-06-30,7860,"Harry Potter",2006-12-31
2006-07-01,1760,"Bible",2007-03-03
2006-07-02,2755,"Bible",2007-03-03
2006-07-03,3760,"Bible",2007-03-03
2006-07-04,,"Bible",2007-03-03
2006-07-05,5788,"Bible",2007-03-03
2006-07-06,6799,"Bible",2007-03-03
...............................
I want to add a new column in each line that is Field 5 (average popularity[over a sample of three days] 6 months before release date)
So for the harry potter book, the release date is 2006-12-31, I need to find the average value of field 2 over the following date points: 2006-06-30,2006-06-28 and 2006-06-27. (This problem is complicated by the fact that the popularity of 2006-06-29 is unavailable and left as blank in the csv, so 2006-06-29 is skipped in the calculation of average).
My supervisor gave me a short timespan to solve this.I've already got the datecalc script found in FAQ but haven't found its use in my context yet. Can someone please help me? Thanks a million!