<jupyter_start><jupyter_text>Create cells table<jupyter_code># Create table of perturbations
command = '''
CREATE TABLE cells
(
    cell_uid INTEGER PRIMARY KEY AUTOINCREMENT,
    cell_id TEXT UNIQUE NOT NULL,
    cell_histology TEXT,
    cell_lineage TEXT,
    cell_source TEXT,
    cell_source_id TEXT,
    cell_type TEXT,
    gender TEXT,
    is_from_metastasis TEXT,
    metastatic_site TEXT
);
'''
cursor.execute(command)

cells_columns = [col[1] for col in cursor.execute('PRAGMA table_info(cells);')][1:]
cells_columns
cell_df = pandas.read_table('data/cellinfo/cellinfo.tsv.gz', compression='gzip', na_values=['-666', ''])
cell_df = cell_df[cells_columns]
cell_df.dropna(subset=['cell_id'], inplace=True)
cell_df.head()
# Create table of perturbations
cell_df.to_sql('cells', connection, if_exists='append', index=False)<jupyter_output><empty_output><jupyter_text>Create perts table<jupyter_code># Create table of perturbations
command = '''
CREATE TABLE perts
(
    pert_uid INTEGER PRIMARY KEY AUTOINCREMENT,
    pert_id TEXT UNIQUE NOT NULL,
    pert_iname TEXT,
    pert_type TEXT NOT NULL,
    num_gold INTEGER NOT NULL,
    num_inst INTEGER NOT NULL,
    num_sig INTEGER NOT NULL,
    in_summly INTEGER NOT NULL,
    inchi_string TEXT,
    inchi_key TEXT,
    pubchem_cid INTEGER
);
'''
cursor.execute(command)

perts_columns = [col[1] for col in cursor.execute('PRAGMA table_info(perts);')][1:]
perts_columns
# read L1000 perturbagens
pert_df = pandas.read_table('data/pertinfo/pertinfo.tsv.gz', compression='gzip', na_values=['-666', ''])
pert_df = pert_df[perts_columns]
pert_df['pubchem_cid'] = pert_df.pubchem_cid.astype(int, raise_on_error=False)
pert_df['in_summly'] = pert_df.in_summly.map(lambda x: x is True)
pert_df['inchi_key'] = pert_df.inchi_key.str.replace('InChIKey=', '')
pert_df.head()
# Create table of perturbations
pert_df.to_sql('perts', connection, if_exists='append', index=False)<jupyter_output><empty_output><jupyter_text>Create sigs tables<jupyter_code># Create table of perturbations
command = '''
CREATE TABLE sigs
(
    sig_uid INTEGER PRIMARY KEY AUTOINCREMENT,
    sig_id TEXT UNIQUE NOT NULL,
    pert_id TEXT NOT NULL,
    pert_itime TEXT,
    pert_idose TEXT,
    cell_id TEXT NOT NULL,
    is_gold INTEGER NOT NULL,
    ngenes_modulated_dn_lm INTEGER NOT NULL,
    ngenes_modulated_up_lm INTEGER NOT NULL,
    FOREIGN KEY(pert_id) REFERENCES perts(pert_id),
    FOREIGN KEY(cell_id) REFERENCES cells(cell_id)
);
'''
cursor.execute(command)

sig_columns = [col[1] for col in cursor.execute('PRAGMA table_info(sigs);')][1:]
sig_columns
# read L1000 perturbagens
sig_df = pandas.read_table('data/siginfo/siginfo.tsv.gz', compression='gzip', na_values=['-666', ''])
sig_df = sig_df[sig_columns]
sig_df.head()
# Create table of perturbations
sig_df.to_sql('sigs', connection, if_exists='append', index=False)<jupyter_output><empty_output><jupyter_text>Similarity<jupyter_code># Create table of compound similarity
command = '''
CREATE TABLE similarities
(
    pert_uid_0 INTEGER NOT NULL,
    pert_uid_1 INTEGER NOT NULL,
    chemical REAL,
    -- FOREIGN KEY(pert_uid_0) REFERENCES perts(pert_uid),
    -- FOREIGN KEY(pert_uid_1) REFERENCES perts(pert_uid),
    PRIMARY KEY(pert_uid_0, pert_uid_1)
);
'''
cursor.execute(command);<jupyter_output><empty_output><jupyter_text>Commit and close database<jupyter_code>connection.commit()
connection.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11325160537069469, 'fraction_numerical': 0.008464681844716871, 'mean_word_length': 5.7727272727272725, 'pattern_counts': {'":': 0, '<': 21, '<?xml version=': 0, '>': 21, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '473828', 'license': 'CC0-1.0', 'n_tokens_mistral': 1281, 'n_tokens_neox': 1214, 'n_words': 263, 'path': 'database.ipynb', 'repo_name': 'dhimmel/lincs', 'size': '3426'}