【ChEMBL】化合物のIC50/ID50を調べる

$ tar -xzvf chembl_34_postgresql.tar.gz

version: "3.6"
services:
  db:
    image: postgres:14.2
    ports:
      - 5432:5432
    tty: true
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: password
    volumes:
      - ./db:/var/lib/postgresql/data

chembl34/chembl_34_postgresql $ docker-compose up

chembl34/chembl_34_postgresql $ mv chembl_34_postgresql.dmp db/chembl_34_postgresql.dmp

chembl34/chembl_34_postgresql $ docker container ls

chembl34/chembl_34_postgresql $ docker exec -it [docker container id] bash

(Docker内) $ pg_restore -d postgres /var/lib/postgresql/data/chembl_34_postgresql.dmp -U postgres -O

(Docker内) $ psql -d postgres -U postgres
posgre: /dt;

CREATE TABLE TEMP_COMPOUND_IC50 AS
	SELECT
    	m.chembl_id AS compound_chembl_id,
		s.canonical_smiles,
    	s.standard_inchi_key AS inchi_key,
    	r.compound_key,
		d.pubmed_id,
    	d.doi,
    	a.description AS assay_description,
		act.standard_type,
		act.standard_relation,
		act.standard_value,
		act.standard_units,
		act.activity_comment,
		t.chembl_id AS target_chembl_id,
		t.pref_name AS target_name,
		t.organism AS target_organism
	FROM compound_structures s
    	RIGHT JOIN molecule_dictionary m ON s.molregno = m.molregno
  		JOIN compound_records r ON m.molregno = r.molregno
  		JOIN docs d ON r.doc_id = d.doc_id
  		JOIN activities act ON r.record_id = act.record_id
  		JOIN assays a ON act.assay_id = a.assay_id
  		JOIN target_dictionary t ON a.tid = t.tid
	AND act.standard_type = 'IC50'
	AND act.standard_units = 'nM’;

COPY TEMP_COMPOUND_IC50 TO '/var/lib/postgresql/data/TEMP_COMPOUND_IC50.csv' DELIMITER ',' CSV HEADER;

CREATE TABLE TEMP_COMPOUND_ID50 AS
	SELECT
    	m.chembl_id AS compound_chembl_id,
		s.canonical_smiles,
		s.standard_inchi_key AS inchi_key,
		r.compound_key,
		d.pubmed_id,
		d.doi,
		a.description AS assay_description,
		act.standard_type,
		act.standard_relation,
		act.standard_value,
		act.standard_units,
		act.activity_comment,
		t.chembl_id AS target_chembl_id,
		t.pref_name AS target_name,
		t.organism AS target_organism
	FROM compound_structures s
 		RIGHT JOIN molecule_dictionary m ON s.molregno = m.molregno
  		JOIN compound_records r ON m.molregno = r.molregno
  		JOIN docs d ON r.doc_id = d.doc_id
  		JOIN activities act ON r.record_id = act.record_id
  		JOIN assays a ON act.assay_id = a.assay_id
  		JOIN target_dictionary t ON a.tid = t.tid
	AND act.standard_type = 'ID50'
	AND act.standard_units = 'nM';

COPY TEMP_COMPOUND_ID50 TO '/var/lib/postgresql/data/TEMP_COMPOUND_ID50.csv' DELIMITER ',' CSV HEADER;

import pandas as pd

df_ic50 = pd.read_csv('chembl_35/chembl_35_postgresql/db/TEMP_COMPOUND_IC50.csv')
df_ic50_human = df_ic50[df_ic50['target_organism'] == 'Homo sapiens']
df_ic50_human = df_ic50_human[['inchi_key', 'standard_value', 'target_name']].drop_duplicates().dropna()
df_ic50_human.head()