MATLAB Answers

Convert Date to DATENUM format

14 views (last 30 days)
Hi everyone,
I have a .csv file with a list of dates in column 3 which I would like to convert to datenum format.
I'm new to matlab but from what I have learned so far, I think I should write code to do this for one line, and then apply a for loop so it applies to every line of data?
-my code suggests I have many .csv files but for now I only have one. I am writing code which in the future I will be able to apply to large data sets.
At the moment I have this:
%Convert date into datenum format-calculates time as the number of days
%from January 0, 0000.
dd = 'input_data'; %label input folder as 'dd'
nowd = cd; %marks current current directory as 'nowd'
cd(dd); %go to input folder (which is within CD)
d = dir('*.csv'); %mark all csv files within current folder
cd(nowd) %GO BACK TO date folder
%In the current .csv file, the date is in column 3.
filename=d.name;
disp(filename);
fid=fopen(fullfile(dd,filename));
tline=fgetl(fid);
i=l;
c = strsplit(tline,','); %this moves down the rows, separating by comma???
date = datenum(c{3},'dd-mm-yyyy');
I realise I haven't yet put in a for loop. I just wanted to try to get it to work for one line, but I'm not sure if I got my commands all mixed up, or if this is even the correct approach.

  3 Comments

Stephen Cobeldick
Stephen Cobeldick on 7 May 2019
Your code is a pretty good start. You might like to read this (it will help understand how to use a loop with multiple files):
Also keep in mind that some file importing functions will natively handle dates, e.g.:
Note that cd is best avoided as it slows code down (MATLAB will scan the new folder for MATLAB-type files) and makes debugging harder. It is easy to avoid in your code by simply using absolute/relative paths for fopen (which you already did correctly) and also for dir. So rather than this:
dd = 'input_data'; %label input folder as 'dd'
nowd = cd; %marks current current directory as 'nowd'
cd(dd); %go to input folder (which is within CD)
d = dir('*.csv'); %mark all csv files within current folder
cd(nowd)
you just need:
dd = 'input_data'; %label input folder as 'dd'
d = dir(fullfile(dd,'*.csv')); %mark all csv files within current folder
Also remember to fclose every file that you fopen !
Louise Wilson
Louise Wilson on 7 May 2019
Hi Stephen,
Thank you so much! That is a lot faster and I can see the difference.
Do you know when, when I use strsplit, I split the column titles, rather than the values I derived from fgetl?
tline=fgetl(fid); %fgetl reads one line from the file
c = strsplit(tline,','); %strsplit splits tline by comma delimiter
For the datenum function, I can't get it to work. What I have so far only identifies the header of the column, and not the full column of data. This seems like a pretty simple issue...
c = strsplit(tline,','); %strsplit splits tline by comma delimiter
date = datenum(c{3},'dd-mm-yyyy'); %datenum converts one or more date vectors into serial date numbers
Something to do with c{3} which is identifying the header only??
Stephen Cobeldick
Stephen Cobeldick on 8 May 2019
"Do you know when, when I use strsplit, I split the column titles, rather than the values I derived from fgetl?"
"Something to do with c{3} which is identifying the header only??"
The commands fgetl and fgets read one line each time they are called. They do not read the entire file content, just one line. You call fgetl just once in your code, so I would expect it to return the first line from that file (that would appear be a file header). If you want to import multiple lines from the file, then you will need to call fgetl multiple times (e.g. in a loop).
But doing so would be rather tiresome and likely inefficient. Because importing file data is such a common need, MATLAB already includes many function for different file types, that will efficiently parse the entire file at once (rather than line-by-line):

Sign in to comment.

Accepted Answer

Peter Perkins
Peter Perkins on 8 May 2019
"which I would like to convert to datenum format."
Louise, you really don't want to do that. Any of it. Unless you are using a pretty old version of MATLAB, like pior to R2014b, I strongly recommend that you use readtable, and your timestamps will come in as datetimes. Then convert to a timetable. In R2019a, you can use readtimetable.
In versions older than about R2017b, you'll probably get text timestamps, not datetime, but you can convert.

  6 Comments

Show 3 older comments
Peter Perkins
Peter Perkins on 9 May 2019
Louise, I can't really tell what you are doing.
Timetables, like tables, have variables. They are refereed to as "variables", not "columns" because a variable itself may have more than one column. That's not what you have, but just for future reference. The thing you are referring to as, "the column which has UTC time in it" is, however, NOT a variable that you can remove. It's the row times, the metadata for the timetable. It's what makes data2 a timetable.
If you want to tag the rows with local time, just set the time zone. I'm going to assume you started out with no time zone information, but I have no idea what you actually started out with. If you had a timetable whose row times were a datetime, I'd suggest that you do this:
data2.Time.TimeZone = 'UTC' % set an unzoned datetime representing UTC to actually be UTC.
data2.Time.TimeZone = 'America/New_York'; % shift to local time
Except that you screen shot suggests that you DON'T have row times that are datetimes, you have durations. If your LocalTime and LocalDate variables are correct, I suggest you combine those and just overwrite data2.Time
data2.UTCTime = data2.LocalDate + data2.LocalTime;
data2.Properties.DimensionNames{1} = 'Time';
Excep tthat for reasons I cannot fathom, while LocalTime appears to be a duration, indicating that you used readtable, LocalDate appears to be text, indicating that you did not. I cannot sort this out.
Louise Wilson
Louise Wilson on 12 May 2019
Hi Peter,
Thanks for your help. It's clear to me now about my language-learning slowly!
Instead I went back with a table rather than a timetable. I had UTC time and the local time here originally but I wanted to remove the UTC time variable. I didn't realise I could just set the timezone, that is really helpful, thank you.
My datetimes are specific points in tim when GPS points were taken, not durations. I had the dates and times in separate variables and I wanted to combine these into one variable, for example, "13/05/2019 10:03:00", so that I could then apply 'datenum' to these to get a serial date number.
Thank you for your help
Peter Perkins
Peter Perkins on 14 May 2019
Do you really have a good reason to go back to using datenums? You are very likely to be much happier using datetimes.

Sign in to comment.

More Answers (0)

Products


Release

R2018b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!