title: Using Pandas and xlrd together. Ignoring absence/presence of column headers
body: <p>I am hoping you can help me - I'm sure its likely a small thing to fix, when one knows how. </p>
  
  <p>In my workshop, neither I nor my colleagues can make 'find and replace all' changes via the front-end of our database. The boss just denies us that level of access. If we need to make changes to dozens or perhaps hundreds of records it must all be done by copy-and-paste or similar means. Craziness.</p>
  
  <p>I am trying to make a workaround to that with Python 2 and in particular libraries such as Pandas, pyautogui and xlrd. </p>
  
  <p>I have researched serval StackOverflow threads and have managed thus far to write some code that works well at reading a given XL file .In production, this will be a file exported from a found data set in the database GUI front-end and will be just a single column of 'Article Numbers' for the items in the computer workshop. This will always have an Excel column header. E.g</p>
  
  <pre><code>ANR
  51234
  34567
  12345
  ...
  </code></pre>
  
  <p>All the records numbers are 5 digit numbers. 
  We also have the means of scanning items with an IR scanner to a 'Workflow' app on the iPad we have and automatically making an XL file out of that list of scanned items. </p>
  
  <p>The XL file here could look something similar to this.</p>
  
  <pre><code>56788
  12345
  89012
  ...
  </code></pre>
  
  <p>It differs in that there is no column header. All XL files have their data 'anchored' at cell A1 on 'Sheet1" and again just single column will be used. No unnecessary complications here!</p>
  
  <p>Here is the script anyway. When it is fully working system arguments will be supplied to it. For now, let's pretend that we need to change records to have their 'RAM' value changed from<br>
  <code>"2GB"</code> to <code>"2 GB"</code>.</p>
  
  <pre><code>import xlrd
  import string
  import re
  import pandas as pd
  
  
  field = "RAM"
  value = "2 GB"
  
  myFile = "/Users/me/folder/testArticles.xlsx"
  df = pd.read_excel(myFile)
  myRegex = "^[0-9]{5}$"
  
  
  # data collection and putting into lists.
  workbook = xlrd.open_workbook(myFile)
  sheet = workbook.sheet_by_index(0)
  data = [[sheet.cell_value(r, c) for c in range(sheet.ncols)] for r in     range(sheet.nrows)]
  
  formatted = []
  deDuped = []
  
  # removing any possible XL headers, setting all values to strings
  # that look like five-digit ints, apply a regex to be sure.
  for i in data:
      cellValue = str(i)
      cellValue = cellValue.translate(None, '\'[u]\'')
  
  
      # remove the decimal point
      # Searching for the header will cause a database front-end problem. 
      cellValue = cellValue[:-2]
      cellValue = cellValue.translate(None, string.letters)
  
      # making sure only valid article numbers get through
      # blank rows etc can take a hike
      if len(cellValue) != 0:
          if re.match(myRegex, cellValue):
              formatted.append(cellValue)
  
  # weeding out any possilbe dupes.
  for i in formatted:
      if i not in deDuped:
          deDuped.append(i)
  
  
  #main code block
  for i in deDuped:
  
      #lots going on here involving pyauotgui
      #making sure of no error running searches, checking for warnings, moving/tabbing around DB front-end etc
  
      #if all goes to plan
      #removing that record number from the excel file and saving the change
      #so that if we run the script again for the same XL file 
      #we don't needlessly update an already OK record again. 
  
          df = df[~df['ANR'].astype(str).str.startswith(i)]
          df.to_excel(myFile, index=False)
  </code></pre>
  
  <p>What I really would to like to find out is how can I run the script so that "<em>doesn't care</em>" about the presence or absence of the column header. </p>
  
  <pre><code>df = df[~df['ANR'].astype(str).str.startswith(i)]
  </code></pre>
  
  <p>Appears to be the line of code where this all hangs on. I've made several changes to the line in different combination but my script always crashes.</p>
  
  <p>If a column header, ("ANR") in my case, is essential for this particular 'pandas' method is there a straight-forward way of inserting a column header into an XL file if it lacks one in the first place - i.e the XL files that come from the IR scanner and the 'Workflow' app on the iPad? </p>
  
  <p>Thanks guys!</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>I've tried as suggested by Patrick implementing some code to check if cell "A1" has a header or not. Partial success. I can put "ANR" in cell A1 if its missing but I lose whatever was there in the first place.</p>
  
  <pre><code>import xlwt
  from openpyxl import Workbook, load_workbook
  from xlutils.copy import copy
  import openpyxl
  
  # data collection
  workbook = xlrd.open_workbook(myFile)
  sheet = workbook.sheet_by_index(0)
  data = [[sheet.cell_value(r, c) for c in range(sheet.ncols)] for r in range(sheet.nrows)]
  
  
  
  cell_a1 = sheet.cell_value(rowx=0, colx=0)
  
  if cell_a1 == "ANR":
      print "has header"
  else:
      wb = openpyxl.load_workbook(filename= myFile)
      ws = wb['Sheet1']
      ws['A1'] = "ANE"
      wb.save(myFile)
      #re-open XL file again etc etc.
  </code></pre>
  
  <p>I found this new block of code over at <a href="https://stackoverflow.com/questions/2725852/writing-to-existing-workbook-using-xlwt/2726298">writing to existing workbook using xlwt</a>. In this instance the contributor actually used openpyxl.</p>