How to allocate the content of a M*1 vector to a T*N matrix (M<T*N) ? problem with cell format

Dear all

I am newcomer to matlab and have a question that, I believe, should be easy to solve. Yet, I have lost way too many hours on this particular detail and find myself unable to advance. I hope someone can point out the problem. Maybe the function is poorly coded to begin with. Anyways, thanks in advance!

I have a set of unbalanced monthly data for various firms.

I want to create a matrix y: dim(y) = T * N, where T is number of periods and N is number of firms

My idea is to create a function - ymatrix - that retrieves the value of interest at time t for firm n and places it in position (t,n) in the matrix y.

With this in mind, I wrote the function below:

 function y = ymatrix(intersection,date,id,var)

 % intersection is an argument that allows me to determine the number of columns of y. % id is the the identification
 % number of the firm - in cell format % var is that variable of interest

 [R C] = size(intersection);

 T = [2007 - 1964 + 1]*12 + 12;

 clear C

 y = NaN(T,R);

 clear T

 sortDate = sort(date,'ascend'); % I do no sort IDs as they are already sorted in the dataset

 T = length(date);

 N = length(id);

 past = cell(T,1);

 for t = 1 : T

    for n = 1 : N

        if sum(ismember(past,id{n})) == 0               

       % This is where the problem seems to lie. Since I want to have each column of y as a firm
       % and since in the dataset a given id appears many times, I must apply the current loop only for firms to which
       % I haven't applied it before. Otherwise, I will have several repeated columns.
       % Hence, at the end of each loop, I want to add the id of the firm I am currently looping through to a 
       % local variable - "past" - that I can then use - in the next loop - to decide whether to skip that particular
       % loop. The problem is that id is in cell format. Either I pre-allocate memory to past in cell format and 
       % use past{n,1} = id{n,1}, in which case, in the next loop, I receive an error in sum(ismember(past,id{n})) ==
       % 0 that reads :
       %"Input must be cell arrays of strings"; or I do not preallocate it to be a cell and allocate it as, 
       %for instance,past = NaN(10,1), and I receive an error in past{n,1} = cusip{n,1} that 
       %reads: "Cell contents assignment to a non-cell array object."

            a = ismember(date,sortDate(t));

            b = ismember(id,id{n});                     

            a = int8(a);

            b = int8(b);

            d = find(a == 1);

            e = find(b == 1);

            index = intersect(d,e);         % Note that the intersection must either be a number of empty

            past{n,1} = id{n,1};

                if index == zeros(0,1)      % This is to account for the possibility that the intersection is empty,
                                            % in which case I simply skip the current loop
                else

                    y(t,n) = var(index,1); 

                end
        else

        end

    end

 end

 end

[James Grayout]

Hey, just realised that the int8(a) stuff and the clear T and clear C are unnecessary.probably also better to use R = numel(intersection) rather than [R C] = size(.). Still blocked concerning the cell format issue, though.

[Guillaume]

Can you provide a reduced example of inputs and desired output? It'll be much easier to answer your question that way rather than trying to understand what you're doing in your function, which as you say, may not be the best approach.

[James Grayout]

inputs:

intersection - 10281*1 cell

 '00003210'
 '00016510'
 '00020910'
 '00035210'

date - 1431697*1 double

 198512
 198601
 198602
 198603

id - 1431697*1 cell

 '36720410'
 '36720410'
 '36720410'
 '36720410'

var - 1431697*1 double

 NaN
 NaN
 -0,509591836734694
 -0,590000000000000

I want the output to be a matrix such that:

1) each row is a date

2) each column is a firm

Hence y(1,1) = the value of the variable for the "1st" firm in the "1st time period".

Hope this helps, Guillaume. Let me know if you need further info .

[James Grayout]

another detail about the input date:

I have monthly data for 1964 till 2007 plus data from July to December 1963 and date from January to June 2008.

Hence, the output matrix should be a (12 + 12*(2007-1964+1))*10281 matrix, where 10281 is the number of firms. Some of the entries are supposed to be NaN as I do not have data for all firms for all years.

Answer 1

[Guillaume]

If I understood correctly, this should work:

[udate, ~, drow] = unique(date); %udate could be replaced by ~ if you don't want a table
[uid, ~, idrow] = unique(id);    %uid could be replaced by ~ if you don't want a table
y = nan(max(drow), max(idrow));
y(sub2ind(size(y), drow, idrow)) = var;

You can prettify the output with:

t = array2table(y, VariableNames, uid, RowNames, cellstr(num2str(udate)))

Note that neither date or id need to be sorted since unique does the sorting anyway.

[James Grayout]

Hi Guillaume. Thanks for that. It does work in general but not in this particular case. The problem is that the variable id is the intersection of the id's of two different databases (that is why I included the variable intersection in my function). However, I cannot simply use that "intersection id" as it has less observations than the "original" id. And if I use the original id, then I will have more firms (columns) than what I want... any ideas? my function is superbly innefficient...it is still running... and probably is not even correct

[Guillaume]

So you only want those id that are found in intersections? Isn't it just a matter of filtering all three arrays (id, date, and var) according to the id membership?

rowstokeep = ismember(id, intesection);
[udate, ~, daterow] = unique(date(rowstokeep));
[uid, ~, idrow] = unique(id(rowstokeep));
y = nan(max(daterow), max(idrow));
y(sub2ind(size(y), daterow, idrow)) = var(rowstokeep);

[James Grayout]

it works! thanks loads! one question: would you recommend any specific resources to be able to learn how to code more efficiently in matlab? of course only time and experience can do the trick, but I was wondering if you could recommend some resources. I read this one https://www.amazon.co.uk/Matlab-Stormy-Attaway/dp/0124058760/, which was useful to get the basics. Yet, it is quite basic...

[Guillaume]

I'm afraid I've only learned matlab from its help file but I had a lot of experience programming in other languages beforehand. I've actually never read any book about matlab.

Solving problems (the not too trivial ones) on Cody and seeing other solvers' approach may be a good way of acquiring experience with matlab functions.

[James Grayout]

Thanks!

Answer 2

[Renato Agurto]

If you preallocate past:

 past = NaN(10,1);

then you should use

 past(n,1) = id{n,1};

instead of

 past{n,1} = id{n,1};

[James Grayout]

Thanks Renato! I did as you suggested but now I get

 Subscripted assignment dimension mismatch.

error message associated with

 past(n,1) = id{n,1};

the problem seems to be that id{n,1} is 1*8 char.

I could define past as

 past = NaN(N,8)

but then

 sum(ismember(past,id{n})) == 0

would no longer make sense. In principle

 sum(sum(ismember(past,id{n}))) ~= 8

would work, though. But then

 past(n,:) = id{n,1};

no longer works. for instance, id{1,1} = 36720410 and past(1,:) = id{1,1} yields

 past = [51  54  55  50  48  52  49  48;
         NaN(T-1,8)]

Any ideas?

[James Grayout]

ps: id{1,1} is only numbers by chance. quite often id's are like 652AA145. thus the need for cells

[James Grayout]

Hi again. So the loop is currently working when I write ymatrix as shown below. I am not sure whether the result will be correct though, as the loop will take a while.

 function output = ymatrix(intersection,date,id,var)                         

 R = numel(intersection);

 T = [2007 - 1964 + 1]*12 + 12;

 Y = NaN(T,R);

 sortDate = sort(date,'ascend');                                             

 T = length(date);

 N = length(id);

 past = NaN(N,8);

 for t = 1 : T

    for n = 1 : N

        if sum(sum(ismember(past,id{n}))) ~= 8

            a = ismember(date,sortDate(t));

            b = ismember(id,id(n));                     

            d = find(a == 1);

            e = find(b == 1);

            index = intersect(d,e);                                         

            past(n,:) = string(id{n,1});

                if isempty(index)

                else

                    Y(t,n) = var(index,1); 

                end
        else

        end

    end

 end

 output = Y(t,n);

 end

Yet, in each loop, I get the message

 In ymatrix at 36
 Warning: string is obsolete and will be discontinued.
         Use char instead

1) However, char does not work in this context... Am I making any mistake here?

2) Also, is there any way of not having the warning appearing in each loop?

3) Assuming that the code produces the desired result, would there be a more efficient/faster way of doing the same thing?