find same dates in two cell array

Hi everyone, i got some troubles in matlab I have one cell array containing 3 columns and 4000 rows. The first colum contain date in order, the second column contain a value min of a sensor and the third column contain a value max of a sensor. I would like to re-synchronize my data in a new tab. My new tab contain date in the first column, i want to find the same date in the old tab and put the min et max value in my newt tab.

I try to do like this:

    for numDate=1:4000
          for j=1:4000
                 date=newtab{numDate,1}; 
                 date2=oldtab(j,1); 
                 if find(ismember(date,date2))
                 newtab(numDate,2)=avg(j,2);
                  break;
                  end
           end
    end

but make a loop 4000 in a loop 4000 is juste not possible knowing that i have in real 38 sensors, this brings me o a time of execution around 75minutes! How can i do more faster?

Thank you, Regards,

Alexis

[dpb]

I don't quite follow--are you saying you simply want the locations in the first array that are in the second? Ignoring that they're dates but just using integers as shorthand example if

a=[ [1:5]' rand(5,2)];
b=[2 3]'

you want the end result

c=[b a(2:3,2:3)];

?

That is the values of the 2nd/3rd columns of a for the indices in column 1 of b that are in a?

I particularly don't get where the avg came from in your example code?

[Cedric]

How are your dates coded? And how is the second order defined?

Answer 1

[Cedric]

At this point I don't know how your dates are coded, but here is a simple illustration. Assume that dates are 28, 29, 30, and data associated with these dates are 100, 101, 102 for min's, and 200, 201, 2002 for max's (in the same order):

 >> table1 = {28, 100, 200; 29, 101, 201; 30, 102, 202}
 table1 = 
    [28]    [100]    [200]
    [29]    [101]    [201]
    [30]    [102]    [202]

Assume as well that you have table2 with the same dates in another order and no data (0's):

 >> table2 = {30, 0, 0; 28, 0, 0; 29, 0, 0}
 table2 = 
    [30]    [0]    [0]
    [28]    [0]    [0]
    [29]    [0]    [0]

Now you want to fill table2 with the data of table1 in the correct order with respect to dates of table2. One way to achieve that is to sort dates from table2 and get indices of sorted elements in the original table2:

 [~,ix] = sort([table2{:,1}]) ;

and then use these indices as a "look up table" for "distributing" values of table1 in the correct order. This is done as follows:

 >> table2(ix,2:3) = table1(:,2:3)
 table2 = 
    [30]    [102]    [202]
    [28]    [100]    [200]
    [29]    [101]    [201]

[Alex]

Thank you for your answer, my problem is that as you can see on the image, i got some gap into my table1 I just want to fill my table where there is a value for the same date and i there is no, stay cell empty

[Cedric]

Ok, then it's not only a reordering. Note that you shouldn't name your cell array "max" as it is conflicting with the function max().

Here is one way to achieve what you want, that I illustrate with a small example:

 tableSynchro = {'2013-03-10 03:30:00', []; ...
                 '2013-03-10 03:30:01', []; ...
                 '2013-03-10 03:30:02', []} ;

 tableMax     = {'2013-03-10 03:30:00', 10; ...
                 '2013-03-10 03:30:02', 15} ;

 corresp = bsxfun( @eq, datenum( tableMax(:,1) ).', ...
                   datenum( tableSynchro(:,1) )) ;

 [r,c] = find( corresp ) ;
 tableSynchro(r,2) = tableMax(c,2) ;

After running this, we have tableSynchro updated with values present in tableMax:

 >> tableSynchro
 tableSynchro = 
    '2013-03-10 03:30:00'    [10]
    '2013-03-10 03:30:01'      []
    '2013-03-10 03:30:02'    [15]

Run this example, and display corresp, r, and c in the command window..

 >> corresp
 corresp =
     1     0
     0     0
     0     1

 >> [r,c]
ans =
     1     1
     3     2

You see that corresp is a table of correspondence between dates from tableSynchro and dates from tableMax (both had to be converted to some numeric equivalent because strings cannot be easily compared in one shot). Then r and c, which are row and column IDs of non-zero elements of this table of correspondences, play the role of a "look up table".

[Alex]

oh god, it works! you rock! :) Thank you very much It took 7 minutes :D

[Cedric]

You're welcome! But 7 minutes seems a lot actually for datasets with 4000-ish entries; if you don't mind sending me your files, I can checkout what takes that much time.

Answer 2

[Alex]

Yep I think it's normal because in real, i have 38 sensors with 4 tab each, min max avg et std which are generated by a server where the request is long, and ater that I should copy the 4 values in my new table for each date. But I attach my file if you want to see Thanks

edit: I hope you can download it, it don't see any file attached?

[Cedric]

It says file corrupt. Did you load all tables and do something like save('infra.mat') ?

Also, if arrays contain 5 columns, the first one being the date and the others the 4 values that you mentioned, you can copy all values at the same time:

 corresp = bsxfun( @eq, datenum( tableMax(:,1) ).', ...
                   datenum( tableSynchro(:,1) )) ;

 [r,c] = find( corresp ) ;
 tableSynchro(r,2:end) = tableMax(c,2:end) ;

Answer 3

[Alex]

Nop I have in fact 4 arrays: max,min,avg and std which contain 3 columns: date, value and a string So i just put 4 time your code for each array and i do it or my 38 sensors

[Cedric]

All 4 arrays have different time stamps? If so, then 7 minutes is reasonable, because calling DATE2NUM on 4000 date strings will take about 1s on a "normal" computer, so ~2s per sensor and per value, which sums up to about 5 minutes for 4 values per sensor and 38 sensors.

If all 4 values share the same time stamps, then you can reuse r and c.

Actually .. if I understand well, you can gain a little time by DATE2NUM-ing the first column of tableSynchro only once:

 dateNumSynchro = datenum( tableSynchro(:,1) ) ;    % Common to all values.

 corresp = bsxfun( @eq, datenum( tableMin(:,1) ).', dateNumSynchro ) ;
 [r,c] = find( corresp ) ;
 tableSynchro(r,2) = tableMin(c,2) ;

 corresp = bsxfun( @eq, datenum( tableMax(:,1) ).', dateNumSynchro ) ;
 [r,c] = find( corresp ) ;
 tableSynchro(r,3) = tableMax(c,2) ;

 corresp = bsxfun( @eq, datenum( tableAvg(:,1) ).', dateNumSynchro ) ;
 [r,c] = find( corresp ) ;
 tableSynchro(r,4) = tableAvg(c,2) ;

 corresp = bsxfun( @eq, datenum( tableStd(:,1) ).', dateNumSynchro ) ;
 [r,c] = find( corresp ) ;
 tableSynchro(r,5) = tableStd(c,2) ;

Answer 4

[Alex]

Hum thank you, indeed, my 4 arrays have same time stamps, but they have not the same length, so if I do what you wrote I obtain "Index exceeds matrix dimensions."

[Cedric]

How can they have same time stamps and not same length?

Answer 5

[Alex]

But you right, I should DATE2NUM-ing the first column only once, I gain some seconds thanks :D

Answer 6

[Alex]

And another question, do you know how can i erase the end of my dates? I mean I want only yyyy-mm-dd HH:MM and erase secondes in my first column I try :

for j=1:1008
        s=mytable(:,1);
        s1 = s(1:end-2);
end

and at the end s contains 138cell and s1 136 why???

[Cedric]

As there is a '0' padding for days and months, you can take the first 10 characters of your dates..

 >> d = '2013-03-10 03:30:00' ;
 >> d(1:10)
 ans =
      2013-03-10

but you cannot do this before having done the match, because there are multiple entries for each day, which would all have the same numeric code if you were truncating the time.

You can apply the truncation to each cell content of a cell array as follows:

 >> d = {'2013-03-10 03:30:00', '2013-03-10 03:30:00'}
 >> truncated = cellfun( @(s)s(1:10), d, 'UniformOutput', false )
 truncated = 
    '2013-03-10'    '2013-03-10'

No idea for your last question, you'll have to debug.