# basic defaults, including study dates, common SQL exclusions and parquet files for anonymized data
%run -i 'data-defaults.py'
select count(distinct el_from, el_to) from externallinks where el_to like '%tools.wmflabs.org%';
| count(distinct el_from, el_to) |
|---|
| 12804396 |
# count of tools.wmflabs.org links in 20190420 externallinks table
query = """
select count(distinct el_from, el_to) AS links
FROM ryanmax.population_externallinks
WHERE LOWER(el_to) LIKE '%tools.wmflabs.org%'
"""
spark.sql(query).show()
+--------+ | links| +--------+ |12664610| +--------+
# count of tools.wmflabs.org events by tools.wmflabs.org/subdirectory/
events_query = """
select CONCAT('tools.wmflabs.org/',REGEXP_REPLACE(link_url,'.*tools.wmflabs.org/([a-zA-Z0-9\-_]+)/?.*','$1')) as link_path, count(*) AS count
FROM citationusage
WHERE wiki = 'enwiki'
AND useragent_is_bot = FALSE
AND LOWER(link_url) LIKE '%://tools.wmflabs.org%'
GROUP BY link_path
ORDER BY count DESC
LIMIT 100
"""
events = spark.sql(events_query)
events.toPandas()
| link_path | count | |
|---|---|---|
| 0 | tools.wmflabs.org/geohack | 1053717 |
| 1 | tools.wmflabs.org/os | 15816 |
| 2 | tools.wmflabs.org/osm4wiki | 11249 |
| 3 | tools.wmflabs.org/isin | 2239 |
| 4 | tools.wmflabs.org/kmlexport | 1966 |
| 5 | tools.wmflabs.org/timescale | 1780 |
| 6 | tools.wmflabs.org/bibleversefinder2 | 1331 |
| 7 | tools.wmflabs.org/ftl | 1115 |
| 8 | tools.wmflabs.org/scholia | 435 |
| 9 | tools.wmflabs.org/panoviewer | 184 |
| 10 | tools.wmflabs.org/dupdet | 50 |
| 11 | tools.wmflabs.org/wikidata-externalid-url | 46 |
| 12 | tools.wmflabs.org/citeplato | 41 |
| 13 | tools.wmflabs.org/zoomviewer | 37 |
| 14 | tools.wmflabs.org/copyvios | 30 |
| 15 | tools.wmflabs.org/wiwosm | 28 |
| 16 | tools.wmflabs.org/family | 26 |
| 17 | tools.wmflabs.org/reasonator | 21 |
| 18 | tools.wmflabs.org/makeref | 13 |
| 19 | tools.wmflabs.org/citations | 11 |
| 20 | tools.wmflabs.org/dplbot | 9 |
| 21 | tools.wmflabs.org/dispenser | 6 |
| 22 | tools.wmflabs.org/refill | 5 |
| 23 | tools.wmflabs.org/hashtags | 4 |
| 24 | tools.wmflabs.org/wikivoyage | 3 |
| 25 | tools.wmflabs.org/magnustools | 3 |
| 26 | tools.wmflabs.org/citation-template-filling | 2 |
| 27 | tools.wmflabs.org/denkmalliste | 1 |
| 28 | tools.wmflabs.org/templatecount | 1 |
| 29 | tools.wmflabs.org/pageviews | 1 |
| 30 | tools.wmflabs.org/citer | 1 |