{"id":10293,"date":"2023-07-05T19:09:00","date_gmt":"2023-07-06T00:09:00","guid":{"rendered":"https:\/\/www.rushworth.us\/lisa\/?p=10293"},"modified":"2023-08-28T10:47:55","modified_gmt":"2023-08-28T15:47:55","slug":"neo4j-using-apoc-to-load-html-table-data","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=10293","title":{"rendered":"NEO4J: Using APOC To Load HTML Table Data"},"content":{"rendered":"\n

I’ve been playing around with loading neo4j data from random tables on the web using apoc.load.html<\/a> from the extended APOC library. The first trick to it is knowing how to use jquery to find elements of a webpage — the table named “listtable” then the path down to the data elements (tbody tr td) and column numbers.<\/p>\n\n\n\n

Once you have extracted the data, you can then manipulate it, map it into fields, create relationships, etc.<\/p>\n\n\n\n

UNWIND<\/a> is used as a “for each” loop that allows us to iterate through the result set. <\/p>\n\n\n\n

MERGE<\/a> creates or updates records (which, in this case, means I have a poor data model … someone could well have run in multiple elections and I am not really accommodating those cases well. Since I don’t actually want<\/em> a database of presidential elections but was really just testing some new-to-me functionality … we’re going to ignore these logic problems)<\/p>\n\n\n\n

SET<\/a> adds (or updates) properties of the node.<\/p>\n\n\n

\nCALL apoc.load.html("https:\/\/www.iweblists.com\/us\/government\/PresidentialElectionResults.html",\n{electionyear: "#listtable tbody tr td:eq(0)"\n\t, winner: "#listtable tbody tr td:eq(1)"\n\t, loser: "#listtable tbody tr td:eq(2)"\n\t, electoral_win: "#listtable tbody tr td:eq(3)"\n\t, electoral_lose: "#listtable tbody tr td:eq(4)"\n\t, popular_win: "#listtable tbody tr td:eq(5)"\n\t, popular_delta: "#listtable tbody tr td:eq(6)" }) yield value \n\nWITH value, size(value.electionyear) as rangeup\n\nUNWIND range(0,rangeup) as i WITH value.electionyear[i].text as ElectionYear\n\t, value.winner[i].text as Winner\n\t, value.loser[i].text as Loser\n\t, value.electoral_win[i].text as EC_Winner\n\t, value.electoral_lose[i].text as EC_Loser\n\t, value.popular_win[i].text as Pop_Vote_Winner\n\t, value.popular_delta[i].text as Pop_Vote_Delta\n\nMERGE (ew:Candidate {name: coalesce(Winner,"Unknown")}) \nMERGE (el:Record {name: coalesce(Loser,"Unknown")}) \nSET ew.EC_Votes = coalesce(EC_Winner,"Unknown") \nSET el.EC_Votes = coalesce(EC_Loser,"Unknown")\nSET ew.Year = ElectionYear\nSET el.Year = ElectionYear\n\nWITH *, replace(Pop_Vote_Delta,",","") as Pop_Vote_Delta_Int, replace(Pop_Vote_Winner,",","") as Pop_Winner_Int\n\nSET ew.Pop_Votes = Pop_Winner_Int\nSET el.Pop_Votes = apoc.number.exact.sub(Pop_Winner_Int, Pop_Vote_Delta_Int)\n\nMERGE (ew)-[:DEFEATED]->(el);\n\n<\/pre><\/div>","protected":false},"excerpt":{"rendered":"
I’ve been playing around with loading neo4j data from random tables on the web using apoc.load.html from the extended APOC library. The first trick to it is knowing how to use jquery to find elements of a webpage — the table named “listtable” then the path down to the data elements (tbody tr td) and …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1896],"tags":[1904,1902,1897],"class_list":["post-10293","post","type-post","status-publish","format-standard","hentry","category-neo4j","tag-apoc","tag-cypher","tag-neo4j"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/10293","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10293"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/10293\/revisions"}],"predecessor-version":[{"id":10300,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/10293\/revisions\/10300"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10293"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10293"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10293"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}