test

#loading the original series matrix
wb1 = openpyxl.load_workbook(r"C:/Users/Shaked Bruner/Desktop/GSE72056_series_matrix_shaked.xlsx")
series_matrix_sheet = wb1["Sheet3"] #the transposed series matrix
#loading the revised series matrix
wb2= openpyxl.load_workbook(r"C:/Users/Shaked Bruner/Desktop/GSE72056_melanoma_single_cell_revised_v2.xlsx")
series_matrix_revised= wb2["Sheet1"] #the transposed revised series matrix
#loading the participant table
wb3= openpyxl.load_workbook(r"C:/Users/Shaked Bruner/Desktop/participant.xlsx")
participants_table= wb3["participant"] #the new table
newTable = openpyxl.Workbook()
newSheet = newTable.active
newSheet.title = "name_xi_color_new"

def insert_to_list (start, stop, column, sheet): #inserting the values of selected column to list. gets starting and ending point and the required letter of column n the exel file
    new_list = []
    for row in range(start, stop):
        pos = column + str(row)
        specific_cell = sheet[pos]
        new_list.append(specific_cell)
    return new_list

def insert_list_to_column (start, column, list): #inserting value from list to the new column in the new table
    length = len(list)
    for row in range(start, length):
        pos = column + str(row)
        newSheet[pos].value = list(row)
        newTable.save(r"C:\Users\Shaked Bruner\Desktop\GSE72056_series_matrix_new.xlsx")

#insert required future columns into list- change it  according to your needs
list_of_required_column= ["!Sample_title", "!Sample_geo_accession","!Sample_source_name_ch1", "!Sample_organism_ch1", "!Sample_characteristics_ch1", "!Sample_characteristics_ch1"]

for letter in ascii_lowercase: #insert to list the right columns from the transposed series matrix
    pos = (str(letter)).upper()+"1"
    column_value_dict= {}
    for column_title in list_of_required_column:
        lst= insert_to_list(2, 4646, str(letter), series_matrix_sheet)
        column_value_dict.update(column_title,lst)