MATLAB, time match filter

Question

OK, I am still getting the hang of MATLAB. I have two files in different format. One Excel file. data1.xls, size= 86400 X 62. It looks like:

Date/Time par1 par2 par3 par4 par5 par6 par6 par7 par8 par9
08/02/09 00:06:45 0 3 27 9.9 -133.2 0 0 0 1 0

Another file, data2.csv, size = 144 X 27. (If nothing is missing.)

It looks like:

date time  P01 P02 P03 P04 P05  P06  P07 P08 P09  P10  P11
8/16/2009 0:00  51   45  46    54   53  52 524  5  399      89      78

Now I am using

Data10minAvg = mean(reshape(Data,300,144,62));

to get the 10 min average of the first Excel file. Now I need to match up that file I am making above with the .csv file. The problem is many timestamps are missing in the .csv file.

How do I make data2.csv into a file of size 144 X 27, replacing the missing datestamps by rows of zero?

It will really help me than compare data1.xls file with newdata2.csv.

Update

Hi This is Paul. Thanks a lot Geodesic. I do not know how to edit so i could not have written my whole files. You understood the question perfectly.

But now when i run your code , it generates a file of length 377 X 11 when i use timediff(i) >10 as i want 10 min intervals , so i should not have more than 144 as my file length. your code is quite complicated for me i was using

fid = fopen('data2.csv', 'rt');

topLine = fgets(fid);

data = textscan(fid, '%s %f %f %f %f %f%f%f %f %f %f %f%f%f %f %f %f %f%f%f %f %f %f %f%f%f %f %f ' ,.... 'Delimiter',',', 'CollectOutput',1, 'HeaderLines',1);

fclose(fid);

p= [datenum(data{1}) data{2}];

for n=1:127 % this file was of length 127

s=datevec(p(n,1)); Hour= s(1,4) min=s(1,5)

nTime(n,:)=60*Hour+min; end

to get my times

Can you help me understand your last for loop so i can use it.

my final file has to be of length 144 X 11 ( columns vary but i can change that ) but rows have to to 144

**Just tried using your code timeDiff = round(diff(datenum(time)*24*6)); it gave me 143 rows , much closer to what i want. **

also all other values are getting rounded, anyway to stop it?

Thanks a ton.

Answer 1

From your reply, with some poetic license, it looks like you'd be using textscan with the string %s %u %u %u %u %u %u %u %u %u %u %u to read in the file. I've made up a file with some random ints in an attempt to emulate the situation:

date time, P01,P02, P03, P04, P05, P06, P07, P08, P09, P10, P11
8/16/2009 0:00,51,45,46,54,53,52,524,5,399,89,78
8/16/2009 0:01,11,73,65,67,10,32,100,64,167,87,48
8/16/2009 0:02,20,58,89,15,30,67,113,76,409,55,26
8/16/2009 0:03,89,33,33,58,90,61,545,51,88,23,34
8/16/2009 0:04,33,9,92,43,86,97,345,20,369,38,29
8/16/2009 0:06,24,86,74,38,38,90,16,48,197,34,75
8/16/2009 0:07,59,86,67,30,7,54,135,8,231,66,19
8/16/2009 0:08,47,11,74,30,30,60,302,49,200,59,40
8/16/2009 0:09,76,50,66,48,92,2,598,83,336,26,34
8/16/2009 0:10,97,12,38,62,54,63,437,32,480,85,53
8/16/2009 0:11,30,78,6,98,98,21,375,50,72,87,15
8/16/2009 0:12,3,63,2,30,14,7,436,100,406,9,55
8/16/2009 0:13,43,19,32,8,31,20,426,29,296,97,50
8/16/2009 0:16,83,1,75,66,43,43,356,40,496,86,68
8/16/2009 0:17,24,69,67,40,81,23,526,36,104,73,10
8/16/2009 0:18,47,28,37,97,31,19,546,73,381,87,8
8/16/2009 0:19,73,38,92,81,21,63,227,50,463,91,74
8/16/2009 0:20,20,56,12,76,79,67,358,47,234,89,57

Is this similar to what you have? The entire row (0:05, 0:14, 0:15) is missing? Then what you want to do is fill in the missing rows with 0's where your data should be like this?

date time, P01,P02, P03, P04, P05, P06, P07, P08, P09, P10, P11
8/16/2009 0:00,51,45,46,54,53,52,524,5,399,89,78
8/16/2009 0:01,11,73,65,67,10,32,100,64,167,87,48
8/16/2009 0:02,20,58,89,15,30,67,113,76,409,55,26
8/16/2009 0:03,89,33,33,58,90,61,545,51,88,23,34
8/16/2009 0:04,33,9,92,43,86,97,345,20,369,38,29
0,0,0,0,0,0,0,0,0,0,0,0
8/16/2009 0:06,24,86,74,38,38,90,16,48,197,34,75
8/16/2009 0:07,59,86,67,30,7,54,135,8,231,66,19
8/16/2009 0:08,47,11,74,30,30,60,302,49,200,59,40
8/16/2009 0:09,76,50,66,48,92,2,598,83,336,26,34
8/16/2009 0:10,97,12,38,62,54,63,437,32,480,85,53
8/16/2009 0:11,30,78,6,98,98,21,375,50,72,87,15
8/16/2009 0:12,3,63,2,30,14,7,436,100,406,9,55
8/16/2009 0:13,43,19,32,8,31,20,426,29,296,97,50
0,0,0,0,0,0,0,0,0,0,0,0
0,0,0,0,0,0,0,0,0,0,0,0
8/16/2009 0:16,83,1,75,66,43,43,356,40,496,86,68
8/16/2009 0:17,24,69,67,40,81,23,526,36,104,73,10
8/16/2009 0:18,47,28,37,97,31,19,546,73,381,87,8
8/16/2009 0:19,73,38,92,81,21,63,227,50,463,91,74
8/16/2009 0:20,20,56,12,76,79,67,358,47,234,89,57

My assumption in this solution is that the file should be the sum of the difference between each time step, which allows you files that aren't always going to be a static size (ie 144). I've pulled out the time to a vector and made the rest of the data a matrix rather than a cell, but as you can see it'd be as easy to fix with a cell solution:

fid = fopen('data2.csv', 'rt');
data = textscan(fid, ['%s' repmat('%u',1,11)], 'HeaderLines', 1, 'Delimiter', ',');
fclose(fid);

%//Make time a datenum of the first column
time = datenum(data{1}, 'mm/dd/yyyy HH:MM');
%//Find the difference in minutes from each row
timeDiff = round(diff(datenum(time))*24*60);

%//the rest of the data
data = cell2mat(data(2:12));


for i=1:length(timeDiff)
    if timeDiff(i) > 1
        %//there's are missing rows, make a gap
        data = [data(1:i,:); repmat(0,timeDiff(i)-1,11);data(i+1:end,:)];
    end
end

And finally, the output:

data =

          51          45          46          54          53          52         524           5         399          89          78
          11          73          65          67          10          32         100          64         167          87          48
          20          58          89          15          30          67         113          76         409          55          26
          89          33          33          58          90          61         545          51          88          23          34
          33           9          92          43          86          97         345          20         369          38          29
           0           0           0           0           0           0           0           0           0           0           0
          24          86          74          38          38          90          16          48         197          34          75
          59          86          67          30           7          54         135           8         231          66          19
          47          11          74          30          30          60         302          49         200          59          40
          76          50          66          48          92           2         598          83         336          26          34
          97          12          38          62          54          63         437          32         480          85          53
          30          78           6          98          98          21         375          50          72          87          15
           3          63           2          30          14           7         436         100         406           9          55
           0           0           0           0           0           0           0           0           0           0           0
           0           0           0           0           0           0           0           0           0           0           0
          43          19          32           8          31          20         426          29         296          97          50
          83           1          75          66          43          43         356          40         496          86          68
          24          69          67          40          81          23         526          36         104          73          10
          47          28          37          97          31          19         546          73         381          87           8
          73          38          92          81          21          63         227          50         463          91          74
          20          56          12          76          79          67         358          47         234          89          57