CODEWITHSUNDEEP
pythonexcelpandas
KLM
KLM

Reputation: 51

excel sheets name in pandas dataframe

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

Upvotes: 2

Views: 9126

Answers (1)

Ji Wei
Ji Wei

Reputation: 881

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)

Upvotes: 4

Related Questions