MATLAB: Calculating hourly averages, monthly averages and mean, median for long term data

@image analystkl

I have a year-long minute basis data in many csv Files. I used Matlab to combine in one table but my table wont allow me to do statistics, I have tried to convert the table to the matrix but I could nt able to do because my table content is number and date.Here i am attaching my sample Csv files which i combine in one table but i don't know how to filter header of each csv file and then how to calculate mean and other statistics.

Best Answer

Hi Devendra,
Here's an example how to calculate monthly mean with your sample data. But before we get to that example, there's some clean-up to do.
How did you import these data? Many of the columns in your 'filecontent' table are numerical but being stored as strings. For example, '5', '6', '7', instead of [5 6 7]. I suggest importing your data again and taking care of assigning the correct class for each column of data.
Another way to solve that problem is to clean it up after import but this is not the better option. Since I cannot import your data, I clean up some of it.
You've got a lot of clean-up to do with these data before you can start analysis. I've done some of the cleanup in this example (but not nearly all of it).
First, give meaningful names to the columns of your table. This will reduce mistakes during analysis and will make your code readable. This can (and should) be done during import but I've done it here:
% Give meaningful names to columns in the table
currentVarNames = filecontent.Properties.VariableNames; 
currentVarNames(1:3) = {'FileIdx', 'SampleNum', 'DateTime'}; 
filecontent.Properties.VariableNames = currentVarNames;
I assume your first row are headers but some of the values in the first row are numerical so be careful during import and be careful when you're cleaning up the dataset.
% Remove the first row (I assume they are headers)
filecontent(1,:) = [];
Here is where I converted some of your columns to numbers (from strings).
% Convert strings to numbers (other columns ignored)
filecontent.FileIdx = str2double(filecontent.FileIdx); 
filecontent.SampleNum = str2double(filecontent.SampleNum);
Here is where I convert your dates to "datetime objects" which are easier to work with in matlab.
% Convert date strings to datetime objects
filecontent.DateTime = datetime(filecontent.DateTime, 'format', 'yyyy/M/dd HH:mm:ss');
Now I convert you table to a timetable which allows us to use the retime() function later.
% Convert your table to a timetable
filecontentTT = table2timetable(filecontent);
Lastly, compute the mean for each month. Since some of the columns in your table are non-numerical, I've only selected variables 4-7 (which are horrible names but you'll clean that up). 'monthlyMeanTable' will be a table of unique months and the means for the 4 variables I selected.
% Compute the mean for each month for var4, var5, var6, var7
colNames = {'Var4', 'Var5', 'Var6', 'Var7'};
colIdx = ismember(filecontentTT.Properties.VariableNames, colNames); 
monthlyMeanTable = retime(filecontentTT(:,colIdx), 'monthly', 'mean');
Look at the means for 'var5'
monthlyMeanTable.var5
If you are using an earlier version of Matlab that does not have the retime() function, we can do this in a loop easily and I could show you how.
Let me know if you get stuck.
Related Question