How to create an excel reading loop using python

Question

I am reading an excel file and I was wondering how I could create a loop that reads specific rows based on a pattern. Let's say for example I want to read the first three rows from my excel sheet (which correspond to 0,1,2) and then read rows 10,11,12 (which correspond to 9,10,11) and so on. The total number of rows is 156.

Hypothesis:
import numpy as np 
import pandas as pd 
df = pd.read_excel("My Excel.xlsx")
a = df.iloc[[0,1,2]]
b = [x + 1 for x in a] 



My code as it is right now.

import numpy as np 
import pandas as pd 
df = pd.read_excel("My Excel.xlsx")
A1 = df.iloc[[0,1,2]].mean() #This is my data transformation
A11 = 1 /(numpy.log10(A2))  # This is also 
A2 = df.iloc[[9,10,11]].mean()
A22 = 1/(numpy.log10(A2)).mean()
....
.... Doing this procedure for other teams of data (3 rows each team)
A17 = df.iloc[[146,147,148]].mean()
A177 = 1/(numpy.log10(A17))

So my question is how can I create a loop choosing specific rows out of my Excel file and applying my transformations to it without doing each one at a time. What if I had 1000 data; It would be a disaster.

Thank you for your time. With respect!

What are you trying to do? Loops in python are the last resort.... — gtomer
– gtomer, Commented Feb 17, 2021 at 21:58
I have updated my question, I hope that I was more clear this time. Thank you again. — Jack21
– Jack21, Commented Feb 18, 2021 at 8:21
You select [0,1,2] and then [9,10,11], why you jump from 2 to 9, what is the logic to automate that? — Rafael Valero
– Rafael Valero, Commented Feb 18, 2021 at 8:28
Rafael Valero The data represent NIR spectra and each group (0,1,2 and 9,10,11 and so on) represents specific and different measurements. For example, 0,1,2 and the rest stands for lipids. Rows 3,4,5 stands for protein and so on. Yes, I am not dealing a problem with reading pandas dataframe. With respect. — Jack21
– Jack21, Commented Feb 18, 2021 at 9:11

Rafael Valero · Accepted Answer · 2021-02-17 23:08:35Z

1

I think perhaps the easier is to upload all the data into a pandas dataframe and work there using the index of the dataframe.
Perhaps this is also interesting. As in here https://stackoverflow.com/a/43257810/7127519

import xlrd
workbook = xlrd.open_workbook('All Train data.xlsx')
worksheet = workbook.sheet_by_index(0)
worksheet.cell(0, 0).value

I was using this for a very large file and worked for me. So I think is not reading all the sheet, but I am not 100% sure, on that.

answered Feb 17, 2021 at 23:08

Rafael Valero

2,83623 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jack21 Over a year ago

That's what I did but still cannot make the row selection automatic. I have to choose the rows by typing code for each one of them. I have updated my code so check it out if you don't mind. Thank you.

Collectives™ on Stack Overflow

How to create an excel reading loop using python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related