loop through a list of excel sheets and create different dataframes

January 19, 2023

Basically, I have an excel file which I have to read all the sheets apart from the first two. I have tried using a list on the sheet_name parameter, however it returns as invalid sintax. Also tried using this

sheets_dict = pd.read_excel('South America services data.xlsx', sheet_name=None)

all_sheets = []
for name, sheet in sheets_dict.items():
    sheet['sheet'] = name
    sheet = sheet.rename(columns=lambda x: x.split('\n')[-1])
    all_sheets.append(sheet)

full_table = pd.concat(all_sheets)
full_table.reset_index(inplace=True, drop=True)

However, I would like to merge them.

I tried using

sheets_dict = pd.read_excel('South America services data.xlsx', sheet_name=([2:]))

But it gives me a invalid sintax error.
This is the error

File "C:\Users\Windows\AppData\Local\Temp\ipykernel_4036\1764796534.py", line 1
    sheets_dict = pd.read_excel('South America services data.xlsx', sheet_name=([2:]))
                                                                                  ^
SyntaxError: invalid syntax

>Solution :

Try using pd.ExcelFile:

xl = pd.ExcelFile("South America services data.xlsx")

all_sheets = []
for i , name in enumerate(xl.sheet_names):

   if i>=2: # Start reading from the third sheet
       sheet = xl.parse(name)
       sheet['sheet'] = name
       sheet = sheet.rename(columns=lambda x: x.split('\n')[-1])
       all_sheets.append(sheet)

full_table = pd.concat(all_sheets)
full_table.reset_index(inplace=True, drop=True)