{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":67356,"databundleVersionId":8006601,"sourceType":"competition"}],"dockerImageVersionId":30673,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"!pip install duckdb","metadata":{"_uuid":"53735182-49bd-44b4-8f10-ce70b39d599b","_cell_guid":"56ffe537-ecdf-4284-8e17-3252edd5ddb5","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:11:22.017678Z","iopub.execute_input":"2024-04-07T16:11:22.018972Z","iopub.status.idle":"2024-04-07T16:11:38.437547Z","shell.execute_reply.started":"2024-04-07T16:11:22.018887Z","shell.execute_reply":"2024-04-07T16:11:38.435885Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import duckdb\nimport pandas as pd\nimport matplotlib.pyplot as plt\n\n\ntrain_path = '/kaggle/input/leash-BELKA/train.parquet'\ntest_path = '/kaggle/input/leash-BELKA/test.parquet'\n\ncon = duckdb.connect()","metadata":{"_uuid":"c84afba9-ab61-40e6-9350-302fd38e0e75","_cell_guid":"e2aca64d-a7bb-42f1-acb6-5b13022d0cbc","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:11:38.440316Z","iopub.execute_input":"2024-04-07T16:11:38.440978Z","iopub.status.idle":"2024-04-07T16:11:39.968536Z","shell.execute_reply.started":"2024-04-07T16:11:38.440934Z","shell.execute_reply":"2024-04-07T16:11:39.967334Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Sample","metadata":{"_uuid":"365e3de5-e8a2-49e4-bc83-4315a9ee8019","_cell_guid":"182e7b94-543b-4d01-adfd-6ce7a039f7f0","trusted":true}},{"cell_type":"code","source":"sample = con.query(f\"\"\"(SELECT * FROM parquet_scan('{train_path}') LIMIT 10)\"\"\").df()\n\nstr(list(sample.columns))","metadata":{"_uuid":"b588916e-dd97-4c3a-8610-12dbc82883ec","_cell_guid":"2ae75e38-8928-415d-ba1d-4886103b6c00","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:35:38.114110Z","iopub.execute_input":"2024-04-07T14:35:38.114807Z","iopub.status.idle":"2024-04-07T14:35:38.224558Z","shell.execute_reply.started":"2024-04-07T14:35:38.114732Z","shell.execute_reply":"2024-04-07T14:35:38.223390Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample","metadata":{"_uuid":"7bca0fc7-bc06-4e3e-8b71-2684d792c31d","_cell_guid":"7097e031-29f0-4691-805d-bbb25c954f95","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:35:38.227059Z","iopub.execute_input":"2024-04-07T14:35:38.227987Z","iopub.status.idle":"2024-04-07T14:35:38.250852Z","shell.execute_reply.started":"2024-04-07T14:35:38.227943Z","shell.execute_reply":"2024-04-07T14:35:38.249817Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"binds_stats = con.query(f\"\"\"(SELECT binds, count(*) as binds_count FROM parquet_scan('{train_path}') GROUP BY binds ORDER BY count(*) DESC)\"\"\").df()\nbinds_stats","metadata":{"_uuid":"d41fe76b-0615-48c8-ba3d-a66b541079df","_cell_guid":"3a3943fb-0ba3-4162-b832-0a1a7ccce847","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:35:38.251914Z","iopub.execute_input":"2024-04-07T14:35:38.252741Z","iopub.status.idle":"2024-04-07T14:35:39.837408Z","shell.execute_reply.started":"2024-04-07T14:35:38.252713Z","shell.execute_reply":"2024-04-07T14:35:39.836176Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Train\n## Targets","metadata":{"_uuid":"109a7356-c3c0-4a14-a10f-04cd74c2dac0","_cell_guid":"4998f243-2fb1-451d-83c1-66fb2b285343","trusted":true}},{"cell_type":"code","source":"plt.bar(binds_stats['binds'], binds_stats['binds_count'])\nplt.xlabel('Binds')\nplt.ylabel('# Count')\nplt.title('Target Distribution')\nplt.show()","metadata":{"_uuid":"4392d9fb-9bf9-40d8-a7c4-5ab3dbe2b055","_cell_guid":"013a6986-98d5-459c-b74b-681f4aa5875b","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:35:39.838626Z","iopub.execute_input":"2024-04-07T14:35:39.838962Z","iopub.status.idle":"2024-04-07T14:35:40.145069Z","shell.execute_reply.started":"2024-04-07T14:35:39.838932Z","shell.execute_reply":"2024-04-07T14:35:40.143963Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Protein","metadata":{"_uuid":"2df290be-2462-4e3d-9c36-a20186a74d27","_cell_guid":"6c3679d1-29c6-4f2f-b34a-2f6a83fdf1d6","trusted":true}},{"cell_type":"code","source":"protein_stats = con.query(f\"\"\"(SELECT protein_name, count(*) as protein_name_count FROM parquet_scan('{train_path}') GROUP BY protein_name ORDER BY count(*) DESC)\"\"\").df()\nprotein_stats","metadata":{"_uuid":"b8b9c429-dc73-4569-b2e7-3b3ff0bac182","_cell_guid":"60a3e946-6c37-4d78-afd2-232230166bfa","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:35:40.146637Z","iopub.execute_input":"2024-04-07T14:35:40.147068Z","iopub.status.idle":"2024-04-07T14:35:44.834670Z","shell.execute_reply.started":"2024-04-07T14:35:40.147031Z","shell.execute_reply":"2024-04-07T14:35:44.833198Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"protein_stats = con.query(f\"\"\"(SELECT protein_name, count(distinct molecule_smiles) as protein_name_count FROM parquet_scan('{train_path}') GROUP BY protein_name ORDER BY count(*) DESC)\"\"\").df()\nprotein_stats","metadata":{"_uuid":"5c60948a-3440-49f5-b764-1d6f613df6b9","_cell_guid":"c328aaf2-1670-404e-a497-37404b829069","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:35:44.835866Z","iopub.execute_input":"2024-04-07T14:35:44.836485Z","iopub.status.idle":"2024-04-07T14:38:27.016657Z","shell.execute_reply.started":"2024-04-07T14:35:44.836453Z","shell.execute_reply":"2024-04-07T14:38:27.013962Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.bar(protein_stats['protein_name'], protein_stats['protein_name_count'])\nplt.xlabel('Protein')\nplt.ylabel('# Count')\nplt.title('Protein Distribution')\nplt.show()","metadata":{"_uuid":"a471393a-1a1f-4ab6-9cdf-0239f77ded87","_cell_guid":"f76d0802-8587-4ed1-8a78-e70c551dc4af","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:27.020588Z","iopub.execute_input":"2024-04-07T14:38:27.021323Z","iopub.status.idle":"2024-04-07T14:38:27.350813Z","shell.execute_reply.started":"2024-04-07T14:38:27.021254Z","shell.execute_reply":"2024-04-07T14:38:27.348867Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"protein_binds_stats = con.query(f\"\"\"(SELECT protein_name, binds, count(*) as protein_name_count FROM parquet_scan('{train_path}') GROUP BY protein_name, binds ORDER BY count(*) DESC)\"\"\").df()\nprotein_binds_stats","metadata":{"_uuid":"17d8e97d-8aa5-49fb-a395-3f9fec70e885","_cell_guid":"4a244c38-b216-4ae5-a67c-e2d475f17ca4","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:27.356260Z","iopub.execute_input":"2024-04-07T14:38:27.356709Z","iopub.status.idle":"2024-04-07T14:38:33.698837Z","shell.execute_reply.started":"2024-04-07T14:38:27.356675Z","shell.execute_reply":"2024-04-07T14:38:33.697713Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"grouped = protein_binds_stats.groupby(['protein_name', 'binds']).sum().unstack('binds')\ngrouped.plot(kind='bar', stacked=False)\nplt.xlabel('Protein Name')\nplt.ylabel('Count')\nplt.title('# Protein Names by Binds Status')\nplt.legend(labels=['binds 0', 'binds 1'])\nplt.show()","metadata":{"_uuid":"55a143fc-7a7c-4e23-9ef5-876b91843bff","_cell_guid":"f18be495-d830-402e-89d0-19103ebbd859","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:33.700061Z","iopub.execute_input":"2024-04-07T14:38:33.700553Z","iopub.status.idle":"2024-04-07T14:38:33.981161Z","shell.execute_reply.started":"2024-04-07T14:38:33.700518Z","shell.execute_reply":"2024-04-07T14:38:33.980056Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"grouped = protein_binds_stats[protein_binds_stats['binds']==1].groupby(['protein_name', 'binds']).sum().unstack('binds')\ngrouped.plot(kind='bar', stacked=False)\nplt.xlabel('Protein Name')\nplt.ylabel('Count')\nplt.title('# Protein Names by Binds Status')\nplt.legend(labels=['binds 1'])\nplt.show()","metadata":{"_uuid":"91a5a6b8-0f2b-4e66-98a1-529bde4a74a9","_cell_guid":"981b1155-99ec-47b8-bef4-4f9615198698","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:33.982568Z","iopub.execute_input":"2024-04-07T14:38:33.983076Z","iopub.status.idle":"2024-04-07T14:38:34.205369Z","shell.execute_reply.started":"2024-04-07T14:38:33.983047Z","shell.execute_reply":"2024-04-07T14:38:34.204190Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## buildingblock1 smiles","metadata":{"_uuid":"2fa0d76b-5bf5-4238-8522-4c577f297de1","_cell_guid":"f1e027e6-df75-4f71-83c4-378ede91cffb","trusted":true}},{"cell_type":"code","source":"buildingblock1_smiles_stats = con.query(f\"\"\"(SELECT buildingblock1_smiles, count(*) as buildingblock1_smiles_count FROM parquet_scan('{train_path}') GROUP BY buildingblock1_smiles ORDER BY count(*) DESC)\"\"\").df()\nbuildingblock1_smiles_stats","metadata":{"_uuid":"fdf3dc8c-7489-4f87-8241-7979180c128d","_cell_guid":"7d554fb3-20f6-4cee-8f85-f35ee2ae797e","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:34.207217Z","iopub.execute_input":"2024-04-07T14:38:34.207930Z","iopub.status.idle":"2024-04-07T14:38:40.180738Z","shell.execute_reply.started":"2024-04-07T14:38:34.207888Z","shell.execute_reply":"2024-04-07T14:38:40.179548Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.bar(buildingblock1_smiles_stats.index, buildingblock1_smiles_stats['buildingblock1_smiles_count'])\nplt.yscale('log')\nplt.ylabel('Log of Buildingblock1 Smiles Count')\nplt.title('# buildingblock1_smiles')\nplt.tight_layout()\nplt.show()","metadata":{"_uuid":"6d093d6c-a1db-4c90-be7c-abfe4912e466","_cell_guid":"a28b8265-9232-4048-966b-d8c52eded8ee","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:40.182689Z","iopub.execute_input":"2024-04-07T14:38:40.183142Z","iopub.status.idle":"2024-04-07T14:38:41.268047Z","shell.execute_reply.started":"2024-04-07T14:38:40.183103Z","shell.execute_reply":"2024-04-07T14:38:41.267232Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"buildingblock1_smiles_1_stats = con.query(f\"\"\"(SELECT buildingblock1_smiles, count(*) as buildingblock1_smiles_count FROM parquet_scan('{train_path}') WHERE binds=1 GROUP BY buildingblock1_smiles ORDER BY count(*) DESC)\"\"\").df()\nbuildingblock1_smiles_1_stats","metadata":{"_uuid":"f23a1a89-cee0-46d2-bb39-05da95ce11c4","_cell_guid":"b36cdd14-69fd-4902-b953-be875a7a1971","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:41.269165Z","iopub.execute_input":"2024-04-07T14:38:41.269611Z","iopub.status.idle":"2024-04-07T14:38:42.978198Z","shell.execute_reply.started":"2024-04-07T14:38:41.269586Z","shell.execute_reply":"2024-04-07T14:38:42.977071Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.bar(buildingblock1_smiles_1_stats.index, buildingblock1_smiles_1_stats['buildingblock1_smiles_count'])\nplt.yscale('log')\nplt.ylabel('Log of Buildingblock1 Smiles 1 Count')\nplt.title('# buildingblock1_smiles_1_stats')\nplt.tight_layout()\nplt.show()","metadata":{"_uuid":"e76156ae-51ae-47e0-8ca3-78b47608b69d","_cell_guid":"7f300047-e080-4247-b728-185af9da7deb","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:42.979857Z","iopub.execute_input":"2024-04-07T14:38:42.980265Z","iopub.status.idle":"2024-04-07T14:38:44.049648Z","shell.execute_reply.started":"2024-04-07T14:38:42.980236Z","shell.execute_reply":"2024-04-07T14:38:44.048429Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"buildingblock2_smiles_stats = con.query(f\"\"\"(SELECT buildingblock2_smiles, count(*) as buildingblock2_smiles_count FROM parquet_scan('{train_path}') GROUP BY buildingblock2_smiles ORDER BY count(*) DESC)\"\"\").df()\nbuildingblock2_smiles_stats","metadata":{"_uuid":"b5724b42-ee07-402f-a366-a31e8da677f5","_cell_guid":"7de5c22b-9632-49f4-9517-873233223e55","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:44.051371Z","iopub.execute_input":"2024-04-07T14:38:44.051715Z","iopub.status.idle":"2024-04-07T14:38:49.797395Z","shell.execute_reply.started":"2024-04-07T14:38:44.051685Z","shell.execute_reply":"2024-04-07T14:38:49.796450Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## buildingblock2 smiles","metadata":{"_uuid":"8f1f3d44-7dc3-4a8e-ab68-f5732858c9be","_cell_guid":"751f25e1-3bfc-4c76-8279-5a22a0eb830e","trusted":true}},{"cell_type":"code","source":"plt.bar(buildingblock2_smiles_stats.index, buildingblock2_smiles_stats['buildingblock2_smiles_count'])\nplt.yscale('log')\nplt.ylabel('Log of Buildingblock2 Smiles Count')\nplt.title('# buildingblock2_smiles')\nplt.tight_layout()\nplt.show()","metadata":{"_uuid":"352da389-1d9a-44fd-99bb-59a11a586c49","_cell_guid":"9d2811db-6632-4e01-91ab-953b95ea9f94","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:49.798576Z","iopub.execute_input":"2024-04-07T14:38:49.798915Z","iopub.status.idle":"2024-04-07T14:38:51.490309Z","shell.execute_reply.started":"2024-04-07T14:38:49.798888Z","shell.execute_reply":"2024-04-07T14:38:51.489173Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"buildingblock2_smiles_1_stats = con.query(f\"\"\"(SELECT buildingblock2_smiles, count(*) as buildingblock2_smiles_count_1 FROM parquet_scan('{train_path}') WHERE binds=1 GROUP BY buildingblock2_smiles ORDER BY count(*) DESC)\"\"\").df()\nbuildingblock2_smiles_1_stats","metadata":{"_uuid":"4a2921e0-042c-460d-8e0d-6896cc976979","_cell_guid":"f0e4477a-01b8-4a3e-a105-4be5156a9539","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:51.491761Z","iopub.execute_input":"2024-04-07T14:38:51.492075Z","iopub.status.idle":"2024-04-07T14:38:53.239304Z","shell.execute_reply.started":"2024-04-07T14:38:51.492048Z","shell.execute_reply":"2024-04-07T14:38:53.238224Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.bar(buildingblock2_smiles_1_stats.index, buildingblock2_smiles_1_stats['buildingblock2_smiles_count_1'])\nplt.yscale('log')\nplt.ylabel('Log of Buildingblock2 Smiles 1 Count')\nplt.title('# buildingblock1_smiles_2_stats')\nplt.tight_layout()\nplt.show()","metadata":{"_uuid":"6e75460b-f225-48ab-841b-00ee42c210f5","_cell_guid":"4b1dbef9-dc79-4214-924a-dc96110d0b47","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:53.240634Z","iopub.execute_input":"2024-04-07T14:38:53.241073Z","iopub.status.idle":"2024-04-07T14:38:54.880518Z","shell.execute_reply.started":"2024-04-07T14:38:53.241034Z","shell.execute_reply":"2024-04-07T14:38:54.879695Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## buildingblock3 smiles","metadata":{"_uuid":"f87385eb-3934-4cb0-8176-52a5d40dfe0e","_cell_guid":"b0650e5f-2069-47ad-8b07-969743806f08","trusted":true}},{"cell_type":"code","source":"buildingblock3_smiles_stats = con.query(f\"\"\"(SELECT buildingblock3_smiles, count(*) as buildingblock3_smiles_count FROM parquet_scan('{train_path}') GROUP BY buildingblock3_smiles ORDER BY count(*) DESC)\"\"\").df()\nbuildingblock3_smiles_stats","metadata":{"_uuid":"29aa2c58-4ec5-4788-92ff-4dffa747920e","_cell_guid":"6eea54f2-ee3d-4e40-8416-de5d741f5ad2","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:38:54.881730Z","iopub.execute_input":"2024-04-07T14:38:54.882729Z","iopub.status.idle":"2024-04-07T14:39:01.969782Z","shell.execute_reply.started":"2024-04-07T14:38:54.882698Z","shell.execute_reply":"2024-04-07T14:39:01.968664Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.bar(buildingblock3_smiles_stats.index, buildingblock3_smiles_stats['buildingblock3_smiles_count'])\nplt.yscale('log')\nplt.ylabel('Log of Buildingblock3 Smiles Count')\nplt.title('# buildingblock3_smiles')\nplt.tight_layout()\nplt.show()","metadata":{"_uuid":"399dfc2a-f78e-44d4-99a2-8b90b6184f08","_cell_guid":"2e7a77b9-c3ac-4607-88d0-2fde9901e281","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:39:01.971127Z","iopub.execute_input":"2024-04-07T14:39:01.971511Z","iopub.status.idle":"2024-04-07T14:39:04.048223Z","shell.execute_reply.started":"2024-04-07T14:39:01.971485Z","shell.execute_reply":"2024-04-07T14:39:04.046919Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"buildingblock3_smiles_1_stats = con.query(f\"\"\"(SELECT buildingblock3_smiles, count(*) as buildingblock3_smiles_count_1 FROM parquet_scan('{train_path}') WHERE binds=1 GROUP BY buildingblock3_smiles ORDER BY count(*) DESC)\"\"\").df()\nbuildingblock3_smiles_1_stats","metadata":{"_uuid":"510cb22f-801f-46bb-a8d6-6ab1bdbe6643","_cell_guid":"7d21710a-8ddf-4952-9568-17ca0949a836","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:39:04.049488Z","iopub.execute_input":"2024-04-07T14:39:04.049810Z","iopub.status.idle":"2024-04-07T14:39:06.654658Z","shell.execute_reply.started":"2024-04-07T14:39:04.049783Z","shell.execute_reply":"2024-04-07T14:39:06.653630Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.bar(buildingblock3_smiles_1_stats.index, buildingblock3_smiles_1_stats['buildingblock3_smiles_count_1'])\nplt.yscale('log')\nplt.ylabel('Log of Buildingblock3 Smiles Count')\nplt.title('# buildingblock3_smiles')\nplt.tight_layout()\nplt.show()","metadata":{"_uuid":"e36b5eee-f862-43dd-bffb-4057584cdb21","_cell_guid":"4486ee7e-9dc1-426a-91b6-c2bbcd26bc2f","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:39:06.656529Z","iopub.execute_input":"2024-04-07T14:39:06.657310Z","iopub.status.idle":"2024-04-07T14:39:08.793220Z","shell.execute_reply.started":"2024-04-07T14:39:06.657272Z","shell.execute_reply":"2024-04-07T14:39:08.792260Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT buildingblock3_smiles FROM parquet_scan('{train_path}'))\n              EXCEPT\n              (SELECT buildingblock3_smiles FROM parquet_scan('{train_path}') WHERE binds=1)\n        \"\"\").df()","metadata":{"_uuid":"47ca3e3e-06a4-4ac0-b78f-86f8c5f4ed51","_cell_guid":"82c95245-dacc-4e60-a80a-be98a859c735","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:39:08.794472Z","iopub.execute_input":"2024-04-07T14:39:08.794776Z","iopub.status.idle":"2024-04-07T14:39:18.783002Z","shell.execute_reply.started":"2024-04-07T14:39:08.794734Z","shell.execute_reply":"2024-04-07T14:39:18.781654Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## molecule_smiles","metadata":{"_uuid":"5cff1c42-ca99-4c2f-a291-ff7015440a21","_cell_guid":"4e8c5b7d-3513-4719-b014-732b42e684c1","trusted":true}},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as molecule_smiles FROM (SELECT distinct molecule_smiles FROM parquet_scan('{train_path}')) as t)\"\"\").df()","metadata":{"_uuid":"23617316-b2de-4c0e-893a-c2ffc7a884b9","_cell_guid":"603acf78-cf16-4f5e-a796-38efe68b50fd","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:39:18.785645Z","iopub.execute_input":"2024-04-07T14:39:18.786085Z","iopub.status.idle":"2024-04-07T14:40:05.023069Z","shell.execute_reply.started":"2024-04-07T14:39:18.786045Z","shell.execute_reply":"2024-04-07T14:40:05.021740Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as molecule_smiles_1 FROM (SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') WHERE binds=1) as t)\"\"\").df()","metadata":{"_uuid":"87afd023-9283-48e0-8065-dbb0671d1fc1","_cell_guid":"e51cca94-edd5-4e84-b22a-7880c0f3c668","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:40:05.025160Z","iopub.execute_input":"2024-04-07T14:40:05.026490Z","iopub.status.idle":"2024-04-07T14:40:13.422314Z","shell.execute_reply.started":"2024-04-07T14:40:05.026447Z","shell.execute_reply":"2024-04-07T14:40:13.421183Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as molecule_smiles_common from (\n              (SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') WHERE binds=0 )\n              INTERSECT\n              (SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') WHERE binds=1 ) ) as t)\n        \"\"\").df()","metadata":{"_uuid":"fe5ffdd8-c70f-4b7d-a132-896f51171874","_cell_guid":"e1ec6349-2f38-48ac-a434-be56cf44c995","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:40:13.430725Z","iopub.execute_input":"2024-04-07T14:40:13.431135Z","iopub.status.idle":"2024-04-07T14:41:10.922823Z","shell.execute_reply.started":"2024-04-07T14:40:13.431096Z","shell.execute_reply":"2024-04-07T14:41:10.921836Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as new_molecule_smiles_as_binds_0 from (\n              (SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') WHERE binds=0 )\n              EXCEPT\n              (SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') WHERE binds=1 ) ) as t)\n        \"\"\").df()","metadata":{"_uuid":"ff0ddee7-4bc4-4ee5-b4a8-ad2f087d5b36","_cell_guid":"cda3dc31-8122-482c-9c7c-f91b208dc038","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:41:10.924168Z","iopub.execute_input":"2024-04-07T14:41:10.925139Z","iopub.status.idle":"2024-04-07T14:42:27.263823Z","shell.execute_reply.started":"2024-04-07T14:41:10.925054Z","shell.execute_reply":"2024-04-07T14:42:27.262723Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as new_molecule_smiles_as_binds_1 from (\n              (SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') WHERE binds=1 )\n              EXCEPT\n              (SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') WHERE binds=0 ) ) as t)\n        \"\"\").df()","metadata":{"_uuid":"daa298d4-942a-4fd1-980e-f2d1dd2fd9d5","_cell_guid":"20462113-bad7-4000-bee2-3318ec8687f9","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:42:27.265473Z","iopub.execute_input":"2024-04-07T14:42:27.265887Z","iopub.status.idle":"2024-04-07T14:43:29.372995Z","shell.execute_reply.started":"2024-04-07T14:42:27.265858Z","shell.execute_reply":"2024-04-07T14:43:29.371622Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT * from (\n              (SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') WHERE binds=1 )\n              EXCEPT\n              (SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') WHERE binds=0 ) ) as t)\n        \"\"\").df()","metadata":{"_uuid":"a6110636-6a11-449c-a21e-6ed7bf8c443f","_cell_guid":"9620bfb1-5913-4ae8-b70a-fee720940d0a","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:43:29.374484Z","iopub.execute_input":"2024-04-07T14:43:29.374919Z","iopub.status.idle":"2024-04-07T14:44:41.100004Z","shell.execute_reply.started":"2024-04-07T14:43:29.374884Z","shell.execute_reply":"2024-04-07T14:44:41.098783Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"total 62 molecule smiles are missing overall from binds=0","metadata":{"_uuid":"ca83a6bd-c935-4933-b4a4-fd9da1375267","_cell_guid":"4112fc78-7767-49d2-9496-b0694b890a03","trusted":true}},{"cell_type":"markdown","source":"## All buildingblock smiles","metadata":{"_uuid":"2cbeab54-754e-433d-b48f-a53400852d60","_cell_guid":"cd3eb948-67c6-470f-9d5c-2a3516360098","trusted":true}},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as all_buildingblock_smiles_count FROM (SELECT distinct buildingblock1_smiles,buildingblock2_smiles,buildingblock3_smiles FROM parquet_scan('{train_path}')) as t)\"\"\").df()","metadata":{"_uuid":"2fce9113-c0ab-4bbb-ba76-44ec62f9f67f","_cell_guid":"64f9cc54-0692-488c-948b-3780eeadd9cb","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:44:41.101617Z","iopub.execute_input":"2024-04-07T14:44:41.101967Z","iopub.status.idle":"2024-04-07T14:45:20.360703Z","shell.execute_reply.started":"2024-04-07T14:44:41.101930Z","shell.execute_reply":"2024-04-07T14:45:20.359836Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# 270 x 692 x 871","metadata":{"_uuid":"dd4634b5-1a4b-4519-8cb8-dde1f4c7bd1a","_cell_guid":"f2ea284f-e79a-4739-bb83-9b9257af5cfb","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:10:59.706661Z","iopub.execute_input":"2024-04-07T16:10:59.707415Z","iopub.status.idle":"2024-04-07T16:10:59.737920Z","shell.execute_reply.started":"2024-04-07T16:10:59.707362Z","shell.execute_reply":"2024-04-07T16:10:59.736745Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"f\"{98415610/163764216*100:.2f}%\"","metadata":{"_uuid":"7b840de9-0c41-4459-b31b-6327b253b7fd","_cell_guid":"c1ed935b-2cf4-4bc7-b9b0-c53c1a15bb47","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:11:01.038372Z","iopub.execute_input":"2024-04-07T16:11:01.039574Z","iopub.status.idle":"2024-04-07T16:11:01.054807Z","shell.execute_reply.started":"2024-04-07T16:11:01.039528Z","shell.execute_reply":"2024-04-07T16:11:01.053576Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Test\n## Targets","metadata":{"_uuid":"6b4a47e5-6255-4698-ae12-d3201464714d","_cell_guid":"531e7df3-8f77-4c92-8095-324f61366821","trusted":true}},{"cell_type":"code","source":"binds_stats = con.query(f\"\"\"(SELECT count(*) as binds_count FROM parquet_scan('{test_path}') ORDER BY count(*) DESC)\"\"\").df()\nbinds_stats","metadata":{"_uuid":"1702a3c2-278c-49bc-ab91-82bfb09dcf93","_cell_guid":"4d824a20-03d3-4ec1-8b2b-0459ada58ca8","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:13:13.909448Z","iopub.execute_input":"2024-04-07T16:13:13.912131Z","iopub.status.idle":"2024-04-07T16:13:13.955461Z","shell.execute_reply.started":"2024-04-07T16:13:13.912047Z","shell.execute_reply":"2024-04-07T16:13:13.954222Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Protein","metadata":{"_uuid":"4f49eb6b-3f96-4723-8e32-58066f1e8c63","_cell_guid":"35c7bf92-b003-4e00-90b9-3ba77159c04d","trusted":true}},{"cell_type":"code","source":"protein_stats = con.query(f\"\"\"(SELECT protein_name, count(*) as protein_name_count FROM parquet_scan('{test_path}') GROUP BY protein_name ORDER BY count(*) DESC)\"\"\").df()\nprotein_stats","metadata":{"_uuid":"af05eb30-0fe3-47cf-af99-4607bafd3779","_cell_guid":"2534633c-4059-4019-b847-93dade1116c5","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:13:15.688302Z","iopub.execute_input":"2024-04-07T16:13:15.688758Z","iopub.status.idle":"2024-04-07T16:13:15.769195Z","shell.execute_reply.started":"2024-04-07T16:13:15.688726Z","shell.execute_reply":"2024-04-07T16:13:15.767833Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"protein_stats = con.query(f\"\"\"(SELECT protein_name, count(distinct molecule_smiles) as protein_name_count FROM parquet_scan('{test_path}') GROUP BY protein_name ORDER BY count(*) DESC)\"\"\").df()\nprotein_stats","metadata":{"_uuid":"04f9ba75-4a20-4f69-bb04-000ef1958208","_cell_guid":"bc8bf220-560f-4d4e-99d2-8c5e2393a635","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:13:16.736517Z","iopub.execute_input":"2024-04-07T16:13:16.736956Z","iopub.status.idle":"2024-04-07T16:13:17.820904Z","shell.execute_reply.started":"2024-04-07T16:13:16.736923Z","shell.execute_reply":"2024-04-07T16:13:17.819274Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.bar(protein_stats['protein_name'], protein_stats['protein_name_count'], color='red')\nplt.yscale('log')\nplt.xlabel('Protein')\nplt.ylabel('# Count')\nplt.title('Protein Distribution')\nplt.show()","metadata":{"_uuid":"d375761d-d8e6-4735-b8b2-dc98233cc146","_cell_guid":"5df1ec9e-1c2b-4598-bc13-a77c0b47e525","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:13:17.823666Z","iopub.execute_input":"2024-04-07T16:13:17.825136Z","iopub.status.idle":"2024-04-07T16:13:18.315814Z","shell.execute_reply.started":"2024-04-07T16:13:17.825079Z","shell.execute_reply":"2024-04-07T16:13:18.314898Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## buildingblock1 smiles","metadata":{"_uuid":"ad89db30-f43d-43a1-be34-11e07e534ba8","_cell_guid":"5386187e-daf1-462d-bc7b-5836d4f1cb70","trusted":true}},{"cell_type":"code","source":"buildingblock1_smiles_stats = con.query(f\"\"\"(SELECT buildingblock1_smiles, count(*) as buildingblock1_smiles_count FROM parquet_scan('{test_path}') GROUP BY buildingblock1_smiles ORDER BY count(*) DESC)\"\"\").df()\nbuildingblock1_smiles_stats","metadata":{"_uuid":"70037ed9-4575-4f1e-9a0d-d2d4d28bb0a0","_cell_guid":"6ff1db04-559a-42b1-9318-626fa97021a9","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:13:19.699028Z","iopub.execute_input":"2024-04-07T16:13:19.699605Z","iopub.status.idle":"2024-04-07T16:13:19.807887Z","shell.execute_reply.started":"2024-04-07T16:13:19.699563Z","shell.execute_reply":"2024-04-07T16:13:19.806461Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.bar(buildingblock1_smiles_stats.index, buildingblock1_smiles_stats['buildingblock1_smiles_count'], color='red')\nplt.yscale('log')\nplt.ylabel('Log of Buildingblock1 Smiles Count')\nplt.title('# buildingblock1_smiles')\nplt.tight_layout()\nplt.show()","metadata":{"_uuid":"18b6a9ab-14a8-4706-83cd-7c2f0f22527d","_cell_guid":"1af734ef-e9b7-4ebb-aaa8-d20f382c5c8a","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:13:20.218505Z","iopub.execute_input":"2024-04-07T16:13:20.219259Z","iopub.status.idle":"2024-04-07T16:13:21.486548Z","shell.execute_reply.started":"2024-04-07T16:13:20.219211Z","shell.execute_reply":"2024-04-07T16:13:21.485405Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"buildingblock2_smiles_stats = con.query(f\"\"\"(SELECT buildingblock2_smiles, count(*) as buildingblock2_smiles_count FROM parquet_scan('{test_path}') GROUP BY buildingblock2_smiles ORDER BY count(*) DESC)\"\"\").df()\nbuildingblock2_smiles_stats","metadata":{"_uuid":"fde3fe7a-a2cb-46e4-a271-24e30008e2a2","_cell_guid":"5fb9b63f-cc12-49e2-b3e9-2db523349a0c","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:13:21.488794Z","iopub.execute_input":"2024-04-07T16:13:21.489494Z","iopub.status.idle":"2024-04-07T16:13:21.586077Z","shell.execute_reply.started":"2024-04-07T16:13:21.489452Z","shell.execute_reply":"2024-04-07T16:13:21.584937Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## buildingblock2 smiles","metadata":{"_uuid":"1bcccda0-5c92-41aa-8e9b-017382524fc0","_cell_guid":"b083ee04-8f8b-41c0-b329-a3ac672f8954","trusted":true}},{"cell_type":"code","source":"plt.bar(buildingblock2_smiles_stats.index, buildingblock2_smiles_stats['buildingblock2_smiles_count'], color='red')\nplt.yscale('log')\nplt.ylabel('Log of Buildingblock2 Smiles Count')\nplt.title('# buildingblock2_smiles')\nplt.tight_layout()\nplt.show()","metadata":{"_uuid":"69dc0cf8-3118-4428-a1c7-db52a0780c1d","_cell_guid":"567834ef-747a-44a8-8c9b-25fd21b73428","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:13:21.816602Z","iopub.execute_input":"2024-04-07T16:13:21.817264Z","iopub.status.idle":"2024-04-07T16:13:24.337893Z","shell.execute_reply.started":"2024-04-07T16:13:21.817227Z","shell.execute_reply":"2024-04-07T16:13:24.337015Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## buildingblock3 smiles","metadata":{"_uuid":"abca1248-05aa-4555-971e-9b76a62faf94","_cell_guid":"82fdae43-9794-4c14-89ca-b81c8ab18734","trusted":true}},{"cell_type":"code","source":"buildingblock3_smiles_stats = con.query(f\"\"\"(SELECT buildingblock3_smiles, count(*) as buildingblock3_smiles_count FROM parquet_scan('{test_path}') GROUP BY buildingblock3_smiles ORDER BY count(*) DESC)\"\"\").df()\nbuildingblock3_smiles_stats","metadata":{"_uuid":"f6922c34-7293-410d-b159-086d19261629","_cell_guid":"aa8c0172-d3d9-4a60-8102-3fa19190bfc7","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:13:24.340215Z","iopub.execute_input":"2024-04-07T16:13:24.341067Z","iopub.status.idle":"2024-04-07T16:13:24.470085Z","shell.execute_reply.started":"2024-04-07T16:13:24.341022Z","shell.execute_reply":"2024-04-07T16:13:24.469212Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plt.bar(buildingblock3_smiles_stats.index, buildingblock3_smiles_stats['buildingblock3_smiles_count'], color='red')\nplt.yscale('log')\nplt.ylabel('Log of Buildingblock3 Smiles Count')\nplt.title('# buildingblock3_smiles')\nplt.tight_layout()\nplt.show()","metadata":{"_uuid":"8561af1d-8e60-4b78-a9f1-b34c85f58005","_cell_guid":"0fb4f992-a605-4afe-81ec-f1a96eb1e3e7","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:13:24.471328Z","iopub.execute_input":"2024-04-07T16:13:24.471810Z","iopub.status.idle":"2024-04-07T16:13:27.429565Z","shell.execute_reply.started":"2024-04-07T16:13:24.471781Z","shell.execute_reply":"2024-04-07T16:13:27.428400Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## molecule_smiles","metadata":{"_uuid":"c2b599af-c83e-4af2-831d-bbbbb4eb305c","_cell_guid":"25b2727b-6344-4f2b-a445-137c3845f6e8","trusted":true}},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as molecule_smiles FROM (SELECT distinct molecule_smiles FROM parquet_scan('{test_path}')) as t)\"\"\").df()","metadata":{"_uuid":"c536d1d7-c5fe-4553-9caa-a33cb9b30fb3","_cell_guid":"eb599e60-549d-463d-a433-8be08beb19bc","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:13:27.432684Z","iopub.execute_input":"2024-04-07T16:13:27.433408Z","iopub.status.idle":"2024-04-07T16:13:27.962412Z","shell.execute_reply.started":"2024-04-07T16:13:27.433365Z","shell.execute_reply":"2024-04-07T16:13:27.960966Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as all_buildingblock_smiles_count FROM (SELECT distinct buildingblock1_smiles,buildingblock2_smiles,buildingblock3_smiles FROM parquet_scan('{test_path}')) as t)\"\"\").df()","metadata":{"execution":{"iopub.status.busy":"2024-04-07T16:13:27.963424Z","iopub.execute_input":"2024-04-07T16:13:27.963740Z","iopub.status.idle":"2024-04-07T16:13:28.436592Z","shell.execute_reply.started":"2024-04-07T16:13:27.963714Z","shell.execute_reply":"2024-04-07T16:13:28.434810Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## All buildingblock smiles Train vs Test","metadata":{"_uuid":"6e007636-0160-4f92-8de5-71a7f32bfc05","_cell_guid":"4a7fd18c-1fbe-426f-b9ae-efbe1d896ad8","trusted":true}},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as all_buildingblock_smiles_count FROM (\n    SELECT distinct buildingblock1_smiles,buildingblock2_smiles,buildingblock3_smiles FROM parquet_scan('{test_path}')\n    INTERSECT\n    SELECT distinct buildingblock1_smiles,buildingblock2_smiles,buildingblock3_smiles FROM parquet_scan('{train_path}')\n    ) as t)\"\"\").df()","metadata":{"_uuid":"81bb639a-835f-4be9-be60-228f8fe1cea2","_cell_guid":"fc9d3a51-ad11-4fd6-9ada-704b11e8a75a","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T16:13:28.438736Z","iopub.execute_input":"2024-04-07T16:13:28.439122Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as all_buildingblock_smiles_count FROM (\n    SELECT distinct molecule_smiles FROM parquet_scan('{test_path}')\n    INTERSECT\n    SELECT distinct molecule_smiles FROM parquet_scan('{train_path}')\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as all_buildingblock_smiles_count FROM (\n    SELECT distinct buildingblock1_smiles FROM parquet_scan('{test_path}')\n    INTERSECT\n    SELECT distinct buildingblock1_smiles FROM parquet_scan('{train_path}')\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as all_buildingblock_smiles_count FROM (\n    SELECT distinct buildingblock1_smiles FROM parquet_scan('{train_path}')\n    EXCEPT\n    SELECT distinct buildingblock1_smiles FROM parquet_scan('{test_path}')\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as all_buildingblock_smiles_count FROM (\n    SELECT distinct buildingblock2_smiles FROM parquet_scan('{test_path}')\n    INTERSECT\n    SELECT distinct buildingblock2_smiles FROM parquet_scan('{train_path}')\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as all_buildingblock_smiles_count FROM (\n    SELECT distinct buildingblock2_smiles FROM parquet_scan('{train_path}')\n    EXCEPT\n    SELECT distinct buildingblock2_smiles FROM parquet_scan('{test_path}')\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as all_buildingblock_smiles_count FROM (\n    SELECT distinct buildingblock3_smiles FROM parquet_scan('{test_path}')\n    INTERSECT\n    SELECT distinct buildingblock3_smiles FROM parquet_scan('{train_path}')\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as all_buildingblock_smiles_count FROM (\n    SELECT distinct buildingblock3_smiles FROM parquet_scan('{train_path}')\n    EXCEPT\n    SELECT distinct buildingblock3_smiles FROM parquet_scan('{test_path}')\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"| Building Blocks | Train | Test | Common | Test New | Train Extra | \n| --- | --- | --- | --- | --- | --- | \n| buildingblock1_smiles | 271 | 340 | 271 | 69 |  0 | \n| buildingblock2_smiles | 693 | 1139  | 693 | 446 | 0 |\n| buildingblock3_smiles | 871 | 1388 | 870 | 517 | 1 |","metadata":{}},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as new_building_blocks_count FROM (\n    SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') \n    WHERE buildingblock1_smiles not in (SELECT distinct buildingblock1_smiles FROM parquet_scan('{test_path}'))\n    AND buildingblock2_smiles not in (SELECT distinct buildingblock2_smiles FROM parquet_scan('{test_path}'))\n    AND buildingblock3_smiles not in (SELECT distinct buildingblock3_smiles FROM parquet_scan('{test_path}'))\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as new_building_blocks_count FROM (\n    SELECT distinct molecule_smiles FROM parquet_scan('{test_path}') \n    WHERE buildingblock1_smiles not in (SELECT distinct buildingblock1_smiles FROM parquet_scan('{train_path}'))\n    AND buildingblock2_smiles not in (SELECT distinct buildingblock2_smiles FROM parquet_scan('{train_path}'))\n    AND buildingblock3_smiles not in (SELECT distinct buildingblock3_smiles FROM parquet_scan('{train_path}'))\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as new_building_blocks_count FROM (\n    SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') \n    WHERE buildingblock1_smiles in (SELECT distinct buildingblock1_smiles FROM parquet_scan('{test_path}'))\n    AND buildingblock2_smiles in (SELECT distinct buildingblock2_smiles FROM parquet_scan('{test_path}'))\n    AND buildingblock3_smiles in (SELECT distinct buildingblock3_smiles FROM parquet_scan('{test_path}'))\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as new_building_blocks_count FROM (\n    SELECT distinct molecule_smiles FROM parquet_scan('{test_path}') \n    WHERE buildingblock1_smiles in (SELECT distinct buildingblock1_smiles FROM parquet_scan('{train_path}'))\n    AND buildingblock2_smiles in (SELECT distinct buildingblock2_smiles FROM parquet_scan('{train_path}'))\n    AND buildingblock3_smiles in (SELECT distinct buildingblock3_smiles FROM parquet_scan('{train_path}'))\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as new_building_blocks_count FROM (\n    SELECT distinct molecule_smiles FROM parquet_scan('{test_path}') \n    WHERE buildingblock1_smiles not in (SELECT distinct buildingblock1_smiles FROM parquet_scan('{train_path}'))\n    OR buildingblock2_smiles not in (SELECT distinct buildingblock2_smiles FROM parquet_scan('{train_path}'))\n    OR buildingblock3_smiles not in (SELECT distinct buildingblock3_smiles FROM parquet_scan('{train_path}'))\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as new_building_blocks_count FROM (\n    SELECT distinct molecule_smiles FROM parquet_scan('{test_path}')\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"con.query(f\"\"\"(SELECT count(*) as new_building_blocks_count FROM (\n    SELECT distinct molecule_smiles FROM parquet_scan('{train_path}')\n    ) as t)\"\"\").df()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Training Data","metadata":{"_uuid":"632e2e8c-be86-4f9d-bc5c-4ea33e01dff2","_cell_guid":"0b3e734d-f411-4a4b-9300-b964b1126767","trusted":true}},{"cell_type":"code","source":"train_1 = con.query(f\"\"\"(SELECT * FROM parquet_scan('{train_path}') WHERE binds=1)\"\"\").df()\ntrain_1.shape","metadata":{"_uuid":"907244c8-a0b9-429c-ade6-9cb86cb4f9fe","_cell_guid":"da65f0ba-c004-4f2b-822a-12ca40d4a092","collapsed":false,"jupyter":{"outputs_hidden":false},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_1.to_csv(\"/kaggle/working/train_1.csv\",index=False)","metadata":{"_uuid":"79c5e038-2b6a-4728-9931-cd4bc4d16fe9","_cell_guid":"ca8b47d7-b3db-46b7-81b0-0a05d323698f","collapsed":false,"jupyter":{"outputs_hidden":false},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_0 = con.query(f\"\"\"(SELECT * FROM parquet_scan('{train_path}') WHERE binds=0 ORDER BY random() LIMIT 1589906*2)\"\"\").df()\ntrain_0.shape","metadata":{"_uuid":"95c0b9d5-7bd2-40ff-9e31-bb3b15988922","_cell_guid":"05706015-2bda-4fd4-8e63-79e0cf066848","collapsed":false,"jupyter":{"outputs_hidden":false},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_0.to_csv(\"/kaggle/working/train_0.csv\",index=False)","metadata":{"_uuid":"933bf2e7-eb93-4d00-8076-4d921211cda0","_cell_guid":"05c4f031-d9f3-4b2d-a0bf-0acde98814b2","collapsed":false,"jupyter":{"outputs_hidden":false},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"buildingblock1_smiles_0 = con.query(f\"\"\"(SELECT distinct buildingblock1_smiles FROM parquet_scan('{train_path}') WHERE binds=0)\"\"\").df()\nbuildingblock1_smiles_0.shape","metadata":{"_uuid":"b5693660-aa38-45b0-983e-a62bc96da1a3","_cell_guid":"5cdb9bf0-3935-4d16-a89e-9676c9c92be5","collapsed":false,"jupyter":{"outputs_hidden":false},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"buildingblock2_smiles_0 = con.query(f\"\"\"(SELECT distinct buildingblock2_smiles FROM parquet_scan('{train_path}') WHERE binds=0)\"\"\").df()\nbuildingblock2_smiles_0.shape","metadata":{"_uuid":"b28478c1-4d9d-4659-9de1-3211f195d031","_cell_guid":"732db5ce-1be8-4590-90d0-e1e9b0b48391","collapsed":false,"jupyter":{"outputs_hidden":false},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"buildingblock3_smiles_0 = con.query(f\"\"\"(SELECT distinct buildingblock3_smiles FROM parquet_scan('{train_path}') WHERE binds=0)\"\"\").df()\nbuildingblock3_smiles_0.shape","metadata":{"_uuid":"27c45efb-175d-42f8-a453-01c3bae68c01","_cell_guid":"62735ef5-b321-43fa-93aa-449ece62ddfb","collapsed":false,"jupyter":{"outputs_hidden":false},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"buildingblock1_smiles_0.to_csv(\"/kaggle/working/buildingblock1_smiles_0.csv\",index=False)\nbuildingblock2_smiles_0.to_csv(\"/kaggle/working/buildingblock2_smiles_0.csv\",index=False)\nbuildingblock3_smiles_0.to_csv(\"/kaggle/working/buildingblock3_smiles_0.csv\",index=False)","metadata":{"_uuid":"8222453a-543e-4dec-b863-2eb7c65ec75a","_cell_guid":"ee198d08-a176-44eb-94b0-cbf7b23ae4f4","collapsed":false,"jupyter":{"outputs_hidden":false},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# molecule_smiles = con.query(f\"\"\"(SELECT molecule_smiles from (\n#               (SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') WHERE binds=0 )\n#               EXCEPT\n#               (SELECT distinct molecule_smiles FROM parquet_scan('{train_path}') WHERE binds=1 ) ) as t)\n#         \"\"\").df()\n# molecule_smiles.shape","metadata":{"_uuid":"bb699518-e376-45c8-9d62-1af41251c5a0","_cell_guid":"307acfe2-fc53-4465-ba5b-f84c8cdcfec5","collapsed":false,"jupyter":{"outputs_hidden":false},"execution":{"iopub.status.busy":"2024-04-07T14:45:20.464521Z","iopub.status.idle":"2024-04-07T14:45:20.464927Z","shell.execute_reply.started":"2024-04-07T14:45:20.464719Z","shell.execute_reply":"2024-04-07T14:45:20.464735Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"_uuid":"8779b4f5-8d37-413d-bf95-ae84d79ab6ea","_cell_guid":"1f8c25ed-751d-46b7-a058-7f6064672252","collapsed":false,"jupyter":{"outputs_hidden":false},"trusted":true},"execution_count":null,"outputs":[]}]}