Iterating through excel row using pandas

Question

To begin with I would like to mention that I new to python. I am trying to iterate over rows in pandas. My data comes from an excel file and looks like this:

I would like to create a loop that calculates the mean of specific rows. For instance row 0,1,2 and then 9,10,11 and so on.

What I have already done:

import pandas as pd
import numpy as np 
df = pd.read_excel("Excel name.xlsx")
for i in range([0,1,2],154,3) 
x =df.iloc[[i]].mean()
print(x)

But I am not getting results. Any idea? Thank you in advance.

What I am doing and my code actually works is:

x1= df.iloc[[0,1,2]].mean()
x2= df.iloc[[9,10,11]].mean()
x3= df.iloc[[18,19,20]].mean()
x4= df.iloc[[27,28,29]].mean()
x5= df.iloc[[36,37,38]].mean()
x6= df.iloc[[45,46,47]].mean()
....
....
....
x17= df.iloc[[146,147,148]].mean()

What if I had 100 x? It would be impossible to code. So my question is if there is a way to automate this procedure with a loop.

Is there a concrete reason to loop? The general paradigm for this is to load the whole dataframe, calculate all row means in 1 operation, then index the particular rows you want (or vice versa). — tdy, Mar 10 '21 at 08:11
Yes. I have grouped my data into three major groups. Each group consists of 17 subgroups and each subgroup contains three measurements. Furthermore, each subgroup rotates with the two others in a specific manner. In particular, the pattern is group A, group B, group C and again group A, B,C and so on. Thank you — Jack21, Mar 10 '21 at 10:34

jezrael · Answer 1 · 2021-03-10T08:30:39.463

Dont loop, rather select all rows by using little maths - here integer division with modulo by 9 and selecting 0,1,2 values in Index.isin, and then aggregate mean:

np.random.seed(2021)
df = pd.DataFrame(np.random.randint(10, size=(20, 3)))

mask = (df.index % 9).isin([0,1,2])
print(df[mask].groupby(df[mask].index // 9).mean())
          0         1         2
0  4.000000  5.666667  6.666667
1  3.666667  6.000000  8.333333
2  6.500000  8.000000  7.000000

Detail:

print(df[mask])
    0  1  2
0   4  5  9
1   0  6  5
2   8  6  6
9   1  6  7
10  5  6  9
11  5  6  9
18  4  9  7
19  9  7  7

Iterating through excel row using pandas

1 Answers1