{"id":643,"date":"2015-01-28T17:33:01","date_gmt":"2015-01-28T16:33:01","guid":{"rendered":"https:\/\/joernhees.de\/blog\/?p=643"},"modified":"2016-09-28T22:45:50","modified_gmt":"2016-09-28T20:45:50","slug":"dbpedia-2014-stats-top-subjects-predicates-and-objects","status":"publish","type":"post","link":"https:\/\/joernhees.de\/blog\/2015\/01\/28\/dbpedia-2014-stats-top-subjects-predicates-and-objects\/","title":{"rendered":"DBpedia 2014 Stats – Top Subjects, Predicates and Objects"},"content":{"rendered":"

Ever wondered what the top subjects \/ predicates \/ objects are in DBpedia?<\/p>\n

I recently came across this problem while trying to draw a random sample of nodes from DBpedia which follow a given degree distribution for my PhD.<\/p>\n

Turns out this is actually more difficult than i expected. Mostly due to the fact that quad stores don’t optimize for such queries. This means that you can’t just ask a SPARQL endpoint (not even your local one<\/a>) to give you the top subjects, predicates or objects with a query like this:<\/p>\n

select ?n count(*) as ?c\r\nwhere {\r\n  ?n ?p ?o.\r\n}\r\norder by desc(?c)\r\nlimit 10\r\n<\/code><\/pre>\n
Try yourself here<\/a> if you don’t believe me… (i set it to time out after 15 seconds and it will return quite a dangerously nonsensical result if you’re not aware that you might get partial answers<\/a>).<\/p>\n
Some Rant<\/h3>\n
So this lead me to the fascinating conclusion that our beloved RDF query language doesn’t even allow us to answer simple questions such as “which node is most often used as a subject \/ predicate \/ object?” (we’re talking with a single SPARQL endpoint here, don’t even try dragging me into an open\/closed world assumption discussion, …).<\/p>\n
So, all is great, let’s just not ask those evil questions…<\/p>\n
… said no (computer) scientist ever.<\/p>\n
So let’s get our hands dirty and use some unix tool magic…<\/p>\n
Working with Dumps in NT Format<\/h2>\n
Luckily, I already had all the dumps laid out locally as described here<\/a>, and lucky again, they are in N-Triples format.<\/p>\n
N-Triples<\/a> is a line based format, which means we have exactly one triple per line. I don’t exactly know whom to thank for this, but should you ever read this (wait, why are you reading my blog?) THANK YOU. It means that neither subject nor predicate nor object can contain (unescaped) newlines. And this means that you can actually quite sanely sort and parse .nt files with standard unix tools that have been optimized by generations of smart people.<\/p>\n
I think you see where this is going: a good old bash one-liner with grep, cut, sort and uniq, by far the fastest tools i know for the job<\/a>.<\/p>\n
A Word about Sort Orders and Locales<\/h2>\n
Sort orders depend on your locale! This means that files sorted with a locale such as en_US.UTF-8 are not properly sorted for someone with a locale such as de_DE.UTF-8. Hence it’s wise to always run this in a shell before working with sort:<\/p>\n
export LC_ALL=C\r\n<\/code><\/pre>\n
It resets your locale to a classic C byte-wise one, having the nice side effect that it’s faster as well.<\/p>\n
Deduplication<\/h2>\n
First, it turns out the DBpedia dumps actually contain quite an astonishing amount of duplicate triples. This is not a problem if loaded into a quad store as they’ll just count once, but for counting them like we will, it is a problem.<\/p>\n
To split them apart let’s do the following: we pick up all the dump files that are loaded into our endpoint with pv<\/code> a handy little tool similar to cat<\/code>, but it shows a nice progress bar. Then we decompress with zcat<\/code>, remove comments from the files with grep<\/code> and then call sort<\/code>. We actually tell sort to use a ton of RAM (32 GB), but actually not even that is enough for the > 80 GB decompressed dumps, so we need temp files. We can direct sort to put them onto an SSD instead of just in \/tmp<\/code> as by default, and we can also compress those temp files on the fly. lzop<\/code> is a very fast compression tool and the perfect fit for this (not compressing the files with this actually degrades performance even at 300 MB\/s write speeds of the SSD!). After this we use tee<\/code> to multiplex our stream into two channels: one plain uniq<\/code> and gzipped with pigz<\/code> (like gzip<\/code> but parallel, as gzipping > 80 GB becomes quite the bottleneck here otherwise) into dbpedia_uniq.nt.gz<\/code> and another invocation of uniq -c -d<\/code> which only counts the duplicate lines and gzip<\/code>s (this is ok to be single threaded, as it’s not sooo big) them into dbpedia_dups.txt.gz<\/code>.<\/p>\n
pv \/usr\/local\/data\/datasets\/remote\/dbpedia\/2014\/importedGraphs\/{dbpedia.org,ext.dbpedia.org,pagelinks.dbpedia.org,topicalconcepts.dbpedia.org}\/* |\r\n  zcat |  # decompress\r\n  grep -v -E '^\\s*#' |  # ignore comments in the nt files\r\n  sort -S32G -T\/ssd\/tmp\/ --compress-program=lzop |\r\n  tee \\\r\n    >( uniq | pigz > dbpedia_uniq.nt.gz ) \\\r\n    >( uniq -c -d | gzip > dbpedia_dups.txt.gz ) \\\r\n    >\/dev\/null\r\n<\/code><\/pre>\n
As you can see from the first line include external, pagelinks and topicalconcepts datasets, but the process is really the same no matter what.<\/p>\n
After ~ 10 minutes we’re left with a 6.5 GB dbpedia_uniq.nt.gz<\/a> (547,084,682 unique triples) and a 238 MB dbpedia_dups.txt.gz<\/a>.<\/p>\n
Top Duplicates<\/h2>\n
The top duplicates as acquired with<\/p>\n
zcat dbpedia_dups.txt.gz | sort -n -r -S8G | head -n20\r\n<\/code><\/pre>\n
are (full file<\/a> (238 MB)):<\/p>\n
   4891 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Flag_of_Slovenia.svg?width=300> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Flag_of_Slovenia.svg> .\r\n   4891 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Flag_of_Slovenia.svg> <http:\/\/xmlns.com\/foaf\/0.1\/thumbnail> <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Flag_of_Slovenia.svg?width=300> .\r\n   4891 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Flag_of_Slovenia.svg> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Flag_of_Slovenia.svg> .\r\n   1520 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Naval_Ensign_of_the_United_Kingdom.svg?width=300> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Naval_Ensign_of_the_United_Kingdom.svg> .\r\n   1520 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Naval_Ensign_of_the_United_Kingdom.svg> <http:\/\/xmlns.com\/foaf\/0.1\/thumbnail> <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Naval_Ensign_of_the_United_Kingdom.svg?width=300> .\r\n   1520 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Naval_Ensign_of_the_United_Kingdom.svg> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Naval_Ensign_of_the_United_Kingdom.svg> .\r\n   1195 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Airplane_silhouette.svg?width=300> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Airplane_silhouette.svg> .\r\n   1195 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Airplane_silhouette.svg> <http:\/\/xmlns.com\/foaf\/0.1\/thumbnail> <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Airplane_silhouette.svg?width=300> .\r\n   1195 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Airplane_silhouette.svg> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Airplane_silhouette.svg> .\r\n   1188 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Med_1.png?width=300> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Med_1.png> .\r\n   1188 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Med_1.png> <http:\/\/xmlns.com\/foaf\/0.1\/thumbnail> <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Med_1.png?width=300> .\r\n   1188 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Med_1.png> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Med_1.png> .\r\n   1159 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Flag_of_the_British_Army.svg?width=300> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Flag_of_the_British_Army.svg> .\r\n   1159 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Flag_of_the_British_Army.svg> <http:\/\/xmlns.com\/foaf\/0.1\/thumbnail> <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Flag_of_the_British_Army.svg?width=300> .\r\n   1159 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Flag_of_the_British_Army.svg> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Flag_of_the_British_Army.svg> .\r\n    914 <http:\/\/en.wikipedia.org\/wiki\/Special:FilePath\/Cricket_no_pic.png?width=300> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Cricket_no_pic.png> .\r\n    914 <http:\/\/en.wikipedia.org\/wiki\/Special:FilePath\/Cricket_no_pic.png> <http:\/\/xmlns.com\/foaf\/0.1\/thumbnail> <http:\/\/en.wikipedia.org\/wiki\/Special:FilePath\/Cricket_no_pic.png?width=300> .\r\n    914 <http:\/\/en.wikipedia.org\/wiki\/Special:FilePath\/Cricket_no_pic.png> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Cricket_no_pic.png> .\r\n    885 <http:\/\/dbpedia.org\/resource\/List_of_Tachinidae_genera> <http:\/\/dbpedia.org\/ontology\/wikiPageWikiLink> <http:\/\/dbpedia.org\/resource\/List_of_Tachinidae_genera> .\r\n    784 <http:\/\/commons.wikimedia.org\/wiki\/Special:FilePath\/Illinois_-_outline_map.svg?width=300> <http:\/\/purl.org\/dc\/elements\/1.1\/rights> <http:\/\/en.wikipedia.org\/wiki\/File:Illinois_-_outline_map.svg> .\r\n<\/code><\/pre>\n
Getting S,P,O Counts<\/h2>\n
OK, now let’s count the subject, predicate and object occurrences.
\nSubjects, predicates and objects are delimited with a single space (” “), everything else in the line we just count as an object (so we just count the final ” .” to the object).
\nSimilar to the above pipeline, we use tee<\/code> again to multiplex the stream into three pipelines for subject, predicate and object counts.
\nEach of them is mostly based on cut<\/code>, first to get the fields (-f1<\/code> for subject, -f2<\/code> predicate, -f3-<\/code> object), then for limiting very long strings to only the first 1024 chars. While this actually introduces some false positive matches for long literals, it’s probably safe for URIs, and reduces sort times and file sizes for the object chunk a lot. If you want very accurate counts you should probably re-run without the cut -c-1024<\/code> lines.
\nAfterwards in each pipeline the occurrences of a node in the s,p,o positions are sorted and counted with uniq -c<\/code>, then gzipped with pigz<\/code>.<\/p>\n
pv dbpedia_uniq.nt.gz |\r\n  zcat |\r\n  tee \\\r\n    >( cut -f1 -d' ' |\r\n       cut -c-1024 |\r\n       sort -S16G -T\/ssd\/tmp\/ --compress-program=lzop |\r\n       uniq -c |\r\n       pigz > dbpedia_1_subject_counts.txt.gz ) \\\r\n    >( cut -f2 -d' ' |\r\n       cut -c-1024 |\r\n       sort -S16G -T\/ssd\/tmp\/ --compress-program=lzop |\r\n       uniq -c |\r\n       pigz > dbpedia_2_predicate_counts.txt.gz ) \\\r\n    >( cut -f3- -d' ' |\r\n       cut -c-1024 |\r\n       sort -S16G -T\/ssd\/tmp\/ --compress-program=lzop |\r\n       uniq -c | pigz > dbpedia_3_object_counts.txt.gz ) \\\r\n    >\/dev\/null\r\n<\/code><\/pre>\n
After 15 minutes we’re left with 3 files:
\ndbpedia_1_subject_counts.txt.gz<\/a> (214M), dbpedia_2_predicate_counts.txt.gz<\/a> (387K), dbpedia_3_object_counts.txt.gz<\/a> (1.9G)<\/p>\n
As expected there are only relatively few different predicates and the objects actually take up quite a lot of data.<\/p>\n
Before getting the tops it’s quite useful to exclude subjects and objects that occur less than 10 times with awk<\/code>, which greatly reduces the filesizes and subsequent sort times:<\/p>\n
zcat dbpedia_1_subject_counts.txt.gz | awk ' $1 > 9 { print } ' | pigz > dbpedia_1_subject_counts_o9.txt.gz\r\nzcat dbpedia_3_object_counts.txt.gz | awk ' $1 > 9 { print } ' | pigz > dbpedia_3_object_counts_o9.txt.gz\r\n<\/code><\/pre>\n
dbpedia_1_subject_counts_o9.txt.gz<\/a> (89M), dbpedia_3_object_counts_o9.txt.gz<\/a> (30M).<\/p>\n
As we can see from the size reduction already there’s actually way more objects occurring less than 10 times than subjects.<\/p>\n
Similar to before the tops can be acquired with something like this:<\/p>\n
for f in dbpedia_1_subject_counts_o9.txt.gz dbpedia_2_predicate_counts.txt.gz dbpedia_3_object_counts_o9.txt.gz ; do\r\n  zcat $f | sort -n -r | pigz > ${f%.txt.gz}_tops.txt.gz\r\ndone\r\n<\/code><\/pre>\n
So here they are, the …<\/p>\n
Top 100 Subjects:<\/h2>\n
dbpedia_1_subject_counts_o9_tops.txt.gz<\/a> (95M)<\/p>\n
   8118 <http:\/\/dbpedia.org\/resource\/Alphabetical_list_of_communes_of_Italy>\r\n   7110 <http:\/\/dbpedia.org\/resource\/List_of_places_in_Afghanistan>\r\n   6162 <http:\/\/dbpedia.org\/resource\/Index_of_Andhra_Pradesh-related_articles>\r\n   5857 <http:\/\/dbpedia.org\/resource\/List_of_populated_places_in_Bosnia_and_Herzegovina>\r\n   5712 <http:\/\/dbpedia.org\/resource\/2013_in_film>\r\n   5550 <http:\/\/dbpedia.org\/resource\/List_of_municipalities_of_Brazil>\r\n   5458 <http:\/\/dbpedia.org\/resource\/List_of_dialling_codes_in_Germany>\r\n   5405 <http:\/\/dbpedia.org\/resource\/IUCN_Red_List_vulnerable_species_(Plantae)>\r\n   5392 <http:\/\/dbpedia.org\/resource\/List_of_CJK_Unified_Ideographs,_part_3_of_4>\r\n   5392 <http:\/\/dbpedia.org\/resource\/List_of_CJK_Unified_Ideographs,_part_2_of_4>\r\n   5392 <http:\/\/dbpedia.org\/resource\/List_of_CJK_Unified_Ideographs,_part_1_of_4>\r\n   5182 <http:\/\/dbpedia.org\/resource\/IUCN_Red_List_vulnerable_species_(Animalia)>\r\n   5152 <http:\/\/dbpedia.org\/resource\/Index_of_India-related_articles>\r\n   5090 <http:\/\/dbpedia.org\/resource\/List_of_law_clerks_of_the_Supreme_Court_of_the_United_States>\r\n   5068 <http:\/\/dbpedia.org\/resource\/List_of_Social_Democratic_Party_of_Germany_members>\r\n   4942 <http:\/\/dbpedia.org\/resource\/List_of_painters_in_the_Web_Gallery_of_Art>\r\n   4873 <http:\/\/dbpedia.org\/resource\/List_of_stage_names>\r\n   4829 <http:\/\/dbpedia.org\/resource\/List_of_CJK_Unified_Ideographs,_part_4_of_4>\r\n   4795 <http:\/\/dbpedia.org\/resource\/List_of_Harvard_University_people>\r\n   4743 <http:\/\/dbpedia.org\/resource\/List_of_OMIM_disorder_codes>\r\n   4726 <http:\/\/dbpedia.org\/resource\/List_of_populated_places_in_Serbia>\r\n   4698 <http:\/\/dbpedia.org\/resource\/List_of_populated_places_in_Serbia_(alphabetic)>\r\n   4690 <http:\/\/dbpedia.org\/resource\/Index_of_philosophy_articles_(I%E2%80%93Q)>\r\n   4603 <http:\/\/dbpedia.org\/resource\/List_of_molluscan_genera_represented_in_the_fossil_record>\r\n   4493 <http:\/\/dbpedia.org\/resource\/List_of_American_television_programs_by_date>\r\n   4457 <http:\/\/dbpedia.org\/resource\/List_of_biographical_films>\r\n   4443 <http:\/\/dbpedia.org\/resource\/List_of_brachiopod_genera>\r\n   4355 <http:\/\/dbpedia.org\/resource\/List_of_English_writers>\r\n   4345 <http:\/\/dbpedia.org\/resource\/List_of_composers_by_name>\r\n   4341 <http:\/\/dbpedia.org\/resource\/List_of_historical_German_and_Czech_names_for_places_in_the_Czech_Republic>\r\n   4329 <http:\/\/dbpedia.org\/resource\/2012_in_film>\r\n   4275 <http:\/\/dbpedia.org\/resource\/List_of_people_from_Illinois>\r\n   4219 <http:\/\/dbpedia.org\/resource\/List_of_people_from_Texas>\r\n   4218 <http:\/\/dbpedia.org\/resource\/List_of_village_development_committees_of_Nepal>\r\n   4194 <http:\/\/dbpedia.org\/resource\/List_of_postal_codes_in_Portugal>\r\n   4159 <http:\/\/dbpedia.org\/resource\/IUCN_Red_List_data_deficient_species_(Chordata)>\r\n   4140 <http:\/\/dbpedia.org\/resource\/List_of_trilobite_genera>\r\n   4137 <http:\/\/dbpedia.org\/resource\/List_of_aircraft_engines>\r\n   4130 <http:\/\/dbpedia.org\/resource\/List_of_moths_of_Taiwan>\r\n   4084 <http:\/\/dbpedia.org\/resource\/List_of_flora_of_the_Sonoran_Desert_Region_by_common_name>\r\n   3992 <http:\/\/dbpedia.org\/resource\/List_of_film_score_composers>\r\n   3984 <http:\/\/dbpedia.org\/resource\/List_of_marine_gastropod_genera_in_the_fossil_record>\r\n   3930 <http:\/\/dbpedia.org\/resource\/List_of_performances_on_Top_of_the_Pops>\r\n   3886 <http:\/\/dbpedia.org\/resource\/List_of_gliders>\r\n   3873 <http:\/\/dbpedia.org\/resource\/List_of_Lepidoptera_of_Romania>\r\n   3839 <http:\/\/dbpedia.org\/resource\/List_of_20th-century_classical_composers>\r\n   3768 <http:\/\/dbpedia.org\/resource\/Rosters_of_the_top_basketball_teams_in_European_club_competitions>\r\n   3740 <http:\/\/dbpedia.org\/resource\/List_of_airports_by_ICAO_code:_K>\r\n   3705 <http:\/\/dbpedia.org\/resource\/List_of_United_States_counties_and_county_equivalents>\r\n   3659 <http:\/\/dbpedia.org\/resource\/List_of_Russian_people>\r\n   3646 <http:\/\/dbpedia.org\/resource\/List_of_Lepidoptera_of_Germany>\r\n   3615 <http:\/\/dbpedia.org\/resource\/List_of_Lepidoptera_of_Switzerland>\r\n   3597 <http:\/\/dbpedia.org\/resource\/List_of_Lepidoptera_of_Slovakia>\r\n   3589 <http:\/\/dbpedia.org\/resource\/List_of_protected_areas_of_China>\r\n   3583 <http:\/\/dbpedia.org\/resource\/List_of_Advanced_Dungeons_&_Dragons_2nd_edition_monsters>\r\n   3541 <http:\/\/dbpedia.org\/resource\/Index_of_U.S._counties>\r\n   3502 <http:\/\/dbpedia.org\/resource\/List_of_Lepidoptera_of_Hungary>\r\n   3499 <http:\/\/dbpedia.org\/resource\/The_opera_corpus>\r\n   3466 <http:\/\/dbpedia.org\/resource\/List_of_German_Christian_Democratic_Union_politicians>\r\n   3466 <http:\/\/dbpedia.org\/resource\/2012%E2%80%9313_UEFA_Europa_League_qualifying_phase_and_play-off_round>\r\n   3439 <http:\/\/dbpedia.org\/resource\/List_of_viruses>\r\n   3439 <http:\/\/dbpedia.org\/resource\/Google_Street_View_in_the_United_States>\r\n   3432 <http:\/\/dbpedia.org\/resource\/2013%E2%80%9314_UEFA_Europa_League_qualifying_phase_and_play-off_round>\r\n   3430 <http:\/\/dbpedia.org\/resource\/List_of_Lepidoptera_of_the_Czech_Republic>\r\n   3392 <http:\/\/dbpedia.org\/resource\/List_of_Lepidoptera_of_Greece>\r\n   3378 <http:\/\/dbpedia.org\/resource\/List_of_surnames_in_Russia>\r\n   3378 <http:\/\/dbpedia.org\/resource\/List_of_film_director_and_actor_collaborations>\r\n   3377 <http:\/\/dbpedia.org\/resource\/2010%E2%80%9311_UEFA_Europa_League_qualifying_phase_and_play-off_round>\r\n   3342 <http:\/\/dbpedia.org\/resource\/2009%E2%80%9310_UEFA_Europa_League_qualifying_phase_and_play-off_round>\r\n   3327 <http:\/\/dbpedia.org\/resource\/Index_of_World_War_II_articles_(U)>\r\n   3321 <http:\/\/dbpedia.org\/resource\/List_of_moths_of_Madagascar>\r\n   3295 <http:\/\/dbpedia.org\/resource\/List_of_country_houses_in_the_United_Kingdom>\r\n   3277 <http:\/\/dbpedia.org\/resource\/List_of_counties_by_U.S._state>\r\n   3273 <http:\/\/dbpedia.org\/resource\/List_of_licensed_and_localized_editions_of_Monopoly:_Europe>\r\n   3255 <http:\/\/dbpedia.org\/resource\/List_of_moths_of_North_America_(MONA_8322-11233)>\r\n   3254 <http:\/\/dbpedia.org\/resource\/List_of_local_administrative_units_of_Romania>\r\n   3236 <http:\/\/dbpedia.org\/resource\/Catalog_of_paintings_in_the_National_Gallery,_London>\r\n   3233 <http:\/\/dbpedia.org\/resource\/IUCN_Red_List_endangered_species_(Animalia)>\r\n   3232 <http:\/\/dbpedia.org\/resource\/IUCN_Red_List_near_threatened_species_(Animalia)>\r\n   3209 <http:\/\/dbpedia.org\/resource\/List_of_Chopped_episodes>\r\n   3201 <http:\/\/dbpedia.org\/resource\/List_of_Lepidoptera_of_Poland>\r\n   3200 <http:\/\/dbpedia.org\/resource\/List_of_directorial_debuts>\r\n   3192 <http:\/\/dbpedia.org\/resource\/List_of_postal_codes_in_Germany>\r\n   3175 <http:\/\/dbpedia.org\/resource\/2010_in_film>\r\n   3163 <http:\/\/dbpedia.org\/resource\/Index_of_philosophy_articles_(R%E2%80%93Z)>\r\n   3156 <http:\/\/dbpedia.org\/resource\/List_of_bannered_U.S._Routes>\r\n   3136 <http:\/\/dbpedia.org\/resource\/Timeline_of_Google_Street_View>\r\n   3135 <http:\/\/dbpedia.org\/resource\/Index_of_Byzantine_Empire-related_articles>\r\n   3129 <http:\/\/dbpedia.org\/resource\/Index_of_Singapore-related_articles>\r\n   3114 <http:\/\/dbpedia.org\/resource\/List_of_postal_codes_of_Switzerland>\r\n   3107 <http:\/\/dbpedia.org\/resource\/2009_in_film>\r\n   3088 <http:\/\/dbpedia.org\/resource\/List_of_University_of_Pennsylvania_people>\r\n   3071 <http:\/\/dbpedia.org\/resource\/List_of_children's_television_series_by_country>\r\n   3065 <http:\/\/dbpedia.org\/resource\/List_of_populated_places_in_the_Netherlands>\r\n   3044 <http:\/\/dbpedia.org\/resource\/List_of_ZX_Spectrum_games>\r\n   3039 <http:\/\/dbpedia.org\/resource\/October_2011_in_sports>\r\n   3022 <http:\/\/dbpedia.org\/resource\/List_of_flora_of_Ohio>\r\n   3019 <http:\/\/dbpedia.org\/resource\/List_of_PlayStation_2_games>\r\n   3016 <http:\/\/dbpedia.org\/resource\/List_of_Lepidoptera_of_Bulgaria>\r\n   3005 <http:\/\/dbpedia.org\/resource\/List_of_voice_actors>\r\n<\/code><\/pre>\n
Observations:<\/h3>\n
The top subjects are clearly dominated by list-like resources. Very big “normal” articles such as those of countries like dbpedia:United_States<\/a> (1375 occurrences as subject) or dbpedia:Germany<\/a> (1331 occurrences as subject) can only be found below ranks of 1518 or 1673. Scrolling through the top subject counts it seems that the amount of “List” vs. non-“List” resources slowly seems to equalize around 1000 occurrences (rank 3800+), but even for subjects that “only” occur ~500 times (rank 21000+) there seem to be ~1\/4 “Lists” still.<\/p>\n
Top 100 Predicates:<\/h2>\n
dbpedia_2_predicate_counts_tops.txt.gz<\/a> (384K)<\/p>\n
149707899 <http:\/\/dbpedia.org\/ontology\/wikiPageWikiLink>\r\n86391520 <http:\/\/www.w3.org\/1999\/02\/22-rdf-syntax-ns#type>\r\n33958849 <http:\/\/www.w3.org\/2002\/07\/owl#sameAs>\r\n18731754 <http:\/\/purl.org\/dc\/terms\/subject>\r\n13926391 <http:\/\/www.w3.org\/2000\/01\/rdf-schema#label>\r\n13494896 <http:\/\/dbpedia.org\/ontology\/wikiPageRevisionID>\r\n13494875 <http:\/\/www.w3.org\/ns\/prov#wasDerivedFrom>\r\n13494819 <http:\/\/dbpedia.org\/ontology\/wikiPageID>\r\n10948106 <http:\/\/dbpedia.org\/ontology\/wikiPageOutDegree>\r\n10948106 <http:\/\/dbpedia.org\/ontology\/wikiPageLength>\r\n10948086 <http:\/\/xmlns.com\/foaf\/0.1\/primaryTopic>\r\n10948086 <http:\/\/xmlns.com\/foaf\/0.1\/isPrimaryTopicOf>\r\n10948086 <http:\/\/purl.org\/dc\/elements\/1.1\/language>\r\n7081593 <http:\/\/dbpedia.org\/ontology\/wikiPageExternalLink>\r\n6473988 <http:\/\/dbpedia.org\/ontology\/wikiPageRedirects>\r\n5926272 <http:\/\/dbpedia.org\/ontology\/abstract>\r\n5925778 <http:\/\/www.w3.org\/2000\/01\/rdf-schema#comment>\r\n4267352 <http:\/\/xmlns.com\/foaf\/0.1\/name>\r\n4041585 <http:\/\/dbpedia.org\/property\/hasPhotoCollection>\r\n3781737 <http:\/\/dbpedia.org\/property\/name>\r\n2342002 <http:\/\/purl.org\/dc\/elements\/1.1\/rights>\r\n2268299 <http:\/\/www.w3.org\/2004\/02\/skos\/core#broader>\r\n2084717 <http:\/\/purl.org\/dc\/elements\/1.1\/description>\r\n1514496 <http:\/\/dbpedia.org\/ontology\/team>\r\n1374565 <http:\/\/xmlns.com\/foaf\/0.1\/depiction>\r\n1374185 <http:\/\/dbpedia.org\/ontology\/thumbnail>\r\n1363398 <http:\/\/dbpedia.org\/ontology\/wikiPageDisambiguates>\r\n1289141 <http:\/\/dbpedia.org\/property\/title>\r\n1231780 <http:\/\/dbpedia.org\/property\/subdivisionType>\r\n1171004 <http:\/\/xmlns.com\/foaf\/0.1\/thumbnail>\r\n1122598 <http:\/\/www.w3.org\/2004\/02\/skos\/core#prefLabel>\r\n1080114 <http:\/\/xmlns.com\/foaf\/0.1\/givenName>\r\n1058532 <http:\/\/www.georss.org\/georss\/point>\r\n1052578 <http:\/\/dbpedia.org\/property\/shortDescription>\r\n1052115 <http:\/\/xmlns.com\/foaf\/0.1\/surname>\r\n1005079 <http:\/\/dbpedia.org\/ontology\/birthPlace>\r\n 995639 <http:\/\/dbpedia.org\/ontology\/birthDate>\r\n 983813 <http:\/\/dbpedia.org\/property\/subdivisionName>\r\n 973597 <http:\/\/dbpedia.org\/ontology\/birthYear>\r\n 968085 <http:\/\/dbpedia.org\/property\/dateOfBirth>\r\n 907869 <http:\/\/www.w3.org\/2003\/01\/geo\/wgs84_pos#lat>\r\n 906919 <http:\/\/www.w3.org\/2003\/01\/geo\/wgs84_pos#long>\r\n 861765 <http:\/\/dbpedia.org\/property\/goals>\r\n 846283 <http:\/\/dbpedia.org\/property\/placeOfBirth>\r\n 846182 <http:\/\/dbpedia.org\/ontology\/isPartOf>\r\n 838381 <http:\/\/dbpedia.org\/property\/birthPlace>\r\n 826348 <http:\/\/dbpedia.org\/property\/years>\r\n 656559 <http:\/\/dbpedia.org\/property\/length>\r\n 653929 <http:\/\/dbpedia.org\/property\/date>\r\n 649375 <http:\/\/xmlns.com\/foaf\/0.1\/homepage>\r\n 643162 <http:\/\/dbpedia.org\/ontology\/careerStation>\r\n 641528 <http:\/\/dbpedia.org\/ontology\/years>\r\n 574296 <http:\/\/dbpedia.org\/property\/birthDate>\r\n 556627 <http:\/\/dbpedia.org\/property\/genre>\r\n 553122 <http:\/\/dbpedia.org\/ontology\/country>\r\n 539366 <http:\/\/dbpedia.org\/property\/clubs>\r\n 529649 <http:\/\/dbpedia.org\/property\/location>\r\n 525787 <http:\/\/dbpedia.org\/property\/rd1Team>\r\n 512507 <http:\/\/dbpedia.org\/ontology\/numberOfGoals>\r\n 501875 <http:\/\/dbpedia.org\/ontology\/genre>\r\n 492028 <http:\/\/dbpedia.org\/ontology\/numberOfMatches>\r\n 453911 <http:\/\/dbpedia.org\/ontology\/deathDate>\r\n 449759 <http:\/\/dbpedia.org\/ontology\/deathYear>\r\n 448696 <http:\/\/dbpedia.org\/property\/dateOfDeath>\r\n 448362 <http:\/\/www.w3.org\/2002\/07\/owl#equivalentClass>\r\n 446799 <http:\/\/dbpedia.org\/property\/caption>\r\n 446238 <http:\/\/www.w3.org\/2000\/01\/rdf-schema#subClassOf>\r\n 440121 <http:\/\/dbpedia.org\/property\/votes>\r\n 437797 <http:\/\/dbpedia.org\/property\/wordnet_type>\r\n 435648 <http:\/\/dbpedia.org\/property\/type>\r\n 431262 <http:\/\/dbpedia.org\/property\/caps>\r\n 418391 <http:\/\/dbpedia.org\/ontology\/utcOffset>\r\n 362378 <http:\/\/dbpedia.org\/property\/percentage>\r\n 362327 <http:\/\/dbpedia.org\/ontology\/type>\r\n 355814 <http:\/\/dbpedia.org\/property\/country>\r\n 346584 <http:\/\/dbpedia.org\/property\/candidate>\r\n 340788 <http:\/\/dbpedia.org\/property\/starring>\r\n 338307 <http:\/\/dbpedia.org\/ontology\/location>\r\n 327879 <http:\/\/dbpedia.org\/ontology\/family>\r\n 326730 <http:\/\/dbpedia.org\/property\/longew>\r\n 326699 <http:\/\/dbpedia.org\/property\/latns>\r\n 315041 <http:\/\/dbpedia.org\/property\/writer>\r\n 314566 <http:\/\/dbpedia.org\/ontology\/starring>\r\n 312958 <http:\/\/dbpedia.org\/property\/label>\r\n 310020 <http:\/\/dbpedia.org\/property\/rd2Team>\r\n 306816 <http:\/\/dbpedia.org\/property\/settlementType>\r\n 306271 <http:\/\/dbpedia.org\/property\/longd>\r\n 306246 <http:\/\/dbpedia.org\/property\/latd>\r\n 306195 <http:\/\/dbpedia.org\/ontology\/populationTotal>\r\n 293237 <http:\/\/dbpedia.org\/property\/team>\r\n 283282 <http:\/\/dbpedia.org\/property\/producer>\r\n 279814 <http:\/\/dbpedia.org\/ontology\/occupation>\r\n 278108 <http:\/\/dbpedia.org\/ontology\/order>\r\n 277006 <http:\/\/dbpedia.org\/property\/episodenumber>\r\n 275430 <http:\/\/dbpedia.org\/property\/longm>\r\n 275416 <http:\/\/dbpedia.org\/property\/latm>\r\n 272562 <http:\/\/dbpedia.org\/ontology\/deathPlace>\r\n 267256 <http:\/\/dbpedia.org\/ontology\/class>\r\n 265454 <http:\/\/dbpedia.org\/property\/timezone>\r\n 264081 <http:\/\/dbpedia.org\/ontology\/viafId>\r\n<\/code><\/pre>\n
Observations:<\/h3>\n
The predicates are clearly dominated by dbpedia-owl:wikiPageWikiLink<\/a> and rdf:type<\/a> relations.
\nWhat’s a bit surprising for me is that dcterms:subject<\/a> occurs less often than rdf:type<\/a>, but my guess is that it’s probably due to YAGO and also hierarchy materialization (Athlete is also a Person). There’s a slight mismatch between dbpedia-owl:wikiPageRevisionID<\/a> and prov:wasDerivedFrom<\/a>. There are more dbpedia-ontology:abstract<\/a>s than rdfs:comment<\/a>s and more geo:lat<\/a>s than geo:long<\/a>s.<\/p>\n
Top 100 Objects:<\/h2>\n
dbpedia_3_object_counts_o9_tops.txt.gz<\/a> (33M)<\/p>\n
10948086 <http:\/\/xmlns.com\/foaf\/0.1\/Document> .\r\n10948086 \"en\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#string> .\r\n6239553 \"1\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n2250659 <http:\/\/dbpedia.org\/class\/yago\/PhysicalEntity100001930> .\r\n2169386 <http:\/\/dbpedia.org\/class\/yago\/Object100002684> .\r\n2155200 <http:\/\/www.w3.org\/2002\/07\/owl#Thing> .\r\n1974654 <http:\/\/www.ontologydesignpatterns.org\/ont\/dul\/DUL.owl#Agent> .\r\n1974654 <http:\/\/dbpedia.org\/ontology\/Agent> .\r\n1816213 <http:\/\/dbpedia.org\/class\/yago\/YagoLegalActorGeo> .\r\n1650316 <http:\/\/xmlns.com\/foaf\/0.1\/Person> .\r\n1649647 <http:\/\/wikidata.dbpedia.org\/resource\/Q5> .\r\n1649647 <http:\/\/wikidata.dbpedia.org\/resource\/Q215627> .\r\n1649647 <http:\/\/schema.org\/Person> .\r\n1649646 <http:\/\/www.ontologydesignpatterns.org\/ont\/dul\/DUL.owl#NaturalPerson> .\r\n1649646 <http:\/\/dbpedia.org\/ontology\/Person> .\r\n1621660 <http:\/\/dbpedia.org\/class\/yago\/Whole100003553> .\r\n1318799 <http:\/\/dbpedia.org\/resource\/Category:Living_people> .\r\n1290718 <http:\/\/dbpedia.org\/class\/yago\/YagoLegalActor> .\r\n1257968 <http:\/\/dbpedia.org\/class\/yago\/YagoPermanentlyLocatedEntity> .\r\n1192248 <http:\/\/www.w3.org\/2004\/02\/skos\/core#Concept> .\r\n1090313 <http:\/\/dbpedia.org\/class\/yago\/LivingThing100004258> .\r\n1090140 <http:\/\/dbpedia.org\/class\/yago\/Organism100004475> .\r\n1046726 <http:\/\/dbpedia.org\/class\/yago\/Person100007846> .\r\n1020287 <http:\/\/dbpedia.org\/class\/yago\/CausalAgent100007347> .\r\n 868376 <http:\/\/dbpedia.org\/resource\/United_States> .\r\n 816854 <http:\/\/www.ontologydesignpatterns.org\/ont\/d0.owl#Location> .\r\n 816837 <http:\/\/schema.org\/Place> .\r\n 816837 <http:\/\/dbpedia.org\/ontology\/Wikidata:Q532> .\r\n 816837 <http:\/\/dbpedia.org\/ontology\/Place> .\r\n 814269 <http:\/\/dbpedia.org\/class\/yago\/YagoGeoEntity> .\r\n 726965 <http:\/\/dbpedia.org\/class\/yago\/Abstraction100002137> .\r\n 658562 <http:\/\/www.ontologydesignpatterns.org\/ont\/dul\/DUL.owl#Situation> .\r\n 643162 <http:\/\/dbpedia.org\/ontology\/CareerStation> .\r\n 561841 <http:\/\/dbpedia.org\/class\/yago\/LivingPeople> .\r\n 547827 <http:\/\/dbpedia.org\/ontology\/PopulatedPlace> .\r\n 547037 \"0\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#integer> .\r\n 539993 \"1\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#integer> .\r\n 531929 <http:\/\/www.opengis.net\/gml\/_Feature> .\r\n 528794 <http:\/\/dbpedia.org\/class\/yago\/Artifact100021939> .\r\n 526256 <http:\/\/www.w3.org\/2003\/01\/geo\/wgs84_pos#SpatialThing> .\r\n 524742 <http:\/\/dbpedia.org\/class\/yago\/Location100027167> .\r\n 505425 <http:\/\/dbpedia.org\/class\/yago\/Region108630985> .\r\n 476724 \"2\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#integer> .\r\n 469006 <http:\/\/dbpedia.org\/ontology\/Settlement> .\r\n 438713 <http:\/\/www.ontologydesignpatterns.org\/ont\/dul\/DUL.owl#InformationEntity> .\r\n 425044 <http:\/\/schema.org\/CreativeWork> .\r\n 425044 <http:\/\/dbpedia.org\/ontology\/Work> .\r\n 419234 <http:\/\/dbpedia.org\/resource\/List_of_sovereign_states> .\r\n 401287 \"N\"@en .\r\n 400317 <http:\/\/dbpedia.org\/resource\/Animal> .\r\n 377252 \"3\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#integer> .\r\n 358891 <http:\/\/dbpedia.org\/class\/yago\/GeographicalArea108574314> .\r\n 350209 <http:\/\/dbpedia.org\/class\/yago\/District108552138> .\r\n 347718 <http:\/\/dbpedia.org\/class\/yago\/Group100031264> .\r\n 336091 <http:\/\/dbpedia.org\/ontology\/Athlete> .\r\n 335320 \"4\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#integer> .\r\n 321614 <http:\/\/dbpedia.org\/class\/yago\/AdministrativeDistrict108491826> .\r\n 313062 <http:\/\/dbpedia.org\/class\/yago\/SocialGroup107950920> .\r\n 302658 <http:\/\/www.ontologydesignpatterns.org\/ont\/dul\/DUL.owl#SocialPerson> .\r\n 302658 <http:\/\/schema.org\/Organization> .\r\n 302658 <http:\/\/dbpedia.org\/ontology\/Organisation> .\r\n 292395 \"28\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 288074 \"29\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 287957 \"27\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 286256 \"5\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#integer> .\r\n 283702 <http:\/\/dbpedia.org\/class\/yago\/Organization108008335> .\r\n 279633 \"yes\"@en .\r\n 279134 <http:\/\/dbpedia.org\/ontology\/SportsTeamMember> .\r\n 279134 <http:\/\/dbpedia.org\/ontology\/OrganisationMember> .\r\n 277439 \"30\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 277025 \"26\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 276000 \"6\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#integer> .\r\n 264578 \"31\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 263773 <http:\/\/dbpedia.org\/class\/yago\/Contestant109613191> .\r\n 261435 <http:\/\/www.ontologydesignpatterns.org\/ont\/dul\/DUL.owl#Organism> .\r\n 261435 <http:\/\/dbpedia.org\/ontology\/Species> .\r\n 260007 \"E\"@en .\r\n 256474 <http:\/\/dbpedia.org\/ontology\/Eukaryote> .\r\n 255993 \"25\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 252172 <http:\/\/dbpedia.org\/resource\/England> .\r\n 249675 \"32\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 240706 <http:\/\/dbpedia.org\/resource\/Iran_Standard_Time> .\r\n 234043 \"24\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 231236 \"33\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 228539 \"7\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#integer> .\r\n 221419 \"0\".\r\n 219180 <http:\/\/dbpedia.org\/class\/yago\/Player110439851> .\r\n 218573 \"23\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 218297 <http:\/\/dbpedia.org\/class\/yago\/PsychologicalFeature100023100> .\r\n 217297 \"34\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 215320 <http:\/\/dbpedia.org\/class\/yago\/Athlete109820263> .\r\n 214359 \"18\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 208654 <http:\/\/dbpedia.org\/class\/yago\/Tract108673395> .\r\n 208383 <http:\/\/dbpedia.org\/resource\/Arthropod> .\r\n 206076 \"22\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 205888 \"8\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#integer> .\r\n 204693 <http:\/\/dbpedia.org\/resource\/Lepidoptera> .\r\n 204220 \"21\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n 203472 <http:\/\/dbpedia.org\/class\/yago\/Instrumentality103575240> .\r\n 202270 \"35\"^^<http:\/\/www.w3.org\/2001\/XMLSchema#nonNegativeInteger> .\r\n<\/code><\/pre>\n
Observations:<\/h3>\n
The object counts are dominated on top with an order of magnitude difference by foaf:Document<\/a> and “en”. The non-negative “1” follows an order of magnitude ahead of the normal “0” and “1” \ud83d\ude09 In between a lot of very useful types follow, and we can see that we have a lot of information about physical things, people, concepts and places. It’s also nice to see http:\/\/wikidata.dbpedia.org\/resource\/Q5<\/a> right under foaf:Person<\/a>, even though the URI doesn’t resolve anymore(?) \ud83d\ude41<\/p>\n
The first “real” “A-Box” resource is dbpedia:United_States<\/a>, followed by dbpedia:Animal<\/a>, dbpedia:England<\/a>, dbpedia:Iran_Standard_Time<\/a>, dbpedia:Arthropod<\/a>, dbpedia:Lepidoptera<\/a>, dbpedia:Canada<\/a>, dbpedia:Insect<\/a>, dbpedia:France<\/a>, dbpedia:United_Kingdom<\/a>, dbpedia:India<\/a>, dbpedia:Germany<\/a>. In general it seems as if apart from ontology types many instances of types country, biological genus and city occur very often as objects.
\nThe top literals seem to be numbers, especially years and single letters.<\/p>\n
Conclusion<\/h2>\n
We’ve seen that it’s sadly not possible to get basic top-degree-counts for big datasets via SPARQL, as the endpoints don’t seem to be optimized for these kind of queries. I hope this changes in the future as it’s quite useful to know degree distributions for all kinds of queries. Especially in the machine learning sector it seems quite essential to know if you’re dealing with a “normal” node or one of the exceptional top nodes that is several orders of magnitude bigger than the rest.<\/p>\n
Hope you enjoyed. Feedback welcome, as always.<\/p>\n
Further reading<\/h2>\n
Thanks for all the feedback<\/a> i got on this post. There are somewhat similar works, that you might be interested in:<\/p>\n