title: using filename as column headers in data frame
body: <p>I had multiple excel files and i needed to collate one column from all of them into a dataframe. I used the below code:</p>
  <pre><code>my_excel_files = glob.glob(r&quot;C:\Users\......\Documents\*.xlsx&quot;)
  
  total_dataframe = pd.DataFrame() 
  
  for file in my_excel_files:
      df = pd.read_excel(file, header = 1) 
      new_df = df['Comments']
      total_dataframe = pd.concat([total_dataframe, new_df], axis=1)
  </code></pre>
  <p>this code gets all the 'Comments' columns from all my excel files and appends them together in total_dataframe. the issue is the columns in that data frame are all 'Comments' so i can't distinguish where each column came from.</p>
  <p>Is there a way to use the full file names of each excel as the column header instead so that I'd know which excel each column came from</p>