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?
