Python Loop Through Excel Sheets, Add Sheetname To List, Then Concat All

I am looping through Excel worksheets and appending them to a list. When the loop finishes, I use Pandas to concat to a single dataframe. The problem I'm having is adding the works

Solution 1:

Seems like you are meeting some scoping issues. One way to avoid this problem is to use a list comprehension. You can also use pd.DataFrame.assign to add a series within your list comprehension:

dfList = [pd.read_excel(infile, sheet_name, header=0).assign(Well_name=sheet_name) \
          for sheet_name in xls.sheet_names()]

dfs = pd.concat(dfList, axis=0)