Extracting Table Data by Date

I have a table full of data that eventually will be made to be frequently updated. I am currently using datestr to display the date as the first column in every row of data. Snippit of my code:

todayDate = datestr(now,'mm/dd/yyyy');                          
datesvct = repmat(todayDate, size(dataTake,1), 1);                
dataTable = [ table(datesvct)  dataTable];

Is it possible to reference and then extract information from the table from only the past week or the past 30 days and display it into a new table?

Answer 1

[Peter Perkins]

Andrew, everything dpd said sounds right, but let me just start over. You're using date strings because they're easy to read. Fair enough. But they're not much good for calculations, you'd need date numbers for that.

This is exactly the reason why MATLAB since R2014b has the datetime date type, which combines the readability of date strings with the computations (and way more, in fact) of date numbers. So here's what I'd do in R2014b or later:

First cook up some data.

>> date = datetime - days(1:5:21)' + hours(randn(5,1));
>> x = randn(5,1);
>> y = randn(5,1);
>> data = table(date,x,y)
data = 
            date               x            y    
    ____________________    ________    _________
    03-Sep-2015 08:48:45     -1.3617       1.0391
    29-Aug-2015 08:13:50     0.45503      -1.1176
    24-Aug-2015 08:32:40    -0.84871       1.2607
    19-Aug-2015 07:53:05    -0.33489      0.66014
    14-Aug-2015 09:13:22     0.55278    -0.067866

Now find the data from the last seven days, and from the current month.

>> lastSevenDaysData = data(datetime('today') - data.date < days(7), :)
lastSevenDaysData = 
            date               x          y   
    ____________________    _______    _______
    03-Sep-2015 08:48:45    -1.3617     1.0391
    29-Aug-2015 08:13:50    0.45503    -1.1176
>> thisMonthsData = data(data.date.Month == month(datetime('today')), :)
thisMonthsData = 
            date               x         y   
    ____________________    _______    ______
    03-Sep-2015 08:48:45    -1.3617    1.0391

If you're using R2013b/R2014a, you can do the same thing, but you'll need to do something like

   data(floor(now) - datenum(data.date) < 7, :)

if you have strings in your table.

Also, my example assumes you're using unzoned datetimes, ignoring daylight saving time. If you want "real" calendar calculations, you'll want to use calendar durations into the mix, with something like

   caldays(between(data.date,datetime('today'),'days')) < 7

Hope this helps.

[Andrew Smith]

Thank you very much, this worked perfectly with my function.

Answer 2

[dpb]

Yes, but it'll be easiest if you add a column that stores the datetime number (using late release) or the date number (if datetime type doesn't exist) and doing the selection via it rather than the string representation.

[Andrew Smith]

Thanks for the quick response, I went with displaying the date number instead. Now that I have this would you have any idea how to extract only the row of information correlating with that number onto another table? If I have (datenum) 6 4 2 on one row and (different date num) 7 3 2 on another row, how would I only pick out row with the first datenum?

[dpb]

I'm at a little of a disadvantage as am limited to R2012b which predates both the table and datetime data types.

I presume the internal storage is still the date number despite the disply so you would simply use logical addressing on the column. If the storage is indeed the underlying value then logical addressing or ismember should work just as for any other numeric value.

Showing the precise code you used would help so at least can read the doc with a specific syntax; meanwhile mayhaps someone with a recent release will also chime in.

[Andrew Smith]

Thanks, I took a look into ismember but couldn't quite figure out how to fit it in here but it looks promising. Here is the specific code:

name = 'dataStream';
dataStream = (varargin{1});
dataTake = dataStream(end-9:end,:);
dataTable = array2table(dataTake);                        
todayDate = datestr(now,'mm/dd/yyyy');  
dateNumber = datenum(todayDate);
datesvct = repmat(dateNumber, size(dataTake,1), 1);                
dataTable = [ table(datesvct)  dataTable];
dataTake = table2cell(dataTable);

[dpb]

Well, I'm more confused than ever...the above is "traditional" date numbers, not the new datetime class...a few comments--

1. dateNumber as datenum(datestr(now)) is identical to just dateNumber=fix(now);
2. I don't grok the usefulness in duplicating that same value 10 times?? What do you have in mind by doing this?

As for the selection if I do just do what you've done similarly except with a set of actual date values instead of a constant and supply another set of corollary data to go with it as

>> d=floor(now+[-4:4].');  % a series of dates beginning 4 days ago
>> datestr(d)   % which specific dates are they?
ans =
30-Aug-2015
31-Aug-2015
01-Sep-2015
02-Sep-2015
03-Sep-2015
04-Sep-2015
05-Sep-2015
06-Sep-2015
07-Sep-2015
>> arry=[d rand(size(d))];  % make up the data to go with dates
>> ix=arry(:,1)>floor(now)  % logical index-retrieve all after today
ix =
   0
   0
   0
   0
   0
   1
   1
   1
   1
>> datestr(d(ix))  % see that above statement is really so...
ans =
04-Sep-2015
05-Sep-2015
06-Sep-2015
07-Sep-2015
>> dat=arry(ix,2) % and retrieve the data associated with those
dat =
  0.7412
  0.8547
  0.5317
  0.3931
>>

As example of ismember, presume want to return today only--other than the numerical test as above,

>> v=arry(ismember(arry(:,1),fix(now)),:)  % get today's result
v =
 1.0e+05 *
  7.3621    0.0000
>> datestr(v(1)), v(2)   % again, show that's what we really got
ans =
03-Sep-2015
ans =
  0.7317
>>

Use the above ideas with the techniques for addressing table datasets (with dot indexing I believe?) and you should find joy...

[Andrew Smith]

Thank you, sorry about that I am an intern with limited matlab experience so I think any confusion may just be from my lack of practice.