def execute_sparql_query(query_text, endpoint='http://avalanche.scripps.edu:9999/bigdata/sparql'):
# Execute the qurey
result = WDItemEngine.execute_sparql_query(query_text, endpoint=endpoint, as_dataframe=True)
# Don't do any processing if empy result
if len(result) == 0:
return result
# Enforce the proper column order and return
col_order = get_col_order(query_text)
return parse_result_uris(result)[col_order]
def get_property_types():
# {'CommonsMedia', 'Time', 'Quantity', 'WikibaseProperty', 'WikibaseItem', 'GlobeCoordinate',
# 'String', 'ExternalId', 'Math', 'Monolingualtext', 'TabularData', 'Url', 'GeoShape'}
query = "SELECT ?p ?pt WHERE {?p wikibase:propertyType ?pt}"
results = WDItemEngine.execute_sparql_query(query)['results']['bindings']
results = [{k: v['value'] for k, v in item.items()} for item in results]
prop_wdtype = {
x['p'].split("/")[-1]: x['pt'].split("#")[-1]
for x in results
}
return prop_wdtype
def check_existing_label(self, label_str):
query_file = open('query_templates/CHECK_EXISTING_LABEL_QUERY.rq', 'r')
query_template = query_file.read()
QUERY = query_template
QUERY = QUERY.replace("#QID#",
self.Organism_QID).replace("#LABEL#", label_str)
results = WDItemEngine.execute_sparql_query(
QUERY)['results']['bindings']
query_file.close()
if len(results) == 0:
return False
else:
return True
def get_wikidata_complexes():
"""Gets all Wikidata items with a Complex Portal ID property"""
get_macromolecular = """
SELECT ?item ?ComplexPortalID
WHERE
{
?item wdt:P7718 ?ComplexPortalID .
}"""
wikidata_complexes = WDItemEngine.execute_sparql_query(
get_macromolecular,
as_dataframe=True).replace({"http://www.wikidata.org/entity/": ""},
regex=True)
return wikidata_complexes
def get_interacted_RNA_references(self):
interacted_RNA_references = []
row_nums = 0
query_file = open('ALL_INTERACTED_SRNA_QUERY.rq', 'r')
query_template = query_file.read()
query_file.close()
QUERY = query_template
QUERY = QUERY.replace("#QID#", self.QID)
results = WDItemEngine.execute_sparql_query(
QUERY)['results']['bindings']
if len(results) != 0:
for result in results:
row_nums += 1
interacted_RNA_references.append([
row_nums, result['rnaLabel']['value'],
result['propLabel']['value'],
result['targetLabel']['value'],
f"{result['quote']['value']}"
'</br><a target="_self" href="Article_Viewer.html?article_PMCID='
f"{result['PMCID']['value']}"
f""e={urllib.parse.quote_plus(result['quote']['value'])}"
'">Read this in the article</a>',
'<div class="form-control"><a target="_blank" href="'
f"{result['rna']['value']}"
'"><img src="static/images/Interact_logo_Wikidata.png" '
'height="30px" class="rounded"></a></div>'
])
else:
return "Query returns nothing."
data_tbl_cols = [
'#', 'sRNA', 'Type of Regulation', 'Target Gene', 'Quote', 'Source'
]
data_tbl_df = pd.DataFrame(interacted_RNA_references,
columns=data_tbl_cols)
pd.set_option('display.max_colwidth', -1)
data_tbl = data_tbl_df.to_html(index=False,
escape=False,
bold_rows=False,
max_rows=None,
max_cols=None,
table_id="data_tbl",
justify="center")
data_tbl = data_tbl.replace('border="1" ', "")
data_tbl = data_tbl.replace(
'class="dataframe" ', 'class="display responsive no-wrap" '
'style="font-family: Courier New; font-size: 13px;"')
final_html = f"<div><h4>Referenced items: {self.get_wd_label()}</h4></div>{data_tbl}"
return final_html
def get_wd_label(self):
query_file = open('Label_Fetch_Query.rq', 'r')
query_template = query_file.read()
QUERY = query_template
QUERY = QUERY.replace("#QID#", self.QID)
results = WDItemEngine.execute_sparql_query(
QUERY)['results']['bindings']
item = ""
if len(results) == 0:
print("Query returns no items for the specified Q-ID.")
elif len(results) == 1:
for result in results:
item = result['label']['value']
else:
print("Query returns more that Item for the same Q-ID.")
query_file.close()
return item
def get_QID(self, query):
item_QID = []
results = WDItemEngine.execute_sparql_query(
query)['results']['bindings']
if len(results) == 0:
item_QID.append("NOT_FOUND_IN_WD")
elif len(results) == 1:
for result in results:
item_QID.append(result['item']['value'].replace(
"http://www.wikidata.org/entity/", ""))
else:
for result in results:
item_QID.append(result['item']['value'].replace(
"http://www.wikidata.org/entity/", ""))
print(
"Warning: Query returns more than one item for the same gene name! Selected: "
+ result['item']['value'])
return item_QID
def search_for_drug(drug_name):
query = """SELECT ?item ?label WHERE {
SERVICE wikibase:mwapi {
bd:serviceParam wikibase:api "EntitySearch" .
bd:serviceParam wikibase:endpoint "www.wikidata.org" .
bd:serviceParam mwapi:search "***s***" .
bd:serviceParam mwapi:language "en" .
?item wikibase:apiOutputItem mwapi:item .
?label wikibase:apiOutputItem mwapi:label
}
?item (wdt:P279|wdt:P31) wd:Q11173 .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
}""".replace("***s***", drug_name)
results = [{
k: v['value'].replace("http://www.wikidata.org/entity/", "")
for k, v in x.items()
} for x in WDItemEngine.execute_sparql_query(query)['results']['bindings']]
return results
def get_wikidata_item_by_propertyvalue(property, value):
"""Gets a Wikidata item for a determined property-value pair
Args:
property (str): The property to search
value (str): The value of said property
"""
query_result = WDItemEngine.execute_sparql_query(
f'SELECT distinct ?item WHERE {{ ?item wdt:{property} "{value}" }}')
try:
match = query_result["results"]["bindings"][0]
except IndexError:
print(f"Couldn't find item for {value}")
return pd.np.NaN
qid = match["item"]["value"]
qid = qid.split("/")[4]
return qid
def get_existing(cls):
# get existing combinations:
query_str = """SELECT ?item ?itemLabel (GROUP_CONCAT(?part; separator=";") as ?f) WHERE {
?item wdt:P527 ?part .
?item wdt:P31|wdt:P279 wd:Q1304270 .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} GROUP BY ?item ?itemLabel"""
results = WDItemEngine.execute_sparql_query(
query_str)['results']['bindings']
qid_combo = {
x['item']['value'].replace("http://www.wikidata.org/entity/", ""):
frozenset([
y.replace("http://www.wikidata.org/entity/", "")
for y in x['f']['value'].split(";")
])
for x in results
}
combo_qid = {v: k for k, v in qid_combo.items()}
assert len(combo_qid) == len(qid_combo)
cls.combo_qid = combo_qid
cls.qid_combo = qid_combo
def get_QID_for_duplicated(self, locus_tag):
item_QID = ""
query_file = open('query_templates/FIND_ITEM_BY_LOCUS_TAG.rq', 'r')
query_template = query_file.read()
QUERY = query_template
QUERY = QUERY.replace("#QID#", self.Organism_QID).replace(
"#LOCUS_TAG#", locus_tag)
results = WDItemEngine.execute_sparql_query(
QUERY)['results']['bindings']
if len(results) == 0:
print("Query returns no items for the specified Q-ID.")
elif len(results) == 1:
for result in results:
item_QID = result['item']['value']
else:
for result in results:
item_QID = result['item']['value']
break
print("Query returns more that Item for the same Q-ID.")
query_file.close()
return item_QID
def get_doid_qid_map():
# get a good QID to DOID map, using exact match only
query = """
SELECT distinct ?disease ?doid ?mrt WHERE {
?disease p:P699 ?s_doid .
?s_doid ps:P699 ?doid .
OPTIONAL {?s_doid pq:P4390 ?mrt} .
}
"""
df = WDItemEngine.execute_sparql_query(query, as_dataframe=True)
df.disease = df.disease.str.replace("http://www.wikidata.org/entity/", "")
df = df[df.mrt.isnull() |
(df.mrt == "http://www.wikidata.org/entity/Q39893449")]
df.drop_duplicates(subset=['disease', 'doid'], inplace=True)
# make sure one doid goes with one qid
bad1 = df[df.duplicated("disease", keep=False)]
bad2 = df[df.duplicated("doid", keep=False)]
# get rid of these baddies
df = df[~df.index.isin(bad1.index)]
df = df[~df.index.isin(bad2.index)]
doid_qid = dict(zip(df.doid, df.disease))
return doid_qid
def get_wikidata_do_mesh():
# get mesh xrefs, and including mapping relation type
# {'DOID:0050856': {'skos:broadMatch_D019958'}}
query = """
select ?item ?doid ?mesh ?mesh_rt where {
?item wdt:P699 ?doid .
?item p:P486 ?mesh_s .
?mesh_s ps:P486 ?mesh .
optional { ?mesh_s pq:P4390 ?mesh_rt }
}"""
results = WDItemEngine.execute_sparql_query(query)['results']['bindings']
results = [{k: v['value'].replace("http://www.wikidata.org/entity/", "") for k, v in item.items()} for item in
results]
df = pd.DataFrame(results)
df['mesh_rt'] = df.apply(lambda row: QID_MAP_REL_TYPE_CURIE[row.mesh_rt] + "_MESH:" + row.mesh, axis=1)
df['_item'] = df['item']
r = df.groupby("_item").aggregate(lambda x: set(y for y in x if not pd.isnull(y))).to_dict("records")
wd = {list(x['doid'])[0]: x for x in r}
wd = {k: v['mesh_rt'] for k, v in wd.items()}
wd = {k: v for k, v in wd.items() if v}
return wd
def get_wikidata_do_xrefs():
"""
From wikidata, get all items with a DOID on them. Get all of the following external-ids (defined in PREFIXES)
:return: dict. values look like:
'DOID:8499': {'ICD10CM': {'H53.6', 'H53.60'},
'ICD9CM': {'368.6', '368.60'},
'MESH': {'D009755'},
'NCI': {'C34850', 'C37997'},
'UMLS_CUI': {'C0028077'},
'disease': 'Q7758678',
'doid': 'DOID:8499'}
"""
# get xrefs on items where DO is an exact match, and the statement doesn't have a MONDO reference
# getting ones in which it has no reference, or it has a reference which is not from mondo
# is too complicated, so get the ref info and we'll filter out the mondo ones afterwards
query_template = """
SELECT ?disease ?doid ?xref ?match ?mondo WHERE {{
?disease p:P699 ?s_doid .
?s_doid ps:P699 ?doid .
OPTIONAL {{ ?s_doid pq:P4390 ?match}}
?disease p:{xref_pid} ?s_xref .
?s_xref ps:{xref_pid} ?xref .
OPTIONAL {{?s_xref prov:wasDerivedFrom ?ref .
?ref pr:P5270 ?mondo }}
}}
"""
# these match the prefixes in DO (but uppercase)
xref_pid = {
'omim': 'P492',
'icd9cm': 'P1692',
'icd10cm': 'P4229',
'ordo': 'P1550',
'gard': 'P4317',
'mesh': 'P486',
'UMLS_CUI': 'P2892',
'nci': 'P1748',
}
dfs = []
for xref, pid in xref_pid.items():
query = query_template.format(xref_pid=pid)
df = WDItemEngine.execute_sparql_query(query, as_dataframe=True)
# has no reference but if it does, is not a mondo reference
df = df[df.mondo.isnull()]
# has no qualifier or is an exact match
df = df[df.match.isnull() |
(df.match == "http://www.wikidata.org/entity/Q39893449")]
df = df.groupby('doid').xref.apply(
lambda x: ",".join(x)).reset_index().set_index("doid")
df.rename(columns={'xref': xref.upper()}, inplace=True)
dfs.append(df)
# join all of these dfs together
dfj = reduce(lambda x, y: x.join(y), dfs)
d = dict()
for doid, row in dfj.iterrows():
s = set()
for k, v in row.to_dict().items():
if not pd.isnull(v):
s.update(k + ":" + vv for vv in v.split(","))
d[doid] = {'xref': s}
return d
def process_query_string(query):
result = WDItemEngine.execute_sparql_query(query)
bindings = result['results'].get('bindings')
return format_wikidata_bindings(bindings)
combo = set(df[(df.Drug.str.count(";") > 0)].Drug)
combo_parts_qid = {
k: frozenset(name_qid.get(kk.lower()) for kk in k.split(";"))
for k in combo
}
#qid_combo_parts = {v:k for k,v in combo_parts_qid.items()}
# combo = set([x for x in combo if all(y.lower() in name_qid for y in x.split(";"))]) # some have families
# will have to make these combinations in wikidata
# get existing combinations:
query_str = """SELECT ?item ?itemLabel (GROUP_CONCAT(?part; separator=";") as ?f) WHERE {
?item wdt:P527 ?part .
?item wdt:P31 wd:Q1304270 .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],en". }
} GROUP BY ?item ?itemLabel"""
results = WDItemEngine.execute_sparql_query(query_str)['results']['bindings']
combo_qid = {
x['item']['value'].replace("http://www.wikidata.org/entity/", ""):
frozenset([
y.replace("http://www.wikidata.org/entity/", "")
for y in x['f']['value'].split(";")
])
for x in results
}
qid_combo = {v: k for k, v in combo_qid.items()}
assert len(combo_qid) == len(qid_combo)
# ---------------- Create combination treatment items
login = WDLogin(user=WDUSER, pwd=WDPASS)
for name_str, items in combo_parts_qid.items():
if not all(x for x in items):
from wikidataintegrator.wdi_core import WDItemEngine
from scheduled_bots.local import GREGUSER, GREGPASS
login = WDLogin(GREGUSER, GREGPASS)
log_path = "/home/gstupp/projects/wikidata-biothings/scheduled_bots/scheduled_bots/ontology/logs/Monarch Disease Ontology-20180727_17:11.log"
# from a sparql query, get a list of items that we want to undo edits from
query = """
select ?item (count(*) as ?c) where {
SELECT DISTINCT ?item ?mondo WHERE {
?item wdt:P5270 ?mondo
}
} group by ?item
having (?c > 1)
order by desc(?c)"""
item_df = WDItemEngine.execute_sparql_query(query, as_dataframe=True)
item_df.item = item_df.item.str.replace("http://www.wikidata.org/entity/", "")
items = list(item_df.item)
# read in a log of the run
def parse_log(file_path):
df = pd.read_csv(file_path,
sep=",",
names=[
'Level', 'Timestamp', 'External ID', 'Prop', 'QID',
'Message', 'Msg Type', 'Rev ID'
],
skiprows=2,
dtype={
'External ID': str,
?item2 wdt:P31|wdt:P279 wd:Q8054 .
FILTER NOT EXISTS {{?item1 wdt:P703 wd:Q15978631}}
FILTER( ?item1 != ?item2 && STR( ?item1 ) < STR( ?item2 ) ) .
}}"""
s_gene = """
SELECT DISTINCT ?item1 ?item2 ?value {{
?item1 wdt:P351 ?value .
?item2 wdt:P351 ?value .
?item1 wdt:P703 ?taxon1 .
?item2 wdt:P703 ?taxon2 .
FILTER( ?item1 != ?item2 && STR( ?item1 ) < STR( ?item2 ) && ?taxon1 = ?taxon2) .
FILTER NOT EXISTS {{?item1 wdt:P703 wd:Q15978631}}
}}"""
s = s_gene
items = [{k: v['value'].split("/")[-1]
for k, v in x.items()}
for x in WDItemEngine.execute_sparql_query(s)['results']['bindings']]
for x in tqdm(items):
try:
WDItemEngine.merge_items(
from_id=x['item2'],
to_id=x['item1'],
login_obj=login,
ignore_conflicts='statement|description|sitelink')
except MergeError as e:
print(e)
pass
def process_query_string(query):
"""Use WikiDataIntegrator Engine to process a SPARQL Query."""
result = WDItemEngine.execute_sparql_query(query,
endpoint=SPARQL_ENDPOINT_URL)
bindings = result['results'].get('bindings')
return _format_wikidata_bindings(bindings)