find same dates in two cell array

4 次查看（过去 30 天）
Alex
Alex 2013-10-3
评论： Cedric 2013-10-8
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
  2 个评论
dpb
dpb 2013-10-3
编辑：dpb 2013-10-3
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
Cedric 2013-10-3
How are your dates coded? And how is the second order defined?

请先登录，再进行评论。

请先登录，再回答此问题。

采纳的回答

Cedric
Cedric 2013-10-3
编辑：Cedric 2013-10-3
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]
  4 个评论
显示 2更早的评论
Alex
Alex 2013-10-4
编辑：Alex 2013-10-4
oh god, it works! you rock! :) Thank you very much It took 7 minutes :D
Cedric
Cedric 2013-10-4
编辑：Cedric 2013-10-4
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.

请先登录，再进行评论。

更多回答（5 个）

Alex
Alex 2013-10-4
编辑：Alex 2013-10-4
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?
  1 个评论
Cedric
Cedric 2013-10-4
编辑：Cedric 2013-10-4
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) ;

请先登录，再进行评论。

Alex
Alex 2013-10-4
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
  1 个评论
Cedric
Cedric 2013-10-4
编辑：Cedric 2013-10-4
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) ;

请先登录，再进行评论。

Alex
Alex 2013-10-7
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."
Alex
Alex 2013-10-7
But you right, I should DATE2NUM-ing the first column only once, I gain some seconds thanks :D
Alex
Alex 2013-10-8
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???
  1 个评论
Cedric
Cedric 2013-10-8
编辑：Cedric 2013-10-8
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.

请先登录，再进行评论。

类别

在 Help Center 和 File Exchange 中查找有关 Calendar 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by