How to create a datetime vector from text input and sum only certain values?

조회 수: 2 (최근 30일)
I have an Excel file from which I use two columns. Column A contains yearly (01.01.-31.12.) time values in text in 15min intervals (01.01. 00:00:00, 01.01. 00:15:00, ...) and column M contains corresponding numeric values for those times.
Is it possible to change column A into a datetime vector where either 4 rows combine into an hour or 95 rows combine to a day (the same happening to values in M)? I want to be able to calculate the daily/weekly/monthly sum/mean of values in column M.
This is my current plot where x-axis is just the text values from the excel file.
% Time on horizontal X-axis and power [kW] on Y-axis
tickStep = 2000;
[~,xAxis] = xlsread('PV_Ertraege.xlsx','32000 qm','A7:A35046');
yAxis = xlsread('PV_Ertraege.xlsx','32000 qm','M7:M35046') ./1000;
plot(yAxis)
set(gca,'xtick',1:tickStep:numel(xAxis))
set(gca,'xticklabel',xAxis(1:tickStep:numel(xAxis)))
xlim([0 numel(xAxis)+1])
xtickangle(90)
ylabel('Total output [kW]')
From this I have only been able to calculate the yearly sum of M7:M35046, but not any anything else.
I have been trying some solutions that I've found online, but to no luck.
% Tried and failed means of changing to datetime:
X = xlsread('PV_Ertraege.xlsx','32000 qm','A7:A35046') ;
t = datetime(X,'ConvertFrom','excel')
% and
A = xlsread('PV_Ertraege.xlsx','32000 qm','A7:A35046')
dv = datevec(A(:,1)) ;
[days,~,subs] =unique(dv(:,1)) ;
dailysum = accumarray(subs, A(:,1)) ;
Thank you to everyone who might be able to try and help!

채택된 답변

Peter Perkins
Peter Perkins 2019년 10월 30일
First, use readtable, not xlsread. If you have a very recent version, use readtimetable. I'm not clear on exactly what's in your spreadsheet, so I can't say exactly how to do that latter.
"M contains corresponding numeric values for those times." If you mean excel serial date numbders, then readtable and readtimetable should automatically make datetimes in MATLAB for you.
"Column A contains yearly (01.01.-31.12.) time values in text in 15min intervals (01.01. 00:00:00, 01.01. 00:15:00, ...)" If you read these as text, convert to datetime:
>> datetime(["01.01. 00:00:00" "01.01. 00:15:00"],'InputFormat','MM.dd. HH:mm:ss')
ans =
1×2 datetime array
01-Jan-2019 00:00:00 01-Jan-2019 00:15:00
Then make a timetable and use retime. daily, monthly, etc. sums etc. are all a one-liner.
  댓글 수: 1
Tarmo Tukiainen
Tarmo Tukiainen 2019년 11월 4일
Thank you for your help Peter!
I ended up finding a way to just create new datetime vectors and ignored the excel input for x-axis altogether with the following code. Posting these here in case someone runs into a similar problem.
if true
t1 = datetime(2018,1,1,0,0,0) ;
t2 = datetime(2018,12,31,23,45,0) ;
end
timeMi = t1:minutes(15):t2 ; % Original size datetime (15min interval) (1x35040)
timeH = t1:minutes(60):t2 ; % 1-hour interval datetime (1x8760)
And combined the values in my M-column from a 35040x1 double (PVe) to create a 8760x1 double (sub1), to then be used as y-axis against timeH;
x = PVe; % Data
size1 = length(x)/4; % / how many values you want to combine
sub1 = ones(size1,1); % intermediate matrix for averaging every hour
for n = 1:size1 % 35040/4 = 8760
sub1(n) = sum(x(4*(n-1)+1 : (4*n))); % Sums every 4 values to sum every hour
end

댓글을 달려면 로그인하십시오.

추가 답변 (0개)

카테고리

Help CenterFile Exchange에서 Spreadsheets에 대해 자세히 알아보기

제품


릴리스

R2019b

Community Treasure Hunt

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

Start Hunting!

Translated by