How do I add a column to my dataframe that says what sheet name each row is from? Python

Question

I am working with a Dataframe that has five sheets and I want to use four of them. So I can load it in:

df = pd.read_excel('***.xls', sheet_name=['a', 'b', 'c', 'd'])

But now I would like to add a column that says what sheet each row was in, and I am not sure how to do this. I tried something like this

for name, frame in df.items():
        frame['Sheet'] = name
        df = df.append(frame, ignore_index=True)

but I was getting the following error:

AttributeError: 'collections.OrderedDict' object has no attribute 'append'

Any help would be greatly appreciated. Thank you in advance!

Let's say this is what my data looks like after I concat the sheets:

df = pd.concat(pd.read_excel(***.xls, sheet_name=['a', 'b', 'c', 'd'],
                          header=1), ignore_index=True, sort=False)

Concat data

My goal is to add a column that says what sheet each row was from, like so...

Concat data with sheet name row

Hopefully that helps you understand what I am trying to go for.

(Edit) I would also like to know how to do this if I wanted to use all the sheets in a dataframe, but didn't want to list the individual names of each sheet. Thanks!

Answer 1

IIUC, try DataFrame.assign in a list comprehension:

sheets = ['a', 'b', 'c', 'd']

df = pd.concat([pd.read_excel('***.xls', sheet_name=s)
                .assign(sheet_name=s) for s in sheets])

---

Update

If you want to use all sheets and assign a column of sheetname, you could do:

workbook = pd.ExcelFile('***.xls')
sheets = workbook.sheet_names

df = pd.concat([pd.read_excel(workbook, sheet_name=s)
                .assign(sheet_name=s) for s in sheets])