在MATLAB中创建Excel文件时，如何解决数据丢失的问题? [英] How do I account for missing data when creating an Excel file in MATLAB?

问题描述

我已经发布了

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. ))

这是因为file2的大小是 130×20 ，而我的file1的大小是 144×20 .我还尝试了使用 ISMEMBER 的另一种方法来查找缺少索引，仍然出现相同的错误.

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?

推荐答案

一种允许您将空"单元格输出到Excel工作表的解决方案是使数据成为具有空值或空白而不是零的单元格数组，使用以下选项之一:

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

然后有两种方法可以进行.如果较小文件中的时间戳只是较大文件中的时间戳的子集(即较小文件中没有不是的时间戳)，那么您可以只使用 ISMEMBER 函数，如下所示:

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

以上内容将丢弃较小文件中的所有时间戳，而不是较大文件中的所有时间戳.如果要包括这些额外的数据，请包括以下其他代码(使用功能 SETDIFF 和 SORT ):

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

现在，如果您使用 XLSWRITE 来将newData输出到Excel文件，尽管XLSWRITE的文档有以下说明，填充单元仍应显示为空:

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:

xlswrite的全部功能取决于 关于使用Microsoft Excel COM 服务器.典型安装 Excel for Windows包括对以下内容的访问 该服务器.如果您的系统没有 已安装Excel for Windows，或 如果COM服务器不可用， xlswrite:

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:

• 将矩阵M编写为文本文件 逗号分隔值(CSV)格式.

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

忽略工作表和范围参数.

Ignores the sheet and range arguments.

如果输入矩阵，则生成错误 M是一个单元格数组.

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

如果您的系统具有Microsoft Office 已安装2003软件，但您要 在Excel 2007中创建文件 格式，您必须安装Office 2007兼容包.

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.

这篇关于在MATLAB中创建Excel文件时，如何解决数据丢失的问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！