How do I identify unique rows based on multiple columns and calculate the average of the rest of the columns?
33 visualizaciones (últimos 30 días)
Mostrar comentarios más antiguos
Leon
el 19 de Oct. de 2021
Comentada: Leon
el 19 de Oct. de 2021
I have a matrix as below:
A = [1 4 3 8; 4 5 6 9; 1 6 3 6; 2 6 9 3; 1 5 3 7];
My goal is to identify rows with both identical Column 1 and identical Column 3 values, and then calculate the average for the rest of the columns, i.e., Column 2 and Column 4, within these duplicate rows. In this example, the duplicate rows would be Rows # 1, 3, and 5. My ending matrix would be:
B = [1 5 3 7; 4 5 6 9; 2 6 9 3];
This is a much simplified example. In reality, I have 35 columns that need to be averaged, and millions of rows. What is the most efficient way of handling this? Do I have to write a loop and process each of the unique rows individually?
Many thanks!
0 comentarios
Respuesta aceptada
Kevin Holly
el 19 de Oct. de 2021
Editada: Kevin Holly
el 19 de Oct. de 2021
A = [1 4 3 8; 4 5 6 9; 1 6 3 6; 2 6 9 3; 1 5 3 7]
I am going to assume that any row that columns 1 and 3 are identical, irregardless of what pair, you want to ignore those rows in the averaging of other columns.
Here is my approach:
t = table(A(:,1),A(:,3))
[C, ia, ic] = unique(t,'rows')
ic==1
A(ic==1,:)
mean(A(ic==1,:))
B = [mean(A(ic==1,:));A(ic~=1,:)]
Your answer:
B = [1 5 3 7; 4 5 6 9; 2 6 9 3]
Without showing work:
t = table(A(:,1),A(:,3));
[~,~,ic] = unique(t,'rows');
B = [mean(A(ic==1,:));A(ic~=1,:)]
Más respuestas (0)
Ver también
Categorías
Más información sobre Loops and Conditional Statements en Help Center y File Exchange.
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!