Styjun

I have a script that reads in hundreds of excel files from a directory. The files are of similar format (all are .xls or .xlsx, therefore readable by pandas) and the script converts them to pandas dataframes to write them back out in a format that I desire. The first 10 rows of each excel files are not needed, and I don't need all the empty rows and columns,(Example spreadsheet: I need all the data from row 11 down.) so I strip them out using df.iloc[9:, 0:47] (47 is the max extent of columns) and use df.dropna(how='all') to drop all the rows with empty values.

for fn in os.listdir(path):
 file = os.path.join(path, fn)
 if os.path.isfile(file):
 ##I need the second sheet of each spreadsheet
 data = pd.read_excel(file, sheet_name=1, index=False)
 relevantData = data.iloc[9:, 0:47] ##This removes the first 10 rows of 
 ##useless data, and caps the 
 ##columns at 48.
 relevantData.dropna(how='all')

However, it has come to my attention that some of the excel files are one whole row short. (Example Image.)Thus the headers will not be the same and the data will not append in further processing, and the script stops because of string vs float interpretation errors(the header columns are all strings and the rest of the data is mostly number values). I want to be able to filter out these files, or dynamically add another row to make sure they conform to the other majority of files.

Is there a way to include in a for-loop a check of the df.iloc[0,0] position (even after I have already used df.iloc previously in the code), to contain a substring that would indicate that the file is missing a row? If the value of the dataframe cell has the substring 'act', then I know that the format is correct and it can continue for further processing. If the substring does not exist (aka the file is 1 row short or a nonstandard format) then either add a row at the 9th position index (if possible), or at the least spit these files out to preprocess manually.

I have tried using if df.iloc[row position, column position].str.contains('act'):, but it appears the placement of this code in the script renders the dataframe value as not a series? It throws exception: AttributeError: 'str' object has no attribute 'str'.

Then I tried a different approach: if sub in df.iloc[0,0]: (where sub is a variable = 'act'.
But even when 'act' existed in that position, the script was sending the values to the False portion of the for loop, however I need it to send it to the True route.
(Also tried if sub in df[0,0]:, this throws error: KeyError: (0, 0))

import os
import pandas as pd


dfList = []
path = 'H:DirectoryWithExcelFiles'
newpath = 'H:FolderWithNewFiles_ThatContain_act'
newpath2 = 'H:FolderWithNonStandardFiles_DontContain_act'


for fn in os.listdir(path):
 file = os.path.join(path, fn)
 if os.path.isfile(file):
 ##I need the second sheet of each spreadsheet
 data = pd.read_excel(file, sheet_name=1, index=False)
 relevantData = data.iloc[9:, 0:47] ##This removes the first 10 rows of 
 ##useless data, and caps the 
 ##columns at 48.
 relevantData.dropna(how='all')
 sub = 'act'


 if sub in relevantData[0,0]:
 # Create a Pandas Excel writer using XlsxWriter as the engine.
 writer1 = pd.ExcelWriter('H:FolderWithNewFiles_ThatContain_act\' + fn, engine='xlsxwriter')

 # Convert the dataframe to an XlsxWriter Excel object.
 relevantData.to_excel(writer1, sheet_name='Sheet1', index=False, header=None)

 # Close the Pandas Excel writer and output the Excel file.
 writer1.save()

 else:
 ## Ideally I would want to add the new row to the data frame here at 
 ##9th position and then send back to the beginning of the loop.
 writer2 = pd.ExcelWriter('H:FolderWithNonStandardFiles_DontContain_act\' + fn, engine='xlsxwriter')
 # Convert the dataframe to an XlsxWriter Excel object.
 relevantData.to_excel(writer2, sheet_name='Sheet1', index=False, header=None)

 # Close the Pandas Excel writer and output the Excel file.
 writer2.save()

I expect that if the substring 'act' is in the data value at df[0,0] that the files should be outputted to newpath1, however I can only get the files to pass in the else: statement (newpath2), even though when printing the value before passing it through the 2nd if loop, print (relevantData.iloc[0,0]) the value clearly contains the substring 'act' within the string 'Action Item/Dig #'(Example picture of positon clearly having the substring.)

Does anyone have any solutions as to why the for loop will not recognize the iloc[] positioning and validate if the string exists? I can provide sample spreadsheets if asked.