unable to read time stamps from a csv file, unable to read the csv file in matlab

Hai all,

This is with regards to the csv data which i gathered from the raspberry pie. The notepad file which consists of 2500 rows and 8 columns, is difficult to be read in excel as well as in matlab due to some reasons,

1. the file consists of timestamps (eg: 2020-02-05 15:21:51.659353) in this format which is(DD-MM-YYY HH:MM:SS:FF). once i convert this to excel file, the microseconds are trimmed or neglected giving me only HH:MM:SS(resulting in incorrect result when plotting).Since the microseconds are really vital in finding the sampling rate, using excel to read the csv data is turning out to be less useful.
2. i alternatively tried reading the csv data in matlab wth the command 'readtable' which was found to be useful (read all the data including the time stamps), but unfortunately, i was unable to carryout other commands since it is not an array function. i even tried to convert the file using 'table2array' but turned out to show errors.
3. i have tried the lab2mat function to convert the file to .tom function which also lead to errors.

I request any of you who are expert in matlab to solve this issue for me as it is urgent for my thesis work and i am stuck in the middle. i am attaching my acquired csv data alonw with the message. please go through.

[Shebin Rasheed]

my project is basically a motion measurement in a 2D narrow wave tank. the scale model which is a floating ball valve were the accelerometer is clamped to the valve to read its heave motion. The scale model is clamped to a T shaped wooden frame for stability. the x-direction of the accelerometer reads the heave motion which is obtained through the raspberry pi. This data is saved in the form of a csv file. what the aim is to plot the data for acceleration vs time, acceleration vs frequency as well as velocity vs time and displacement vs time. This graph is then compared with the graph obtained by using a filter especially kalman filter (i have got no idea how a kalman filter can be applied in this). I am not that expert in matlab, just a beginner, please, if someone could sort this out, i would really be grateful, please guide me thorugh.

Answer 1

[Cris LaPierre]

If you got it working with readtable, use that. You just need to learn how to access the data in a table. For that, see this documentation page.

[Shebin Rasheed]

Hai cris,

Thnax for the reply, as you have mentioned, i have got it working with the read table command but nfortunately i am not able to conver the time to seconds or perform any kind of equation on the table, i have tried several ways, several existing commands, but nothing worked as intended. as i have indicated in my message, the timestamps that i obtained from the accelerometer through raspberry pi, is in the order of DD-MM-YYY HH:MM:SS:FF, were the main variation happens in the :FF(as the recording happend fro 60 seconds and the data variation noticably occurs during microseconds) area which should be seperated from the timestamp coloumn to a seperate coloumn. The program that i used is wriiten below,

t = readtable('SenseLog-10mm04hz.csv');%read the data as 't'

t.datetime = t.(1) + t.(2); %merge date and time into one variable 't.(1)' indicates D/M/Y and t.(2) indicates H:M:S

x = t(:,2);%reading 'x'as accelerometer value in the vertical direction.

acc = x{:,:}.*9.81;%converting it to m/s^2

[Shebin Rasheed]

i have tried this as well,

A = xlsread('10mm04hz.xlsx'); %read the data from the excel file
Time = A(:,12);%assingng the time bin
Ts1 = mean(diff(Time)); %to find the mean value of the time period
Fs1 = 1/Ts1; % to find the sampling frequency
Fn = Fs1/2; % nyquest frequency
x = A(:,2);%assigning the x direction values to x bin
y = A(:,3);%assigning the y direction values to y bin
z= A(:,4);%assigning the Z direction vales to z bin
%acc = sqrt(x.^2 + y.^2 + z.^2); % possibly in G's
%Ax = x/16384; %dividing the raw data to sensitivity scale factor to get acceleration in gravitational force unit.
Acc= x.*9.80; %converts g to m/s^2
figure()
plot (Time,Acc);%plotting time and acceleration
% xlim([0 0.5E-9]);%limiting the value to 0.5E^-10
xlabel('time in sec');%time in sec in the x-axis
ylabel('acceleration');%Acceleration in cm/sec^2 in the y-axis
L=length(Time); %to read the whole data in the time coloum
L1 = 2^nextpow2(L);
%h=Acc';
%f= Fs1/2*linspace(0,1,L1/2+1);
%f= Fs1*(0:(L1/2))/L1; 
%plot(f,abs(Acc))
Y = fft(x); %taking the fast fourier transform
P2 = abs(Y/L); %taking the absolute value of Y/L1
P1=P2(1:L/2+1);
P1(2:end-1)=2*P1(2:end-1);
freq=Fs1*(0:(L/2))/L;
figure()
plot(freq,P1)
xlabel('frequency')
ylabel('Acceleration')

order = 3; %applying filter of order 3
fcutlow = 1.1;%limit for the lower band filter
fcuthigh = 1.3;%limit for the higher band filter
[b,a]=butter(order,[fcutlow,fcuthigh]/(Fs1/2),'bandpass');
filtsig=filter(b,a,Acc);

Y2 = fft(filtsig);
P22 = abs(Y2/L);
P12=P22(1:L/2+1);
P12(2:end-1)=2*P12(2:end-1);

figure()
plot(freq,P12)
ylim([0 0.2])
xlabel('Freq')
ylabel('Accel - AMP')
title('After filter')

figure();plot(Time,filtsig)
% xlim([10 20])
xlabel('Time')
ylabel('Accel in g')
title('After filter')
j=filtsig.*9.81;
velocity=cumtrapz(Time,j);
velocity1=velocity-mean(velocity); % allow for non-zero start
figure();
plot(Time,velocity1)
% xlim([10 20])
xlabel('Time')
ylabel('velocity in m/s')
title('After filter')

disp=cumtrapz(L,velocity);
disp = disp-mean(disp); % allow for non-zero start
figure();plot(L,disp)
% xlim([10 20])
xlabel('Time')
ylabel('displacement m')
title('After filter')

but there occurs to be error, {Error using cumtrapz (line 61)

LENGTH(X) must equal the length of Y in dim 1.}. Also in this, the file (which i have attaced to this message) i have manually fund the sampling rate of the time stamp and formed another coloumn (12th) which is then read and further process is being carried out.

[Cris LaPierre]

I still am not understanding what the original problem is. It seems you are struggling with how to read in the timestamp.

Try something like this.

opts = detectImportOptions("SenseLog-10mm04hz.csv");
opts = setvaropts(opts,"timestamp","InputFormat","yyyy-MM-dd HH:mm:ss.SSSSSS"); 
t = readtable("SenseLog-10mm04hz.csv",opts);

If you then just want to get the elapsed time, you could do this:

t.elapsedTime = t.timestamp-t.timestamp(1);
t.elapsedTime.Format = "s"

t = 
    temp_h    accel_x     accel_y     accel_z     gyro_x      gyro_y     gyro_z             timestamp             elapsedTime 
    ______    ________    ________    ________    _______    ________    _______    __________________________    ____________
    21.189    0.034424     -1.0107    0.071289    -4.6947      5.5878    -2.7176    2020-02-05 15:21:51.659353           0 sec
    21.189    0.044922     -1.0076    0.062256    -2.5802      5.5573    -2.5344    2020-02-05 15:21:51.690022    0.030669 sec
    21.236    0.063477     -1.0322    0.070068    -1.7252       3.313    -3.0992    2020-02-05 15:21:51.720578    0.061225 sec
    21.283     0.02417     -1.0361    0.098389    -0.9771     0.83206    -4.1145    2020-02-05 15:21:51.751118    0.091765 sec
    21.142    0.027344    -0.95581    0.043945    0.25954     0.16031    -3.3511    2020-02-05 15:21:51.781722     0.12237 sec
    21.095    0.069092    -0.96362    0.072266    0.37405     0.84733     -2.542    2020-02-05 15:21:51.812266     0.15291 sec
    21.142     0.03418    -0.97119     0.11011    -2.1374     0.19084    -3.0229    2020-02-05 15:21:51.842803     0.18345 sec
    21.236    0.046631    -0.98584    0.070557    -3.1603      -1.145    -2.8779    2020-02-05 15:21:51.873338     0.21398 sec
    21.283    0.069092     -0.9856    0.074951    -2.9924     -1.2595    -3.6641    2020-02-05 15:21:51.903864     0.24451 sec
    21.236     0.11475    -0.98657    0.071777    -1.7939    -0.30534    -2.8931    2020-02-05 15:21:51.934396     0.27504 sec
...

I see in your code you want to convert accel_x to .

acc=t.accel_x*9.81;

[Cris LaPierre]

And here is how I would update your code to work with the table the code I shared creates. This runs without error for me. It is still up to you to determine if the plots and numbers calculated are correct.

Time = seconds(t.elapsedTime); %A(:,12);%assingng the time bin
Ts1 = mean(diff(Time)); %to find the mean value of the time period
Fs1 = 1/Ts1; % to find the sampling frequency
Fn = Fs1/2; % nyquest frequency
x = t.accel_x; %A(:,2);%assigning the x direction values to x bin
y = t.accel_y; %A(:,3);%assigning the y direction values to y bin
z= t.accel_z; %A(:,4);%assigning the Z direction vales to z bin

Acc= x.*9.80; %converts g to m/s^2

figure()
plot (Time,Acc);%plotting time and acceleration
% xlim([0 0.5E-9]);%limiting the value to 0.5E^-10
xlabel('time in sec');%time in sec in the x-axis
ylabel('acceleration');%Acceleration in cm/sec^2 in the y-axis
L=length(Time); %to read the whole data in the time coloum
L1 = 2^nextpow2(L);

Y = fft(x); %taking the fast fourier transform
P2 = abs(Y/L); %taking the absolute value of Y/L1
P1=P2(1:L/2+1);
P1(2:end-1)=2*P1(2:end-1);
freq=Fs1*(0:(L/2))/L;
figure()
plot(freq,P1)
xlabel('frequency')
ylabel('Acceleration')

order = 3; %applying filter of order 3
fcutlow = 1.1;%limit for the lower band filter
fcuthigh = 1.3;%limit for the higher band filter
[b,a]=butter(order,[fcutlow,fcuthigh]/(Fs1/2),'bandpass');
filtsig=filter(b,a,Acc);

Y2 = fft(filtsig);
P22 = abs(Y2/L);
P12=P22(1:L/2+1);
P12(2:end-1)=2*P12(2:end-1);

figure()
plot(freq,P12)
ylim([0 0.2])
xlabel('Freq')
ylabel('Accel - AMP')
title('After filter')

figure();plot(Time,filtsig)
% xlim([10 20])
xlabel('Time')
ylabel('Accel in g')
title('After filter')
j=filtsig.*9.81;
velocity=cumtrapz(Time,j);
velocity1=velocity-mean(velocity); % allow for non-zero start

figure();
plot(Time,velocity1)
% xlim([10 20])
xlabel('Time')
ylabel('velocity in m/s')
title('After filter')

disp=cumtrapz(L,velocity);
disp = disp-mean(disp); % allow for non-zero start
figure();
plot(disp) % I removed the "L" as x, since it contains a single number (2500)
xlabel('Time')
ylabel('displacement m')
title('After filter')

[Shebin Rasheed]

hai cris thank you so much for your effor to help me, but unfortunately these codes are'nt detecting in my matlab. i think its because of the version. i carry the matlab 2014 version. this might be the latest one.

[Shebin Rasheed]

this is regarding the the file detecting code, especially 'detectimportoption'

[Cris LaPierre]

Yes, that would be a problem. Can you update? The import functionality and date handling just keeps getting better and better. If not, it is important to know if you have 2014a or 2014b, as the datetime function wasn't released until 2014b.

[Shebin Rasheed]

Hai Cris,

as u suggested i have got matlab R2019a, and tried to read the file and got the error as shown,

Error using matlab.io.internal.functions.SetVarOpts/tryAssign (line 123)

To set the option 'InputFormat', it must be available for all the variables in the selection.

Error in matlab.io.internal.functions.SetVarOpts/execute (line 66)

varopts = func.tryAssign(varopts,supplied);

Error in matlab.io.internal.functions.SetVarOpts/validateAndExecute (line 74)

[varargout{1:nargout}] = func.execute(supplied);

Error in matlab.io.ImportOptions/setvaropts (line 395)

opts = func.validateAndExecute(opts,varargin{:});

Error in Untitled (line 5)

opts = setvaropts(opts,"timestamp","InputFormat","yyyy-MM-dd HH:mm:ss.SSSSSS");

[Cris LaPierre]

So close. I'm using R2019b, and it runs without error. Let me see if I can get access to 19a and test.

[Cris LaPierre]

Ok, got it. In r2019a, the dates are not getting detected automatically. Instead, they are getting read in as text, causing the error related to the InputFormat setting - it doesn't work with text. The dates need to be datetimes. So all we need to do is set it manually.

opts = detectImportOptions("SenseLog-10mm04hz.csv");
opts.VariableTypes{8} = "datetime";
opts = setvaropts(opts,"timestamp","InputFormat","yyyy-MM-dd HH:mm:ss.SSSSSS"); 
t = readtable("SenseLog-10mm04hz.csv",opts);
...

[Shebin Rasheed]

Hey chris, thanx a lot for the help. it did work as expected. your effort was priceless. The above coding which you have corrected for me was regarding using a band pass filter to filter out the unnecessary datas from the obtained information from the raspberry pi module. i have to try kalman filter for the same as well. but, infortunately i am not aware about its implementation, unlike the band pass filter. i have a sample code with me but its really confussing. will you be able to help me with that as well.

[Cris LaPierre]

It would be better to start a new question focused just on implementing the kalman filter.

[Shebin Rasheed]

sure. as you say.