Investigation: Q1 Count Discrepancy (1001 vs 920)

Goal: The class-counts notebook returns 1001 items for Q1 (Category), but the expected count is 920. This notebook investigates the 81 extra items.

Wikibase Instance: ClimateKG Production

📓 Download Notebook: investigate-q1-count.ipynb

Show code

from SPARQLWrapper import SPARQLWrapper, JSON
import pandas as pd
from IPython.display import display, Markdown, HTML

SPARQL_ENDPOINT = "https://prod-climatekg.semanticclimate.org/query/proxy/sparql"
WIKIBASE_URL = "https://prod-climatekg.semanticclimate.org"
PREFIXES = f"""
PREFIX wd: <{WIKIBASE_URL}/entity/>
PREFIX wdt: <{WIKIBASE_URL}/prop/direct/>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
"""

def run_query(query):
    sparql = SPARQLWrapper(SPARQL_ENDPOINT)
    sparql.setQuery(PREFIXES + query)
    sparql.setReturnFormat(JSON)
    results = sparql.query().convert()
    rows = [{k: v["value"] for k, v in b.items()} for b in results["results"]["bindings"]]
    return pd.DataFrame(rows)

print("✓ Ready")

✓ Ready

Step 1 — Confirm the counts

First confirm the raw count vs distinct count to check for duplicate triples.

Show code

# Raw count (same as class-counts notebook)
df_raw = run_query("SELECT (COUNT(?item) AS ?count) WHERE { ?item wdt:P1 wd:Q1 . }")
raw_count = int(df_raw["count"].iloc[0])

# Distinct count — removes duplicate ?item rows from the result set
df_distinct = run_query("SELECT (COUNT(DISTINCT ?item) AS ?count) WHERE { ?item wdt:P1 wd:Q1 . }")
distinct_count = int(df_distinct["count"].iloc[0])

print(f"Raw COUNT:          {raw_count}")
print(f"COUNT(DISTINCT):    {distinct_count}")
print(f"Duplicate triples:  {raw_count - distinct_count}")
print(f"Expected:           920")
print(f"Over expected:      {distinct_count - 920}")

Raw COUNT:          1001
COUNT(DISTINCT):    1001
Duplicate triples:  0
Expected:           920
Over expected:      81

Step 2 — Items with duplicate P1=Q1 triples

If raw != distinct, these items have the P1=Q1 statement recorded more than once.

Show code

df_dupes = run_query("""
SELECT ?item (COUNT(?item) AS ?triples) WHERE {
  ?item wdt:P1 wd:Q1 .
}
GROUP BY ?item
HAVING (COUNT(?item) > 1)
ORDER BY DESC(?triples)
""")

if df_dupes.empty:
    print("✓ No duplicate triples — each item has exactly one P1=Q1 statement.")
else:
    print(f"Found {len(df_dupes)} items with duplicate P1=Q1 triples:")
    df_dupes["wikibase_link"] = df_dupes["item"].apply(
        lambda u: f'<a href="{u}" target="_blank">{u.split("/")[-1]}</a>'
    )
    display(HTML(df_dupes[["wikibase_link", "triples"]].to_html(escape=False, index=False)))

✓ No duplicate triples — each item has exactly one P1=Q1 statement.

Step 3 — Check if any Q1 items also have a second P1 value

Items classified as Q1 (Category) that also have another instance-of value — these might be misclassified.

Show code

df_multi = run_query("""
SELECT ?item ?otherClass WHERE {
  ?item wdt:P1 wd:Q1 .
  ?item wdt:P1 ?otherClass .
  FILTER(?otherClass != wd:Q1)
}
ORDER BY ?otherClass
""")

if df_multi.empty:
    print("✓ No Q1 items have additional P1 values.")
else:
    print(f"Found {len(df_multi)} Q1 items that also belong to another class:")
    df_multi["item_link"] = df_multi["item"].apply(
        lambda u: f'<a href="{u}" target="_blank">{u.split("/")[-1]}</a>'
    )
    df_multi["class_link"] = df_multi["otherClass"].apply(
        lambda u: f'<a href="{u}" target="_blank">{u.split("/")[-1]}</a>'
    )
    display(HTML(df_multi[["item_link", "class_link"]].to_html(escape=False, index=False)))
    print("\nBreakdown by other class:")
    print(df_multi["otherClass"].apply(lambda u: u.split("/")[-1]).value_counts().to_string())

✓ No Q1 items have additional P1 values.

Step 4 — Sample the extra items

Retrieve items with their labels to spot what the unexpected Q1 items are. We fetch all 1001 and look for patterns — items without labels, items with unusual QIDs, etc.

Show code

df_all = run_query("""
SELECT DISTINCT ?item ?label WHERE {
  ?item wdt:P1 wd:Q1 .
  OPTIONAL { ?item rdfs:label ?label . FILTER(LANG(?label) = "en") }
}
ORDER BY ?item
""")

# Extract numeric QID for sorting/analysis
df_all["qid"] = df_all["item"].apply(lambda u: u.split("/")[-1])
df_all["qid_num"] = pd.to_numeric(df_all["qid"].str.replace("Q", ""), errors="coerce")
df_all = df_all.sort_values("qid_num")

no_label = df_all[df_all["label"].isna()]
has_label = df_all[df_all["label"].notna()]

print(f"Total distinct Q1 items: {len(df_all)}")
print(f"  With label:            {len(has_label)}")
print(f"  Without label:         {len(no_label)}")
print(f"\nQID range: {df_all['qid'].iloc[0]} — {df_all['qid'].iloc[-1]}")

Total distinct Q1 items: 1001
  With label:            1001
  Without label:         0

QID range: Q8 — Q1175

Items without English labels

Show code

if no_label.empty:
    print("✓ All Q1 items have English labels.")
else:
    print(f"{len(no_label)} Q1 items have no English label:")
    no_label_display = no_label.copy()
    no_label_display["link"] = no_label_display["item"].apply(
        lambda u: f'<a href="{u}" target="_blank">{u.split("/")[-1]}</a>'
    )
    display(HTML(no_label_display[["link"]].to_html(escape=False, index=False)))

✓ All Q1 items have English labels.

High-QID items — likely recently added or imported

Items above a certain QID threshold may be from a recent import batch.

Show code

# Items with QID above 10000 — adjust threshold as needed
THRESHOLD = 10000
df_high = df_all[df_all["qid_num"] > THRESHOLD].copy()

print(f"Q1 items with QID > Q{THRESHOLD}: {len(df_high)}")
if not df_high.empty:
    df_high["link"] = df_high["item"].apply(
        lambda u: f'<a href="{u}" target="_blank">{u.split("/")[-1]}</a>'
    )
    display(HTML(df_high[["link", "label"]].head(50).to_html(escape=False, index=False)))
    if len(df_high) > 50:
        print(f"... and {len(df_high) - 50} more.")

Q1 items with QID > Q10000: 0

Step 5 — Summary

Summarise findings to pinpoint the source of the discrepancy.

Show code

print("=" * 50)
print("INVESTIGATION SUMMARY")
print("=" * 50)
print(f"Raw count (P1=Q1):        {raw_count}")
print(f"Distinct count:           {distinct_count}")
print(f"Expected:                 920")
print(f"Excess over expected:     {distinct_count - 920}")
print()
print("Possible causes:")
if raw_count != distinct_count:
    print(f"  [!] {raw_count - distinct_count} duplicate P1=Q1 triples found — clean up recommended")
else:
    print("  [✓] No duplicate triples")
if not df_multi.empty:
    print(f"  [!] {len(df_multi)} items have Q1 + another P1 class — possible misclassification")
else:
    print("  [✓] No items with multiple P1 classes")
if not no_label.empty:
    print(f"  [!] {len(no_label)} items missing English label — possible test/import artefacts")
else:
    print("  [✓] All items have English labels")
if not df_high.empty:
    print(f"  [!] {len(df_high)} items above Q{THRESHOLD} — check if from unexpected import batch")
else:
    print(f"  [✓] No items above Q{THRESHOLD} threshold")

==================================================
INVESTIGATION SUMMARY
==================================================
Raw count (P1=Q1):        1001
Distinct count:           1001
Expected:                 920
Excess over expected:     81

Possible causes:
  [✓] No duplicate triples
  [✓] No items with multiple P1 classes
  [✓] All items have English labels
  [✓] No items above Q10000 threshold