Top Level Domain Data¶

TLD and .gov counts from population_externallinks table for W and WP:M pages
TLD and .gov event counts by event type for W and WP:M pages
hostname parsing here explodes web.archive.org links; for web.archive.org counts, please see events-and-counts--hostnames.ipynb

In [3]:

# basic defaults, including study dates, common SQL exclusions and parquet files for anonymized data
%run -i 'data-defaults.py'

# show lots of data
pd.options.display.max_rows=1000

# parse hostnames from links
# includes extraction for web.archive.org links
parse_host = """
    PARSE_URL(REGEXP_REPLACE(LOWER({}),'^https?://web.archive.org/web/[^/]+/',''),'HOST')
"""
parse_host_from_el_to = parse_host.format('el_to')
parse_host_from_link_url = parse_host.format('link_url')

W counts from externallinks table¶

limited to W pages with external links
limited to 10000 or more links

In [4]:

# Summary counts of top level domains
# limited to W pages with external links
# limited to 10000 or more links
w_tld_query = """
SELECT 
    REGEXP_EXTRACT({},'(\.[^\.]+)$',1) AS tld, 
    COUNT(distinct el_from, el_to) AS num_links
FROM ryanmax.population_externallinks
WHERE 
    el_from IN (SELECT page_id FROM ryanmax.population_w_pages_with_extlinks)
GROUP BY tld
ORDER BY num_links DESC
"""
w_tld = spark.sql(w_tld_query.format(parse_host_from_el_to))
w_tld_pda = w_tld.toPandas()
df_filtered = w_tld_pda.query('num_links>10000').copy()
df_filtered

Out[4]:

	tld	num_links
0	.com	23883415
1	.org	14362804
2	.gov	4242619
3	.uk	3379001
4	.net	1749237
5	.edu	1544413
6	.au	1235596
7	.fr	996717
8	.ca	725635
9	.de	568513
10	.info	498609
11	.jp	454646
12	.in	328948
13	.us	289545
14	.nz	282507
15	.it	278292
16	.ru	263225
17	.is	243465
18	.cz	239175
19	.nl	230645
20	.ch	220589
21	.no	215629
22	.es	203148
23	.ie	201124
24	.se	187715
25	.mil	183311
26	.pl	174611
27	.eu	165142
28	.ir	158233
29	.br	131192
30	.cn	111787
31	.be	111576
32	.kr	108656
33	.za	106296
34	.fi	99709
35	.dk	96898
36	.tv	92665
37	.ph	85902
38	.mx	83582
39	.ro	82169
40	.at	78247
41	.gr	76477
42	.int	75565
43	.pk	71331
44	.il	69635
45	.my	66949
46	.pt	66362
47	.tr	65519
48	.hu	61909
49	.ua	61464
50	.scot	58618
51	.ar	55854
52	.hr	50064
53	.lk	49648
54	.sg	47578
55	.si	44939
56	.tw	44324
57	.rs	44288
58	.hk	40987
59	None	34587
60	.co	32542
61	.cl	28117
62	.ee	28045
63	.lv	26734
64	.id	25775
65	.sk	25763
66	.today	25157
67	.biz	22921
68	.bg	22367
69	.th	20284
70	.lt	19658
71	.az	19451
72	.cat	17723
73	.pe	17312
74	.lu	17160
75	.am	16234
76	.io	16231
77	.ng	15892
78	.me	15535
79	.vn	15368
80	.fm	14571
81	.ae	14262
82	.ke	14118
83	.ug	13832
84	.va	13721
85	.bd	13281
86	.ba	12749
87	.al	12602
88	.np	12420
89	.uy	11014
90	.md	10260
91	.by	10128

WP:M counts from externallinks table¶

limited to WP:M pages with external links

In [5]:

# Summary counts of top level domains
# limited to WP:M pages with external links
wpm_tld_query = """
SELECT 
    REGEXP_EXTRACT({},'(\.[^\.]+)$',1) as tld, 
    COUNT(distinct el_from, el_to) AS num_links
FROM ryanmax.population_externallinks
WHERE 
    el_from IN (SELECT page_id FROM ryanmax.population_wpm_pages_with_extlinks)
GROUP BY tld
ORDER BY num_links DESC
"""
wpm_tld = spark.sql(wpm_tld_query.format(parse_host_from_el_to))
wpm_tld.toPandas()

Out[5]:

	tld	num_links
0	.org	331874
1	.gov	286912
2	.com	177458
3	.uk	28999
4	.edu	27992
5	.int	19854
6	.ca	9430
7	.au	7407
8	.net	7302
9	.fr	5201
10	.info	3788
11	.jp	3310
12	.eu	3209
13	.de	2958
14	.no	2568
15	.nz	1467
16	.is	1448
17	.cz	1308
18	.in	1195
19	.ch	1127
20	.se	1052
21	.us	1005
22	.ie	989
23	.nl	925
24	.it	901
25	.today	882
26	.es	784
27	.ru	763
28	.br	714
29	.mil	694
30	.za	670
31	.cn	619
32	.io	574
33	.be	404
34	.pl	395
35	.sg	352
36	.pk	335
37	.dk	334
38	.il	325
39	.tw	322
40	.fi	308
41	.my	292
42	.hk	285
43	.ar	278
44	.at	248
45	.ro	235
46	.mx	219
47	.pt	201
48	.tv	199
49	.co	194
50	.ph	194
51	.ug	188
52	.kr	183
53	.hu	176
54	.ir	168
55	.hr	163
56	.ua	152
57	.tr	138
58	None	127
59	.lv	122
60	.lk	109
61	.cl	94
62	.ae	93
63	.gr	92
64	.rs	90
65	.si	88
66	.am	86
67	.ke	83
68	.th	76
69	.vn	64
70	.sa	64
71	.bg	63
72	.ee	60
73	.mm	60
74	.cc	57
75	.cu	57
76	.ng	56
77	.ve	56
78	.mt	54
79	.az	53
80	.pe	52
81	.id	50
82	.np	50
83	.sk	49
84	.scot	49
85	.bd	49
86	.jo	49
87	.cat	48
88	.bt	48
89	.ws	45
90	.zm	41
91	.mp	40
92	.sl	40
93	.tz	39
94	.uy	37
95	.lt	37
96	.biz	36
97	.gh	35
98	.eg	33
99	.md	32
100		29
101	.va	29
102	.bo	27
103	.lb	25
104	.cr	25
105	.me	25
106	.om	24
107	.wales	24
108	.tt	21
109	.pa	21
110	.nu	20
111	.ma	19
112	.al	19
113	.museum	19
114	.na	18
115	.uz	18
116	.ec	18
117	.kz	18
118	.lu	18
119	.mk	17
120	.news	17
121	.ge	16
122	.asia	16
123	.li	14
124	.tm	14
125	.bn	14
126	.4	14
127	.cy	13
128	.mn	13
129	.lr	13
130	.qa	13
131	.ni	13
132	.pg	12
133	.ac	12
134	.tn	12
135	.lc	11
136	.rw	11
137	.ms	11
138	.et	11
139	.fm	11
140	.af	10
141	.gt	10
142	.kg	10
143	.vg	10
144	.ht	10
145	.fo	10
146	.kp	10
147	.bz	9
148	.by	9
149	.pro	9
150	.fj	9
151	.ba	9
152	.mo	9
153	.py	9
154	.bw	9
155	.mv	8
156	.zw	8
157	.gi	8
158	.hn	8
159	.ly	8
160	.pr	7
161	.jm	7
162	.tj	7
163	www	7
164	.do	7
165	.html	7
166	.bm	7
167	.mw	7
168	.kw	7
169	.132	6
170	.gg	6
171	.im	6
172	.sv	6
173	.vu	6
174	.bf	6
175	.ps	6
176	.sd	6
177	.kn	6
178	.173	6
179	.to	6
180	.ag	5
181	.kh	5
182	.68	5
183	.bb	5
184	.65	5
185	.la	5
186	.dm	5
187	.gy	5
188	.26	4
189	.codes	4
190	.ml	4
191	.press	4
192	.51	4
193	.coop	4
194	.102	4
195	.guide	4
196	.103	4
197	.ky	4
198	.online	4
199	.foundation	4
200	.9	4
201	.sz	4
202	.87	4
203	.aero	4
204	.cymru	4
205	.review	4
206	.bs	4
207	.5	4
208	.43	3
209	.150	3
210	.mobi	3
211	.135	3
212	.report	3
213	.su	3
214	.ai	3
215	.144	3
216	.nyc	3
217	.global	3
218	.29	3
219	.bh	3
220	.206	3
221	.166	3
222	.life	3
223	.237	3
224	.name	3
225	.academy	3
226	.198	3
227	.world	3
228	.cdc	2
229	.media	2
230	.legendsofamerica	2
231	.216	2
232	.dz	2
233	.33	2
234	.13	2
235	peterson	2
236	.clinic	2
237	.sc	2
238	.xyz	2
239	.224	2
240	.pf	2
241	carl	2
242	.176	2
243	.vi	2
244	.live	2
245	.sm	2
246	.mu	2
247	.220	2
248	.mr	2
249	.12	2
250	.60	2
251	.blog	2
252	.183	2
253	.gm	2
254	.je	2
255	.195	2
256	.99	2
257	.2	2
258	.131	2
259	.31	2
260	.mc	2
261	.91	2
262	.52	2
263	.171	2
264	.care	2
265	.154	2
266	.nrw	2
267	.cancerresearch	2
268	.45	2
269	.75	2
270	.10	2
271	https	2
272	.194	2
273	.ad	2
274	.247	2
275	.130	2
276	.167	2
277	.107	2
278	.tk	2
279	.ao	2
280	.vc	2
281	.58	2
282	.18	2
283	.149	1
284	pn	1
285	.86	1
286	.211	1
287	.nihgov	1
288	http	1
289	.205	1
290	.zone	1
291	.23	1
292	.surgery	1
293	.bi	1
294	.36	1
295	.mdanderson	1
296	.93	1
297	.ga	1
298	.63	1
299	.233	1
300	.gd	1
301	.bid	1
302	.88	1
303	.202	1
304	.jobs	1
305	.cv	1
306	.231	1
307	.ye	1
308	.https	1
309	.bio	1
310	.cin	1
311	brescianogibraltarmysteries	1
312	.8	1
313	.ooo	1
314	.cd	1
315	www-uptodate-com	1
316	.sn	1
317	.104	1
318	.178	1
319	web	1
320	.219	1
321	.246	1
322	.100	1
323	.157	1
324	.eus	1
325	.wiki	1
326	.50	1
327	.ucr	1
328	.xn--p1ai	1
329	.pub	1
330	.png	1
331	ec	1
332	.ltd	1
333	.site	1
334	.cx	1
335	.uic	1
336	.14	1
337	pages	1
338	.libinst	1
339	.77	1
340	.ucfhttps	1
341	.190	1
342	.229	1
343	.133	1
344	.252	1
345	.70	1
346	.buzz	1
347	www-ncbi-nlm-nih-gov	1
348	.http	1
349	.238	1
350	.aarpinternational	1
351	.39	1
352	.sh	1
353	.radio	1
354	.support	1
355	.nip	1
356	.234	1
357	www-clinicalkey-com	1
358	.comu	1
359	.90	1
360	suite	1
361	.223	1
362	.ngo	1
363	.win	1
364	.83	1
365	.72	1
366	.comhttps	1
367	.pharmacy	1
368	.emerck	1
369	.200	1
370	.186	1
371	.78	1
372	.fund	1
373	.education	1
374	.gop	1
375	.iq	1
376	.40	1
377	.84	1
378	.241	1
379	med	1
380	.ajc	1
381	.tips	1
382	.archives	1
383	.group	1
384	.sy	1
385	.tl	1
386	monitoring	1
387	.expert	1
388	winslow-webpage	1
389	books	1
390	.center	1
391	.ci	1
392	pritzker	1
393	.189	1
394	.108	1
395	.222	1
396	.study	1
397	.80	1
398	.180	1
399	.xn--congosynthse-6db	1
400	.tg	1
401	.212	1

W .gov counts from externallinks table¶

limited to W pages with external links

In [6]:

# Summary counts of top level domains
# limited to W pages with external links
w_gov_query = """
SELECT 
    REGEXP_EXTRACT({},'(\.[^\.]+\.[^\.]+)$',1) as domain,
    COUNT(distinct el_from, el_to) AS num_links
FROM ryanmax.population_externallinks
WHERE 
    el_from IN (SELECT page_id FROM ryanmax.population_w_pages_with_extlinks)
    AND {} LIKE '%.gov'
GROUP BY domain
ORDER BY num_links DESC
"""
w_gov = spark.sql(w_gov_query.format(parse_host_from_el_to,parse_host_from_el_to))
w_gov.toPandas()

Out[6]:

	domain	num_links
0	.nih.gov	1203974
1	.nasa.gov	665995
2	.loc.gov	529931
3	.nps.gov	330918
4	.census.gov	265741
5	.usgs.gov	185509
6	.itis.gov	147485
7	.noaa.gov	70033
8	.fcc.gov	57186
9	.ars-grin.gov	37804
10	.ca.gov	37723
11	.usda.gov	37254
12	.congress.gov	37007
13	.state.gov	23578
14	.ed.gov	22937
15	.archives.gov	21956
16	.house.gov	19395
17	.faa.gov	16235
18	.virginia.gov	14903
19	.cia.gov	13015
20	.senate.gov	12410
21	.ny.gov	11422
22	.epa.gov	11062
23	.gpo.gov	10206
24	.wa.gov	9909
25	.whitehouse.gov	9604
26	.in.gov	9584
27	.nyc.gov	9504
28	.maryland.gov	9079
29	.sec.gov	8793
30	.fws.gov	8387
31	.defense.gov	8287
32	.dot.gov	8223
33	loc.gov	7547
34	.fjc.gov	6758
35	.mo.gov	6484
36	.utah.gov	6317
37	.cdc.gov	6248
38	.ga.gov	5436
39	.uspto.gov	5376
40	.ohio.gov	5239
41	.ky.gov	5140
42	.nist.gov	5099
43	.justice.gov	5046
44	.fbi.gov	4939
45	.uscourts.gov	4705
46	.oregon.gov	4675
47	.idaho.gov	4528
48	.fda.gov	4481
49	.usembassy.gov	4382
50	.supremecourt.gov	4164
51	.ct.gov	3996
52	.sc.gov	3756
53	.nationalmap.gov	3703
54	.la.gov	3593
55	.pa.gov	3582
56	.ncdcr.gov	3344
57	.michigan.gov	3124
58	.mass.gov	3068
59	.fec.gov	3058
60	.blm.gov	3048
61	.doe.gov	2966
62	.iowa.gov	2880
63	.usdoj.gov	2775
64	.federalregister.gov	2718
65	.mt.gov	2649
66	.ntsb.gov	2599
67	.energy.gov	2515
68	.wisconsin.gov	2492
69	.weather.gov	2454
70	.ri.gov	2403
71	.nj.gov	2403
72	.texas.gov	2381
73	.nsf.gov	2276
74	.irs.gov	2236
75	nj.gov	2214
76	.ilga.gov	2110
77	.maine.gov	2081
78	.delaware.gov	2043
79	.bop.gov	1913
80	.hawaii.gov	1902
81	.louisiana.gov	1888
82	.usbr.gov	1876
83	.bls.gov	1809
84	.usaid.gov	1737
85	.ok.gov	1733
86	.wi.gov	1731
87	.treasury.gov	1687
88	.nysed.gov	1678
89	.va.gov	1669
90	.gao.gov	1664
91	.nga.gov	1655
92	.eia.gov	1642
93	.seattle.gov	1640
94	.fema.gov	1639
95	.alaska.gov	1638
96	.dhs.gov	1636
97	.tn.gov	1621
98	.lbl.gov	1620
99	.regulations.gov	1608
100	.sd.gov	1568
101	.ftc.gov	1546
102	.gpoaccess.gov	1532
103	.ms.gov	1513
104	.usps.gov	1508
105	.nationalatlas.gov	1492
106	.dc.gov	1485
107	.txdot.gov	1482
108	.nd.gov	1467
109	.nh.gov	1425
110	.deldot.gov	1424
111	.houstontx.gov	1378
112	.kingcounty.gov	1348
113	.osti.gov	1332
114	.colorado.gov	1309
115	.ncdot.gov	1305
116	.dol.gov	1289
117	.md.gov	1284
118	.federalreserve.gov	1266
119	.hhs.gov	1171
120	.nwcg.gov	1152
121	.nrc.gov	1136
122	.newjersey.gov	1135
123	.cbo.gov	1128
124	.ornl.gov	1122
125	.arkansas.gov	1115
126	.mn.gov	1088
127	.illinois.gov	1071
128	.vermont.gov	1057
129	.nv.gov	1049
130	.ne.gov	1044
131	.mi.gov	1034
132	hawaii.gov	1031
133	.azsos.gov	1020
134	.bts.gov	1005
135	.wv.gov	1005
136	.doi.gov	939
137	.nysenate.gov	932
138	.lanl.gov	927
139	clinicaltrials.gov	909
140	.neh.gov	898
141	.uscis.gov	894
142	.nsa.gov	891
143	.gsa.gov	876
144	.nrel.gov	871
145	.anl.gov	867
146	.sandiego.gov	863
147	.ssa.gov	842
148	.osha.gov	836
149	pa.gov	831
150	.iowadot.gov	815
151	.myfloridahouse.gov	812
152	.cancer.gov	808
153	.cityofboston.gov	786
154	.ecfr.gov	778
155	.panynj.gov	761
156	.ncjrs.gov	755
157	.lacounty.gov	744
158	.cbp.gov	740
159	.bldrdoc.gov	729
160	.alabama.gov	713
161	.bnl.gov	708
162	.llnl.gov	695
163	.usmint.gov	667
164	.fdic.gov	661
165	.hud.gov	627
166	.fnal.gov	618
167	.arts.gov	612
168	energy.gov	611
169	medlineplus.gov	605
170	.bart.gov	604
171	.nebraska.gov	602
172	.fueleconomy.gov	600
173	.nea.gov	590
174	.az.gov	580
175	.azleg.gov	577
176	.miamidade.gov	562
177	.phila.gov	561
178	.georgia.gov	557
179	.fdlp.gov	548
180	.uscg.gov	545
181	.azgfd.gov	543
182	.treas.gov	529
183	.il.gov	523
184	.portlandoregon.gov	523
185	.america.gov	519
186	.centennialofflight.gov	516
187	.azlibrary.gov	496
188	.copyright.gov	491
189	.aoc.gov	489
190	.nara.gov	485
191	.sandia.gov	484
192	.bea.gov	476
193	.tennessee.gov	475
194	sdsos.gov	470
195	.azdot.gov	468
196	.cpsc.gov	459
197	.nycourts.gov	456
198	.doc.gov	439
199	.govinfo.gov	437
200	.clinicaltrials.gov	432
201	educateiowa.gov	431
202	.fairfaxcounty.gov	429
203	.ohiodnr.gov	427
204	.scstatehouse.gov	415
205	.atlantaga.gov	407
206	.flsenate.gov	399
207	.dni.gov	396
208	.okhouse.gov	394
209	michigan.gov	391
210	mn.gov	391
211	.pr.gov	388
212	.cambridgema.gov	370
213	morriscountynj.gov	367
214	.usconsulate.gov	367
215	.arkansased.gov	363
216	.safercar.gov	352
217	nationalatlas.gov	349
218	.wvdnr.gov	345
219	.wicourts.gov	343
220	.nmlegis.gov	343
221	.nhtsa.gov	343
222	.minneapolismn.gov	337
223	malegislature.gov	334
224	.opm.gov	330
225	wisconsindot.gov	328
226	.inl.gov	327
227	.ncsbe.gov	326
228	.educateiowa.gov	322
229	.fbo.gov	321
230	.ice.gov	321
231	.montgomerycountymd.gov	320
232	.ncparks.gov	317
233	.atf.gov	316
234	ilga.gov	315
235	.abmc.gov	313
236	.eeoc.gov	310
237	.recreation.gov	303
238	.baltimorecity.gov	303
239	.louisvilleky.gov	298
240	.sba.gov	288
241	.nashville.gov	286
242	.usmission.gov	283
243	nebraskalegislature.gov	280
244	.bjs.gov	277
245	.usa.gov	277
246	.ustreas.gov	274
247	.ferc.gov	270
248	census.gov	268
249	.ks.gov	266
250	.9-11commission.gov	266
251	.cabq.gov	265
252	.paauditor.gov	263
253	.rivers.gov	262
254	.iowacourts.gov	260
255	.stlouis-mo.gov	259
256	.supremecourtus.gov	252
257	.roanokeva.gov	251
258	.energystar.gov	251
259	.usap.gov	248
260	.tsa.gov	247
261	.usmarshals.gov	247
262	.akleg.gov	247
263	.cincinnati-oh.gov	246
264	.fordlibrarymuseum.gov	241
265	.ustr.gov	240
266	.samhsa.gov	237
267	.cftc.gov	237
268	.namus.gov	236
269	.oksenate.gov	236
270	.commerce.gov	235
271	.milwaukee.gov	234
272	.malegislature.gov	233
273	.tva.gov	229
274	.peacecorps.gov	227
275	.uscirf.gov	223
276	.ntdprogram.gov	222
277	.wichita.gov	220
278	.nvsos.gov	220
279	tennessee.gov	219
280	.bia.gov	219
281	.metrokc.gov	218
282	lacoast.gov	216
283	.ahrq.gov	216
284	.nystart.gov	216
285	arts.gov	214
286	.cap.gov	214
287	.sanjoseca.gov	213
288	.alexandriava.gov	211
289	epa.gov	211
290	.gop.gov	206
291	.oregonlegislature.gov	205
292	.pnl.gov	203
293	.ncua.gov	202
294	.pnnl.gov	202
295	.njsda.gov	201
296	.invasivespeciesinfo.gov	200
297	.princegeorgescountymd.gov	195
298	.guam.gov	193
299	.cecc.gov	191
300	.iowadnr.gov	190
301	phoenix.gov	188
302	.nifc.gov	186
303	.ffiec.gov	184
304	nvsos.gov	181
305	.indy.gov	179
306	.mms.gov	179
307	.imls.gov	179
308	majorityleader.gov	176
309	.bpa.gov	175
310	.austintexas.gov	174
311	.uscc.gov	173
312	.penndot.gov	172
313	.ussc.gov	170
314	.mncourts.gov	169
315	.globalchange.gov	166
316	americansamoa.gov	166
317	.nro.gov	166
318	.nlrb.gov	165
319	.mndnr.gov	164
320	.msha.gov	164
321	.hrsa.gov	164
322	.dea.gov	164
323	.maricopa.gov	163
324	.honolulu.gov	163
325	.clarkcountynv.gov	163
326	azsos.gov	160
327	.okc.gov	160
328	.erie.gov	159
329	.tempe.gov	158
330	.drugabuse.gov	158
331	.us-cert.gov	158
332	.genome.gov	158
333	.nixonlibrary.gov	157
334	.americaslibrary.gov	157
335	change.gov	155
336	.sdlegislature.gov	154
337	.ilsos.gov	154
338	.howardcountymd.gov	153
339	nsf.gov	153
340	ustr.gov	152
341	.transportation.gov	152
342	.ourdocuments.gov	151
343	.illinoiscourts.gov	150
344	.arc.gov	150
345	.consumerfinance.gov	149
346	.sanantonio.gov	149
347	.ohiohouse.gov	149
348	.nebraskalegislature.gov	148
349	.usaspending.gov	148
350	.detroitmi.gov	146
351	.digitalpreservation.gov	145
352	.doleta.gov	145
353	.kentucky.gov	141
354	.nc.gov	141
355	.nassaucountyny.gov	138
356	.phoenix.gov	138
357	.boston.gov	137
358	.sss.gov	137
359	.pppl.gov	136
360	.treasurydirect.gov	134
361	stlouis-mo.gov	134
362	.cms.gov	131
363	.salemcountynj.gov	131
364	.utcourts.gov	131
365	.longbeach.gov	128
366	.oregonmetro.gov	128
367	.hanford.gov	128
368	.export.gov	128
369	.somervillema.gov	127
370	.ttb.gov	127
371	.speaker.gov	126
372	.rrb.gov	124
373	.climatescience.gov	124
374	.pima.gov	124
375	.ohiosenate.gov	123
376	.capnhq.gov	122
377	.solardecathlon.gov	121
378	.ncpc.gov	119
379	.riversideca.gov	119
380	.eac.gov	119
381	.occ.gov	119
382	.nationalservice.gov	118
383	.fincen.gov	117
384	cbo.gov	115
385	.secretservice.gov	115
386	.osac.gov	114
387	.elpasotexas.gov	113
388	kingcounty.gov	112
389	.windpoweringamerica.gov	112
390	.txcourts.gov	111
391	.csb.gov	111
392	nyassembly.gov	110
393	.mcc.gov	110
394	nyc.gov	110
395	.fresno.gov	107
396	.childwelfare.gov	107
397	.lasvegasnevada.gov	107
398	azleg.gov	106
399	virginiageneralassembly.gov	105
400	.ar.gov	105
401	charlottenc.gov	105
402	maine.gov	104
403	capemaycountynj.gov	104
404	georgia.gov	103
405	.endow.gov	103
406	ntsb.gov	103
407	.usccr.gov	103
408	.raleighnc.gov	102
409	.suffolkcountyny.gov	102
410	.thomas.gov	100
411	.tfhrc.gov	99
412	.stpaul.gov	99
413	.socialsecurity.gov	99
414	.loudoun.gov	98
415	.surgeongeneral.gov	98
416	.usitc.gov	98
417	.santafenm.gov	96
418	.columbus.gov	96
419	.ofcm.gov	95
420	.hillsboro-oregon.gov	95
421	.moneyfactory.gov	94
422	.sfwmd.gov	93
423	.osmre.gov	93
424	.ustaxcourt.gov	93
425	mich.gov	92
426	.boem.gov	92
427	.baltimorecountymd.gov	91
428	.tucsonaz.gov	91
429	.csce.gov	88
430	tn.gov	88
431	.climate.gov	88
432	.huntsvilleal.gov	87
433	.buyusa.gov	87
434	.morriscountynj.gov	86
435	alexandriava.gov	85
436	.bbg.gov	85
437	.data.gov	85
438	.ada.gov	85
439	senate.gov	84
440	.missouri.gov	83
441	.dccourts.gov	83
442	.ndcourts.gov	82
443	.cityofrochester.gov	82
444	.seminolecountyfl.gov	82
445	.dod.gov	82
446	.scottsdaleaz.gov	82
447	.trade.gov	81
448	.ameslab.gov	81
449	.nctc.gov	81
450	iowadot.gov	80
451	.as.gov	80
452	.pittsburghpa.gov	79
453	.nola.gov	79
454	.duluthmn.gov	79
455	.wyo.gov	78
456	outdoornebraska.gov	78
457	.wvlegislature.gov	77
458	.gps.gov	76
459	.azcourts.gov	75
460	.miamibeachfl.gov	75
461	.eugene-or.gov	75
462	snohomishcountywa.gov	74
463	.manchesternh.gov	73
464	.norfolk.gov	73
465	.chulavistaca.gov	73
466	.jimmycarterlibrary.gov	72
467	.sbcounty.gov	72
468	abmc.gov	71
469	ipcc-wg2.gov	70
470	.reaganlibrary.gov	70
471	fortworthtexas.gov	70
472	.azcorrections.gov	69
473	.ntis.gov	69
474	.middlesexcountynj.gov	69
475	.attorneygeneral.gov	69
476	.goes-r.gov	68
477	.glin.gov	68
478	copyright.gov	68
479	.monroecounty.gov	68
480	.dol-esa.gov	68
481	deldot.gov	68
482	.azcc.gov	67
483	.wapa.gov	67
484	.fl.gov	67
485	myloc.gov	66
486	.kansas.gov	66
487	.lowellma.gov	66
488	.whitehousedrugpolicy.gov	65
489	.boemre.gov	65
490	.access-board.gov	64
491	.restorethegulf.gov	64
492	ftc.gov	64
493	.sbir.gov	64
494	columbus.gov	64
495	.ncdhhs.gov	64
496	detroitmi.gov	64
497	.ncdps.gov	64
498	.ready.gov	64
499	.santabarbaraca.gov	63
...	...	...
4289	.tiffinohio.gov	1
4290	.cuyonoticias.gov	1
4291	.fallcreekwi.gov	1
4292	.oceangate-nj.gov	1
4293	villageofphoenix-ny.gov	1
4294	.dccode.gov	1
4295	pima.gov	1
4296	hopewellva.gov	1
4297	.leo.gov	1
4298	sussexcountyva.gov	1
4299	.floresvilletx.gov	1
4300	visitthecapitol.gov	1
4301	andersontx.gov	1
4302	raleighnc.gov	1
4303	indiana.gov	1
4304	broadband.gov	1
4305	.oakham-ma.gov	1
4306	cranberryisles-me.gov	1
4307	.alexandercountync.gov	1
4308	.washingtoncountyny.gov	1
4309	.waltoncountyga.gov	1
4310	rutherfordcountytn.gov	1
4311	manchesternh.gov	1
4312	.stmaryparishla.gov	1
4313	.elbertcounty-co.gov	1
4314	.fsam.gov	1
4315	.iowastem.gov	1
4316	pacificflyway.gov	1
4317	hillsboro-oregon.gov	1
4318	.brycecanyoncityut.gov	1
4319	.angelfirenm.gov	1
4320	.washingtoncountyks.gov	1
4321	.abingdon-va.gov	1
4322	.northhampton-nh.gov	1
4323	.hispanicheritagemonth.gov	1
4324	brownwoodtexas.gov	1
4325	buckeyeaz.gov	1
4326	newrussiatownship-oh.gov	1
4327	.lewisham.gov	1
4328	.supreme-court.gov	1
4329	beavercreekohio.gov	1
4330	.jerome-oh.gov	1
4331	easthamptonma.gov	1
4332	pmi.gov	1
4333	tusayan-az.gov	1
4334	.nz.gov	1
4335	.staffordnj.gov	1
4336	goes-r.gov	1
4337	.wallawallawa.gov	1
4338	cityofpattersonla.gov	1
4339	wheelingil.gov	1
4340	marlboro-nj.gov	1
4341	azdoa.gov	1
4342	.mendonma.gov	1
4343	alexandercountync.gov	1
4344	sullivancountytn.gov	1
4345	martinsville-va.gov	1
4346	.riversideoh.gov	1
4347	.azunclaimed.gov	1
4348	.starnc.gov	1
4349	.miamicountyohio.gov	1
4350	leaguecitytx.gov	1
4351	jamesmadison.gov	1
4352	townoftropicut.gov	1
4353	daytonohio.gov	1
4354	leoncountyfl.gov	1
4355	.gustavus-ak.gov	1
4356	.burnsharbor-in.gov	1
4357	mobilecountyal.gov	1
4358	.eagle-wi.gov	1
4359	.egrpra.gov	1
4360	spp.gov	1
4361	smart.gov	1
4362	.iowacollegeaid.gov	1
4363	.jaspercountyin.gov	1
4364	.brysoncitync.gov	1
4365	dontserveteens.gov	1
4366	.portagewi.gov	1
4367	.jewishheritagemonth.gov	1
4368	waukeshacounty.gov	1
4369	kechiks.gov	1
4370	indianaffairs.gov	1
4371	.huroncounty-oh.gov	1
4372	hraunfoss.gov	1
4373	southoldtownny.gov	1
4374	.dh.gov	1
4375	normanparkga.gov	1
4376	.hin.gov	1
4377	.junctioncity-ks.gov	1
4378	brookhaven-ms.gov	1
4379	.mnhousing.gov	1
4380	.medweek.gov	1
4381	.cottagecitymd.gov	1
4382	townofriverheadny.gov	1
4383	cstx.gov	1
4384	.marylandtaxes.gov	1
4385	lacounty.gov	1
4386	.arkansacs.gov	1
4387	.yanceycountync.gov	1
4388	maineservicecommission.gov	1
4389	.investamerica.gov	1
4390	.dalharttx.gov	1
4391	crossvilletn.gov	1
4392	.mckenzietn.gov	1
4393	.cbca.gov	1
4394	.seatacwa.gov	1
4395	.mountainairnm.gov	1
4396	.grandrapidsmi.gov	1
4397	.lakepark-fl.gov	1
4398	.jeffersoncountyga.gov	1
4399	.northprovidenceri.gov	1
4400	.eatonville-wa.gov	1
4401	.mitchell-in.gov	1
4402	.montague-ma.gov	1
4403	.washingtonville-ny.gov	1
4404	.suffolk-va.gov	1
4405	.fff.gov	1
4406	.mountkiscony.gov	1
4407	.washingtonisland-wi.gov	1
4408	dcradio.gov	1
4409	manchester-ga.gov	1
4410	.greenwoodcounty-sc.gov	1
4411	amherstma.gov	1
4412	.monckscornersc.gov	1
4413	.plainfieldnj.gov	1
4414	ready.gov	1
4415	.baldwincountyal.gov	1
4416	.cityofladue-mo.gov	1
4417	.gulfshoreal.gov	1
4418	newbritainct.gov	1
4419	.danvilleva.gov	1
4420	lsc.gov	1
4421	.lyndonks.gov	1
4422	.montgomeryma.gov	1
4423	.pittsfieldnh.gov	1
4424	americanlatinomuseum.gov	1
4425	.nilestwpmi.gov	1
4426	iarpa.gov	1
4427	dunmorepa.gov	1
4428	.foodsafetyworkinggroup.gov	1
4429	.westford-ma.gov	1
4430	spaceflorida.gov	1
4431	.harriscountyga.gov	1
4432	.cityofgroveok.gov	1
4433	ustaxcourt.gov	1
4434	greyforest-tx.gov	1
4435	.bellavistaar.gov	1
4436	.cityofweyauwega-wi.gov	1
4437	buenavistaco.gov	1
4438	orau.gov	1
4439	.carboncyclescience.gov	1
4440	mansfieldct.gov	1
4441	.adamn.gov	1
4442	gsaig.gov	1
4443	mtbs.gov	1
4444	ricocolorado.gov	1
4445	.cityofworthingtonhillsky.gov	1
4446	utcourts.gov	1
4447	manisteemi.gov	1
4448	.mrlc.gov	1
4449	.cityofgalenapark-tx.gov	1
4450	oshrc.gov	1
4451	nbm.gov	1
4452	miramarfl.gov	1
4453	kingsporttn.gov	1
4454	.tuskegeealabama.gov	1
4455	lakeforestca.gov	1
4456	fcda.gov	1
4457	rappahannockcountyva.gov	1
4458	mnrg.gov	1
4459	bulverdetx.gov	1
4460	.hoopeston-il.gov	1
4461	.frogweb.gov	1
4462	savanna-il.gov	1
4463	.atlanta.gov	1
4464	.starkcountynd.gov	1
4465	.haverhillma.gov	1
4466	sf.gov	1
4467	symbols.gov	1
4468	eloyaz.gov	1
4469	.normanok.gov	1
4470	.qld.gov	1
4471	.cityofkingsburg-ca.gov	1
4472	.coag.gov	1
4473	.aftonwyoming.gov	1
4474	.measuretn.gov	1
4475	.farmington-mo.gov	1
4476	.tuxedopark-ny.gov	1
4477	dcfra.gov	1
4478	.savanna-il.gov	1
4479	.calaisvermont.gov	1
4480	nccs.gov	1
4481	mcminncountytn.gov	1
4482	.bacacountyco.gov	1
4483	.yukonok.gov	1
4484	.4woman.gov	1
4485	.hampsteadmd.gov	1
4486	azag.gov	1
4487	hamptonnh.gov	1
4488	pawneerock-ks.gov	1
4489	dot.gov	1
4490	.heyworth-il.gov	1
4491	carthagemo.gov	1
4492	fema.gov	1
4493	cullmanal.gov	1
4494	.coopercountymo.gov	1
4495	.greenecountyga.gov	1
4496	.citizenservices.gov	1
4497	huntspoint-wa.gov	1
4498	.identitytheft.gov	1
4499	.northsiouxcity-sd.gov	1
4500	.trousdalecountytn.gov	1
4501	.bosquefarmsnm.gov	1
4502	trumbull-ct.gov	1
4503	.statehouse.gov	1
4504	.historicpreservation.gov	1
4505	.fairmountheightsmd.gov	1
4506	.sccwi.gov	1
4507	mymedicare.gov	1
4508	poquoson-va.gov	1
4509	.piercecountywa.gov	1
4510	.fitzwilliam-nh.gov	1
4511	.copperascovetx.gov	1
4512	virginia.gov	1
4513	.milan-ny.gov	1
4514	newarknj.gov	1
4515	.boiseidaho.gov	1
4516	.sanmiguelcountyco.gov	1
4517	mead-co.gov	1
4518	.morgancountywv.gov	1
4519	.middletonma.gov	1
4520	.rockyhillct.gov	1
4521	desmoineswa.gov	1
4522	.foxcrossingwi.gov	1
4523	exim.gov	1
4524	.middleburgva.gov	1
4525	.interlachen-fl.gov	1
4526	.watchungnj.gov	1
4527	.ktik-nsn.gov	1
4528	mca.gov	1
4529	piermont-ny.gov	1
4530	.stoneham-ma.gov	1
4531	gallatin-tn.gov	1
4532	yesican.gov	1
4533	digital.gov	1
4534	.legislation.gov	1
4535	fbo.gov	1
4536	blainecounty-mt.gov	1
4537	.colchestervt.gov	1
4538	.medlineplus.gov	1
4539	cityofsouthfultonga.gov	1
4540	.greenecountyms.gov	1
4541	.warrickcounty.gov	1
4542	.stanlycountync.gov	1
4543	.emmitsburgmd.gov	1
4544	.coronaca.gov	1
4545	fhfb.gov	1
4546	hendersoncountytn.gov	1
4547	.hooveralabama.gov	1
4548	hialeahfl.gov	1
4549	fdicig.gov	1
4550	.jfc.gov	1
4551	.summervillesc.gov	1
4552	.burkittsville-md.gov	1
4553	lexingtonky.gov	1
4554	.njccc.gov	1
4555	duluthmn.gov	1
4556	.villageofkensingtonny.gov	1
4557	.toolecountymt.gov	1
4558	salemva.gov	1
4559	.openinternet.gov	1
4560	ohiohouse.gov	1
4561	.tisburyma.gov	1
4562	.ndop.gov	1
4563	ankenyiowa.gov	1
4564	franklincountyga.gov	1
4565	.cityofsouthfultonga.gov	1
4566	lawrenceburgtn.gov	1
4567	rpvca.gov	1
4568	.hpca.gov	1
4569	columbianaohio.gov	1
4570	.nolaoig.gov	1
4571	bureaucounty-il.gov	1
4572	.bureaucounty-il.gov	1
4573	.azboec.gov	1
4574	.nclawspecialists.gov	1
4575	.clarioniowa.gov	1
4576	.bayside-wi.gov	1
4577	rosebudcountymt.gov	1
4578	ise.gov	1
4579	section508.gov	1
4580	.ncdor.gov	1
4581	.richlandsnc.gov	1
4582	wwwcia.gov	1
4583	mesagrandeband-nsn.gov	1
4584	.gadsdencountyfl.gov	1
4585	.nagsheadnc.gov	1
4586	.crimevictims.gov	1
4587	.northvernon-in.gov	1
4588	.pinevillenc.gov	1
4589	browncounty-in.gov	1
4590	buyaccessible.gov	1
4591	.syracuseks.gov	1
4592	boxborough-ma.gov	1
4593	.azusaca.gov	1
4594	.emanuelco-ga.gov	1
4595	.transparencyflorida.gov	1
4596	.harrisonohio.gov	1
4597	.lakemt.gov	1
4598	williamsoncounty-tn.gov	1
4599	.sugarcityidaho.gov	1
4600	cheathamcountytn.gov	1
4601	lakestevenswa.gov	1
4602	.claycountyin.gov	1
4603	.lackawannany.gov	1
4604	.mahouse.gov	1
4605	swaincountync.gov	1
4606	heyworth-il.gov	1
4607	.clearspringmd.gov	1
4608	.franklinpa.gov	1
4609	fgdc.gov	1
4610	.limerick-me.gov	1
4611	.craigcountyva.gov	1
4612	.cherokeecounty-al.gov	1
4613	crystalmn.gov	1
4614	bountifulutah.gov	1
4615	honolulu.gov	1
4616	.sunnyside-wa.gov	1
4617	.townofcallahan-fl.gov	1
4618	consumeraction.gov	1
4619	.bellairetx.gov	1
4620	cravencountync.gov	1
4621	crestoniowa.gov	1
4622	.scottcountymn.gov	1
4623	cityoftitusvillepa.gov	1
4624	.chatsworthga.gov	1
4625	.azcjc.gov	1
4626	txdmv.gov	1
4627	wellesleyma.gov	1
4628	pci-nsn.gov	1
4629	cityofirondaleal.gov	1
4630	williamstownma.gov	1
4631	.covingtoncountyms.gov	1
4632	indianpoint-mo.gov	1
4633	.ca10uscourts.gov	1
4634	.losranchosnm.gov	1
4635	.mineralwellstx.gov	1
4636	.dallascountyiowa.gov	1
4637	dotgov.gov	1
4638	.clubdrugs.gov	1
4639	.dcfra.gov	1
4640	.blainemn.gov	1
4641	.piercecountynd.gov	1
4642	westonwi.gov	1
4643	northvernon-in.gov	1
4644	ramona-nsn.gov	1
4645	annapolis.gov	1
4646	nepa.gov	1
4647	townofhurtva.gov	1
4648	.killinglyct.gov	1
4649	.mecklenburgcountync.gov	1
4650	endingthedocumentgame.gov	1
4651	.masticbeachvillageny.gov	1
4652	starkcountyohio.gov	1
4653	.nutrition.gov	1
4654	.jackson-sc.gov	1
4655	.dumfriesva.gov	1
4656	.warren-ma.gov	1
4657	.mshaa.gov	1
4658	sandyspringsga.gov	1
4659	.eisgatewaypacificwa.gov	1
4660	.ninilchiktribe-nsn.gov	1
4661	.azhousing.gov	1
4662	mountainairnm.gov	1
4663	.superioraz.gov	1
4664	digitalliteracy.gov	1
4665	hhs.gov	1
4666	.trinityal.gov	1
4667	.tnk12.gov	1
4668	.buildingamerica.gov	1
4669	.usgms.gov	1
4670	.lccountymt.gov	1
4671	ratonnm.gov	1
4672	lagrangeny.gov	1
4673	.cg.gov	1
4674	.justthinktwice.gov	1
4675	achp.gov	1
4676	.chukchansi-nsn.gov	1
4677	cityofchetek-wi.gov	1
4678	townofminervany.gov	1
4679	harrisburgsd.gov	1
4680	shrewsbury-ma.gov	1
4681	.careervoyages.gov	1
4682	.noa.gov	1
4683	.recalls.gov	1
4684	srmt-nsn.gov	1
4685	.tallulahfallsga.gov	1
4686	.showlowaz.gov	1
4687	fortsmithar.gov	1
4688	.forestheightsmd.gov	1
4689	townofmoriahny.gov	1
4690	.federalreserveconsumerhelp.gov	1
4691	montana.gov	1
4692	.jacksoncounty-il.gov	1
4693	lovington-il.gov	1
4694	courtswv.gov	1
4695	usatradeonline.gov	1
4696	nysdhcr.gov	1
4697	.cityofbowmanga.gov	1
4698	registertovoteflorida.gov	1
4699	cns.gov	1
4700	metrokc.gov	1
4701	.lavernia-tx.gov	1
4702	colrain-ma.gov	1
4703	wmatc.gov	1
4704	.flleg.gov	1
4705	.warrencountyky.gov	1
4706	lasalle-il.gov	1
4707	mtshastaca.gov	1
4708	.ghana.gov	1
4709	.npas.gov	1
4710	.veterans.gov	1
4711	lantabus-pa.gov	1
4712	dhs.gov	1
4713	.walkercountyga.gov	1
4714	.westjeffersonohio.gov	1
4715	mypyramid.gov	1
4716	.donaldoregon.gov	1
4717	.hendersoncountytn.gov	1
4718	.eklutna-nsn.gov	1
4719	cantontwp-oh.gov	1
4720	bendoregon.gov	1
4721	.daniabeachfl.gov	1
4722	.evergladesrestoration.gov	1
4723	.nationalinstituteofcorrections.gov	1
4724	.wyomingofficeoftourism.gov	1
4725	.aztecnm.gov	1
4726	.knoxvilleia.gov	1
4727	.italladdsup.gov	1
4728	rochestermn.gov	1
4729	.chulavistca.gov	1
4730	borgertx.gov	1
4731	.wspmn.gov	1
4732	.nnlm.gov	1
4733	.federalreporting.gov	1
4734	.usaseanconnect.gov	1
4735	.cpars.gov	1
4736	.glennheightstx.gov	1
4737	ecorsemi.gov	1
4738	.dcsc.gov	1
4739	tupeloms.gov	1
4740	iowacore.gov	1
4741	.mcrmc.gov	1
4742	usap.gov	1
4743	newjersey.gov	1
4744	nhl.gov	1
4745	.scribner-ne.gov	1
4746	.townofpenningtonva.gov	1
4747	bacacountyco.gov	1
4748	oakhillwv.gov	1
4749	needhamma.gov	1
4750	.woodville-tx.gov	1
4751	.breckenridgetx.gov	1
4752	fredericksburgva.gov	1
4753	naplescityut.gov	1
4754	.ma.gov	1
4755	freeportflorida.gov	1
4756	.oklatourism.gov	1
4757	.atpa.gov	1
4758	.plymouthcountyma.gov	1
4759	.mansfieldga.gov	1
4760	.healthyforests.gov	1
4761	.blissfieldmichigan.gov	1
4762	rockwellnc.gov	1
4763	aikencountysc.gov	1
4764	gulfport-ms.gov	1
4765	.newtownohio.gov	1
4766	azcleanelections.gov	1
4767	bnl.gov	1
4768	.vernontx.gov	1
4769	.cityofhubbard-oh.gov	1
4770	.pleasantvalley-ny.gov	1
4771	.beckemeyeril.gov	1
4772	gardencity-ga.gov	1
4773	.stmaryspa.gov	1
4774	tuckerga.gov	1
4775	.townofnortheastny.gov	1
4776	.sunlandpark-nm.gov	1
4777	computersforlearning.gov	1
4778	fremontne.gov	1
4779	.toombscountyga.gov	1
4780	.wcnyh.gov	1
4781	morehead-ky.gov	1
4782	.marshfieldmo.gov	1
4783	.rockdalecountyga.gov	1
4784	.cityofpataskalaohio.gov	1
4785	.aftac.gov	1
4786	kannapolisnc.gov	1
4787	.njmvc.gov	1
4788	northforkrancheria-nsn.gov	1

4789 rows × 2 columns

WP:M .gov counts from externallinks table¶

limited to WP:M pages with external links

In [7]:

# Summary counts of top level domains
# limited to WP:M pages with external links
wpm_gov_query = """
SELECT 
    REGEXP_EXTRACT({},'(\.[^\.]+\.[^\.]+)$',1) as domain,
    COUNT(distinct el_from, el_to) AS num_links
FROM ryanmax.population_externallinks
WHERE 
    el_from IN (SELECT page_id FROM ryanmax.population_wpm_pages_with_extlinks)
    AND {} LIKE '%.gov'
GROUP BY domain
ORDER BY num_links DESC
"""
wpm_gov = spark.sql(wpm_gov_query.format(parse_host_from_el_to,parse_host_from_el_to))
wpm_gov.toPandas()

Out[7]:

	domain	num_links
0	.nih.gov	257219
1	.cdc.gov	7040
2	.fda.gov	4336
3	.loc.gov	3671
4	.cancer.gov	1569
5	medlineplus.gov	1072
6	.epa.gov	747
7	.hhs.gov	577
8	clinicaltrials.gov	466
9	.ahrq.gov	441
10	.usda.gov	376
11	.ca.gov	293
12	.gpo.gov	287
13	.medicaid.gov	270
14	.va.gov	246
15	.cms.gov	244
16	.congress.gov	237
17	.nasa.gov	227
18	.house.gov	203
19	.archives.gov	192
20	.clinicaltrials.gov	192
21	.drugabuse.gov	183
22	.usdoj.gov	179
23	.whitehouse.gov	177
24	.uspto.gov	157
25	.itis.gov	150
26	.hrsa.gov	146
27	.cia.gov	132
28	.osha.gov	131
29	.senate.gov	130
30	.samhsa.gov	126
31	.sec.gov	126
32	.womenshealth.gov	120
33	.cbo.gov	119
34	.justice.gov	114
35	.wa.gov	109
36	.bls.gov	105
37	.surgeongeneral.gov	101
38	.federalregister.gov	101
39	.genome.gov	99
40	.usaid.gov	97
41	.census.gov	91
42	.guideline.gov	91
43	.ny.gov	86
44	.ed.gov	85
45	.ncjrs.gov	84
46	.healthcare.gov	79
47	.gao.gov	78
48	.irs.gov	74
49	.usgs.gov	73
50	.oregon.gov	71
51	.nyc.gov	71
52	.fema.gov	71
53	cdc.gov	68
54	.uscourts.gov	67
55	.nps.gov	62
56	.state.gov	61
57	.nist.gov	59
58	.fbi.gov	56
59	.dot.gov	56
60	.ihs.gov	55
61	.faa.gov	54
62	.dc.gov	53
63	.ftc.gov	51
64	.medicare.gov	50
65	.healthit.gov	50
66	.nsf.gov	49
67	.ssa.gov	45
68	.mo.gov	42
69	.noaa.gov	41
70	.ornl.gov	41
71	.healthypeople.gov	41
72	.michigan.gov	38
73	.virginia.gov	37
74	.nrc.gov	36
75	.fcc.gov	35
76	.dea.gov	34
77	.osti.gov	34
78	.ars-grin.gov	33
79	.mass.gov	32
80	.health.gov	30
81	health.gov	30
82	.ct.gov	30
83	.utah.gov	29
84	.supremecourt.gov	28
85	.pepfar.gov	27
86	.lbl.gov	26
87	.wisconsin.gov	26
88	.maryland.gov	26
89	.ohio.gov	25
90	.lacounty.gov	24
91	.ems.gov	24
92	.usembassy.gov	24
93	.ky.gov	23
94	.in.gov	23
95	.bnl.gov	22
96	.pandemicflu.gov	22
97	.fdlp.gov	21
98	.childwelfare.gov	21
99	.regulations.gov	20
100	.dol.gov	20
101	.ntsb.gov	19
102	womenshealth.gov	19
103	.nhtsa.gov	19
104	.kingcounty.gov	19
105	.eeoc.gov	19
106	.azdhs.gov	19
107	.aids.gov	18
108	.ri.gov	18
109	.flu.gov	18
110	.energy.gov	18
111	.doe.gov	18
112	.medpac.gov	18
113	.choosemyplate.gov	18
114	.scdhec.gov	17
115	.govinfo.gov	17
116	.bjs.gov	17
117	.usphs.gov	17
118	.gpoaccess.gov	16
119	.maine.gov	16
120	.mentalhealthcommission.gov	15
121	.vermont.gov	15
122	.nysed.gov	15
123	.alaska.gov	15
124	.anl.gov	15
125	.delaware.gov	14
126	.treasury.gov	14
127	healthypeople.gov	13
128	.phe.gov	13
129	.nj.gov	13
130	.hiv.gov	13
131	.lanl.gov	13
132	locatorplus.gov	13
133	.sandiego.gov	13
134	.guidelines.gov	12
135	healthfinder.gov	12
136	.azsos.gov	12
137	.cpsc.gov	12
138	.psc.gov	12
139	.mn.gov	12
140	.wi.gov	12
141	.idaho.gov	11
142	.dhhs.gov	11
143	.hawaii.gov	11
144	.colorado.gov	11
145	.dhs.gov	11
146	.illinois.gov	11
147	.selectagents.gov	11
148	.phila.gov	10
149	.pa.gov	10
150	.vaccines.gov	10
151	.ready.gov	9
152	.georgia.gov	9
153	.mt.gov	9
154	.fws.gov	9
155	.defense.gov	9
156	.nlm.gov	9
157	.louisvilleky.gov	9
158	drugabuse.gov	9
159	.ecfr.gov	9
160	.4woman.gov	9
161	.mi.gov	9
162	.ok.gov	9
163	.ncdhhs.gov	9
164	.nh.gov	8
165	guideline.gov	8
166	bestbonesforever.gov	8
167	.louisiana.gov	8
168	.metrokc.gov	8
169	loc.gov	8
170	.opm.gov	8
171	.pmi.gov	8
172	healthvermont.gov	8
173	.whitehousedrugpolicy.gov	8
174	.kdheks.gov	7
175	epa.gov	7
176	cbo.gov	7
177	.csb.gov	7
178	cancer.gov	7
179	.gsa.gov	7
180	.tn.gov	7
181	.nara.gov	7
182	.treas.gov	7
183	.traviscountytx.gov	7
184	.organdonor.gov	7
185	.ne.gov	6
186	.mypyramid.gov	6
187	.arkansas.gov	6
188	.healthfinder.gov	6
189	.newjersey.gov	6
190	.miamidade.gov	6
191	.usa.gov	6
192	hawaii.gov	6
193	.sd.gov	6
194	.ks.gov	6
195	.ada.gov	6
196	.america.gov	6
197	.uscis.gov	6
198	.fbo.gov	6
199	.pubmedcentral.gov	6
200	.osac.gov	6
201	.msha.gov	6
202	.texas.gov	5
203	.newportbeachca.gov	5
204	.iowa.gov	5
205	organdonor.gov	5
206	.peacecorps.gov	5
207	aids.gov	5
208	.dni.gov	5
209	.milwaukee.gov	5
210	.aoa.gov	5
211	.ms.gov	5
212	.riversideca.gov	5
213	.seattle.gov	5
214	.neh.gov	5
215	.fjc.gov	5
216	.mentalhealth.gov	4
217	.nbii.gov	4
218	.nij.gov	4
219	.socialsecurity.gov	4
220	.la.gov	4
221	.ncd.gov	4
222	.hanford.gov	4
223	isotopes.gov	4
224	.tsa.gov	4
225	.arsusda.gov	4
226	.bop.gov	4
227	majorityleader.gov	4
228	.flsenate.gov	4
229	nihseniorhealth.gov	4
230	.nc.gov	4
231	.wv.gov	4
232	nj.gov	4
233	.nv.gov	4
234	.clinicaltrial.gov	4
235	hhs.gov	4
236	ttb.gov	4
237	senate.gov	3
238	.myfloridahouse.gov	3
239	.foodsafety.gov	3
240	.healthvermont.gov	3
241	.nationalchildrensstudy.gov	3
242	.usbr.gov	3
243	.malegislature.gov	3
244	.ttb.gov	3
245	.doleta.gov	3
246	.healthdata.gov	3
247	.commerce.gov	3
248	.transportation.gov	3
249	.wyo.gov	3
250	.fitness.gov	3
251	.floridahealth.gov	3
252	smokefree.gov	3
253	bioethics.gov	3
254	.eia.gov	3
255	surgeongeneral.gov	3
256	guidelines.gov	3
257	.ga.gov	3
258	.azleg.gov	3
259	.fec.gov	3
260	4women.gov	3
261	riversideca.gov	3
262	.az.gov	3
263	.uscirf.gov	3
264	austintexas.gov	3
265	healthit.gov	3
266	.oksenate.gov	3
267	.ilga.gov	3
268	.nysenate.gov	3
269	costamesaca.gov	3
270	malegislature.gov	3
271	nsf.gov	3
272	.llnl.gov	3
273	.hud.gov	3
274	.cecc.gov	3
275	.nitrd.gov	3
276	.arc.gov	2
277	.4women.gov	2
278	.montgomerycountymd.gov	2
279	.nsa.gov	2
280	.federalreserve.gov	2
281	.fordlibrarymuseum.gov	2
282	.usconsulate.gov	2
283	.wvlegislature.gov	2
284	.data.gov	2
285	.md.gov	2
286	.girlshealth.gov	2
287	.attorneygeneral.gov	2
288	.jct.gov	2
289	.ustr.gov	2
290	.citizencorps.gov	2
291	.nutrition.gov	2
292	.glin.gov	2
293	.fnal.gov	2
294	.ice.gov	2
295	genome.gov	2
296	.collegedrinkingprevention.gov	2
297	.tennessee.gov	2
298	nnlm.gov	2
299	.baltimorecity.gov	2
300	.usap.gov	2
301	.ncifcrf.gov	2
302	.azcc.gov	2
303	.baltimorecountymd.gov	2
304	.ar.gov	2
305	.marylandhealthconnection.gov	2
306	.oregonlegislature.gov	2
307	.nyhealth.gov	2
308	ngc.gov	2
309	.usmission.gov	2
310	.invasivespeciesinfo.gov	2
311	.ncdcr.gov	2
312	.acus.gov	2
313	.cwc.gov	2
314	nih.gov	2
315	usphs.gov	2
316	.sbir.gov	2
317	.usitc.gov	2
318	.omhrc.gov	2
319	.bioethics.gov	2
320	.cityofboston.gov	2
321	flu.gov	2
322	.orau.gov	2
323	.nola.gov	2
324	mn.gov	2
325	.healthindicators.gov	2
326	.safercar.gov	2
327	maine.gov	2
328	.stopmedicarefraud.gov	2
329	.bestbonesforever.gov	2
330	.gop.gov	2
331	gao.gov	2
332	.agingstats.gov	2
333	.nmlegis.gov	2
334	.oilspillcommission.gov	2
335	.ncagr.gov	2
336	.chemsafety.gov	2
337	vetsuccess.gov	2
338	.fiscalcommission.gov	2
339	sierranevadawild.gov	2
340	.vt.gov	2
341	.hayward-ca.gov	2
342	.sc.gov	2
343	healthcare.gov	2
344	.psn.gov	2
345	illinoisattorneygeneral.gov	1
346	usich.gov	1
347	.copyright.gov	1
348	.pubmed.gov	1
349	.boem.gov	1
350	.hcfa.gov	1
351	.pr.gov	1
352	.floridahealthfinder.gov	1
353	.distraction.gov	1
354	.recoverymonth.gov	1
355	.consumer.gov	1
356	.nrd.gov	1
357	.cap.gov	1
358	.nea.gov	1
359	flsenate.gov	1
360	.fedcenter.gov	1
361	.nebraska.gov	1
362	dni.gov	1
363	njconsumeraffairs.gov	1
364	.letsmove.gov	1
365	.morgantownwv.gov	1
366	clermontcountyohio.gov	1
367	michigan.gov	1
368	.ohioattorneygeneral.gov	1
369	.sandiegocounty.gov	1
370	.change.gov	1
371	.costamesaca.gov	1
372	.azmd.gov	1
373	.wvdnr.gov	1
374	.williamsoncountyil.gov	1
375	.neglecteddiseases.gov	1
376	.jimmycarterlibrary.gov	1
377	.cavc.gov	1
378	.nro.gov	1
379	.juvenilecouncil.gov	1
380	.pnl.gov	1
381	.fido.gov	1
382	cityofboston.gov	1
383	.eldercare.gov	1
384	ojp.gov	1
385	.ojjdp.gov	1
386	change.gov	1
387	.nationalservice.gov	1
388	.houstontx.gov	1
389	osha.gov	1
390	.recovery.gov	1
391	.plainlanguage.gov	1
392	.challenge.gov	1
393	.scstatehouse.gov	1
394	energy.gov	1
395	.austintexas.gov	1
396	.crimesolutions.gov	1
397	.ohioagriculture.gov	1
398	nist.gov	1
399	.boston.gov	1
400	crimesolutions.gov	1
401	.burlingtonvt.gov	1
402	.ghi.gov	1
403	.somervillema.gov	1
404	.alabama.gov	1
405	.ovc.gov	1
406	.illinoisattorneygeneral.gov	1
407	.us-cert.gov	1
408	hrsa.gov	1
409	.ohioresponds.gov	1
410	.access-board.gov	1
411	.ncdps.gov	1
412	.ilsos.gov	1
413	.roanokeva.gov	1
414	.benefits.gov	1
415	pubmed.gov	1
416	outdoornebraska.gov	1
417	.hcqualitycommission.gov	1
418	.ngc.gov	1
419	.ussc.gov	1
420	.doc.gov	1
421	.visitthecapitol.gov	1
422	.srpmic-nsn.gov	1
423	.dna.gov	1
424	.lymenh.gov	1
425	.cabq.gov	1
426	.atf.gov	1
427	.usaspending.gov	1
428	.pcip.gov	1
429	.dconc.gov	1
430	.adf.gov	1
431	.jccbi.gov	1
432	kentucky.gov	1
433	azdhs.gov	1
434	phila.gov	1
435	.samsha.gov	1
436	.usccr.gov	1
437	azleg.gov	1
438	.sba.gov	1
439	.insurekidsnow.gov	1
440	.stpaul.gov	1
441	.pnnl.gov	1
442	census.gov	1
443	.globalhealth.gov	1
444	udall.gov	1
445	.pearlandtx.gov	1
446	.bja.gov	1
447	.alpinecountyca.gov	1
448	.alabamapublichealth.gov	1
449	.greenvillenc.gov	1
450	ems.gov	1
451	.cahwnet.gov	1
452	medpac.gov	1
453	.who.gov	1
454	.cstx.gov	1
455	.ntis.gov	1
456	azsos.gov	1
457	.staffordtx.gov	1
458	.section508.gov	1
459	childwelfare.gov	1
460	.medlineplus.gov	1
461	pandemicflu.gov	1
462	.ohiopmp.gov	1
463	mdcourts.gov	1
464	.scdhhs.gov	1
465	biometrics.gov	1
466	.quic.gov	1
467	.stopfraudcolorado.gov	1
468	federalregister.gov	1
469	.usps.gov	1
470	.howardcountymd.gov	1
471	section508.gov	1
472	.hearttruth.gov	1
473	.sno-nsn.gov	1
474	.txcourts.gov	1
475	.usich.gov	1
476	.expectmore.gov	1
477	.usdol.gov	1
478	myloc.gov	1
479	.longbeach.gov	1
480	.azauditor.gov	1
481	.inl.gov	1
482	.sanantonio.gov	1
483	.nebraskalegislature.gov	1
484	oregon.gov	1

Top Level Domain Event Data¶

Event counts for top level domains by event type¶

limited to W pages with external links
limited to >= 1000 events

In [8]:

# Event counts for top level domains by event type
# limited to W pages with external links
w_tld_query = """
SELECT REGEXP_EXTRACT({},'(\.[^\.]+)$',1) as tld, action, COUNT(*) AS count 
FROM citationusage 
WHERE wiki = 'enwiki'
AND page_id IN 
    (SELECT page_id FROM ryanmax.population_w_pages_with_extlinks)
{}
AND to_date(event_time) >= '{}'
AND to_date(event_time) <= '{}'
AND useragent_is_bot = FALSE
GROUP BY tld, action
ORDER BY COUNT(*) DESC
"""
w_tld_events = spark.sql(
    w_tld_query.format(
        parse_host_from_link_url,
        event_exclusion_sql, start_date_string, end_date_string
    ))
w_tld_events_rdd = w_tld_events.rdd
w_tld_events_df = sqlContext.createDataFrame(w_tld_events_rdd)
w_tld_events_pandas = w_tld_events_df.toPandas()

In [9]:

tld_pda = w_tld_events_pandas.copy()
parsing_errors = tld_pda.loc[(tld_pda['tld'] == '') | (tld_pda['tld'].isnull())]
count_parsing_errors = parsing_errors['count'].sum()
total = tld_pda['count'].sum()
display(Markdown("Could not parse hostname/domain from {0} link_urls (first two rows) which represents {1:.2%} of all values.".format(count_parsing_errors,count_parsing_errors/total)))

# limit to counts of 1K or more
df_filtered = tld_pda.query('count>1000').copy()
# set precision before pivot
df_filtered['count'] = df_filtered['count'].map(lambda x: '{0:.0f}'.format(x))
df_filtered.pivot(index='tld', columns='action', values='count')

Could not parse hostname/domain from 74917 link_urls (first two rows) which represents 0.07% of all values.

Out[9]:

action	extClick	fnClick	fnHover	upClick
tld
NaN	21792	22848	27224	None
	2074	None	None	None
.122	4480	None	None	None
.132	3450	None	None	None
.173	3321	None	None	None
.194	2610	None	None	None
.208	2602	None	None	None
.27	4104	None	None	None
.5	1071	None	None	None
.ac	2047	None	None	None
.ad	1456	None	None	None
.ae	30891	None	None	None
.aero	20623	None	None	None
.af	3455	None	None	None
.ai	5139	None	None	None
.al	7450	None	None	None
.am	7781	None	None	None
.ao	1434	None	None	None
.app	1718	None	None	None
.ar	24965	None	None	None
.asia	3249	None	None	None
.at	49233	None	None	None
.au	639031	None	None	None
.az	7928	None	None	None
.ba	6667	None	None	None
.band	1485	None	None	None
.basketball	6781	None	None	None
.bd	30033	None	None	None
.be	55951	None	None	None
.bg	11467	None	None	None
.bh	2812	None	None	None
.biz	16942	None	None	None
.blog	1236	None	None	None
.bm	1202	None	None	None
.bn	4731	None	None	None
.bo	1658	None	None	None
.br	56845	None	None	None
.bt	2847	None	None	None
.bw	2187	None	None	None
.by	7683	None	None	None
.bz	1744	None	None	None
.ca	531791	None	None	None
.canon	1149	None	None	None
.cat	12627	None	None	None
.cc	14172	None	None	None
.cd	1050	None	None	None
.ch	103424	None	None	None
.church	1577	None	None	None
.cl	12667	None	None	None
.club	5516	None	None	None
.cn	134545	None	None	None
.co	58620	None	None	None
.com	28766412	48469	72195	1064
.company	1133	None	None	None
.coop	3443	None	None	None
.cr	1782	None	None	None
.cu	4020	None	None	None
.cx	2777	None	None	None
.cy	5950	None	None	None
.cymru	1173	None	None	None
.cz	43883	None	None	None
.de	363660	None	None	None
.design	2637	None	None	None
.dev	8484	None	None	None
.dk	56814	None	None	None
.do	2772	None	None	None
.dz	3559	None	None	None
.earth	1381	None	None	None
.ec	103453	None	None	None
.edu	1467353	None	None	None
.ee	13676	None	None	None
.eg	10372	None	None	None
.es	70851	None	None	None
.et	6480	None	None	None
.eu	143356	None	None	None
.eus	2350	None	None	None
.fi	45985	None	None	None
.fj	1652	None	None	None
.fm	11396	None	None	None
.fo	3466	None	None	None
.foundation	1078	None	None	None
.fr	220212	None	None	None
.ga	5764	None	None	None
.ge	7977	None	None	None
.gg	3122	None	None	None
.gh	7884	None	None	None
.gi	1286	None	None	None
.gl	4818	None	None	None
.global	2440	None	None	None
.google	1884	None	None	None
.gov	1247591	None	None	None
.gr	44695	None	None	None
.group	7938	None	None	None
.gt	1447	None	None	None
.hk	39828	None	None	None
.hn	1205	None	None	None
.hockey	4308	None	None	None
.hr	18834	None	None	None
.hu	30829	None	None	None
.id	24768	None	None	None
.ie	114385	None	None	None
.il	63303	None	None	None
.im	3749	None	None	None
.in	999248	None	None	None
.info	158173	None	None	None
.int	57368	None	None	None
.io	89879	None	None	None
.iq	3358	None	None	None
.ir	18890	None	None	None
.is	113277	None	None	None
.it	184562	None	None	None
.jm	2092	None	None	None
.jo	4386	None	None	None
.jp	284947	None	None	None
.ke	12322	None	None	None
.kg	1665	None	None	None
.kh	2911	None	None	None
.kp	2543	None	None	None
.kpmg	1021	None	None	None
.kr	82184	None	None	None
.kw	3983	None	None	None
.ky	1382	None	None	None
.kz	8188	None	None	None
.la	2325	None	None	None
.lb	5291	None	None	None
.li	4106	None	None	None
.life	1950	None	None	None
.live	1607	None	None	None
.lk	29139	None	None	None
.london	1609	None	None	None
.lt	11414	None	None	None
.lu	8334	None	None	None
.lv	8623	None	None	None
.ly	5341	None	None	None
.ma	6002	None	None	None
.mc	1237	None	None	None
.md	4370	None	None	None
.me	25845	None	None	None
.media	2526	None	None	None
.mil	139913	None	None	None
.mk	7439	None	None	None
.mm	4225	None	None	None
.mn	3974	None	None	None
.mo	2118	None	None	None
.movie	5304	None	None	None
.ms	1601	None	None	None
.mt	5360	None	None	None
.mu	3165	None	None	None
.museum	2881	None	None	None
.mv	2097	None	None	None
.mx	43933	None	None	None
.my	72376	None	None	None
.mz	1217	None	None	None
.na	3026	None	None	None
.name	4507	None	None	None
.net	1069963	None	1578	None
.news	3673	None	None	None
.ng	22097	None	None	None
.ni	1124	None	None	None
.nl	118028	None	None	None
.no	63161	None	None	None
.np	21760	None	None	None
.nu	4502	None	None	None
.nyc	2235	None	None	None
.nz	126910	None	None	None
.om	6502	None	None	None
.online	2552	None	None	None
.org	6708760	23036327	35825166	1109571
.pa	1665	None	None	None
.party	1252	None	None	None
.pe	8242	None	None	None
.pg	1549	None	None	None
.ph	82864	None	None	None
.pk	86222	None	None	None
.pl	72733	None	None	None
.pr	1387	None	None	None
.pro	1002	None	None	None
.ps	2258	None	None	None
.pt	106694	None	None	None
.pw	14387	None	None	None
.py	1781	None	None	None
.qa	6712	None	None	None
.report	4014	None	None	None
.ro	32895	None	None	None
.rs	21969	None	None	None
.ru	146320	None	None	None
.rw	2501	None	None	None
.sa	18170	None	None	None
.scot	7222	None	None	None
.sd	1945	None	None	None
.se	99000	None	None	None
.sg	59667	None	None	None
.sh	4732	None	None	None
.si	15588	None	None	None
.sk	17903	None	None	None
.sm	1026	None	None	None
.space	2111	None	None	None
.sport	1078	None	None	None
.su	2733	None	None	None
.sv	1011	None	None	None
.sy	1809	None	None	None
.technology	1085	None	None	None
.th	21965	None	None	None
.tj	1135	None	None	None
.tk	1568	None	None	None
.tl	1828	None	None	None
.tm	1628	None	None	None
.tn	2573	None	None	None
.to	73995	None	None	None
.today	10079	None	None	None
.tr	48292	None	None	None
.travel	2258	None	None	None
.tt	2153	None	None	None
.tv	108570	None	None	None
.tw	63867	None	None	None
.tz	9041	None	None	None
.ua	35800	None	None	None
.ug	7801	None	None	None
.uk	2553969	None	None	None
.us	224854	None	None	None
.uy	3587	None	None	None
.uz	3264	None	None	None
.va	13992	None	None	None
.ve	5778	None	None	None
.vn	18353	None	None	None
.wales	3228	None	None	None
.website	2200	None	None	None
.world	2962	None	None	None
.ws	15411	None	None	None
.xxx	5503	None	None	None
.xyz	5895	None	None	None
.za	86343	None	None	None
.zm	1636	None	None	None
.zw	4103	None	None	None

WP:M event counts for top level domains by event type¶

limited to WP:M pages with external links
limited to >= 100 events

In [10]:

# Event counts for top level domains by event type
# limited to WP:M pages with external links
wpm_tld_query = """
SELECT REGEXP_EXTRACT({},'(\.[^\.]+)$',1) as tld, action, COUNT(*) AS count 
FROM citationusage 
WHERE wiki = 'enwiki'
AND page_id IN 
    (SELECT page_id FROM ryanmax.population_wpm_pages_with_extlinks)
{}
AND to_date(event_time) >= '{}'
AND to_date(event_time) <= '{}'
AND useragent_is_bot = FALSE
GROUP BY tld, action
ORDER BY COUNT(*) DESC
"""
wpm_tld_events = spark.sql(
    wpm_tld_query.format(
        parse_host_from_link_url,
        event_exclusion_sql, start_date_string, end_date_string
    ))
wpm_tld_events_rdd = wpm_tld_events.rdd
wpm_tld_events_df = sqlContext.createDataFrame(wpm_tld_events_rdd)
wpm_tld_events_pandas = wpm_tld_events_df.toPandas()

In [11]:

tld_pda = wpm_tld_events_pandas.copy()
parsing_errors = tld_pda.loc[(tld_pda['tld'] == '') | (tld_pda['tld'].isnull())]
count_parsing_errors = parsing_errors['count'].sum()
total = tld_pda['count'].sum()
display(Markdown("Could not parse hostname/domain from {0} link_urls (first row) which represents {1:.2%} of all values.".format(count_parsing_errors,count_parsing_errors/total)))

# limit to counts of 100 or more
df_filtered = tld_pda.query('count>100').copy()
# set precision before pivot
df_filtered['count'] = df_filtered['count'].map(lambda x: '{0:.0f}'.format(x))
df_filtered.pivot(index='tld', columns='action', values='count')

Could not parse hostname/domain from 1372 link_urls (first row) which represents 0.04% of all values.

Out[11]:

action	extClick	fnClick	fnHover	upClick
tld
NaN	168	412	698	None
.ag	120	None	None	None
.al	170	None	None	None
.am	139	None	None	None
.at	263	None	None	None
.au	7959	None	None	None
.be	532	None	None	None
.br	460	None	None	None
.ca	14045	None	None	None
.ch	2739	None	None	None
.cn	1212	None	None	None
.co	334	None	None	None
.com	267229	3213	4246	None
.cz	223	None	None	None
.de	2849	None	None	None
.dk	387	None	None	None
.edu	41718	None	None	None
.es	315	None	None	None
.eu	3792	None	None	None
.fi	232	None	None	None
.fr	1850	None	None	None
.gov	165336	None	None	None
.hk	240	None	None	None
.hr	139	None	None	None
.hu	202	None	None	None
.ie	992	None	None	None
.il	286	None	None	None
.in	3641	None	None	None
.info	2811	None	None	None
.int	24312	None	None	None
.io	350	None	None	None
.ir	251	None	None	None
.is	1471	None	None	None
.it	1947	None	None	None
.jp	1875	None	None	None
.kr	196	None	None	None
.kz	103	None	None	None
.mil	1133	None	None	None
.mp	308	None	None	None
.mx	166	None	None	None
.my	219	None	None	None
.net	8439	None	120	None
.nl	934	None	None	None
.no	2100	None	None	None
.nz	992	None	None	None
.org	212378	883997	1554886	49116
.ph	228	None	None	None
.pk	344	None	None	None
.pl	1223	None	None	None
.pt	149	None	None	None
.ro	232	None	None	None
.ru	741	None	None	None
.se	906	None	None	None
.sg	509	None	None	None
.today	528	None	None	None
.tw	282	None	None	None
.ua	307	None	None	None
.ug	111	None	None	None
.uk	37474	None	None	None
.us	1420	None	None	None
.za	507	None	None	None

.gov Domain Event Data¶

W event counts for .gov top level domain by event type¶

limited to W pages with external links
Only extClick events found

In [12]:

# Event counts for .gov top level domain by event type
# limited to W pages with external links
w_gov_query = """
SELECT REGEXP_EXTRACT({},'(\.[^\.]+\.[^\.]+)$',1) as domain, action, COUNT(*) AS count 
FROM citationusage 
WHERE wiki = 'enwiki'
AND page_id IN 
    (SELECT page_id FROM ryanmax.population_w_pages_with_extlinks)
{}
AND to_date(event_time) >= '{}'
AND to_date(event_time) <= '{}'
AND useragent_is_bot = FALSE
AND REGEXP_EXTRACT({},'(\.[^\.]+)$',1) = '.gov'
GROUP BY domain, action
ORDER BY COUNT(*) DESC
"""
w_gov_events = spark.sql(
    w_gov_query.format(
        parse_host_from_link_url,
        event_exclusion_sql, start_date_string, end_date_string,
        parse_host_from_link_url
    ))
w_gov_events_rdd = w_gov_events.rdd
w_gov_events_df = sqlContext.createDataFrame(w_gov_events_rdd)
w_gov_events_pandas = w_gov_events_df.toPandas()

In [13]:

tld_pda = w_gov_events_pandas.copy()
tld_pda.sort_values(by=['count'],ascending=False)[['domain','count']]

Out[13]:

	domain	count
0	.nih.gov	187352
1	.nps.gov	71874
2	.nasa.gov	61757
3	.loc.gov	46585
4	.census.gov	36855
5	.usgs.gov	34132
6	.ca.gov	30849
7	.house.gov	29518
8	.congress.gov	23638
9	.usda.gov	23079
10	.cia.gov	19587
11	.noaa.gov	19268
12	.state.gov	19064
13	.nist.gov	18483
14	.sec.gov	16488
15	.senate.gov	16228
16	.ed.gov	15778
17	.gpo.gov	14966
18	.epa.gov	13277
19	.archives.gov	12975
20	.fbi.gov	12918
21	.faa.gov	12781
22	.fcc.gov	12495
23	.fda.gov	10912
24	.irs.gov	10733
25	.whitehouse.gov	9971
26	.nyc.gov	9763
27	.justice.gov	9561
28	.cdc.gov	8898
29	.ny.gov	8116
30	.bop.gov	7238
31	.fws.gov	6558
32	.maryland.gov	6364
33	.dot.gov	6112
34	.wa.gov	5630
35	.virginia.gov	5475
36	.uscourts.gov	5282
37	.in.gov	4941
38	.ecfr.gov	4923
39	.supremecourt.gov	4800
40	.defense.gov	4731
41	.uspto.gov	4586
42	.ntsb.gov	4556
43	.dhs.gov	4473
44	.fjc.gov	4460
45	.ars-grin.gov	4333
46	.mass.gov	4167
47	.namus.gov	3930
48	.ky.gov	3850
49	.eia.gov	3840
50	.itis.gov	3745
51	.blm.gov	3739
52	.bls.gov	3736
53	loc.gov	3715
54	.texas.gov	3629
55	.ohio.gov	3593
56	.lbl.gov	3313
57	.energy.gov	3307
58	.usembassy.gov	3285
59	.lacounty.gov	3087
60	.ct.gov	3067
61	.cbp.gov	3066
62	.mo.gov	3065
63	.va.gov	3019
64	.usdoj.gov	3017
65	.utah.gov	2926
66	.doe.gov	2905
67	.sc.gov	2758
68	.federalreserve.gov	2650
69	.ssa.gov	2627
70	.nga.gov	2523
71	.dol.gov	2425
72	.fec.gov	2399
73	.treasury.gov	2387
74	.oregon.gov	2361
75	.uscis.gov	2306
76	.osha.gov	2301
77	.ftc.gov	2192
78	.wi.gov	2190
79	.michigan.gov	2185
80	.illinois.gov	2135
81	southbendin.gov	1955
82	.gao.gov	1947
83	.nsf.gov	1935
84	.federalregister.gov	1789
85	.ornl.gov	1783
86	.pa.gov	1690
87	.weather.gov	1652
88	.tn.gov	1638
89	.nv.gov	1630
90	.bea.gov	1619
91	.maine.gov	1609
92	.hud.gov	1524
93	.usbr.gov	1510
94	.nsa.gov	1491
95	.nrel.gov	1477
96	.hhs.gov	1473
97	.idaho.gov	1468
98	.mt.gov	1461
99	.ok.gov	1452
100	.colorado.gov	1435
101	.ncdcr.gov	1404
102	.ilga.gov	1380
103	.opm.gov	1380
104	.govinfo.gov	1380
105	.fema.gov	1337
106	.delaware.gov	1328
107	.lanl.gov	1297
108	.nj.gov	1285
109	.ohiodnr.gov	1282
110	.osti.gov	1269
111	.cancer.gov	1253
112	.anl.gov	1252
113	.usaid.gov	1234
114	.ncjrs.gov	1213
115	.wicourts.gov	1210
116	.seattle.gov	1197
117	energy.gov	1194
118	.hawaii.gov	1173
119	.sss.gov	1166
120	.arkansas.gov	1159
121	justice.gov	1121
122	.sandia.gov	1114
123	.nhtsa.gov	1109
124	.nysenate.gov	1109
125	.ga.gov	1108
126	.wisconsin.gov	1102
127	.eeoc.gov	1090
128	.miamidade.gov	1088
129	.nrc.gov	1082
130	.nd.gov	1081
131	whitehouse.gov	1067
132	.cbo.gov	1046
133	.gpoaccess.gov	1033
134	.nixonlibrary.gov	1031
135	.bts.gov	1030
136	.dc.gov	1025
137	.sba.gov	1004
138	.doc.gov	993
139	clinicaltrials.gov	980
140	.gsa.gov	980
141	.fdic.gov	974
142	.usa.gov	972
143	.ncparks.gov	954
144	.neh.gov	934
145	.bjs.gov	931
146	.az.gov	920
147	.panynj.gov	914
148	.usmint.gov	909
149	.llnl.gov	886
150	.sandiego.gov	877
151	.ice.gov	865
152	.alaska.gov	826
153	hawaii.gov	816
154	.nh.gov	809
155	.azgfd.gov	796
156	.ms.gov	777
157	.alabama.gov	777
158	.sd.gov	763
159	.fnal.gov	754
160	.ourdocuments.gov	750
161	.atf.gov	742
162	.nysed.gov	742
163	.kingcounty.gov	726
164	.dni.gov	718
165	.austintexas.gov	717
166	.iowa.gov	706
167	.georgia.gov	700
168	.ri.gov	691
169	.peacecorps.gov	685
170	.bldrdoc.gov	681
171	.tva.gov	680
172	.fdlp.gov	669
173	.secretservice.gov	666
174	.usmarshals.gov	662
175	.bnl.gov	661
176	.doi.gov	657
177	.vermont.gov	653
178	.louisiana.gov	648
179	.ffiec.gov	622
180	.cityofboston.gov	622
181	.fairfaxcounty.gov	620
182	.azleg.gov	613
183	.nwcg.gov	605
184	.mn.gov	603
185	.mi.gov	597
186	fbi.gov	597
187	.consumerfinance.gov	584
188	.pr.gov	570
189	.iowadnr.gov	568
191	.treas.gov	566
190	.myfloridahouse.gov	566
192	.nationalservice.gov	564
193	.ne.gov	560
194	.fueleconomy.gov	560
195	.md.gov	556
196	.cpsc.gov	556
197	.txcourts.gov	535
198	.dea.gov	534
199	.ntis.gov	532
200	.tennessee.gov	526
201	dhs.gov	524
202	.copyright.gov	520
203	.aoc.gov	518
204	medlineplus.gov	517
205	.houstontx.gov	516
206	.phila.gov	511
207	.nycourts.gov	497
208	census.gov	495
209	michigan.gov	485
210	.nc.gov	476
211	.gps.gov	473
212	.baltimorecity.gov	466
213	.fincen.gov	461
214	wisconsindot.gov	459
215	federalreserve.gov	458
216	.nationalmap.gov	457
217	.clarkcountynv.gov	452
218	.uscg.gov	442
219	.nashville.gov	439
220	.inl.gov	435
221	.treasurydirect.gov	431
222	.cms.gov	430
223	.nifc.gov	428
224	.abmc.gov	428
225	miramarfl.gov	426
226	.us-cert.gov	425
227	.samhsa.gov	423
228	.la.gov	422
229	texas.gov	413
230	.azdot.gov	401
231	.wv.gov	400
232	outdoornebraska.gov	395
233	.donotcall.gov	383
234	.bia.gov	380
235	.phoenix.gov	380
236	.nebraska.gov	380
237	malegislature.gov	380
238	.cabq.gov	379
239	.honolulu.gov	378
240	.transportation.gov	366
241	.ks.gov	364
242	.nola.gov	359
243	flsenate.gov	359
244	ustr.gov	356
245	.commerce.gov	350
246	.nara.gov	349
247	.detroitmi.gov	344
248	.ncdot.gov	343
249	.doleta.gov	340
250	.export.gov	340
251	.clinicaltrials.gov	339
252	ilga.gov	337
253	.scstatehouse.gov	337
254	.centennialofflight.gov	336
255	.malegislature.gov	333
256	.occ.gov	331
257	.indy.gov	330
258	.sanantonio.gov	329
259	.ferc.gov	328
260	.invasivespeciesinfo.gov	322
261	.flsenate.gov	318
262	.bart.gov	313
263	.usps.gov	308
264	.9-11commission.gov	306
265	.recreation.gov	306
266	.milwaukee.gov	306
267	.cftc.gov	306
268	.portlandoregon.gov	300
269	.tsa.gov	296
270	.usitc.gov	293
271	.txdot.gov	292
272	.genome.gov	289
273	.childwelfare.gov	288
274	.raleighnc.gov	285
275	.ussc.gov	285
276	.pnnl.gov	284
277	charlottenc.gov	284
278	mn.gov	280
279	.energystar.gov	272
280	.nmlegis.gov	272
281	.navajo-nsn.gov	271
282	georgia.gov	269
283	.guam.gov	266
284	.sanjoseca.gov	264
285	nj.gov	262
288	nasa.gov	261
286	speaker.gov	261
287	.moneyfactory.gov	261
289	sanantonio.gov	260
290	.fbo.gov	254
291	.oregonlegislature.gov	252
292	.sam.gov	251
293	moneyfactory.gov	249
294	epa.gov	248
295	.regulations.gov	245
296	nyc.gov	243
297	.southbendin.gov	242
298	.csb.gov	241
299	.hrsa.gov	239
300	.cincinnati-oh.gov	237
301	.atlantaga.gov	237
302	.america.gov	235
303	.digitalpreservation.gov	233
304	.speaker.gov	230
305	.globalchange.gov	228
306	.erie.gov	227
307	.plano.gov	224
308	usaid.gov	224
309	copyright.gov	223
310	.data.gov	221
311	.reaganlibrary.gov	220
312	.nro.gov	220
313	.safercar.gov	218
314	.monroecounty.gov	217
315	.pima.gov	217
316	.ustr.gov	213
318	stlouis-mo.gov	210
317	dc.gov	210
319	supremecourt.gov	209
320	.usap.gov	208
321	.uspis.gov	207
322	.longbeach.gov	205
323	.ncdps.gov	203
324	.prescott-az.gov	203
325	.traviscountytx.gov	202
326	.msha.gov	201
327	.ncua.gov	201
328	phoenix.gov	201
329	.deldot.gov	198
330	.fresno.gov	197
331	.azcorrections.gov	196
332	.pppl.gov	195
333	nhtsa.gov	194
334	.fletc.gov	193
335	.louisvilleky.gov	193
336	.bpa.gov	192
337	.cnss.gov	191
338	coloradosprings.gov	191
339	kcmo.gov	191
340	.loudoun.gov	190
341	.sbcounty.gov	190
342	.stlouis-mo.gov	188
343	.jobcorps.gov	188
344	.socialsecurity.gov	186
345	.osc.gov	186
346	.americaslibrary.gov	186
347	.usmission.gov	184
348	.montgomerycountymd.gov	184
350	.trade.gov	183
349	.elpasotexas.gov	183
351	.arts.gov	181
352	.nationalatlas.gov	180
353	.intelink.gov	180
354	.columbus.gov	179
355	.ehawaii.gov	177
356	.ttb.gov	174
357	.usajobs.gov	174
358	ntsb.gov	173
359	.roundrocktexas.gov	173
360	.augustaga.gov	172
361	.pnl.gov	171
362	.usconsulate.gov	170
363	.newmexico.gov	168
364	.howardcountymd.gov	168
365	.capitol.gov	167
366	.jimmycarterlibrary.gov	166
367	.uscc.gov	164
368	.wvdnr.gov	164
369	.ohioattorneygeneral.gov	159
370	.kansas.gov	158
371	.clarkstonga.gov	158
372	.cambridgema.gov	157
373	.nassaucountyny.gov	155
374	arts.gov	155
375	.ada.gov	155
376	.tsp.gov	154
377	.okc.gov	153
378	.iowadot.gov	152
379	.fordlibrarymuseum.gov	151
380	.charleston-sc.gov	149
381	.maricopa.gov	149
382	.boston.gov	148
383	.suffolkcountyny.gov	147
384	archives.gov	147
385	.eugene-or.gov	146
386	.ameslab.gov	145
387	usmint.gov	143
388	cdc.gov	143
389	.sedonaaz.gov	143
390	.ins.gov	142
391	.brevardfl.gov	142
392	.baltimorecountymd.gov	142
393	.minneapolismn.gov	140
394	nationalmap.gov	139
395	pittsburghpa.gov	139
396	.ncifcrf.gov	139
397	.tucsonaz.gov	138
399	.rrb.gov	137
398	.lasvegasnevada.gov	137
400	.flhsmv.gov	136
401	lasvegasnevada.gov	136
402	.nlrb.gov	136
403	.palmspringsca.gov	133
404	.santafenm.gov	132
405	.alexandriava.gov	131
406	.dccourts.gov	130
407	.cap.gov	129
408	.missouri.gov	129
409	.pbgc.gov	128
410	.vi.gov	128
411	.fdot.gov	128
414	opm.gov	127
415	.fpds.gov	127
413	virginiageneralassembly.gov	127
412	federalregister.gov	127
416	.stpaul.gov	125
417	tsa.gov	124
418	.mms.gov	124
419	.il.gov	123
420	.ashevillenc.gov	123
423	.iad.gov	122
424	.memphistn.gov	122
422	.harriscountytx.gov	122
421	.birminghamal.gov	122
425	.fishwatch.gov	121
426	alaska.gov	121
427	.id.gov	120
428	.wichita.gov	120
429	louisvilleky.gov	119
430	nist.gov	119
431	.gsaadvantage.gov	118
432	.ohiohouse.gov	117
433	.nij.gov	117
434	.nypa.gov	117
435	lanl.gov	117
436	.tncourts.gov	117
437	wyoming.gov	116
438	.santabarbaraca.gov	116
439	.roanokeva.gov	115
440	.choosemyplate.gov	115
441	.azsos.gov	115
442	fincen.gov	114
443	.cityofkeywest-fl.gov	114
444	.climate.gov	114
450	.airnow.gov	113
448	nyassembly.gov	113
449	.opic.gov	113
446	.acquisition.gov	113
447	.americansamoa.gov	113
445	.flhealth.gov	113
451	.tempe.gov	112
452	.ready.gov	112
453	atlantaga.gov	111
454	.fhfa.gov	110
455	.iowacourts.gov	110
456	.drugabuse.gov	110
460	.time.gov	109
461	ftc.gov	109
458	.coloradosprings.gov	109
459	acquisition.gov	109
457	.nsopr.gov	109
462	.chattanooga.gov	108
463	maine.gov	108
464	.ic3.gov	108
467	bea.gov	107
468	.norfolk.gov	107
465	ipcc-wg2.gov	107
466	kingcounty.gov	107
469	.mcc.gov	106
470	.americorps.gov	106
471	colorado.gov	105
472	.pittsburghpa.gov	105
473	aviationweather.gov	105
474	.uscirf.gov	105
475	.cookcountyil.gov	105
476	.usccr.gov	105
477	.ustreas.gov	104
478	.codot.gov	104
482	.usagm.gov	103
483	.miamibeachfl.gov	103
479	.goes-r.gov	103
481	.osac.gov	103
480	.cdfifund.gov	103
484	.exim.gov	102
485	.schohariecounty-ny.gov	102
486	.unicor.gov	102
487	.usability.gov	102
488	.medicare.gov	101
489	.savannahga.gov	101
490	.aqmd.gov	100
491	.nea.gov	100
492	.springfieldmo.gov	100
493	.scottsdaleaz.gov	100
496	.acl.gov	99
495	.aoa.gov	99
494	gatlinburgtn.gov	99
497	.attorneygeneral.gov	98
498	.sandiegocounty.gov	98
499	.greenvillenc.gov	97
...	...	...
3566	usembassy-mexico.gov	2
3564	.lavernia-tx.gov	2
3533	.fairviewnc.gov	2
3563	.buffalony.gov	2
3534	.galvestontx.gov	2
3535	cantonohio.gov	2
3536	manchesternh.gov	2
3537	.westamptonnj.gov	2
3538	andoverma.gov	2
3539	.gocc.gov	2
3540	.sunnyside-wa.gov	2
3541	cranberryisles-me.gov	2
3542	pcah.gov	2
3543	.middletonnh.gov	2
3544	.padillabay.gov	2
3545	flauditor.gov	2
3546	.sialkot.gov	2
3547	.buildingamerica.gov	2
3548	.twpoceannj.gov	2
3549	.takepride.gov	2
3550	.webstercountymo.gov	2
3551	.morningsidemd.gov	2
3552	iowaculture.gov	2
3553	redcliff-nsn.gov	2
3554	.northsiouxcity-sd.gov	2
3555	globalchange.gov	2
3556	pompanobeachfl.gov	2
3557	.rosebudcountymt.gov	2
3558	remember.gov	2
3559	.nuclear.gov	2
3560	.ridgefieldnj.gov	2
3561	.smartcheck.gov	2
3562	presidentialinnovationfellows.gov	2
3467	.esc.gov	2
3484	.milanohio.gov	2
3465	.townofvassnc.gov	2
3368	.consumer.gov	2
3370	.nhl.gov	2
3371	gpo.gov	2
3372	.app.gov	2
3373	oceancommission.gov	2
3374	.florence-nj.gov	2
3375	.newjersey.gov	2
3376	dublinohiousa.gov	2
3377	.azland.gov	2
3378	montgomerycountyga.gov	2
3379	franklincountyga.gov	2
3380	nepa.gov	2
3381	.highlandil.gov	2
3382	columbianaohio.gov	2
3383	.greencastlepa.gov	2
3384	.upto.gov	2
3385	woodlandhills-ut.gov	2
3386	.lakesitetn.gov	2
3387	feedthefuture.gov	2
3388	.apec2011.gov	2
3389	invasivespeciesinfo.gov	2
3390	.battlefieldmo.gov	2
3391	.lanesborough-ma.gov	2
3392	realestatesales.gov	2
3393	customs.gov	2
3394	.ohiocourtofclaims.gov	2
3395	.ceciltonmd.gov	2
3396	normanparkga.gov	2
3397	.smartcard.gov	2
3398	okhouse.gov	2
3369	.wartimecontracting.gov	2
3367	.warrencountyky.gov	2
3400	portvincent-la.gov	2
3366	makinghomeaffordable.gov	2
3464	beachhaven-nj.gov	2
3337	.nagb.gov	2
3338	.pentagon.gov	2
3339	supremecourtus.gov	2
3340	.fortluptonco.gov	2
3341	.azhousing.gov	2
3342	.murrietaca.gov	2
3343	bioethics.gov	2
3344	bellevuewa.gov	2
3345	medicare.gov	2
3346	.californiadesert.gov	2
3347	.yarrowpointwa.gov	2
3348	.daughertytownship-pa.gov	2
3349	merchantvillenj.gov	2
3350	.dayton-me.gov	2
3351	.townofnortheastny.gov	2
3352	rosebudcountymt.gov	2
3353	cupcao.gov	2
3354	nea.gov	2
3355	.oceancommission.gov	2
3356	.bixbyok.gov	2
3357	.noscamnc.gov	2
3358	brooklynwi.gov	2
3359	.usis.gov	2
3361	savanna-il.gov	2
3362	.dalton-ma.gov	2
3363	.chathamtownship-nj.gov	2
3364	.beaumonttexas.gov	2
3365	nantucket-ma.gov	2
3399	crystalmn.gov	2
3360	5aday.gov	2
3401	.clintontownship-mi.gov	2
3433	.arnet.gov	2
3435	.govloans.gov	2
3436	.mrgo.gov	2
3437	.sturtevant-wi.gov	2
3438	.ngi.gov	2
3439	.williamsoncounty-tn.gov	2
3440	acwi.gov	2
3441	sacandfoxnation-nsn.gov	2
3442	.cmts.gov	2
3443	.greenwoodcounty-sc.gov	2
3444	bja.gov	2
3445	.fff.gov	2
3446	.dsac.gov	2
3447	newarkde.gov	2
3448	daniabeachfl.gov	2
3449	.amtrakoig.gov	2
3450	paristn.gov	2
3452	.ich.gov	2
3453	lowerpaxton-pa.gov	2
3454	roswell-nm.gov	2
3455	.lyndonks.gov	2
3456	shivelyky.gov	2
3457	.ashvilleohio.gov	2
3458	.villageofkensingtonny.gov	2
3459	iowa.gov	2
3460	.nortonva.gov	2
3461	ise.gov	2
3462	miamibeachfl.gov	2
3402	.claycountymn.gov	2
3463	.shakopeemn.gov	2
3434	consumeraction.gov	2
3451	strategicsourcing.gov	2
3432	.de.gov	2
3417	.ttbonline.gov	2
3404	.tallapoosaga.gov	2
3431	ustraderep.gov	2
3405	.wilmingtonma.gov	2
3406	marysvillewa.gov	2
3407	.innocencecommission-nc.gov	2
3408	sc.gov	2
3409	.telework.gov	2
3411	.cityofhouston.gov	2
3412	.citizenservices.gov	2
3413	.goshen-ohio.gov	2
3414	.orangecountync.gov	2
3415	.westminstermd.gov	2
3416	.sustainability.gov	2
3410	pflugervilletx.gov	2
3418	azlibrary.gov	2
3425	.protectyourmove.gov	2
3430	.performance.gov	2
3429	.ndop.gov	2
3419	vetsuccess.gov	2
3428	.identitytheft.gov	2
3427	.azdfi.gov	2
3426	maroaillinois.gov	2
3403	.chowancounty-nc.gov	2
3423	usgeo.gov	2
3422	cooperativeconservation.gov	2
3421	.ohioagriculture.gov	2
3420	townofwillsborony.gov	2
3424	wethersfieldct.gov	2
3826	amberalert.gov	1
3818	.christiancountymo.gov	1
3825	.bureaucounty-il.gov	1
3824	.biosecurityboard.gov	1
3823	.cantontwp-oh.gov	1
3822	taylortx.gov	1
3821	.tomballtx.gov	1
3820	.baycounty-mi.gov	1
3819	.nibin.gov	1
3816	.coast2050.gov	1
3809	.estrategy.gov	1
3817	.trinityal.gov	1
3815	.bunkerhilltx.gov	1
3814	.mrlc.gov	1
3813	greenvillenc.gov	1
3811	waverlyhallga.gov	1
3810	.defendamerica.gov	1
3808	virginiacapitol.gov	1
3812	.harpercountyks.gov	1
3839	.nalusda.gov	1
3827	.saddlerockny.gov	1
3828	lakegroveny.gov	1
3845	.taxreformpanel.gov	1
3806	dedham-ma.gov	1
3844	.watertown-ma.gov	1
3843	.fireplan.gov	1
3842	broadbandmap.gov	1
3841	imls.gov	1
3840	.brookhavenny.gov	1
3838	drought.gov	1
3837	.fitchburgwi.gov	1
3836	ourdocuments.gov	1
3835	.georgewbushlibrary.gov	1
3834	web-services.gov	1
3833	.pelhamalabama.gov	1
3832	morriscountynj.gov	1
3831	.nyassembly.gov	1
3830	.goshen-oh.gov	1
3829	ncjrs.gov	1
3807	hilliardohio.gov	1
3775	.stat-usa.gov	1
3805	fgdc.gov	1
3784	.winneconnewi.gov	1
3782	azot.gov	1
3781	socialcirclega.gov	1
3780	.challenge.gov	1
3779	.jacksonwy.gov	1
3778	usaspending.gov	1
3777	cityofhaywardwi.gov	1
3776	.chcoc.gov	1
3774	frtr.gov	1
3773	sullivancountynh.gov	1
3772	hempsteadny.gov	1
3771	.noa.gov	1
3770	.aftac.gov	1
3769	ridgecrest-ca.gov	1
3768	.stocktonca.gov	1
3767	.vetbiz.gov	1
3766	hialeahfl.gov	1
3847	clermontcountyohio.gov	1
3783	.lyman-me.gov	1
3785	.oceangate-nj.gov	1
3804	.azinvestor.gov	1
3786	.washingtonnc.gov	1
3803	.azclimatechange.gov	1
3802	.daniabeachfl.gov	1
3801	.kentcountymi.gov	1
3800	nh.gov	1
3799	.micmac-nsn.gov	1
3798	.getyouhome.gov	1
3797	fdlp.gov	1
3796	usptocareers.gov	1
3795	.americanlatinomuseum.gov	1
3794	.tsunami.gov	1
3793	mesaaz.gov	1
3792	sedonaaz.gov	1
3791	climatetechnology.gov	1
3790	yonkersny.gov	1
3789	ucrdatatool.gov	1
3788	heritageabroad.gov	1
3787	.stopfraud.gov	1
3846	.iedison.gov	1
3911	.miamicountyin.gov	1
3848	tnpromise.gov	1
3901	hooveralabama.gov	1
3909	.woodburymn.gov	1
3908	.psob.gov	1
3907	wheelingil.gov	1
3906	.acqnet.gov	1
3905	.medlineplus.gov	1
3904	yelmwa.gov	1
3903	.wcnyh.gov	1
3902	salisburync.gov	1
3900	.firescience.gov	1
3849	athol-ma.gov	1
3899	.basslakewi.gov	1
3898	.ems.gov	1
3897	buenavistaco.gov	1
3896	scsenate.gov	1
3895	uspto.gov	1
3894	.nwbc.gov	1
3893	.dh.gov	1
3892	.wsdot.gov	1
3910	start2farm.gov	1
3764	cendi.gov	1
3912	hud.gov	1
3913	.surfcitync.gov	1
3930	.heritageny.gov	1
3929	ctoc.gov	1
3928	mesagrandeband-nsn.gov	1
3927	.housedemocrats.gov	1
3926	indianaffairs.gov	1
3925	arlingtonma.gov	1
3924	fedcir.gov	1
3923	tetoncountyidaho.gov	1
3922	.mcminncountytn.gov	1
3921	.science360.gov	1
3920	.sourcewell-mn.gov	1
3919	.mndnr.gov	1
3918	ncs.gov	1
3917	marlborough-ma.gov	1
3916	.bremertonwa.gov	1
3915	swinomish-nsn.gov	1
3914	.cityofwestonlakes-tx.gov	1
3891	azdoa.gov	1
3890	.gsaig.gov	1
3889	mtbs.gov	1
3868	.washingtoncountyks.gov	1
3866	.daviecountync.gov	1
3865	.ppirs.gov	1
3864	westonwi.gov	1
3863	boemre.gov	1
3862	.scconsumer.gov	1
3861	.fredenbergtwp-mn.gov	1
3860	culpepercounty.gov	1
3859	monroewa.gov	1
3858	.manitousprings-co.gov	1
3857	.ardot.gov	1
3856	.virginiageneralassembly.gov	1
3855	.eastborough-ks.gov	1
3854	.putnamcountytn.gov	1
3853	.heyworth-il.gov	1
3852	.unioncountyil.gov	1
3851	oceangate-nj.gov	1
3850	.crimevictims.gov	1
3867	.lakevillemn.gov	1
3869	fhfa.gov	1
3888	.greenecountyms.gov	1
3870	blainecounty-mt.gov	1
3887	.sdi.gov	1
3886	.flcrc.gov	1
3885	tsunami.gov	1
3884	roanecountytn.gov	1
3883	simontontexas.gov	1
3882	.healthierus.gov	1
3881	.lincolnbicentennial.gov	1
3880	troyny.gov	1
3879	.morrillcountyne.gov	1
3878	.capitolflags.gov	1
3877	onrr.gov	1
3876	peacecorps.gov	1
3875	.willingboronj.gov	1
3874	ohiocourtofclaims.gov	1
3873	mitigationcommission.gov	1
3872	.results.gov	1
3871	roundrocktexas.gov	1
3765	.azftf.gov	1
3699	lansingmi.gov	1
3763	.fsam.gov	1
3647	.crt2014-2024review.gov	1
3655	harrisburgsd.gov	1
3654	thayercountyne.gov	1
3653	.thehistoryofareacodes.gov	1
3652	.disabilityinfo.gov	1
3651	.girardkansas.gov	1
3650	springfieldohio.gov	1
3649	.middletownrancheria-nsn.gov	1
3648	.frogweb.gov	1
3646	.americusga.gov	1
3762	salemva.gov	1
3645	.yadkincounty.gov	1
3644	.civilianresponsecorps.gov	1
3643	americorps.gov	1
3642	.jwod.gov	1
3641	illinoisattorneygeneral.gov	1
3640	.richlandsnc.gov	1
3639	.delawaretownshippa.gov	1
3638	ottawaks.gov	1
3656	.brunswickcountync.gov	1
3657	fnal.gov	1
3658	.floresvilletx.gov	1
3659	.artreasury.gov	1
3676	.ohiojudicialcenter.gov	1
3675	champaignil.gov	1
3674	.greenwichct.gov	1
3673	.ellijay-ga.gov	1
3672	.cityoflincoln-il.gov	1
3671	fargond.gov	1
3670	.ashgrovemo.gov	1
3669	wyomingmi.gov	1
3668	.asc.gov	1
3667	.mandauecity.gov	1
3666	restorethegulf.gov	1
3665	starkcountyohio.gov	1
3664	lymansc.gov	1
3663	.hanovercounty.gov	1
3662	dallasga.gov	1
3661	stpaul.gov	1
3660	fueleconomy.gov	1
3637	thomas.gov	1
3636	viennava.gov	1
3635	manufacturing.gov	1
3614	losgatosca.gov	1
3612	.waveland-ms.gov	1
3611	longhillnj.gov	1
3610	johnscreekga.gov	1
3609	.pauma-nsn.gov	1
3608	.belleriveacresmo.gov	1
3607	nashville.gov	1
3606	aqmd.gov	1
3605	.financialresearch.gov	1
3604	bpa.gov	1
3603	wheelingwv.gov	1
3602	.goddardks.gov	1
3601	childstats.gov	1
3600	anl.gov	1
3599	.arklegaudit.gov	1
3598	braintreema.gov	1
3597	.azarts.gov	1
3596	.marine.gov	1
3613	.disasterhelp.gov	1
3615	takomaparkmd.gov	1
3634	virginia.gov	1
3616	.supreme-court.gov	1
3633	.oregonvotes.gov	1
3632	.presidiotrust.gov	1
3631	.scribner-ne.gov	1
3630	ucore.gov	1
3629	columbiasc.gov	1
3628	.limerick-me.gov	1
3627	.jaspercountyin.gov	1
3626	.eastfishkillny.gov	1
3625	.omhrc.gov	1
3624	.durban.gov	1
3623	.abilityone.gov	1
3622	.cfoc.gov	1
3621	roanokeva.gov	1
3620	.flu.gov	1
3619	tennesseepromise.gov	1
3618	bethlehem-pa.gov	1
3617	.ma.gov	1
3677	auburnmaine.gov	1
3678	collegenavigator.gov	1
3679	needhamma.gov	1
3742	.stpauls.gov	1
3740	louisianaentertainment.gov	1
3739	knoxvilletn.gov	1
3738	.otaywater.gov	1
3737	.measuretn.gov	1
3736	.ninilchiktribe-nsn.gov	1
3735	.egrpra.gov	1
3734	dcfra.gov	1
3733	.manisteecountymi.gov	1
3732	chilmarkma.gov	1
3731	.maplegrovemn.gov	1
3730	sdsos.gov	1
3729	.mnhousing.gov	1
3728	surgeongeneral.gov	1
3727	.ihaveaplaniowa.gov	1
3726	.njmvc.gov	1
3725	pa.gov	1
3724	.maharashtra.gov	1
3741	mlkday.gov	1
3743	whitehousedrugpolicy.gov	1
3722	.sequatchiecounty-tn.gov	1
3744	.byesvilleoh.gov	1
3761	.beecavetexas.gov	1
3760	.azbn.gov	1
3759	.mich.gov	1
3758	pawneerock-ks.gov	1
3757	csosa.gov	1
3756	truman.gov	1
3755	galenaks.gov	1
3754	.blainecounty-mt.gov	1
3753	greenecountymo.gov	1
3752	.swinomish-nsn.gov	1
3751	sbcounty.gov	1
3750	.plandomeheights-ny.gov	1
3749	nativeamericanheritagemonth.gov	1
3748	azliquor.gov	1
3747	.edgarcounty-il.gov	1
3746	cindy.gov	1
3745	.supremecourtofohio.gov	1
3723	.frelinghuysentwp-nj.gov	1
3721	jamesmadison.gov	1
3680	ouraycountyco.gov	1
3700	.deserttortoise.gov	1
3697	.icbemp.gov	1
3696	.galvestoncountytx.gov	1
3695	cityoftitusvillepa.gov	1
3694	.azgohs.gov	1
3693	.capitolheightsmd.gov	1
3692	.belaireks.gov	1
3691	tukwilawa.gov	1
3690	.eastcoventry-pa.gov	1
3689	.childcare.gov	1
3688	.mundytwp-mi.gov	1
3687	.encinitasca.gov	1
3686	njsda.gov	1
3685	.druidhillsky.gov	1
3684	symbols.gov	1
3683	.juvenilecouncil.gov	1
3682	.ypt-nsn.gov	1
3681	.summervillesc.gov	1
3698	.whitehouseoh.gov	1
3701	.fnc.gov	1
3720	uscirf.gov	1
3702	.kingsburyny.gov	1
3719	.westlinnoregon.gov	1
3718	.pnt.gov	1
3717	rowe-ma.gov	1
3716	maricopa.gov	1
3715	.townshipoftabernacle-nj.gov	1
3714	.911commission.gov	1
3713	.conshohockenpa.gov	1
3712	.sausalito.gov	1
3711	.projectsafechildhood.gov	1
3710	.cityofhokah-mn.gov	1
3709	chandleraz.gov	1
3708	cityofcanalfulton-oh.gov	1
3707	.alabamapublichealth.gov	1
3706	.whcoa.gov	1
3705	cliftonaz.gov	1
3704	scijinks.gov	1
3703	.okgeosurvey1.gov	1
3931	sno-nsn.gov	1

3932 rows × 2 columns

WP:M event counts for .gov top level domain by event type¶

limited to WP:M pages with external links
Only extClick events found

In [14]:

# Event counts for .gov top level domain by event type
# limited to WP:M pages with external links
wpm_gov_query = """
SELECT REGEXP_EXTRACT({},'(\.[^\.]+\.[^\.]+)$',1) as domain, action, COUNT(*) AS count 
FROM citationusage 
WHERE wiki = 'enwiki'
AND page_id IN 
    (SELECT page_id FROM ryanmax.population_wpm_pages_with_extlinks)
{}
AND to_date(event_time) >= '{}'
AND to_date(event_time) <= '{}'
AND useragent_is_bot = FALSE
AND REGEXP_EXTRACT({},'(\.[^\.]+)$',1) = '.gov'
GROUP BY domain, action
ORDER BY COUNT(*) DESC
"""
wpm_gov_events = spark.sql(
    wpm_gov_query.format(
        parse_host_from_link_url,
        event_exclusion_sql, start_date_string, end_date_string,
        parse_host_from_link_url
    ))
wpm_gov_events_rdd = wpm_gov_events.rdd
wpm_gov_events_df = sqlContext.createDataFrame(wpm_gov_events_rdd)
wpm_gov_events_pandas = wpm_gov_events_df.toPandas()

In [15]:

wpm_gov_events_pandas.sort_values(by=['count'],ascending=False)[['domain','count']]

Out[15]:

	domain	count
0	.nih.gov	117605
1	.cdc.gov	11238
2	.fda.gov	9536
3	.cancer.gov	3703
4	medlineplus.gov	2302
5	.hhs.gov	1601
6	.cms.gov	1228
7	.epa.gov	1157
8	.gpo.gov	793
9	.va.gov	621
10	.usda.gov	600
11	.ahrq.gov	566
12	.house.gov	522
13	.drugabuse.gov	506
14	.samhsa.gov	468
15	.congress.gov	442
16	.cia.gov	432
17	.senate.gov	410
18	.womenshealth.gov	394
19	clinicaltrials.gov	390
20	.ohio.gov	382
21	.fema.gov	362
22	.ca.gov	333
23	.hrsa.gov	326
24	.loc.gov	318
25	.genome.gov	274
26	.sec.gov	252
27	.nasa.gov	245
28	.medicaid.gov	245
29	.osha.gov	231
30	.healthcare.gov	225
31	.irs.gov	218
32	.usdoj.gov	211
33	.fbi.gov	203
34	.ny.gov	193
35	.census.gov	190
36	.clinicaltrials.gov	190
37	.govinfo.gov	189
38	.medicare.gov	165
39	.bls.gov	160
41	.cbo.gov	142
40	.guideline.gov	142
42	.in.gov	141
43	.federalregister.gov	139
44	.justice.gov	137
45	.ed.gov	131
46	.wa.gov	127
47	.surgeongeneral.gov	111
48	.dc.gov	108
49	.nist.gov	104
50	.gao.gov	92
51	.whitehouse.gov	90
52	.dot.gov	89
53	.uspto.gov	88
54	.fcc.gov	80
55	.ornl.gov	77
56	.usaid.gov	76
57	.dol.gov	75
58	.usphs.gov	74
59	health.gov	74
60	.archives.gov	72
62	.ada.gov	70
61	.ncjrs.gov	70
63	.oregon.gov	69
64	.usgs.gov	69
65	cdc.gov	67
66	.noaa.gov	66
67	.medlineplus.gov	64
68	.ihs.gov	63
69	.healthit.gov	63
70	.hiv.gov	62
72	.vermont.gov	60
71	.faa.gov	60
73	.bnl.gov	59
74	.lacounty.gov	56
75	.eeoc.gov	54
76	.vaccines.gov	54
77	.mass.gov	54
78	.nola.gov	52
79	.ftc.gov	50
80	.nsf.gov	50
81	.doe.gov	49
83	.ssa.gov	48
82	.miamidade.gov	48
84	.idaho.gov	47
85	.ars-grin.gov	46
86	.uscourts.gov	43
87	.juvenilecouncil.gov	43
88	.childwelfare.gov	42
89	.nhtsa.gov	41
90	.dea.gov	40
91	.ems.gov	40
92	.lanl.gov	39
93	.osti.gov	39
94	.bjs.gov	38
95	.michigan.gov	37
96	.nlm.gov	37
97	.virginia.gov	37
98	.health.gov	36
99	.utah.gov	35
100	.dhhs.gov	34
101	.doleta.gov	33
102	.nyc.gov	31
103	.sandiego.gov	30
104	.state.gov	29
105	.phila.gov	27
107	mn.gov	27
108	.nrc.gov	27
106	.healthypeople.gov	27
109	.safercar.gov	26
110	healthypeople.gov	26
111	.anl.gov	26
112	.aids.gov	25
113	.lbl.gov	25
116	.energy.gov	24
117	.supremecourt.gov	24
114	.nps.gov	24
115	.treasury.gov	24
118	.ne.gov	23
119	.wisconsin.gov	23
120	womenshealth.gov	20
121	.milwaukee.gov	20
122	.riversideca.gov	20
125	nj.gov	19
124	.usembassy.gov	19
123	.tennessee.gov	19
127	.illinois.gov	18
128	.fdlp.gov	18
126	.ky.gov	18
129	.choosemyplate.gov	17
130	.usa.gov	17
131	cityofboston.gov	17
132	.fitness.gov	17
136	.nara.gov	16
137	.flu.gov	16
135	.csb.gov	16
134	healthvermont.gov	16
133	.regulations.gov	16
138	.tsa.gov	15
139	.cpsc.gov	14
140	bioethics.gov	14
141	.nv.gov	14
142	.pandemicflu.gov	14
143	.maryland.gov	14
147	.ntsb.gov	13
148	.metrokc.gov	13
144	.marylandhealthconnection.gov	13
146	.organdonor.gov	13
145	.phe.gov	13
149	.whitehousedrugpolicy.gov	12
150	.colorado.gov	12
151	.delaware.gov	12
152	.mn.gov	12
157	.kingcounty.gov	11
160	.nh.gov	11
159	.mentalhealth.gov	11
158	.gpoaccess.gov	11
155	.nysed.gov	11
156	.fws.gov	11
154	organdonor.gov	11
153	guideline.gov	11
167	.hud.gov	10
166	cancer.gov	10
165	.medpac.gov	10
164	.stopfraudcolorado.gov	10
163	.louisvilleky.gov	10
161	.fedcenter.gov	10
162	michigan.gov	10
168	.ncdhhs.gov	9
169	healthit.gov	9
170	.msha.gov	9
175	.foodsafety.gov	8
178	.wi.gov	8
177	section508.gov	8
176	healthfinder.gov	8
173	.psc.gov	8
174	.opm.gov	8
172	.consumer.gov	8
171	.ct.gov	8
183	.4woman.gov	7
186	.pa.gov	7
184	.visitthecapitol.gov	7
185	.treas.gov	7
182	.ecfr.gov	7
179	.defense.gov	7
180	smokefree.gov	7
181	.ready.gov	7
192	.azdhs.gov	6
197	.mypyramid.gov	6
196	.maine.gov	6
195	.ncd.gov	6
193	.insurekidsnow.gov	6
194	.mo.gov	6
191	biometrics.gov	6
190	.ga.gov	6
189	.cavc.gov	6
188	drugabuse.gov	6
187	.nj.gov	6
205	.iowa.gov	5
211	guidelines.gov	5
210	.seattle.gov	5
209	.hawaii.gov	5
208	.bop.gov	5
207	.pepfar.gov	5
206	healthcare.gov	5
203	.dni.gov	5
204	.nitrd.gov	5
202	.cityofboston.gov	5
201	.healthindicators.gov	5
200	.scstatehouse.gov	5
199	.sc.gov	5
198	.mi.gov	5
221	.sandiegocounty.gov	4
229	.newportbeachca.gov	4
228	.transportation.gov	4
227	.healthfinder.gov	4
226	.neh.gov	4
225	.hanford.gov	4
224	surgeongeneral.gov	4
222	.ri.gov	4
223	.ohiopmp.gov	4
220	.scdhhs.gov	4
218	.arc.gov	4
217	nih.gov	4
216	.commerce.gov	4
215	.pmi.gov	4
214	.ks.gov	4
213	maine.gov	4
219	.pcip.gov	4
212	genome.gov	4
248	phila.gov	3
244	.usap.gov	3
245	.ilsos.gov	3
246	.nij.gov	3
252	.globalhealth.gov	3
249	aids.gov	3
250	.nbii.gov	3
251	usphs.gov	3
253	.lymenh.gov	3
242	.nyhealth.gov	3
243	azdhs.gov	3
247	.scdhec.gov	3
241	locatorplus.gov	3
234	.osac.gov	3
240	.bja.gov	3
230	.traviscountytx.gov	3
231	.baltimorecountymd.gov	3
233	usich.gov	3
232	.fido.gov	3
235	.mentalhealthcommission.gov	3
236	.uscis.gov	3
237	.gsa.gov	3
238	loc.gov	3
239	.tn.gov	3
278	.ok.gov	2
272	.mt.gov	2
273	mdcourts.gov	2
274	.jccbi.gov	2
275	.fjc.gov	2
276	azleg.gov	2
277	.sbir.gov	2
284	.az.gov	2
279	costamesaca.gov	2
280	.uscirf.gov	2
281	senate.gov	2
282	.invasivespeciesinfo.gov	2
283	.dhs.gov	2
270	.texas.gov	2
271	.fnal.gov	2
285	.wv.gov	2
269	.nutrition.gov	2
260	.myfloridahouse.gov	2
268	.itis.gov	2
254	.jct.gov	2
255	.aoa.gov	2
256	.llnl.gov	2
258	.challenge.gov	2
259	.section508.gov	2
257	.nc.gov	2
261	.alabamapublichealth.gov	2
262	.fec.gov	2
263	federalregister.gov	2
264	isotopes.gov	2
265	cbo.gov	2
266	.wvlegislature.gov	2
267	epa.gov	2
317	.ojjdp.gov	1
309	.ngc.gov	1
310	.usbr.gov	1
311	.kdheks.gov	1
312	.azleg.gov	1
313	.usmission.gov	1
314	.us-cert.gov	1
315	.america.gov	1
316	.samsha.gov	1
325	.omhrc.gov	1
318	.guidelines.gov	1
319	.usps.gov	1
320	riversideca.gov	1
321	.arkansas.gov	1
322	.federalreserve.gov	1
323	.acus.gov	1
324	.howardcountymd.gov	1
307	.la.gov	1
326	.newjersey.gov	1
327	.nebraskalegislature.gov	1
308	flsenate.gov	1
299	.selectagents.gov	1
306	.fbo.gov	1
294	.costamesaca.gov	1
286	energy.gov	1
287	.recovery.gov	1
288	.nationalservice.gov	1
289	.healthdata.gov	1
290	.inl.gov	1
291	childwelfare.gov	1
292	4women.gov	1
293	.sno-nsn.gov	1
295	.dna.gov	1
305	.alabama.gov	1
296	gao.gov	1
297	.wvdnr.gov	1
298	.cstx.gov	1
300	.neglecteddiseases.gov	1
301	.vt.gov	1
302	.usccr.gov	1
303	.hearttruth.gov	1
304	.nationalchildrensstudy.gov	1
328	.copyright.gov	1