#!/usr/bin/python

import sys
import os
from openpyxl import load_workbook
from datetime import datetime
from pandas import read_table
import csv
from collections import namedtuple

# Variables
sheet_directory = r'/home/test'

# Subroutines
def get_text_data(txt_filename):
    dict_pos = {}
    Scores = namedtuple("Scores", ["POS", "ALT", "REF"])
    first_line = True
    with open('/home/test/scores.txt') as txt_filename:
        for line in txt_filename:
            if first_line:
                first_line = False
                continue
            line = line.rstrip('\n')
            x = line.split('\t')
            cpos = Scores(POS=x[0], ALT=x[2], REF=x[1])
            dict_pos[cpos] = x[3]
        print dict_pos          
        return dict_pos


def process_xl_sheets():
    for sheet_root, sheet_dirs, sheet_files in os.walk(sheet_directory):
        for sheet_file in sheet_files:
            if sheet_file.endswith('.xlsx'):
                print(sheet_file)
                dpos = get_text_data(sheet_file.replace('.xlsx', '.txt'))  ##what exactly is this part doing ? There is only one text file 'score.txt' to be referenced against several xlsx files named S12.xlsx , S13.xlsx etc
                sheet_xl_file = os.path.join(sheet_root, sheet_file)
                wb = load_workbook(sheet_xl_file, data_only=True)
                ws = wb.get_sheet_by_name('raw_data')
                pos_col_no = 'E'
                alt_col_no = 'G'
                ref_col_no = 'F'
                score_col_no = 'V'
                row_no = 4
                #compare = Scores(POS=pos_col_no, ALT=alt_col_no, REF=ref_col_no)
                #cell = ws[compare + str(row_no) ]
                cell = ws[pos_col_no + alt_col_no + ref_col_no + str(row_no)]
                print cell.value           ##doesn't print
                while cell.value:
                    if str(cell.value) in dpos:
                        ws[score_col_no + str(row_no)] = dpos[str(cell.value)]
                    else:
                        ws[score_col_no + str(row_no)] = 'Unknown_' + datetime.now().strftime("%B") + datetime.now().strftime("%Y")
                        row_no += 1
                        cell = ws[pos_col_no + str(row_no)]
                wb.save(sheet_xl_file)

# Main section
process_xl_sheets()

/usr/bin/python2.7 /home/test/annotate.py
S12.xlsx
{Scores(POS='73', ALT='C', REF='CN'): 'A', Scores(POS='497', ALT='C', REF='T'): '1', Scores(POS='2196', ALT='T', REF='C'): '1', Scores(POS='2080', ALT='C', REF='A'): '1', Scores(POS='2456', ALT='C', REF='T'): '1'}
None