I am writing a program to calculate the mean between rows (with several conditions)of each column from columns D to G. In this program, as shown in the picture below, the calculation for the mean can only be done when columns A, B, and C are having the same content. Hence, row 2 and row 6; row 3 and row 7; row 4 and row 8; row 5 and row 9 have the same title, PC, and rd/wr, respectively. I did some research and I know that the fact that python counts row number one as 0. The index should have a form of n+1.
This is my code:
import pandas as pd
import numpy as np
df = pd.read_excel('Report_testing.xlsx')
x = df.iloc[[1, 5], [2, 6], [3, 7], [4, 8]].mean() #I hardcode the location as I still have no idea to set the condition from Columns A to C
print(x)
Expected Output:
Test_1 PC0 Write Latency 89 1844 628.5 292
Test_1 PC1 Write Latency 90.5 2383 669.5 362.5
Test_2 PC0 Write Latency 89 1527 577.5 275
Test_2 PC1 Write Latency 90.5 1513 577.5 271
However, when I compile my code, it has an error and the mean cannot be calculated. How should I amend my code so that the mean can be processed accordingly? I am also having trouble writing the conditions so that the mean only processes the row with the same content from columns A to C.
Content of Excel File:
title pc rd/wr Min Max Avg Std_dev
Test_1 PC0 Write Latency 88 1838 634 297
Test_1 PC1 Write Latency 92 2363 661 369
Test_2 PC0 Write Latency 90 1524 576 273
Test_2 PC1 Write Latency 94 1526 568 267
Test_1 PC0 Write Latency 90 1850 623 287
Test_1 PC1 Write Latency 89 2403 678 356
Test_2 PC0 Write Latency 88 1530 579 277
Test_2 PC1 Write Latency 87 1500 587 275
Thank you in advance.