How to exclude weekend data from an excel sheet in matlab?
3 views (last 30 days)
Show older comments
Youssef Chehade
on 24 Feb 2021
Commented: Cris LaPierre
on 24 Feb 2021
I have a data set on excel which on the rows is the hours of the day, and the columns are the days of year. I am doing a calculation of the highest values for each month, but I have to exclude the weekend days (Saturday & Sunday), any thoughts of how to do it using matlab?
I attached the excel sheet to make the point clearer
1 Comment
Cris LaPierre
on 24 Feb 2021
What data do you want to import? All of it, or just the data starting at column LU?
Accepted Answer
Cris LaPierre
on 24 Feb 2021
You have mixed data in your columns, so the way I would do this is as follows.
Use readtable to load the first two rows. I'm going to rely on the dates rather than the day names to determine weekends. I use the isweekend function. You could probably do something with the day function as well.
To save time, I'm just not going to import columns that corerspond to weekends using SelectedVariableNames option. I'm assuming you just want data from column LU to the right.
% Load just the dates
date = readtable("DH_Small.xlsx","Range",'LU1:MW2')
ind = [false isweekend(date{1,2:end})];
% Create import options and remove weekend columns from import
opts = detectImportOptions("DH_Small.xlsx","Range",'LU3');
opts.SelectedVariableNames(ind) = [];
% Import data
data = readmatrix("DH_Small.xlsx",opts)
1 Comment
Cris LaPierre
on 24 Feb 2021
Now if I were going to do it, I'd transpose the data in you excel file so that columns are hours of the day and rows are days in the year. This code will find the max of each month.
% I added the trasposed data to sheet2
data2 = readtable("DH_Small.xlsx",'Sheet',"Sheet2");
% Combine hour variables to a vector
data2 = mergevars(data2,[3:26]);
data2.Properties.VariableNames = ["Day","Date","data"]
% Code is simpler if I delete after importing
data2(isweekend(data2.Date),:) = [];
% find the overall max for each month
monthMax = groupsummary(data2(:,2:end),"Date","month",@(x) max(x,[],'all'),'data')
I have only verified that Jan 2019 is correct, so use with caution.
More Answers (0)
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!