Importing data to change a NaN

8 views (last 30 days)
Matthew Perry
Matthew Perry on 5 Dec 2019
Edited: Guillaume on 5 Dec 2019
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)
Matthew Perry
Matthew Perry on 5 Dec 2019
this is my full code:
clear all
%Initial tabulated recycling percentages for each place
RD = readmatrix("Recycling_data.xlsx");
%Allocating each column to a variable
years = RD(:,2:4);
y17 = RD(:,2);
y18 = RD(:,3);
y19 = RD(:,4);
place = RD(:,1);
%Import second table of opening times and location
%Menu to determin which recycling centre data is wanted
choice = menu("Please choose your nearest recycling centre ","Bidston","Clatterbridge","Formby","Huyton","Johnsons Lane","Kirkby","Newton-Le-Willows","Old Swan","Otterspool","Picow Farm","Rainhill","Ravenhead","Sefton Meadows","South Sefton","Southport","West Kirkby");
%Displays the percentages of each year
x = 2017:1:2019;
y = [y17(choice,:),y18(choice,:),y19(choice,:)];
%Calculating the mean
avg = mean(RD(choice,2:3));
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,:)];
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)
bar(2017:2019, RD(choice, 2:4));

Sign in to comment.

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});

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)])

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!