Importing data to change a NaN
8 views (last 30 days)
Show older comments
Hi there, Im importing a data set from excel which is made up of 4 comlumns and 16 rows.
the first column are place names, the remaining comlumns are made up of percentages of recycled material for each place.
im importing data using readmatrix as this seems to be the only one that works for my purposes. but since the first column is place names obviously matlab, when asked returns NaN. how do I change this so that I can display a matrix shwowing: "placename = Meanrecyclingdata" as my code does not work.
in this code, choice is a value returned from a multi choice menu function that corresponds to the row in which that place name is in the data set.
RD = readmatrix("filename.xlsx")
avg = mean(choice,:)
name = RD(choice,1)
disp(name avg)
3 Comments
Guillaume
on 5 Dec 2019
Numbered variables are always a bad idea. They make the code more complicated, not simpler. For example, in your code, you have three lines to split a perfectly useable matrix into three numbered variables and then another line to join them back together, whereas you could have just done:
%instead of
% y17 = RD(:,2);
% y18 = RD(:,3);
% y19 = RD(:,4);
% y = [y17(choice,:),y18(choice,:),y19(choice,:)];
%simply
y = RD(choice, 2:4);
or even simpler:
%instead of
% y17 = RD(:,2);
% y18 = RD(:,3);
% y19 = RD(:,4);
% x = 2017:1:2019;
% y = [y17(choice,:),y18(choice,:),y19(choice,:)];
% bar(x,y)
%simply
bar(2017:2019, RD(choice, 2:4));
Answers (2)
Guillaume
on 5 Dec 2019
Edited: Guillaume
on 5 Dec 2019
You should be using readtable to load your data into a table. You could also use readcell but this would complicate things. You cannot store text into a matrix.
recycled = readtable('C:\somewhere\somefile.xlsx');
It is trivial to filter a table according to the value of any column. E.g. assuming that the place name variable is called placename (actual name depends on the header of your excel file if it has one, if it hasn't got one, it'll be Var1 in which case you should rename it):
recycled_london = recycled(strcmp(recycled.placename, 'London'), :) %get all rows which are in London
But if you want the mean of the other columns for each location that can be done at once with:
mean_recycled = groupsummary(recycled, 'placename', 'mean')
edit: now that you've posted some example code, here's how to do it with code that is easy to understand:
recycling_data = readtable("Recycling_data.xlsx"); %read into a table
years = 2017:2019;
recycling_data.Properties.VariableNames = [{'placename'}, compose('year_%d', years)]; %assuming your table has 4 variables
placenames = unique(recycling_data.placename); %get list of place names from the file instead of hardcoding it. That way you're sure it matches what's in the file
selectedplaceindex = menu("Please choose your nearest recycling centre", placenames{:}); %use placenames to populate the menu
assert(selectedplaceindex ~= 0, 'User canceled the selection'); %no point in continuing then...
selected_data = recycling_data(strcmp(recycling_data.placename, placenames{selectedplaceindex}), :); %only keep the rows that match the selected place
bar(years, selected_data{:, 2:4})); %plot
avg = mean(selected_data{:, 2:3});
0 Comments
meghannmarie
on 5 Dec 2019
Try readcell:
RD = readcell('filename.xlsx')
choice = 1
avg = mean([RD{choice,2:end}]);
name = RD{choice,1};
disp([name ': ' num2str(avg)])
0 Comments
See Also
Categories
Find more on Spreadsheets in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!