Combine time related columns into timestamp for data in csv

Hi, I have a number of csv files where I would like change the way date and time data are recorded so that it is timestamped.

Time and data are currently stored as below:

year,month,day,hour,minute,second,data1,data2,data3

2009,1,1,0,0,0,2.9,12.1,7.1

I would like the CSV to read:

2009-01-01 00:00:00, 2.9, 12.1,7.1

I have tried the following script, which I appreciate has a few inefficiencies:

for y = 1:5 
    yStr = num2str(y); 
    
        
    C = csvread([folderPath,'c',yStr,'.csv']);
    t = string(datestr(datetime(C(:,1),C(:,2),C(:,3),C(:,4),C(:,5),C(:,6)), 'yyyy-mm-dd HH:MM:SS'));
    D = [t,C(:,7),C(:,8),C(:,9)];
    
    csvfilename = 'd'+string(y)+'.csv';
    csvwrite(csvfilename,D);
   
end

However the output I get has each individual letter in the string seperated by a comma.

How do I get the script to record the timestamp in my preferred format? The data stored in the variable 't' looks correct when viewed

Answer 1

[Dave B]

The datestr is perhaps adding confusion?

This page has some good information on how to format datetimes: https://www.mathworks.com/help/matlab/matlab_prog/set-display-format-of-date-and-time-arrays.html

%% Generate some data that looks like your C
somerandomdates = datetime('now') - days(rand(100,1)*10);
C = [somerandomdates.Year somerandomdates.Month somerandomdates.Day somerandomdates.Hour somerandomdates.Minute somerandomdates.Second];

%% convert back to datetime, set the format, convert to string
dt = datetime(C); % Or you could use C(:,1:6) if you have more columns in your matrix
dt.Format = 'yyyy-MM-dd hh:mm:ss'; % note that MM is month, mm is minute
string(dt)

ans = 100×1 string array
    "2021-09-05 04:52:05"
    "2021-08-29 12:29:16"
    "2021-08-31 09:44:02"
    "2021-09-02 02:55:05"
    "2021-08-29 05:57:31"
    "2021-09-05 04:09:57"
    "2021-08-30 04:52:39"
    "2021-08-28 07:53:39"
    "2021-09-03 02:28:25"
    "2021-09-02 07:51:07"
    "2021-08-31 11:15:13"
    "2021-08-29 07:52:29"
    "2021-08-30 02:04:30"
    "2021-08-30 03:50:26"
    "2021-09-06 02:32:11"
    "2021-08-31 06:28:24"
    "2021-09-01 04:57:40"
    "2021-09-01 11:04:14"
    "2021-09-05 09:00:17"
    "2021-09-07 04:41:36"
    "2021-09-02 09:40:03"
    "2021-09-04 02:09:25"
    "2021-09-03 07:26:21"
    "2021-08-30 06:32:53"
    "2021-09-01 04:19:05"
    "2021-08-29 12:43:07"
    "2021-09-06 06:31:58"
    "2021-08-29 12:55:13"
    "2021-08-30 01:19:35"
    "2021-08-29 05:53:59"

I'd also recommend considering writematrix instead of csvwrite, or you might consider writecell/writetable for your heterogenous data. If you load the data with readtable then writetable will work particularly well.

writematrix([string(dt) string(rand(size(dt)))], 'foo.csv')

[Ant376]

Hi Dave, thank you for taking the time to answer again.

I have got round my problem using writematrix instead of csvwrite.

At some point while playing with the script (not sure what I did...) I started getting error messages instead of the script outputing csv files with undesired timestamp format.

The variable D in my original script contained the information I wanted in the below format:

I then replaced:

csvwrite(csvfilename,D);

with:

writematrix(D,csvfilename);

which gave me csv files with the below format:

2009-01-01 00:00:00,2.9247,12.011,7.151

2009-01-01 01:00:00,2.8886,11.638,7.2377

2009-01-01 02:00:00,2.8553,11.457,7.3229

I have checked the months and minutes in the outputted csv and it is all correct, apologies if I am missing something, I am very new to matlab.

[Dave B]

No worries, if you have writematrix then by all means use it instead of csvwrite, it's better in every way. It sounds like this resolves your issue, but if you're still struggling or need to use csvwrite for some reason please let me know.

If you can use readtable/writetable instead, you may find it's even easier - tables work naturally with different types of variables, so you don't have to pretend that the values are all strings.

[Ant376]

That's good to know, thank you again for the help!