Looping and average through column values of a CSV

Hello All, i am relatively new to matlab so dont understand much.

I have a csv file with timeseries data taken every 6 seconds on a single day for 7 different parameters (all paramters are present in the csv as well).

I want to generate a method using for looping by which i can generate averages at every 1min, 5 mins, 1hours etc and plot those points as well.

My data loops something like this.

date & time          x  y  z  xa  xb  xc
02/05/2016 11:02:01 AM  101.3  42.8  22.6  102.3  95.5  57.9
02/05/2016 11:02:07 AM  99.8  49.7  22.1  142.9  103.4  61.6
02/05/2016 11:02:13 AM  1568.8  62.8  25.5  11407.1  2017  259.2
02/05/2016 11:02:19 AM  453.6  158  35.5  941.3  506.1  208.1
02/05/2016 11:02:25 AM  144.3  50.5  22.5  273.5  161.5  69.1
02/05/2016 11:02:31 AM  155.7  49.6  22.1  434.1  166.9  71.1
02/05/2016 11:02:37 AM  1265.4  51.5  35.9  4925.7  1565.9  188.5
02/05/2016 11:02:43 AM  249.3  111.4  28.5  281.7  246.4  145.8
02/05/2016 11:02:49 AM  379.6  76.2  24.9  888.9  429  125.9
02/05/2016 11:02:55 AM  197.9  42.5  23.3  499.8  204.3  66.9
02/05/2016 11:03:01 AM  174.1  84.6  58.9  234.6  178.8  103.1
02/05/2016 11:03:07 AM  153  81.5  50.8  441.5  157  100.8
02/05/2016 11:03:13 AM  154.3  72.7  36.8  228  153.7  88.5
02/05/2016 11:03:19 AM  91.8  49.6  23.2  91.8  90.8  64.9
02/05/2016 11:03:25 AM  117.7  51.7  24.2  127.7  113  66.4
02/05/2016 11:03:31 AM  115.8  57.2  23.6  137.9  120.1  77.3
02/05/2016 11:03:37 AM  119  53.6  22.6  319.8  124.9  71.8
02/05/2016 11:03:43 AM  85.2  47.6  24.7  90.3  83.8  59.2
02/05/2016 11:03:49 AM  122.2  53.3  24.3  165.3  126.4  73.8
02/05/2016 11:03:55 AM  174.4  71.9  26.7  269.8  180.4  95.1
02/05/2016 11:04:01 AM  587.2  44.5  25.7  2079  691.7  127.3
02/05/2016 11:04:07 AM  109.1  51.7  27.5  184.9  119.9  65.9
02/05/2016 11:04:13 AM  86.4  50.2  24.9  129.2  92.6  60.7

the data extends to nearly 2000 entries. Check the attached image for a proper format.

Can anyone help me out? thank you.

[Andrei Bobrov]

Please attach your data as csv-file.

[Dhwanilnath Gharekhan]

Here is the data. is it possible to generate from this??

Answer 1

[Guillaume]

Probably the easiest is to import your data into a table with readtable. Without seeing your file, it's hard to tell you what the syntax would be but it's likely that

data = readtable('yourfile.csv');

is all that is required. If the first column is not automatically read as datetime, you can then convert it to datetime:

data.nameofcolumn1 = datetime(data.nameofcolumn1, 'InputFormat', 'dd/MM/yyyy hh:mm:ss a');

After that you have two options:

Option 1. Stay with tables and use varfun to compute your stats. Create new columns for grouping your data using discretize. e.g. for grouping by 5 minutes:

data.group5min = discretize(data.nameofcolumn1, minutes(5));

Then varfun to compute the mean:

mean5minutes = varfun(@mean, data, 'InputVariables', {'x', 'y', 'z'}, 'GroupingVariable', 'group5min');

Option 2. Convert to timetable and retime your data.e.g, for hourly:

tdata = table2timetable(data);
thourly = retime(tdata, 'hourly', 'mean');

For some reason, retime does not let you specify arbitrary intervals such as 5 minutes like discretize does. So, for the 5 minutes case, you'd have to create your 5 minutes resampling array from the start and end time of your timetable. So, option 1 would probably be easier.

[Dhwanilnath Gharekhan]

Hello Guillaume,

i tried both methods you mentioned somehow, neither of them are working with my csv. the discretize function pops an error. I am atatching my csv, see if it helps.

[Guillaume]

The problem is certainly not with discretize, it has to do with something beforehand. Probably you haven't converted the first column to datetime. The following works for me with your example file:

data = readtable('Data_test.csv');
data.date_Time = datetime(data.date_Time, 'InputFormat', 'dd-MM-yyyy HH:mm');
data.group5min = discretize(data.date_Time, minutes(5));
mean5minutes = varfun(@mean, data, 'InputVariables', 2:6, 'GroupingVariables', 'group5min')

[Dhwanilnath Gharekhan]

No luck, it still keeps mentioning, cannot run at the discretize phase.

"Error using discretize Expected input number 1, x, to be one of these types:

numeric, logical

Instead its type was datetime.

Error in discretize (line 57) validateattributes(x, {'numeric','logical'}, {'real'}, funcname, 'x', 1)

Error in discretize_format (line 8) data.group5min = discretize(data.date_Time, minutes(5));"

[Andrei Bobrov]

+1

[Dhwanilnath Gharekhan]

add +1 where??

[Guillaume]

Alright, the support for datetime in discretize was added in R2016b. For earlier versions, you can transform your datetime column into a duration array by subtracting the first time. Then convert that duration into minutes or hours or whatever unit you want and discretize that. e.g. for 5 minutes:

durminutes = minutes(data.date_Time - data.date_Time(1));
data.group5min = discretize(durminutes, 0:5:durminutes(end)+5);

[Dhwanilnath Gharekhan]

Hey,

it worked perfectly, thank you so much.

Answer 2

[Andrei Bobrov]

For MATLAB >= R2016b

T = readtable('Data_test.csv');
a = datetime(T.date_time,'I','dd-MM-yyyy HH:mm');
T.date_time = a;
TT = table2timetable(T);

TT_mean_1minutes = retime(TT,'minutely','mean');

b = TT.date_time(1):minutes(5):TT.date_time(end);

or other variant for b

c = TT.date_time(1);
b = ((c - minutes(rem(minute(c),5))):minutes(5):T.date_time(end))';

TT_mean_5minutes = retime(TT,b,'mean');

TT_mean_1hour = retime(TT,'hourly','mean');

[Dhwanilnath Gharekhan]

Hey can you help for older version, my Matlab is R2016a version.

in this, the software doesnt have a function for table2timetable.