<jupyter_start><jupyter_text>Process raw table<jupyter_code>ortho_table = pd.read_excel(
    'SITable30-Science11-Evolutionary_Rates_of_1111_Orthologous_Groups.xls', 
    skiprows=2, parse_dates=False
)
ortho_table
ortho_table[ortho_table['Spom Gene Names'].apply(lambda x: isinstance(x, datetime.datetime))]
ortho_table.loc[
    ortho_table['Spom Gene Names'].apply(lambda x: isinstance(x, datetime.datetime)),
    'Spom Gene Names'
] = np.nan

ortho_table[ortho_table['Spom Gene Names'].apply(lambda x: isinstance(x, datetime.datetime))]
ortho_table.loc[(477, 1524, 2329), :]
ortho_table[pd.isna(ortho_table['Spom Gene Names'])]
original_na_row_idx = np.where(pd.isna(ortho_table['Spom Gene Names']))[0]
original_na_row_idx
ortho_table.loc[477, 'Spom Gene Names'] = 'sep1'
ortho_table.loc[616, 'Spom Gene Names'] = 'mrp49'
ortho_table.loc[1017, 'Spom Gene Names'] = 'pet117'
ortho_table.loc[1524, 'Spom Gene Names'] = 'oct1'
ortho_table.loc[1559, 'Spom Gene Names'] = 'iec5'
ortho_table.loc[2329, 'Spom Gene Names'] = 'pmc6'

ortho_table[pd.isna(ortho_table['Spom Gene Names'])]
ortho_table.loc[pd.isna(ortho_table['Spom Gene Names']), 'Spom Gene Names'] = (
    ortho_table.loc[pd.isna(ortho_table['Spom Gene Names']), 'Genes'].str.split(';', expand=True).iloc[:, 0]
)
ortho_table[pd.isna(ortho_table['Spom Gene Names'])]
ortho_table.loc[original_na_row_idx, :]<jupyter_output><empty_output><jupyter_text>Write find table to json<jupyter_code>dict(Counter(sorted([_[:4] for _ in ';'.join(ortho_table['Genes'].tolist()).split(';')])))
for gene in (
    [_ for _ in ';'.join(ortho_table['Genes'].tolist()).split(';')]
):
    df = ortho_table[ortho_table['Genes'].str.contains(gene, regex=False)]
    if len(df) != 1:
        print(gene)
        print(df)
        break

gene
df
yeast_gene_prefix_table = {
    'SPAC': 'pombe',
    'SPAP': 'pombe',
    'SPBC': 'pombe',
    'SPBP': 'pombe',
    'SPCC': 'pombe',
    'SPCP': 'pombe',
    'SJAG': 'japonicus', 
    'SPOG': 'cryophilus', 
    'SOCG': 'octosporus',
}
recorder_order = {
    'pombe': 1,
    'japonicus': 2,
    'cryophilus': 3,
    'octosporus': 4,
}

OrthoTransDict = {
    'Data': [],
}

for idx, (row_idx, row) in enumerate(ortho_table.iterrows()):
    genes = row['Genes'].split(';')
    pombe_gene_name = row['Spom Gene Names']
    pombe_gene_desc = row['Spom Gene Descroption']
    
    new_gene_rec = ['' for i in range(5)]
    for gene in genes:
        gene_prefix_4 = gene[:4]
        species = yeast_gene_prefix_table[gene_prefix_4]
        
        if species != 'pombe':
            gene = f'{gene[:4]}_{gene[4:]}'
        
        new_gene_rec[recorder_order[species]] = gene
        
        OrthoTransDict[gene] = idx
    
    if pombe_gene_name != new_gene_rec[recorder_order['pombe']]:
        new_gene_rec[0] = pombe_gene_name
        OrthoTransDict[pombe_gene_name] = idx
    
    new_gene_rec.append(pombe_gene_desc)

    OrthoTransDict['Data'].append(new_gene_rec)

for gene in ['SPAC323.03c', 'SOCG_03083', 'SPOG_02688', 'SJAG_05315']:
    print(gene, OrthoTransDict[gene])
for gene in ['SPBC216.02', 'SOCG_03562', 'SPOG_04176', 'SJAG_00368', 'mcp5']:
    print(gene, OrthoTransDict[gene])
for gene in ['SPAC25G10.06', 'SOCG_04152', 'SPOG_00390', 'SJAG_04746', 'rps2801']:
    print(gene, OrthoTransDict[gene])
OrthoTransDict['Data'][4219]
OrthoTransDict['Data'][-1]
len(OrthoTransDict['Data'])
with open(r'..\external_data\OrthoGeneTable-Rhind_Science_11.json', 'w') as f:
    json.dump(OrthoTransDict, f)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16785004260153366, 'fraction_numerical': 0.039477421187162735, 'mean_word_length': 4.745513866231648, 'pattern_counts': {'":': 0, '<': 9, '<?xml version=': 0, '>': 9, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '54217', 'license': 'MIT', 'n_tokens_mistral': 1549, 'n_tokens_neox': 1426, 'n_words': 232, 'path': 'Data/GenOrthoGeneTransTable.ipynb', 'repo_name': 'Tomoyasu-Sugiyama-Lab/Yeaseq', 'size': '3521'}