How to use the excel filename as a value to populate new column, using Pandas?

[scidam]

If your data frame is defined and prefilled, you can create a new column and fill it as follows df['Date'] = 'desired value'. This automatically creates Date column in the data frame and fills it with desired value.

If you want to incorporate data from all excel files into one data frame, your algorithm might be the following:

1. Define an empty list, e.g., named acc (it will be used later).
   
2. Use os.path.walk (os.walk in Py3) or glob.glob to iterate over all files;
   
3. Load data from each file using pandas, e.g. pandas.read_csv, pandas.read_excel;
   
4. Once you iterating files, you know their names; Let filename is the current filename of a file being loaded into df; You can just do df['Dates'] = filename;
   
5. Append each df to acc list;
   
6. Use pd.concat to combine all dfs stored in acc into a new data frame.