title: Python - looking for best approach to reading a complex excel file into one dataframe
body: <p>I have an excel file produced automatically by a system so I need to ingest as it is. there is a number of format challenges with the file. Format of file shown below in screenshot.
  <a href="https://i.stack.imgur.com/E4N7W.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/E4N7W.png" alt="Screenshot of sample file" /></a></p>
  <ul>
  <li>Firstly I need to take as a table the data from row 10 and below with row 10 being the table headers. challenge here is that in cases of merged columns like column A. the header is actually row 9 'Project number', for column C it is row 10 'Family Name'</li>
  <li>Secondly some column names repeat (in row 10) based on row 9. It would be good to be able to make sense of this by concatonating the row 9 with row 10...for example 'Person name - passport number'. It might be possible to hardcode.</li>
  <li>Third, I need to take other information from cells and put as a column in the dataframe - eg C4 which has a date is needed. this can be hardcoded</li>
  </ul>
  <p>What python package is best to use here, as there is a combination of challenges. I have been looking at openpyxl and I can see i can take values from specfic cells for example but haven't seen how this could be used to solve my first two challenges</p>