{"id":1816,"date":"2017-08-16T23:40:54","date_gmt":"2017-08-16T23:40:54","guid":{"rendered":"http:\/\/blogs.lincoln.ac.nz\/gis\/?p=1816"},"modified":"2023-05-07T03:41:41","modified_gmt":"2023-05-07T03:41:41","slug":"turning-the-tables-on-text","status":"publish","type":"post","link":"https:\/\/blogs.lincoln.ac.nz\/gis\/turning-the-tables-on-text\/","title":{"rendered":"Turning the Tables on Text"},"content":{"rendered":"

This post demonstrates how to use the field calculator to extract text values from a field and add them to another field.<\/em><\/p>\n

We join our programme already in progress…<\/p>\n

So here’s the thing – I’ve been doing some more analysis on the proximity of tobacco retailers to secondary schools, focusing on areas within 0 – 500 m of each school in Christchurch – all things we’ve covered in earlier posts.\u00a0 I used Network Analysis to create zones around the schools and then used a tool that joins together attributes from two layers based on where they are with respect to each other.\u00a0 In this case, I tabulated the number of retailers within each polygon.\u00a0 Here’s what the layers looks like:<\/p>\n

\"\"<\/a><\/p>\n

My aim is to summarise the number of retailers in each zone by the schools so I’d like to join the attribute table to my schools layer so I have everything in one place, only I’ve got a problem – the attribute that holds the school name is different in the two layers:<\/p>\n

\"\"<\/a><\/p>\n

In the secondary schools layer, it’s just the school name (e.g. “Aranui High School”) but in the walking zones layer, it’s “Aranui High School : 0 – 500”.\u00a0 The “0 – 500” bit came from the network analysis layer and I can’t do much about that.\u00a0 In order to do a table join, I need the school names to be exactly<\/strong> the same.\u00a0 So what’s a GIS analyst to do?\u00a0 This calls for a bit of field calculating<\/a>.\u00a0 I’d like to extract everything to the left of the colon and add that text to a new field.\u00a0 As a first step, I’ll add a new text attribute to my walking zones layer; I’ll call it SchoolName:<\/p>\n

\"\"<\/a><\/p>\n

Next, right-click on the new field name and choose the Field Calculator:<\/p>\n

\"\"<\/a><\/p>\n

Normally, we use the Field Calculator to calculate or add values to fields but we can also use it to extract text or concatenate<\/a> (link together) multiple attributes.\u00a0 Here’s one way to remove the “0 – 500” bit.<\/p>\n

First tick “Show Codeblock”<\/p>\n

\"\"<\/a><\/p>\n

This allows us to write a small snippet of Visual Basic (VB) code to do the job.\u00a0 Python users, note that you can tick the Python parser to use commands in that scripting language.\u00a0 We’ve got two new boxes now: “Pre-Logic Script Code” and “SchoolName =”.\u00a0 We can type our code in the Pre-Logic box and then actually run it on the SchoolName attribute in that box.\u00a0 I’ve set it up this way:<\/p>\n

\"\"<\/a><\/p>\n

Allow me to translate.\u00a0 Take the value in the “Name” attribute field (from the WalkingZones layer) and split the text at the colon character.\u00a0 Take what’s to the left of the colon and refer to it as “newName”.\u00a0 The “newName(0)” in the “SchoolName =” box then runs the code for each record in the table (or only on selected records if there are any) and puts the extracted text in that field when I click OK.\u00a0 After running it, here’s what I’ve got in my Walking Zones table:<\/p>\n

\"\"<\/a><\/p>\n

My SchoolName attribute values now match my NAME values in ChchSecondarySchools and I can proceed to a table join:<\/p>\n

\"\"<\/a><\/p>\n

Et voila!\u00a0 Now I’ve got all the data I need in my secondary schools layer:<\/p>\n

\"\"<\/a><\/p>\n

This is just a small taste of the types of things we can do with the Field Calculator.\u00a0 More can be found here<\/a> if one is inclined towards such geeky level GIS stuff (counseling is available).<\/p>\n

C<\/p>\n","protected":false},"excerpt":{"rendered":"

This post demonstrates how to use the field calculator to extract text values from a field and add them to another field. We join our programme already in progress… So here’s the thing – I’ve been doing some more analysis on the proximity of tobacco retailers to secondary schools, focusing on areas within 0 – […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1816","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/posts\/1816","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/comments?post=1816"}],"version-history":[{"count":1,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/posts\/1816\/revisions"}],"predecessor-version":[{"id":4131,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/posts\/1816\/revisions\/4131"}],"wp:attachment":[{"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/media?parent=1816"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/categories?post=1816"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.lincoln.ac.nz\/gis\/wp-json\/wp\/v2\/tags?post=1816"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}