<jupyter_start><jupyter_text>Number of sequences<jupyter_code>seq_per_species_query = """
select 
    assembly_accession, 
    count(*) as sequence_count,
    min(length) as min_length,
    max(length) as max_length,
    avg(length) as avg_length
from sequences
group by assembly_accession
""" 
sequence_count_per_species = pd.read_sql(seq_per_species_query, engine).set_index('assembly_accession', drop=True)
sequence_count_per_species.head()
min_s = sequence_count_per_species['sequence_count'].min()
max_s = sequence_count_per_species['sequence_count'].max()
avg_s = sequence_count_per_species['sequence_count'].mean()
print('Sequence count')
print(f'Minimum: {min_s:,}')
print(f'Maximum: {max_s:,}')
print(f'Mean: {avg_s:,.0f}')
sequence_count_per_species.loc['GCA_000828855.1']
sequence_count_per_species.sort_values('max_length', ascending=False)<jupyter_output><empty_output><jupyter_text>Sequence lengths<jupyter_code>min_s = sequence_count_per_species['min_length'].min()
max_s = sequence_count_per_species['max_length'].max()
print('Sequence length')
print(f'Minimum: {min_s:,}')
print(f'Maximum: {max_s:,}')
avg_sequence_length = pd.read_sql(
    'select avg(length) as avg_seq_l from sequences', 
    engine,
).iloc[0]['avg_seq_l']
print(f'Average sequence length: {avg_sequence_length:,.0f}')
sequence_lengths = pd.read_sql('select length from sequences', engine)['length'].values
_, ax = plt.subplots(1, 1, figsize=(12, 6))
bins = [
    1, 2, 3, 4, 5, 6, 7, 8, 9, 
    10, 20, 30, 40, 50, 60, 70, 80, 90, 
    100, 200, 300, 400, 500, 600, 700, 800, 900, 
    1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000, 
    10e3, 20e3, 30e3, 40e3, 50e3, 60e3, 70e3, 80e3, 90e3,
]
ax.set_xscale('log');
ax.hist(sequence_lengths, bins=bins, log=True);
q = """
select 
    assembly_accession, 
    cast(sum(length) / 1000 as integer) as cds_total_length
from sequences
where sequence_type = 'CDS'
group by assembly_accession
""" 
s1 = pd.read_sql(q, engine)
q = """
select 
    assembly_accession, 
    cast(sum(length) / 1000 as integer) as non_coding_total_length
from sequences
where sequence_type = 'non_coding'
group by assembly_accession
""" 
s2 = pd.read_sql(q, engine)
s = pd.merge(s1, s2, on='assembly_accession').set_index('assembly_accession', drop=True)
s.head()
s['coding_ratio'] = s.apply(
    lambda row: row['cds_total_length'] / (row['cds_total_length'] + row['non_coding_total_length']), 
    axis=1,
)
s
_, ax = plt.subplots(1, 1, figsize=(12, 6))
ax.hist(s['coding_ratio'], bins=20, log=True);<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15064731267163595, 'fraction_numerical': 0.06080816006276971, 'mean_word_length': 6.391304347826087, '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': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '192991', 'license': 'MIT', 'n_tokens_mistral': 1124, 'n_tokens_neox': 957, 'n_words': 192, 'path': 'notebook/Database statistics.ipynb', 'repo_name': 'srom/rna_learn', 'size': '2549'}