<jupyter_start><jupyter_text>possible remedies?- host_disease_stat (host_disease? host_disease_outcome?)- plant_growth_med (there is a general growth_med )- plant_struc (there is a plant_body_site)- samp_collec_device spelled as samp_collect_device CJM doesn't care about these- season (there is a season_environment)- seq_meth<jupyter_code>manual_adds = [
    "host_disease",
    "host_disease_outcome",
    "growth_med",
    "plant_body_site",
    "samp_collect_device",
]
terms_to_tabulate = terms_to_tabulate + manual_adds
terms_to_tabulate.sort()
terms_to_tabulate
env_package_count_manual = pd.read_csv(env_package_count_file, sep="\t")
env_package_count_manual
# add env_package_count_manual to sqlite database

## indices!
# create index biosample_env_pack_idx on
#  biosample(env_package) ;
# build query from sample id, raw and tidy env_package, and the terms_to_tabulate columns
# then cast wide to long

for_query = ", ".join(terms_to_tabulate)
whole_query = (
    """select
	id,
	b.env_package as env_package_raw,
	epcm.tidy as env_package_tidy, """
    + for_query
    + """ from
	biosample b
join env_package_count_manual epcm on
	b.env_package = epcm .env_package
where
	epcm.tidy != ''
-- limit 9
"""
)
termterms_by_tidypackage = pd.read_sql_query(whole_query, connection)
wtl = pd.melt(
    termterms_by_tidypackage,
    id_vars="id",
    value_vars=terms_to_tabulate,
)
wtl = wtl[wtl["value"].notna()]
wtl["value"] = wtl["value"].str.split("|")
wtl = wtl.explode("value", ignore_index=True)
wtl_value_counts = wtl["value"].value_counts().to_frame()
wtl_value_counts.reset_index(level=0, inplace=True)
wtl_value_counts.columns = ["value", "count"]
for_capture = "(" + obo_pattern + ")"
p = re.compile(for_capture)
wtl_value_counts["extract"] = wtl_value_counts["value"].str.extract(p)
# are there any extracts that generate a list?
# now how to tidy and or validate? OLS lookup?
extract_count = wtl_value_counts["extract"].value_counts().to_frame()
extract_count.reset_index(level=0, inplace=True)
extract_count.columns = ["extract", "count"]
split_extract = extract_count["extract"].str.split(r"[ .:_]", expand=True)
split_extract.columns = ["prefix", "local_part"]
extract_count = pd.concat([extract_count, split_extract], axis=1)
extract_count["prefix_lower"] = extract_count["prefix"].str.lower()
extract_count["prefix_upper"] = extract_count["prefix"].str.upper()

# what if prefix doesn't ahve the right capitalizastion
# could lowercase, then look up as OLS ontologyId and get preferredPrefix

extract_count["reconstituted"] = extract_count[["prefix_upper", "local_part"]].agg(
    ":".join, axis=1
)
prefix_count = extract_count["prefix_lower"].value_counts().to_frame()
prefix_count.reset_index(level=0, inplace=True)
prefix_count.columns = ["prefix", "count"]
r = requests.get(ols_ontologies_url)
rj = json.loads(r.content)
# only up to mid 200s now. Not bothering with pagination
rj["page"]["totalPages"]
# ols_ontology_ids = []
ols_ontology_ids = {}
ols_ontology_list = rj["_embedded"]["ontologies"]
# dict comprehension?
for ontology in ols_ontology_list:
    #     ols_ontology_ids.append(ontology["ontologyId"])
    if "config" in ontology:
        if "preferredPrefix" in ontology["config"]:
            ols_ontology_ids[ontology["ontologyId"]] = ontology["config"][
                "preferredPrefix"
            ]
    else:
        ols_ontology_ids[ontology["ontologyId"]] = ""

## preferredPrefix says "NCBITAXON" not "NCBITaxon"
ols_ontology_id_list = list(ols_ontology_ids.keys())
claimed_prefixes = set(prefix_count["prefix"])
ols_doesnt_recognize = list(claimed_prefixes - set(ols_ontology_id_list))
ols_doesnt_recognize
claimed_recognized = list(claimed_prefixes.intersection(set(ols_ontology_id_list)))
claimed_recognized<jupyter_output><empty_output><jupyter_text>_could manually assert  env -> envo_<jupyter_code>prefix_count["recognized"] = prefix_count["prefix"].isin(claimed_recognized)
temp = prefix_count[["prefix", "recognized"]]
temp.columns = ["prefix_lower", "recognized"]
extract_count = pd.merge(
    extract_count, temp, left_on="prefix_lower", right_on="prefix_lower", how="left"
)
extracted_recognized_list = extract_count["reconstituted"].loc[
    extract_count["recognized"]
]
extracted_recognized_list.sort_values(inplace=True)<jupyter_output><empty_output><jupyter_text>Look up labels for term ids that could be parsed out of the annotations<jupyter_code># term_to_label = {}
# for term in extracted_recognized_list:
#     print(term)
#     r = requests.get(findByIdAndIsDefiningOntology_url + term)
#     if r.status_code == 200:
#         temp = json.loads(r.content)
#         if "_embedded" in temp:
#             if "terms" in temp["_embedded"]:
#                 # list. assume 0 or 1?
#                 #   as opposed to multiple hits for a single term in the defining ontology
#                 if "label" in temp["_embedded"]["terms"][0]:
#                     print(temp["_embedded"]["terms"][0]["label"])
#                     term_to_label[term] = temp["_embedded"]["terms"][0]["label"]
# use this as a cache to minimize OLS API REST calls
# term_to_label = pd.DataFrame(term_to_label.items(), columns=["id", "label"])
# term_to_label.to_sql(
#     "term_to_label",
#     connection,
#     if_exists="append",
#     index=False,
#     index_label=None,
#     chunksize=None,
#     dtype=None,
#     method=None,
# )
term_to_label = pd.read_sql(
    "select * from term_to_label",
    connection,
    index_col=None,
    coerce_float=False,
    params=None,
    parse_dates=None,
    columns=None,
    chunksize=None,
)
extract_count = pd.merge(
    extract_count, term_to_label, left_on="reconstituted", right_on="id", how="left"
)
wtl_value_counts = pd.merge(
    wtl_value_counts,
    extract_count,
    how="left",
    on="extract",
    suffixes=("_annot", "_extr"),
)
wtl_value_counts["depleted"] = wtl_value_counts["value"].replace(
    value="", inplace=False, limit=None, regex=obo_pattern
)
term_to_label["tidy"] = term_to_label["label"]
term_to_label["tidy"] = term_to_label["tidy"].str.lower()

# replace punctuation
term_to_label["tidy"] = term_to_label["tidy"].str.replace(
    "[{}]".format(string.punctuation), " "
)

term_to_label["tidy"] = term_to_label["tidy"].str.strip()
term_to_label["tidy"] = term_to_label["tidy"].replace(
    value=" ", inplace=False, limit=None, regex=" +"
)

# refactor!
# wtl_value_counts['tidy'] = scoped_mapping.whiteout...

wtl_value_counts["tidy"] = wtl_value_counts["depleted"]
wtl_value_counts["tidy"] = wtl_value_counts["tidy"].str.lower()

# replace punctuation
wtl_value_counts["tidy"] = wtl_value_counts["tidy"].str.replace(
    "[{}]".format(string.punctuation), " "
)

wtl_value_counts["tidy"] = wtl_value_counts["tidy"].str.strip()
wtl_value_counts["tidy"] = wtl_value_counts["tidy"].replace(
    value=" ", inplace=False, limit=None, regex=" +"
)

# replace or utilize ontology name prefixes (before text, not numbers)
preflist = list(prefix_count["prefix"].loc[prefix_count["recognized"]])
for prefix in preflist:
    anchored = "^" + prefix + " "
    wtl_value_counts["tidy"] = wtl_value_counts["tidy"].replace(
        value="", inplace=False, limit=None, regex=anchored
    )

# propigate labels that were already looked up from terms?
temp = term_to_label[["tidy", "id"]]
wtl_value_counts = pd.merge(
    wtl_value_counts,
    temp,
    left_on="tidy",
    right_on="tidy",
    how="left",
    suffixes=("_orig", "_backtrack"),
)

wtl_value_counts["id_consensus"] = wtl_value_counts["id_orig"].fillna(
    wtl_value_counts["id_backtrack"]
)

# don't pursue tidy terms that only appear once
#   but what about numerical suffixes like fish lung 1, fish lung 2, etc>
wtl_value_counts.to_clipboard(index=False)
singletons = wtl_value_counts["tidy"].loc[
    wtl_value_counts["count_annot"] == 1 & wtl_value_counts["id_consensus"].isna()
]
singletons = singletons.value_counts().to_frame()
singletons.reset_index(level=0, inplace=True)
singletons.columns = ["value", "count"]

singletons["strlen"] = singletons["value"].str.len()
singletons["pursue"] = singletons["count"] == 1 & singletons["strlen"].gt(0)
singletons = singletons.loc[singletons["pursue"]]

singletons["generalized"] = singletons["value"].replace(
    value="", inplace=False, limit=None, regex="[ 0-9]+$"
)

generalized = singletons["generalized"].value_counts().to_frame()
generalized.reset_index(level=0, inplace=True)
generalized.columns = ["generalized", "count"]
generalized["strlen"] = generalized["generalized"].str.len()

singletons = pd.merge(
    singletons,
    generalized,
    left_on="generalized",
    right_on="generalized",
    how="left",
    suffixes=("_s", "_g"),
)

gen_useful = singletons.loc[singletons["count_g"].gt(1) & singletons["strlen_g"].gt(2)]

gen_useful = gen_useful[["value", "generalized"]]

# also remove " from.*$" ?

# words = np.asarray(temp['value'].loc[temp['use_for_clust']])
gen_useful.to_clipboard(index=False)
wtl_value_counts = pd.merge(
    wtl_value_counts, gen_useful, how="left", left_on="value", right_on="value"
)
wtl_value_counts["generalized_consensus"] = wtl_value_counts["generalized"].fillna(
    wtl_value_counts["tidy"]
)
wtl_value_counts.to_clipboard(index=False)
min_count = 10
elected_ontologies = "envo,micro,fma,uberon,ncbitaxon,foodon,ma,efo,chebi,agro,pato,doid,mondo,obi,pr,hp,po"
raw_list = (
    wtl_value_counts.loc[wtl_value_counts["id_consensus"].isna()]
    .groupby(by=["generalized_consensus"])
    .sum()
)

raw_list.reset_index(level=0, inplace=True)

raw_list = list(
    raw_list["generalized_consensus"].loc[raw_list["count_annot"].ge(min_count)]
)

list_len = len(raw_list)

# skip blanks or all numbers
# raw_list<jupyter_output><empty_output><jupyter_text>This times out or errors out sometimes. Make sure computer doesn't go to sleep.Cache it!<jupyter_code>print(elected_ontologies)
print(min_count)
print(list_len)

sgaw_res = scoped_mapping.search_get_annotations_wrapper(
    raw_list,
    #     bad_chars=standard_replacement_chars,
    #     cat_name=standard_cat_name,
    ontoprefix=elected_ontologies,
    #     query_fields="",
    rr=10,
    string_dist_arg=2,
)

# ge 100 (329 queries) 7 min
#  was using gt, not gte
#  8 minutes for count > 30
# 12 minutes for count > 10 (1066 queries)
# XX minutes for count >  3 (1538 queries)
#    error

# not currently prioritizing by ontology
# so root may go to NCBItaxon
# query	name	string_dist_rank	string_dist
# non saline sediment environment	saline sediment environment	1	0.045
# microbial mats	microbial mat	1	0.039

ba = scoped_mapping.get_best_acceptable(sgaw_res, max_string_dist=0.04)

print(min_count)
print(list_len)
print(len(ba.index))
ols_mergable = ba[["raw", "obo_id", "label"]]
wtl_value_counts = pd.merge(
    wtl_value_counts,
    ols_mergable,
    left_on="generalized_consensus",
    right_on="raw",
    how="left",
    suffixes=("_presearch", "_withsearch"),
)
wtl_value_counts["id_final"] = wtl_value_counts["obo_id"].fillna(
    wtl_value_counts["id_consensus"]
)
wtl_value_counts["label_final"] = wtl_value_counts["label_withsearch"].fillna(
    wtl_value_counts["label_presearch"]
)
wtl_value_counts["label_final"] = wtl_value_counts["label_final"].fillna(
    wtl_value_counts["generalized_consensus"]
)
merge_back_to_samples = wtl_value_counts[["value", "id_final", "label_final"]]
merge_back_to_samples = merge_back_to_samples.loc[
    ~merge_back_to_samples["id_final"].isna()
]
wtl = pd.merge(
    wtl,
    merge_back_to_samples,
    left_on="value",
    right_on="value",
    how="left",
    suffixes=("_sqlres", "_mappingres"),
)
wtl["id_final"] = wtl["id_final"].fillna("unmapped")
wtl["label_final"] = wtl["label_final"].fillna("unmapped")

wtl["lab_id"] = wtl["label_final"] + " [" + wtl["id_final"] + "]"
for_cast = wtl[["id", "variable", "lab_id"]]
for_cast["lab_id"] = (
    for_cast[["id", "lab_id", "variable"]]
    .groupby(["id", "variable"])["lab_id"]
    .transform(lambda x: " | ".join(x))
)
for_cast = for_cast[["id", "variable", "lab_id"]].drop_duplicates()
casted = for_cast.pivot(index="id", columns="variable", values="lab_id")
casted.reset_index(level=0, inplace=True)
temp = termterms_by_tidypackage[["id", "env_package_tidy"]]
temp.columns = ["id", "env_package"]
casted = casted.drop("env_package_tidy", axis=1)
casted = pd.merge(
    casted,
    temp,
    how="left",
    on="id",
    suffixes=("_slots", "_package"),
)
put_first = ["id", "env_package"]
casted_cols = set(casted.columns)
casted_cols = list(casted_cols - set(put_first))
casted_cols.sort()
casted_cols = put_first + casted_cols
casted = casted[casted_cols]
casted.to_csv(output_file, index=False, sep="\t")
# casted = pd.read_csv("onto_slots_by_env_pack.tsv", sep="\t", low_memory=False)

put_first = ["env_package"]
omit_only = ["id"]
special_request = put_first + omit_only

for_melt = casted.columns

for_melt = list(set(for_melt) - set(special_request))
for_melt.sort()

casted_cols = put_first + for_melt
casted_cols

ignore_samples = casted[casted_cols]
is_melt = pd.melt(
    ignore_samples,
    id_vars="env_package",
    value_vars=for_melt,
)

is_melt = is_melt[is_melt["value"].notna()]

is_melt["placeholder"] = 1
is_counts = (
    is_melt[["env_package", "variable", "value", "placeholder"]]
    .groupby(["env_package", "variable", "value"])
    .agg(["count"])
)

is_counts.reset_index(inplace=True)
is_counts.columns = ["env_package", "variable", "value", "val_count"]
is_sums = (
    is_counts[["env_package", "variable", "val_count"]]
    .groupby(["env_package", "variable"])
    .agg(["sum"])
)
is_sums.reset_index(inplace=True)
is_sums.columns = ["env_package", "variable", "ep_var_sum"]
is_frac = pd.merge(is_counts, is_sums)
is_frac["fraction"] = is_frac["val_count"] / is_frac["ep_var_sum"]
is_frac = is_frac.drop("ep_var_sum", axis=1)
is_frac = is_frac.rename(columns={"variable": "slot"})

is_frac
envpack_slot_fracs_file = "envpack_slot_fractions.tsv"
is_frac.to_csv(envpack_slot_fracs_file, index=False, sep="\t")
casted.reset_index(level=0, inplace=True)<jupyter_output><empty_output><jupyter_text>---<jupyter_code># ba['ontology_prefix'].value_counts()

# ontology_elections = 'ENVO,MICRO,FMA,UBERON,NCBITAXON,FOODON,MA,EFO,CHEBI,AGRO,PATO,DOID,MONDO,OBI,PR,HP,PO'.lower()<jupyter_output><empty_output><jupyter_text>accounting of unfiltered search over top ~ 1000 query results```ENVO            191NCIT            137MICRO            34OMIT             29FMA              16GAZ              11UBERON           10NCBITAXON         7FOODON            6MA                6EFO               4CHEBI             4AGRO              4SPD               3CCO               3GENEPIO           3BAO               2OHPI              2OHMI              2PATO              2NMR               2MS                2DOID              2IDOMAL            2OGG               2OBI               2EOL               2VTO               2PR                1MRO               1MCO               1HP                1ARO               1CO_357            1ECAO              1IDO-COVID-19      1EDAM              1MAXO              1CO_366            1BTO               1TGMA              1EUPATH            1ExO               1CEPH              1PLANP             1MIRO              1FIDEO             1COVOC             1SYMP              1HCAO              1WBPhenotype       1SO                1ONS               1EMAPA             1AFO               1ECTO              1CO_340            1``` ---- we can now say what is meant by the presence of term ids in many of the annotationsQuestions:- should we compare the asserted label to the depleted annotation?- over what space should we search the values for which a term/label wasn't found?- use rdftab, runner or API like OLS?- how to rank and filter hits?- try harder with some of the malformed extracts?- what about PREFIX:label style? remove prefixes that have already been recognized from the extracts? ---- Slow and unsuccessful attempt to cluster singletonsUsing right-truncation of numbers instead<jupyter_code># # slow

# lev_similarity = -1 * np.array(
#     [[distance.levenshtein(w1, w2) for w1 in words] for w2 in words]
# )

# lev_similarity
# affprop = AffinityPropagation(affinity="precomputed", damping=0.5)
# affprop.fit(lev_similarity)<jupyter_output><empty_output><jupyter_text>```/Users/MAM/Documents/gitrepos/scoped-mapping/venv/lib/python3.9/site-packages/sklearn/cluster/_affinity_propagation.py:148: FutureWarning: 'random_state' has been introduced in 0.23. It will be set to None starting from 1.0 (renaming of 0.25) which means that results will differ at every function call. Set 'random_state' to None to silence this warning, or to 0 to keep the behavior of versions <0.23.  warnings.warn(/Users/MAM/Documents/gitrepos/scoped-mapping/venv/lib/python3.9/site-packages/sklearn/cluster/_affinity_propagation.py:246: ConvergenceWarning: Affinity propagation did not converge, this model will not have any cluster centers.  warnings.warn("Affinity propagation did not converge, this model "```<jupyter_code># for cluster_id in np.unique(affprop.labels_):
#     exemplar = words[affprop.cluster_centers_indices_[cluster_id]]
#     cluster = np.unique(words[np.nonzero(affprop.labels_ == cluster_id)])
#     cluster_str = ", ".join(cluster)
#     print(" - *%s:* %s" % (exemplar, cluster_str))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1351958525345622, 'fraction_numerical': 0.01065668202764977, 'mean_word_length': 4.474929044465468, 'pattern_counts': {'":': 2, '<': 30, '<?xml version=': 0, '>': 34, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '619856', 'license': 'MIT', 'n_tokens_mistral': 6341, 'n_tokens_neox': 6095, 'n_words': 1121, 'path': 'notebooks/onto_slots_by_env_pack.ipynb', 'repo_name': 'turbomam/scoped-mapping', 'size': '17360'}