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
2 个评论
Star Strider
2014-11-2
It would help if you posted a larger and more representative selection of your data, preferably spanning about 3 years.
回答（1 个）
Geoff Hayes
2014-11-1
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 个评论
mt
2014-11-2
Geoff Hayes
2014-11-2
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
2014-11-2
Geoff Hayes
2014-11-2
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));
类别
在 帮助中心 和 File Exchange 中查找有关 Tables 的更多信息
2014-11-2
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
