Imported data becomes NaN
155 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
Robin Nilsson
el 17 de Oct. de 2022
Comentada: dpb
el 17 de Oct. de 2022
I'm trying to import data from an excel document with many rows and columns.
When I import it in any way Matlab gives Nan.
Ex. 1. I import from excel as Table (also tried column vectors, column vectors and numeric matrix), If I open it in the workspace I see numbers. When loading in matlab command window it becomes NaN all of it.
Ex 2. I save the excel into txt files and other various file types and try to use "loadtable", "readcell", "load" but it becomes "NaN" or "1x1 missing".
Does anyone have an idea of what I'm doing wrong?
Update, I can now see the reason for my troubles are that the imported file gives many NaN and I didn't realise I had so many NaN in the end. All columns are not the same length. Now I just need to remove remove all NaN. I assume I need to take each column and remove nan for that column.
Respuesta aceptada
dpb
el 17 de Oct. de 2022
opt=detectImportOptions(websave('CA_dvs.xlsx','https://www.mathworks.com/matlabcentral/answers/uploaded_files/1159783/CA_dvs.xlsx'));
tCA=readtable(websave('CA_dvs.xlsx','https://www.mathworks.com/matlabcentral/answers/uploaded_files/1159783/CA_dvs.xlsx'));
head(tCA)
Looks ok excepting more than likely there are additional header lines in the file besides the first...let's see what the file really, really contains...
CA=readcell(websave('CA_dvs.xlsx','https://www.mathworks.com/matlabcentral/answers/uploaded_files/1159783/CA_dvs.xlsx'));
CA(1:5,:)
And, indeed, that's what we see -- there's another pair of lines prior to the actual data that is metadata apparently; one has to recognize what is in the file one is trying to read and adjust expectations to fit reality.
In this case, I'd suggest modifying the optons object slightly first before importing -- since the percentages are embedded in the first header row, one can extract them later and then just begin reading the real data after skipping the additional two header lines. That would look like--
opt.DataRange='A4'; % keeping the opt object already read, just fixup where data starts
tCA=readtable(websave('CA_dvs.xlsx','https://www.mathworks.com/matlabcentral/answers/uploaded_files/1159783/CA_dvs.xlsx'),opt);
head(tCA)
And, voila! joy ensues...to get the percentages for each pair of observations is easy enough...
pct=str2double(extract(tCA.Properties.VariableNames(1:2:end),digitsPattern))
Más respuestas (1)
Kevin Holly
el 17 de Oct. de 2022
I would suggest using the Import Data button found on the toolstrip.
After selecting the Excel spreadsheet to open, it should give you a preview and show what cells are unimportable. By default these cells are converted to NaN values. This could help you resolve the problem.
3 comentarios
dpb
el 17 de Oct. de 2022
The import options object has ways to handle those as well; you can either use the default missing value and get the NaN or have any records with missing data skipped entirely, or a plethora of other options.
Ver también
Categorías
Más información sobre Spreadsheets en Help Center y File Exchange.
Productos
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!