<jupyter_start><jupyter_text>Create a Basic Mapping of Broad IDs to MOAs and TargetsHere, I add the `pert_id` column to an additional file.This file contains only the essential mapping columns between cell painting data and drug annotations.The primary addition is truncating the `broad_id` column into the `pert_id` column (e.g. `BRD-K89787693-001-01-1` becomes `BRD-K89787693`) and describing the impact.I create a new file (called `repurposing_info_basic.tsv`) that only contains the unique columns `pert_id`, `pert_iname`, `moa`, and `target`.The `broad_id` column contains additional supplier, batch and aliquot information.The `pert_id` column is the essential information that is used to directly map compounds to profiles.<jupyter_code>%load_ext nb_black
%matplotlib inline
import os
import pandas as pd
use_cols = ["pert_id", "pert_iname", "moa", "target"]<jupyter_output><empty_output><jupyter_text>Load Data and Create the `pert_id` column<jupyter_code>annotation_df = pd.read_csv("repurposing_info.tsv", sep="\t")

annotation_df = annotation_df.assign(pert_id=annotation_df.broad_id.str.slice(0, 13))

print(annotation_df.shape)
annotation_df.head()<jupyter_output>(13553, 19)<jupyter_text>Describe the Effect of adding `pert_id`How many columns are replicated?<jupyter_code># There are no duplicate `broad_ids`
annotation_df.broad_id.duplicated().sum()
pert_counts = annotation_df.pert_id.value_counts()
pert_counts.head(10)
pert_counts.hist()
# How many pert ids are duplicated?
num_duplicate = (pert_counts > 1).sum()
percent_duplicate = (num_duplicate / pert_counts.shape[0]) * 100

print(
    "There are {} ({}%) duplicated `pert_ids`".format(num_duplicate, percent_duplicate)
)
# Examples of duplicate pert_id columns
top_duplicated_pert_id = pert_counts.head(1).index.values[0]
annotation_df.query("pert_id == @top_duplicated_pert_id")<jupyter_output><empty_output><jupyter_text>Select only essential columns and determine/reconcile any discrepancies<jupyter_code>basic_df = annotation_df.loc[:, use_cols].drop_duplicates()

print(basic_df.shape)
basic_df.head()
duplicated_pert_ids = basic_df.pert_id.loc[basic_df.pert_id.duplicated()]
assert len(duplicated_pert_ids) == 0, "Warning! There are duplicated pert_ids"
output_file = "repurposing_simple.tsv"
basic_df.to_csv(output_file, index=False, sep="\t")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10658174097664544, 'fraction_numerical': 0.017834394904458598, 'mean_word_length': 7.6300366300366305, 'pattern_counts': {'":': 0, '<': 16, '<?xml version=': 0, '>': 17, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '641578', 'license': 'CC0-1.0', 'n_tokens_mistral': 823, 'n_tokens_neox': 783, 'n_words': 195, 'path': 'metadata/moa/1.create-basic-mapping.ipynb', 'repo_name': 'michaelbornholdt/lincs-cell-painting', 'size': '2355'}