MATLAB: Whenever I import data from excel, one of the column that contains values such as 2.1ELKFJQLHxxxMxxxxxSx4xxxIxSxJJxxxxx, ends up with NaN in Matlab table.

data importimporting excel data

I am working on a table that contains columns such as timestamp, machine status, valueAsString, etc. The problem here is: whenever I upload the excel file with this columns to Matlab, the 'column D' that contains values such as 2.1ELKFJQLHxxxMxxxxxSx4xxxIxSxJJxxxxx ends up with a NaN in Matlab table.

For convenience, I have attached the sample data, and the 'column D', is the one I want to be displayed in the right way in Matlab.

Best Answer

You forgot to post your code. I guess it is:

[num, txt, raw] = xlsread(FileName);

Now you observe, that the file of num is set to NaN, if the Excel sheet contains the data

2.1ELKFJQLHxxxMxxxxxSx4xxxIxSxJJxxxxx

This is the expected and wanted behavior, because this is not a number. The field is imported in the 3rd output raw in its native format, as explained in the documentation.

Or may you use readtable? This is the problem when not posting the code: Writing an answer requires guessing. If so, specify the 'Format' accordingly, such that this field is not interpreted as a number.

Related Solutions

MATLAB: Sparse( ix, jx, sx, rx, cx ) implausibly slow

Using 5 input arguments with the sparse command is indeed an efficient way to construct a sparse matrix. However, you most likely will not be able to speed up the call to sparse, because no matter how the matrix is constructed there is some overhead cost in storing it.

Specifically, MATLAB uses an efficient storage format for sparse matrices. Rather than store the column indices of the non-zero elements, sparse matrices instead store a vector that holds, for each column, the total number of nonzero elements in all preceding columns. The purpose of this is to reduce the overall storage, as in most cases the total number of non-zeros is greater than the number of columns. However, it also means that sparse has to compute the entries of this vector, which introduces overhead.

The suggested test for your code demonstrates quite well why this system is beneficial. When I run the test, I produce the following sparse matrix X:

>> whos X
  Name          Size                    Bytes  Class     Attributes
    X         90000x90000            1175152232  double    sparse

The memory used by X is given by the formula

>> int32(2*8*nnz(X) + (size(X,2)+1)*8)
ans =
  1175152232

The first term is the cost of storing the values of the entries and their row indices, and the second is the cost of storing the column data. Compare this with the cost of storing the both the column and row indices:

>> int32(3*8*nnz(X))
ans =
  1761648336

So MATLAB's format saves 586 MB of memory, but it comes at the cost of a more time-consuming initialization.

MATLAB: X’SX were X is a column vector and S is a matrix.

I suppose what you mean is that X is d-by-N, not d-by-1? So it seems like what you want is

SX = S * X;
for i = 1:N
    z(i) = X(:,i).'*SX(:,i);
end

So you can vectorize this with

z = sum(X.*(S*X), 1).';

Actually, your code is quite explicit that X is a vector, but then saying that X'*S*X produces a vector rather than a scalar makes no sense, so perhaps you should clarify exactly what these shapes are.

Best Answer

Related Solutions

MATLAB: Sparse( ix, jx, sx, rx, cx ) implausibly slow

MATLAB: X’SX were X is a column vector and S is a matrix.

Related Question