![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/383228/image.png)
how to filter imported data which is imported from excel?
7 次查看(过去 30 天)
显示 更早的评论
hi all:
1: i want ot import specific data from above excel file , i need to import only 01:00pm solar radiation and to unknown the formulla is given below.
2: i want to plot all T2 on fix x-axises which is 01:00PM.
3: S solar radiation given in excel file.
K=1200;
Tb=300;
Cm=4180;
m=0.5;
k*S.*(Tb-T1)=Cm*m(T2-T1)
plot(T2)
采纳的回答
Image Analyst
2020-10-16
Not sure what T1 is, but how does this work for you?
clc; % Clear the command window.
close all; % Close all figures (except those of imtool.)
clear; % Erase all existing variables. Or clearvars if you want.
workspace; % Make sure the workspace panel is showing.
format long g;
format compact;
fontSize = 22;
fprintf('Beginning to run %s.m ...\n', mfilename);
% 1: I want to import specific data from above excel file , i need to import only 01:00pm solar radiation and to unknown the formula is given below.
% 2: I want to plot all T2 on fix x-axises which is 01:00PM.
% 3: S solar radiation given in excel file.
[numbers, strings, raw] = xlsread('solardata.xlsx')
times = raw(2:end-1, 1)
solar_radiation = numbers(1:end-1); % Ignore last row which is nan.
% Convert time strings into date/time numbers
d = datevec(times);
% Find out which rows are during the 1 O'clock hour
rowsOfOneOclock = d(:, 4) == 13
% Get the solar radiation for 1 o'clock.
S1 = solar_radiation(rowsOfOneOclock)
days = strings(2:end, 2);
days = days(rowsOfOneOclock);
% Now some other math that I don't know what it means...
K=1200;
Tb=300;
Cm=4180;
m=0.5;
% k*S.*(Tb-T1)=Cm*m(T2-T1)
% Solve for T2
% k*S.*(Tb-T1) / (Cm * m) + T1 = T2
T1 = 1; % No idea -- just a guess here.
T2 = K * S1 .* (Tb - T1) / (Cm * m) + T1
plot(T2, 'b.-', 'LineWidth', 3, 'MarkerSize', 40);
grid on;
xlabel('Different Days', 'FontSize', 20);
ylabel('T2', 'FontSize', 20);
title('T2 = K * S1 .* (Tb - T1) / (Cm * m) + T1', 'FontSize', 20);
fprintf('Done Running %s.m ...\n', mfilename);
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/383228/image.png)
21 个评论
Engineer Batoor khan mummand
2020-10-17
编辑:Engineer Batoor khan mummand
2020-10-17
hi Image Analyst:
thanks dear from your support and helping .
i learned alot from your code but same file for large data it gives me error i do not know why file is attached in this comment so request you to correct my error i will be very thankful to you .
T1=20;
find T2 for all solar radiation of 1:00pm .
Thanks
Engineer Batoor khan mummand
2020-10-17
编辑:Engineer Batoor khan mummand
2020-10-17
this is error.....
Index exceeds matrix dimensions.
Error in dat11 (line 20)
rowsOfOneOclock = d(:, 10) == 25;
Image Analyst
2020-10-17
Did you open up the files and look at them? The first one has only 1:00 PM which is the 13th hour. The other, hiii.xlsx, doesn't have any of those. It has 1:00 AM, and 13:00 PM instead. If you look at d, it seems 13:00PM evaluates to hour 1, not 13. Plus should 1:00AM be 1:00PM? At least where I live, at 1:00 AM it's night and the solar radiation is probably many orders of magnitude less than in the day - so small as to not even be worth measuring for most situations. So you can check for both 1 and 13.
You could change the code to be like this to check for both formats:
% Find out which rows are during the 1 O'clock hour, both AM and PM
rowsOfOneOclock = d(:, 4) == 13 | d(:, 4) == 1
Engineer Batoor khan mummand
2020-10-17
Hi dear : I hope u will be fine and doing well as you know in Excel file there is 1:00Am and the second is 1:00pm so I need to import all those solar radiation which for 1:00 pm and all other should ignore . Thank u so much dear from your reply
Engineer Batoor khan mummand
2020-10-17
I put rowsOfOneOclock=d(:,4)==13... From 13 you mean number of rows and what about 4 ?
Engineer Batoor khan mummand
2020-10-17
First 1:00Am is in second row And 1:00PM is in 25 rows ... Dear
Image Analyst
2020-10-17
In "d", the hour is in column 4. 1 AP or PM will either show up as a 13 or a 1 in that column.
rowsOfOneOclock is a logical vector that will be either true (1) or false(0) if there is either a 1 or 13 in column 4.
Engineer Batoor khan mummand
2020-10-18
hi dear :
i have done thankyou so much dear from your helping.
one another point i want to ask you about my last problem...
if i want to import average solar radiation of each day instead of specific hour (1:00PM) than how we can import that ?
thank you so much
Image Analyst
2020-10-18
You have to get a column that represents what day each measurement is on. Then you can use splitapply() or grpstats().
Engineer Batoor khan mummand
2020-10-18
Hi dear : Will you use splitapply()or grpstats() in above problem I will be very thankful .
Image Analyst
2020-10-19
Will I? Well, I wasn't planning on it. Why can't you look it up in the help and do it? It's not hard to use. Certainly it's not beyond your capabilities is it?
Engineer Batoor khan mummand
2020-10-19
Ok know dear sir but as you know in matlab if you use even only one dot wrong than it will not run all programme that is why I said to u . Thanks
Engineer Batoor khan mummand
2020-10-21
编辑:Engineer Batoor khan mummand
2020-10-21
hi Dear:
i used below code to import average data of each day from above excel file but it gives me answer nan, what is wrong in below code dear.
thanks
[~,Num,Data]=xlsread('hiii.xlsx');
arrayData = cell2mat(Data(2:end, 3))
average = mean(arrayData);
Image Analyst
2020-10-21
编辑:Image Analyst
2020-10-21
Which column is the day of the year? That's what you need to pass in to splitapply(). I computed it. Try this:
[numbers, strings, rawData] = xlsread('hiii.xlsx')
daysOfWeek = strings(2:end, 2);
sequentialDays = ones(length(daysOfWeek), 1);
for k = 2 : length(sequentialDays)
if ~strcmpi(daysOfWeek{k}, daysOfWeek{k-1})
% It's not the same as the prior day so we're starting a new day.
sequentialDays(k:end) = sequentialDays(k-1) + 1;
end
end
% Get the averages over any and all hours of each day.
dailyAverages = splitapply(@mean, numbers, sequentialDays);
plot(dailyAverages, 'b-', 'LineWidth', 2);
title('Daily Average of Solar Radiation', 'FontSize', 18);
xlabel('Day', 'FontSize', 18);
ylabel('Average over the Day', 'FontSize', 18);
grid on;
g = gcf;
g.WindowState = 'maximized';
fprintf('Done running %s.m ...\n', mfilename);
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/387773/image.png)
Engineer Batoor khan mummand
2020-10-22
编辑:Engineer Batoor khan mummand
2020-10-22
thank you so much dear i have learned alot from you ,
dear sir i understood and my problem is solved but one thing i have that is ignoring all those solar radiation values which are less than 200 from each day than taking average of each day .
i am very thankful dear
thanks
Engineer Batoor khan mummand
2020-10-22
dear sir i want to ignore all those values which is less than 200 and than taking average of each day .
thank you so much dear from your helping.
Image Analyst
2020-10-22
You need to extract only those values 200 or greater, then plot, like this:
[numbers, strings, rawData] = xlsread('hiii.xlsx');
daysOfWeek = strings(2:end, 2);
% Threshold at 200
goodIndexes = numbers >= 200;
% Extract only those elements above 200.
numbers = numbers(goodIndexes);
daysOfWeek = daysOfWeek(goodIndexes);
% Label each individual day.
sequentialDays = ones(length(daysOfWeek), 1);
for k = 2 : length(sequentialDays)
if ~strcmpi(daysOfWeek{k}, daysOfWeek{k-1})
% It's not the same as the prior day so we're starting a new day.
sequentialDays(k:end) = sequentialDays(k-1) + 1;
end
end
% Get the averages over any and all hours of each day.
dailyAverages = splitapply(@mean, numbers, sequentialDays);
plot(dailyAverages, 'b-', 'LineWidth', 2);
title('Daily Average of Solar Radiation', 'FontSize', 18);
xlabel('Day', 'FontSize', 18);
ylabel('Average over the Day', 'FontSize', 18);
grid on;
% Smooth the daily averages
smoothedAverages = movmean(dailyAverages, 45);
hold on;
plot(smoothedAverages, 'r-', 'LineWidth', 4);
legend('Data >= 200', 'Smoothed');
g = gcf;
g.WindowState = 'maximized';
fprintf('Done running %s.m ...\n', mfilename);
![](https://www.mathworks.com/matlabcentral/answers/uploaded_files/388763/image.png)
Engineer Batoor khan mummand
2020-11-16
Hi Image Analyst:
i hope you will be fine and doing well , if i want to take average of each month and each weeks instead of each days than which part of the code i should change .
thank you so much dear ..
Engineer Batoor khan mummand
2020-11-16
Hi image Analyst ... I hope you will be fine and doing well. Yes I have used splitapply() but as u know each month length is not equal I mean we have some months 30 days and some of months of 31 days so this is creating problem in matlab. Thanks dear
更多回答(0 个)
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Data Import and Analysis 的更多信息
产品
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!发生错误
由于页面发生更改,无法完成操作。请重新加载页面以查看其更新后的状态。
您也可以从以下列表中选择网站:
如何获得最佳网站性能
选择中国网站(中文或英文)以获得最佳网站性能。其他 MathWorks 国家/地区网站并未针对您所在位置的访问进行优化。
美洲
- América Latina (Español)
- Canada (English)
- United States (English)
欧洲
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)
亚太
- Australia (English)
- India (English)
- New Zealand (English)
- 中国
- 日本Japanese (日本語)
- 한국Korean (한국어)