Monthly average text file question
4 views (last 30 days)
Show older comments
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
2 Comments
Star Strider
on 2 Nov 2014
It would help if you posted a larger and more representative selection of your data, preferably spanning about 3 years.
Answers (1)
Geoff Hayes
on 1 Nov 2014
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.
4 Comments
Geoff Hayes
on 2 Nov 2014
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));
See Also
Categories
Find more on Dates and Time in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!