AVERAGE VALUES FOR .XLSX AND PLOT MATLAB R2021a

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')

[Steven Lord]

What does "does not run" mean in this context?

• Do you receive warning and/or error messages? If so the full and exact text of those messages (all the text displayed in orange and/or red in the Command Window) may be useful in determining what's going on and how to avoid the warning and/or error.
• Does it do something different than what you expected? If so, what did it do and what did you expect it to do?
• Did MATLAB crash? If so please send the crash log file (with a description of what you were running or doing in MATLAB when the crash occured) to Technical Support so we can investigate.

[Walter Roberson]

data1 = readtable('historico_12.xlsx', 'VariableNamingRule','preserve');
MyDateTime = data1.Date + data1.Time;

The variable names stored in the .xlsx are literally Var1, Var2, and Var3 -- those exact names are stored in the file. No "Date" field, no "Time" field.

Note that if you add the second variable to the first (which is in DateTime format) then the second variable will be treated as fractions of a calendar day. Which appears to be acceptable in this case, but is unusual.

[Willian]

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]

[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');

Answer 1

[dpb]

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 

[Willian]

Thanks, I appreciate the help