Averaging of rows from excel table

조회 수: 4 (최근 30일)
Katelyn
Katelyn 2024년 4월 16일
댓글: Katelyn 2024년 4월 16일
Hi! I have some excel data as shown in the picture. I would like to write a code where Matlab takes the average of the two rows that have both the same name and date and combines these two rows into one row with the value diaplayed as the average of the two combined values. I have highlighted in this example the two rows that have the same Name and Date with differing values. I have also included a picture of what the desired output would look like.
This is the input data
This is the desired output data.
Thank you!

채택된 답변

Ayush Anand
Ayush Anand 2024년 4월 16일
Hi,
You can read the data into MATLAB using "readtable" and extract the unique combinations of "Name" and "Date" using the "unique" function. Iterating through the unique combination groups and averaging the values for each group should give you the desired answer. Here's how you can do the same:
% Read the Excel Data
filename = 'temp.xlsx'; % Specify your Excel file name
dataTable = readtable(filename);
% Converting 'Date' to datetime format:
dataTable.Date = datetime(dataTable.Date,'InputFormat','MM-dd-yyyy');
%Identify Unique Combinations of Name and Date
[uniqueGroups, ~, groupIndices] = unique(dataTable(:, {'Name', 'Date'}), 'rows');
% Average the Values for Each Unique Combination
% Initialize an array to store the averaged values
averagedValues = zeros(height(uniqueGroups), 1);
for i = 1:height(uniqueGroups)
% Find rows belonging to the current group
currentGroupRows = groupIndices == i;
% Calculate the average value for the current group
averagedValues(i) = mean(dataTable.Value(currentGroupRows));
end
% Create a New Table with Averaged Values
dateFormat = "MM-dd-yyyy";
resultTable = [uniqueGroups, table(averagedValues, 'VariableNames', {'AverageValue'})];
resultTable.Date = string(resultTable.Date, dateFormat);
% write the result to a new Excel file
outputFilename = 'averaged_data.xlsx';
writetable(resultTable, outputFilename);
Read more about the unique function here: https://www.mathworks.com/help/matlab/ref/double.unique.html
  댓글 수: 1
Katelyn
Katelyn 2024년 4월 16일
This worked for me, thank you so much!

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

추가 답변 (0개)

카테고리

Help CenterFile Exchange에서 Data Import from MATLAB에 대해 자세히 알아보기

제품


릴리스

R2023b

Community Treasure Hunt

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

Start Hunting!

Translated by