excel sheets name in pandas dataframe

Question

I have an Excel workbook that I have already loaded and put all the sheets together, now I would like to add a column where I have the name of each original sheet, I don't know if I have to do it before I put everything together, and if that's how I could do it , I am using pandas. This is my code so far, I want the sheet name or number to be in the "Week" column.

xlsx= pd.ExcelFile('archivo.xlsx')
hojas=[]
for hojaslibro in xlsx.sheet_names:
    hojas.append(xlsx.parse(hojaslibro))
estado=pd.concat(hojas,ignore_index=True)

estado['Week']=0

>I would like to add a column where I have the name of each original sheet; If i didn't misunderstood the question, just grab the sheet names and do estado.Week = <all_sheet_names_list>? — Aditya
– Aditya, Commented Jun 21, 2020 at 4:07

Ji Wei · Accepted Answer · 2020-06-21 04:29:18Z

4

This should work:

xl = pd.ExcelFile('archvio.xlsx')
df_combined = pd.DataFrame()
for sheet_name in xl.sheet_names:
    df = xl.parse(sheet_name)
    df['Week'] = sheet_name       # this adds `sheet_name` into the column `Week`
    df_combined = df_combined.append(df)

answered Jun 21, 2020 at 4:29

Ji Wei

8819 silver badges20 bronze badges

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

1 Comment

KLM Over a year ago

The answer of "Ji Wei" worked perfectly for me, thank you very much!

Collectives™ on Stack Overflow

excel sheets name in pandas dataframe

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related