Programming

Pos    Ref    Alt    Score
44    a    bb    1 
57    c    ab    4 
64    d    d    5

Col1.. Col2..   Pos    Ref    Alt ... Score... Col26
id2.. 57    c    ab...    ... 
id3.. 64    d    d...  ... 
id4.. 103  e g ...    ...

Col1.. Col2..   Pos    Ref    Alt ... Score... Col26
 id2.. 57    c    ab...    4 ... 
id3.. 64    d    d... 5 ... 
id4.. 103  e g ...   Unknown_June2017 ...

#!/usr/bin/python
 
import os
from openpyxl.reader.excel import load_workbook
import csv
from openpyxl.drawing.image import Image
import PIL
 
xl_directory = r'/home/test'
txt_directory = r'/home/test'
 
for xl_root, xl_dirs, xl_files in os.walk(xl_directory):
   for xl_file in xl_files:
       if xl_file.endswith('.xlsx'):
           xl_abs_file = os.path.join(xl_root, xl_file)
           wb = load_workbook(xl_abs_file, data_only=True)
           ws = wb.get_sheet_by_name('Unannotated')
           ##clear the contents of the file
           for row in ws['A4:U1000']:
               for cell in row:
                   cell.value = None
   image = Image('/home/logo3.jpg')
           ws.add_image(image, 'A1')
           ## go through text file and write data on worksheet
           for txt_root, txt_dirs, txt_files in os.walk(txt_directory):
               for txt_file in txt_files:
                   if txt_file == xl_file.replace('xlsx', 'txt'):
                       with open(os.path.join(txt_root, txt_file)) as fh:
                           reader = csv.reader(fh, delimiter='\t')
                           [next(reader) for skip in range(1)]
                           for row in reader:
                               ws.append(row)
                               wb.save(xl_abs_file)

#!python
import os
from openpyxl import load_workbook
from datetime import datetime
  
# Variables
sheet_directory = r'<path_of_Excel_files>'
text_directory = r'<path_of_text_files>'
  
# Subroutines
def get_text_data(txt_filename):
    dict_pos = {}
    first_line = True
    for text_root, text_dirs, text_files in os.walk(text_directory):
        for text_file in text_files:
            if text_file == txt_filename:
                # A matching text file was found
                fh = open(os.path.join(text_root, text_file))
                for line in fh:
                    # Skip the header; read the data into the dictionary
                    if first_line:
                        first_line = False
                        continue
                    line = line.rstrip('\n')
                    x = line.split('\t')
                    dict_pos[x[0]] = x[3]
    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'):
                # Read the corresponding text file from the text_directory and
                # populate a dictionary of "Pos" values.
                dpos = get_text_data(sheet_file.replace('.xlsx', '.txt'))
                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('rawdata')
                # If you already know the columns that have the headers "Pos" and
                # "Score", set them here. Otherwise, iterate through the first row
                # to determine those columns.
                pos_col_no = 'C'
                score_col_no = 'F'
                row_no = 2
                cell = ws[pos_col_no + str(row_no)]
                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/python
...
...
xl_directory = r'/home/test'
txt_directory = r'/home/test'
...
...

            for txt_root, txt_dirs, txt_files in os.walk(txt_directory):
               for txt_file in txt_files:
                  if txt_file == xl_file.replace('xlsx', 'txt'):
                       with open(os.path.join(txt_root, txt_file)) as fh:
...
...

...
...
sheet_directory = r'<path_of_Excel_files>'
text_directory = r'<path_of_text_files>'
  
# Subroutines
def get_text_data(txt_filename):
    dict_pos = {}
    first_line = True
    for text_root, text_dirs, text_files in os.walk(text_directory):
        for text_file in text_files:
           if text_file == txt_filename:
                # A matching text file was found
...
...
   
def process_xl_sheets():
...
...
   
# Main section
process_xl_sheets()

import os
from openpyxl import load_workbook
from datetime import datetime
import csv
  
# Variables
sheet_directory = r'/home/test'
text_directory = r'/home/test'
  
# Subroutines
def get_text_data(txt_filename):
    dict_pos = {}
    first_line = True
    with open('scores.txt') as txt_filename:
        tab_reader = csv.reader(txt_filename, delimiter='\t')
        for line in tab_reader:
            if first_line:
                first_line = False
                continue
                line = line.rstrip('\n')
                x = line.split('\t')
                dict_pos[x[0]] = 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'):
                dpos = get_text_data(sheet_file.replace('.xlsx', '.txt'))
                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 = 'F'
                score_col_no = 'V'
                row_no = 4
                cell = ws[pos_col_no + str(row_no)]
                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()