Removing specific cell values from a table

Hi so I recently loaded a table from excel into matlab using the xlsread command. Now some of the cells in the table are empty and show in matlab as NaN. I wanted to run the diff command on each of the rows in the table but cannot do that with these NaN cells present. Is there anyway I could possibly remove these specific cells from the table in matlab and then proceed to do the calculation on the cells in each row that actually have a numeric value?

[Walter Roberson]

When you talk about the diff command, are you referring to using the Unix file comparison tool diff to compare the .xlsx files? Or are you talking about the numeric difference call in MATLAB, diff() ?

If you are talking about numeric differences, perhaps rmmissing() might be useful to you?

[Image Analyst]

If there is a NAN in the row, then that means that there is not a number there. There is a cell interrupting the run of numbers that is a blank or a text cell. You can't do a diff across that kind of break/gap in the numbers. Why would you want to? You forgot to attach your Excel workbook.

[Robert A Easter]

So in each row of the excel table I am trying to calculate the derivative for that row of data points but unfortunately some of the cells in each row of the table are empty. What I want to do is have the system ignore the empty cells and calcualte the derivative for the rest of the row but I am not sure on how to do that. I tried the rmmissing command but unfortunately that removed any rows from the excel file that had any empty cells present which is not what I want to do. I was wondering if there was any way I could just mark those empty cells as unnecessary for doing the calculation?

Answer 1

[Walter Roberson]

I was wondering if there was any way I could just mark those empty cells as unnecessary for doing the calculation?

No, you cannot do that. diff() always does the equivalent of

x(2:end) - x(1:end-1)

and if there are nan in the inputs then you will get nan in the output.

What you are asking for would require that there be a "hole" in the output -- e.g., if x(7) were nan, that the 6th and 7th outputs would just not exist, that the data structure would store elements 1 to 5, in one part of memory, and store elements 8 onward in a different part of memory, and when you asked to look at the 6th output, it would... what? Return some kind of numeric "missing" indication that is somehow different than nan ??

If you use gradient() and there are nan in the inputs, then you will get nan in the output, for the same kinds of reasons, except that since gradient() uses central differences, three adjacent outputs are affected by one nan. And I would ask again what you expect if you ask to see the values at those locations --

 >> fx(6,3)
 
 ans =
 ??? Sorry, that data seems to be missing ???
 
 >> surf(fx)
 
 ans =
 ??? Sorry, there are some places that have missing data and what does it mean to plot missing data ???
 

The current output for nan is well defined -- you get out nan when you ask to see the location, and a hole is left in the surface where nan are. But you want the data to not exist rather than being nan, and how can you plot what does not exist?

Might I suggest that instead you consider using fillmissing() ?