How to extract rows by date?

say I have a table A

18-Dec-2015 42.3000   29.9000    7.3000
18-Dec-2015 37.3000   30.1000   13.4000
18-Dec-2015 39.1000   30.0300    6.5000
18-Dec-2015 42.3000   29.9000    7.3000
19-Dec-2015 37.3000   30.1000   13.4000
19-Dec-2015 39.1000   30.0300    6.5000
20-Dec-2015 42.3000   29.9000    7.3000
20-Dec-2015 39.1000   30.0300    6.5000
20-Dec-2015 37.3000   30.1000   13.4000

I have almost 3000 days to do. How could I use a loop to extract rows by time and do some calculation separately? Thanks in advance!!! I tried ismember and the following one but neither works.

for i = (:,1)
if A(i,1)=A(i+1,1)
C=A(i,:)
...

[Jan]

It would be useful to post some code, which creates the example data or to attach the variable as MAT file. This would clarify, which format the "table" has exactly.

What is the purpose of "for i = (:,1)"? "Neither works" is less useful than posting the error message.

[Iris Li]

Sorry it's my bad for I'm not familiar with asking a question yet. What I need to do is to separate C and P for each day each maturity, and then merge by the common strike price And then do calculations later.

date = {'20160419';'20160419';'20160419';'20160419';'20160419';'20160419';'20160418';'20160418';'20160418';'20160418'};
cp_flag = {'C';'C';'C';'P';'P';'P';'C';'C';'P';'P'};
maturity = [2;2;1;2;2;1;2;1;2;1];
strike_price = [40;30;20;40;30;20;40;30;40;30];
mid_value = [0.021; 0.022; 0.033; NaN; 0.025;0.026;0.027; 0.028; 0.039; 0.041];
T = table(date,cp_flag,maturity,strike_price,mid_value)

T =

10×5 table

       date       cp_flag    maturity    strike_price    mid_value
    __________    _______    ________    ____________    _________

    '20160419'    'C'        2           40              0.021    
    '20160419'    'C'        2           30              0.022    
    '20160419'    'C'        1           20              0.033    
    '20160419'    'P'        2           40              NaN    
    '20160419'    'P'        2           30              0.025    
    '20160419'    'P'        1           20              0.026    
    '20160418'    'C'        2           40              0.027    
    '20160418'    'C'        1           30              0.028    
    '20160418'    'P'        2           40              0.039    
    '20160418'    'P'        1           30              0.041

I need to get

T_new =

5×9 table

       date       cp_flag_A    maturity_A    strike_price_A    cp_flag_B    maturity_B    strike_price_B    mid_value_A    mid_value_B
    __________    _________    __________    ______________    _________    __________    ______________    ___________    ___________

    '20160419'    'C'          2             40                'P'          2             40                0.021            NaN      
    '20160419'    'C'          2             30                'P'          2             30                0.022          0.025      
    '20160419'    'C'          1             20                'P'          1             20                0.033          0.026      
    '20160418'    'C'          2             40                'P'          2             40                0.027          0.039      
    '20160418'    'C'          1             30                'P'          1             30                0.028          0.041

find the corresponding strike price when the abs(mid_value_A - mid_value_B) is smallest.

Answer 1

[Akira Agata]

It's not clear what you want to do, but, as Star-san mentioned, retime function would be your help. Let me start assuming your table A has 4 columns and the 1st column is stored as datetime, like:

>> A
A =
9×4 table
     Time       Var1    Var2     Var3
  __________    ____    _____    ____
  2015/12/18    42.3     29.9     7.3
  2015/12/18    37.3     30.1    13.4
  2015/12/18    39.1    30.03     6.5
  2015/12/18    42.3     29.9     7.3
  2015/12/19    37.3     30.1    13.4
  2015/12/19    39.1    30.03     6.5
  2015/12/20    42.3     29.9     7.3
  2015/12/20    39.1    30.03     6.5
  2015/12/20    37.3     30.1    13.4

To use retime function, first you should convert your table into "timetable" by using table2timetable function.

>> A = table2timetable(A);
>> A
A =
9×3 timetable
     Time       Var1    Var2     Var3
  __________    ____    _____    ____
  2015/12/18    42.3     29.9     7.3
  2015/12/18    37.3     30.1    13.4
  2015/12/18    39.1    30.03     6.5
  2015/12/18    42.3     29.9     7.3
  2015/12/19    37.3     30.1    13.4
  2015/12/19    39.1    30.03     6.5
  2015/12/20    42.3     29.9     7.3
  2015/12/20    39.1    30.03     6.5
  2015/12/20    37.3     30.1    13.4

Now you can use retime function to do some calculation separately for each day. For example, you can calculate daily average for each variable (Var1~Var3) in 1 line, like:

>> retime(A,'daily','mean')
ans =
3×3 timetable
     Time        Var1      Var2      Var3 
  __________    ______    ______    ______
  2015/12/18     40.25    29.983     8.625
  2015/12/19      38.2    30.065      9.95
  2015/12/20    39.567     30.01    9.0667

I hope my post somehow helps you !

[Iris Li]

Thank you for the patience! It's my bad for I'm not familiar with asking a question yet. What I need to do is to separate C and P for each day each maturity, and then merge by the common strike price And then do calculations later.

date = {'20160419';'20160419';'20160419';'20160419';'20160419';'20160419';'20160418';'20160418';'20160418';'20160418'};
cp_flag = {'C';'C';'C';'P';'P';'P';'C';'C';'P';'P'};
maturity = [2;2;1;2;2;1;2;1;2;1];
strike_price = [40;30;20;40;30;20;40;30;40;30];
mid_value = [0.021; 0.022; 0.033; NaN; 0.025;0.026;0.027; 0.028; 0.039; 0.041];
T = table(date,cp_flag,maturity,strike_price,mid_value)

T =

10×5 table

       date       cp_flag    maturity    strike_price    mid_value
    __________    _______    ________    ____________    _________

    '20160419'    'C'        2           40              0.021    
    '20160419'    'C'        2           30              0.022    
    '20160419'    'C'        1           20              0.033    
    '20160419'    'P'        2           40              NaN    
    '20160419'    'P'        2           30              0.025    
    '20160419'    'P'        1           20              0.026    
    '20160418'    'C'        2           40              0.027    
    '20160418'    'C'        1           30              0.028    
    '20160418'    'P'        2           40              0.039    
    '20160418'    'P'        1           30              0.041

I need to get

T_new =

5×9 table

       date       cp_flag_A    maturity_A    strike_price_A    cp_flag_B    maturity_B    strike_price_B    mid_value_A    mid_value_B
    __________    _________    __________    ______________    _________    __________    ______________    ___________    ___________

    '20160419'    'C'          2             40                'P'          2             40                0.021            NaN      
    '20160419'    'C'          2             30                'P'          2             30                0.022          0.025      
    '20160419'    'C'          1             20                'P'          1             20                0.033          0.026      
    '20160418'    'C'          2             40                'P'          2             40                0.027          0.039      
    '20160418'    'C'          1             30                'P'          1             30                0.028          0.041

find the corresponding strike price when the abs(mid_value_A - mid_value_B) is smallest.

[Akira Agata]

Thank you for the clarification!

OK. Then, the following code can do that task. In the following, minVal becomes the calculated minimum value of abs(mid_value_C - mid_value_P), and corrStrikePrice is the corresponding strike price.

% Calculate T_new
% (Note: The following 'T_new' is not exactly the same as your 'T_new')
C = T(strcmp(T.cp_flag,'C'),:);
P = T(strcmp(T.cp_flag,'P'),:);
T_new = innerjoin(C,P,'Keys',{'date','maturity','strike_price'});

% Find the corresponding strike price when the abs(mid_value_C - mid_value_P) is smallest.
[minVal,idx] = min(abs(T_new.mid_value_C - T_new.mid_value_P));
corrStrikePrice = T_new.strike_price(idx);

[Iris Li]

Thank you a thousand times!!!

[Peter Perkins]

Iris, you really want to turn Date into a datetime, not text, and turn cp_flag into categorical, not text. Your life will be much easier.

Answer 2

[Star Strider]

I have no idea what you are doing, so I am guessing here.

If you have R2016b or later, see if the retime (link) function will help you do what you want.