Associating csv data with matrix & Matrix creation for Day of Year / time

Hi,

I have a set of data which has one column as the day of year (i.e. 144 for 23/5/12 during this leap year), the next column is time in the format 1352 for 1:52 pm, then two columns of instrument data (mV).

i.e. 110,1352,2.648,1.889

What i want to do is import this into MatLab (i've figured this out) and then i need to sift through the data and grab the relevant times. I only want data between 8 am and 4 pm, and the data file has some missing time lines (a minute here and there).

Hence i need to create a DATA matrix with a day of year column (DOY), then a time column and two spare columns and then i need to search the IMPORTED csv matrix for the DOY and time and then grab the two mV readings and place them.

I then need to make a 5 min average of the imported data and compare it to the DATA matrix (i.e. compare the average of 13:51-13:55 to in individual reading of 13:52).

I'm quite new to MatLab and was hoping someone could steer me in the right direction. I don't necessarily want you to do the problem for me, you could simply list the commands you think would be used and i'll go look them up.

Many thanks and Kind Regards.

Orfeo

Answer 1

[Oleg Komarov]

1. Import the whole data (all rows and the 4 columns) with fopen(), textscan() fclose() (check example 2 of textscan on how to use them together).
2. Index the second column for times between 800 and 1600: idx = data(:,2) > 800 & data(:,2) < 1601 and discard values that do not meet the criteria.
3. Map hour data to [0,1] interval, i.e. datenum format.
4. Then I would use accumarray() to take 5 minute averages.

An example, suppose your csv is test.txt, is located on the desktop and has the following content:

110, 0734, 0,  0
110, 1352, 1,  9
110, 1353, 2, 10
110, 1354, 3, 11
110, 1355, 4, 12
110, 1357, 5, 13
110, 1358, 6, 14
110, 1359, 7, 15
111, 1359,20, 30

% Import data into 10 by 4 matrix

fid  = fopen('C:\Users\Oleg\Desktop\test.txt');
data = textscan(fid,'%.f%.f%.f%.f','Delimiter',',','CollectOutput',1);
data = data{1};
fclose(fid);

% Index only 8 am - 4 pm and discard the rest

idx  = data(:,2) > 800 & data(:,2) < 1601;
data = data(idx,:);

% Convert hour data into datenum format

hour = datenum(0,0,0,fix(data(:,2)/100),mod(data(:,2),100),0);

% 5 minute averages

subc = fix((hour-8/24-1/(60*24))/5*60*24);
out  = accumarray([data(:,1) subc],data(:,3),[365,96],@mean,NaN)

The resulting matrix is 365 by 96, i.e. the row is the DOY (so row 5 is 5th day, row 59 is day 59) and the column is the 5 minute average (column 35 is the 35th 5-minute average, 10:56-11:00)

[Orfeo]

Thanks Oleg,

I'm trying to implement your advise now. One thing i wondered is how best to compare the minute data to the created 5 min averages in 'out'.

I.e. if i need to compare the 1352 minute data from column 3 (1) to the 5 min average (out(110,71) for that time period (2.5) how should i go about doing it?

Ideally i want to have a 4th column in the 'data' array which is a comparison between the minute data and the 5 min average. If the minute data differs by more than 2% from the 5 min average then i'll throw that minute out and recalculate the 5 min average and compare again.

Once i have a stable data set i can then average all the individual minute data which has passed this 2% cut off.

Many thanks again

orfeo

[Oleg Komarov]

I would take out(:) and expand it to pad data(:,3) with run length decoding (use rude() on the FEX), then subtract the mean and calculate the absolute percentage change. With logical indexing identify the values of data(:,3), to discard and do so with data(:,1) and subc, then simply rerun the accumarray().

The critical point to gain speed is to expand out(:) to the same length of data(:,3). You have to count how many values are in each 5-minute average, to do so accumarray([data(:,1) subc],1,[365,96],@sum,NaN).