MATLAB: 1 have 3 csv files of various sizes with timestamps (TS) in Column 1 of each csv. 2 csv files will always have same size matrix, but third csv TS is delayed which causes a smaller matrix. How to pad third csv with 0’s until TS3 = TS1 or TS2

MATLABmatrix manipulation

1 have 3 csv files of various sizes with timestamps (TS) in Column 1 of each csv. 2 csv files will always have same size matrix, but third csv TS is delayed which causes a smaller matrix. How do I pad third csv with 0's until TS3 = TS1 or TS2?

Best Answer

Assuming your data in 3 csv files are stored in timetable, say TT1 TT2 and TT3, you can pad the data in TT3 with 0s until TT3's timestamp is TT1's timestamp by using retime function, like:

% Sample data
TS = datetime({'2015-12-18 07:02:12';'2015-12-18 08:00:47';...
               '2015-12-18 09:01:37';'2015-12-18 10:03:10';...
               '2015-12-18 10:59:34'});
Data = [37.3;41.9;45.7;42.3;39.8];
TT1 = timetable(TS,Data);
TT3 = TT1(3:end,:);
% Pad a 3rd data with 0 until TT3.TS = TT1.TS
TT3 = retime(TT3,TT1.TS,'fillwithconstant','Constant',0);

Related Solutions

MATLAB: Using panel data, how can I create a new table with weekly price data based on a table with daily price data, for every stock

John, I'll assume you have the Financial Toolbox. I think what you're looking to do is to calculate mean price, by ticker and week, with the exemplar date for each week being the last business day. There are several ways you could do that. Since you already have a table, varfun is one easy way.

First cook up some fake data.

>> Date = datetime(2016,3,[22;23;24;28;29;21;22;23;24;28;29],'Format','eee dd-MMM-yyyy');
>> Ticker = categorical({'A'; 'A'; 'A'; 'A'; 'A'; 'B'; 'B'; 'B'; 'B'; 'B'; 'B'});
>> Price = rand(size(Ticker));
>> TS = table(Date,Ticker,Price)
TS =
         Date          Ticker     Price  
    _______________    ______    ________
    Tue 22-Mar-2016    A          0.13197
    Wed 23-Mar-2016    A          0.94205
    Thu 24-Mar-2016    A          0.95613
    Mon 28-Mar-2016    A          0.57521
    Tue 29-Mar-2016    A          0.05978
    Mon 21-Mar-2016    B          0.23478
    Tue 22-Mar-2016    B          0.35316
    Wed 23-Mar-2016    B          0.82119
    Thu 24-Mar-2016    B         0.015403
    Mon 28-Mar-2016    B         0.043024
    Tue 29-Mar-2016    B          0.16899

Now find the last business day in each week, by first finding the end of the week and then stepping back.

>> hol = holidays(datetime(2016,1,1),datetime(2016,12,31))
>> EOWDate = dateshift(TS.Date,'end','week');
>> TS.BusDate = busdate(EOWDate,'previous',hol);
>> TS.BusDate.Format = ['eee ' TS.BusDate.Format]
TS =
         Date          Ticker     Price           BusDate      
    _______________    ______    _______    ___________________
    Tue 22-Mar-2016    A         0.64912    Thu Thu 24-Mar-2016
    Wed 23-Mar-2016    A         0.73172    Thu Thu 24-Mar-2016
    Thu 24-Mar-2016    A         0.64775    Thu Thu 24-Mar-2016
    Mon 28-Mar-2016    A         0.45092    Fri Fri 01-Apr-2016
    Tue 29-Mar-2016    A         0.54701    Fri Fri 01-Apr-2016
    Mon 21-Mar-2016    B         0.29632    Thu Thu 24-Mar-2016
    Tue 22-Mar-2016    B         0.74469    Thu Thu 24-Mar-2016
    Wed 23-Mar-2016    B         0.18896    Thu Thu 24-Mar-2016
    Thu 24-Mar-2016    B         0.68678    Thu Thu 24-Mar-2016
    Mon 28-Mar-2016    B         0.18351    Fri Fri 01-Apr-2016
    Tue 29-Mar-2016    B         0.36848    Fri Fri 01-Apr-2016

Finally, apply mean to the prices, grouping by ticker and week.

>> varfun(@mean,TS,'GroupingVariables',{'Ticker' 'BusDate'},'InputVariables','Price')
ans =
    Ticker      BusDate      GroupCount    mean_Price
    ______    ___________    __________    __________
    A         24-Mar-2016    3             0.67672   
    A         01-Apr-2016    2             0.31749   
    B         24-Mar-2016    4             0.35613   
    B         01-Apr-2016    2             0.10601

MATLAB: Match datetime within 3 seconds from two tables of different sizes

This is a good question! Basically, if I understand correctly, you want to extract rows from table_2 whose timestamp matches that of table_1 within a tolerance; then you want those rows added to table_1 at corresponding, tolerance-matched timestamps.

At the moment, you cannot do this with one single function. But here's a workflow that could help you get there:

First of all, timetable is superior to table when dealing with timestamped data. You may use readtimetable (since R2019b) to directly import as a timetable, or use table2timetable to convert an existing table:

tt1 = table2timetable(table_1);
tt2 = table2timetable(table_2);

Now that your data are in timetable format, you can combine withtol, retime and synchronize to achieve what you need:

% Define your tolerance
tol = seconds(3);
% Extract the rows, using withtol, and corresponding time vector
% in tt1 that matches tt2's Time within the tolerance.
% This assumes the 'time' variable in your initial table is named 'Time'.
tMatch_tt1 = tt1(withtol(tt2.Time,tol), :).Time;
% Extract rows in tt2 that are within tolerance from tMatch_tt1
tt2_matched = tt2(withtol(tMatch_tt1, tol),:);
% Adjust tt2_matched's time to the matched tt1 time (i.e. tMatch_tt1). 
% % 'nearest' works especially there is no ambiguous match in your 
% according to your description.
%
% Alternatively, you can also directly assign the time here if you 
% are sure both are already sorted by time
%     tt2_matched.Time = tMatch_tt1;
tt2_matched = retime(tt2_matched, tMatch_tt1, 'nearest');
% Finally, use synchronize to join the matched rows in both timetables
tt_result = synchronize(tt1, tt2_matched);

As an example, your result will look like this with the following tt1 and tt2:

>> tt1
tt1 =
  11×1 timetable
            Time            Var1
    ____________________    ____
    18-Nov-2019 09:00:00      0 
    18-Nov-2019 09:30:00    0.5 
    18-Nov-2019 10:00:00      1 
    18-Nov-2019 10:30:00    1.5 
    18-Nov-2019 11:00:00      2 
    18-Nov-2019 11:30:00    2.5 
    18-Nov-2019 12:00:00      3 
    18-Nov-2019 12:30:00    3.5 
    18-Nov-2019 13:00:00      4 
    18-Nov-2019 13:30:00    4.5 
    18-Nov-2019 14:00:00      5 
    
>> tt2
tt2 =
  11×1 timetable
            Time            Var1
    ____________________    ____
    18-Nov-2019 09:00:05     10 
    18-Nov-2019 09:30:03     11 
    18-Nov-2019 10:00:03     12 
    18-Nov-2019 10:30:02     13 
    18-Nov-2019 11:00:05     14 
    18-Nov-2019 11:30:02     15 
    18-Nov-2019 12:00:01     16 
    18-Nov-2019 12:30:04     17 
    18-Nov-2019 13:00:02     18 
    18-Nov-2019 13:30:02     19 
    18-Nov-2019 14:00:03     20 
    
>> tt_result =
  11×2 timetable
            Time            Var1_tt1    Var1_tt2_matched
    ____________________    ________    ________________
    18-Nov-2019 09:00:00        0             NaN       
    18-Nov-2019 09:30:00      0.5              11       
    18-Nov-2019 10:00:00        1              12       
    18-Nov-2019 10:30:00      1.5              13       
    18-Nov-2019 11:00:00        2             NaN       
    18-Nov-2019 11:30:00      2.5              15       
    18-Nov-2019 12:00:00        3              16       
    18-Nov-2019 12:30:00      3.5             NaN       
    18-Nov-2019 13:00:00        4              18       
    18-Nov-2019 13:30:00      4.5              19       
    18-Nov-2019 14:00:00        5              20

Best Answer

Related Solutions

MATLAB: Using panel data, how can I create a new table with weekly price data based on a table with daily price data, for every stock

MATLAB: Match datetime within 3 seconds from two tables of different sizes

Related Question