AVERAGE VALUES FOR .XLSX AND PLOT MATLAB R2021a

2 次查看（过去 30 天）
Willian
Willian 2024-10-15
评论： Walter Roberson 2024-10-16
Ran in:
I want to calculate the hour by hour average values of the Var3 column of the attached file, I have tried the following code but I could not solve the problem, then I want to graph the average data vs time
I am trying to run this code for .cvs files but applied it for .xlsx files but the code does not run
data1 = readtable('historico_12.xlsx', 'VariableNamingRule','preserve');
MyDateTime = data1.Date + data1.Time;
Error using . (line 229)
Unrecognized table variable name 'Date'.
MyDateTime.Format = 'yyyy-MM-dd HH:mm:ss';
data2 = [data1(:,1) table(MyDateTime) data1(:,[3:end])];
%%
% Create a new column for the time (round up to the start of the hour)
data2.Hour = dateshift(data2.MyDateTime, 'start', 'hour');
% Group by new time column and calculate average
averageData = groupsummary(data2, 'Hour', 'mean', 'Var3');
% Show results
disp(averageData);
% Graph average hourly data
figure;
plot(averageData.Hour, averageData.mean_Var3, '-');
xlabel('Date & Time');
ylabel('Average value Pressure');
title('Average Hour by Hour');
grid on;
% Save results to a new CSV file
writetable(averageData, 'average_hours_by_hour.csv')
I'm trying to adapt it to the following code:
clear;
clc;
%% obtener información sobre los archivos
S = dir('*.xlsx');
%% construir nombres de archivos de ruta completa
filenames = fullfile({S.folder},{S.name});
%% lee cada archivo en una tabla, almacenado en la matriz de estructura S
%como campo 'datos'
for ii = 1:numel(S) %cuenta cuantos archivos hay que almacenar
S(ii).data = readtable(filenames{ii});
end
%% % combine all tables into one
T = vertcat(S.data);
%% eliminar filas con tiempos duplicados
[t,idx] = unique(T.(1)+days(T.(2)));
T = T(idx,:);
%% plot
figure
plot(t,T.(3),'-','LineWidth',1)
grid on
axis tight
%% Simbología
%figure('Name','Measured Data');
xlabel('time (hours)')
ylabel('pressure')
title('WELL')
grid on
grid minor
hold on
% Save results to a new XLSX file
writetable(averageData, 'average_hours_by_hour.xlsx')
  4 个评论
显示 2更早的评论
Willian
Willian 2024-10-15
Thanks for the answer, I have adapted the code according to the suggestion, but I have an error in the line t.Hour
clear;
clc;
%%
S = dir('*.xlsx');
%%
filenames = fullfile({S.folder},{S.name});
%%
%como campo 'datos'
for ii = 1:numel(S) %cuenta cuantos archivos hay que almacenar
S(ii).data = readtable(filenames{ii});
end
%% % combine all tables into one
T = vertcat(S.data);
%%
[t,idx] = unique(T.(1)+days(T.(2)));
T = T(idx,:);
%%
% Create a new column for the time (round up to the start of the hour)
t.Hour = dateshift(t, 'start', 'hour');
% Group by new time column and calculate average
averageData = groupsummary(t, 'Hour', 'mean', 'Var3');
% Show results
disp(averageData)
% Graph average hourly data
figure (1);
plot(averageData.Hour, averageData.mean_Var3, '-k');
xlabel('Date & Time');
ylabel('Average Pressure Psi');
title('Average Hour by Hour');
ax = gca;
ax.YAxis.Exponent =0;
grid minor
grid on
I intend to get average results per hour
Walter Roberson
Walter Roberson 2024-10-16
[t,idx] = unique(T.(1)+days(T.(2)));
That is creating t as a datetime array
t.Hour = dateshift(t, 'start', 'hour');
That is attempting to create a field named Hour within the datetime array t
You probably wanted
T.Hour = dateshift(t, 'start', 'hour');
and
averageData = groupsummary(T, 'Hour', 'mean', 'Var3');

请先登录，再进行评论。

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

采纳的回答

dpb
dpb 2024-10-16
Ran in:
tH=readtable('historico_12.xlsx');
tH.Properties.VariableNames(1)={'Date'};
tH.Date.Format='yyyy-MM-dd HH:mm:ss';
height(tH)
ans = 111317
[head(tH,5);tail(tH,5)]
ans = 10x3 table
Date Var2 Var3 ___________________ __________ ______ 2024-10-01 00:00:00 4.6296e-05 1287.1 2024-10-01 00:00:00 0.00016204 1288 2024-10-01 00:00:00 0.00027778 1287.9 2024-10-01 00:00:00 0.00039352 1287.8 2024-10-01 00:00:00 0.00050926 1287.8 2024-10-14 00:00:00 0.0012847 1280.3 2024-10-14 00:00:00 0.0014005 1280.3 2024-10-14 00:00:00 0.0015162 1280.1 2024-10-14 00:00:00 0.0016319 1280.2 2024-10-14 00:00:00 0.0017477 1280
[min(tH.Var2) max(tH.Var2) mean(diff(tH.Var2))*1E6]
ans = 1×3
0 1.0000 0.0153
<mw-icon class=""></mw-icon>
<mw-icon class=""></mw-icon>
plot(tH.Var2)
nDays=day(tH.Date(end))-day(tH.Date(1))==numel(find(diff(tH.Var2)<0))
nDays = logical
1
So, it appears @Walter Roberson's hypothesis is correct; we'll add the time to the day. As he notes, it's unusual but appears to be what was done when writing the timestamp.
It would seem to do the hourly averages, the easiest route would be to just convert to the timetable and retime...
tH.Properties.VariableNames(2:3)={'DayFraction','Unknown'};
tH.Date=tH.Date+tH.DayFraction;
tH=removevars(tH,'DayFraction');
tH=table2timetable(tH);
head(tH,5)
Date Unknown ___________________ _______ 2024-10-01 00:00:04 1287.1 2024-10-01 00:00:14 1288 2024-10-01 00:00:24 1287.9 2024-10-01 00:00:34 1287.8 2024-10-01 00:00:43 1287.8
tHrAvg=retime(tH,'hourly',@mean);
[head(tHrAvg);tail(tHrAvg)]
ans = 16x1 timetable
Date Unknown ___________________ _______ 2024-10-01 00:00:00 1287.3 2024-10-01 01:00:00 1286.9 2024-10-01 02:00:00 1286.8 2024-10-01 03:00:00 1286.8 2024-10-01 04:00:00 1287.1 2024-10-01 05:00:00 1287.3 2024-10-01 06:00:00 1287.2 2024-10-01 07:00:00 1287.5 2024-10-13 17:00:00 1283 2024-10-13 18:00:00 1283 2024-10-13 19:00:00 1282.7 2024-10-13 20:00:00 1282.6 2024-10-13 21:00:00 1282.2 2024-10-13 22:00:00 1281.2 2024-10-13 23:00:00 1280.3 2024-10-14 00:00:00 1280.2

更多回答（0 个）

类别

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

标签

Community Treasure Hunt

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

Start Hunting!

Translated by