Add File Name to Created Pandas Dataframe From Excel File

Question

I am trying to write a python script to open a list of excel files, grab cell ranges, and put them all in a single dataframe.

After I create the dataframe to the excel read, I want to add the file name and some other to the dataframe related to the file that was opened and read. Essentially, my read code looks like this:

df = [pd.ExcelFile(file).parse('Daily Revenue',skiprows=1,skip_footer=1,index_col=None,parse_cols=(0,4),parse_dates=True,na_values=['N/A'])]

after initial open/read from excel, i get a dataframe that looks like this:

date    rev
2/1     500
2/2     600
2/3     700

i'm looking to modify this dataframe to make it look like:

 date   rev    file       group
 2/1    500    abc.xlsx   first
 2/2    600    abc.xlsx   first
 2/3    700    abc.xlsx   first

Where 'group' is a just the name of the person who sent the file.

How can I change my original df to append those 2 additional columns?

Answer 1

df['file'] = 'abc.xlsx'
df['group'] = 'first'