Importing data to change a NaN

4 次查看（过去 30 天）
Matthew Perry
Matthew Perry 2019-12-5
编辑： Guillaume 2019-12-5
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 个评论
显示 1更早的评论
Matthew Perry
Matthew Perry 2019-12-5
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
Guillaume 2019-12-5
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));

请先登录，再进行评论。

请先登录，再回答此问题。

回答（2 个）

Guillaume
Guillaume 2019-12-5
编辑：Guillaume 2019-12-5
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
meghannmarie 2019-12-5
Try readcell:
RD = readcell('filename.xlsx')
choice = 1
avg = mean([RD{choice,2:end}]);
name = RD{choice,1};
disp([name ': ' num2str(avg)])

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

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

Start Hunting!

Translated by