title: Performance evaluating excel cells with python
body: <p>I am trying to compare 2 columns of an excel file with a 2D-matrix row by row with python. My excel file contains 20'100 rows and the computing time via Pycharm is more than 1 hour. Is there any way how to do these value comparisons more time efficient?</p>
  
  <pre><code>import openpyxl as xl
  from IDM import idm_matrix
  
  # load and create excel file
  wb = xl.load_workbook('Auswertung_C33.xlsx')
  result_wb = xl.Workbook() #new workbook for results
  result_sheet = result_wb.create_sheet('Ergebnisse') #create new sheet in result file
  result_wb.remove(result_wb['Sheet'])
  sheet = wb['TriCad_Format']
  
  # copy 1st row
  first_row = sheet[1:1]
  list_first_row =[]
  for item in first_row:
      list_first_row.append(item.value)
  result_sheet.append(list_first_row)
  
  # Value check
  for row in range(2, sheet.max_row + 1):
      row_list = []
      for col in range(1, sheet.max_column + 1):
          cell = sheet.cell(row, col)
          row_list.append(cell.value)
      for matrix in idm_matrix:
          if row_list[7] is None:
              continue
          elif matrix[0] in row_list[7]:
              if row_list[14] is None or matrix[1] != row_list[14]:
                  result_sheet.append(row_list)
  
  
  print("saving file...")
  result_wb.save('Auswertung.xlsx') #saves the file in a new wb
  print("Done!")
  </code></pre>
  
  <p>Thanks for your help!
  Alex</p>
  
  <p>----- Sample of Data ------</p>
  
  <pre class="lang-none prettyprint-override"><code>Input:
  
  BEZ                 | _IDM
  Schirmsprinkler-SU5 | EAL
  </code></pre>
  
  <p>--> if column BEZ contains the string 'Schirmsprinkler' and column _IDM has any value, the row should be copied. If the column _IDM is empty, the row is fine and should not be copied. There are many strings in BEZ where _IDM should be empty, so thats why I am trying to put them all in the df_idm lists. However, it doesn't work with an empty string "".</p>
  
  <p>Update 20th of May 2020:</p>
  
  <pre><code>import openpyxl as xl
  from IDM import idm_matrix
  import pandas as pd
  
  # EXCEL DATA FRAME
  xl_file = 'Auswertung_C33.xlsx'
  df_excel = pd.read_excel(xl_file, sheet_name="TriCad_Format")
  
  # IDM LIST DATA FRAME
  df_idm = pd.DataFrame(idm_matrix, columns=['LongName', 'ShortName'])
  
  # REMOVE ROWS WHICH HAVE NO VALUE IN COLUMN 6
  df_excel.dropna(subset=['BEZ'], inplace=True)
  
  # MATCH ON CORRESPONDING COLUMNS
  search_list = df_idm['LongName'].unique().tolist()
  matches1 = df_excel[(df_excel["BEZ"].str.contains("|".join(search_list), regex=True)) &amp;
                     (~df_excel["_IDM"].isin(df_idm["ShortName"].tolist()))]
  matches2 = df_excel[(~df_excel["BEZ"].str.contains("|".join(search_list), regex=True)) &amp; (~pd.isnull(df_excel["_IDM"]))]
  
  # CREATE LIST OF MATCHING DATAFRAMES
  sum_of_idm = [matches1, matches2]
  
  # CREATE NEW WORKBOOK
  writer = pd.ExcelWriter('Ergebnisse.xlsx')
  pd.concat(sum_of_idm).to_excel(writer, sheet_name="Ergebnisse", index=False)
  writer.save()
  </code></pre>