Using find in Matlab cell array for each date

Question

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;

Do you mean that ZeroAndOne, changes over time? And how is ZeroAndOne determined? — ViG
– ViG, Commented Feb 6, 2018 at 12:19

Tommaso Belluzzo · Accepted Answer · 2018-02-06 21:53:40Z

1

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

edited Feb 6, 2018 at 21:53

answered Feb 6, 2018 at 21:07

Tommaso Belluzzo

23.7k8 gold badges77 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LenaH Over a year ago

Hi Tommaso, thank you! Are there any (dis)advantages of working with cell arrays vs tables in this instance? I always find that quite confusing...

Tommaso Belluzzo Over a year ago

Actually, I can't find any disadvantage. Tables are very similar to cell matrices, in the sense that they can let you work with different types crammed together. The difference is that, for example, calling a column header can let you obtain the column data in the original type. On the top of that, there is a wide variety of built-in functions that help you working with them.

Tommaso Belluzzo Over a year ago

What you are creating with cells is just a table.. so why not using a real one instead? I gave you the code for casting a cell to a table supposing you were using the matrix all along your previous code, but you can also create one directly.

Collectives™ on Stack Overflow

Using find in Matlab cell array for each date

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related