merging excel files - but a bit trickier than usual

[BBMcL]

Advanced thanks for any helping. I'm running Python on a Mac OS X.

Here's the basic situation. A single group of people had various health measurements performed on them over the course of a few decades. But individuals dropped out of the study between examinations. For instance, for the first exam there were 3,000 individuals, then for the second exam (a few years later), there were 2,500, and so on. By the last exam, exam 26, there were just a few hundred individuals of the initial group left. No individuals were added to the study at any point.

The records from each exam are saved as separate Excel files. The number of rows in the file depends upon how many patients there were for that exam - e.g., the file for exam 1 has 3,000 rows; for exam 2, 2,500; for exam 26, 200. The rows correspond to the number of subjects because each subject was given a patient ID number, and they were put into the last column of the file. So each row has one subject's health records, with the ID at the end of the row indicating which patient it is.

Basically, what happened is that if a patient wasn't at a particular exam, their patient ID was left off of that file. They did not keep the same number of rows in each file, so the files get shorter and shorter as people dropped out. Each column in the file is either the patient ID column or is a variable that was measured - e.g., blood pressure, cholesterol, etc.

I want to create a masterfile that has 3,000 rows (for the 3,000 original patient ID numbers) and has information from specified columns for all of the exams. Specifically, cholesterol is recorded in a single column in every exam. I want to create a masterfile that has 3,000 rows and 27 columns (records for 26 exams, plus one column for the patient ID column) and call it "cholesterol_masterfile". If a subject wasn't at a later examination, I just want to put in "was_not_here" in those cells for which there are no records. I hope this is clear so far.

Now, the first exam has the 3,000 rows, and I just deleted all the columns that weren't cholesterol records. So it has 3,000 rows and two columns - cholesterol, plus patient ID. So my task comes in two steps. First, I need to import columns from later exams into this first Excel file. But also - and this is trickier - I need to match up the patient ID numbers so that the information gets put in the right row. So for instance, in the fourth examination, we can imagine that there was a subject with patient ID 45554. I want the information in the choleterol column - say, column 5 - of this row and I want to put it in the row in the first examination that is for patient ID 45554. And I want to do this for all of the patients. I figured when this is all done, I can loop through it and fill in blank cells with "was_not_there".

Don't hesitate to ask for clarification if this doesn't make sense.

[bvdet]

The experts on this site are more than happy to help you with your problems but they cannot write your program for you. Attempt the program yourself first and post questions regarding any difficulties you have or about a particular function of the code that you don't know how to achieve.

Please read the Posting Guidelines and particularly the Coursework Posting Guidelines.

Having said that, I would approach the problem in this manner:
Initialize an empty dictionary to hold all data
Iterate on the Excel file names
Use module xlrd to create an xlrd.Book instance of the current file
Create an xlrd.sheet.Sheet instance from the xlrd.Book instance work sheet that contains the data
Iterate on the xlrd.sheet.Sheet rows
Get the current row values
Assign to the dictionary the subject ID as the key and a list of the values that you want to tabulate

Example iteration on the current workbook sheet rows where the ID is at index 0 and the data is at index 2:

dd = {}
for j, file_name in enumerate(list_of_file_names):
    wb = xlrd.open_workbook(file_name)
    sheet1 = wb.sheet_by_name(u'Name of Worksheet')
 
    for i in range(sheet1.nrows):
        rowList = sheet1.row_values(i)
        # skip rows with no ID
        if str(rowList[0]).strip():
            dd.setdefault(str(rowList[0]), []).append(str(rowList[2]))
 

Assuming the first file_name has all 3000 ID's, if an individual dd[ID#] does not have a listing in the current file, pad the value list with string "was_not_there". You may be able to use index value j for that task.

After iterating on list_of_file_names, you should have all the data to create a CSV file.

Example:

keys = dd.keys()
keys.sort()
result = "\n".join(["%s,%s" % (key, ",".join(dd[key])) for key in keys])

Then it's a matter of writing result to disk.

BV - Moderator