How to separate weekends from weekdays from an Excel file in MATLAB?

Hi Everone,

I have an Excel file like this:

(the date is like a text file)

Column 1 Column 2 Column 3

2020-04-02-thursday 45 67

2020-04-03-friday 33 88

....

How can I seperate the weekends and weekdays and write them to two seperate files in MATLAB?

Also, how can I extract a range from these days? For example, extract the days that their column 2 has value above 60?

Also, another question, how can I say, multiply cells that have a value above 50 by 2 in columns2 and multiply cells that have a value betwenn 20 and 30 by 0.5 in column 2.

Thank you so much. I am a novice in MATLAB so I would really appreciate it if you could help me!

Answer 1

[dpb]

Well, ya' gotsa' start somewhere! We all were newbies once...give it a shot and see how far you get on your own first...

1. Import the data with readtable or since you do have dates, optionally readtimetable
2. See weekday combined with
3. logical indexing; one of the most powerful features in MATLAB and one must learn to use to be effective.

All of the above is pretty simple and straightforward but you'll learn MATLAB faster by trying than just taking code somebody else hands you.

The outline of the above would be something like

t=readtimetable('ExcelFile.xlsx');
isWeekEnd=beginsWith(t.Date,'S','ignorecase',1);    % logical vector if day starts with S|s 
tWE=t(isWeekEnd,:);                                 % the WE only table all columns
ix=t.Var2>50;                                       % logical vector Var2 > 50
t.Var2(ix)=2*t.Var2(ix);                            % multiply those by 2

As can see, it's all pretty obvious when see it...there are examples of all this syntax in the "getting started" section in the doc or examples for the various functions.

[dpb]

The above assumes that the day string is in the Excel file -- I didn't look at it, explicitly. If that were to be removed, then MATLAB would be able to import the data automagically as a datetime and then there's function weekday that will find day of week for any date.

If it is in that first column, MATLAB won't be able to parse the date field on its own and you'll have to convert it yourself after stripping of the day string -- otomh don't recall there is a formatting option to read it; although I think I recall maybe theres' one that will let you skip text--but not sure it will do anything except a fixed text string to interpret as a literal.

Hence, readtimetable will probably fail, use readtable instead.

[Wolfgang McCormack]

Thank you so much for this; I realllly appreciate it. I have a few troubles, when you say t.Date what does the Date mean?

the isweekend is defined because we want to search Saturdays?

What is ignore case doing here and 1?

So far, I was able to create a timetable in which the date and time are shown rather than string in excel files. Now, is there a solution for extracting the date from those timetables?

for example I want to extract May 25 12:00:00 to Oct 15 15:00:00. Then multiply the column next to these times by a value like 5. How should I do that? By extracting, I don't mean to write it to some other file. I just want them to stay where they are.

Also, how can multiply specific values next to times in a data by a number,

For example May 25 to Oct 15, from time 00:00 8:00:, the next column (value column) should be multiplied by 3 and then 8:00 to 16:00 by 5 and then 16:00 to 23:00 by 10. Now the values for hours will change for Oct 15 to Jan 1 but the hours remain the same.

Could you please help me with this as well?

[Wolfgang McCormack]

@dpb I had an initial idea of using time range within time range, but I guess that won't work because I want to create a clean excel out of that that includes the times steps.

So, i was thinking maybe there is some kind of if clause that might be able to solve my last question

[dpb]

The dot nomenclature refers to the variable of that name in the table, t. I just presumed using Date for the time column would be understood implicitly to be that.

The optional input named parameters are name,value pairs; 'ignorecase' is a logical flag whose values would be True|False --> 1|0. So, that tells it to be a case-insensitive match. Read the doc!!! :)

We ended up with the weekend days because Saturday and Sunday are the only two days of the week that begin with an 'S'. I did case-insensitive because I didn't know for sure what the file content might contain...

If, indeed, MATLAB was smart enough to actually read the dates in correctly into a timetable, well, then the time is actually a piece of the timetable and not an independent variable column as it would be for a regular table. I don't use timetables all that much; I forget just what they actually do call the time column.

However, for the Q? about selecting ranges, there's a builtin function specifically for the job -- timerange

[Wolfgang McCormack]

@dpb thank you so much for the info.

Regarding the time range, I actually looked it up but it includes the dates as well. I need to select the hours and then select dates using another timerange. But it's not working the way i want :D

[dpb]

Well, I'm not sure about how to do that with timerange as I've not used it enough to be fully familiar with the optional use of durations and the 'unitOfTime' time periods to be able to do this internally to it or not. The examples are of some help but do not always explore all the nuances that are possible.

However, there was this link to what appears to be the same question on the Answers page <How-to-filter-rows-in-a-timetable-based-on-a-range-between-08-00-and-16-00-for-everyday> that appears to address the Q?