Fail to create a new variable in the Timetable

I have a 3 column timetable and want to create the fourth column with values assigned from the third column with a pre-specified step. Running this code, gives me an error of 'To assign to or create a variable in a table, the number of rows must match the height of the table.'

ShiftDates = ExpirationDates1.'
DateNumber = datenum(ShiftDates)
  for n = DateNumber 
  if datenum(Data.Date) >= n & datenum(Data.Date)< (n+91)
      Data.Rate = Data{datestr(n),3}
  else Data.Rate = 3.686
  end
  end

Answer 1

[Peter Perkins]

Angelina, if you are using timetables, you will likely be happier using datetimes, rather than going back and forth between those and datenum/datestr. For one thing, you can get rid of, I think, both loops using discretize on the row times of your timetable.

From your original code it looks like you want to use 733117:91:737044, or maybe [733117:91: 737030 737044] to define time bins, and assign the Rate for each row of the timetable Data as the value of the third Data variable at that row's lower bin edge, with two hardcoded Rates for rows that fall outside those bin edges. You've got two different things in two different code snippets, but as near as I can tell, three lines do what you want.

First set up some fake data

>> dt = datetime(2007,3,14) + caldays(0:3931)';
>> tt = timetable(randn(size(dt)),'RowTimes',dt,'VariableNames',{'Value'});

Create the edges, discretize to the bin values, and add the Rate variable to the timetable.

>> % binEdges = datetime([-Inf 733117:91:737044 737044 Inf],'ConvertFrom','datenum');
>> binEdges = [datetime(-Inf,0,0) datetime(2007,3,16):caldays(91):datetime(2017,12,1) datetime(2017,12,15) datetime(Inf,0,0)];
>> ttEdges = tt(binEdges(2:end-1),:)
ttEdges =
  8×1 timetable
       Time         Value  
    ___________    ________
    16-Mar-2007     0.14403
    15-Jun-2007     0.11639
    14-Sep-2007      1.0293
    14-Dec-2007     -0.7579
    14-Mar-2008    -0.96555
    13-Jun-2008    0.021778
    12-Sep-2008     0.61274
    12-Dec-2008      1.7534
[snip]
>> binValues = [3.686; ttEdges.Value(1:end-1); -0.329];
>> tt.Rate = discretize(tt.Time,edges,binValues)
tt =
  8×2 timetable
       Time          Value        Rate  
    ___________     ________     _______
    14-Mar-2007      -2.0342       3.686
    15-Mar-2007      0.56785       3.686
    16-Mar-2007      0.14403     0.14403
    17-Mar-2007      -1.1699     0.14403
    18-Mar-2007       0.1909     0.14403
    19-Mar-2007     -0.02497     0.14403
    20-Mar-2007      0.69467     0.14403
    21-Mar-2007      -1.0174     0.14403
[snip]
    10-Dec-2017       1.0961    -0.55113
    11-Dec-2017      -1.1777    -0.55113
    12-Dec-2017      0.09743    -0.55113
    13-Dec-2017       -1.256    -0.55113
    14-Dec-2017     -0.69425    -0.55113
    15-Dec-2017       1.3073      -0.329
    16-Dec-2017    -0.047647      -0.329
    17-Dec-2017        0.964      -0.329

Answer 2

[KL]

if Data is your timetable, you cannot just say

 Data.Rate = Data{datestr(n),3} or Data.Rate = 3.686

Pre-allocate the new column first, like

 Data.Rate = cell(height(Data),1);

and then use indexing inside your for-loop,

 Data.Rate(k) = Data{datestr(n(k)),3}

additional tip: Use the for loop to index through another variable. For example,

 for k=1:numel(DateNumber)
   if DateNumber(k)...

[Angelina]

So I have modified the code, but now I get the 'Index exceeds matrix dimensions' error. What I'm trying to get with the second elseif condition is that if the date associated with a Data.Rate{k} belongs to an interval [733117:733208], this Data.Rate{k} is assigned value from the third column as at 733117 and so on. It obviously is not working, but I don't see why...

Data.Rate = cell(height(Data),1);
for k=1:numel(Data)
    for n = [733117:91:737044]
    if datenum(Data.Date(k)) > 737044
    Data.Rate{k} = -0.329;
    elseif datenum(Data.Date(k)) < 733117
        Data.Rate{k} = 3.686
    elseif ismember(datenum(Data.Date(k)),n)
        Data.Rate{k} = Data{datestr(n(1)),3};
    else Data.Rate{k} = 'NA';
end
    end
end

[KL]

You probably do all this without any loop, I do not have access to your variables so I didn't test the code. But something like the following should work.

Data.Rate = repmat({'NA'},height(Data),1);
indx_a = datenum(Data.Date) > 737044;
indx_b = datenum(Data.Date) < 733117;
indx_c = ismember(datenum(Data.Date),733117:91:737044);
Data.Rate(indx_a) = -0.329;
Data.Rate(indx_b) = 3.686;
Data.Rate(indx_c) = Data{datestr(n(1)),3};