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

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.

[Adam Danz]

Could you attach a mat file that contains the first 50-100 rows of your data in the table format you described?

[Adam Danz]

Hi Devendra,

Please reply here in the comment section unless you're proposing an answer to your own question.

Good news is that your table looks fine and you should be able to perform many types of statistics with these data. I can walk you through that but first clearly describe one of the simpler statistics you'd like to do with your data.

[Devendra Pal]

Hi Adam, I would like to do an hourly mean and std. As you know I have 3 year long data, so I would like to do a monthly average and std as well. thanks very much in advance.

Answer 1

[Adam Danz]

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.

[Devendra Pal]

Thanks Adam, I really appreciate your help. I am not much familiar with Matlab, But i did my best to understand your code, it looks really cool. I am using Matlab2017a, and i am getting the following error.

filecontent.SampleNum = str2double(filecontent.SampleNum); To assign to or create a variable in a table, the number of rows must match the height of the table. %% %%%%%%%%%%% For importing data i used following code,

For importing the data i used following code:
path = 'E:\Snow ambient air paper\DevCsv';
files = dir(fullfile(path, '*.csv'));
result = table(); 
for F = 1:numel(files)
 filecontent = readtable(fullfile(path, files(F).name)); 
 [~, filenumber] = fileparts(files(F).name);
 filecontent.file_label = repmat(str2double(filenumber), height(filecontent), 1);
 if F==1

         result = [result; filecontent];
   else
     result = [result; filecontent(2:end,:)];
   end
end

[Adam Danz]

You'll need to format your code above by using the { } button so I can read it.

The error you're getting is at the part of the code where we're converting string ('5') to numbers (5). As I recommended earlier, you should import your data again and this time make sure you're importing it correctly.

I had to use str2double() to clean up your code but you can skip that if you import it correctly in the first place.

How are you importing it? If you're importing it with the import tool, it would be helpful to see a screenshot so I can make suggestions.

If you're importing it using a function to read the file, it would be helpful to see those lines of code.

[Devendra Pal]

Thanks Again, As i wish to you inform i had more than 100 csv file, First i combined those fuile in single table its calles result. I am attaching here my Screenshots for code and a table for all files. %%%%%%% For importing the data i used following code:

path = 'E:\Snow ambient air paper\DevCsv'; files = dir(fullfile(path, '*.csv')); result = table(); for F = 1:numel(files) filecontent = readtable(fullfile(path, files(F).name)); [~, filenumber] = fileparts(files(F).name); filecontent.file_label = repmat(str2double(filenumber), height(filecontent), 1); if F==1 result = [result; filecontent]; else result = [result; filecontent(2:end,:)]; end end

[Adam Danz]

This is helpful. Read carefully the 'format' input to readtable(). https://www.mathworks.com/help/matlab/ref/readtable.html#btx_238-1-Format

Hopefully the format of each column is the same for all 100+ of your files. If that's the case, you need to specify the format for each column and enter that into readtable().

It will look something like this where you'll have one '%x' for each column.

T = readtable(filename,'Format','%d%d%D%f%f%f%s')
%d are for integers (the first 2 columns of your data)
%D are for datetime 
%f are for floating points (with decimal places)
%s are for strings.

You'll have to figure out which data type each column should be. For more info which format to use: https://www.mathworks.com/help/matlab/ref/textscan.html#btghhyz-1-formatSpec

Once you figure that out, run 1 file and look at the data to ensure it's all as expected. Then you can run that on all of you 100+ files and your data will be a lot cleaner. You also need to get rid of that first row and use the header names to name your table columns. You can learn how to do that:

doc readtable

If your 100+ files do not have the same format, first send an angry email to the person who put that data together. Secondly, you'll need to clean the data manually the way I showed you.

Answer 2

[Devendra Pal]

Kindly find the test file for month data.

thanks! Dev