operation between timetables with different index

What i want to do is to subtract the first five elements of a timetable with just one another element of a different timetable with different indexes. The code below is kind of expensive as i want to apply this subtraction to a large dataset. Do you have any suggestion? thank you

T.Vull(1:5)-AvWe.Valp(1);

[dpb]

What makes you think the above is "expensive"?

Other than there is no assignment of the results anywhere so all you'll get is the default ans variable for the result.

You could create a temporary of the constant, but that's done by the JIT optimizer behind the scenes anyway, and if you don't need that particular value for something else later on, there's no point in explicitly creating it.

[Attilio Pittelli]

It is expensive, as i've to do it for more than 100 times without a loop.

[dpb]

Need more background on the use than this for it to make any sense at all...that's just a single operation but it has no point in isolation to do so if you don't do something with the result.

[Attilio Pittelli]

the 1:5 stands for one 'week' period and after i made the array with the subtraction that i pointed up in the post before, of course i need to made statistical calculation on that

[dpb]

Don't try to be so terse and assume we have any idea whatsoever of what you have -- we can't see your terminal and we don't know anything at all about what you're trying to do.

Explain the problem you're trying to solve and show us the data structure you're working with.

Nothing you've said so far indicates any sizable problem at all; if you have time-related data and need to process it in some manner, then using timetable and retime may be the trick...there are functions to get working weekdays, etc., etc., etc., and the isbetween facility to address subsections of timetables -- but we can't give advice of any depth unless we can understand the actual problem to be solved.

Or, rowfun and grouping variables are extremely powerful -- there are quite a number of my Answers here that use it to solve complicated problems quite easily -- although I've not thought to keep a link to stuff I've posted and the subject lines won't necessarily give any hint about rowfun being the silver bullet.

[Attilio Pittelli]

What i need to do is to divide or create subset of a timetable, that is composed by daily data, into weeks

[dpb]

Well, as said, that's a perfect application for either retime or rowfun, depending still on details you're holding too close to your chest for us to be able to do anything with.

But, I'm willing to bet it won't be much computationally once finally get to what the real problem actually is and the data table.

And it's a common misconception that it is necessary to create either new tables or files or otherwise physically separate out data by whatever grouping is desired in order to operate on the pieces independently. That is rarely actually needed to be done.

[Attilio Pittelli]

Update:

w = week(T.Date);
w_1 = week(AvWe.Date)
T.Vull(w) - AvWe(w_1)

still not working

[Attilio Pittelli]

Provided Data:

Time series (n, 6) that is T

T.Vull is the 6th column

AvWe(m,1) with m << n

[dpb]

We can't debug what we can't see...attach a .mat file with the data table(s) and explain FULLY what the result wanted is.

"Not working" is a completely useless description -- we need the actual line of code and the complete error message in context to even have a chance -- and that would include every bit of the red text from one prompt to the next.

Being coy doesn't help; it just wastes both your time and ours...

[dpb]

The above is so; "help us help you!" by providing the data.

BUT, week() returns the yearly week number of the datetime vectors; it is NOT an indexing variable.

It could make a reasonable grouping variable, but

TT2 = retime(TT,'weekly',@yourFunctionHandle);

has a good chance of being able to be all the top level code you need once there's a definite description of what your function really needs to do.

[Attilio Pittelli]

I've already tried to use "retime", but the problem is with the function, that has to do the subtraction of the each element of the week with the corrisponding average element. I think the more easier approach would be to create a subset of data by week and then subtract the corrisponding average element of the week, but i'm still stucked in it

[dpb]

ONE LAST TIME!!!

Attach a sample of the data tables as .mat file(s) and give us a working definition of what the function is supposed to do. We can't code what we don't have a precise definition for.

I'm sure it can be done but not by a crystal ball...

It can take some ingenuity to write a function, but without a clear definition of PRECISELY what the inputs and expected output(s) are it's a fool's errand.

I'm willing to help (these challenges are kinda' fun and my entertainment), but can only work with what information am provided, and what has been given so far simply isn't sufficient to the task.

[dpb]

If, indeed, there are data needed from two separate timetables, it may well behoove to synchronize them first.

But, again, we simply are too much in the dark as to what you're trying to do to know what the best approach would be...

[Attilio Pittelli]

Sorry for the waiting,

here comes the code and a sample of data

clear;
ticker = readtable('SPCE (2).csv');
open = ticker.Open;
close = ticker.Close;
high = ticker.High;
low = ticker.Low;
volume = ticker.Volume;
T = table2timetable(ticker);
AvWe = retime(T,'weekly','mean');
%this below is the problem
w = discretize(ticker.Date,'day');
T(w,"Volume");

[dpb]

OK, that's a start; at least have data to work with -- now, what is the definition of the needed functionality in order to be able to know what to do next?

Remember, we know absolutely nothing but what you tell us -- only you know what you're trying to accomplish.

ADDENDUM:

However, I'd wonder if the real variable of interest for stock data would be the weekly average??? May be, I don't know, just seems like a peculiar choice to me, but again see the above...

[Attilio Pittelli]

i need to subtract the elements of the timetable to the corrisponding weekly mean (Av.We)

example:

T.('first week',Volume) - AvWe('first week') %this needs to be done for all the values and i want to store the results in an array or timetable

[dpb]

Well, maybe that's what the early request meant, but it wasn't easy to tell that... :) Language is undoubtedly part of the problem...but, that's a trivial operation although you need a grouping variable over week and year to do it. And, retime on its own isn't the tool; this is a job for rowfun -- but, to do it as neatly as would like, also takes an m-file function; not possible to rearrange the output as would like in an anonymous function.

It's still not too bad and is quite concise, but does, I'll grant, take some expertise in using MATLAB more than pure novice level... :)

Answer 1

[dpb]

tTicker=readtable('SPCE.csv');                                  % read the data file
tTicker.Yr=year(tTicker.Date;tTicker.Wk=week(tTicker.Date);     % create grouping variables

So far, leaves us with

> [head(tTicker);tail(tTicker)]
ans =
  16×9 table
       Date        Open      High      Low      Close    AdjClose      Volume       Yr     Wk
    __________    ______    ______    ______    _____    ________    __________    ____    __
    2020-07-06     16.58     17.09     16.05    16.95     16.95      1.3902e+07    2020    28
    2020-07-07     16.63     16.84    16.151    16.55     16.55       8.204e+06    2020    28
    2020-07-08     16.61     16.82      16.2    16.79     16.79      8.7226e+06    2020    28
    2020-07-09      17.1     19.49     17.08    19.46     19.46      5.1498e+07    2020    28
    2020-07-10     19.32     19.98     18.27    19.25     19.25      2.7769e+07    2020    28
    2020-07-13     19.55     20.99    17.712     18.1      18.1      3.6018e+07    2020    29
    2020-07-14    18.111     18.61     17.05    18.43     18.43      1.5803e+07    2020    29
    2020-07-15     18.97     19.14     18.06    18.55     18.55       1.165e+07    2020    29
    2021-06-23     38.96     41.65    38.751    40.82     40.82      2.0806e+07    2021    26
    2021-06-24      41.1     41.58     39.26    40.26     40.26      1.3388e+07    2021    26
    2021-06-25     47.81     57.29     45.87    55.91     55.91      2.6135e+08    2021    26
    2021-06-28     57.15     57.51     52.48    54.84     54.84      1.0957e+08    2021    27
    2021-06-29      52.3      53.6     45.97    47.02     47.02      7.4309e+07    2021    27
    2021-06-30     44.42    47.981    43.081       46        46      6.4212e+07    2021    27
    2021-07-01        46     46.43     43.19    43.19     43.19      2.7077e+07    2021    27
    2021-07-02     53.23      54.4     44.04    44.94     44.94      1.3545e+08    2021    27
>> 

Now write function to return a variable array minus mean of the array by column that will return a variable for each input column...

function varargout=fnAP(x)
% computes x-mean(x) for input array x
% returns size(x,2) outputs each as a column vector

  varargout=mat2cell(x-mean(x),size(x,1),ones(1,size(x,2)));
end

Now for the magic... :)

tVal=rowfun(@fnAP,tTicker,'InputVariables',{'Open','High'}, ...
                          'GroupingVariables',{'Yr','Wk'}, ...
                          'SeparateInputs',0,'NumOutputs',2, ...
                          'OutputVariableNames',{'Open-Mean','High-Mean'});

Let's look at what this bought us---

>> [head(tVal);tail(tVal)]
ans =
  16×5 table
     Yr     Wk    GroupCount    Open-Mean    High-Mean
    ____    __    __________    _________    _________
    2020    28        5           -0.668       -0.954 
    2020    28        5           -0.618       -1.204 
    2020    28        5           -0.638       -1.224 
    2020    28        5           -0.148        1.446 
    2020    28        5            2.072        1.936 
    2020    29        5          -0.2262      -0.1536 
    2020    29        5          -1.6652      -2.5336 
    2020    29        5          -0.8062      -2.0036 
    2021    26        5           -1.032       -1.754 
    2021    26        5            1.108       -1.824 
    2021    26        5            7.818       13.886 
    2021    27        5             6.53       5.5258 
    2021    27        5             1.68       1.6158 
    2021    27        5             -6.2      -4.0032 
    2021    27        5            -4.62      -5.5542 
    2021    27        5             2.61       2.4158 
>> 

I just did two variables to demonstrate; you can use whichever variables you want as the 'InputVariables' setting the 'NumOutputs' to match as well as the 'OutputVariableNames'

These can all be variables as well and do something like use

>> tTicker.Properties.VariableNames
ans =
  1×9 cell array
    {'Date'}    {'Open'}    {'High'}    {'Low'}    {'Close'}    {'AdjClose'}    {'Volume'}    {'Yr'}    {'Wk'}
>> 

from which could say

inVars=tTicker.Properties.VariableNames(2:7);
outVars=outVars=strcat(inVars,'-Mean');
numOut=numel(outVars);
tVal=rowfun(@fnAP,tTicker,'InputVariables',inVars, ...
                          'GroupingVariables',{'Yr','Wk'}, ...
                          'SeparateInputs',0, ...
                          'NumOutputs',numOut, ...
                          'OutputVariableNames',outVars);

Well, I was so close already,

>> [head(tVal);tail(tVal)]
ans =
  16×9 table
     Yr     Wk    GroupCount    Open-Mean    High-Mean    Low-Mean    Close-Mean    AdjClose-Mean    Volume-Mean
    ____    __    __________    _________    _________    ________    __________    _____________    ___________
    2020    28        5           -0.668       -0.954     -0.7002        -0.85          -0.85        -8.1175e+06
    2020    28        5           -0.618       -1.204     -0.5992        -1.25          -1.25        -1.3815e+07
    2020    28        5           -0.638       -1.224     -0.5502        -1.01          -1.01        -1.3296e+07
    2020    28        5           -0.148        1.446      0.3298         1.66           1.66         2.9479e+07
    2020    28        5            2.072        1.936      1.5198         1.45           1.45           5.75e+06
    2020    29        5          -0.2262      -0.1536     -1.1184        -1.94          -1.94         1.8498e+06
    2020    29        5          -1.6652      -2.5336     -1.7804        -1.61          -1.61        -1.8365e+07
    2020    29        5          -0.8062      -2.0036     -0.7704        -1.49          -1.49        -2.2518e+07
    2021    26        5           -1.032       -1.754     -0.1152       -1.666         -1.666        -4.6083e+07
    2021    26        5            1.108       -1.824      0.3938       -2.226         -2.226          -5.35e+07
    2021    26        5            7.818       13.886      7.0038       13.424         13.424         1.9446e+08
    2021    27        5             6.53       5.5258      6.7278        7.642          7.642          2.745e+07
    2021    27        5             1.68       1.6158      0.2178       -0.178         -0.178         -7.816e+06
    2021    27        5             -6.2      -4.0032     -2.6712       -1.198         -1.198        -1.7913e+07
    2021    27        5            -4.62      -5.5542     -2.5622       -4.008         -4.008        -5.5048e+07
    2021    27        5             2.61       2.4158     -1.7122       -2.258         -2.258         5.3327e+07
>> 

You can then keep the new table or pick the variables from it you want/need and append them to the tTicker table or whatever at will.

NB:

>> height(tVal)
ans =
   252
>> height(tTicker)
ans =
   252
>> 

so the two match up, row for row.

So, as noted, going back to the original posting, it's not at all an "expensive" calculation and rowfun is magical in what you can do...you just have to be somewhat creative in thinking about what the function definition needs to be.

MOST of the time, one can write what one wants in a simple anonymous function; your needs were a little more exotic. If one were satisfied to just get an array back, then you can, in fact, do this in a one-liner using an anonymous function --

>> tValAnon=rowfun(@(v)(v-mean(v)),tTicker,'InputVariables',{'Open','High'},'GroupingVariables',{'Yr','Wk'},'SeparateInputs',0,'OutputVariableNames','Variables-Week Mean');
>> head(tValAnon)
ans =
  8×4 table
     Yr     Wk    GroupCount    Variables-Week Mean
    ____    __    __________    ___________________
    2020    28        5          -0.668     -0.954 
    2020    28        5          -0.618     -1.204 
    2020    28        5          -0.638     -1.224 
    2020    28        5          -0.148      1.446 
    2020    28        5           2.072      1.936 
    2020    29        5         -0.2262    -0.1536 
    2020    29        5         -1.6652    -2.5336 
    2020    29        5         -0.8062    -2.0036 
>> 

This, of course, works, and you can produce the same output from it by extracting the columns from the one composite 2D array; doing it that way means have to do that every time run it; as opposed to writing the explicit function once and being done with it.

[dpb]

ADDENDUM:

The real key here is to recognize that you can compute the means of the groups on the fly by group and do the calculation by group while doing so. That's what removes the hurdle you had initially about having two tables of disparate sizes and trying to match them up.

It's possible to do that, of course, but it's much more straightforward to just do it all at once....if you need the weekly means for some other purpose, you can just compute them separately, or you could, even, include the mean as the last element of the output array by appending it to the differences. That would make addressing the data by row problematic in having to separate out every sixth row as being different, so would not recommend doing it, but it is possible.

[Attilio Pittelli]

First of all i would like to say thank you! and second i want to apologize. Your help was very useful! Thank you again

[dpb]

Well, that's ok...glad to be able to help and teach -- but, hopefully a lesson also learned is to be much more forthright from the opening salvo in providing sufficient information so folks have the needed information with which to be able to help. I could have made presumptions, but then if they don't work either you go away disappointed and convinced MATLAB can't do something or folks aren't wanting to be helpful or the like or it takes a continuing conversation to extract pieces of the puzzle one by one...

If the above resolves your problem, go ahead and Accept the answer so at least others know it has been done if for no other reason...

[Peter Perkins]

The Sporting News reports that dpb has just been unanimously voted into the Rowfun Hall Of Fame on the first ballot.

[dpb]

chuckles...

[dpb]

ADDENDUM:

Indeed, as noted above and that I have commented on numerous times before, rowfun is truly almost magical if one "thinks out of the box" on what the function definition can be besides just the normal run-of-the-mill @mean, etc., etc., ...

This one isn't so bad excepting @Attilio Pittelli got off on the wrong foot by computing his means first independently and then trying to match those up with the proper elements does get more complicated.

It's entertainment for the old fogey retired consulting guy to keep a hand in... :)