Adding a new variable called 'Count', which Indexes or counts the occurrence of each stock_code based on their dates

Hi All, I need some guidance on adding incremental Count value to each stock_codes. I have a table with variables, Stock_code, date, close, low, high columns. I need help on, For example., Stocks: 200 Stocks totally, No.of Years: 1year(270 days, if we neglect holidays and suspension period etc.,). 1stock would have had 270 days, next stock might be having 200 days, 3rd like 230 days etc., So i need to add a variable called count, and it should have values like 1,2,3,4,5...270 for stock1, Repeat for Stock2 like 1,2,3,4,5...200, Stock3 like 1,2,3,4,5...230. Kindly advise how to achieve this.

[Joshua]

Do you have any code written? What are you trying to do with the variable "count"? Is it going to be a column vector of numbers (say from 1 to 270) that will be displayed as a column of the table? If so you could try:

count=[1:270]'

And then add count into the table as another column.

[Lakshmi Gandhi]

Hi Joshua, Thanks for your response!Each stock's count value might be different, not exactly 270 for all stocks. I just need to count number of times each stock is repeating and then do some lag and moving average calculations.

[Andrei Bobrov]

Please attach small example your data here

[Lakshmi Gandhi]

Hi Andrei, Could you able to view the image that I have attached? Left side data('HAVE', highlighted in Red) is what I have, I need a new variable named 'Count' as you can see in the right side('WANT',highlighted in Green) and the values like how it is in the example.

[Lakshmi Gandhi]

Answer 1

[Andrei Bobrov]

let T - your table.

[~,~,ii] = unique(T.symbol);
s = accumarray(ii,1);
idx = cumsum(s);
c = ones(idx(end),1);
c(idx(1:end-1)+1) = 1-s(1:end-1);
T.Count = cumsum(c);

[Lakshmi Gandhi]

Thanks much Andrei! Your code is working fine. I am very new to MATLAB, migrating from SAS. This community is providing such a great help. Kudos!!! But on the other side, I couldn't able to fully understand how the code is working, I am exploring line by line.

[Lakshmi Gandhi]

Hi Andrei, Is there any document or manual that I can go through to know more about Matlab programming? Like, Programming guide or manual for all functions and procedures? I couldn't able to find any article online, if I search using keywords. Kindly guide me.

[Andrei Bobrov]

The basic guide to using MATLAB is Help to your version of the program and this forum.

[Lakshmi Gandhi]

Thanks much!!!

Answer 2

[Peter Perkins]

If you are using R2016b or later, you are almost certainly better off using a timetable, since those already have methods for lagging, synchronizing, aggregating, and so on. Prior to 16b, use varfun with 'GroupingVariables'. Andrei's code would be a one-liner. Since all you're doing here is assigning a monotonic index withing each group, using varfun seems a little like overkill, but I suspect once you do something more complicated, you'll appreciate it's power.

>> t = table({'a';'a';;'b';'b';'b'},datetime(2017,3,[21;22;22;23;24]),randn(5,1),'VariableNames',{'Symbol','Date','X'})
t =
  5×3 table
    Symbol       Date           X    
    ______    ___________    ________
     'a'      21-Mar-2017     0.32519
     'a'      22-Mar-2017    -0.75493
     'b'      22-Mar-2017      1.3703
     'b'      23-Mar-2017     -1.7115
     'b'      24-Mar-2017    -0.10224
>> tCount = varfun(@(x)(1:length(x))', t, 'GroupingVariables','Symbol','InputVariables','X');
>> t.Count = tCount.Fun_X
t =
  5×4 table
    Symbol       Date           X        Count
    ______    ___________    ________    _____
     'a'      21-Mar-2017     0.32519      1  
     'a'      22-Mar-2017    -0.75493      2  
     'b'      22-Mar-2017      1.3703      1  
     'b'      23-Mar-2017     -1.7115      2  
     'b'      24-Mar-2017    -0.10224      3

[Andrei Bobrov]

+1

[Lakshmi Gandhi]

Thanks much Peter!!! Quite interesting method!!! I will apply this concept and try too and I could able to access some document also for varfun online.