<jupyter_start><jupyter_text>Filtering records using SPARQL<jupyter_code>%load_ext autoreload
%autoreload 2
import sys
sys.path.append("..")
import pandas as pd
import re
from fuzzywuzzy import fuzz
from heritageconnector.utils.sparql import get_sparql_results

pd.set_option('display.max_colwidth', None)
pd.set_option('display.max_columns', None)

endpoint_url = "https://query.wikidata.org/sparql"

df = pd.read_pickle("../GITIGNORE_DATA/lookup_result.pkl")
people_df = df[df['GENDER'].isin(["M", "F"])]

len(people_df)
map_ids = lambda ids: ", ".join([f"wd:{i}" for i in ids])
map_ids_values = lambda ids: " ".join([f"(wd:{i})" for i in ids])

def return_labels_aliases_by_property(query_ids, property_id, include_class_tree):
    
    class_tree = "/wdt:P279*" if include_class_tree else ""
        
    query = f"""
    SELECT ?item ?itemLabel ?altLabel
            WHERE
            {{
                VALUES (?item) {{ {map_ids_values(query_ids)} }}
                ?item wdt:P31{class_tree} wd:{property_id}.
                ?item skos:altLabel ?altLabel .
                FILTER (lang(?altLabel) = "en")

                SERVICE wikibase:label {{ 
                  bd:serviceParam wikibase:language "en" .
                }}
            }} 
    GROUP BY ?item ?itemLabel ?altLabel
    """
    res = get_sparql_results(endpoint_url, query)['results']['bindings']
    
    res_df = pd.json_normalize(res)
    res_df['qcode'] = res_df['item.value'].apply(lambda x: re.findall(r"(Q\d+)", x)[0])
    res_df = res_df[['qcode', 'itemLabel.value', 'altLabel.value']]
    # convert aliases to lowercase and drop duplicates
    res_df['altLabel.value'] = res_df['altLabel.value'].astype(str).str.lower()
    res_df = res_df.drop_duplicates(subset = 'altLabel.value')
    
    return res_df
qcodes_unique = [item for item in list(set(people_df['res_WIKIDATA_IDs'].sum())) if str(item).startswith("Q")]
len(qcodes_unique)
qcodes_query = qcodes_unique
import time
start = time.time()
res_df = return_labels_aliases_by_property(qcodes_query, "Q5", include_class_tree=False)
print(time.time() - start)
res_df
def get_aliases(qcodes):
    return [res_df.loc[res_df['qcode'] == qcode, 'altLabel.value'].tolist() for qcode in qcodes]

def get_labels(qcodes):
    return [res_df.loc[res_df['qcode'] == qcode, 'itemLabel.value'].unique().tolist() for qcode in qcodes]

#get_labels(["Q762", "Q55021352"]), get_aliases(["Q762", "Q55021352"])<jupyter_output><empty_output><jupyter_text>integrating into heritageconnector<jupyter_code>from heritageconnector.entity_matching.filtering import Filter

f = Filter(dataframe=people_df, qcode_col="res_WIKIDATA_IDs")
f.add_instanceof_filter("Q5", False)
f.add_label_filter("PREFERRED_NAME", threshold=90, include_aliases=True, fuzzy_match_scorer=fuzz.token_sort_ratio)
f.process_dataframe()
new_df = f.get_dataframe()
len(new_df[(new_df['qcodes_filtered'].map(lambda d: len(d)) > 0)]) / len(people_df)
org_df = df[df['GENDER'] == "N"]
fo = Filter(dataframe=org_df, qcode_col="res_WIKIDATA_IDs")
fo.add_instanceof_filter("Q43229", True)
fo.add_label_filter("PREFERRED_NAME", threshold=80, include_aliases=True, fuzzy_match_scorer=fuzz.token_set_ratio)
fo.process_dataframe()
org_res_df = fo.get_dataframe()
num_orgs_after_filter = len(org_res_df[(org_res_df['qcodes_filtered'].map(lambda d: len(d)) > 0)])
num_orgs_after_filter, len(org_df), num_orgs_after_filter / len(org_df)
# no. organisations with more than one qcode remaining
len(org_res_df[(org_res_df['qcodes_filtered'].map(lambda d: len(d)) > 1)])
ids_less_strict = org_res_df[(org_res_df['qcodes_filtered'].map(lambda d: len(d)) > 0)].index.tolist()
ids_more_strict = org_res_df[(org_res_df['qcodes_filtered'].map(lambda d: len(d)) > 0)].index.tolist()
org_res_df.columns
org_res_df.loc[list(set(ids_less_strict) - set(ids_more_strict)), ["PREFERRED_NAME", "NOTE", "res_WIKIDATA_IDs", "res_URLS", "qcodes_filtered"]]
fo.view_stats()<jupyter_output>No. records after filtering: 697/7743 (9.0%)<jupyter_text>filtering with birth and death dates<jupyter_code>f = Filter(dataframe=people_df, qcode_col="res_WIKIDATA_IDs")
f.add_date_filter("BIRTH_DATE", "birthYear", 8)
f.process_dataframe()<jupyter_output>/Users/kalyan/.local/share/virtualenvs/heritage-connector-tkmarX41/lib/python3.7/site-packages/pandas/core/indexing.py:966: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  self.obj[item] = s
{'fraction_non_alphanumeric': 0.14483354950281022, 'fraction_numerical': 0.01340250756593169, 'mean_word_length': 5.462290502793296, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 16, 'https://': 2, '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': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '244199', 'license': 'MIT', 'n_tokens_mistral': 1789, 'n_tokens_neox': 1709, 'n_words': 288, 'path': 'experiments/2020_06_23 SPARQL filter.ipynb', 'repo_name': 'TheScienceMuseum/heritage-connector', 'size': '4626'}