Extract column data based on date

2 views (last 30 days)
Joseff Saunders
Joseff Saunders on 23 Apr 2019
Commented: Guillaume on 23 Apr 2019
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
Adam Danz
Adam Danz on 23 Apr 2019
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
Joseff Saunders on 23 Apr 2019
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.

Answers (1)

Guillaume
Guillaume on 23 Apr 2019
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
Adam Danz
Adam Danz on 23 Apr 2019
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
Guillaume on 23 Apr 2019
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.

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!