#!/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
import csv


# Variables
sheet_directory = r'/home/test'

def process_xl_sheets():
    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 ##prints

    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) ##prints
                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'
                NG = score_col_no
                row_no = 4
                # cell = ws[pos_col_no  + str(row_no)]
                cell_pos = ws[pos_col_no + str(row_no)]
                cell_ref = ws[ref_col_no + str(row_no)]
                cell_alt = ws[alt_col_no + str(row_no)]
                NG = ws[score_col_no + str(row_no)] = 'Unknown_' + datetime.now().strftime("%B")
                cpos = Scores(POS=cell_pos.value, ALT=cell_alt.value, REF=cell_ref.value)
                print cpos ##prints only row E4:G4:F4 and not all
                #print dict_pos ##prints all the scores
                if (dict_pos.has_key(cpos)):
                        NG = dict_pos[cpos]
                        print dict_pos[cpos] ##prints the actual score
                else:
                        NG = 'Unknown_' + datetime.now().strftime("%B") + datetime.now().strftime("%Y")
                        row_no += 1
                        cpos = Scores(POS=cell_pos.value, ALT=cell_alt.value, REF=cell_ref.value)
    wb.save(sheet_xl_file)

process_xl_sheets()

/usr/bin/python2.7 /home/test/new_dict.py

##reads only row 4 
Scores(POS=u'73', ALT=u'G', REF=u'A')

##prints all values from 'score.txt' 
{Scores(POS='2171', ALT='C', REF='T'): '5', Scores(POS='73', ALT='G', REF='A'): '11', Scores(POS='114', ALT='T', REF='C'): '1', Scores(POS='2080', ALT='C', REF='T'): '4', Scores(POS='1189', ALT='C', REF='T'): '1'}
 
##returns the value from score.txt against row 4 of xlsx but does not save it to excel 
11 

 Process finished with exit code 0