Monthly average text file question

Hi all! I have a csv file that has bunch of line with date (span of 4 years) and I am trying to find the monthly average for the second column and export it as a csv file again. Anybody could help with writing the loop statement? Thanks! Below is an example of the text.

1998-08-29,892.98,986.75,14688,14688,82.5,17812,0
1998-08-30,892.97,986.47,14678,14678,82.4,17812,0
1998-08-31,892.96,986.19,14668,14668,82.3,17812,0
1998-09-01,892.95,985.91,14658,14658,82.3,17812,0
1998-09-02,892.95,985.91,14658,14658,82.3,17812,0
1998-09-03,892.95,985.91,14658,14658,82.3,17812,0
1998-09-04,892.95,985.91,14658,14658,82.3,17812,0
1998-09-05,892.93,985.34,14638,14638,82.2,17812,0
1998-09-06,892.92,985.06,14629,14629,82.1,17812,0
1998-09-07,892.91,984.78,14619,14619,82.1,17812,0
1998-09-08,892.92,985.06,14629,14629,82.1,17812,0
1998-09-09,892.92,985.06,14629,14629,82.1,17812,0
1998-09-10,892.91,984.78,14619,14619,82.1,17812,0
1998-09-11,892.90,984.50,14609,14609,82.0,17812,0
1998-09-12,892.90,984.50,14609,14609,82.0,17812,0
1998-09-13,892.90,984.50,14609,14609,82.0,17812,0
1998-09-14,892.90,984.50,14609,14609,82.0,17812,0
1998-09-15,892.89,984.22,14599,14599,82.0,17812,0
1998-09-16,892.89,984.22,14599,14599,82.0,17812,0
1998-09-17,892.88,983.94,14589,14589,81.9,17812,0
1998-09-18,892.87,983.66,14579,14579,81.8,17812,0
1998-09-19,892.87,983.66,14579,14579,81.8,17812,0
1998-09-20,892.87,983.66,14579,14579,81.8,17812,0
1998-09-21,892.87,983.66,14579,14579,81.8,17812,0
1998-09-22,892.87,983.66,14579,14579,81.8,17812,0
1998-09-23,892.86,983.38,14569,14569,81.8,17812,0
1998-09-24,892.87,983.66,14579,14579,81.8,17812,0
1998-09-25,892.88,983.94,14589,14589,81.9,17812,0
1998-09-26,892.88,983.94,14589,14589,81.9,17812,0
1998-09-27,892.87,983.66,14579,14579,81.8,17812,0
1998-09-28,892.87,983.66,14579,14579,81.8,17812,0
1998-09-29,892.87,983.66,14579,14579,81.8,17812,0
1998-09-30,892.86,983.38,14569,14569,81.8,17812,0
1998-10-01,892.86,983.38,14569,14569,81.8,17812,0
1998-10-02,892.86,983.38,14569,14569,81.8,17812,0
1998-10-03,892.85,983.11,14560,14560,81.7,17812,0
1998-10-04,892.85,983.11,14560,14560,81.7,17812,0
1998-10-05,892.85,983.11,14560,14560,81.7,17812,0
1998-10-06,892.85,983.11,14560,14560,81.7,17812,0

[Star Strider]

It would help if you posted a larger and more representative selection of your data, preferably spanning about 3 years.

[mt]

Edit is done. 3 years.. would be too long and redundant. Also I had a typo - 4 years not 40

Answer 1

[Geoff Hayes]

Consider using readtable to read data from your file (rather than using a loop). If your file name is test.txt, then try

 A = readtable('test.txt','ReadVariableNames',false)

to read the data into a table. You can then access the second column as

 A.(2)

and perform any calculation (average) that you need to.

[mt]

Okay, what about the average loop?

[Geoff Hayes]

Given your example, each record is assumed to correspond to one month for some year. The second column represents some unknown data for that month. You stated that you are trying to find the monthly average for the second column. How would you calculate that average if the second column can be retrieved/accessed with A.(2)?

[mt]

I was talking the average of the second column in respect to time. Every row is a daily value.

[Geoff Hayes]

Oh, my mistake. Use the first column too. If we can assume that all dates are in ascending order and of the format YYYY-MM-DD, then just compare the first seven characters of the date string in row k with the first seven characters of the date string in row k-1. If different, then you know that you have started a new month and so must average the previous days. Something like

 % get the dates
 dateStrings = A.(1);

 % get the second column
 secondCol   = A.(2);

 % set the first day of (new) month index to first element in our
 % date string column
 firstNewDateIdx = 1;

 for k=2:size(dateStrings,1)

     if ~strcmp(dateStrings{k}(1:7),dateStrings{k-1}(1:7))

        % we've reached a new month, so calculate the average of the
        % previous month
        monthAverage = mean(secondCol(firstNewDateIdx:k-1));

        % do something with this month average

        % save the index of the first day of the new month
        firstNewDateIdx = k;

     end
 end

 % do the average of the final month
 monthAverage = mean(secondCol(firstNewDateIdx:end));