{"id":1303,"date":"2016-11-09T15:30:42","date_gmt":"2020-02-18T06:55:22","guid":{"rendered":"https:\/\/redfrontdoor.org\/blg2\/?p=1303"},"modified":"2020-02-18T16:51:28","modified_gmt":"2020-02-18T16:51:28","slug":"post-1297","status":"publish","type":"post","link":"https:\/\/redfrontdoor.org\/blog\/?p=1303","title":{"rendered":"Joining and mapping government data sets"},"content":{"rendered":"<div style=\"float:right;margin-left:1.5em\"><a href=\"https:\/\/petermullan.carto.com\/viz\/4822860c-a2d7-11e6-8274-0e233c30368f\/map\"><img loading=\"lazy\" decoding=\"async\" height=\"400\" src=\"\/blog\/wp-content\/uploads\/2016\/11\/Carto-map-screengrab.png\" width=\"240\"><\/a><\/div>\n<p>A while ago, the principal at the primary school my children attend remarked at how time-consuming it was for her to manually collate two different sources of information on schools, and cross-reference against a map of schools&#8217; locations.  Her task, as I understand it, was to find nearby schools who might want to pool their &#8216;resource hours&#8217; with our school and thereby share a resource teacher.  To allow schools to do this, the relevant state bodies publish PDFs listing the resource hours allocated to each school, as well as the number of &#8216;permanent resource posts&#8217;, which is also relevant to the pooling discussions.  The department also have on their website a mechanism for finding a school by map.<\/p>\n<p>Separately, I&#8217;d come across <a href=\"https:\/\/carto.com\/\">Carto<\/a>, which is a site\/service for presenting data-sets on maps.  I thought it would be an interesting project to try to tie all these together, and this is the result, as hosted by the INTO:<\/p>\n<ul>\n<li><a href=\"http:\/\/www.into.ie\/ROI\/NewsEvents\/LatestNews\/Title,40105,en.php\">INTO notice: Data set map showing additional resource hours<\/a>\n<li><a href=\"https:\/\/petermullan.carto.com\/viz\/4822860c-a2d7-11e6-8274-0e233c30368f\/map\">The data-set map itself<\/a>\n<\/li>\n<\/li>\n<\/ul>\n<p>Ideally, the Department of Education and Skills would provide this, since they have all the data already there in machine-friendly form.  Perhaps they will do so in subsequent years.<\/p>\n<h2>Aside: School &#8216;Roll Number&#8217;<\/h2>\n<p>After a bit of experimenting, it looks to me very much like a school&#8217;s Roll Number uses the same check-letter algorithm as the <a href=\"https:\/\/en.wikipedia.org\/wiki\/Personal_Public_Service_Number#Check_character\">Personal Public Service Number<\/a>, which is interesting and, as far as I could find, undocumented.<\/p>\n<h2>Implementation of map<\/h2>\n<p>Some notes on the process I went through to create this are below.  For a revised version, I did the join externally (in Python) to get more appropriate handling of missing values, but the major steps are the same.<\/p>\n<h3>Gather data<\/h3>\n<h4>School location data<\/h4>\n<p>This data-set gives the core school details including latitude and longitude.<\/p>\n<h5>Source<\/h5>\n<p>Go to <a href=\"http:\/\/www.education.ie\/en\/find-a-school\">http:\/\/www.education.ie\/en\/find-a-school<\/a> and select:<\/p>\n<ul>\n<li>School Level: Primary\n<li>Geographical Area: All\n<\/li>\n<\/li>\n<\/ul>\n<p>Before you click &#8216;Search&#8217;, open Chrome Developer Tools (or equivalent in other browser), and select the &#8216;Network&#8217; tab.  Then, back on the main browser window, click &#8216;Search&#8217;.  The &#8216;network&#8217; tab will fill with requests.  Look down the Requests for &#8216;GetSchoolsMap&#8217;, which is a POST request.  Right-click on it, and choose &#8216;Copy Response&#8217;.  You now have a 1.6MB lump of JSON.  Paste it into a new text file <code>schools-list.json<\/code>.<\/p>\n<p>The department does offer a spreadsheet of this information, but it lacks the latitude\/longitude information.  It would be useful if a person in the department could add the lat\/long information to the spreadsheet.<\/p>\n<h5>Convert to CSV<\/h5>\n<p>The <a href=\"https:\/\/github.com\/bennorth\/primary-resource-hours\/blob\/master\/schools_data.py\"><code>schools_data.py<\/code><\/a> Python program (instructions contained in it) will turn this into a CSV.  You should now have <code>schools.csv<\/code>.<\/p>\n<h4>Base posts<\/h4>\n<p>We need the &#8216;permanent resource posts&#8217; information, which the department does make available, but as a PDF.  We need to extract the data from the PDF into a CSV.<\/p>\n<p>It would be useful if a person in the department could make the underlying spreadsheet available rather than just a PDF.<\/p>\n<h5>Source<\/h5>\n<p>Start at <a href=\"http:\/\/www.education.ie\/\">http:\/\/www.education.ie\/<\/a> and go to:<\/p>\n<ul>\n<li>Schools &amp; Colleges (red tab)\n<li>Allocation of Teachers to Schools (first bullet under &#8216;Services&#8217;)\n<li>Primary (big blue button)\n<li>Appendix C (under &#8216;Circular 0007\/2016 Information and relevant forms&#8217;)\n<\/li>\n<\/li>\n<\/li>\n<\/li>\n<\/ul>\n<p>Just now, the final PDF was located at<\/p>\n<ul>\n<li><a href=\"http:\/\/www.education.ie\/en\/Circulars-and-Forms\/Active-Circulars\/cl0007_2016_appendix_C.pdf\">http:\/\/www.education.ie\/en\/Circulars-and-Forms\/Active-Circulars\/cl0007_2016_appendix_C.pdf<\/a>\n<\/li>\n<\/ul>\n<p>but this will vary as it&#8217;s revised, I expect.<\/p>\n<p>The column we need is &#8216;Base Schools for permanent Resource posts&#8217;.<\/p>\n<h5>Convert to CSV<\/h5>\n<p>To pull the actual data out of the PDF, I used the Free Software application <a href=\"http:\/\/tabula.technology\/\">Tabula<\/a>.  Download it  and install.  Then open a web browser and go to <a href=\"http:\/\/127.0.0.1:8080\/\">http:\/\/127.0.0.1:8080\/<\/a> to actually use Tabula.  &#8216;Import&#8217; the &#8216;Appendix C&#8217; PDF just downloaded, and &#8216;Extract Data&#8217; from it.  Drag out a rectangle over the main body of the table on the first page (excluding the headers), adjust by dragging its edges if required, and click &#8216;Repeat this selection&#8217;.  Then click &#8216;Preview &amp; Export Extracted Data&#8217;.  Choose the &#8216;Stream&#8217; extraction method and check everything looks OK (e.g., no groups of numbers all in one cell).  Then export as CSV to <code>base-posts.csv<\/code>.<\/p>\n<p>The Python program <a href=\"https:\/\/github.com\/bennorth\/primary-resource-hours\/blob\/master\/base_posts.py\"><code>base_posts.py<\/code><\/a> adds headers.  It contains instructions.  Finally we get <code>base-posts-headed.csv<\/code>.<\/p>\n<p>(An alternative would be to open <code>base-posts.csv<\/code> in a spreadsheet program and add the headers yourself.)<\/p>\n<h4>Resource hours<\/h4>\n<p>The National Council for Special Education website contains this data, but as a PDF.<\/p>\n<p>It would be useful if a person in the Council could make the underlying spreadsheet available rather than just a PDF.<\/p>\n<h5>Source<\/h5>\n<p>Go to <a href=\"http:\/\/www.ncse.ie\/\">http:\/\/www.ncse.ie\/<\/a> and<\/p>\n<ul>\n<li>Hover over &#8216;For Schools&#8217;\n<li>Click on &#8216;Resource Teaching and SNA Allocations&#8217;\n<li>Click on &#8216;Resource Teaching Hours Allocations 2016\/2017 Primary&#8217; (under &#8216;2016\/2017 School Allocations &#8211; September 2016&#8217;)\n<\/li>\n<\/li>\n<\/li>\n<\/ul>\n<p>Just now, the final PDF was located at<\/p>\n<ul>\n<li><a href=\"http:\/\/ncse.ie\/wp-content\/uploads\/2016\/05\/NCSE-Allocation-of-RT-to-Primary-Schools-September-2016.pdf\">http:\/\/ncse.ie\/wp-content\/uploads\/2016\/05\/NCSE-Allocation-of-RT-to-Primary-Schools-September-2016.pdf<\/a>\n<\/li>\n<\/ul>\n<p>but I imagine this will vary as it&#8217;s revised.<\/p>\n<h5>Convert to CSV<\/h5>\n<p>Use Tabula again to get <code>resource-hours.csv<\/code> and use the Python program <a href=\"https:\/\/github.com\/bennorth\/primary-resource-hours\/blob\/master\/resource_hours.py\"><code>resource_hours.py<\/code><\/a> to turn this into <code>resource-hours-headed.csv<\/code>.  Or, open the first CSV in a spreadsheet program and add the headers yourself.<\/p>\n<h3>Create Carto account<\/h3>\n<p>Go to <a href=\"https:\/\/carto.com\/signup\">https:\/\/carto.com\/signup<\/a> and create an account.<\/p>\n<h3>Upload data-sets to Carto<\/h3>\n<p>At the top of your Carto dashboard, choose Datasets, and then &#8216;New Dataset&#8217;.  Upload the CSVs:<\/p>\n<h4>Schools dataset<\/h4>\n<p>The &#8216;schools&#8217; data-set needs some care to avoid mangling the phone numbers.  Make sure the &#8216;Let CARTO automatically guess data types and content on import&#8217; tick-box is NOT ticked, then browse to <code>schools.csv<\/code>, and click &#8216;connect dataset&#8217;.  Everything from the CSV should appear as &#8216;string&#8217;, with the phone numbers keeping their leading &#8216;0&#8217;.  Unfortunately, this means Carto does not infer the lat\/long information, so we must do that manually.<\/p>\n<h5>Add lat\/long information into column &#8216;<code>the_geom<\/code>&#8216;<\/h5>\n<p>Go to the &#8216;SQL&#8217; tab in the toolbar on the right.  Run the following query:<\/p>\n<pre>\n    UPDATE schools\n    SET the_geom = cdb_latlng(CAST(lat AS NUMERIC), CAST(long AS NUMERIC));\n<\/pre>\n<p>and you should see &#8216;the_geom&#8217; now contains (lat, long) information.  Clicking on &#8216;map view&#8217; should now show the schools&#8217; locations.<\/p>\n<p>(St Columba&#8217;s National School, Galway, has missing lat\/long information so shows up off the coast of Africa.)<\/p>\n<h4>Base resource posts dataset<\/h4>\n<p>Create a new Carto dataset from the <code>base-posts-headed.csv<\/code> file.  This time, do tick the &#8216;automatically guess types&#8217; box.<\/p>\n<h4>Resource hours dataset<\/h4>\n<p>Again, create a dataset from <code>resource-hours-headed.csv<\/code> with &#8216;automatically guess types&#8217;.<\/p>\n<h3>Create one dataset with all information in it<\/h3>\n<p>Go to &#8216;your datasets&#8217;, and choose the &#8216;schools&#8217; dataset.<\/p>\n<h4>Merge in &#8216;permanent resource posts&#8217; column<\/h4>\n<p>Under &#8216;edit&#8217;, choose &#8216;merge with dataset&#8217;, then &#8216;column join&#8217;, then choose the base-posts dataset from the right-hand drop-down.  Choose the &#8216;rollnumber&#8217; entry in both datasets (schools and base-posts), then &#8216;next step&#8217;.<\/p>\n<p>Select only the &#8216;permanent_resource_posts&#8217; entry from the right-hand set, then &#8216;merge datasets&#8217;.  This will create a dataset with &#8216;_merge&#8217; appended to the name, and with the permanent_resource_posts column added.<\/p>\n<h4>Merge in &#8216;resource hours&#8217; column<\/h4>\n<p>Start with the &#8216;_merge&#8217; dataset.  Go through the same process to add the &#8216;hours_sep_2016&#8217; column from the &#8216;resource hours&#8217; dataset.<\/p>\n<h4>Rename final dataset<\/h4>\n<p>Now you have a &#8216;schools_merge_merge&#8217; dataset.  Rename it to something more sane, say &#8216;schools_with_resource_info&#8217;, by clicking on the name.<\/p>\n<h3>Create a map from final dataset<\/h3>\n<p>Click on the &#8216;+&#8217; tab (the top one in the toolbar on the right).  It says &#8216;A map is required to add layers&#8217;; click &#8216;ok, create map&#8217;.<\/p>\n<p>Add the &#8216;schools_with_resource_info&#8217; dataset by selecting it from the list, and clicking &#8216;add layer&#8217;.<\/p>\n<p>At this point you should have a map of Ireland covered in orange dots.<\/p>\n<h3>Customize info pop-up<\/h3>\n<p>Click on the speech-bubble tool in the toolbar on the right (&#8216;infowindow&#8217;).  Click on the &#8221; at the right; this lets you provide custom HTML.  Replace the contents there with<\/p>\n<pre>\n&lt;div class=\"cartodb-popup v2\"&gt;\n  &lt;a href=\"#close\" class=\"cartodb-popup-close-button close\"&gt;x&lt;\/a&gt;\n  &lt;div class=\"cartodb-popup-content-wrapper\"&gt;\n    &lt;div class=\"cartodb-popup-content\"&gt;\n      &lt;h3 style=\"color:#22a\"&gt;{{name}}&lt;\/h3&gt;\n      &lt;p&gt;{{rollnumber}} &amp;mdash; {{address1}}&lt;\/p&gt;\n      &lt;p&gt;{{hours_sep_2016}} hrs{{#permanent_resource_posts}};\n      {{permanent_resource_posts}}\n      full post\/s{{\/permanent_resource_posts}}&lt;\/p&gt;\n      &lt;p&gt;{{principal}}&lt;\/p&gt;&lt;p&gt;{{phone}}&lt;\/p&gt;\n    &lt;\/div&gt;\n  &lt;\/div&gt;\n  &lt;div class=\"cartodb-popup-tip-container\"&gt;&lt;\/div&gt;\n&lt;\/div&gt;\n<\/pre>\n<p>or adjust as you like.<\/p>\n<h3>Publish<\/h3>\n<p>When ready, click the &#8216;publish&#8217; button at top-right.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>A while ago, the principal at the primary school my children attend remarked at how time-consuming it was for her to manually collate two different sources of information on schools, and cross-reference against a map of schools&#8217; locations. Her task, as I understand it, was to find nearby schools who might want to pool their &#8216;resource hours&#8217; with our school and thereby share a resource teacher. To allow schools to do this, the relevant state bodies publish PDFs listing the resource hours allocated to each school, as well as the number of &#8216;permanent resource posts&#8217;, which is also relevant to the pooling discussions. The department also have on their website a mechanism for finding a school by map. Separately, I&#8217;d<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1303","post","type-post","status-publish","format-standard","hentry","category-uncategorized","comments-off"],"_links":{"self":[{"href":"https:\/\/redfrontdoor.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1303","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/redfrontdoor.org\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/redfrontdoor.org\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/redfrontdoor.org\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/redfrontdoor.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1303"}],"version-history":[{"count":1,"href":"https:\/\/redfrontdoor.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1303\/revisions"}],"predecessor-version":[{"id":2159,"href":"https:\/\/redfrontdoor.org\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1303\/revisions\/2159"}],"wp:attachment":[{"href":"https:\/\/redfrontdoor.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1303"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/redfrontdoor.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1303"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/redfrontdoor.org\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1303"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}