CODEWITHSUNDEEP
pythonpandas
stack user
stack user

Reputation: 300

Pandas merge different excel sheets into one along with a new column derived by sheet name

I need to merge different excel sheets into one and also add a new column as a corresponding sheet name

The below code merge all sheets, but how do I add a sheet name as a column ??

import pandas as pd
df = pd.concat(pd.read_excel(r"C:\\Users\\xx\\FC_List.xlsx", sheet_name=None), ignore_index=True)
print(single_df)
df.to_csv(r"C:\\Users\\Users\\FC_List.csv", index=False)

below code fetch sheet name

import pandas as pd
df = pd.read_excel(r"C:\\Users\\cc\\FC_List.xlsx", None);
df.keys()

can u advise how to add both together as a new column

Upvotes: 0

Views: 363

Answers (1)

Umar.H
Umar.H

Reputation: 23099

Split it into steps.

 import pandas as pd
 dfs = pd.read_excel(r"C:\\Users\\xx\\FC_List.xlsx", sheet_name=None)
 df = pd.concat(dfs,keys=dfs.keys())

This will set your index as the column name, you can then reset it and rename it.

you could also do something like.

df = pd.concat([sheet.assign(src_sheet=sheet_name) for sheet_name,sheet in dfs.items()])

Upvotes: 1

Related Questions