MATLAB Answers

0

Extract column data based on date

Asked by Joseff Saunders on 23 Apr 2019 at 14:34
Latest activity Commented on by Guillaume
on 23 Apr 2019 at 17:46
Hi there, I have a csv file contatining wave height (Hs) and period (Tp) with date/time which has a recording for every 30 minute interval over the course of a year:
Date/Time (GMT) Hs Tp
01-Jan-2018 00:00:00 2.15 11.1
01-Jan-2018 00:30:00 2.2 14.3
01-Jan-2018 01:00:00 2.19 10.5
01-Jan-2018 01:30:00 2.29 14.3
01-Jan-2018 02:00:00 2.39 14.3
I've been extracting monthly averages by seperating each column and simply extracting the data from the specified column based on monthly derived row values so for January it would be:
Jan = nanmean(Hs(1:1488));
What is a quicker way to extract monthly averages of Hs and Tp directly from the csv file?
Thanks in advance

  2 Comments

You could use splitapply() to calculate the monthly averages for all columns. If you upload a mat file containing the data after reading it in from the csv file, I can help out.
Hi Adam, i've attached the table, it has several column headings but im only interested in Hs and Tp for now. Thanks for your help

Sign in to comment.

1 Answer

Answer by Guillaume
on 23 Apr 2019 at 15:16

Most likely (for lack of a sample file to test code with):
wavedata = readtimetable('C:\somewhere\somefile.csv'); %requires R2019a, in previous versions:
%wavedata = table2timetable(readtable('C:\somewhere\somefile.csv'));
monthlywavedata = retime(wavedata, 'monthly', 'mean');
All done!

  3 Comments

Hi Guillaume, sorry i've attached a file in the previous comment, as the date is set in 'dd-mmm-yyyy 00:00:00' format I can't extract by monthly values using the code you provided.
The code below implements Guillaume's solution to your data:
A.Date_Time_GMT_ = datetime(A.Date_Time_GMT_); % Replace date string with datetime in column 1
Att = table2timetable(A); % convert to time table
Amean = retime(Att, 'monthly', 'mean')
Guillaume
on 23 Apr 2019 at 17:46
It is possible to read the date/time in the original csv file directly as a datetime. I'm surprised that readtable didn't already do it correctly but when it doesn't work, it's straightforward to override with detectImportOptions.
Converting afterwards as shown by Adam also works of course.

Sign in to comment.