Selecting tables among 540 tables, based on a start and end date

Hello all,

I have an x.mat cell which includes over 540 tables for 540 climate stations. In every 540 tables, there is a column named data (data is a wrong variable name for date). I want to have a code that selects every table that has a specific period from 1989 to 2018 and saves them in a new cell named x_selected.

I want to save the table if the start (1989) and end time (2018) are satisfied in the tables even if other years in between doesn't exist in it.

the following examples describe what I want:

for example, if one table (station) has only data from 2000 to 2018 so it is not going to save in x_selected because it hasn't data from 1989 to 2018. But if a table (station) has data for 1989 and 2018 (and not in between these two years) I want to save it in x_selected.

if a table has data for 2000 to 2003 I don't want it because the start time isn't 1989 and the end time isn't 2018.

if a table has data only for 2015 I don't want it because the time range 1989-2018 is not considered.

if a table has data from 1989 to 2018 but missed some years (like 2015, 2017) I want to save it because start time and end time are satisfied.

the above year is for example and may be different in the tables I just write them for example, what's important is start time (1989) and end time(2018).

x.mat is attached.

Thank you so much.

[Adam Danz]

Hi Behzad Navidi,

This project of yours is really coming alive here in this forum :D.

How would you start to solve this?

[BN]

I'm so sorry. I'm not really professional in Matlab. I think it's take many years and I'm learning now and every function is new for me. unfortunately no one in my region knows Matlab to teach me. Before sending a question I search for the solution in previous related answered questions in Mathworks and google, sometimes it fixed and sometimes I haven't any idea about it. You right please pardon me. Thank you and other professionals for your time and generosity.

I don't know how to start. I want to define start_time and end_time like this:

start_time = datetime([1989 01 01]);%set start date
end_time = datetime([2018 12 31]);%set end date

then using for loop and if to find desire table

idx = (T.data = start_date) & (T.data = end_date);
x_selected = T(idx);

or I don't know if the solution needs for loof and if

 for idx = 1:numel(x)
    T = x{idx};
    if start_time = x.data.firs_row || ...
       end_time = x.data.end_row
       T=selected_x
    end
 end

[Adam Danz]

The first two blocks of your code are a good start and are similar to my approach in the answer section (I'm adding it now).

One difference in my version is that i'm pulling out the year rather than using 1/1/1989 - 12/1/2018. Both approaches would work, though.

Please take a few minutes to see how I'm using cellfun(). That's an alternative to using loops.

Answer 1

[Adam Danz]

Here's my thought process as I explore the data and solve the problem in parallel.

Please take time to go through each line and think about what each line is doing. The comments should help.

You'll see two interpretation of the "select" variable (one of them is commented-out). Choose whichever one fits what you need.

% Clear out workspace, load variable(s)
clear; close all
load('x.mat')

% what do these tables look like?
% show first few rows of 1st table
head(x{1}) 

% are the dates in datetime format?
% Test the first date in the first table
isdatetime(x{1}.data(1)) 

% Check for missing values in data column
any(cellfun(@(T)any(ismissing(T.data)),x)) % = 0 means none

% Identify start and stop years
yrBounds = [1989, 2018]; 

% Get the minimum and maximum years of each table. 
% Note that your dates are *not* sorted.
% This cellfun extracts the min value of each data column 
% from each table.  That produces a vector of datetime values.
% Then the year() function extracts the years.
startYear = year(cellfun(@(T)min(T.data),x));

% now get the max year value
endYear = year(cellfun(@(T)max(T.data),x));

% Identify which startYear and endYear satisfy our bounds.
% Use this line if you want tables whose dates span from 1989:2018
select = startYear==yrBounds(1) & endYear==yrBounds(2); 

% Use this line if you want tables whose dates span from 1989:2018
% but may have earlier or later years, too. 
% select = startYear<=yrBounds(1) & endYear>=yrBounds(2); 

% How many tables were selected?
sum(select)

% Extract the selected tables
x_selected = x(select);

% Check for accuracty. Here we'll select a table to look at 
% (table #n) and we'll list the unique years in the data column. 
n = 1; % which table to look at
unique(year(x_selected{n}.data))

[Adam Danz]

Here's a bonus lesson.

The rows of your tables are not in chronological order. That might not be a problem but if you wanted to resort the rows of each table according to the "data" (date) column here's what you can do.

% Are the dates in chronological order for each table?
% This line is just exploratory, in order to see which
% tables are out of order 
cellfun(@(T)all(diff(T.data)>0),x)  % 1 means in order

% Sort the rows of the tables according to date
xSort = cellfun(@(T){sortrows(T,'data')},x);

Now look at the unsorted table #2 and compare it with the sorted table #2.

x{2}
xSort{2}

[Adam Danz]

@Behzad Navidi, I left a comment for you in the discussion we were having in the comment section under Star Strider's answer that he just deleted. Just wanted you to know that if you're using the 2nd version of "select", you can delete or comment-out the 1st version above it.

[BN]

Thank you so much @Adam Danz.