{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Import Library","metadata":{}},{"cell_type":"code","source":"import pandas as pd","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:34:34.246116Z","iopub.execute_input":"2023-09-23T07:34:34.246627Z","iopub.status.idle":"2023-09-23T07:34:34.717896Z","shell.execute_reply.started":"2023-09-23T07:34:34.246536Z","shell.execute_reply":"2023-09-23T07:34:34.716996Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Step 1: Load the training data","metadata":{}},{"cell_type":"code","source":"train_path = '/kaggle/input/open-problems-single-cell-perturbations/de_train.parquet'\ndf_train = pd.read_parquet(train_path)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:34:34.720212Z","iopub.execute_input":"2023-09-23T07:34:34.721121Z","iopub.status.idle":"2023-09-23T07:34:37.819103Z","shell.execute_reply.started":"2023-09-23T07:34:34.721078Z","shell.execute_reply":"2023-09-23T07:34:37.818093Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Print the training data if necessary\ndf_train","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:34:37.820779Z","iopub.execute_input":"2023-09-23T07:34:37.821476Z","iopub.status.idle":"2023-09-23T07:34:37.871301Z","shell.execute_reply.started":"2023-09-23T07:34:37.821434Z","shell.execute_reply":"2023-09-23T07:34:37.869832Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Step 2: Group by drug and calculate the mean","metadata":{}},{"cell_type":"code","source":"df_simple = df_train.iloc[:, [1] + list(range(5, df_train.shape[1]))]\nmean_df = df_simple.groupby('sm_name').mean().reset_index()\nmean_df","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:34:37.875115Z","iopub.execute_input":"2023-09-23T07:34:37.875668Z","iopub.status.idle":"2023-09-23T07:34:38.233711Z","shell.execute_reply.started":"2023-09-23T07:34:37.875622Z","shell.execute_reply":"2023-09-23T07:34:38.232792Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Step 3: Load ID map","metadata":{}},{"cell_type":"code","source":"id_map_path = '/kaggle/input/open-problems-single-cell-perturbations/id_map.csv'\ndf_ids = pd.read_csv(id_map_path)\nprint(df_ids.shape)\ndisplay(df_ids)  # This is where you are trying to access df_ids\n","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:34:38.234899Z","iopub.execute_input":"2023-09-23T07:34:38.235870Z","iopub.status.idle":"2023-09-23T07:34:38.264004Z","shell.execute_reply.started":"2023-09-23T07:34:38.235757Z","shell.execute_reply":"2023-09-23T07:34:38.262633Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Step 4: Load the sample template","metadata":{}},{"cell_type":"code","source":"sample_submission_path = '/kaggle/input/open-problems-single-cell-perturbations/sample_submission.csv'\nsubmit_df = pd.read_csv(sample_submission_path)\nprint(submit_df.shape)\ndisplay(submit_df)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:34:38.265661Z","iopub.execute_input":"2023-09-23T07:34:38.266279Z","iopub.status.idle":"2023-09-23T07:34:43.226736Z","shell.execute_reply.started":"2023-09-23T07:34:38.266237Z","shell.execute_reply":"2023-09-23T07:34:43.225522Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Step 5: Filter and prepare rows based on 'sm_name'","metadata":{}},{"cell_type":"code","source":"filtered_rows = []\n\nfor variable_name in df_ids['sm_name']:\n    matching_rows = mean_df[mean_df['sm_name'] == variable_name].copy()\n    matching_rows['variable_name'] = variable_name\n    filtered_rows.append(matching_rows)\n\n# Concatenate all filtered rows into a single DataFrame\nresult_df = pd.concat(filtered_rows)\nresult_df = result_df.reset_index(drop=True)\nresult_df","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:34:43.228686Z","iopub.execute_input":"2023-09-23T07:34:43.229136Z","iopub.status.idle":"2023-09-23T07:34:44.525182Z","shell.execute_reply.started":"2023-09-23T07:34:43.229093Z","shell.execute_reply":"2023-09-23T07:34:44.523972Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Step 6: Paste the results into the sample submission file","metadata":{}},{"cell_type":"code","source":"for i, col in enumerate(submit_df.columns):\n    if col == 'id':\n        continue\n    submit_df[col] = result_df[col]\n    if (i % 1000) == 0:\n        print(i, col)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:34:44.526775Z","iopub.execute_input":"2023-09-23T07:34:44.527195Z","iopub.status.idle":"2023-09-23T07:34:54.042044Z","shell.execute_reply.started":"2023-09-23T07:34:44.527156Z","shell.execute_reply":"2023-09-23T07:34:54.041032Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Step 7: Group by cell type and calculate the mean","metadata":{}},{"cell_type":"code","source":"df_simple = df_train.iloc[:, [0] + list(range(5, df_train.shape[1]))]\ncell_type_mean = df_simple.groupby('cell_type').mean().reset_index()\ncell_type_mean","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:34:54.043682Z","iopub.execute_input":"2023-09-23T07:34:54.044109Z","iopub.status.idle":"2023-09-23T07:34:54.253058Z","shell.execute_reply.started":"2023-09-23T07:34:54.044076Z","shell.execute_reply":"2023-09-23T07:34:54.251606Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Step 8: Filter and prepare rows based on 'cell_type'","metadata":{}},{"cell_type":"code","source":"filtered_rows = []\n\nfor variable_name in df_ids['cell_type']:\n    matching_rows = cell_type_mean[cell_type_mean['cell_type'] == variable_name].copy()\n    matching_rows['variable_name'] = variable_name\n    filtered_rows.append(matching_rows)\n\n# Concatenate all filtered rows into a single DataFrame\nresult_df = pd.concat(filtered_rows)\nresult_df = result_df.reset_index(drop=True)\nresult_df","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:34:54.257619Z","iopub.execute_input":"2023-09-23T07:34:54.257992Z","iopub.status.idle":"2023-09-23T07:34:55.467586Z","shell.execute_reply.started":"2023-09-23T07:34:54.257960Z","shell.execute_reply":"2023-09-23T07:34:55.466019Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Step 9: Load the sample template for cell type","metadata":{}},{"cell_type":"code","source":"submit_df_celltype = pd.read_csv(sample_submission_path)\nsubmit_df_celltype","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:34:55.469223Z","iopub.execute_input":"2023-09-23T07:34:55.469693Z","iopub.status.idle":"2023-09-23T07:35:00.514858Z","shell.execute_reply.started":"2023-09-23T07:34:55.469649Z","shell.execute_reply":"2023-09-23T07:35:00.513446Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Step 10: Paste the results into the sample submission file for cell type","metadata":{}},{"cell_type":"code","source":"for i, col in enumerate(submit_df_celltype.columns):\n    if col == 'id':\n        continue\n    submit_df_celltype[col] = result_df[col]\n    if (i % 1000) == 0:\n        print(i, col)","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:35:00.516256Z","iopub.execute_input":"2023-09-23T07:35:00.516638Z","iopub.status.idle":"2023-09-23T07:35:10.056330Z","shell.execute_reply.started":"2023-09-23T07:35:00.516593Z","shell.execute_reply":"2023-09-23T07:35:10.054971Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Step 11: Combine the submission with a high-scoring one","metadata":{}},{"cell_type":"code","source":"fn = '/kaggle/input/open-problems-single-cell-perturbations/sample_submission.csv'\nv18sub = pd.read_csv(fn)\nprint(v18sub.shape)\n\n\n# Adjust the weights (0.45, 0.45, 0.1) as needed\naverage_df = v18sub * 0.45 + submit_df * 0.45 + submit_df_celltype * 0.1 \n\n# Convert 'id' back to int32\naverage_df['id'] = average_df['id'].astype('int32')\n\n# Save the final submission as a CSV file\naverage_df.to_csv('submission.csv', index=False)\naverage_df","metadata":{"execution":{"iopub.status.busy":"2023-09-23T07:35:10.058090Z","iopub.execute_input":"2023-09-23T07:35:10.058651Z","iopub.status.idle":"2023-09-23T07:36:06.472010Z","shell.execute_reply.started":"2023-09-23T07:35:10.058603Z","shell.execute_reply":"2023-09-23T07:36:06.470464Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Feel free to leave comments and suggestions on how to improve!","metadata":{}}]}