how to expand the date matrix?

Hi

I need your help.

I have a csv file with date minimum and date maximum in the last 2 columns. I'd like to expand the date column such that each column is one date. For example, below first is my data, column 3-4 is date min ( Aug 1 2019) and date max (Sept 1 2019). From this matrix, I'd like to expand the matrix to the 2nd figure with 30 columns ( from Aug 1 2019-Aug 2 2019 to Sept 1 2019). How to do it? Thanks

Answer 1

[Peter Perkins]

roudan, this question doesn't seem to make any sense. For one thing, it doesn't seem useful that you have a column whose header is 8/1/2019, and all of whose values are 8/1/2019. Same for all the other columns. For another, you say Aug 1 to Sep 1, but you example doesn't even show that.

You are also showing only Excel. If you are asking about how to do this in Excel, you are in the wrong place.

In any case, I suggest you use readtable to import your spreadsheet into a table. Your spreadsheet has two header rows, I have no idea what will actually happen but you will have to sort that out yourself. At that point, you have a table that looks somethign like this:

>> t = table([1;2;3],[4;5;6],datetime(2019,8,[1;1;1]),datetime(2019,9,[1;1;1]))
t =
  3×4 table
    Var1    Var2       Var3           Var4    
    ____    ____    ___________    ___________
     1       4      01-Aug-2019    01-Sep-2019
     2       5      01-Aug-2019    01-Sep-2019
     3       6      01-Aug-2019    01-Sep-2019

Then do something like this:

>> Dates = t.Var3 + caldays(0:5)
Dates = 
  3×6 datetime array
   01-Aug-2019   02-Aug-2019   03-Aug-2019   04-Aug-2019   05-Aug-2019   06-Aug-2019
   01-Aug-2019   02-Aug-2019   03-Aug-2019   04-Aug-2019   05-Aug-2019   06-Aug-2019
   01-Aug-2019   02-Aug-2019   03-Aug-2019   04-Aug-2019   05-Aug-2019   06-Aug-2019
>> t = [t(:,1:2) array2table(Dates)]
t =
  3×8 table
    Var1    Var2      Dates1         Dates2         Dates3         Dates4         Dates5         Dates6   
    ____    ____    ___________    ___________    ___________    ___________    ___________    ___________
     1       4      01-Aug-2019    02-Aug-2019    03-Aug-2019    04-Aug-2019    05-Aug-2019    06-Aug-2019
     2       5      01-Aug-2019    02-Aug-2019    03-Aug-2019    04-Aug-2019    05-Aug-2019    06-Aug-2019
     3       6      01-Aug-2019    02-Aug-2019    03-Aug-2019    04-Aug-2019    05-Aug-2019    06-Aug-2019

[roudan]

Thanks Peter, yes header is the date, and data in the column is equal to header. That is what I design it for. I am not using excel to do. I am doing it in Matlab.. I like your code to use caldays() which is new to me. Thanks. for your method, how to add header to be date as well? Thanks

Answer 2

[roudan]

ok,

Here is what I come up with, not efficient. could you please review it and appreciate any suggestion for improvement. Thanks

A=rawdata(3:,4:end);

numdays=datenum(A(3,end),'mm/dd/yyyy')-datenum(A(3,1),'mm/dd/yyyy')+1;

[row,column]=size(A);

%create an empty array with known size

B=zeros(row,numdays);

%fill the array with date based on min and max

for col=1:numdays % loop over columns

B(:,col)=datenum(A(:,1),'mm/dd/yyyy')+col-1;

end

[roudan]

the for loop take a long time, how can i convert it to broadcasting to make it faster? Thanks