Python loop through excel sheets, add sheetname to list, then concat all

Question

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 worksheet name into the appropriate list.

# infile is a filepath variable    
xls = xlrd.open_workbook(infile, on_demand=True)



dfList = []
for sheet_name in xls.sheet_names():
    df = pd.read_excel(infile, sheet_name, header = 0)
    #df['Well_name'] = sheet_name
    dfList.append(df)
    print(sheet_name + " appended.")
    #time.sleep(2)
print("Loop complete")
# Concatenating the appended lists
dfs = pd.concat(dfList, axis=0)

I tried creating a new column in df but that created a length mismatch and it also didn't work because it was constantly rewritten to the last worksheet name in the loop.

Any thoughts or suggestions?

jpp · Accepted Answer · 2018-06-12 23:34:00Z

3

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)

answered Jun 12, 2018 at 23:34

jpp

166k37 gold badges301 silver badges362 bronze badges

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

1 Comment

Chris Over a year ago

Thanks @jpp this did exactly what I wanted!

Collectives™ on Stack Overflow

Python loop through excel sheets, add sheetname to list, then concat all

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