I need to fill in missing data points through linear interpolation. These missing data points are marked as "AWAY" or "NULL" in the dataset. Essentially, I need to take the first number before a missing data string (y1) and subtract it from the last number in a missing data string (y2). I then need to take the positions of y1 and y2 in the string and subtract these from each other (X2 - X1). For example, let's take the following number string:
1, 4, 6, AWAY, NULL, 7
I need to subtract the difference between 7 (Y2) and 6 (Y1). This is 1. The difference in their positions is 3. 6 is the third number in the string and 7 is the sixth number in the string. 1/3 is the slope.
Now I need to use this slope to fill in the missing data points. It would work like this:
1, 4, 6, 6 1/3, 6 2/3, 7
There will be some missing data at the beginning or end of the strings like this:
AWAY, AWAY, NULL, 1, 5, 7, 7, 8
1, 5, AWAY, AWAY, NULL
For these data strings, I want to fill in with the average of the first five or last five data points (depending on whether the missing data comes first or last).
For data strings where there are no data points at all, we can just leave them alone.
I have a sample data file for you to work with and can email it to you once you agree to do the job. All I need is a copy of the program to process the data files with. If you get it to work with the sample data file I give you, the job is done.
Please comment the program so I can have an understanding of what the program is doing.