Iterating excel sheets with python

Someone recommended Pandas, but I have used openpyxl to do similar things.

First you can import openpyxl and setup the worksheet like this:

import openpyxl #openpyxl will need to be installed via pip
workbook = openpyxl.load_workbook(PATHTOYOURWORKSHEET)

to access specific sheets of the workbook, as you mentioned each consumer has a seperate sheet, you do this:

worksheet1 = workbook["THE NAME OF YOUR SHEET"]
#you can call each whatever you want, this is how you get into a specific sheet.

Then to select a specific column you could do something like this although this is for a row, not a column, but if you read openpyxl docs i think its just iter_col instead, which just iterates every single cell in the row under your parameters:

sheet1Row2 = worksheet1.iter_rows(min_row=2, max_row=2, min_col=1, max_col=sheet1.max_column, values_only=True)

then using this, you could do something like for each 'sheet' in the workbook, target the row you want and then do whatever you want with it, add it to an array, save each to a new variable, etc.

Hope it helps

you can read the entire dataframe without specifying the sheet which will give you a dictionary dataframe.

you can then use a list comprehension and loop.

MCVE

import pandas as pd 

df1 = pd.get_dummies(list('ABCD'))

df2 = df1
df2['B'] = 5

writer = pd.ExcelWriter('workbook.xlsx')
df1.to_excel(writer,sheet_name='test1')
df2.to_excel(writer,sheet_name='test2')
writer.save()

print(df1)

   A  B  C  D
0  1  0  0  0
1  0  1  0  0
2  0  0  1  0
3  0  0  0  1

print(df2)

   A  B  C  D
0  1  5  0  0
1  0  5  0  0
2  0  5  1  0
3  0  5  0  1

df = pd.read_excel('workbook.xlsx',sheet_name=None)

print(df)

{'test1':    Unnamed: 0  A  B  C  D
0           0  1  5  0  0
1           1  0  5  0  0
2           2  0  5  1  0
3           3  0  5  0  1, 'test2':    Unnamed: 0  A  B  C  D
0           0  1  5  0  0
1           1  0  5  0  0
2           2  0  5  1  0
3           3  0  5  0  1}


new = pd.concat(df.values())[["A", "C"]]

print(new)

   A  C
0  1  0
1  0  0
2  0  1
3  0  0
0  1  0
1  0  0
2  0  1
3  0  0