Convert Date to DATENUM format

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.

[Stephen23]

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):

https://www.mathworks.com/help/matlab/import_export/process-a-sequence-of-files.html

Also keep in mind that some file importing functions will natively handle dates, e.g.:

https://www.mathworks.com/help/matlab/ref/readtable.html

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]

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??

[Stephen23]

"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):

https://www.mathworks.com/help/matlab/data-import-and-export.html

Answer 1

[Peter Perkins]

"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.

[Louise Wilson]

Hi Peter,

Thank you for your advice. I have since re-done my whole code.

I am attempting to write a script which can be used to modify very large data sets, and run through many .csv files at a time. Just now I only have one such file.

From these .csv files, I wanted to remove unnecessary columns, and add in a column of data (not done this yet). The code looks to be much simpler now but I am having an issue...

Since I converted the table to a timetable, the column which has UTC time in it has become fixed, and I don't seem to be able to remove it. I want to keep local date and time but remove UTC date and time.

I'm not sure why it has selected this column to keep! It means I can't select it in to remove it, as I have done with e.g. with 'UTCDATE'.

Thanks in advance!!

My data before:

After:

My code:

dd = 'input_data';
nowd = cd;                  
d = dir('*.csv');


for j=1:length(d)
tic
    
    filename=d(j).name;
    disp(filename);
    
    dat=readtable(filename);
    data=table2timetable(dat);
    
    try 
    fid = fopen(fullfile(dd,filename)); 
    
    data2=removevars(data, [1 2 3 7 9 10]);
    
    catch
             disp('error');
    
    end

end

[Louise Wilson]

Hi Peter,

Forgive me-I have found the solution:

dd = 'input_data';
nowd = cd;                  
d = dir('*.csv');


for j=1:length(d)
tic
    
    filename=d(j).name;
    disp(filename);
    
    dat=readtable(filename);
    data=table2timetable(dat, 'RowTimes', 'LOCALTIME');
    
    try 
    fid = fopen(fullfile(dd,filename)); 
    
    data2=removevars(data, [1 2 3 4 7 9 10]);
    
    catch
             disp('error');
    
    end

end

%TT = table2timetable(T,'RowTimes',timeVarName) 
%TT = table2timetable(T,'RowTimes','RestorationTime');

[Louise Wilson]

Should I now combine date and time into one column to get a datenumber?

[Peter Perkins]

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]

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]

Do you really have a good reason to go back to using datenums? You are very likely to be much happier using datetimes.