How to exclude weekend data from an excel sheet in matlab?

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

[Cris LaPierre]

What data do you want to import? All of it, or just the data starting at column LU?

Answer 1

[Cris LaPierre]

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')

date = 1x29 table
    Var1      Friday        Saturday        Sunday         Monday         Tuesday       Wednesday      Thursday       Friday_1      Saturday_1      Sunday_1       Monday_1       Tuesday_1     Wednesday_1    Thursday_1      Friday_2      Saturday_2      Sunday_2       Monday_2       Tuesday_2     Wednesday_2    Thursday_2      Friday_3      Saturday_3      Sunday_3       Monday_3       Tuesday_3     Wednesday_3    Thursday_3 
    ____    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________    ___________

    NaN     29-Nov-2019    30-Nov-2019    01-Dec-2019    02-Dec-2019    03-Dec-2019    04-Dec-2019    05-Dec-2019    06-Dec-2019    07-Dec-2019    08-Dec-2019    09-Dec-2019    10-Dec-2019    11-Dec-2019    12-Dec-2019    13-Dec-2019    14-Dec-2019    15-Dec-2019    16-Dec-2019    17-Dec-2019    18-Dec-2019    19-Dec-2019    20-Dec-2019    21-Dec-2019    22-Dec-2019    23-Dec-2019    24-Dec-2019    25-Dec-2019    26-Dec-2019

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)

data = 24×21

0 0.9341 1.0094 1.0736 0.9012 1.1284 1.0168 1.0829 0.9724 1.0766 1.0128 0.7743 0.9598 1.1050 0.9852 1.1645 0.9788 0.9166 0.9575 1.1235 1.1464 1.0000 0.8248 1.1543 1.0398 0.7838 0.9758 0.9504 0.8540 0.9025 0.9159 0.8044 0.8713 0.8908 0.9135 0.7196 0.9013 0.9436 0.7565 0.9143 0.9394 1.0132 2.0000 0.7932 0.9745 1.0163 0.9159 0.9125 0.9779 0.8220 0.8976 0.8985 0.7508 0.7819 0.8934 0.7778 0.7211 0.9749 0.8635 0.8049 0.9028 0.8882 0.8607 3.0000 0.9748 1.0885 1.1798 1.0225 1.0928 1.0138 1.0277 0.9242 1.0897 0.9337 1.0133 0.9798 0.8928 0.8665 1.1013 0.9709 0.8032 0.8884 0.7949 0.7189 4.0000 1.0002 1.2505 1.2212 0.9712 1.1314 1.1292 1.1781 1.1738 1.1469 1.0099 1.0855 1.2097 1.0782 0.8261 1.2388 1.1608 0.8140 1.0770 0.8508 0.8625 5.0000 1.1205 1.2759 1.2986 1.0898 1.1145 1.2720 1.0537 1.0824 1.2248 0.8651 1.0265 1.1368 1.0795 0.9905 1.2062 1.2318 0.9706 1.0679 0.7458 0.8107 6.0000 1.0378 1.2910 1.2963 1.1198 1.2385 1.1667 1.1207 1.1392 1.3910 1.0048 1.0768 1.0865 1.0854 0.9929 1.3191 1.1290 0.8585 1.2803 0.8205 0.7501 7.0000 1.1816 1.5237 1.5302 1.1888 1.3894 1.1497 1.2741 1.3377 1.2905 1.1405 1.3379 1.2723 1.1997 1.1415 1.4247 1.2187 0.8810 1.2635 0.8024 0.9033 8.0000 1.0830 1.2756 1.2170 0.9862 1.1053 1.0227 1.1591 1.1884 1.2143 1.0012 1.0923 1.1172 1.0259 1.2185 1.2813 1.1588 0.9923 1.2249 0.7622 0.8531 9.0000 1.0256 1.3826 1.1281 1.0757 1.0944 0.7770 0.9656 1.1005 1.1732 1.0270 0.9659 1.0147 1.1271 1.3237 1.3492 1.0692 1.0405 1.4232 0.8947 0.8334

[Cris LaPierre]

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"]

data2 = 360x3 table
         Day            Date            data     
    _____________    ___________    _____________

    {'Tuesday'  }    01-Jan-2019    [1x24 double]
    {'Wednesday'}    02-Jan-2019    [1x24 double]
    {'Thursday' }    03-Jan-2019    [1x24 double]
    {'Friday'   }    04-Jan-2019    [1x24 double]
    {'Saturday' }    05-Jan-2019    [1x24 double]
    {'Sunday'   }    06-Jan-2019    [1x24 double]
    {'Monday'   }    07-Jan-2019    [1x24 double]
    {'Tuesday'  }    08-Jan-2019    [1x24 double]
    {'Wednesday'}    09-Jan-2019    [1x24 double]
    {'Thursday' }    10-Jan-2019    [1x24 double]
    {'Friday'   }    11-Jan-2019    [1x24 double]
    {'Saturday' }    12-Jan-2019    [1x24 double]
    {'Sunday'   }    13-Jan-2019    [1x24 double]
    {'Monday'   }    14-Jan-2019    [1x24 double]
    {'Tuesday'  }    15-Jan-2019    [1x24 double]
    {'Wednesday'}    16-Jan-2019    [1x24 double]

% 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')

monthMax = 12x3 table
    month_Date    GroupCount    fun1_data
    __________    __________    _________

     Jan-2019         23         1.9715  
     Feb-2019         20         2.1462  
     Mar-2019         21         1.9669  
     Apr-2019         22         1.5923  
     May-2019         23         1.4666  
     Jun-2019         20         1.1882  
     Jul-2019         23         1.4441  
     Aug-2019         22         1.4231  
     Sep-2019         21         1.6825  
     Oct-2019         23         1.8257  
     Nov-2019         21         2.0042  
     Dec-2019         19         2.0425  

I have only verified that Jan 2019 is correct, so use with caution.