Pandas - Loop through sheets

Question

I have 5 sheets and created a script to do numerous formatting, I tested it per sheet, and it works perfectly.

import numpy as np
import pandas as pd
           
FileLoc = r'C:\T.xlsx'
Sheets = ['Alex','Elvin','Gerwin','Jeff','Joshua',]

df = pd.read_excel(FileLoc, sheet_name= 'Alex', skiprows=6) 
df = df[df['ENDING'] != 0]
df = df.head(30).T
df = df[~df.index.isin(['Unnamed: 2','Unnamed: 3','Unnamed: 4','ENDING' ,3])]
df.index.rename('STORE', inplace=True)
df['index'] = df.index

df2 = df.melt(id_vars=['index', 2 ,0, 1] ,value_name='SKU' )
df2 = df2[df2['variable']!= 3]

df2['SKU2'] = np.where(df2['SKU'].astype(str).fillna('0').str.contains('ALF|NOB|MET'),df2.SKU, None)
df2['SKU2'] = df2['SKU2'].ffill()
df2 = df2[~df2[0].isnull()]
df2 = df2[df2['SKU'] != 0]

df2[1] = pd.to_datetime(df2[1]).dt.date
df2.to_excel(r'C:\test.xlsx', index=False)

but when I assigned a list in Sheet_name = Sheets it always produced an error KeyError: 'ENDING'. This part of the code:

Sheets = ['Alex','Elvin','Gerwin','Jeff','Joshua',]
df = pd.read_excel(FileLoc,sheet_name='Sheets',skiprows=6)

Is there a proper way to do this, like looping?

My expected result is to execute the formatting that I have created and consolidate it into one excel file.

NOTE: All sheets have the same format.

@АлексейР -- Thanks for the reply but it's not working, I got this error KeyError: 'ENDING — Trunks
– Trunks, Commented Nov 1, 2021 at 3:43
Read the sheet using : sheets_dict = pd.read_excel(FileLoc, sheet_name= None) Access using: sheet = sheets_dict['Alex'] — itas97
– itas97, Commented Nov 1, 2021 at 3:56

Minura Punchihewa · Accepted Answer · 2021-11-01 03:59:37Z

2

In using the read_excel method, if you give the parameter sheet_name=None, this will give you a OrderedDict with the sheet names as keys and the relevant DataFrame as the value. So, you could apply this and loop through the dictionary using .items().

The code would look something like this,

dfs = pd.read_excel('your-excel.xlsx', sheet_name=None)
for key, value in dfs.items():
  # apply logic to value

If you wish to combine the data in the sheets, you could use .append(). We can append the data after the logic has been applied to the data in each sheet. That would look something like this,

combined_df = pd.DataFrame()
dfs = pd.read_excel('your-excel.xlsx', sheet_name=None)
for key, value in dfs.items():
  # apply logic to value, which is a DataFrame
  combined_df = combined_df.append(sheet_df)

answered Nov 1, 2021 at 3:59

Minura Punchihewa

2,1154 gold badges32 silver badges72 bronze badges

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

9 Comments

Trunks Over a year ago

sorry i'm not familiar with the loop .. where will I put the Key and Value in my DF ? ` for key, value in dfs.items():` df=pd.read_excel(FileLoc,sheet_name= 'Alex', df = df[df['ENDING']!= 0] df = df.head(30).T

Minura Punchihewa Over a year ago

When you use dfs = pd.read_excel('your-excel.xlsx', sheet_name=None), all of the sheets will be read and you will have a dictionary like object where the keys are the sheet names and the values are DataFrames for each sheet. As a result, when you do for key, value in dfs.items():, key will be your sheet name and value will be your DataFrame. So, then you can just apply your logic to value, which is the DataFrame for that sheet. Does that make sense?

Trunks Over a year ago

thanks for the reply. I'm trying to assign a column for sheet name value['Sales Man'] = key why is not working dateutil.parser._parser.ParserError: Unknown string format: Alex?

Trunks Over a year ago

btw it's working but I'm still having this error KeyError: 'ENDING'

Minura Punchihewa Over a year ago

Is ENDING a column that is available in your Excel sheets? A KeyError is raised when the column is not available.

|

Collectives™ on Stack Overflow

Pandas - Loop through sheets

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related