How do I account for missing data when creating an Excel file in MATLAB?

Question

I had posted a similar question related to plotting data, and now I'd like to know how to handle missing data when outputting data to an Excel file using the XLSWRITE function.

I have two sets of data of different length (described in the question I link to above). I am trying to replace the smaller file with zeroes for the times when the data is missing. This is the code I tried to use:

newfile2 = zeros(144,20);
[ts,ifile1,ifile2] = intersect(file1(:,1),file2(:,1));
newdfile2(ifile2,:) = file2;

Here, I have already converted the column 1 data to a number using DATENUM. So ifile2 is giving me indices of times that are common to both files. The problem is I get this error for line 3:

((??? Subscripted assignment dimension mismatch. ))

This is because the size of file2 is 130-by-20, but my file1 is of size 144-by-20. I also tried a different approach using ISMEMBER to find the missing indices and still got the same error.

Can someone please give me some guidance?

Answer 1

If I understand your question correctly, you want to copy from file2 to newfile2, and you want to copy the data that has timestamps that exist in file1, and you want newfile2 to be the same size as file1.

newfile2 = zeros(size(file1));
[ts,ifile1,ifile2] = intersect(file1(:,1),file2(:,1));
newfile2(ifile1,:) = file2(ifile2,:);

should do the trick.

Answer 2

One solution that will allow you to output "empty" cells to your Excel worksheet is to make the data a cell array with empty values or blanks instead of zeroes, using one of the following options:

newData = cell(size(fileData1));  %# Each cell is initialized to []
[newData{:}] = deal('');          %# Change the cell contents to an empty string
[newData{:}] = deal(' ');         %# Change the cell contents to a blank

There are then two ways to proceed. If the time-stamps in the smaller file are only ever a subset of the ones in the larger (i.e. there is never a time-stamp in the smaller file that is not in the larger), then you can just use the ISMEMBER function as follows:

t = fileData1(:,1);                      %# Time-stamps from file 1
index = ismember(t,fileData2(:,1));      %# Find index of common time-stamps
newData(:,1) = num2cell(t);              %# Copy time-stamps
newData(index,:) = num2cell(fileData2);  %# Copy file 2 data

However, if there are time-stamps in the smaller file that aren't in the larger, you should instead do the following using the INTERSECT function:

t = fileData1(:,1);                                  %# Time-stamps from file 1
[junk,index1,index2] = intersect(t,fileData2(:,1));  %# Find indices
newData(:,1) = num2cell(t);                          %# Copy time-stamps
newData(index1,:) = num2cell(fileData2(index2,:));   %# Copy file 2 data

The above will discard any time-stamps that are in the smaller file but not in the larger. If you want to include this extra data then include the following additional code (which uses the functions SETDIFF and SORT):

[junk,index] = setdiff(fileData2(:,1),t);           %# Unique time-stamp indices
newData = [newData; num2cell(fileData2(index,:))];  %# Add unique data
[junk,index] = sort([newData{:,1}]);                %# Sort the time-stamps
newData = newData(index,:);                         %# Reorder the data

Now, if you use XLSWRITE to output newData to an Excel file, the padding cells should show up as empty, although the documentation for XLSWRITE has these remarks:

Full functionality of xlswrite depends on the use of the Microsoft Excel COM server. The typical installation of Excel for Windows includes access to this server. If your system does not have Excel for Windows installed, or if the COM server is unavailable, xlswrite:

• Writes matrix M as a text file in comma-separated value (CSV) format.

• Ignores the sheet and range arguments.

• Generates an error if the input matrix M is a cell array.

If your system has Microsoft Office 2003 software installed, but you want to create a file in an Excel 2007 format, you must install the Office 2007 Compatibility Pack.