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?
#1
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!
Quote
#2
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.
Quote
#3
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?
Quote
#4
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.
Quote
#5
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?
Quote
#6
I'll try to get started with what I know and post some sample code. Thanks.
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Dropping a column from pandas dataframe marco_ita 6 493 Sep-07-2019, 08:36 AM
Last Post: marco_ita
  How to drop column in pandas SriMekala 3 376 Aug-26-2019, 06:36 PM
Last Post: snippsat
  Need Help With Filtering Data For Excel Files Using Pandas eddywinch82 9 486 Aug-06-2019, 03:44 PM
Last Post: eddywinch82
  Pandas Import CSV count between numerical values within 1 Column ptaylor520 3 202 Jul-16-2019, 08:13 AM
Last Post: ptaylor520
  pandas change row value an existing column with conditionals Gigux 1 284 Jun-22-2019, 08:04 PM
Last Post: Gigux
  pandas writing to excel .. help anna 0 233 Jun-20-2019, 06:34 AM
Last Post: anna
  How to delete column if entire column values are "nan" Sri 4 528 Apr-13-2019, 12:16 PM
Last Post: Sri
  Python pandas remove default format in excel indra 0 363 Feb-06-2019, 04:48 AM
Last Post: indra
  Create selection box to pass string value based on uniques in Excel column sneakysnek 1 496 Nov-18-2018, 07:29 PM
Last Post: Stefanovietch
  Python read Password protected excel and convert to Pandas DataFrame FORTITUDE 2 4,354 Aug-30-2018, 01:08 PM
Last Post: FORTITUDE

Forum Jump:


Users browsing this thread: 1 Guest(s)