Extract column data based on date

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

[Adam Danz]

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.

[Joseff Saunders]

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

Answer 1

[Guillaume]

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!

[Joseff Saunders]

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.

[Adam Danz]

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]

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.