<jupyter_start><jupyter_text>Purpose Many of the drug names are incorrectly listed on the platemaps. Rather than going into each platemap and manually renaming everything, `which would be frought with peril,` wel will programatically rename these plate maps.  Procedure Followed to rename drugs 1. "original" plate maps are stored in `../platemap-backup/.` and remain unchanged. 2. using the output file `HNSCC_all_functional_data.csv` produced by the original platemaps and ~20 patients (as of 4/18/2020), I grabbed all unique drug names (single agent + combo) and produced the file `drug_name_rework.xlsx`.     a) the first column in this excel contians these original drug names (those stored in the original drug plate maps)     b) the second column contains the names renamed as I think they should be     c) the third column is the capitalized version of col 2 - to minimize complexity 3. This file is use to create a dictionary mapping {original_names -> correct_name_CAPS}4. The dictionary is applied across all inhibitor platemaps and the resulting platemap files are save to `../platemaps/.` For future name changes, I suggest working from the plate map backups directory and updating column 2 of the `rework` excel file.  Incorrect Drug Names We have a list of combination drugs that we know are incorrect, we can start with those. Load data This is output from the `combination_ratio.ipynb` notebook.<jupyter_code>#failed_comb = pd.read_csv('../output/failed_combinations.txt', header=None)[0].values.tolist()

#print('number of failed drug combination names:', len(failed_comb))

#print('\n'.join(failed_comb))<jupyter_output><empty_output><jupyter_text>Lets just grab all drugs To be sure we have them correct. This is just to produce the list of drugs in `drug_name_rework.xlsx`<jupyter_code>#_all = pd.read_csv('../output/HNSCC_all_functional_data.csv')
#single_agent_inhibs = _all[~_all.inhibitor.str.contains(';')].inhibitor.unique()
#comb_agent_inhibs = _all[_all.inhibitor.str.contains(';')].inhibitor.unique()

#print('\n'.join(single_agent_inhibs))
#print('\n'.join(comb_agent_inhibs))<jupyter_output><empty_output><jupyter_text>load `drug_name_rework.xlsx` this is where we'll map the old names to the new names.<jupyter_code>rework = pd.read_excel('../drug_name_rework.xlsx')
rework.head()
rename_dict = {x.previous_name:x.correct_name_CAPS for i,x in rework.iterrows()}<jupyter_output><empty_output><jupyter_text>Load platemap in<jupyter_code>plate_map_dir = '../plate_maps-backup/'
plate_map_out = '../plate_maps/'

platemap_names = os.listdir(plate_map_dir)
print('plate maps available:')
print('\n'.join(platemap_names))
print()

platemap_choice = plate_map_dir + platemap_names[0]
platemap_out = plate_map_out + platemap_names[0]

print(platemap_choice)
print(platemap_out)
def rename_sheets(path_in, path_out, rename_dict): 
    
    book = load_workbook(path_in, data_only=True)         # load excel document
    sheets = np.array(book.sheetnames)
    sheets_to_rename = sheets[((np.arange(len(sheets))%2 == 0) * (np.arange(len(sheets)) > 0))]
    print('sheets selected for renaming...', end='\n\t')
    print(sheets_to_rename)

    writer = pd.ExcelWriter(path_out, engine='openpyxl') 
    writer.book = book

    ## ExcelWriter for some reason uses writer.sheets to access the sheet.
    ## If you leave it empty it will not know that sheet Main is already there
    ## and will create a new sheet.

    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

    for _sheet in sheets_to_rename: 
    
        df = pd.read_excel(path_in, sheet_name=_sheet, header=None, index_col=None)             # load data in 
        
        df = df.applymap(lambda x: x.strip() if isinstance(x, str) else x)      # make sure no names have trailing spaces
        
        df = df.replace(rename_dict, value=None)
        
        df.to_excel(writer, _sheet, header=False, index=False)              # save back to excel 
        
    writer.save()

    #print('finished!')

# test    
#rename_sheets(platemap_choice, platemap_out, rename_dict)
for i, pm in enumerate(platemap_names): 
    print('renaming: ', pm)
    platemap_choice = plate_map_dir + platemap_names[i]
    platemap_out = plate_map_out + platemap_names[i]
    rename_sheets(platemap_choice, platemap_out, rename_dict)
    print('------------------------------------------------------------')
#? Really odd behavior with excel: 
#! This only started occuring AFTER I implemented the plate_map renaming script. Now, when I read in the values (seems to be just for conc sheets)
#! The formula cells are read in as NA, by changing: , na_values = None, keep_default_na = False , we can prevent this for cells, however we 
#! still have to rename the header, for some reason it doesn't get handled the same... 

#! SOLVED: The openpyxl does not evaluate formulas, so when the renaming script saves the new excel plate maps, it has unevaluated equations. 
#! to fix this issue, after running the renaming script, you have to open each excel file, go to each tab (should autocalculate) and THEN run this. 
#! If you don't do this, it will result in a ton of NA values.<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10507036822826296, 'fraction_numerical': 0.004048582995951417, 'mean_word_length': 4.151936444885799, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '128620', 'license': 'MIT', 'n_tokens_mistral': 1668, 'n_tokens_neox': 1599, 'n_words': 547, 'path': 'python/.ipynb_checkpoints/platemap_drug_renaming-checkpoint.ipynb', 'repo_name': 'nathanieljevans/HNSCC_functional_data_pipeline', 'size': '5187'}