Bottom Page

Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
 How to use the excel filename as a value to populate new column, using Pandas?
I'm new to Python. I have about 50 excel workbooks that all have different names e.g. 012018 CBA, 022018 CBA, etc. For each, I would like to insert a new column called "Date" and use the first 6 digits "012018" of the excel filename as the value to populate the entire Date column. I would like to accomplish this using Pandas. Thank you!
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.
Thanks, but maybe I didn't explain it very well. I have 50 Excel files with a naming sequence of 012018 CBA, 022018 CBA, 032018 CBA, 042018 CBA, etc. For each file, I want to use the first 6 digits of the filename to populate the new column within that particular file ... Then do the same for the all the others.

For example, first file called "012018 CBA", I want to insert a new column called "Date" and use the "012018" to populate that column. Next file called "022018 CBA", I want to insert a new column called "Date" and use the "022018" to populate that column. Next file called "032018 CBA", I want to insert a new column called "Date" and use the "032018" to populate that column, etc for all 50 files.

Make sense?
Your files have regular names, so you can extract desired dates using index slicing, e.g. date = filename[:6].

(Jun-26-2019, 10:32 AM)Dequanharrison Wrote: I want to insert a new column called "Date" and use the "032018" to populate that column, etc for all 50 files.

So, you will get a data frame with at least 50 columns that have the same name Date? In any case, you will likely use either os.walk or glob.glob to get all file names. You can read about these modules in official docs here and here.

This isn't to be a hard problem, so it would be better if you post some code to show your efforts on solving the problem.
Not looking to create a datadrame with 50 columns, but rather end up with 50 dataframes/Excel files woth the additional new "date" column filled with the filename of the original file. Saved as a CSV.

Make sense?
I'll try to get started with what I know and post some sample code. Thanks.

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Pandas DataFrame and unmatched column sritsv19 0 131 Jul-07-2020, 12:52 PM
Last Post: sritsv19
  Assigning Column nunique values to another DataFrame column Pythonito 0 134 Jun-25-2020, 05:04 PM
Last Post: Pythonito
  Pandas - Dynamic column aggregation based on another column theroadbacktonature 0 209 Apr-17-2020, 04:54 PM
Last Post: theroadbacktonature
  Add column to CSV using Pandas nsadams87xx 2 335 Apr-15-2020, 08:41 PM
Last Post: snippsat
  add formatted column to pandas data frame alkaline3 0 232 Mar-22-2020, 06:44 PM
Last Post: alkaline3
  itertuples, new column, datetime, pandas karlito 6 665 Nov-29-2019, 11:07 AM
Last Post: karlito
  sort values of a column pandas karlito 2 583 Oct-22-2019, 06:11 AM
Last Post: karlito
  Dropping a column from pandas dataframe marco_ita 6 4,697 Sep-07-2019, 08:36 AM
Last Post: marco_ita
  How to drop column in pandas SriMekala 3 844 Aug-26-2019, 06:36 PM
Last Post: snippsat
  Need Help With Filtering Data For Excel Files Using Pandas eddywinch82 9 1,137 Aug-06-2019, 03:44 PM
Last Post: eddywinch82

Forum Jump:

Users browsing this thread: 1 Guest(s)