Investigate the metadata of a protein detective session.¶
This notebook uses a session generated by running the commands (first incantation of each command) in the README.md.
In [1]:
Copied!
from pathlib import Path
session_dir = Path("../mysession")
duckdb_file = (session_dir / "meta.duckdb").absolute()
from pathlib import Path
session_dir = Path("../mysession")
duckdb_file = (session_dir / "meta.duckdb").absolute()
In [ ]:
Copied!
from protein_detective.meta import create_meta_duckdb_file
create_meta_duckdb_file(session_dir, duckdb_file=duckdb_file)
from protein_detective.meta import create_meta_duckdb_file
create_meta_duckdb_file(session_dir, duckdb_file=duckdb_file)
Query duckdb database¶
Getting a lay of the land by looking at the tables and their relationships in an ER diagram:
(Diagram generation)
This diagram was initially generated with pnpx duckerd -d mysession/meta.duckdb -m docs/meta-er.mmd -o /tmp/notneeded.svg.
The Mermaid diagram at meta-er.mmd was edited to remove the meta. prefix from table names and relationships were added manually.
The final diagram as svg was generated from meta-er.mmd using pnpx --package @mermaid-js/mermaid-cli -- mmdc -i docs/meta-er.mmd -o docs/meta-er.svg -w 1200 -H 1600
In [2]:
Copied!
%config SqlMagic.autopandas = True
%config SqlMagic.feedback = False
%config SqlMagic.displaycon = False
%config SqlMagic.autopandas = True
%config SqlMagic.feedback = False
%config SqlMagic.displaycon = False
In [3]:
Copied!
import duckdb
import pandas as pd
pd.set_option("display.max_colwidth", None)
%load_ext sql
conn = duckdb.connect(duckdb_file, read_only=True)
%sql conn --alias duckdb
import duckdb
import pandas as pd
pd.set_option("display.max_colwidth", None)
%load_ext sql
conn = duckdb.connect(duckdb_file, read_only=True)
%sql conn --alias duckdb
The 'toml' package isn't installed. To load settings from pyproject.toml or ~/.jupysql/config, install with: pip install toml
In [4]:
Copied!
%sql duckdb
%sql duckdb
Each protein-detective command execution is recorded as an RO-crate create action, which can be queried from the rocrate_create_actions table.
In [5]:
Copied!
%sql SELECT command,startTime,endTime,agent,object,result FROM rocrate_create_actions ORDER BY startTime;
%sql SELECT command,startTime,endTime,agent,object,result FROM rocrate_create_actions ORDER BY startTime;
Out[5]:
| command | startTime | endTime | agent | object | result | |
|---|---|---|---|---|---|---|
| 0 | protein-detective search --taxon-id 9606 --reviewed --subcellular-location-uniprot nucleus --subcellular-location-go GO:0005634 --molecular-function-go GO:0003677 --limit-uniprot 100 --pdbe.limit 100 ./mysession | 2026-08-26T09:39:24.039958+00:00 | 2026-08-26T09:39:31.514802+00:00 | {'@id': 'stefanv'} | <NA> | [{'@id': 'uniprot.txt'}, {'@id': 'alphafold.csv'}, {'@id': 'pdbe.csv'}, {'@id': 'pdbe-quality.json'}] |
| 1 | protein-detective retrieve ./mysession | 2026-08-26T09:39:40.212855+00:00 | 2026-08-26T09:39:41.531971+00:00 | {'@id': 'stefanv'} | [{'@id': 'alphafold.csv'}, {'@id': 'pdbe.csv'}] | [{'@id': 'pdbe_stats.csv'}, {'@id': 'alphafold_stats.csv'}, {'@id': 'downloads/pdbe/'}, {'@id': 'downloads/alphafold/'}] |
| 2 | protein-detective filter --min-confidence 50 --min-residues 100 --max-residues 1000 ./mysession | 2026-08-26T09:39:48.253670+00:00 | 2026-08-26T09:39:52.582074+00:00 | {'@id': 'stefanv'} | [{'@id': 'pdbe.csv'}, {'@id': 'pdbe-quality.json'}, {'@id': 'downloads/pdbe/'}, {'@id': 'downloads/alphafold/'}] | [{'@id': 'uniprots_verified_stats.csv'}, {'@id': 'single_chain_stats.csv'}, {'@id': 'combined_stats.csv'}, {'@id': 'merge_structure_files.csv'}, {'@id': 'uniprots_verified/'}, {'@id': 'single_chain/'}, {'@id': 'combined_input/'}, {'@id': 'combined_output/'}] |
| 3 | protein-detective powerfit run ../powerfit-tutorial/ribosome-KsgA.map 13 --angle 20 --gpu-backend cuda ./mysession | 2026-08-26T09:42:42.758179+00:00 | 2026-08-26T09:43:28.572273+00:00 | {'@id': 'stefanv'} | [{'@id': 'powerfit/run_001/ribosome-KsgA.map'}, {'@id': 'combined_output/'}] | [{'@id': 'powerfit/fittable_structures.csv'}, {'@id': 'powerfit/run_001/'}] |
| 4 | protein-detective powerfit fit-models mysession | 2026-08-26T09:43:51.159738+00:00 | 2026-08-26T09:43:53.161522+00:00 | {'@id': 'stefanv'} | [{'@id': 'single_chain/3i8z_updated_A2A.cif.gz'}, {'@id': 'single_chain/6mzc_updated_E2A.cif.gz'}, {'@id': 'single_chain/6mzd_updated_D2A.cif.gz'}, {'@id': 'combined_output/AF-O00110-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-E7ETH6-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A1W2PPK0-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-B2RXF5-F1-model_v6.cif.gz'}, {'@id': 'single_chain/6mew_updated_A2A.cif.gz'}, {'@id': 'combined_output/AF-E9PAV3-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A2R8Y619-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A5F9ZHS7-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NDR6-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A1W2PPM1-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A8MUZ8-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-B2RPK0-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A1W2PPF3-F1-model_v6.cif.gz'}, {'@id': 'single_chain/2jgn_updated_A2A.cif.gz'}, {'@id': 'combined_output/AF-A6NFD8-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NGD5-F1-model_v6.cif.gz'}, {'@id': 'single_chain/3v30_updated_A2A.cif.gz'}, {'@id': 'combined_output/AF-C9JBD0-F1-model_v6.cif.gz'}, {'@id': 'single_chain/6mzm_updated_D2A.cif.gz'}, {'@id': 'single_chain/2p6v_updated_A2A.cif.gz'}, {'@id': 'combined_output/AF-A8MWA4-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NE82-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NDZ8-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-B2RD01-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A1B0GTS1-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A1W2PQL4-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A8MTJ6-F1-model_v6.cif.gz'}, {'@id': 'single_chain/3uxg_updated_A2A.cif.gz'}, {'@id': 'combined_output/AF-E9PGG2-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NM28-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NK53-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A3B3IU63-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A1W2PRP0-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O00287-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A8K8V0-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NI15-F1-model_v6.cif.gz'}, {'@id': 'single_chain/5l11_updated_A2A.cif.gz'}, {'@id': 'combined_output/AF-A8MZ59-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A1YPR0-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NFQ7-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A1B0GWH4-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NLW8-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NJ08-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O14628-F1-model_v6.cif.gz'}, {'@id': 'single_chain/5y7y_updated_A2A.cif.gz'}, {'@id': 'combined_output/AF-A8MT65-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A9YTQ3-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NJG6-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A1B0GVZ6-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NJL1-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O00470-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O00570-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O00712-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A087WUV0-F1-model_v6.cif.gz'}, {'@id': 'single_chain/4pld_updated_A2A.cif.gz'}, {'@id': 'single_chain/4oni_updated_A2A.cif.gz'}, {'@id': 'combined_output/AF-O14593-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O00321-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O00716-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A1W2PQ73-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-B4DX44-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A8K830-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O00409-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A8K0S8-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A2RRD8-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A2RU54-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NP11-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O14627-F1-model_v6.cif.gz'}, {'@id': 'single_chain/4ple_updated_A2A.cif.gz'}, {'@id': 'combined_output/AF-A6NHJ4-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NJT0-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O00327-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A1A519-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O00358-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O00482-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O14503-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NMT0-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O00257-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A0A2Z4LIS9-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NHT5-F1-model_v6.cif.gz'}, {'@id': 'single_chain/3plz_updated_A2A.cif.gz'}, {'@id': 'combined_output/AF-C9JN71-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-B4DU55-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NK75-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NFI3-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-C9JSJ3-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NNA5-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-B4DXR9-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-O00268-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NDX5-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A8MTY0-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NNF4-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NJ46-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A8MYZ6-F1-model_v6.cif.gz'}, {'@id': 'combined_output/AF-A6NCS4-F1-model_v6.cif.gz'}, {'@id': 'single_chain/4og4_updated_A2A.cif.gz'}, {'@id': 'combined_output/AF-B7Z6K7-F1-model_v6.cif.gz'}, ...] | [{'@id': 'powerfit/run_001/3i8z_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/6mzc_updated_E2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/6mzd_updated_D2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00110-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-E7ETH6-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A1W2PPK0-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-B2RXF5-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/6mew_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-E9PAV3-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A2R8Y619-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A5F9ZHS7-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NDR6-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A1W2PPM1-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A8MUZ8-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-B2RPK0-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A1W2PPF3-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/2jgn_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NFD8-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NGD5-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/3v30_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-C9JBD0-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/6mzm_updated_D2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/2p6v_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A8MWA4-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NE82-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NDZ8-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-B2RD01-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A1B0GTS1-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A1W2PQL4-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A8MTJ6-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/3uxg_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-E9PGG2-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NM28-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NK53-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A3B3IU63-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A1W2PRP0-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00287-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A8K8V0-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NI15-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/5l11_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A8MZ59-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A1YPR0-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NFQ7-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A1B0GWH4-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NLW8-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NJ08-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O14628-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/5y7y_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A8MT65-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A9YTQ3-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NJG6-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A1B0GVZ6-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NJL1-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00470-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00570-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00712-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A087WUV0-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/4pld_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/4oni_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O14593-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00321-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00716-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A1W2PQ73-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-B4DX44-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A8K830-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00409-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A8K0S8-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A2RRD8-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A2RU54-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NP11-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O14627-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/4ple_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NHJ4-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NJT0-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00327-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A1A519-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00358-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00482-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O14503-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NMT0-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00257-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A0A2Z4LIS9-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NHT5-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/3plz_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-C9JN71-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-B4DU55-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NK75-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NFI3-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-C9JSJ3-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NNA5-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-B4DXR9-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-O00268-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NDX5-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A8MTY0-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NNF4-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NJ46-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A8MYZ6-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-A6NCS4-F1-model_v6.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/4og4_updated_A2A.cif.gz/fit_1.pdb'}, {'@id': 'powerfit/run_001/AF-B7Z6K7-F1-model_v6.cif.gz/fit_1.pdb'}, ...] |
Notice that the file paths are relative to the session directory.
Filter input and output structure files¶
Let's look at the input structure files of the filter command.
In [6]:
Copied!
%%sql
SELECT a.command, o.object_id, n.type, n.description , f.file
FROM rocrate_create_actions a
JOIN rocrate_objects o ON a.id = o.action_id
JOIN rocrate_inodes n ON o.object_id = n.id
JOIN structure_files f ON n.name = f.parent_dir
WHERE a.command LIKE '%filter%'
%%sql
SELECT a.command, o.object_id, n.type, n.description , f.file
FROM rocrate_create_actions a
JOIN rocrate_objects o ON a.id = o.action_id
JOIN rocrate_inodes n ON o.object_id = n.id
JOIN structure_files f ON n.name = f.parent_dir
WHERE a.command LIKE '%filter%'
Out[6]:
| command | object_id | type | description | file | |
|---|---|---|---|---|---|
| 0 | protein-detective filter --min-confidence 50 --min-residues 100 --max-residues 1000 ./mysession | downloads/alphafold/ | Dataset | Directory where the AlphaFold files were downloaded. | downloads/alphafold/AF-A0A087WUV0-F1-model_v6.cif.gz |
| 1 | protein-detective filter --min-confidence 50 --min-residues 100 --max-residues 1000 ./mysession | downloads/alphafold/ | Dataset | Directory where the AlphaFold files were downloaded. | downloads/alphafold/AF-A0A0C5B5G6-F1-model_v6.cif.gz |
| 2 | protein-detective filter --min-confidence 50 --min-residues 100 --max-residues 1000 ./mysession | downloads/alphafold/ | Dataset | Directory where the AlphaFold files were downloaded. | downloads/alphafold/AF-A0A0U1RQI7-F1-model_v6.cif.gz |
| 3 | protein-detective filter --min-confidence 50 --min-residues 100 --max-residues 1000 ./mysession | downloads/alphafold/ | Dataset | Directory where the AlphaFold files were downloaded. | downloads/alphafold/AF-A0A1B0GTS1-F1-model_v6.cif.gz |
| 4 | protein-detective filter --min-confidence 50 --min-residues 100 --max-residues 1000 ./mysession | downloads/alphafold/ | Dataset | Directory where the AlphaFold files were downloaded. | downloads/alphafold/AF-A0A1B0GVZ6-F1-model_v6.cif.gz |
| ... | ... | ... | ... | ... | ... |
| 139 | protein-detective filter --min-confidence 50 --min-residues 100 --max-residues 1000 ./mysession | downloads/pdbe/ | Dataset | Directory where the PDBe files were downloaded. | downloads/pdbe/6mzd_updated.cif.gz |
| 140 | protein-detective filter --min-confidence 50 --min-residues 100 --max-residues 1000 ./mysession | downloads/pdbe/ | Dataset | Directory where the PDBe files were downloaded. | downloads/pdbe/6mzm_updated.cif.gz |
| 141 | protein-detective filter --min-confidence 50 --min-residues 100 --max-residues 1000 ./mysession | downloads/pdbe/ | Dataset | Directory where the PDBe files were downloaded. | downloads/pdbe/6nce_updated.cif.gz |
| 142 | protein-detective filter --min-confidence 50 --min-residues 100 --max-residues 1000 ./mysession | downloads/pdbe/ | Dataset | Directory where the PDBe files were downloaded. | downloads/pdbe/6ncm_updated.cif.gz |
| 143 | protein-detective filter --min-confidence 50 --min-residues 100 --max-residues 1000 ./mysession | downloads/pdbe/ | Dataset | Directory where the PDBe files were downloaded. | downloads/pdbe/6o5i_updated.cif.gz |
144 rows × 5 columns
Similar but for output structure files of the filter command. Above we can see that the filter command outputted a 'combined_stats.csv' file, which can be queried using the combined_stats table.
Lets first look at why 1H3O did not pass the filter.
In [7]:
Copied!
%%sql
SELECT * FROM combined_stats WHERE structure_id ='1H3O';
%%sql
SELECT * FROM combined_stats WHERE structure_id ='1H3O';
Out[7]:
| input_file | structure_id | uniprot_accession | resolution | high_confidence_residues_count | total_residue_count | method | is_alphafold | uniprot_start | uniprot_end | sequence_identity | chain_length | geometry_quality | passed | output_file | reason | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | combined_input/1h3o_updated_A2A.cif.gz | 1H3O | O00268 | 2.3 | <NA> | 50 | X-ray | False | 870 | 943 | 1.0 | 50 | 8.53 | False | None | Chain length 50 not in range [100, 1000] |
Ah, it has a residue count of 50 while filter had 100..1000 as valid range.
Lets look at a passing structure
In [8]:
Copied!
%sql SELECT * FROM combined_stats WHERE passed=True LIMIT 1;
%sql SELECT * FROM combined_stats WHERE passed=True LIMIT 1;
Out[8]:
| input_file | structure_id | uniprot_accession | resolution | high_confidence_residues_count | total_residue_count | method | is_alphafold | uniprot_start | uniprot_end | sequence_identity | chain_length | geometry_quality | passed | output_file | reason | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | combined_input/AF-A0A087WUV0-F1-model_v6.cif.gz | AF-A0A087WUV0-F1 | A0A087WUV0 | 0.0 | 328 | 522 | Predicted | True | 1 | 522 | 1.0 | 522 | NaN | True | combined_output/AF-A0A087WUV0-F1-model_v6.cif.gz | None |
Powerfit solutions¶
Lets get the top 10 best overall ranked solutions taking the best ranked solution for each structure.
In [9]:
Copied!
%%sql
SELECT ROW_NUMBER() OVER (ORDER BY cc DESC) AS overall_rank, *
FROM solutions WHERE rank=1 ORDER BY cc DESC LIMIT 10;
%%sql
SELECT ROW_NUMBER() OVER (ORDER BY cc DESC) AS overall_rank, *
FROM solutions WHERE rank=1 ORDER BY cc DESC LIMIT 10;
Out[9]:
| overall_rank | powerfit_run_id | structure | rank | cc | fishz | relz | translation | rotation | template_file | uniprot_accessions | structure_id | is_alphafold | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1 | run_001 | 3i8z_updated_A2A.cif.gz | 1 | 0.617 | 0.720 | 13.521000 | [217.97, 147.36, 208.76] | [0.0, 0.604, -0.797, -0.0, -0.797, -0.604, -1.0, 0.0, 0.0] | combined_output/3i8z_updated_A2A.cif.gz | O00257 | 3I8Z | False |
| 1 | 2 | run_001 | 6mzc_updated_E2A.cif.gz | 1 | 0.547 | 0.614 | 14.671000 | [199.55, 214.9, 165.78] | [1.0, 0.0, 0.0, 0.0, -1.0, 0.0, 0.0, 0.0, -1.0] | combined_output/6mzc_updated_E2A.cif.gz | O00268 | 6MZC | False |
| 2 | 3 | run_001 | 6mzd_updated_D2A.cif.gz | 1 | 0.519 | 0.575 | 13.761000 | [273.23, 251.74, 165.78] | [0.896, 0.258, -0.362, -0.258, -0.362, -0.896, -0.362, 0.896, -0.258] | combined_output/6mzd_updated_D2A.cif.gz | O00268 | 6MZD | False |
| 3 | 4 | run_001 | AF-O00110-F1-model_v6.cif.gz | 1 | 0.496 | 0.543 | 15.671000 | [144.29, 147.36, 224.11] | [-0.816, 0.184, 0.548, 0.548, 0.548, 0.632, -0.184, 0.816, -0.548] | combined_output/AF-O00110-F1-model_v6.cif.gz | O00110 | AF-O00110-F1 | True |
| 4 | 5 | run_001 | AF-E7ETH6-F1-model_v6.cif.gz | 1 | 0.475 | 0.516 | 18.344999 | [217.97, 199.55, 221.04] | [-0.184, -0.816, 0.548, -0.816, -0.184, -0.548, 0.548, -0.548, -0.632] | combined_output/AF-E7ETH6-F1-model_v6.cif.gz | E7ETH6 | AF-E7ETH6-F1 | True |
| 5 | 6 | run_001 | AF-A0A1W2PPK0-F1-model_v6.cif.gz | 1 | 0.473 | 0.513 | 14.219000 | [162.71, 132.01, 165.78] | [0.797, 0.604, 0.0, 0.0, -0.0, -1.0, -0.604, 0.797, -0.0] | combined_output/AF-A0A1W2PPK0-F1-model_v6.cif.gz | A0A1W2PPK0 | AF-A0A1W2PPK0-F1 | True |
| 6 | 7 | run_001 | AF-B2RXF5-F1-model_v6.cif.gz | 1 | 0.472 | 0.513 | 16.382999 | [168.85, 135.08, 190.34] | [-0.548, -0.548, -0.632, 0.816, -0.184, -0.548, 0.184, -0.816, 0.548] | combined_output/AF-B2RXF5-F1-model_v6.cif.gz | B2RXF5 | AF-B2RXF5-F1 | True |
| 7 | 8 | run_001 | 6mew_updated_A2A.cif.gz | 1 | 0.470 | 0.511 | 16.686001 | [224.11, 260.95, 168.85] | [0.548, -0.632, -0.548, 0.816, 0.548, 0.184, 0.184, -0.548, 0.816] | combined_output/6mew_updated_A2A.cif.gz | O14593 | 6MEW | False |
| 8 | 9 | run_001 | AF-E9PAV3-F1-model_v6.cif.gz | 1 | 0.464 | 0.502 | 14.816000 | [162.71, 144.29, 181.13] | [-0.632, 0.548, -0.548, -0.548, -0.816, -0.184, -0.548, 0.184, 0.816] | combined_output/AF-E9PAV3-F1-model_v6.cif.gz | E9PAV3 | AF-E9PAV3-F1 | True |
| 9 | 10 | run_001 | AF-A0A2R8Y619-F1-model_v6.cif.gz | 1 | 0.463 | 0.501 | 13.182000 | [221.04, 227.18, 190.34] | [0.0, -1.0, 0.0, 0.0, 0.0, 1.0, -1.0, 0.0, 0.0] | combined_output/AF-A0A2R8Y619-F1-model_v6.cif.gz | A0A2R8Y619 | AF-A0A2R8Y619-F1 | True |
Lets say that I know the density map actually is a protein with uniprot accession O14593, and I want to check that it is in the top 10 best overall ranked solutions.
In [10]:
Copied!
%%sql
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY cc DESC) AS overall_rank, *
FROM solutions WHERE rank=1 ORDER BY cc DESC LIMIT 10
)
WHERE uniprot_accessions = 'O14593';
%%sql
SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY cc DESC) AS overall_rank, *
FROM solutions WHERE rank=1 ORDER BY cc DESC LIMIT 10
)
WHERE uniprot_accessions = 'O14593';
Out[10]:
| overall_rank | powerfit_run_id | structure | rank | cc | fishz | relz | translation | rotation | template_file | uniprot_accessions | structure_id | is_alphafold | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 8 | run_001 | 6mew_updated_A2A.cif.gz | 1 | 0.47 | 0.511 | 16.686001 | [224.11, 260.95, 168.85] | [0.548, -0.632, -0.548, 0.816, 0.548, 0.184, 0.184, -0.548, 0.816] | combined_output/6mew_updated_A2A.cif.gz | O14593 | 6MEW | False |
Yep, it is ranked 8 overall as the 6MEW pdb id.
PDB and AlphaFold coverage per accession¶
Identify proteins with many experimental structures and those represented only by AlphaFold.
In [11]:
Copied!
%%sql
SELECT u.uniprot_accession,
count(DISTINCT p.pdb_id) AS pdb_structures,
count(DISTINCT af.af_id) AS alphafold_models
FROM uniprot u
LEFT JOIN pdbe p USING (uniprot_accession)
LEFT JOIN alphafold af USING (uniprot_accession)
GROUP BY u.uniprot_accession
ORDER BY pdb_structures DESC, u.uniprot_accession;
%%sql
SELECT u.uniprot_accession,
count(DISTINCT p.pdb_id) AS pdb_structures,
count(DISTINCT af.af_id) AS alphafold_models
FROM uniprot u
LEFT JOIN pdbe p USING (uniprot_accession)
LEFT JOIN alphafold af USING (uniprot_accession)
GROUP BY u.uniprot_accession
ORDER BY pdb_structures DESC, u.uniprot_accession;
Out[11]:
| uniprot_accession | pdb_structures | alphafold_models | |
|---|---|---|---|
| 0 | A8MT69 | 5 | 1 |
| 1 | O00255 | 5 | 1 |
| 2 | O00268 | 5 | 1 |
| 3 | O00482 | 5 | 1 |
| 4 | O00571 | 5 | 1 |
| ... | ... | ... | ... |
| 95 | O00712 | 0 | 1 |
| 96 | O00716 | 0 | 1 |
| 97 | O14503 | 0 | 1 |
| 98 | O14627 | 0 | 1 |
| 99 | O14628 | 0 | 1 |
100 rows × 3 columns
Filter attrition by reason¶
Summarize where structures are lost and contrast failure modes for PDB and AlphaFold models.
In [12]:
Copied!
%%sql
SELECT CASE
WHEN passed THEN 'Passed'
WHEN reason LIKE 'Chain length%' THEN 'Chain length outside range'
WHEN reason LIKE 'Geometry quality%' THEN 'Low geometry quality'
WHEN reason LIKE 'Low confidence%' THEN 'Insufficient high-confidence residues'
ELSE reason
END AS outcome,
count(*) FILTER (WHERE NOT is_alphafold) AS pdb,
count(*) FILTER (WHERE is_alphafold) AS alphafold,
count(*) AS total
FROM combined_stats
GROUP BY outcome
ORDER BY total DESC;
%%sql
SELECT CASE
WHEN passed THEN 'Passed'
WHEN reason LIKE 'Chain length%' THEN 'Chain length outside range'
WHEN reason LIKE 'Geometry quality%' THEN 'Low geometry quality'
WHEN reason LIKE 'Low confidence%' THEN 'Insufficient high-confidence residues'
ELSE reason
END AS outcome,
count(*) FILTER (WHERE NOT is_alphafold) AS pdb,
count(*) FILTER (WHERE is_alphafold) AS alphafold,
count(*) AS total
FROM combined_stats
GROUP BY outcome
ORDER BY total DESC;
Out[12]:
| outcome | pdb | alphafold | total | |
|---|---|---|---|---|
| 0 | Passed | 19 | 95 | 114 |
| 1 | Chain length outside range | 21 | 0 | 21 |
| 2 | Insufficient high-confidence residues | 0 | 5 | 5 |
| 3 | Low geometry quality | 4 | 0 | 4 |
Leaderboard deduplicated by biological target¶
Prevent proteins with multiple PDB structures from occupying several places in the leaderboard.
In [13]:
Copied!
%%sql
WITH best_per_accession AS (
SELECT *
FROM solutions
WHERE rank = 1
QUALIFY row_number() OVER (
PARTITION BY uniprot_accessions ORDER BY cc DESC
) = 1
)
SELECT row_number() OVER (ORDER BY cc DESC) AS overall_rank,
structure_id, uniprot_accessions, is_alphafold, cc
FROM best_per_accession
ORDER BY overall_rank
LIMIT 10;
%%sql
WITH best_per_accession AS (
SELECT *
FROM solutions
WHERE rank = 1
QUALIFY row_number() OVER (
PARTITION BY uniprot_accessions ORDER BY cc DESC
) = 1
)
SELECT row_number() OVER (ORDER BY cc DESC) AS overall_rank,
structure_id, uniprot_accessions, is_alphafold, cc
FROM best_per_accession
ORDER BY overall_rank
LIMIT 10;
Out[13]:
| overall_rank | structure_id | uniprot_accessions | is_alphafold | cc | |
|---|---|---|---|---|---|
| 0 | 1 | 3I8Z | O00257 | False | 0.617 |
| 1 | 2 | 6MZC | O00268 | False | 0.547 |
| 2 | 3 | AF-O00110-F1 | O00110 | True | 0.496 |
| 3 | 4 | AF-E7ETH6-F1 | E7ETH6 | True | 0.475 |
| 4 | 5 | AF-A0A1W2PPK0-F1 | A0A1W2PPK0 | True | 0.473 |
| 5 | 6 | AF-B2RXF5-F1 | B2RXF5 | True | 0.472 |
| 6 | 7 | 6MEW | O14593 | False | 0.470 |
| 7 | 8 | AF-E9PAV3-F1 | E9PAV3 | True | 0.464 |
| 8 | 9 | AF-A0A2R8Y619-F1 | A0A2R8Y619 | True | 0.463 |
| 9 | 10 | AF-A0A5F9ZHS7-F1 | A0A5F9ZHS7 | True | 0.452 |
Best PDB versus AlphaFold model¶
Directly compare experimental and predicted structures for accessions represented by both sources.
In [14]:
Copied!
%%sql
WITH best AS (
SELECT *,
row_number() OVER (
PARTITION BY uniprot_accessions, is_alphafold
ORDER BY cc DESC
) AS source_rank
FROM solutions
WHERE rank = 1
)
SELECT p.uniprot_accessions,
p.structure_id AS best_pdb, p.cc AS pdb_cc,
a.structure_id AS alphafold_model, a.cc AS alphafold_cc,
p.cc - a.cc AS pdb_advantage
FROM best p
JOIN best a USING (uniprot_accessions)
WHERE NOT p.is_alphafold AND a.is_alphafold
AND p.source_rank = 1 AND a.source_rank = 1
ORDER BY abs(p.cc - a.cc) DESC;
%%sql
WITH best AS (
SELECT *,
row_number() OVER (
PARTITION BY uniprot_accessions, is_alphafold
ORDER BY cc DESC
) AS source_rank
FROM solutions
WHERE rank = 1
)
SELECT p.uniprot_accessions,
p.structure_id AS best_pdb, p.cc AS pdb_cc,
a.structure_id AS alphafold_model, a.cc AS alphafold_cc,
p.cc - a.cc AS pdb_advantage
FROM best p
JOIN best a USING (uniprot_accessions)
WHERE NOT p.is_alphafold AND a.is_alphafold
AND p.source_rank = 1 AND a.source_rank = 1
ORDER BY abs(p.cc - a.cc) DESC;
Out[14]:
| uniprot_accessions | best_pdb | pdb_cc | alphafold_model | alphafold_cc | pdb_advantage | |
|---|---|---|---|---|---|---|
| 0 | O00257 | 3I8Z | 0.617 | AF-O00257-F1 | 0.315 | 0.302 |
| 1 | O00268 | 6MZC | 0.547 | AF-O00268-F1 | 0.288 | 0.259 |
| 2 | O00571 | 2JGN | 0.433 | AF-O00571-F1 | 0.263 | 0.170 |
| 3 | O14593 | 6MEW | 0.470 | AF-O14593-F1 | 0.349 | 0.121 |
| 4 | O00482 | 5L11 | 0.381 | AF-O00482-F1 | 0.321 | 0.060 |
| 5 | O14497 | 6LTJ | 0.266 | AF-O14497-F1 | 0.221 | 0.045 |
| 6 | O00255 | 4OG4 | 0.276 | AF-O00255-F1 | 0.236 | 0.040 |
| 7 | A9YTQ3 | 5Y7Y | 0.374 | AF-A9YTQ3-F1 | 0.369 | 0.005 |
Structures with ambiguous top placements¶
Find structures whose first- and second-ranked fits have nearly identical correlation coefficients.
In [15]:
Copied!
%%sql
SELECT structure_id, uniprot_accessions, is_alphafold,
max(cc) FILTER (WHERE rank = 1) AS best_cc,
max(cc) FILTER (WHERE rank = 2) AS second_cc,
best_cc - second_cc AS margin
FROM solutions
GROUP BY ALL
ORDER BY margin
LIMIT 20;
%%sql
SELECT structure_id, uniprot_accessions, is_alphafold,
max(cc) FILTER (WHERE rank = 1) AS best_cc,
max(cc) FILTER (WHERE rank = 2) AS second_cc,
best_cc - second_cc AS margin
FROM solutions
GROUP BY ALL
ORDER BY margin
LIMIT 20;
Out[15]:
| structure_id | uniprot_accessions | is_alphafold | best_cc | second_cc | margin | |
|---|---|---|---|---|---|---|
| 0 | AF-A8K0S8-F1 | A8K0S8 | True | 0.338 | 0.338 | 0.000 |
| 1 | AF-O00712-F1 | O00712 | True | 0.361 | 0.361 | 0.000 |
| 2 | 6LTH | O14497 | False | 0.261 | 0.261 | 0.000 |
| 3 | AF-O00482-F1 | O00482 | True | 0.321 | 0.321 | 0.000 |
| 4 | AF-A8MT65-F1 | A8MT65 | True | 0.370 | 0.370 | 0.000 |
| 5 | AF-A6NJL1-F1 | A6NJL1 | True | 0.366 | 0.366 | 0.000 |
| 6 | AF-B4DU55-F1 | B4DU55 | True | 0.301 | 0.300 | 0.001 |
| 7 | AF-A8K830-F1 | A8K830 | True | 0.339 | 0.338 | 0.001 |
| 8 | 6MEW | O14593 | False | 0.470 | 0.469 | 0.001 |
| 9 | AF-A8MZ59-F1 | A8MZ59 | True | 0.380 | 0.379 | 0.001 |
| 10 | 3I8Z | O00257 | False | 0.617 | 0.616 | 0.001 |
| 11 | AF-A6NHT5-F1 | A6NHT5 | True | 0.305 | 0.304 | 0.001 |
| 12 | AF-B4DX44-F1 | B4DX44 | True | 0.340 | 0.339 | 0.001 |
| 13 | AF-A6NCS4-F1 | A6NCS4 | True | 0.279 | 0.278 | 0.001 |
| 14 | AF-O00327-F1 | O00327 | True | 0.321 | 0.320 | 0.001 |
| 15 | AF-O14503-F1 | O14503 | True | 0.316 | 0.315 | 0.001 |
| 16 | AF-C9JSJ3-F1 | C9JSJ3 | True | 0.297 | 0.295 | 0.002 |
| 17 | AF-A6NDR6-F1 | A6NDR6 | True | 0.448 | 0.446 | 0.002 |
| 18 | 4PLE | O00482 | False | 0.328 | 0.326 | 0.002 |
| 19 | AF-A8MXY4-F1 | A8MXY4 | True | 0.244 | 0.242 | 0.002 |