'how to combine many excel tabs in one tab and create a new column with the name of every tab

I have excel sheet that have many tabs with different names with the same columns, I need to merge all those tabs in one df and create a new column "Queue" to declare every value to it's queue.

The problem here that I can't do this actions in all tabs at once

The Original sheet

The expected sheet

Solution 1:^[1]

This should work although you will need to fix the workbook location.

This adds the sheet name to the DataFrames within the dict and then concats the DataFrames into one.

import pandas as pd

workbook_location = 'Book1.xlsx'
sheets = ['Sheet1', 'Sheet2']
# sheets = ['ALMB-After Sales', 'ALMB-Corp', 'ALMB-Sales', 'CS', 'ALMB-OGOV', 'Flights', 'Retail', 'Sales']

inbound_new_calls = pd.read_excel(workbook_location, sheet_name = sheets)

for sheet, values in  inbound_new_calls.items():
    values['Queue'] = sheet

pd.concat(inbound_new_calls).groupby(['Queue', 'Date'])['Offered'].sum()

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Cole

'how to combine many excel tabs in one tab and create a new column with the name of every tab

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]