How NOT to read blank Excel cells as NaN when using xlsread?
54 次查看(过去 30 天)
显示 更早的评论
I have been given several large data files in Excel. The first column is "time" which is at the highest sample rate of all the following parameters. Each next column is a measured parameter that may or may not be at the same sample rate of "Time". If the parameter is a a slower sample rate the cells between the data will be blank. So in column format the data can be blank, blank, blank, data, blank, blank, blank, blank, data, blank, blank, blank, blank, data, blank... Using xlsread the blanks are brought into Matlab as NaN e.g. NaN, NaN, NaN, data, NaN, NaN, NaN, NaN, data, NaN, NaN, NaN, NaN, data, NaN... I then take that column of data and NaNs and run it through a loop and using isnan pull out the data values and then write them using xlswrite to a new compressed Excel file, which is used by another Matlab file. For ~34 such original files of sizes up to 500Mb this can take up to 15 hours to run. And most times that I've run it, it never made it through all of the files. I've had Matlab stop at file 33 with an error message and somtimes it stops without an error message but with a blinking | in the Command Window but not doing anything at all. Is there some way to not read in the blank cells as NaN and only read in the data? Can someone explain why the program just stops? Note of Caution: I'm an old timer who writes Matlab code like I wrote Fortan IV back in 1979. I don't know all the nifty shortcuts.
I found readtable but that is in R2018b and we still have R2017B. This has options to omit blanks. Is rmmissing in R2017b?
I tried using readtable and opts.MissingRule = 'omitrow';uint32 and the opts was not recognized.
The raw data file is huge and contains aircraft state, vibration, structural, handling quality, etc. data. I need to pull out the columns for 10 aircraft state parameters and 30 vibration parameters. I calculate the mean, min, and max of the aircraft state parameters. I calculate mean, min, and max of several rotor harmonic amplitudes for the vibration parameters. These are then output to a new Excel file. Blanks cells and interpolated cells will screw up the FFT calculations.
I used new_data=rmmissing(data) and the code is running like a champ.
2 个评论
Guillaume
2018-9-24
readtable was introduced in R2013b. I don't think there are many differences between the 2017b and 1018b version of readtable.
If you want matlab to retain the tabular format of the data, then the blanks need to be imported as something. The fact that the something is NaN should not affect the speed of what you do next, so if it is the case, perhaps your code need improving and you ought to give more details.
采纳的回答
Surbhi Pillai
2018-9-24
As evident from your explanation, the sample time is much higher compared to rate at which the parameters are read in the Excel file. Due to this there exist missing values amongst the recorded data. While reading the Excel data using 'xlsread' function, the missing values are represented as NaN in MATLAB.
One simple solution to the problem could be you can use 'rmmissing' function available in MATLAB which removes the missing values from a table or an array.
Say for example:
data=xlsread('data.xlsx');
new_data=rmmissing(data); %new_data is obtained after removing rows with missing values
The documentation link for 'rmmissing' function is shared below:
On the contrary, you can also interpolate or fill the missing values using 'fillmissing' function available in MATLAB. The documentation link for the same is given below:
I hope this helps!
3 个评论
Bereketab Gulai
2020-5-8
编辑:Bereketab Gulai
2020-5-8
retime() is not appropriate for tables...
Try using more variables with rmmissing().
rmmissing(t, 1, 'MinNumMissing',50)
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!