How do I manipulate data from a csv datafile containing date, time and number data?

This sounds intuitively easy but it has me puzzled. Columns 1 and 2 contain datetime formatted data, with column 3 double precision numerical. I can import the data using READTABLE with no apparent problem but subsequent manipulation continues to fall over. Is it best to split this into three separate (n x 1) arrays, or can I work with the data as one (n x 3) matrix? My routine is to read in the data, extract portions of the data (e.g. 600 rows) and then statistically analyse the values of the third column. The routine was working fine until I exceeded 24 hours of data, when it then ran into ambiguity due to ambiguous data type. I continue to run into a problem during subsequent code using DATEVEC, which produces an error message regarding data type, and using TABLE2ARRAY which also produces a data-type error. I am using R2016b. I am pretty new to Matlab and have attached a small sample input data. Cheers Bob

Answer 1

[Andrei Bobrov]

f = fopen('Input Data.csv');
c = textscan(f,'%s %s %f','delimiter',',');
fclose(f);
TT = timetable(datetime(strcat(c{1},c{2}),'In','dd-MMM-yyhh:mm:ss a'),c{3},'Va',{'datas'});

[Robert Fitzell]

Many thanks Andrei for your very prompt response - and your solution sorts the data very quickly. However, if I am applying it correctly, TT is then an (nx1) table, which generates the same error message when I try to use DATEVEC, and an error message if I attempt an operation to the numerical (originally 3rd) column. i.e. Following your suggested code with a subsequent command of either: Value=datevec(TT(x)) or my original commands of temp=readtable('Input Data.csv') which read an nx3 table of data called temp Value=datevec(temp(n,2)) where n=say 2 both produce the same error message of: "The input to DATEVEC was not an array of character vectors." Additionally, if TT is an nx1 table, can I then determine, say, the mean of the values in the original table column 3? Sorry, I know I might be just thick. Cheers and thanks for your help Bob

[Robert Fitzell]

Andrei, Your answer proved the catalyst for me to find the rest of the solution, which appears to work: dod = datevec(TT.Var1) %producing an nX1 array of date data tod=datevec(TT.Var2) %producing an nx1 array of time of day data, %and Value=(TT.Var3) %producing an nx1 array of numerical data No error messages! So many thanks. Bob

Answer 2

[Peter Perkins]

Your file has date and time in two separate fields. Depending on what version of MATLAB you're using, the table that readtable returns may already have datetimes in it, or it might have text. It sounds like the former. If that's correct, what will happen is that readtable will read the second column of your spreadsheet as datetimes, filling in "today" as the date portion.

You say,

"The routine was working fine until I exceeded 24 hours of data, when it then ran into ambiguity due to ambiguous data type."

but your sample file doesn't have an example of that, it's all on one day, all on the same hour. And it's not at all clear what you mean by ambiguous. I can only guess.

My guess is that you mean that while your date stamps run over several days, your time-only stamps seem not to. That's because of what I've described above. Try this: call timeofday on the second variable in the table that readtable returns. That creates a duration. Mow add that duration to the first var in the table. You now have the datetimestamps that I think you want. You may have to adjust the display format to show the time portion, but I think that will happen automatically.

Hope this helps.

[Robert Fitzell]

Thanks for your help Peter. I will certainly look at your suggestions. The term 'ambiguous' was from the Matlab error message. I am probably a somewhat lazy programmer, having been largely using Basic 64 and not needing quite the rigour necessary for Tables of data - subscripted variables in Basic work fairly readily for calculations and, to be objective, I wasn't ever trying to work with tables mixing dates and numerical data. I managed to resolve my computing hurdle starting with the suggestion made by Andrei Bobrov. I'm not sure that I didn't step around the problem, rather than solving it elegantly, but my analyses are now progressing very smoothly and quite quickly. Matlab is certainly a step up from Basic 64. Cheers and best regards Bob