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

1 ビュー (過去 30 日間)
Tarmo Tukiainen 2019 年 10 月 28 日
コメント済み: Tarmo Tukiainen 2019 年 11 月 4 日
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;
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:
t = datetime(X,'ConvertFrom','excel')
% and
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 2019 年 10 月 30 日
"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 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

サインインしてコメントする。

R2019b

### Community Treasure Hunt

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

Start Hunting!