Arranging corresponding elements of rows in a cell.

I have a data file as attached . The first column corresponds to date, second column corresponds to time. Whenever an object is detected at a particular time, the identity of the object along with with the X, Y coordinates are added to the next row.

What i am trying do is to calculate the time in seconds and keep the identity of corresponding detection and coordinates together in a single row. I wrote a code for this, but it is having a problem with matching the time associated with detection. It associates the time stamp of first detection rather than the actual time to the corresponding coordinates.

    for ii=1:data_size{kk}(1)
        if isempty(strfind(data{kk}(ii,1),'-'))==0;  %for checking if a specific pattern '-' is in the rows of the first column nad if its there, then calculate the time in seconds
        [~,~,~,H,MN,S] = datevec(strcat(data{kk}(ii,1)," ", data{kk}(ii,2))); %here it takes the year, month, day, hour,minutes,sec    
        start_time = S+60*MN+3600*H; %here everything is converted to seconds
        else
            ant_trajectories{kk}(dex{kk},1) = start_time;                     %first column have the time in sec when tag is detected
            ant_trajectories{kk}(dex{kk},2) = data{kk}(dex{kk},1);            %takes the id and saves it to second column
            ant_trajectories{kk}(dex{kk},3) = data{kk}(dex{kk},2);            %takes the X coordinate and saves it in the third column
            ant_trajectories{kk}(dex{kk},4) = data{kk}(dex{kk},3);            %takes the Y coordinate and saves it in the fourth column
            dex{kk}=dex{kk}+1;
        end
    end

[Jan]

I do not understand, what the problem is.

Just a hint:

if isempty(strfind(data{kk}(ii,1),'-'))==0

could be expressed simpler:

if contains(data{kk}(ii,1), '-')

or

if any(data{kk}(ii,1) == '-')

[Hari krishnan]

What i am trying to do is to convert the time given in second column to seconds and associate this time to the row when there is a detection. Eg: In line 726 there is a detection. This detection is from the time corresponding to row 725, second column. I want to convert this time (13:50:53) to seconds and put it as the detection corresponding to 2047

[jonas]

Should be fairly easy. Can you give upload some sample data?

[Hari krishnan]

the sample data is attached.

[jonas]

First of all we need to fix how you import the data. Can you attach the original file? .txt or .xlsx or anything else.

[Hari krishnan]

the text file is attached

[jonas]

What happened to the image that you had in your original question? The structure in that image was completely different... In this .txt all dates are associated with multiple values.

[Hari krishnan]

Its essentially the same. I have more detections here, its the only difference. In the image shown before there was only one detection at a given time

[jonas]

As you described it before, the data structure was very clear. You had a bunch of dates, and after certain dates you had data entries corresponding to a detection. You wanted to find the data and the associated time-stamp.

In the file you attached, you have multiple detections (multiple rows of data) for each time-stamp... In terms of coding, this is an entirely different problem. What is your desired output anyway?

[Hari krishnan]

What i want is to calculate the time in seconds of each detection and associate this time to the each detection. Each detection at a time stamp is unique.

[jonas]

So basically you want to have a bunch of dates (rounded to seconds) and their corresponding detection ID's? Something like this?

date1 'id1 id2 id3...'
date2 'empty'
date3 'empty'
date4 'id1 id2'

where id is the first value of the detection, e.g. 1329

[Hari krishnan]

No. I want just the time to be converted to seconds. If you look at line 3 of the code I am doing that. 46802 corresponds to time in seconds. X-val and Y-cal corresponds to the second and third column of detection. What i want is ;

46802 1329 X-val Y-val

46802 1326 X-val Y-val

46802 1324 X-val Y-val

46802 1321 X-val Y-val

46802 1220 X-val Y-val

46802 1212 X-val Y-val

46802 1211 X-val Y-val

46802 1209 X-val Y-val

46802 1855 X-val Y-val

[jonas]

Got it. So in the end your output should have the same amount of rows as the number of detections?

[Hari krishnan]

Yes. Exactly. There will be some time stamps where i donot have any detections. What i just want to do is the associate this with time.

Answer 1

[jonas]

Thsi code got ugly quick. Anyway, here's something I stitched together. I'm not sure if it will work when there is nothing detected.

%%Read dates and the two second rows of data
opt = {'Delimiter','(),\t','MultipleDelimsAsOne',true,'CollectOutput',true};
fmt = ['%s%f%f %*[^\n]']
[fid,msg] = fopen('daaata.txt','rt');
C = textscan(fid,fmt,opt{:});
fclose(fid);

%%Grab the first column of data...
[fid,msg] = fopen('daaata.txt','rt');
fmt = ['%f %*[^\n]']
D = textscan(fid,fmt,opt{:});
fclose(fid);

%%Find index of dates
DateLocs=find(isnan(C{2}(:,1))==1);

%%Convert to DateTime
Dates=datetime(C{1}(DateLocs));

%%Create time-vector
t(DateLocs)=Dates'

%%Remove NaNs from data
data=[C{2} D{1}];

%%Add date at end of series and fillmissing 
t(size(data,1))=Dates(end)
t=fillmissing(t,'previous')'

%%Store in TimeTable
TT=timetable(t,data)

%%Delete rows with NAN
TT(isnan(TT.data(:,1)),:)=[];

TT =

58×1 timetable

             t                         data           
    ____________________    __________________________

    17-May-2018 13:00:02    1703.7    1479.3      1329
    17-May-2018 13:00:02    1644.7    1468.1      1326
    17-May-2018 13:00:02    1585.6    1456.9      1324
    17-May-2018 13:00:02    1526.6    1445.7      1321
    17-May-2018 13:00:02    1606.2    596.35      1220
    17-May-2018 13:00:02    1665.8    590.81      1212
    17-May-2018 13:00:02    1725.6     585.5      1211
    17-May-2018 13:00:02    1785.2    580.38      1209
    17-May-2018 13:00:02    3106.8    327.73      1885
    17-May-2018 13:00:07    1703.7    1479.3      1329
    ...

I don't understand what you meant by converting to seconds but I am sure you can easily do it now that you have the data in DateTime format.

[Hari krishnan]

Thank you, this is what i needed. Unfortunately i am getting an error in this line:

%%Convert to DateTime
Dates=datetime(C{1}(DateLocs));
Error:
Error using datetime (line 616)
Could not recognize the format of the date/time text. You can specify a format character vector using the 'InputFormat' parameter. If the date/time text contain day, month, or time zone names in a language foreign to the 'en_US' locale, those might not be recognized. You can specify a different locale using the 'Locale' parameter.

I added the InputFormat, but still the error persists:

 Dates=datetime(C{1}(DateLocs),'InputFormat', 'yyyy-MM-dd');

[jonas]

Is this when you run with the same data I have used or with your entire data set? I haven't tested it when there are dates with zero data. Try running it with the same txt you uploaded, if you did not already do so.

[Hari krishnan]

2017 b

[Hari krishnan]

I ran with the same file shown in the question

[jonas]

OK! Show me what

C{1}(DateLocs)

outs

Answer 2

[Hari krishnan]

    {'2018-05-17 13:00:02.754000'}
    {'2018-05-17 13:00:07.047000'}
    {'2018-05-17 13:00:11.279000'}
    {'2018-05-17 13:00:15.571000'}
    {'2018-05-17 13:00:19.864000'}
    {'2018-05-17 13:00:24.098000'}
    {'2018-05-17 13:00:28.389000'}

[jonas]

That is really weird... exactly the same output as me. Try with this inputformat

Dates=datetime(C{1}(DateLocs),'inputformat','yyyy-MM-dd HH:mm:ss.SSSSSS');

[Hari krishnan]

Thank you very much for your time. It's solved.

[jonas]

Great! I hope it works with the full dataset as well. Otherwise I have made an alternative solution that may work.

[Hari krishnan]

Hi, I just checked for the full data set, where there are no detections. It didnot work in that case. As i the size of the file is large, am showing a section without detections

[jonas]

OK! Back to work.

[jonas]

It almost works. Do you want an empty date with NaN in the table if there is no detection data? Or just remove the date completely?

[Hari krishnan]

Hi, it works. It was a mistake i was making. Thank you very much :)

[jonas]

Great!