{"id":133,"date":"2008-08-29T16:21:22","date_gmt":"2008-08-29T21:21:22","guid":{"rendered":"http:\/\/www.kralidis.ca\/blog\/?p=133"},"modified":"2008-10-13T12:13:08","modified_gmt":"2008-10-13T17:13:08","slug":"pivoting-in-python","status":"publish","type":"post","link":"https:\/\/www.kralidis.ca\/blog\/2008\/08\/29\/pivoting-in-python\/","title":{"rendered":"pivoting in Python"},"content":{"rendered":"<p>I needed to do some pre-processing of some data which involved transposing column names to values.  The condition was that the value for each respective column (frequency count) had to be &gt; 1.<\/p>\n<p>My input was a csv file, and my goal was an output csv file which would feed into a batch database import process.<\/p>\n<pre>ID,DA,NL,PHENOM1,PHENOM2,PHENOM3,PHENOM4\r\n233,99,44,0.00,27.00,12.00,0.00<\/pre>\n<p>The other interesting bit was that only a range of columns applied to the condition; the other columns represented ancillary data.<\/p>\n<p>Enter Python:<\/p>\n<pre>#!\/usr\/bin\/python\r\n\r\nimport sys\r\nimport csv\r\n\r\n# open file and read headers\r\nfPhenomenon = open(\"phenomenon.txt\",\"r\")\r\nsHeaders    = fPhenomenon.readline().replace(r'\"','')\r\naHeaders    = sHeaders.split(\",\")\r\n\r\n# feed the rest to csv\r\ncsvIn  = csv.reader(fPhenomenon)\r\ncsvOut = csv.writer(sys.stdout)\r\n\r\nfor sRowIn in csvIn:\r\n    aRowOut = []\r\n    aPhenomenon = []\r\n    aRowOut.append(sRowIn[0]) # procedure ID\r\n    aRowOut.append(sRowIn[1]) # major drainage area ID\r\n    for nIndexTupleVal, tupleVal in enumerate(sRowIn[3:-1]):\r\n        if (float(tupleVal) &gt; 0): # phenomenon measured at least once\r\n            # add phenomenon name to list\r\n            aPhenomenon.append(aHeaders[nIndexTupleVal+3])\r\n        # add phenomenon list to record\r\n        aRowOut.append(\",\".join(aPhenomenon))\r\n    csvOut.writerow(aRowOut)<\/pre>\n<p>Notes<\/p>\n<ul>\n<li>hooray for raw strings!<\/li>\n<li>enumerate() is great and saves you the trouble of declaring your own counter<\/li>\n<li>like any language, modules\/libraries makes things so easy to work with<\/li>\n<li>I wish the header stuff was a bit cleaner (I should look further into the csv module w.r.t. headers<\/li>\n<\/ul>\n<p>That&#8217;s my hack for the day.  Have a good weekend!<\/p>\n<p><strong>UPDATE<\/strong>: ah, the csv module has a .next() method, which can be used instead of the shoemaker attempt I made above to regularize \/ split \/ store the header list.<\/p>\n<link rel=\"stylesheet\" href=\"http:\/\/cdn.leafletjs.com\/leaflet-0.5\/leaflet.css\" \/>\n<!--[if lte IE 8]>\n  <link rel=\"stylesheet\" href=\"http:\/\/cdn.leafletjs.com\/leaflet-0.5\/leaflet.ie.css\" \/>\n<![endif]-->\n<script src=\"http:\/\/cdn.leafletjs.com\/leaflet-0.5\/leaflet.js\"><\/script>\n<style type=\"text\/css\">#map133 { width: 300px; height: 200px; }<\/style>\n\n<div id=\"map133\"><\/div>\n<script type=\"text\/javascript\">\n  var map133 = L.map('map133').setView([43.620495, -79.513198], 10);\n  L.tileLayer('http:\/\/{s}.tile.osm.org\/{z}\/{x}\/{y}.png', {\n      attribution: '&copy; <a href=\"http:\/\/osm.org\/copyright\">OpenStreetMap<\/a> contributors'\n  }).addTo(map133);\n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>I needed to do some pre-processing of some data which involved transposing column names to values. The condition was that the value for each respective column (frequency count) had to be &gt; 1. My input was a csv file, and my goal was an output csv file which would feed into a batch database import [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,3,10],"tags":[],"class_list":["post-133","post","type-post","status-publish","format-standard","hentry","category-open-source","category-technology","category-website"],"_links":{"self":[{"href":"https:\/\/www.kralidis.ca\/blog\/wp-json\/wp\/v2\/posts\/133","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.kralidis.ca\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.kralidis.ca\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.kralidis.ca\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.kralidis.ca\/blog\/wp-json\/wp\/v2\/comments?post=133"}],"version-history":[{"count":19,"href":"https:\/\/www.kralidis.ca\/blog\/wp-json\/wp\/v2\/posts\/133\/revisions"}],"predecessor-version":[{"id":142,"href":"https:\/\/www.kralidis.ca\/blog\/wp-json\/wp\/v2\/posts\/133\/revisions\/142"}],"wp:attachment":[{"href":"https:\/\/www.kralidis.ca\/blog\/wp-json\/wp\/v2\/media?parent=133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.kralidis.ca\/blog\/wp-json\/wp\/v2\/categories?post=133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.kralidis.ca\/blog\/wp-json\/wp\/v2\/tags?post=133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}