title: Extract String From Files In Directory And Assign It To Dataframe in Python
body: <p>I have 3 Excel files in a folder called:</p>
  
  <ul>
  <li>FY18_Msft Cloud_test.xlsm</li>
  <li>FY19_Msft Cloud_test.xlsm</li>
  <li>FY20_Msft Cloud_test.xlsm</li>
  </ul>
  
  <p>They all have a sheet called "Digital" and I want to merge the data from the three files into a new data frame which I've done with the code below. However, then I also want to add two new columns: one called "Channel" that will read "Digital" for all files and another one called "Fiscal" that will read "FY18" for the data taken from the first file, FY19 from the second file and FY20 from the third file.</p>
  
  <p>I have the for loop below and while I manage to get the get the data merge and the Channel okay I can't figure out a way to add the correct fiscal against each data entry. I extract the name of the fiscal from the file name by taking the string before the first underscore.</p>
  
  <pre><code>digital_df = pd.DataFrame()
  os.chdir(plans_folder)
  
              for infile in glob.glob(plans_folder + "*Msft Cloud*.xlsm"):
                  for fiscal in glob.glob("*Msft Cloud*.xlsm"):
                      data_digital = pd.read_excel(infile, sheetname="Digital", skiprows=9)
                      data_digital.insert(0, "Channel", "Digital")
                      digital_df = digital_df.append(data_digital, ignore_index=True)
                      fiscal = fiscal.split('_', 1)[0]
                      digital_df['Fiscal'] = fiscal
  
  digital_df
  </code></pre>
  
  <p>Any ideas?</p>