Importing data to change a NaN

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)

[Luna]

What part your code doesn't work? Can you be more clear and give an examle reproducible code?

[Matthew Perry]

this is my full code:

clear all
clc

%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 
disp(RD(choice,2:4));
x = 2017:1:2019;
y = [y17(choice,:),y18(choice,:),y19(choice,:)];
bar(x,y)

%Calculating the mean
avg = mean(RD(choice,2:3));

[Guillaume]

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

Answer 1

[Guillaume]

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

Answer 2

[meghannmarie]

Try readcell:

RD = readcell('filename.xlsx')
choice = 1
avg = mean([RD{choice,2:end}]);
name = RD{choice,1};

disp([name ': ' num2str(avg)])