MATLAB: Filtering cell array table

filter cell array

I want to filter the cell array column according to 'DD. mmmm YYYY' so I get only the dates. How can I do that? thanks

'Thu, Feb-14-13'
  'Thu, Feb-07-13'
  'Thu, Jan-31-13'
  'Thu, Jan-24-13'
  'Thu, Jan-17-13'
  'Thu, Jan-10-13'
  'Thu, Jan-03-13'
  'Thu, Dec-27-12'
  'Thu, Dec-20-12'
  'Time'
  'Time'
  'Reg.'
  'Reg.'
  'UXS'
  'CXX'
  'UCC'
  'USDC'
  'USCCD'

Best Answer

Why did you delete your earlier question? I was busy posting an answer to it and it disappeared out from under me :(

Good thing I saved the answer:

   matchresult = regexp(YourCell, '^[a-zA-Z]{3},\s[a-zA-Z]{3}-\d{2}-\d{2}$');
   nomatch = cellfun(@isempty, matchresult);
   dateless = YourCell(nomatch);
   datelike = YourCell{~nomatch};

Caution: this will not accept single digit day of the month as being a date. But it will accept any three letters as potential weekday name or month name. It will also not accept spaces before or after the date. (Some of these are easy to change but I am sticking with the format that you presented.)

Related Solutions

MATLAB: Filtering problem of dates

As the warning suggests, try specifying the correct 'InputFormat' when you call datetime.

datelike = {'01/10/2017' ; '02/10/2017'};
dt = datetime(datelike)
Warning: Successfully read the date/time text using the format 'MM/dd/uuuu', but their
format is ambiguous and could also be 'dd/MM/uuuu'. Specify a format character vector
to avoid ambiguity. 
> In guessFormat (line 66)
  In datetime (line 612) 
dt = 
  2×1 datetime array
   10-Jan-2017
   10-Feb-2017

versus:

datelike = {'01/10/2017' ; '02/10/2017'};
dt = datetime(datelike, 'InputFormat', 'dd/MM/uuuu')
dt = 
  2×1 datetime array
   01-Oct-2017
   02-Oct-2017

-Cam

MATLAB: Time series data with missing dates to remap to calendar days vector

If you have R2014b or later, use tables and datetimes. For the specific question you've asked, simplest to use ismember and indexing:

>> Date = datetime({'01/01/2015';'01/14/2015';'05/30/2015'});
>> Value = [10; 5; 10];
>> TSomeDays = table(Date,Value)
TSomeDays =
       Date        Value
    ___________    _____
    01-Jan-2015    10   
    14-Jan-2015     5   
    30-May-2015    10   
>> Date = datetime(2015,1,(1:180)');
>> Value = zeros(size(Date));
>> TAllDays = table(Date,Value);
>> [~,i] = ismember(TSomeDays.Date,TAllDays.Date)
i =
     1
    14
   150
>> TAllDays.Value(i) = TSomeDays.Value
TAllDays =
       Date        Value
    ___________    _____
    01-Jan-2015    10   
    02-Jan-2015     0   
[snip]
    13-Jan-2015     0   
    14-Jan-2015     5   
    15-Jan-2015     0   
[snip]
    29-May-2015     0   
    30-May-2015    10   
    31-May-2015     0   
[snip]
>> Date = datetime(2015,1,(1:365)');
>> TAllDays = table(Date);

Another possibility is to use an outer join, which may be too heavy of a hammer for this specific question, but which provides capabilities that you may yet need. outerjoin fills missing values with NaN, but you can put zeros in their place:

>> TAllDays = outerjoin(TAllDays,TSomeDays,'Key','Date','MergeKeys',true)
TAllDays =
  365×2 table
       Date        Value
    ___________    _____
    01-Jan-2015     10  
    02-Jan-2015    NaN  
    03-Jan-2015    NaN  
[snip]
    13-Jan-2015    NaN  
    14-Jan-2015      5  
    15-Jan-2015    NaN  
[snip]
    29-May-2015    NaN  
    30-May-2015     10  
    31-May-2015    NaN  
[snip]
>> TAllDays.Value(isnan(TAllDays.Value)) = 0
TAllDays =
       Date        Value
    ___________    _____
    01-Jan-2015    10   
    02-Jan-2015     0   
[snip]
    13-Jan-2015     0   
    14-Jan-2015     5   
    15-Jan-2015     0   
[snip]
    29-May-2015     0   
    30-May-2015    10   
    31-May-2015     0   
[snip]

If you have R2016b, this is trivial using a timetable:

>> Date = datetime(2015,1,(1:365)');
>> TTAllDays = retime(TTSomeDays,Date,'FillWithConstant','Constant',0)
TTAllDays =
        Date       Value
    ___________    _____
    01-Jan-2015    10   
    02-Jan-2015     0   
[snip]
    13-Jan-2015     0   
    14-Jan-2015     5   
    15-Jan-2015     0   
[snip]
    29-May-2015     0   
    30-May-2015    10   
    31-May-2015     0   
[snip]

Best Answer

Related Solutions

MATLAB: Filtering problem of dates

MATLAB: Time series data with missing dates to remap to calendar days vector

Related Question