CODEWITHSUNDEEP
matlabdatetimefind
LenaH
LenaH

Reputation: 323

Using find in Matlab cell array for each date

I have the following cell array (wider and longer in reality):

Table = cell(5,4);

Table{1,1} = 'Datetime';
Table(2,1) = num2cell(datetime('01.01.1999','InputFormat','dd.MM.yyyy'));
Table(3,1) = num2cell(datetime('01.01.1999','InputFormat','dd.MM.yyyy'));
Table(4,1) = num2cell(datetime('05.01.1999','InputFormat','dd.MM.yyyy'));
Table(5,1) = num2cell(datetime('05.01.1999','InputFormat','dd.MM.yyyy'));

Table{1,2} = 'ZeroAndOne';
Table{2,2} = 1;
Table{3,2} = 1;
Table{4,2} = 0;
Table{5,2} = 1;

I want to add two columns, one column that is 1 when 'ZeroAndOne' is one for the first time, for each date; and one column that is 1 when 'ZeroAndOne' is one for the last time, for each date. How would you do it in general terms? Here is the solution "by hand":

Table{1,3} = 'OneForFirstTime';
Table{2,3} = 1;
Table{3,3} = 0;
Table{4,3} = 0;
Table{5,3} = 1;

Table{1,4} = 'OneForLastTime';
Table{2,4} = 0;
Table{3,4} = 1;
Table{4,4} = 0;
Table{5,4} = 1;

Upvotes: 1

Views: 178

Answers (1)

Tommaso Belluzzo
Tommaso Belluzzo

Reputation: 23695

If you convert your cell matrix into a table, as follows:

T = cell2table(C(2:end,:));
T.Properties.VariableNames = C(1,:);

You can then use findgroups and splitapply to achieve the result you need:

G = findgroups(T.Datetime);
res = cell2mat(splitapply(@(x){Process(x)},T.ZeroAndOne,G));
T.OneForFirstTime = res(:,1);
T.OneForLastTime = res(:,2);

function res = Process(x)
    res = zeros(numel(x),2);
    res(find(x,1,'first'),1) = 1;
    res(find(x,1,'last'),2) = 1;
end

The process is accomplished by splitting the table into groups based on the date, and then by applying the specified logics on each separate group. This is the result:

 Datetime      ZeroAndOne    OneForFirstTime    OneForLastTime
___________    __________    _______________    ______________

01-Jan-1999    1             1                  0             
01-Jan-1999    1             0                  1             
05-Jan-1999    0             0                  0             
05-Jan-1999    1             1                  1

This works no matter how many entries are allocated for every date (I don't know if only two entries are permitted for each unique date, your example could just be a little bit misleading on this point of view):

% Example...
C = cell(7,2);
C{1,1} = 'Datetime';
C(2,1) = num2cell(datetime('01.01.1999','InputFormat','dd.MM.yyyy'));
C(3,1) = num2cell(datetime('01.01.1999','InputFormat','dd.MM.yyyy'));
C(4,1) = num2cell(datetime('05.01.1999','InputFormat','dd.MM.yyyy'));
C(5,1) = num2cell(datetime('05.01.1999','InputFormat','dd.MM.yyyy'));
C(6,1) = num2cell(datetime('05.01.1999','InputFormat','dd.MM.yyyy'));
C(7,1) = num2cell(datetime('05.01.1999','InputFormat','dd.MM.yyyy'));
C{1,2} = 'ZeroAndOne';
C{2,2} = 1;
C{3,2} = 1;
C{4,2} = 0;
C{5,2} = 1;
C{6,2} = 0;
C{7,2} = 1;

% My code for converting the cell into a table...
% My code for calculating the two additional rows...

Output:

 Datetime      ZeroAndOne    OneForFirstTime    OneForLastTime
___________    __________    _______________    ______________

01-Jan-1999    1             1                  0             
01-Jan-1999    1             0                  1             
05-Jan-1999    0             0                  0             
05-Jan-1999    1             1                  0             
05-Jan-1999    0             0                  0             
05-Jan-1999    1             0                  1

Upvotes: 1

Related Questions