<jupyter_start><jupyter_text>Preparing to submit wold stranded samples....The spreasheet has a bunch of library accessions in the files accession column which is wrong. I think it needs to be fixed.<jupyter_code>import os
import sys
import requests
import pandas
import paramiko
import json
from IPython import display
from pathlib import Path
from curation_common import *
from htsworkflow.submission.encoded import DCCValidator
from htsworkflow.submission.encoded import Document
from htsworkflow.submission.aws_submission import run_aws_cp
# live server & control file
server = ENCODED('www.encodeproject.org')
spreadsheet_name = Path('~/woldlab/ENCODE/stranded-22563-22581.xlsx').expanduser()
engine=None
#engine='odf'

# test server & datafile
#server = ENCODED('test.encodedcc.org')
#spreadsheet_name = os.path.expanduser('~diane/woldlab/ENCODE/C1-encode3-limb-2017-testserver.ods')

server.load_netrc()
validator = DCCValidator(server)
award = 'UM1HG009443'<jupyter_output><empty_output><jupyter_text>Register Biosamples<jupyter_code>biosample = pandas.read_excel(spreadsheet_name, sheet_name='Biosample', header=0, engine=engine)<jupyter_output><empty_output><jupyter_text>Register Libraries<jupyter_code>libraries = pandas.read_excel(spreadsheet_name, sheet_name='Library', header=0, engine=engine)<jupyter_output><empty_output><jupyter_text>Register Experiments<jupyter_code>experiments = pandas.read_excel(spreadsheet_name, sheet_name='Experiment', header=0, engine=engine)

records = []
for i, experiment in experiments.iterrows():
    experiment_accession = experiment['accession']
    if experiment_accession.startswith('ENCSR'):
        obj = server.get_json(experiment_accession)
        for file_accession in obj['original_files']:
            f = server.get_json(file_accession)
            records.append({
                'md5sum': f['md5sum'],
                'uuid': f['uuid'],
                'file_accession': f['accession'],
                'submitted_file_name': f['submitted_file_name']
            })
submitted_files = pandas.DataFrame(records)<jupyter_output><empty_output><jupyter_text>Register Replicates<jupyter_code>replicates = pandas.read_excel(spreadsheet_name, sheet_name='Replicate', header=0, engine=engine)<jupyter_output><empty_output><jupyter_text>Check Files<jupyter_code>files = pandas.read_excel(spreadsheet_name, sheet_name='File', header=0, engine=engine)

fixed_files = pandas.merge(files, submitted_files, left_on='md5sum', right_on='md5sum')
fixed_files.to_excel('/dev/shm/fixed_files.xlsx')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10968494749124855, 'fraction_numerical': 0.012446518864255154, 'mean_word_length': 6.412103746397695, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 1, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '205494', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 877, 'n_tokens_neox': 838, 'n_words': 144, 'path': 'encode-wold-stranded-22563-22581-fix-files-accession.ipynb', 'repo_name': 'detrout/encode4-curation', 'size': '2571'}