{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.12","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"},{"sourceId":10999764,"sourceType":"datasetVersion","datasetId":6847413},{"sourceId":11003433,"sourceType":"datasetVersion","datasetId":6849853},{"sourceId":11004368,"sourceType":"datasetVersion","datasetId":6850517}],"dockerImageVersionId":30918,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"execution":{"iopub.status.busy":"2025-03-12T08:54:28.808869Z","iopub.execute_input":"2025-03-12T08:54:28.809191Z","iopub.status.idle":"2025-03-12T08:54:28.844772Z","shell.execute_reply.started":"2025-03-12T08:54:28.809167Z","shell.execute_reply":"2025-03-12T08:54:28.843462Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"!pip install rdkit","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T08:54:30.685418Z","iopub.execute_input":"2025-03-12T08:54:30.685862Z","iopub.status.idle":"2025-03-12T08:54:39.362971Z","shell.execute_reply.started":"2025-03-12T08:54:30.685828Z","shell.execute_reply":"2025-03-12T08:54:39.361247Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import duckdb\nimport pandas as pd\nfrom tqdm import tqdm\nimport numpy as np # linear algebra\nfrom rdkit import Chem\nfrom rdkit.Chem import AllChem\n","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T08:54:39.670355Z","iopub.execute_input":"2025-03-12T08:54:39.670771Z","iopub.status.idle":"2025-03-12T08:54:39.691477Z","shell.execute_reply.started":"2025-03-12T08:54:39.670732Z","shell.execute_reply":"2025-03-12T08:54:39.690219Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"import pyarrow.parquet as pq\nimport pandas as pd\nimport gc  # 引入垃圾回收模組\n\nfilename = \"/kaggle/input/leash-BELKA/train.parquet\"\ncolumns_to_read = [\"molecule_smiles\", \"protein_name\", \"binds\"]\n\nbatch_size = 60000    # 每次讀取 60,000 筆\ntarget_rows = 1200000  # 每輪儲存 1,200,000 筆\ntotal_batches = 15    # 總共執行 15 次\ntotal_rows = 0        # 記錄當前累積筆數\n\nparquet_file = pq.ParquetFile(filename)\n\n# 獲取總行組數量\nnum_row_groups = parquet_file.num_row_groups\nprint(f\"Total row groups in file: {num_row_groups}\")\n\n# 計算每次讀取多少行組\nrow_groups_per_batch = target_rows // batch_size\n\n\n# 開始進行批次處理\nfor i in range(total_batches):\n    chunks = []\n    current_rows = 0  # 每輪的計數器\n    batch_start_row_group = i * row_groups_per_batch  # 直接依序取\n    batch_end_row_group = min(batch_start_row_group + row_groups_per_batch, num_row_groups)\n\n    if batch_end_row_group >= num_row_groups:\n        batch_end_row_group = num_row_groups  # 避免超出行組範圍\n\n    print(f\"✅ 第 {i+1} 次處理：從行組 {batch_start_row_group} 到行組 {batch_end_row_group}\")\n\n    # 使用 pyarrow 的 ParquetFile 直接讀取指定範圍的行組\n    for row_group_idx in range(batch_start_row_group, batch_end_row_group):\n        try:\n            batch = parquet_file.read_row_groups([row_group_idx], columns=columns_to_read)\n            chunk = batch.to_pandas()\n\n            # 檢查是否有資料\n            if not chunk.empty:\n                chunks.append(chunk)\n                current_rows += len(chunk)\n                total_rows += len(chunk)\n\n            # 如果讀取到指定範圍的資料，就停止\n            if total_rows >= target_rows * (i + 1):\n                break  # 如果已經讀到該批次的結尾就停止\n\n        except Exception as e:\n            print(f\"⚠️ 讀取行組 {row_group_idx} 時發生錯誤: {e}\")\n\n    if chunks:  # 確保有資料才進行合併\n        # 合併 DataFrame\n        batch_df = pd.concat(chunks, ignore_index=True)\n\n        # 存成 parquet，每次都存不同的檔案\n        output_filename = f\"/kaggle/working/train_part{i+1}.parquet\"\n        final_df.to_parquet(output_filename, index=False)\n\n        print(f\"✅ 第 {i+1} 次存檔：{len(final_df)} 筆，已累積 {total_rows} 筆\")\n\n        # 清理無用的變數，釋放記憶體\n        del batch_df, batch_pivot, smiles_df, final_df\n        gc.collect()  # 執行垃圾回收\n    else:\n        print(f\"⚠️ 第 {i+1} 次處理未讀取到任何資料，跳過該批次。\")","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# 15 個 Parquet 檔案\nparquet_files = [f\"/kaggle/working/train_part{i+1}.parquet\" for i in range(0, 15)]\n\n# 初始化 DuckDB 連線\ncon = duckdb.connect()\n\n# 存放所有批次的 DataFrame\nall_samples = []\n\n# 逐個處理 15 個檔案\nfor i, file in enumerate(parquet_files):\n    print(f\"📂 正在處理檔案: {file}\")\n\n    df = con.query(f\"\"\"(SELECT * FROM parquet_scan('{file}')\n                            WHERE bind = 0\n                            ORDER BY random()\n                            LIMIT 15000)\n                            UNION ALL\n                            (SELECT * FROM parquet_scan('{file}')\n                            WHERE bind = 1 \n                            ORDER BY random()\n                            LIMIT 5000)\"\"\").df()\n\n# 儲存該批次結果\n    output_filename = f\"/kaggle/working/sampled_test_part{i+1}.parquet\"\n    df.to_parquet(output_filename, index=False)\n    print(f\"✅ 已儲存抽樣結果: {output_filename}（共 {len(df)} 筆）\")\n\n    all_samples.append(df)\n\n# 合併所有結果\nfinal_test_df = pd.concat(all_samples, ignore_index=True)\n\n# 儲存總合併的 Parquet\nfinal_test_output = \"/kaggle/working/sampled_train_all.parquet\"\nfinal_test_df.to_parquet(final_test_output, index=False)\nprint(f\"🎯 全部 15 個檔案已處理完畢，最終合併檔案: {final_test_output}（共 {len(final_test_df)} 筆）\")\n\n# 關閉 DuckDB\ncon.close()","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# 設定檔案路徑\ntrain_path = '/kaggle/working/sampled_train_all.parquet'\n\n# 建立 DuckDB 連線\ncon = duckdb.connect()\n\n# 使用進度條來顯示進度\nwith tqdm(total=2, desc=\"Processing Data\") as pbar:\n    # 查詢第一部分數據\n    df_part1 = con.query(f\"\"\"SELECT *\n                              FROM parquet_scan('{train_path}')\n                              WHERE binds = 0\n                              ORDER BY random()\n                              LIMIT 150000\"\"\").df()\n    pbar.update(1)  # 更新進度條\n\n    # 查詢第二部分數據\n    df_part2 = con.query(f\"\"\"SELECT *\n                              FROM parquet_scan('{train_path}')\n                              WHERE binds = 1\n                              ORDER BY random()\n                              LIMIT 50000\"\"\").df()\n    pbar.update(1)  # 更新進度條\n\n# 合併兩部分數據\ndf = pd.concat([df_part1, df_part2], ignore_index=True)\n\n# 隨機洗牌數據（frac=1 表示保持原始大小，shuffle 整個 DataFrame）\ndf = df.sample(frac=1, random_state=42).reset_index(drop=True)\n\n# 關閉連線\ncon.close()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T07:12:14.594509Z","iopub.execute_input":"2025-03-12T07:12:14.594933Z","iopub.status.idle":"2025-03-12T07:13:11.556055Z","shell.execute_reply.started":"2025-03-12T07:12:14.594894Z","shell.execute_reply":"2025-03-12T07:13:11.555065Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# # 確認數據\nprint(df.head())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T07:13:11.557201Z","iopub.execute_input":"2025-03-12T07:13:11.557568Z","iopub.status.idle":"2025-03-12T07:13:11.576467Z","shell.execute_reply.started":"2025-03-12T07:13:11.557531Z","shell.execute_reply":"2025-03-12T07:13:11.575021Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"def smiles_to_morgan_fingerprint(smiles, n_bits=2048):\n    mol = Chem.MolFromSmiles(smiles)\n    if mol is None:\n        return np.zeros(n_bits, dtype=int)\n    else:\n        generator = AllChem.GetMorganGenerator(radius=2, fpSize=n_bits)\n        return np.array(generator.GetFingerprint(mol), dtype=int)\n\n# 對 \"molecule_smiles\" 欄位進行轉換並顯示進度條\ntqdm.pandas(desc=\"Transforming molecule_smiles\")\ndf[\"molecule_smiles\"] = df[\"molecule_smiles\"].progress_apply(lambda x: smiles_to_morgan_fingerprint(x))\n\n# 對 protein 欄位進行 One-Hot Encoding\nprotein_one_hot = pd.get_dummies(df[\"protein_name\"], prefix=\"protein\").astype(int)\n\n# 合併 One-Hot 結果\ndf_one_hot = pd.concat([df, protein_one_hot], axis=1)\n\n# 合併需要的欄位：molecule_smiles, binds, 和經過 One-Hot Encoding 的 protein\ndf_one_hot = pd.concat([df_one_hot[[\"id\", \"molecule_smiles\", \"binds\"]], protein_one_hot], axis=1)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T07:13:11.577601Z","iopub.execute_input":"2025-03-12T07:13:11.578265Z","iopub.status.idle":"2025-03-12T07:18:28.542868Z","shell.execute_reply.started":"2025-03-12T07:13:11.578232Z","shell.execute_reply":"2025-03-12T07:18:28.541805Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df_one_hot","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T07:18:28.544019Z","iopub.execute_input":"2025-03-12T07:18:28.544412Z","iopub.status.idle":"2025-03-12T07:18:28.577351Z","shell.execute_reply.started":"2025-03-12T07:18:28.544374Z","shell.execute_reply":"2025-03-12T07:18:28.575979Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# # 可選：移除原始 protein 欄位\n# df1_one_hot.drop(\"protein_name\", axis=1, inplace=True)\n\n# # 檢視處理後數據\n# print(df_one_hot.head())\n\n# 僅保留需要的欄位\ncolumns_to_keep = [\"id\", \"molecule_smiles\", \"binds\"] + protein_one_hot.columns.tolist()\ndf_filtered = df_one_hot[columns_to_keep]\n\n# 儲存處理後的數據\noutput_filename = \"train_transformed_morgan(150k,50k).parquet\"\ndf_filtered.to_parquet(output_filename, index=False)\n\n# output_filename = f\"test_transformed_morgan(10k,10k).parquet\"\n# df.to_parquet(output_filename, index=False)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T07:18:28.579884Z","iopub.execute_input":"2025-03-12T07:18:28.580218Z","iopub.status.idle":"2025-03-12T07:18:57.521379Z","shell.execute_reply.started":"2025-03-12T07:18:28.580188Z","shell.execute_reply":"2025-03-12T07:18:57.519167Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"morganfile = '/kaggle/working/train_transformed_morgan(150k,50k).parquet'\nmorgan = pd.read_parquet(morganfile)\nmorgan","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T07:18:57.524501Z","iopub.execute_input":"2025-03-12T07:18:57.525010Z","iopub.status.idle":"2025-03-12T07:19:23.920934Z","shell.execute_reply.started":"2025-03-12T07:18:57.524965Z","shell.execute_reply":"2025-03-12T07:19:23.919723Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"topolfile = '/kaggle/input/dataset1/Data_Transformed__TopologicalFingerprint(100k100k)/train_transformed__topological(100k,100k).parquet'\ntopol = pd.read_parquet(topolfile)\ntopol","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T07:19:23.922224Z","iopub.execute_input":"2025-03-12T07:19:23.922590Z","iopub.status.idle":"2025-03-12T07:19:43.764107Z","shell.execute_reply.started":"2025-03-12T07:19:23.922562Z","shell.execute_reply":"2025-03-12T07:19:43.762665Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# 設定檔案路徑\ntrain_path = '/kaggle/input/leash-BELKA/train.parquet'\n\n# 建立 DuckDB 連線\ncon = duckdb.connect()\n\n# 使用進度條來顯示進度\nwith tqdm(total=2, desc=\"Processing Data\") as pbar:\n    # 查詢第一部分數據\n    df_part1 = con.query(f\"\"\"SELECT *\n                              FROM parquet_scan('{train_path}')\n                              WHERE binds = 0\n                              ORDER BY random()\n                              LIMIT 100000\"\"\").df()\n    pbar.update(1)  # 更新進度條\n\n    # 查詢第二部分數據\n    df_part2 = con.query(f\"\"\"SELECT *\n                              FROM parquet_scan('{train_path}')\n                              WHERE binds = 1\n                              ORDER BY random()\n                              LIMIT 100000\"\"\").df()\n    pbar.update(1)  # 更新進度條\n\n# 合併兩部分數據\ndf = pd.concat([df_part1, df_part2], ignore_index=True)\n\n# 隨機洗牌數據（frac=1 表示保持原始大小，shuffle 整個 DataFrame）\ndf = df.sample(frac=1, random_state=42).reset_index(drop=True)\n\n# 關閉連線\ncon.close()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T07:19:43.765344Z","iopub.execute_input":"2025-03-12T07:19:43.765714Z","iopub.status.idle":"2025-03-12T07:20:36.799298Z","shell.execute_reply.started":"2025-03-12T07:19:43.765685Z","shell.execute_reply":"2025-03-12T07:20:36.798268Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"print(df.head())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T07:20:36.800715Z","iopub.execute_input":"2025-03-12T07:20:36.801117Z","iopub.status.idle":"2025-03-12T07:20:36.809830Z","shell.execute_reply.started":"2025-03-12T07:20:36.801081Z","shell.execute_reply":"2025-03-12T07:20:36.808540Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"def smiles_to_morgan_fingerprint(smiles, n_bits=2048):\n    mol = Chem.MolFromSmiles(smiles)\n    if mol is None:\n        return np.zeros(n_bits, dtype=int)\n    else:\n        generator = AllChem.GetMorganGenerator(radius=2, fpSize=n_bits)\n        return np.array(generator.GetFingerprint(mol), dtype=int)\n\n# 對 \"molecule_smiles\" 欄位進行轉換並顯示進度條\ntqdm.pandas(desc=\"Transforming molecule_smiles\")\ndf[\"molecule_smiles\"] = df[\"molecule_smiles\"].progress_apply(lambda x: smiles_to_morgan_fingerprint(x))\n\n# 對 protein 欄位進行 One-Hot Encoding\nprotein_one_hot = pd.get_dummies(df[\"protein_name\"], prefix=\"protein\").astype(int)\n\n# 合併 One-Hot 結果\ndf_one_hot = pd.concat([df, protein_one_hot], axis=1)\n\n# 合併需要的欄位：molecule_smiles, binds, 和經過 One-Hot Encoding 的 protein\ndf_one_hot = pd.concat([df_one_hot[[\"id\", \"molecule_smiles\", \"binds\"]], protein_one_hot], axis=1)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T07:20:36.811235Z","iopub.execute_input":"2025-03-12T07:20:36.811653Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"df_one_hot","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# # 可選：移除原始 protein 欄位\n# df1_one_hot.drop(\"protein_name\", axis=1, inplace=True)\n\n# # 檢視處理後數據\n# print(df_one_hot.head())\n\n# 僅保留需要的欄位\ncolumns_to_keep = [\"id\", \"molecule_smiles\", \"binds\"] + protein_one_hot.columns.tolist()\ndf_filtered = df_one_hot[columns_to_keep]\n\n# 儲存處理後的數據\n\noutput_filename = \"train_transformed_morgan(100k,100k).parquet\"\ndf_filtered.to_parquet(output_filename, index=False)","metadata":{"trusted":true},"outputs":[],"execution_count":null},{"cell_type":"code","source":"morganfile = '/kaggle/input/train1/train_transformed_morgan(100k100k).parquet'\nmorgan = pd.read_parquet(morganfile)\nmorgan","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T09:11:34.519247Z","iopub.execute_input":"2025-03-12T09:11:34.519855Z","iopub.status.idle":"2025-03-12T09:11:54.428755Z","shell.execute_reply.started":"2025-03-12T09:11:34.519807Z","shell.execute_reply":"2025-03-12T09:11:54.427219Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"morganfile = '/kaggle/input/testset/test_transformed__morgan(180k20k).parquet'\nmorgan = pd.read_parquet(morganfile)\nmorgan","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-12T09:11:54.430358Z","iopub.execute_input":"2025-03-12T09:11:54.430693Z","iopub.status.idle":"2025-03-12T09:12:14.914889Z","shell.execute_reply.started":"2025-03-12T09:11:54.430663Z","shell.execute_reply":"2025-03-12T09:12:14.913572Z"}},"outputs":[],"execution_count":null}]}