Convert timestamps in CSV file to seconds using readtable and table2timetable

Hello,

I found several solutions for converting timestamps to seconds (by eliminating year, month, day...), but none of them worked. How can I use readtable() and table2timetable() for converting the timestamps in my CSV file to seconds? Attached you can find the CSV file. My goal is to plot gX, gY, gZ in terms of time. What is the most efficient way of applying that?

Thanks

[dpb]

First you need to create the input file with sufficient precision to differentiate between samples (or eliminate the repeated timestamps in some fashion).

After you do that, I suspect you'll have better luck using the timeseries object than the datetime or timetable to use linear time instead of calendar time for plotting as the duration time object is hamstrung for less than seconds intervals it appears.

Or, just revert to the old datenum and treat the absicssa as purely numeric and fixup the axes using the datetimeruler manually.

[YH]

thanks for the reply. Which function should I use to read the CSV file and import the TimeStamps data to timeseries? Can you give example for using timeseries as required?

Answer 1

[Jeremy Hughes]

This will work as long as the times are on the same day (as in the file).

T = readtable(file,'Format','%{dd-MMM-uuuu HH:mm:ss.SS}D%f%f%f')
times = timeofday(T.TimeStamp);
times.Format = 'hh:mm:ss.SSS';
TT = table2timetable(T(:,2:end),'RowTimes',times)

you can also subtract datetimes, which would make them elapsed times since start. Not sure what you're looking for.

times = T.TimeStamp - T.TimeStamp(1)

[YH]

thank you. Kind of what I was looking for. The goal is to plot the measurements with respect to the time (TimeStamps). And csvread function doesn't read non-numeric data. So I had to use readtable. Now I have to convert hh:mm:ss.SSS to seconds as elapsed times or duration.

[YH]

I realized that I can actually plot the numeric values in terms of time format hh:mm:ss.SSS without the need to convert it to seconds. Perfect!

[Jeremy Hughes]

Yes! Even better then.

[dpb]

Jeremey, I was looking specifically at the original request to plot in seconds and ran into an issue with duration variables and diff applied to datetime -- while it is documented it makes it rather useless to trash fractional seconds as it does.

The timeseries object is also pretty rough and doesn't really match up with datetime; it really seems like two attempts at what could be done with one--or at least make syntax/use much more consistent. It doesn't seem as though there's a real top-level design philosophy that permeates the entire product any more...more like competing internal teams vying to introduce features somewhat akin to fighting for skit time on SNL.

[YH]

I plot the graph with time basis as in CSV. I couldn’t subtract the time because it eliminates the fractional seconds, which becomes useless for me. So instead of starting at time 00:00:00.00, I had to start at 16:14:44.64 Which doesn’t look so good, but at least it’s working

[Eric Sofen]

YH and dpb,

Subtracting off the date or using diff does not eliminate the fractional seconds. The default display format for duration does not show fractional seconds:

>> d = datetime('now')-datetime('yesterday')
d = 
duration
 40:34:59
>> d.Format = 'hh:mm:ss.SSS'
d = 
duration
 40:34:59.187

Setting a display format with fractional seconds makes them appear. Also, the datetime/duration plotting "knows" about the fractional seconds whether or not they are displayed, so the data should be properly plotted starting from 00:00:00.00 after subtracting off the first datetime. If it's not, that's a bug.

[dpb]

OK, Eric, I stand corrected; it was the fignewton of appearance between the default duration format plus YH's insufficient precision in his input data that combined to make me draw the wrong conclusion on what had gone wrong.

Seeing

    >> t(1:10,:)
  ans = 
      TimeStamp       gX           gY          gZ   
      _________    _________    ________    ________
      18:17.3      -0.076035    -0.71863    -0.61618
      18:17.3      -0.082672    -0.74306     -0.6469
      18:17.3       -0.03009    -0.76733    -0.79691
      18:17.3       0.020218    -0.81285    -0.86838
      18:17.3       0.049622    -0.85599    -0.90698
      18:17.3       0.058319    -0.91023    -0.89687
      18:17.3       0.071381    -0.93538    -0.84306
      18:17.4       0.088852    -0.94637     -0.7709
      18:17.4        0.13455    -0.92213    -0.71928
      18:17.4        0.21944    -0.86867    -0.67058
  >> dt=diff(t.TimeStamp);
  >> [min(dt) max(dt)]
  ans = 
    1×2 duration array
     00:00:00   00:00:00
  >>

I fell into the trap that by using max the underlying data would have been revealed; while there is some convenience in being able to set the format there's some idiosyncracies that come along with it, one being the above that small but non-zero values don't show. (That's also true w/ default double format but that's where the min/max "trick" generally reveals the truth whereas needs must remember that doesn't carry over with time formats.)

I also don't like the limited choice of output formats for a duration; it wouldn't accept just 'ss.SSS', for example, if one did want to see it in cumulative seconds as YH asked for in original ?.

Thanks for the follow-up...

[YH]

thanks Eric for the clarification. Now the time axis looks much better.

dpb, the reason for having redundant values is because I'm using the sensor of my phone (accelerometer) with very high resolution (having 11808 data entries for 200 steps..). And I guess having background applications running at the same time make my phone gets slower and unable of continuing measuring in such high resolution for data acquisition. The precision problem can be solved, as you said, by eliminating the redundant values using

unique(T, 'stable');

[dpb]

Depending upon what you're trying to do with the data you might also be able to interpolate the time vector to backfill the missing times accurately enough...

[YH]

Yep, it might help too