tag:blogger.com,1999:blog-26061387417974574572024-03-28T05:48:21.602-07:00charte.cacharte.ca infographic examples, tricks, announcementsAdminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.comBlogger29125tag:blogger.com,1999:blog-2606138741797457457.post-45311864653718004442019-10-17T17:57:00.003-07:002019-10-17T17:57:33.906-07:00Canada mortgages: CMHC-backed portion shrinks, slowly taking taxpayers off the hook<div style="text-align: center;">
<embed src='https://charteca.blob.core.windows.net/pubs/FZCgfAYLqW3F4zTf' type='image/svg+xml' style='width:100%;'></embed>
</div>Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-55040895929354130772019-06-26T18:52:00.003-07:002021-03-14T19:59:13.391-07:00Step line chartsWe are excited no announce step line chart support:
<br />
<br />
<div style="text-align: center;">
<embed src='https://charteca.blob.core.windows.net/pubs/RUhmh9fnePdp4XaS' type='image/svg+xml' style='width:100%;'></embed>
</div>
<br />
<br />
Use "Line options" panel to change line style:
<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilnTDekaiZxlJARCJznXPAgiMBRisCCtbXnqxgcepLUs2NpqZMtHpuZY004qYK_oTeafQpiQoUAHEJ0Y4VwfP9Zd7T-lp_wAL1Lu57auJuQ38a619X1xK-GB1Ob-xXeOI5cOx7wef_bzRB/s1600/2019-06-26-step-line.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilnTDekaiZxlJARCJznXPAgiMBRisCCtbXnqxgcepLUs2NpqZMtHpuZY004qYK_oTeafQpiQoUAHEJ0Y4VwfP9Zd7T-lp_wAL1Lu57auJuQ38a619X1xK-GB1Ob-xXeOI5cOx7wef_bzRB/s320/2019-06-26-step-line.jpg" width="320" height="161" data-original-width="1585" data-original-height="796" /></a></div>
Sources:<br />
<a href="https://fred.stlouisfed.org/series/HDTGPDCAQ163N" target="_blank">Canada houseld debt to GDP, Federal Reserve Bank of St Louis</a><br />
<a href="https://fred.stlouisfed.org/series/HDTGPDUSQ163N" target="_blank">US houseld debt to GDP, Federal Reserve Bank of St Louis</a><br />
<a href="http://www.cbrates.com/canada/" target="_blank">BoC rate</a><br />
<a href="https://www.thebalance.com/fed-funds-rate-history-highs-lows-3306135" target="_blank">Fed rate</a><br />
Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com1tag:blogger.com,1999:blog-2606138741797457457.post-37792222009155949882019-05-01T17:53:00.003-07:002021-03-14T19:19:31.573-07:00Canada federal policies and housing/debt crisis timelineHere is a cheatsheet: how selected federal policies have changed and who was at the helm. Updated: March 2021.
<br />
<br />
<div><embed src="https://charteca.blob.core.windows.net/pubs/Bcf3ZpgEmGKmUWa2" style="background: rgb(255, 255, 255); width: 100%;" type="image/svg+xml"></embed></div>
<br />
<br />
Governor of Bank of Canada:<br />
David Dodge February 1, 2001 – January 31, 2008<br />
Mark Carney February 1, 2008 – June 3 2013<br />
Stephen Poloz June 3, 2013 - June 3, 2020<br />
Tiff Macklem June 3, 2020 - now<br />
<br />
<br />
<br />
CMHC President:<br />
Karen Kinsley 2003-2013<br />
Evan Siddal January 1, 2014 - present<br />
<br />
<br />
<br />
Minister of Finance of Canada:<br />
Ralph Goodale December 11, 2003 – February 6, 2006<br />
Jim Flaherty February 6, 2006 – March 18, 2014<br />
Joe Oliver March 19, 2014 – November 4, 2015<br />
Bill Morneau November 4, 2015 - August 18, 2020<br />
Chrystia Freeland August 18, 2020 - now<br />
<br />
<br />
<br />
2004-01<span style="white-space: pre;"> </span>Department of Finance<span style="white-space: pre;"> </span>CMHC insurance limit raised<span style="white-space: pre;"> </span>The Department of Finance raises the aggregate amount of insurance that CMHC can have outstanding to $300 billion from $250 billion.<br />
2006-01<span style="white-space: pre;"> </span>Department of Finance<span style="white-space: pre;"> </span>CMHC insurance limit raised<span style="white-space: pre;"> </span>The Department of Finance raises the aggregate amount of insurance that CMHC can have outstanding to $350 billion from $300 billion.<br />
2008-03<span style="white-space: pre;"> </span>Department of Finance<span style="white-space: pre;"> </span>CMHC insurance limit raised<span style="white-space: pre;"> </span>The Department of Finance raises the aggregate amount of insurance that CMHC can have outstanding to $450 billion from $350 billion.<br />
2009-03<span style="white-space: pre;"> </span>Department of Finance<span style="white-space: pre;"> </span>CMHC insurance limit raised<span style="white-space: pre;"> </span>The Department of Finance raises the aggregate amount of insurance that CMHC can have outstanding to $600 billion from $450 billion.<br />
2020-12<span style="white-space: pre;"> </span>Department of Finance<span style="white-space: pre;"> </span>CMHC insurance limit raised<span style="white-space: pre;"> </span>The Department of Finance raises the aggregate amount of insurance that CMHC can have outstanding to $750 billion from $600 billion.<br />
<br />
2006-06<span style="white-space: pre;"> </span>CMHC<span style="white-space: pre;"> </span>Expansion of inured mortgages<span style="white-space: pre;"> </span>The CMHC announces mortgages with amortizations of up to 30 and 35 years can be insured, as well as interest-only mortgages (for the first 10 years of the mortgage).<br />
2006-11<span style="white-space: pre;"> </span>CMHC<span style="white-space: pre;"> </span>Expansion of inured mortgages<span style="white-space: pre;"> </span>The CHMC announces mortgages with amortizations up to 40 years can be insured, as well as zero-down payment mortgages.<br />
2008-07<span style="white-space: pre;"> </span>Department of Finance<span style="white-space: pre;"> </span>Amortization reduction<span style="white-space: pre;"> </span>The maximum amortization period is shortened from 40 years to 35 years.<br />
2011-01<span style="white-space: pre;"> </span>Department of Finance<span style="white-space: pre;"> </span>Amortization reduction<span style="white-space: pre;"> </span>The maximum amortization period is shortened to 30 years from 35 years on insured mortgages.<br />
2012-01<span style="white-space: pre;"> </span>OSFI<span style="white-space: pre;"> </span>Amortization reduction<span style="white-space: pre;"> </span>The maximum amortization period is shortened to 25 years from 30 years on insured mortgages.<br />
<br />
2010-02<span style="white-space: pre;"> </span>Department of Finance<span style="white-space: pre;"> </span>Reduction of insured refinancing<span style="white-space: pre;"> </span>The maximum amount for insured refinances is reduced to 90% from 95%.<br />
2011-01<span style="white-space: pre;"> </span>Department of Finance<span style="white-space: pre;"> </span>Reduction of insured refinancing<span style="white-space: pre;"> </span>The maximum amount for insured refinances is reduced to 85% from 90%.<br />
2012-06<span style="white-space: pre;"> </span>OSFI<span style="white-space: pre;"> </span>Reduction of insured refinancing<span style="white-space: pre;"> </span>The maximum amount for insured refinances is reduced to 80% from 85%.<br />
<br />
<br />
<br />
Sources:<br />
<a href="https://www.ratespy.com/history-of-mortgage-rule-changes-03255560" target="_blank">History of Mortgage Rule Changes in Canada, RateSpy.com</a><br />
<a href="https://fred.stlouisfed.org/series/HDTGPDCAQ163N" target="_blank">Canada houseld debt to GDP, Federal Reserve Bank of St Louis</a><br />
<a href="https://housepriceindex.ca/" target="_blank">Teranet Housing Price Index</a><br />
Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com3tag:blogger.com,1999:blog-2606138741797457457.post-60886913798881768622018-08-23T18:17:00.000-07:002018-08-23T18:17:23.746-07:00Collaboration scenario: sharing and cloning<div class="separator" style="clear: both; text-align: left;">
Recently, charte.ca feature set got new additions: chart cloning and chart sharing. Cloning is getting an exact copy of a source chart. Sharing is allowing other users to edit your chart.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Consider an example: Bob is working on a chart and wants Alice to help him. Bob opens the chart in the editor, finds "Sharing" panel and clicks "Share with another user" button. He is presented with a popup that displays a URL of the invitation that Bob has to send Alice so she gets access to his chart. Bob presses "Confirm":</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEig515j-TboprWPa0CK-uemWTWb1Dt2FjrMaCxQJbfw-6wIY6-uspTOpI750dNEu0Zkr9rsL4lUdNhdv2Rrkw_z07x7C9ynJPPFSX9Fc1U-BYq9TFupqdVkOVD2qLYr7Bplxk7QigcVSrWC/s1600/01-bob-share.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="574" data-original-width="936" height="196" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEig515j-TboprWPa0CK-uemWTWb1Dt2FjrMaCxQJbfw-6wIY6-uspTOpI750dNEu0Zkr9rsL4lUdNhdv2Rrkw_z07x7C9ynJPPFSX9Fc1U-BYq9TFupqdVkOVD2qLYr7Bplxk7QigcVSrWC/s320/01-bob-share.png" width="320" /></a></div>
<br />
Now, "Sharing" panel displays the invitation in the list. If Bob changes his mind, he can cancel this invitation by clicking the "x" button on the right of the invitation:<br />
<br />
<a name='more'></a><br /><br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOt-ipWuYHc-fuY0fxZmAqakLjUl_7FM-ID3SH_AM_kiasQNmVj8l9F6CXiPHyw7hI9jj-CkBV3Qz0mgVHnCGjuIxJS5SueKo4J4KnGizE8O8Pm88jnJ-UWQqjta0_W1KhdoeqoA__dGCf/s1600/02-bob-share-done.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="581" data-original-width="624" height="296" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOt-ipWuYHc-fuY0fxZmAqakLjUl_7FM-ID3SH_AM_kiasQNmVj8l9F6CXiPHyw7hI9jj-CkBV3Qz0mgVHnCGjuIxJS5SueKo4J4KnGizE8O8Pm88jnJ-UWQqjta0_W1KhdoeqoA__dGCf/s320/02-bob-share-done.png" width="320" /></a></div>
<br />
But we are assuming Bob is determined to share his chart and does not cancel the invitation. Alice receives invitation URL via email (or some other media) and opens it in her browser. She is presented with a login screen if needed and, eventually, she can see the shared chart in her "Charts -> Shared with me" list, and she can see who shared this chart with her - it was Bob:<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCdrZJT7hcS5ZAlGQBg1sWi-cXWwsdIWGbOnxVKNwMOJSNgXJlwPknRli5PJmsEkd_AgfV6KgYevXSxKgox_pE7OeGH-1FaIaiST-7ul6Qzg6IDarBUDEwIwspOhSDC_qMuubj7PrNkeyR/s1600/03-alice-accepted.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="279" data-original-width="907" height="98" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCdrZJT7hcS5ZAlGQBg1sWi-cXWwsdIWGbOnxVKNwMOJSNgXJlwPknRli5PJmsEkd_AgfV6KgYevXSxKgox_pE7OeGH-1FaIaiST-7ul6Qzg6IDarBUDEwIwspOhSDC_qMuubj7PrNkeyR/s320/03-alice-accepted.png" width="320" /></a></div>
<br />
Bob, on his side, re-loads the chart and now can see that Alice accepted the invitation. Again, if he changes his mind, he can leave Alice without access to this chart by pressing "x", but let's assume he is happy that Alice got access to it:<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOOOgiFROqu4buMn76B3-cuEpx6N6Or6s28zWNXwb3hTzSHvWpdprPxdYNY_Q_Yc2gSoMmm2fN8Y-vcTmP0kDuyprqFHZ2pPqBIfr3kBXrSCwqdN8jmAPQqYtZrHH6ENziIxAxhSjr9hsp/s1600/04-bob-see-alice.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="172" data-original-width="613" height="89" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOOOgiFROqu4buMn76B3-cuEpx6N6Or6s28zWNXwb3hTzSHvWpdprPxdYNY_Q_Yc2gSoMmm2fN8Y-vcTmP0kDuyprqFHZ2pPqBIfr3kBXrSCwqdN8jmAPQqYtZrHH6ENziIxAxhSjr9hsp/s320/04-bob-see-alice.png" width="320" /></a></div>
<br />
Alice opens the chart in the editor by clicking on it in the shared chart list. She can edit it and accomplish her mission of helping Bob. When Bob is happy with the edits, he publishes the chart.<br />
<br />
Please note that Bob is the only owner of the chart so he is the only person who can publish it or share it with others. What if Alice wants to publish it, share it with her friends or just save it in her chart repository for future use? Cloning is the answer - Alice can press "Clone" button:<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1HimKJ4jiU2l-eXwluf9YWq8q_h_UdAlG-D-X0LlD3i6X65eYgzBh2XU1h75qu_ub32yZLcQszm_r94_EMFNIYPPqzbmg4PgJ6gUKcDR3LU2Lm5DIag2pOIuiTSgobWwrDhcHkI6np8JL/s1600/05-alice-edit.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="460" data-original-width="625" height="235" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1HimKJ4jiU2l-eXwluf9YWq8q_h_UdAlG-D-X0LlD3i6X65eYgzBh2XU1h75qu_ub32yZLcQszm_r94_EMFNIYPPqzbmg4PgJ6gUKcDR3LU2Lm5DIag2pOIuiTSgobWwrDhcHkI6np8JL/s320/05-alice-edit.png" width="320" /></a></div>
<br />
The editor offers Alice to choose a name for the cloned chart, and Alice clicks "Create":<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkvbjORx-PmUQr7lvIdIFyhE9YHjRzM3Ta8uibEF5pbUgon0_TWilIf4BUTJfv__ki419ZHVHrAVl3Wq-YQbmBfYG7cncl3VtAUJCM5HmqGVYo26pRKgqsS7UlHJPZB6g4F3WJ2la97A5N/s1600/06-alice-click-clone.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="457" data-original-width="815" height="179" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkvbjORx-PmUQr7lvIdIFyhE9YHjRzM3Ta8uibEF5pbUgon0_TWilIf4BUTJfv__ki419ZHVHrAVl3Wq-YQbmBfYG7cncl3VtAUJCM5HmqGVYo26pRKgqsS7UlHJPZB6g4F3WJ2la97A5N/s320/06-alice-click-clone.png" width="320" /></a></div>
<br />
Now, if Alice goes to the "Charts -> My charts" screen, she will see cloned chart:<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh11jMv0U4pWUPErn2bIar_B_h0Ub6upJANlOg1PE1tJPPaTDC8YZSdBj_J4orSS4dM1ds4ZMlp2blyULYA7x_N5D12jgVMD1AbIs9BDuMJsz0QjCmRt8GRB_T-qIuEV8qpt8mMZJr5nBCD/s1600/07-alice-cloned.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="280" data-original-width="534" height="167" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh11jMv0U4pWUPErn2bIar_B_h0Ub6upJANlOg1PE1tJPPaTDC8YZSdBj_J4orSS4dM1ds4ZMlp2blyULYA7x_N5D12jgVMD1AbIs9BDuMJsz0QjCmRt8GRB_T-qIuEV8qpt8mMZJr5nBCD/s320/07-alice-cloned.png" width="320" /></a></div>
<br />
Alice is the owner of the cloned chart and has full control of it.Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com3tag:blogger.com,1999:blog-2606138741797457457.post-33239633405214937832018-05-07T11:32:00.000-07:002018-05-08T11:33:15.212-07:00Javascript ES6 only pleaseStarting from May 7, 2018, charte.ca charts are not displayed in browsers that do not support major <a href="https://caniuse.com/#search=es6" target="_blank">ES6</a> features. In plain English: Internet Explorer users will not be able to see charts created by charte.ca, Edge users should be fine. Also, the editor now requires ES6 as well (which is not much of a change since the editor requires <a href="https://developer.mozilla.org/en-US/docs/Web/HTTP/Basics_of_HTTP/Data_URIs" target="_blank">data uri</a> support and IE and Edge are both lacking it).<br />
<br />
Charts have just become 5-10% smaller in size.Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com1tag:blogger.com,1999:blog-2606138741797457457.post-71373291948753527662018-01-29T11:08:00.000-08:002018-01-29T11:08:20.271-08:00charte.ca as an educational resource: motion charts visualizing World Bank dataHigh school students extract data from the World Bank dataset and create a motion chart that shows the relationship between life expectancy and expenditure on health in different countries. See discussion in Information Technology Teachers' Mailing List:<br />
<a href="https://www.blogger.com/goog_1238500064"><br /></a>
<a href="https://edulists.com.au/pipermail/yr11it/2017-August/003375.html">https://edulists.com.au/pipermail/yr11it/2017-August/003375.html</a><br />
<br />
A set of instructions for Yr11 Information Technology students:<br />
<br />
<a href="http://www.edulists.com.au/pipermail/yr11it/attachments/20170820/71ebeb96/VisualisingBigDatausingCharte-steps-0001.docx">http://www.edulists.com.au/pipermail/yr11it/attachments/20170820/71ebeb96/VisualisingBigDatausingCharte-steps-0001.docx</a><br />
<div>
<br /></div>
Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-25709206962217605982017-11-30T17:25:00.000-08:002017-11-30T22:47:17.716-08:00Visualizing Google analytics report: motion chart with drilldownsBelow is a motion chart with drilldowns visualizing Google Analytics report for a fictional website. Click and hold on any bubble to see acquisition and conversion details for a specific channel: Direct, Organic Search, Referral, Social.<br />
<br />
<div style="text-align: center;">
<embed src="https://charteca.blob.core.windows.net/pubs/5KZgM2TvccMA8tUf" style="height: 600px; width: 800px;" type="image/svg+xml"></embed>
</div>
<br />
<h2>
Why motion charts?</h2>
<br />
Motion chart allows efficient and interactive exploration and visualization of multi-dimensional data and can make it easier to notice an important trend. Google has done a great job <a href="https://analytics.googleblog.com/2008/11/new-features-are-now-available-in-your.html" target="_blank">explaining how motion chart can be useful in web analytics</a> and providing a tool for building motion charts based on analytics reports.<br />
<br />
<a name='more'></a><br />
<br />
<h2>
Why not using motion charts provided by Google Analytics?</h2>
<br />
There are a few reasons why a visualization built with charte.ca may be a better choice than Google motion chart:<br />
<ul>
<li>it does not require Adobe Flash</li>
<li>it can be easily shared, privately or publicly</li>
<li>it provides drilldown capabilities</li>
<li>it is highly customizable</li>
</ul>
<br />
<ul>
</ul>
<h2>
Creating the visualization</h2>
<div>
<br />
Below are the steps I followed to create the chart above.<br />
<br /></div>
<h3>
Preparing the data</h3>
<div>
<br />
In my Google Analytics console, I created a report that shows the following metrics for each acquisition channel:</div>
<div>
<ul>
<li>number of sessions</li>
<li>bounce rate</li>
<li>goal completion rate</li>
<li>goal completions</li>
</ul>
</div>
<div>
Metric numbers are grouped by month for the period of two years: 2016 and 2017 (see "Secondary dimension: Month of Year" dropdown in the top left corner of the report. Google shows months in a "YYYYMM" format, which may not be the most readable option, but works well for reporting purposes.</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIF2o7_YktUKI2t7R1h7LnBEMirrHQ0WsqlxHqNkfnOqZkjhQVersGuPLCTEN4KINDRMeJSaz0luQ_66dbLjPyhBRpCgc6WkpC575cxpv4wLW-oLApnAFOr7CBt1Uj8UxPJuaPWV0-pOa-/s1600/web-analytics-screenshot.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="805" data-original-width="1452" height="177" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIF2o7_YktUKI2t7R1h7LnBEMirrHQ0WsqlxHqNkfnOqZkjhQVersGuPLCTEN4KINDRMeJSaz0luQ_66dbLjPyhBRpCgc6WkpC575cxpv4wLW-oLApnAFOr7CBt1Uj8UxPJuaPWV0-pOa-/s320/web-analytics-screenshot.png" width="320" /></a></div>
<div>
<br /></div>
<div>
I exported the report as Excel file using Export -> Excel menu command:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLeAwMNT3z4nCiSCRTXrOzCZkL74fngQtuPcsnBRbb_IFnMbimoXYbVb9LP-83rJ2PWAaxYIRegaXa0QZOFZdvUhlXqXjlJatW2jk5J4PC5mCDkZykSpjz4ekMleTqq9pWFN1mzm_4G1sT/s1600/web-analytics-screenshot-export.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="200" data-original-width="246" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgLeAwMNT3z4nCiSCRTXrOzCZkL74fngQtuPcsnBRbb_IFnMbimoXYbVb9LP-83rJ2PWAaxYIRegaXa0QZOFZdvUhlXqXjlJatW2jk5J4PC5mCDkZykSpjz4ekMleTqq9pWFN1mzm_4G1sT/s1600/web-analytics-screenshot-export.png" /></a></div>
<div>
<br /></div>
<div>
For the sake of this example, I replaced the real data with fictional numbers and submitted the spreadsheet to Google Docs and <a href="https://docs.google.com/spreadsheets/d/17ckQnvBw41X_3gHV-PadbYgNSm9TRlVOj7W_poStcXg" target="_blank">made it publicly available</a>, see the "Snapshot" tab. This will be the data for the top-level motion chart with the following metrics:</div>
<div>
<ul>
<li>Bounce rate (x-axis)</li>
<li>Goal 1 Conversion Rate (y-axis)</li>
<li>Sessions (z-axis, bubble size)</li>
<li>Month of year (category)</li>
<li>Channel (bubble color shows the type of channel)</li>
</ul>
</div>
<div>
For channel-specific sub-charts (drilldowns), I decided to work with the following metrics:</div>
<div>
<ul>
<li>Sessions</li>
<li>Goal 1 Completions</li>
<li>Bounce Rate</li>
<li>Goal 1 Conversion Rate</li>
</ul>
</div>
<div>
so I created separate tabs for all four channels and populated them with those four metrics. See tabs named "Direct", "Organic Search", "Referral", "Social".<br />
<br /></div>
<h3>
Creating the top-level chart (motion chart)</h3>
<div>
<br />
I opened the <a href="https://editor.charte.ca/" target="_blank">charte.ca editor</a> and created a new motion chart (let's call it "blog-web-analytics-summary"):</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-0Hlfcm06WvpRRCTVdtJz3rwYbR3SkV7DXJabeJgzLljK_4m2Xc6ykMHSLRNG8_exjcT180KAGV7I-3whsBKo5vOjDWnQ7dOxEHsemazkF79D0WmzsNB8jk8vsUHWv0_ZOVra3lBufZ-Q/s1600/summary-create.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="843" data-original-width="789" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-0Hlfcm06WvpRRCTVdtJz3rwYbR3SkV7DXJabeJgzLljK_4m2Xc6ykMHSLRNG8_exjcT180KAGV7I-3whsBKo5vOjDWnQ7dOxEHsemazkF79D0WmzsNB8jk8vsUHWv0_ZOVra3lBufZ-Q/s320/summary-create.png" width="299" /></a></div>
<div>
<br /></div>
<div>
In the "Data" panel, I clicked the "Import" button and copy-and-pasted "Snaphot" data to the text area:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBHj7ND2jLHG3kLc7Qii7_iMrqB5x0V4On8OXMTwgziBrXi-e8kobpbbxznYCUmshg8HhtMQT7UpbqcXaukmA9BUyJi-pDucdcdzVv5HdTvPlji-Yt5neXDVFZR4sK_VQIFqeUj2rvUqN9/s1600/summary-import-start.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="827" data-original-width="1600" height="165" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBHj7ND2jLHG3kLc7Qii7_iMrqB5x0V4On8OXMTwgziBrXi-e8kobpbbxznYCUmshg8HhtMQT7UpbqcXaukmA9BUyJi-pDucdcdzVv5HdTvPlji-Yt5neXDVFZR4sK_VQIFqeUj2rvUqN9/s320/summary-import-start.png" width="320" /></a></div>
<div>
<br /></div>
<div>
On step 2 of the import wizard, I mapped data columns to motion chart dimensions as follows:</div>
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfLW44FLArc5z0V0yABXhk2ZAmiyMNkACIxBDtYK1PygD6gAbbrypiJPksICjf5f36g_gHOi3Ahryf85XUf34dpJvvVPhf4RyVRfxQw_KKfyDfW3i0kDI_55jDkMsAHDX7_ltDGTNG94m4/s1600/summary-import-columns.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="684" data-original-width="855" height="256" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfLW44FLArc5z0V0yABXhk2ZAmiyMNkACIxBDtYK1PygD6gAbbrypiJPksICjf5f36g_gHOi3Ahryf85XUf34dpJvvVPhf4RyVRfxQw_KKfyDfW3i0kDI_55jDkMsAHDX7_ltDGTNG94m4/s320/summary-import-columns.png" width="320" /></a></div>
<div>
<br /></div>
<div>
<br />
I completed the wizard using default settings and ended up with a motion chart displaying four bubbles: one bubble per channel. I tweaked chart appearance (size, colors, fonts, title/subtitle), switched to log scale for x-axis, turned off automatic animation start, and added trend lines (see "XY regression options" panel) for the bubbles. Not surprisingly, since my numbers are fake, there is a good correlation between bounce rate and goal conversion rate. Please do not be puzzled by the shape of the linear trends - this is what they look like when one of the axis is logarithmic.<br />
<br />
<h3>
Creating sub-charts</h3>
<br />
I navigated back to "My charts" and created a new dual-axis chart "blog-web-analytics-direct":<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhArJ321YWwK6yZzcRwyx5XETEhAc9TyIHiJwrDYUzAqZGLlX82RR2ZI1wH43yPpKzLCQKeHg5BImwyKCGR6vJMfPT4Ptj37afw0D9shgeeYPHdUHEJKf2m1LDOg5CdhZuuDRoHj6tEkHaS/s1600/details-create.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="684" data-original-width="792" height="276" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhArJ321YWwK6yZzcRwyx5XETEhAc9TyIHiJwrDYUzAqZGLlX82RR2ZI1wH43yPpKzLCQKeHg5BImwyKCGR6vJMfPT4Ptj37afw0D9shgeeYPHdUHEJKf2m1LDOg5CdhZuuDRoHj6tEkHaS/s320/details-create.png" width="320" /></a></div>
<br />
After copying "Direct" data from the Google spreadsheet right into the chart data spreadsheet<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiq8PrC_KlMqbRfhzDMWLFxIl3bIDD0SomZK5v9I8_f4mqQz_HTq9on9DqmCQ1ZTfBp7fulwpMd8blcR943xEB1nfU938U23PqpbcO5kT_gEE_f_YEGskGPE442CE55gzsITrE_aV3l1S2f/s1600/details-copy-data.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="840" data-original-width="1212" height="221" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiq8PrC_KlMqbRfhzDMWLFxIl3bIDD0SomZK5v9I8_f4mqQz_HTq9on9DqmCQ1ZTfBp7fulwpMd8blcR943xEB1nfU938U23PqpbcO5kT_gEE_f_YEGskGPE442CE55gzsITrE_aV3l1S2f/s320/details-copy-data.png" width="320" /></a></div>
<br />
I set chart size to 800x600 (it must be the same as the top-level chart's) and played a bit with chart appearance, title, axis labels and made sure that absolute numbers (sessions, goal completions) are bars and percent values (bounce rate, conversion rate) are lines:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIE8Ef5eg8-e-iAAHxupM6K4HYJeitHy5p4VdsGmDM2GfqijzlQZqGiy1XBdq7KwzZqE-IEta7xYAG0nyqWZATIpIojvN1y5CAliMZiYwV1EU2D4m4j5Pemk7Jnmz6U96RVEu_PNOwvKo_/s1600/details-bar-line.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="450" data-original-width="541" height="266" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgIE8Ef5eg8-e-iAAHxupM6K4HYJeitHy5p4VdsGmDM2GfqijzlQZqGiy1XBdq7KwzZqE-IEta7xYAG0nyqWZATIpIojvN1y5CAliMZiYwV1EU2D4m4j5Pemk7Jnmz6U96RVEu_PNOwvKo_/s320/details-bar-line.png" width="320" /></a></div>
<br />
I repeated these steps for other channels: Organic Search, Referral, Social. This is where "Clone" command comes handy.<br />
<br />
<h3>
Configuring drilldowns</h3>
<br />
I opened the top-level chart again and clicked on the drilldown selector for "Direct" series:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaDsU4mzO3DoNpHwjocU-MerGTTzRQ-WQw_aFnmGoan9gI14x-ftaKNLOZ3iB3ITHVDNAfTXuSfaUTmpfK2mC3BSWzEiLOLPF-F3kkG7JxMoyTlMMUU7XPD6UUPFeMLLZ_Z1TPzWLVRpn2/s1600/summary-drilldown.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="598" data-original-width="785" height="243" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhaDsU4mzO3DoNpHwjocU-MerGTTzRQ-WQw_aFnmGoan9gI14x-ftaKNLOZ3iB3ITHVDNAfTXuSfaUTmpfK2mC3BSWzEiLOLPF-F3kkG7JxMoyTlMMUU7XPD6UUPFeMLLZ_Z1TPzWLVRpn2/s320/summary-drilldown.png" width="320" /></a></div>
<br />
In the selection popup, I clicked "Search" and picked the "blog-web-analytics-direct" chart:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiP_Y6ucgCkhcpScq8K9w8p0if_WAEDiHHqjoyOnKURmQ19MLB5FvqX4N9icmPSS5dcN7HlOYuBmEFnwG0AYdtxL-23JNH7F4XmJnq7GSxpxXCsv1JhY4ED5v3pdCpjYPoQZg5-jIl5lVxE/s1600/summary-drilldown-select.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="511" data-original-width="1114" height="146" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiP_Y6ucgCkhcpScq8K9w8p0if_WAEDiHHqjoyOnKURmQ19MLB5FvqX4N9icmPSS5dcN7HlOYuBmEFnwG0AYdtxL-23JNH7F4XmJnq7GSxpxXCsv1JhY4ED5v3pdCpjYPoQZg5-jIl5lVxE/s320/summary-drilldown-select.png" width="320" /></a></div>
<br />
I selected correspondent charts for other three drilldowns and saved the top-level chart. I tested the drilldowns right from the editor by clicking and holding a bubble.<br />
<br />
<h3>
Sharing it</h3>
<br />
The visualization was ready. I published the top-level chart and embedded it into this post:<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: dodgerblue; font-weight: bold;"><embed</span> <span style="color: dodgerblue;">src=</span><span style="color: #aa5500;">"https://charteca.blob.core.windows.net/pubs/5KZgM2TvccMA8tUf"</span> <span style="color: dodgerblue;">style=</span><span style="color: #aa5500;">"height: 600px; width: 800px;"</span> <span style="color: dodgerblue;">type=</span><span style="color: #aa5500;">"image/svg+xml"</span><span style="color: dodgerblue; font-weight: bold;">></embed></span>
</pre>
</div>
<br />
Alternatively, I could export it as an SVG file<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiU2pJq8Ztcys962EBBORxTEJ31UzRJhXWWkTnivLjnAbkkvENGv_moBPffE9MuAQp_PucFEaHYVx4bGkp-VVPezWD5Mff66uO7RvCRzyiV5wtZJ0b2AZacNX2tEG9qkakI0oEwIR9Ifjae/s1600/summary-export.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="248" data-original-width="476" height="166" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiU2pJq8Ztcys962EBBORxTEJ31UzRJhXWWkTnivLjnAbkkvENGv_moBPffE9MuAQp_PucFEaHYVx4bGkp-VVPezWD5Mff66uO7RvCRzyiV5wtZJ0b2AZacNX2tEG9qkakI0oEwIR9Ifjae/s320/summary-export.png" width="320" /></a></div>
<br />
and send it by email or make it visible on the internal website using the same embedding technique:
<!-- HTML generated using hilite.me --><br />
<br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: dodgerblue; font-weight: bold;"><embed</span> <span style="color: dodgerblue;">src=</span><span style="color: #aa5500;">"some/intranet/location/5KZgM2TvccMA8tUf.svg"</span> <span style="color: dodgerblue;">style=</span><span style="color: #aa5500;">"height: 600px; width: 800px;"</span> <span style="color: dodgerblue;">type=</span><span style="color: #aa5500;">"image/svg+xml"</span><span style="color: dodgerblue; font-weight: bold;">></embed></span>
</pre>
</div>
<br />
<h2>
What's next?</h2>
<br />
This article focuses on sessions, bounce and conversion rate, but there are other metrics available. Working with multi-dimensional data takes some time to get used to, so please experiment.<br />
<br /></div>
Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com1tag:blogger.com,1999:blog-2606138741797457457.post-80259411774989929362017-11-28T15:32:00.000-08:002017-12-13T11:42:51.619-08:00Interactive charts in online presentationsEarlier this year, we posted <a href="http://blog.charte.ca/2017/01/2017-01-us-canada-demographics.html" target="_blank">a small research on US and Canada immigration patterns</a>. Let's make a simple online presentation from it.<br />
<br />
<h2>
Using slides.com</h2>
We will be using <a href="http://slides.com/">slides.com</a>, which is probably the most flexible and powerful online presentation engine these days. We will re-use all charts from our earlier post without modifications - they are already published and ready to be embedded. The presentation is available here:<br />
<br />
<a href="http://slides.com/chartecaguy/us-canada-demographics#/">http://slides.com/chartecaguy/us-canada-demographics#/</a><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgelrF1zwJ8Bm2rj-_kIyEgmPTY_SnFD5YZGXJNllZq2vyyFbXQCj6ICr4xU2GY2oRlHTUyOau4lTl9nXpIKU_KgZ7Ab2SeqG9KQYXAzos2BCoAX7vqRh5RdUJVOXqdMGr7B-JwcmqTO50o/s1600/ff-screenshot.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="911" data-original-width="1370" height="212" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgelrF1zwJ8Bm2rj-_kIyEgmPTY_SnFD5YZGXJNllZq2vyyFbXQCj6ICr4xU2GY2oRlHTUyOau4lTl9nXpIKU_KgZ7Ab2SeqG9KQYXAzos2BCoAX7vqRh5RdUJVOXqdMGr7B-JwcmqTO50o/s320/ff-screenshot.png" width="320" /></a></div>
<br />
A few things worth noting.<br />
<br />
1. All charts are interactive - move mouse pointer over the curves and see.<br />
<br />
2. All charts are embedded with <span style="font-family: "courier new" , "courier" , monospace;">style="width:100%"</span> attribute, which makes them scalable: try running the presentation in full-screen mode or resize your browser window - you will see charts changing their sizes accordingly.<br />
<br />
A screenshot of the slides.com editor with embedded chart:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNW8HxcfASxwgErh5zfup4YTXMkayC4YGetH0xzDx9Ur5Vujc9hOVW04MSt5gPuSETzVCULl0P86hVlsXPLm8TFqZNeT-7fNj20u3bsJEx6-8AGTDshyHTqISiwJ5VTnqirEylNvLxmRTk/s1600/slides-screenshot.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="837" data-original-width="1600" height="167" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiNW8HxcfASxwgErh5zfup4YTXMkayC4YGetH0xzDx9Ur5Vujc9hOVW04MSt5gPuSETzVCULl0P86hVlsXPLm8TFqZNeT-7fNj20u3bsJEx6-8AGTDshyHTqISiwJ5VTnqirEylNvLxmRTk/s320/slides-screenshot.png" width="320" /></a></div>
<br />
<h2>
Other online presentation tools</h2>
Are there any other online presentation tools that support HTML embedding from arbitrary sources? Unfortunately, not many. So far I have come up with this list:
<br />
<br />
<ul>
<li>Emaze - <span style="color: red;">NO</span></li>
<li>Google Slides - <span style="color: red;">NO</span></li>
<li>Haiku Deck - <span style="color: red;">NO</span></li>
<li>Prezi - <span style="color: red;">NO</span></li>
<li>Slides.com- <span style="color: green;">YES</span></li>
<li>Sway - <span style="color: red;">NO</span></li>
<li>Slidebean - <span style="color: red;">NO</span></li>
<li>Visme - <span style="color: green;">YES</span></li>
</ul>
<br />
<br />Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-70093498024234257272017-11-24T17:31:00.000-08:002017-11-25T19:56:20.005-08:00Chart background graphics: SVGWe are excited to announce SVG background support. This means that chart authors now can use vector or raster graphics for chart backgrounds. The following example shows gender symbols and generation names behind the population pyramid chart:
<br />
<br />
<div style="text-align: center;">
<embed src="https://charteca.blob.core.windows.net/pubs/WwGdfqB7KhaKXATv" style="height: 800px; width: 800px;" type="image/svg+xml"></embed>
</div>
<br />
<br />
This chart uses data from <a href="https://docs.google.com/spreadsheets/d/1YLSvHc5LSJrWQQpdSMznsnyU-1WJgq3mlvsHCB8en5w/edit?usp=sharing">this spreadsheet</a>. Import settings are as follows:
<br />
<br />
<a name='more'></a><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTGpcTv-D0xWadYnFYNGkzGqeFHQG0wv9_GnWTKRXU_OXuCR-I8uhu1PICd-VBLUE6tRdafS9FmT4zxn3MYLpmYcqd6-PBbgEj0S7ZraPM5ueyLkLf8kTK6IUFWv697rkTE8dRNDPccpCI/s1600/import-settings1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="484" data-original-width="1373" height="113" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTGpcTv-D0xWadYnFYNGkzGqeFHQG0wv9_GnWTKRXU_OXuCR-I8uhu1PICd-VBLUE6tRdafS9FmT4zxn3MYLpmYcqd6-PBbgEj0S7ZraPM5ueyLkLf8kTK6IUFWv697rkTE8dRNDPccpCI/s320/import-settings1.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjEcOcF1z4CXGk1Xnk8ARCyy-3G3YRL0twiCRo6WLESjetWvpHlPhpwuPw3upYBzJMNGt1RKRAVB_nn0TSQh4b05Ebvmf-31cIQUzTc1fcvwdSAvYaaSDSJEWSSakTMHKwxoCEUQdXYH3qe/s1600/import-settings2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="442" data-original-width="1378" height="103" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjEcOcF1z4CXGk1Xnk8ARCyy-3G3YRL0twiCRo6WLESjetWvpHlPhpwuPw3upYBzJMNGt1RKRAVB_nn0TSQh4b05Ebvmf-31cIQUzTc1fcvwdSAvYaaSDSJEWSSakTMHKwxoCEUQdXYH3qe/s320/import-settings2.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
After the data is imported, I used the following SVG content as background:</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: dodgerblue; font-weight: bold;"><svg</span> <span style="color: dodgerblue;">xmlns=</span><span style="color: #aa5500;">"http://www.w3.org/2000/svg"</span> <span style="color: dodgerblue;">xmlns:xlink=</span><span style="color: #aa5500;">"http://www.w3.org/1999/xlink"</span> <span style="color: dodgerblue;">width=</span><span style="color: #aa5500;">"100%"</span> <span style="color: dodgerblue;">height=</span><span style="color: #aa5500;">"100%"</span> <span style="color: dodgerblue;">version=</span><span style="color: #aa5500;">"1.1"</span> <span style="color: dodgerblue;">preserveAspectRatio=</span><span style="color: #aa5500;">"none"</span> <span style="color: dodgerblue;">viewBox=</span><span style="color: #aa5500;">"0 0 760 590"</span><span style="color: dodgerblue; font-weight: bold;">></span>
<span style="color: dodgerblue; font-weight: bold;"><g</span> <span style="color: dodgerblue;">id=</span><span style="color: #aa5500;">"female"</span> <span style="color: dodgerblue;">transform=</span><span style="color: #aa5500;">"scale(0.4,0.4) translate(1700,0)"</span> <span style="color: dodgerblue;">opacity=</span><span style="color: #aa5500;">"0.7"</span><span style="color: dodgerblue; font-weight: bold;">></span>
<span style="color: dodgerblue; font-weight: bold;"><path</span> <span style="color: dodgerblue;">fill=</span><span style="color: #aa5500;">"#ff007f"</span> <span style="color: dodgerblue;">d=</span><span style="color: #aa5500;">"m97.633598,79.828003c17.902405,0 32.415413,-14.511002 32.415413,-32.414001c0,-17.900002 -14.513008,-32.414001 -32.415413,-32.414001c-17.899002,0 -32.413002,14.514 -32.413002,32.414001c0,17.903 14.514,32.414001 32.413002,32.414001z"</span> <span style="color: dodgerblue;">id=</span><span style="color: #aa5500;">"path1591"</span><span style="color: dodgerblue; font-weight: bold;">/></span>
<span style="color: dodgerblue; font-weight: bold;"><path</span> <span style="color: dodgerblue;">d=</span><span style="color: #aa5500;">"m93.042,263.497833l0.012428,108.849213c0,19.583954 -29.227676,19.583954 -29.227676,0l-0.012432,-108.849213l-38.731819,0l41.442776,-144.328766l-6.513958,0l-24.406277,82.445465c-5.809202,17.982651 -30.401011,10.59549 -24.774462,-8.660126l27.098139,-89.570564c2.905071,-10.214363 15.772331,-28.322784 38.045235,-28.322784l20.537308,0l0,0l22.104027,0c22.074013,0 34.999596,18.261055 38.33976,28.322784l27.109604,89.483208c5.421921,19.245041 -18.976715,27.215912 -24.785934,8.456619l-24.397659,-82.154602l-7.053291,0l41.887444,144.328766l-38.901062,0l0,108.993225c0,19.439941 -29.105675,19.342957 -29.105675,0l0,-108.993225l-8.666473,0z"</span> <span style="color: dodgerblue;">id=</span><span style="color: #aa5500;">"path1595"</span> <span style="color: dodgerblue;">fill=</span><span style="color: #aa5500;">"#ff007f"</span><span style="color: dodgerblue; font-weight: bold;">/></span>
<span style="color: dodgerblue; font-weight: bold;"></g></span>
<span style="color: dodgerblue; font-weight: bold;"><g</span> <span style="color: dodgerblue;">id=</span><span style="color: #aa5500;">"male"</span> <span style="color: dodgerblue;">transform=</span><span style="color: #aa5500;">"scale(0.4,0.4) translate(-200,0)"</span> <span style="color: dodgerblue;">opacity=</span><span style="color: #aa5500;">"0.7"</span><span style="color: dodgerblue; font-weight: bold;">></span>
<span style="color: dodgerblue; font-weight: bold;"><path</span> <span style="color: dodgerblue;">d=</span><span style="color: #aa5500;">"m254.400421,75.940079c-20.842255,0 -37.604355,17.87822 -37.604355,40.177902l0,95.043503c0,18.478043 25.711075,18.478043 25.711075,0l0,-86.907471l6.086899,0l0,240.808762c0,24.705597 34.238785,23.97879 34.238785,0l0,-140.978989l5.896851,0l0,140.978989c0,23.97879 34.426086,24.705597 34.426086,0l0,-240.808762l5.944336,0l0,86.907471c0,18.621124 25.577667,18.620178 25.531067,0l0,-94.474945c0,-20.562752 -15.182098,-40.703819 -38.065826,-40.703819l-62.164917,-0.042641z"</span> <span style="color: dodgerblue;">id=</span><span style="color: #aa5500;">"path1599"</span> <span style="color: dodgerblue;">fill=</span><span style="color: #aa5500;">"#0000ff"</span><span style="color: dodgerblue; font-weight: bold;">/></span>
<span style="color: dodgerblue; font-weight: bold;"><path</span> <span style="color: dodgerblue;">fill=</span><span style="color: #aa5500;">"#0000ff"</span> <span style="color: dodgerblue;">d=</span><span style="color: #aa5500;">"m284.134003,80.828003c17.901001,0 32.414001,-14.511002 32.414001,-32.414001c0,-17.900002 -14.513,-32.414001 -32.414001,-32.414001c-17.899994,0 -32.414001,14.514 -32.414001,32.414001c0,17.903 14.514008,32.414001 32.414001,32.414001z"</span> <span style="color: dodgerblue;">id=</span><span style="color: #aa5500;">"path1603"</span><span style="color: dodgerblue; font-weight: bold;">/></span>
<span style="color: dodgerblue; font-weight: bold;"></g></span>
<span style="color: dodgerblue; font-weight: bold;"><g</span> <span style="color: dodgerblue;">fill=</span><span style="color: #aa5500;">"#c0c0c0"</span> <span style="color: dodgerblue;">font-family=</span><span style="color: #aa5500;">"Verdana"</span> <span style="color: dodgerblue;">font-size=</span><span style="color: #aa5500;">"42px"</span> <span style="color: dodgerblue;">text-anchor=</span><span style="color: #aa5500;">"middle"</span><span style="color: dodgerblue; font-weight: bold;">></span>
<span style="color: dodgerblue; font-weight: bold;"><text</span> <span style="color: dodgerblue;">x=</span><span style="color: #aa5500;">"380"</span> <span style="color: dodgerblue;">y=</span><span style="color: #aa5500;">"80"</span> <span style="color: dodgerblue; font-weight: bold;">></span>G.I. Generation<span style="color: dodgerblue; font-weight: bold;"></text></span>
<span style="color: dodgerblue; font-weight: bold;"><text</span> <span style="color: dodgerblue;">x=</span><span style="color: #aa5500;">"380"</span> <span style="color: dodgerblue;">y=</span><span style="color: #aa5500;">"180"</span> <span style="color: dodgerblue; font-weight: bold;">></span>Silent Generation<span style="color: dodgerblue; font-weight: bold;"></text></span>
<span style="color: dodgerblue; font-weight: bold;"><text</span> <span style="color: dodgerblue;">x=</span><span style="color: #aa5500;">"380"</span> <span style="color: dodgerblue;">y=</span><span style="color: #aa5500;">"280"</span> <span style="color: dodgerblue; font-weight: bold;">></span>Baby Boomers<span style="color: dodgerblue; font-weight: bold;"></text></span>
<span style="color: dodgerblue; font-weight: bold;"><text</span> <span style="color: dodgerblue;">x=</span><span style="color: #aa5500;">"380"</span> <span style="color: dodgerblue;">y=</span><span style="color: #aa5500;">"350"</span> <span style="color: dodgerblue; font-weight: bold;">></span>Generation X<span style="color: dodgerblue; font-weight: bold;"></text></span>
<span style="color: dodgerblue; font-weight: bold;"><text</span> <span style="color: dodgerblue;">x=</span><span style="color: #aa5500;">"380"</span> <span style="color: dodgerblue;">y=</span><span style="color: #aa5500;">"480"</span> <span style="color: dodgerblue; font-weight: bold;">></span>Millenials<span style="color: dodgerblue; font-weight: bold;"></text></span>
<span style="color: dodgerblue; font-weight: bold;"></g></span>
<span style="color: dodgerblue; font-weight: bold;"></svg></span>
</pre>
</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
I simply pasted it into the "Background SVG content" field:</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLy0FWofpqVEmOBl2wyEExJSL2TxF0CAC535Yf7EOLpM0nb5KEuecwWk2sOSFFEWWPnvrn_PYoKvq-h3Bl6wonV8T4LFIGg7V1bxR5eAa5AcjB24jGY0uzjFz2AEJuxh8pjtM-S7PbQbny/s1600/background-field.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="553" data-original-width="791" height="223" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiLy0FWofpqVEmOBl2wyEExJSL2TxF0CAC535Yf7EOLpM0nb5KEuecwWk2sOSFFEWWPnvrn_PYoKvq-h3Bl6wonV8T4LFIGg7V1bxR5eAa5AcjB24jGY0uzjFz2AEJuxh8pjtM-S7PbQbny/s320/background-field.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both;">
One thing worth mentioning here is the viewBox attribute: viewBox="0 0 760 590". Why 760x590, you may ask?</div>
<div class="separator" style="clear: both;">
<br /></div>
<div class="separator" style="clear: both;">
As you can guess from the screenshot above, I picked the "Grid area only" option for the background, which tells about my intent to draw the background for the grid area only, leaving alone other areas like chart title/subtitle and category radio buttons. Before I started working on the background SVG, I have made a screenshot of the chart and measured the grid area - from the top of the "100+" bars to the bottom axis. It turned out to be (approximately) 760x590, so I used these dimensions for my SVG content.</div>
<div>
<br /></div>
<div>
If I wanted to create a background for the whole chart, I would create SVG content of size 800x800, which is the size of the chart.</div>
<div>
<br /></div>
<div>
One of the best things about this SVG background feature is the ability to tweak the background in the "Background SVG content" field and see the changes right away, like in a graphics editor.</div>
<div>
<br /></div>
<div>
Everybody is welcome to use this new feature, but please keep in mind it requires some basic knowledge of SVG format. Even if just want to use a PNG or JPG image as a background, you have to wrap it in an SVG:</div>
<!-- HTML generated using hilite.me --><br />
<div style="background: #ffffff; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: dodgerblue; font-weight: bold;"><svg</span> <span style="color: dodgerblue;">xmlns=</span><span style="color: #aa5500;">"http://www.w3.org/2000/svg"</span> <span style="color: dodgerblue;">xmlns:xlink=</span><span style="color: #aa5500;">"http://www.w3.org/1999/xlink"</span> <span style="color: dodgerblue;">width=</span><span style="color: #aa5500;">"100%"</span> <span style="color: dodgerblue;">height=</span><span style="color: #aa5500;">"100%"</span> <span style="color: dodgerblue;">version=</span><span style="color: #aa5500;">"1.1"</span> <span style="color: dodgerblue;">preserveAspectRatio=</span><span style="color: #aa5500;">"none"</span> <span style="color: dodgerblue;">viewBox=</span><span style="color: #aa5500;">"0 0 PUT_IMAGE_WIDTH_HERE PUT_IMAGE_HEIGHT_HERE"</span><span style="color: dodgerblue; font-weight: bold;">></span>
<span style="color: dodgerblue; font-weight: bold;"><image</span> <span style="color: dodgerblue;">xlink:href=</span><span style="color: #aa5500;">"PUT_IMAGE_URL_HERE"</span><span style="color: dodgerblue; font-weight: bold;">/></span>
<span style="color: dodgerblue; font-weight: bold;"></svg></span>
</pre>
</div>
<div>
<br />
Now just imagine what you can do with your chart's background! Photos, other charts, watermark images, animations, icons... Enjoy.</div>
Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com2tag:blogger.com,1999:blog-2606138741797457457.post-89168441014307269542017-11-17T20:30:00.001-08:002017-11-24T08:53:53.093-08:00MI, NC, OH, PA congressional elections 1972-2016: district breakdown by % of votes for DemocratsInspired by <a href="https://blog.districks.com/2017/11/11/44-years-of-redistricting-consequences-in-14-seconds" target="_blank">Blake Esseltyn's attempt to visualize MI,NC,OH, and PA redistricting results</a>, I copied congressional election results (democratic votes only) in those four states from <a href="http://gerrymander.princeton.edu/">gerrymander.princeton.edu</a> into a <a href="https://docs.google.com/spreadsheets/d/1y2nFR7TxDQM_aLerbF8sMf-pPcEkcto_S17ldFGH7N0" target="_blank">spreadsheet</a> and broke down district results by percentile.
For example, for 1972 Michigan congressional elections we have 19 districts with the following votes % for Democrats (in no particular order):<br />
<br />
89.08%, 43.57%, 40.24%, 32.75%, 38.14%, 49.36%, 29.89%, 39.93%, 29.83%, 31.63%, 30.05%, 50.82%, 86.54%, 54.86%, 66.68%, 69.58%, 67.15%, 47.38%, 28.93%.<br />
<br />
After breaking down buy percentile, we get:<br />
Districts that got between 0% and 10% votes for Democrats: 0 out of 19: 0%<br />
Districts that got between 10% and 20% votes for Democrats: 0 out of 19: 0%<br />
Districts that got between 20% and 30% votes for Democrats: 3 out of 19: 16%<br />
Districts that got between 30% and 40% votes for Democrats: 5 out of 19: 26%<br />
and so on.<br />
<br />
The following are four heatmap charts that visualize Democratic votes breakdown for these fours states. Watch districts gravitating towards 30-50 democratic vote percentiles since 2010.
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTCuDfZ1pqCSz3KjWiaJi3nzYyB0SSiA-f-vkb0ANtQD-u-oUzXa3041YF5DPVcHoUM5A8JSG2UG6xFEcv4jNqwH5DrMZbRb01A5rmkeAKiDCbz5iz-dOyCA61zbUt-n6M_lfD2IiRpPp5/s1600/all-states.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="850" data-original-width="1200" height="226" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTCuDfZ1pqCSz3KjWiaJi3nzYyB0SSiA-f-vkb0ANtQD-u-oUzXa3041YF5DPVcHoUM5A8JSG2UG6xFEcv4jNqwH5DrMZbRb01A5rmkeAKiDCbz5iz-dOyCA61zbUt-n6M_lfD2IiRpPp5/s320/all-states.png" width="320" /></a></div>
<br />Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-73907729810569230402017-09-11T22:18:00.000-07:002017-11-24T08:54:05.919-08:00More heatmaps: other types of crime is San Francisco, 2003-2016This is a follow-up to <a href="http://blog.charte.ca/2017/09/sfpd-incident-database-and-heatmaps.html" target="_blank">the earlier post about visualizing SFPD data</a>. Now let's focus on other crime categories: vehicle theft, larceny/theft, burglary, assault.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZGwHool6mPRcjSbDLqXWP1yvHE1HrZGufKRs481n6fNlsn5IH_XnTIGMudjETjoFKs_MJksw8Mhc_edVBjjkC6u0Fjg0u6xWGEwKHpqEpoeUdAZl8usO_rJmOyhXPlKtLWyanjgPuRJLf/s1600/weekday-time-4.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="928" data-original-width="895" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZGwHool6mPRcjSbDLqXWP1yvHE1HrZGufKRs481n6fNlsn5IH_XnTIGMudjETjoFKs_MJksw8Mhc_edVBjjkC6u0Fjg0u6xWGEwKHpqEpoeUdAZl8usO_rJmOyhXPlKtLWyanjgPuRJLf/s640/weekday-time-4.gif" width="616" /></a></div>
<br />
Animated GIF is not the best way to explore patterns. Check out the original interactive charts for each type of crime below.<br />
<br />
<a name='more'></a><br />
<br />
<span style="font-size: large;">Vehicle theft
</span><br />
<br />
<embed src="https://charteca.blob.core.windows.net/pubs/aakN9P4P5QXWQ2qQ" style="height: 600px; width: 600px;" type="image/svg+xml"></embed>
<br />
<br />
<span style="font-size: large;">Larceny/Theft
</span><br />
<br />
<embed src="https://charteca.blob.core.windows.net/pubs/egzmEaSsZ5zXDV5D" style="height: 600px; width: 600px;" type="image/svg+xml"></embed>
<br />
<span style="font-size: large;">Burglaries
</span><br />
<br />
<embed src="https://charteca.blob.core.windows.net/pubs/ccHnvPTGepUxnZBv" style="height: 600px; width: 600px;" type="image/svg+xml"></embed>
<br />
<span style="font-size: large;"><br /></span>
<span style="font-size: large;">Assaults
</span><br />
<br />
<embed src="https://charteca.blob.core.windows.net/pubs/hEAFfQUNf4F6D9rE" style="height: 600px; width: 600px;" type="image/svg+xml"></embed>
<br />
<br />Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com1tag:blogger.com,1999:blog-2606138741797457457.post-10593744651613988672017-09-11T13:47:00.005-07:002017-11-24T08:54:39.279-08:00SFPD incident database and heatmaps: drug-related incident patternsSan Francisco Police Department provides free access to a great data source: https://data.sfgov.org/browse?category=Public+Safety. The following couple of charts display drug-related incident distribution by year, weekday, time of day, and district.<br />
<br />
<br />
<embed src="https://charteca.blob.core.windows.net/pubs/xAgHmdxXfpZnkQAF" style="height: 600px; width: 600px;" type="image/svg+xml"></embed>
<br />
<embed src="https://charteca.blob.core.windows.net/pubs/eYuy8BGDcwvaSsuA" style="height: 600px; width: 600px;" type="image/svg+xml"></embed>
<br />
Some observations:<br />
<br />
<ul>
<li>the notorious "welfare Wednesday" effect is present in early years;</li>
<li>incident numbers drop significantly starting 2010, which may be caused by Proposition 215: <a href="https://en.wikipedia.org/wiki/California_Proposition_215_(1996)" target="_blank">Wikipedia says</a> "It was not until March 2009 that federal officials announced that they would no longer try to thwart medical marijuana distribution/use in California".</li>
</ul>
There is <a href="http://blog.charte.ca/2017/09/more-heatmaps-other-types-of-crime-is.html" target="_blank">a follow-up post focusing on other types of crime in SF</a> using the same data source.<br />
<br />
The following are the steps to create the weekday/time heatmap chart using charte.ca editor.<br />
<br />
<a name='more'></a><br />
<br />
1. Get data from SFPD database and prepare it for charte.ca import. In this example, I was using <a href="https://docs.google.com/spreadsheets/d/1WuhnY1mziMZa_3qXpdz5mhL7a9yVSRP1ffcC__ior-g/edit?usp=sharing" target="_blank">this spreadsheet</a> - it contains all drug-related incidents between 2003 and 2016.<br />
<br />
2. Log in to charte.ca, create a new "XY heatmap with multiple categories" chart, click "Import" below the data grid, paste the SFPD data from the spreadsheet link above.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi207-atyGXRKpLLIhqR_LJs12jCxVJOrXhwffwKK6Fh2iideJzv-zzZmec7GGHyQ5RxanMwDJE1gkoHWE2wd1ByHSIUwSrlQU3omSYxBzV8r0-O1PpGrk5Sj1-UVtFKFqQLekK31HP-roH/s1600/step-1.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="304" data-original-width="860" height="113" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi207-atyGXRKpLLIhqR_LJs12jCxVJOrXhwffwKK6Fh2iideJzv-zzZmec7GGHyQ5RxanMwDJE1gkoHWE2wd1ByHSIUwSrlQU3omSYxBzV8r0-O1PpGrk5Sj1-UVtFKFqQLekK31HP-roH/s320/step-1.jpg" width="320" /></a></div>
<br />
<br />
3. Select appropriate fields on step 2.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9eNSG0W24K05ETMM9qkV-JIgmM9YsNlrLXn2_If0SwVz90pzVw_cryJ42BtoN8J_Slf-dnD5xw-477AeJNbSHmEH1nOdtAYC1NQ1ZFv-GPNiJq3ykWS58NZ3FSk0q6ncnbuOrhupsswRP/s1600/step-2.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="311" data-original-width="856" height="116" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9eNSG0W24K05ETMM9qkV-JIgmM9YsNlrLXn2_If0SwVz90pzVw_cryJ42BtoN8J_Slf-dnD5xw-477AeJNbSHmEH1nOdtAYC1NQ1ZFv-GPNiJq3ykWS58NZ3FSk0q6ncnbuOrhupsswRP/s320/step-2.jpg" width="320" /></a></div>
<br />
<br />
4.On step 3, leave all dropdowns blank - we will use all raw data rows.<br />
<br />
5. On step 4, make sure you are using COUNT aggregate function.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsE-MeduvAYIScgqwT-w19DjhI3IfPVKwWopJOWdvauAljb-Q8gYpYR7mahx-2Qj6oSjc7LJgrnVb9kUkW19VCEo1j8pUUDfGRyu0suu8mrmgmEJU0ewIA9c1zJsVKZe8X_ToW1kwrsohQ/s1600/step-4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="215" data-original-width="852" height="80" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsE-MeduvAYIScgqwT-w19DjhI3IfPVKwWopJOWdvauAljb-Q8gYpYR7mahx-2Qj6oSjc7LJgrnVb9kUkW19VCEo1j8pUUDfGRyu0suu8mrmgmEJU0ewIA9c1zJsVKZe8X_ToW1kwrsohQ/s320/step-4.jpg" width="320" /></a></div>
<br />
6. On step 5, make sure that X axis values (weekdays) come in the order they appear in the source spreadsheet. For this particular example, weekdays are sorted in the spreadsheet from Monday to Sunday. It makes sense to order Y axis and categories values alphabetically, hence the "asc" setting in correspondent dropdowns.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj82-EdP5hAptkUr-pCs3mhsQxOSFfEtM-YaZtiWb67VUdJkmB2qfGreGaAl7f6iwD6HsHtjrWd27l_PBGZz8solqsSdXwf4WkAxVrm5rPWdNzlmaFwW9mR1qt7d6G4q_FV57Y3PR4h4hu5/s1600/step-5.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="265" data-original-width="852" height="99" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj82-EdP5hAptkUr-pCs3mhsQxOSFfEtM-YaZtiWb67VUdJkmB2qfGreGaAl7f6iwD6HsHtjrWd27l_PBGZz8solqsSdXwf4WkAxVrm5rPWdNzlmaFwW9mR1qt7d6G4q_FV57Y3PR4h4hu5/s320/step-5.jpg" width="320" /></a></div>
<br />
7. Tweak chart size, title, colors, fonts and it is ready for publishing.Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-56502400886101203822017-08-15T16:20:00.001-07:002017-11-24T08:55:16.763-08:00World Health Organization (WHO): spotting spending patterns in 2000-2015It took me a while to find and groom WHO spending numbers. All financial reports I used can be found <a href="https://www.dropbox.com/s/rcz9ana0e9p8rk5/who-financials-2000-2015.zip?dl=0" target="_blank">here</a>. There were a few challenges, here are some worth mentioning.<br />
<br />
1. Between 2000 and 2011, the WHO published two-year financial reports, and in 2012, they switched to one-year schedule.<br />
<br />
2. The list of expenditure categories was changing from year two year. As a result, I could not break down equipment and supplies category into meaningful groups, so medical supplies and furniture fall into the same category. Same with the "Other" category: it includes grants, research contracts, local subsidies and what not.<br />
<br />
3. Since 2008, WHO financial reports stopped providing staff costs breakdown, so "Stuff costs" category includes full-time and part-time employees, consultants and even governing body delegates.<br />
<br />
<br />
Below is the two-axis chart that shows absolute spending numbers as bars and the share of some spending categories as lines:
<br />
<br />
<embed src="https://charteca.blob.core.windows.net/pubs/MgD4PX3xNnVGkmZW" style="height: 600px; width: 800px;" type="image/svg+xml"></embed>
<br />
<br />
Key observations:
<br />
<br />
<ul>
<li>the WHO is drifting towards outsourcing some of the activities: watch the staff+equipment spending go down while contractual services going up;</li>
<li>the share of travel expenses grew three times between 2000 and 2015.</li>
</ul>
<br />
<br />
It turned out both observations make sense: just google "world health organization outsourcing" and "world health organization travel expenses".Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-75564246872696188012017-08-08T16:29:00.000-07:002017-08-08T16:29:18.788-07:00Animated GIF is alive and wellI have just came across a blog featuring high-quality charts visualizing all kinds of data: <a href="https://datasoaring.blogspot.com/" target="_blank">https://datasoaring.blogspot.com</a>. Charts are made with <a href="http://plot.ly/" target="_blank">plot.ly</a><span id="goog_118485530"></span><a href="https://www.blogger.com/"></a><span id="goog_118485531"></span>, <a href="http://www.highcharts.com/" target="_blank">highcharts</a><span id="goog_118485520"></span><a href="https://www.blogger.com/"></a><span id="goog_118485521"></span>, <a href="http://bokeh.pydata.org/" target="_blank">bokeh</a>, <a href="http://charte.ca/" target="_blank">charte.ca</a> and other tools. Some charts are published as high-quality animated GIFs. Apparently, the blogger uses this data format as a last resort - not so many tools out there support animation right out of the box. Anyways, good to see charte.ca in a good company.Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-45070956784315860462017-08-08T14:53:00.001-07:002017-11-24T08:56:05.277-08:00Log scale for XY chartsFrom now on, charte.ca supports log scale in all XY charts (bubble, scatter, motion). Consider the following visualization of <a href="https://docs.google.com/spreadsheets/d/1vAEX6O3AyY4SsLbGnaxP5yTo649nR_oAKQBBRlXyKao/edit?usp=sharing">World Bank and SIPRI Military Expenditure Database data</a>:
<br />
<br />
<div style="background: white; height: 800px; width: 800px;">
<embed src="https://charteca.blob.core.windows.net/pubs/PbuDtpZwdTbrkFHq" style="height: 800px; width: 800px;" type="image/svg+xml"></embed>
</div>
<br />
<br />
<br />
Why log scale? There are a few cases when a log scale is appropriate. The most well known is: log scales allow a large range to be displayed without small values being compressed down into bottom of the graph. Consider the same visualization made using linear scale (this is just a screenshot, not an interactive chart, but it gives you a good idea about the problem):
<br />
<br />
<a name='more'></a><br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgytpDcHgn3G5o8HitISFOTh7wonAx51crQzrTsAeS_1iUtZsIAFi99Ny09VS3jmhYBmhe4YHINl7UdRokwR2_Ff2wAI1XQVkov7daAqIM56FFcY_VUwh9iRIy2cffdhBi5c5BFkQnhN-Zf/s1600/no-logscale.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="798" data-original-width="798" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgytpDcHgn3G5o8HitISFOTh7wonAx51crQzrTsAeS_1iUtZsIAFi99Ny09VS3jmhYBmhe4YHINl7UdRokwR2_Ff2wAI1XQVkov7daAqIM56FFcY_VUwh9iRIy2cffdhBi5c5BFkQnhN-Zf/s320/no-logscale.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
As you see, X-axis outliers (India and China) and Y-axis outliers (Middle East states) make the rest of the pack crowd in the bottom left corner of the chart, making the whole chart unusable. Of course, if your goal is to stress the fact that India and China has huge population, or you want to make Middle Eastern countries military budget look even more impressive, you may want to use linear scale. It's totally up to the chart designer which scale to use.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
As usual, a quick hint on how to import flat series data to charte.ca. After pasting raw data from the spreadsheet mentioned above, on step two of the import wizard, I made the following selections:</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGhl4tdMsSZcKhnxeJXhBp3MoIzA7K2iOxl_AT_-qVejcbeiO0IvRGNLtKAln84VPfJUWgf26yXZarQOVYI6tLgTN98hMDAnGIiRqWuRHVA_4ERU3Xo7j7te1wkkSyvN4zpHdAXpWW3lbw/s1600/select-columns.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="602" data-original-width="848" height="227" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgGhl4tdMsSZcKhnxeJXhBp3MoIzA7K2iOxl_AT_-qVejcbeiO0IvRGNLtKAln84VPfJUWgf26yXZarQOVYI6tLgTN98hMDAnGIiRqWuRHVA_4ERU3Xo7j7te1wkkSyvN4zpHdAXpWW3lbw/s320/select-columns.jpg" width="320" /></a></div>
<br />
Enjoy the power of log scale.Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-78808464414961559602017-08-01T18:04:00.001-07:002017-11-24T08:56:22.451-08:00More fun with motion charts: European top 20 imports and exports, 1990-2016World Bank Group provides a lot of data to play with. Let's build a motion chart based on <a href="http://data.worldbank.org/indicator/NE.EXP.GNFS.ZS?page=4">export</a>, <a href="http://data.worldbank.org/indicator/NE.IMP.GNFS.ZS?page=4">import</a>, and <a href="http://data.worldbank.org/indicator/NY.GDP.MKTP.CD?page=4">GDP</a> data for European top 20 economies, grouped by regions - Northern, Western, Eastern, and Southern.
<br />
<br />
<div style="background-position: left top; background: white; height: 800px; width: 800px;">
<embed src="https://charteca.blob.core.windows.net/pubs/FN2XxVnFnwgM7EdW" style="height: 800px; width: 800px;" type="image/svg+xml"></embed>
</div>
<br />
<br />
<br />
Watch international trade shrinking between 2008 and 2009!
Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com2tag:blogger.com,1999:blog-2606138741797457457.post-50875034918685360182017-07-31T14:02:00.003-07:002017-11-24T08:56:40.176-08:00Motion charts: add categories and animate those bubblescharte.ca now supports <a href="https://en.wikipedia.org/wiki/Motion_chart" target="blank">motion charts</a>. Motion charts are basically bubble charts with one more dimension added (usually, it's time). In charte.ca, we use category for this dimension, remember category radio buttons in charte.ca pie charts and bar charts? So, our motion charts are bubble charts with category radio buttons.<br />
<br />
Recently, Statistics Canada published a quick <a href="http://www.statcan.gc.ca/pub/11-630-x/11-630-x2017002-eng.htm" target="_blank">overview of interprovincial migration data</a>, using simple line charts. Let's make this data look fancier:<br />
<br />
<div style="background-position: left top; background: white; height: 500px; width: 800px;">
<embed src="https://charteca.blob.core.windows.net/pubs/SruvG5pbsnFVuMPh" style="height: 500px; width: 800px;" type="image/svg+xml"></embed>
</div>
<br />
<br />
Here are the steps to create this motion chart.
<br />
<a name='more'></a><br />
<br />
Login to charte.ca and create a new motion chart. Press "Import" button under chart data grid and paste flat migration data borrowed from the Statistics Canada page (and flattened using Excel):<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjF-Px7hmBjZ0WyrQlMhKqyayihvpff3RNcZgyhaYXqKfUUAxviPxDUuvNCTA26wGPwdtRq3B-0M3XH2XpcaUpCsCl59vPfNgRNF8FBdlMqlh3Ib-gUdlRCMgvGMZTOA6BioJDSSO0ZhHGp/s1600/step-1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="468" data-original-width="860" height="174" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjF-Px7hmBjZ0WyrQlMhKqyayihvpff3RNcZgyhaYXqKfUUAxviPxDUuvNCTA26wGPwdtRq3B-0M3XH2XpcaUpCsCl59vPfNgRNF8FBdlMqlh3Ib-gUdlRCMgvGMZTOA6BioJDSSO0ZhHGp/s320/step-1.png" width="320" /></a></div>
<br />
<br />
On step 2 of the import wizard, select appropriate raw data columns:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhO9tkiKzzWjiYPm7AqDzWNVeEdzRpi17dYbKKcGtetKHu-Xy-ZPhVNZ5Xki_mdkVDgeKmAGqvd1XIuBqfY0d-R5d7WIRTON7ePAJ6Y8ZSrteT0_zeGd97ve0EKh3mmBjr2U15QjUnLrZo4/s1600/step-2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="594" data-original-width="860" height="221" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhO9tkiKzzWjiYPm7AqDzWNVeEdzRpi17dYbKKcGtetKHu-Xy-ZPhVNZ5Xki_mdkVDgeKmAGqvd1XIuBqfY0d-R5d7WIRTON7ePAJ6Y8ZSrteT0_zeGd97ve0EKh3mmBjr2U15QjUnLrZo4/s320/step-2.png" width="320" /></a></div>
<br />
No selection required on steps 3 and 4, and on step 5 I choose not to sort series (Province abbreviation) and category (Year) values.<br />
<br />
Tweak the appearance a bit (fonts, titles, colors) and provide a background image with red and green zones and the motion chart is ready.
<br />
<br />
The visualization clearly shows the following:<br />
<ul>
<li>the biggest provinces (Ontario and Quebec) have been relatively neutral in term of interprovincial migration all those years;</li>
<li>atlanitc provinces are permanent donors (people leaving them);</li>
<li>British Columbia and Alberta attract people from other provinces;</li>
<li>Manitoba and Saskatchewan used to be recipients, and became donors in the second half of the 20th century.</li>
</ul>
<br />
<br />
<br />Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-40939760163720218872017-06-29T14:12:00.001-07:002017-11-24T08:57:24.174-08:00Happy Birthday, Canada: animated categoriesWhile Canadians looking for the ultimate party to celebrate Canada's 150th birthday on July 1, we will have a quick look at their spending habits. And we will do that by using charte.ca new feature - animated categories.
<br />
<br />
Chart options panels went through some reorganization. Now we have "Current category label" panel:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglv7u7FFPTrqxVCwknx-xPpKL1azly9zW3dl0zDXcLm5O-keR52rFUZCfrKIu2Gz2PfhDZWgq67QjvhIhH3gLUUnsGHzZ2ctT6fCfvxVzl-VtmB9tKp6r8LuTMx1ZWKNT07rkamBrzuXPe/s1600/2017-06-29-label.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="521" data-original-width="620" height="268" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglv7u7FFPTrqxVCwknx-xPpKL1azly9zW3dl0zDXcLm5O-keR52rFUZCfrKIu2Gz2PfhDZWgq67QjvhIhH3gLUUnsGHzZ2ctT6fCfvxVzl-VtmB9tKp6r8LuTMx1ZWKNT07rkamBrzuXPe/s320/2017-06-29-label.png" width="320" /></a></div>
<br />
and "Animation" panel:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7w5l6yQCpneI1W_-Vf_Ptr2CtvD-onOyjJUHAuKI88DSqDhRCUimSV7jAajl3-aGNWxJXzArBk8r65aMuQyhK5UJ-eiLDIKiah_DmMnC3OlZyADHo5hX6rS67fZh30s5quTRqqbB6l7oq/s1600/2017-06-29-animation.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="461" data-original-width="620" height="237" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7w5l6yQCpneI1W_-Vf_Ptr2CtvD-onOyjJUHAuKI88DSqDhRCUimSV7jAajl3-aGNWxJXzArBk8r65aMuQyhK5UJ-eiLDIKiah_DmMnC3OlZyADHo5hX6rS67fZh30s5quTRqqbB6l7oq/s320/2017-06-29-animation.png" width="320" /></a></div>
<br />
For charts with multiple data rows that are considered categories (pie, regular bar and comparative histogram charts), charte.ca can walk through all categories and update the appearance, creating an animation effect - see chart below.<br />
<br />
<div class="separator" style="text-align: center;">
<embed src="https://charteca.blob.core.windows.net/pubs/gxp6ELmG23EC2Zhp" style="height: 500px; width: 800px;" type="image/svg+xml"></embed>
</div>
<br />
Try it for yourself - create a pie, regular bar or comparative histogram chart with multiple data rows and play with animation settings. The feature comes handy when you embed charte.ca visualization into a presentation and do not have a convenient way to click or hover over the chart to navigate between categories.<br />
<br />Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-67797027409083868062017-06-14T17:09:00.001-07:002017-11-24T08:58:23.904-08:00The case for pie chartsPie charts get a lot of criticism these days. I must agree that bar charts deliver the message better because:
<br />
<br />
<ul>
<li>bar charts can present data for multiple categories without user interaction;</li>
<li>human eye is much better at telling differences between lengths than it is at determining differences between areas.</li>
</ul>
<br />
The charts below visualize the same data, and they all have the same size of 640x640 pixels. Decide for yourself which one does it better.<br />
<br />
Pie chart in question - move your mouse or tap over radio buttons to change category:<br />
<br />
<br />
<div style="text-align: center;">
<embed src="https://charteca.blob.core.windows.net/pubs/99BqNEn3nwN53T6e" style="height: 640px; width: 640px;" type="image/svg+xml"></embed>
</div>
<br />
<br />
<a name='more'></a><br />
<br />
Let's try a simple bar chart. Move the mouse or tap over radio buttons to change category:<br />
<br />
<br />
<div style="text-align: center;">
<embed src="https://charteca.blob.core.windows.net/pubs/kQL967zeKq2nLXbD" style="height: 640px; width: 640px;" type="image/svg+xml"></embed>
</div>
<br />
<br />
Now it's a good time to remember what data visualization expert Kaiser Fung could say about these radio buttons: <a href="http://junkcharts.typepad.com/junk_charts/2017/04/attractive-interactive-graphic-challenges-lazy-readers.html" target="_blank">"Every extra button or knob you place on the graphic, you lose another batch of readers"</a>. Stacked bar chart to the rescue - all categories are visible at once:<br />
<br />
<br />
<div style="text-align: center;">
<embed src="https://charteca.blob.core.windows.net/pubs/8tM2WDmNWE8qLd6r" style="height: 640px; width: 640px;" type="image/svg+xml"></embed>
</div>
Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-16003852900422972752017-06-01T21:48:00.001-07:002017-11-24T08:58:44.363-08:00Scalable charts<div class="separator" style="clear: both; text-align: left;">
Great news: charte.ca charts are now fully scalable. Content editors and users can change the size of the container - the chart will scale accordingly, preserving the original aspect ratio, without losing any functionality or quality.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsOF94l3K1_VzmYmdQY5Q78jA5NUmxJf-991m_tffbFcdGufaxqd8PdD3xgT3vjN7dQWgPuapPKhZV19lvI6GqEFV11KB3e1rpgDSae1kWZ_069Ovh-yZOBzxu1ORIqn092BrBrtsxYhT5/s1600/scale.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="600" data-original-width="800" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsOF94l3K1_VzmYmdQY5Q78jA5NUmxJf-991m_tffbFcdGufaxqd8PdD3xgT3vjN7dQWgPuapPKhZV19lvI6GqEFV11KB3e1rpgDSae1kWZ_069Ovh-yZOBzxu1ORIqn092BrBrtsxYhT5/s400/scale.gif" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<br />
Source code for this test HTML file - pay attention to the 100% width style:
<br />
<!-- HTML generated using hilite.me --><br />
<div style="background: #272822; border-width: 0.1em 0.1em 0.1em 0.8em; border: solid gray; overflow: auto; padding: 0.2em 0.6em; width: auto;">
<pre style="line-height: 125%; margin: 0;"><span style="color: #f92672;"><html></span>
<span style="color: #f92672;"><body></span>
<span style="color: #f92672;"><div></span>
<span style="color: #f92672;"><embed</span> <span style="color: #a6e22e;">src=</span><span style="color: #e6db74;">"example-drinking.svg"</span> <span style="color: #a6e22e;">type=</span><span style="color: #e6db74;">"image/svg+xml"</span> <span style="color: #a6e22e;">style=</span><span style="color: #e6db74;">"width:100%;"</span> <span style="color: #f92672;">/></span>
<span style="color: #f92672;"></div></span>
<span style="color: #f92672;"><body></span>
<span style="color: #f92672;"><html></span>
</pre>
</div>
<br />
<br />
To see this in action, open http://charte.ca and play with the browser window size - you will see sample charts shrinking and expanding as well.Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com1tag:blogger.com,1999:blog-2606138741797457457.post-71392957639168585422017-05-23T10:17:00.004-07:002018-04-10T15:03:27.916-07:00Integration API: charts with live datacharte.ca integration API comes handy when you want your published chart to display up-to-date information and you do not have the luxury of logging in to the editor, tweaking the data and re-publishing the chart manually every time the data changes. Consider the following P/E ratio vs. Price to book bubble chart that we want to be updated every hour:<br />
<div style="text-align: center;">
<br /></div>
<div style="text-align: center;">
<embed src="https://charteca.blob.core.windows.net/pubs/g7h9GRRPqk23GRZW" style="height: 600px; width: 600px;" type="image/svg+xml"></embed>
</div>
<br />
<br />
<a name='more'></a><br />
This chart was created and published using charte.ca online editor once, and it is updated and re-published by a script that runs every hour and performs the following tasks:<br />
<ul>
<li>obtain stock quotes for specific symbols from Yahoo;</li>
<li>compare them with the data currently displayed by the chart, and, if the data is different:
<ul>
<li>upload new data to the chart;</li>
<li>update chart subtitle so it reflects the latest update</li>
<li>re-publish the chart</li>
</ul>
</li>
</ul>
This example uses the following API calls:<br />
<ul>
<li>Download data</li>
<li>Put config</li>
<li>Upload data</li>
<li>Publish chart</li>
</ul>
For full list of available API calls, see <a href="http://demo.charte.ca/#/integration_guide" target="_blank">charte.ca API guide</a>.<br />
<br />
This particular example uses Python as scripting language, but it could be any language or framework capable of performing HTTP requests. The script is scheduled to run every hour on a system integrator's infrastructure. Full source code is below.<br />
<br />
<!-- HTML generated using hilite.me --><div style="background: #272822; overflow:auto;width:auto;border:solid gray;border-width:.1em .1em .1em .8em;padding:.2em .6em;"><pre style="margin: 0; line-height: 125%"><span style="color: #f92672">import</span> <span style="color: #f8f8f2">urllib2</span>
<span style="color: #f92672">import</span> <span style="color: #f8f8f2">json</span>
<span style="color: #f92672">import</span> <span style="color: #f8f8f2">sys</span>
<span style="color: #f92672">import</span> <span style="color: #f8f8f2">time</span>
<span style="color: #66d9ef">def</span> <span style="color: #a6e22e">call_charteca_api</span><span style="color: #f8f8f2">(url,</span> <span style="color: #f8f8f2">method,</span> <span style="color: #f8f8f2">content_type</span><span style="color: #f92672">=</span><span style="color: #f8f8f2">None,</span> <span style="color: #f8f8f2">data</span><span style="color: #f92672">=</span><span style="color: #f8f8f2">None):</span>
<span style="color: #f8f8f2">opener</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">urllib2</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">build_opener(urllib2</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">HTTPHandler)</span>
<span style="color: #f8f8f2">request</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">urllib2</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">Request(url,</span> <span style="color: #f8f8f2">data</span><span style="color: #f92672">=</span><span style="color: #f8f8f2">data)</span>
<span style="color: #f8f8f2">request</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">add_header(</span><span style="color: #e6db74">'api-key'</span><span style="color: #f8f8f2">,</span> <span style="color: #f8f8f2">api_key)</span>
<span style="color: #66d9ef">if</span> <span style="color: #f8f8f2">content_type</span> <span style="color: #f92672">!=</span> <span style="color: #f8f8f2">None:</span>
<span style="color: #f8f8f2">request</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">add_header(</span><span style="color: #e6db74">'Content-Type'</span><span style="color: #f8f8f2">,</span> <span style="color: #f8f8f2">content_type)</span>
<span style="color: #f8f8f2">request</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">get_method</span> <span style="color: #f92672">=</span> <span style="color: #66d9ef">lambda</span><span style="color: #f8f8f2">:</span> <span style="color: #f8f8f2">method</span>
<span style="color: #66d9ef">return</span> <span style="color: #f8f8f2">opener</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">open(request)</span>
<span style="color: #75715e"># Retrieve data as TSV, as it is stored in the datagrid</span>
<span style="color: #66d9ef">def</span> <span style="color: #a6e22e">download_quote_data</span><span style="color: #f8f8f2">(symbols,</span> <span style="color: #f8f8f2">symbol_short_name_map,</span> <span style="color: #f8f8f2">symbol_long_name_map):</span>
<span style="color: #f8f8f2">data</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">[[</span><span style="color: #e6db74">"Symbol"</span><span style="color: #f8f8f2">],[</span><span style="color: #e6db74">"P/E Ratio"</span><span style="color: #f8f8f2">],[</span><span style="color: #e6db74">"Price to book"</span><span style="color: #f8f8f2">],[</span><span style="color: #e6db74">"Market cap (billions)"</span><span style="color: #f8f8f2">],[</span><span style="color: #e6db74">"Performance today"</span><span style="color: #f8f8f2">],[</span><span style="color: #e6db74">"Name"</span><span style="color: #f8f8f2">],[</span><span style="color: #e6db74">"Symbol"</span><span style="color: #f8f8f2">]]</span>
<span style="color: #66d9ef">for</span> <span style="color: #f8f8f2">symbol</span> <span style="color: #f92672">in</span> <span style="color: #f8f8f2">symbols:</span>
<span style="color: #75715e"># Yahoo ditched YQL on Nov 2, 2017. Get it right from the website</span>
<span style="color: #f8f8f2">opener</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">urllib2</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">build_opener(urllib2</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">HTTPHandler)</span>
<span style="color: #f8f8f2">req</span> <span style="color: #f92672">=</span> <span style="color: #e6db74">'https://finance.yahoo.com/quote/'</span> <span style="color: #f92672">+</span> <span style="color: #f8f8f2">symbol</span>
<span style="color: #f8f8f2">request</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">urllib2</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">Request(req)</span>
<span style="color: #f8f8f2">request</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">add_header(</span><span style="color: #e6db74">'Cache-Control'</span><span style="color: #f8f8f2">,</span> <span style="color: #e6db74">"no-cache"</span><span style="color: #f8f8f2">)</span>
<span style="color: #f8f8f2">request</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">get_method</span> <span style="color: #f92672">=</span> <span style="color: #66d9ef">lambda</span><span style="color: #f8f8f2">:</span> <span style="color: #e6db74">'GET'</span>
<span style="color: #f8f8f2">r</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">opener</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">open(request)</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">read()</span>
<span style="color: #f8f8f2">i1</span><span style="color: #f92672">=</span><span style="color: #ae81ff">0</span>
<span style="color: #f8f8f2">i1</span><span style="color: #f92672">=</span><span style="color: #f8f8f2">r</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">find(</span><span style="color: #e6db74">'root.App.main'</span><span style="color: #f8f8f2">,</span> <span style="color: #f8f8f2">i1)</span>
<span style="color: #f8f8f2">i1</span><span style="color: #f92672">=</span><span style="color: #f8f8f2">r</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">find(</span><span style="color: #e6db74">'{'</span><span style="color: #f8f8f2">,</span> <span style="color: #f8f8f2">i1)</span>
<span style="color: #f8f8f2">i2</span><span style="color: #f92672">=</span><span style="color: #f8f8f2">r</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">find(</span><span style="color: #e6db74">"</span><span style="color: #ae81ff">\n</span><span style="color: #e6db74">"</span><span style="color: #f8f8f2">,</span> <span style="color: #f8f8f2">i1)</span>
<span style="color: #f8f8f2">i2</span><span style="color: #f92672">=</span><span style="color: #f8f8f2">r</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">rfind(</span><span style="color: #e6db74">';'</span><span style="color: #f8f8f2">,</span> <span style="color: #f8f8f2">i1,</span> <span style="color: #f8f8f2">i2)</span>
<span style="color: #f8f8f2">jsonstr</span><span style="color: #f92672">=</span><span style="color: #f8f8f2">r[i1:i2]</span>
<span style="color: #75715e">#load the raw json data into a python data object</span>
<span style="color: #f8f8f2">json_data</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">json</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">loads(jsonstr)</span>
<span style="color: #f8f8f2">qss</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">json_data[</span><span style="color: #e6db74">'context'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'dispatcher'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'stores'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'QuoteSummaryStore'</span><span style="color: #f8f8f2">]</span>
<span style="color: #75715e">#pull the values that we are interested in </span>
<span style="color: #f8f8f2">change_sign</span> <span style="color: #f92672">=</span> <span style="color: #e6db74">""</span> <span style="color: #66d9ef">if</span> <span style="color: #f8f8f2">float(qss[</span><span style="color: #e6db74">'price'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'regularMarketChange'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'fmt'</span><span style="color: #f8f8f2">])</span> <span style="color: #f92672"><=</span> <span style="color: #ae81ff">0.0</span> <span style="color: #66d9ef">else</span> <span style="color: #e6db74">"+"</span>
<span style="color: #f8f8f2">data[</span><span style="color: #ae81ff">0</span><span style="color: #f8f8f2">]</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">append(symbol_short_name_map[symbol]</span> <span style="color: #f92672">+</span> <span style="color: #e6db74">": "</span> <span style="color: #f92672">+</span> <span style="color: #f8f8f2">change_sign</span> <span style="color: #f92672">+</span> <span style="color: #f8f8f2">qss[</span><span style="color: #e6db74">'price'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'regularMarketChange'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'fmt'</span><span style="color: #f8f8f2">])</span>
<span style="color: #f8f8f2">data[</span><span style="color: #ae81ff">1</span><span style="color: #f8f8f2">]</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">append(qss[</span><span style="color: #e6db74">'summaryDetail'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'trailingPE'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'fmt'</span><span style="color: #f8f8f2">])</span>
<span style="color: #f8f8f2">data[</span><span style="color: #ae81ff">2</span><span style="color: #f8f8f2">]</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">append(qss[</span><span style="color: #e6db74">'defaultKeyStatistics'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'priceToBook'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'fmt'</span><span style="color: #f8f8f2">])</span>
<span style="color: #f8f8f2">data[</span><span style="color: #ae81ff">3</span><span style="color: #f8f8f2">]</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">append(qss[</span><span style="color: #e6db74">'summaryDetail'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'marketCap'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'fmt'</span><span style="color: #f8f8f2">])</span>
<span style="color: #f8f8f2">data[</span><span style="color: #ae81ff">4</span><span style="color: #f8f8f2">]</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">append(change_sign</span><span style="color: #f92672">+</span><span style="color: #f8f8f2">qss[</span><span style="color: #e6db74">'price'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'regularMarketChangePercent'</span><span style="color: #f8f8f2">][</span><span style="color: #e6db74">'fmt'</span><span style="color: #f8f8f2">])</span>
<span style="color: #f8f8f2">data[</span><span style="color: #ae81ff">5</span><span style="color: #f8f8f2">]</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">append(symbol_long_name_map[symbol])</span>
<span style="color: #f8f8f2">data[</span><span style="color: #ae81ff">6</span><span style="color: #f8f8f2">]</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">append(symbol)</span>
<span style="color: #75715e"># Print out tab-separated values</span>
<span style="color: #f8f8f2">tsv_data</span> <span style="color: #f92672">=</span> <span style="color: #e6db74">""</span>
<span style="color: #66d9ef">for</span> <span style="color: #f8f8f2">data_row</span> <span style="color: #f92672">in</span> <span style="color: #f8f8f2">data:</span>
<span style="color: #f8f8f2">tsv_data</span> <span style="color: #f92672">+=</span> <span style="color: #e6db74">"</span><span style="color: #ae81ff">\t</span><span style="color: #e6db74">"</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">join(data_row)</span> <span style="color: #f92672">+</span> <span style="color: #e6db74">"</span><span style="color: #ae81ff">\n</span><span style="color: #e6db74">"</span>
<span style="color: #66d9ef">return</span> <span style="color: #f8f8f2">tsv_data</span>
<span style="color: #75715e"># Rertrieve existing chart grid data as it is</span>
<span style="color: #66d9ef">def</span> <span style="color: #a6e22e">download_chart_data</span><span style="color: #f8f8f2">(charteca_root,</span> <span style="color: #f8f8f2">api_key,</span> <span style="color: #f8f8f2">chart_id):</span>
<span style="color: #66d9ef">print</span> <span style="color: #e6db74">"Downloading existing chart data..."</span>
<span style="color: #f8f8f2">download_response</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">call_charteca_api(charteca_root</span> <span style="color: #f92672">+</span> <span style="color: #e6db74">'/chart_integration/data/'</span> <span style="color: #f92672">+</span> <span style="color: #f8f8f2">chart_id,</span> <span style="color: #e6db74">"GET"</span><span style="color: #f8f8f2">)</span>
<span style="color: #f8f8f2">download_response_data</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">download_response</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">read()</span>
<span style="color: #f8f8f2">download_response_headers</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">download_response</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">info()</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">dict</span>
<span style="color: #66d9ef">if</span> <span style="color: #e6db74">"content-type"</span> <span style="color: #f92672">not</span> <span style="color: #f92672">in</span> <span style="color: #f8f8f2">download_response_headers:</span>
<span style="color: #66d9ef">print</span> <span style="color: #e6db74">"Error: no content-type in the response"</span>
<span style="color: #f8f8f2">sys</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">exit(</span><span style="color: #f92672">-</span><span style="color: #ae81ff">1</span><span style="color: #f8f8f2">);</span>
<span style="color: #66d9ef">if</span> <span style="color: #e6db74">"application/json"</span> <span style="color: #f92672">in</span> <span style="color: #f8f8f2">download_response_headers[</span><span style="color: #e6db74">"content-type"</span><span style="color: #f8f8f2">]:</span>
<span style="color: #75715e"># We got an error</span>
<span style="color: #66d9ef">print</span> <span style="color: #f8f8f2">download_response_data</span>
<span style="color: #f8f8f2">sys</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">exit(</span><span style="color: #f92672">-</span><span style="color: #ae81ff">1</span><span style="color: #f8f8f2">);</span>
<span style="color: #66d9ef">if</span> <span style="color: #e6db74">"text/tab-separated-values"</span> <span style="color: #f92672">not</span> <span style="color: #f92672">in</span> <span style="color: #f8f8f2">download_response_headers[</span><span style="color: #e6db74">"content-type"</span><span style="color: #f8f8f2">]:</span>
<span style="color: #75715e"># We expected TSV data</span>
<span style="color: #66d9ef">print</span> <span style="color: #e6db74">" Error: expected TSV data in the response"</span>
<span style="color: #f8f8f2">sys</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">exit(</span><span style="color: #f92672">-</span><span style="color: #ae81ff">1</span><span style="color: #f8f8f2">);</span>
<span style="color: #66d9ef">return</span> <span style="color: #f8f8f2">download_response_data</span>
<span style="color: #75715e"># Update chart config: change the subtitle to reflect the date</span>
<span style="color: #66d9ef">def</span> <span style="color: #a6e22e">update_chart_title</span><span style="color: #f8f8f2">(charteca_root,</span> <span style="color: #f8f8f2">api_key,</span> <span style="color: #f8f8f2">chart_id):</span>
<span style="color: #66d9ef">print</span> <span style="color: #e6db74">"Updating chart config..."</span>
<span style="color: #f8f8f2">updated_on</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">time</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">strftime(</span><span style="color: #e6db74">"%Y-%m-%d %H:%M:%S UTC"</span><span style="color: #f8f8f2">,</span> <span style="color: #f8f8f2">time</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">gmtime())</span>
<span style="color: #f8f8f2">update_response</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">call_charteca_api(charteca_root</span> <span style="color: #f92672">+</span> <span style="color: #e6db74">'/chart_integration/config/'</span> <span style="color: #f92672">+</span> <span style="color: #f8f8f2">chart_id,</span> <span style="color: #e6db74">"PUT"</span><span style="color: #f8f8f2">,</span> <span style="color: #f8f8f2">content_type</span><span style="color: #f92672">=</span><span style="color: #e6db74">"application/json"</span><span style="color: #f8f8f2">,</span> <span style="color: #f8f8f2">data</span><span style="color: #f92672">=</span><span style="color: #e6db74">'{"config": {"subtitle": {"text1": "Data from Yahoo Finance: %s"}}}'</span> <span style="color: #f92672">%</span> <span style="color: #f8f8f2">updated_on)</span>
<span style="color: #f8f8f2">update_respose_object</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">json</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">loads(update_response</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">read())</span>
<span style="color: #66d9ef">if</span> <span style="color: #f8f8f2">update_respose_object[</span><span style="color: #e6db74">"code"</span><span style="color: #f8f8f2">]</span> <span style="color: #f92672">!=</span> <span style="color: #e6db74">"SUCCESS"</span><span style="color: #f8f8f2">:</span>
<span style="color: #66d9ef">print</span> <span style="color: #f8f8f2">json</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">dumps(update_respose_object)</span>
<span style="color: #f8f8f2">sys</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">exit(</span><span style="color: #f92672">-</span><span style="color: #ae81ff">1</span><span style="color: #f8f8f2">);</span>
<span style="color: #66d9ef">def</span> <span style="color: #a6e22e">update_chart_data</span><span style="color: #f8f8f2">(charteca_root,</span> <span style="color: #f8f8f2">api_key,</span> <span style="color: #f8f8f2">chart_id,</span> <span style="color: #f8f8f2">new_data):</span>
<span style="color: #66d9ef">print</span> <span style="color: #e6db74">"Uploading data..."</span>
<span style="color: #f8f8f2">upload_response</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">call_charteca_api(charteca_root</span> <span style="color: #f92672">+</span> <span style="color: #e6db74">'/chart_integration/data/'</span> <span style="color: #f92672">+</span> <span style="color: #f8f8f2">chart_id,</span> <span style="color: #e6db74">"PUT"</span><span style="color: #f8f8f2">,</span> <span style="color: #f8f8f2">content_type</span><span style="color: #f92672">=</span><span style="color: #f8f8f2">None,</span> <span style="color: #f8f8f2">data</span><span style="color: #f92672">=</span><span style="color: #f8f8f2">new_data)</span>
<span style="color: #f8f8f2">upload_respose_object</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">json</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">loads(upload_response</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">read())</span>
<span style="color: #66d9ef">if</span> <span style="color: #f8f8f2">upload_respose_object[</span><span style="color: #e6db74">"code"</span><span style="color: #f8f8f2">]</span> <span style="color: #f92672">!=</span> <span style="color: #e6db74">"SUCCESS"</span><span style="color: #f8f8f2">:</span>
<span style="color: #66d9ef">print</span> <span style="color: #f8f8f2">json</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">dumps(upload_respose_object)</span>
<span style="color: #f8f8f2">sys</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">exit(</span><span style="color: #f92672">-</span><span style="color: #ae81ff">1</span><span style="color: #f8f8f2">);</span>
<span style="color: #66d9ef">def</span> <span style="color: #a6e22e">republish_chart</span><span style="color: #f8f8f2">(charteca_root,</span> <span style="color: #f8f8f2">api_key,</span> <span style="color: #f8f8f2">chart_id):</span>
<span style="color: #66d9ef">print</span> <span style="color: #e6db74">"Publishing chart..."</span>
<span style="color: #f8f8f2">publish_response</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">call_charteca_api(charteca_root</span> <span style="color: #f92672">+</span> <span style="color: #e6db74">'/chart_integration/publication/'</span> <span style="color: #f92672">+</span> <span style="color: #f8f8f2">chart_id,</span> <span style="color: #e6db74">"POST"</span><span style="color: #f8f8f2">)</span>
<span style="color: #f8f8f2">publish_respose_object</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">json</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">loads(publish_response</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">read())</span>
<span style="color: #66d9ef">if</span> <span style="color: #f8f8f2">publish_respose_object[</span><span style="color: #e6db74">"code"</span><span style="color: #f8f8f2">]</span> <span style="color: #f92672">!=</span> <span style="color: #e6db74">"SUCCESS"</span><span style="color: #f8f8f2">:</span>
<span style="color: #66d9ef">print</span> <span style="color: #f8f8f2">json</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">dumps(publish_respose_object)</span>
<span style="color: #f8f8f2">sys</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">exit(</span><span style="color: #f92672">-</span><span style="color: #ae81ff">1</span><span style="color: #f8f8f2">);</span>
<span style="color: #f8f8f2">charteca_root</span> <span style="color: #f92672">=</span> <span style="color: #e6db74">"https://api.charte.ca"</span>
<span style="color: #f8f8f2">api_key</span> <span style="color: #f92672">=</span> <span style="color: #e6db74">"INSERT_YOUR_KEY_HERE"</span>
<span style="color: #f8f8f2">chart_id</span> <span style="color: #f92672">=</span> <span style="color: #e6db74">"g7h9GRRPqk23GRZW"</span>
<span style="color: #75715e"># Stock exchange symbols we are interested in. Order matters.</span>
<span style="color: #f8f8f2">symbols</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">[</span><span style="color: #e6db74">"TD"</span><span style="color: #f8f8f2">,</span><span style="color: #e6db74">"BMO"</span><span style="color: #f8f8f2">,</span><span style="color: #e6db74">"BNS"</span><span style="color: #f8f8f2">,</span><span style="color: #e6db74">"CM"</span><span style="color: #f8f8f2">,</span><span style="color: #e6db74">"RY"</span><span style="color: #f8f8f2">]</span>
<span style="color: #f8f8f2">symbol_short_name_map</span> <span style="color: #f92672">=</span><span style="color: #f8f8f2">{</span><span style="color: #e6db74">"TD"</span><span style="color: #f8f8f2">:</span><span style="color: #e6db74">"TD"</span><span style="color: #f8f8f2">,</span><span style="color: #e6db74">"RY"</span><span style="color: #f8f8f2">:</span><span style="color: #e6db74">"RBC"</span><span style="color: #f8f8f2">,</span> <span style="color: #e6db74">"BMO"</span><span style="color: #f8f8f2">:</span><span style="color: #e6db74">"BMO"</span><span style="color: #f8f8f2">,</span> <span style="color: #e6db74">"BNS"</span><span style="color: #f8f8f2">:</span><span style="color: #e6db74">"Scotia Bank"</span><span style="color: #f8f8f2">,</span><span style="color: #e6db74">"CM"</span><span style="color: #f8f8f2">:</span><span style="color: #e6db74">"CIBC"</span><span style="color: #f8f8f2">}</span>
<span style="color: #f8f8f2">symbol_long_name_map</span> <span style="color: #f92672">=</span><span style="color: #f8f8f2">{</span><span style="color: #e6db74">"TD"</span><span style="color: #f8f8f2">:</span><span style="color: #e6db74">"Toronto Dominion"</span><span style="color: #f8f8f2">,</span><span style="color: #e6db74">"RY"</span><span style="color: #f8f8f2">:</span><span style="color: #e6db74">"Royal Bank of Canada"</span><span style="color: #f8f8f2">,</span> <span style="color: #e6db74">"BMO"</span><span style="color: #f8f8f2">:</span><span style="color: #e6db74">"Bank of Montreal"</span><span style="color: #f8f8f2">,</span> <span style="color: #e6db74">"BNS"</span><span style="color: #f8f8f2">:</span><span style="color: #e6db74">"Scotia Bank"</span><span style="color: #f8f8f2">,</span><span style="color: #e6db74">"CM"</span><span style="color: #f8f8f2">:</span><span style="color: #e6db74">"Canadian Imperial Bank of Commerce"</span><span style="color: #f8f8f2">}</span>
<span style="color: #75715e"># Retrieve new data from Yahoo, existing data from charteca integration API</span>
<span style="color: #f8f8f2">new_data</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">download_quote_data(symbols,</span> <span style="color: #f8f8f2">symbol_short_name_map,</span> <span style="color: #f8f8f2">symbol_long_name_map)</span>
<span style="color: #f8f8f2">old_data</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">download_chart_data(charteca_root,</span> <span style="color: #f8f8f2">api_key,</span> <span style="color: #f8f8f2">chart_id)</span>
<span style="color: #75715e"># Compare existing data with what we got from Yahoo</span>
<span style="color: #f8f8f2">old_lines</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">filter(None,</span> <span style="color: #f8f8f2">[line</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">replace(</span><span style="color: #e6db74">"</span><span style="color: #ae81ff">\r</span><span style="color: #e6db74">"</span><span style="color: #f8f8f2">,</span> <span style="color: #e6db74">""</span><span style="color: #f8f8f2">)</span> <span style="color: #66d9ef">for</span> <span style="color: #f8f8f2">line</span> <span style="color: #f92672">in</span> <span style="color: #f8f8f2">old_data</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">split(</span><span style="color: #e6db74">"</span><span style="color: #ae81ff">\n</span><span style="color: #e6db74">"</span><span style="color: #f8f8f2">)])</span>
<span style="color: #f8f8f2">new_lines</span> <span style="color: #f92672">=</span> <span style="color: #f8f8f2">filter(None,</span> <span style="color: #f8f8f2">[line</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">replace(</span><span style="color: #e6db74">"</span><span style="color: #ae81ff">\r</span><span style="color: #e6db74">"</span><span style="color: #f8f8f2">,</span> <span style="color: #e6db74">""</span><span style="color: #f8f8f2">)</span> <span style="color: #66d9ef">for</span> <span style="color: #f8f8f2">line</span> <span style="color: #f92672">in</span> <span style="color: #f8f8f2">new_data</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">split(</span><span style="color: #e6db74">"</span><span style="color: #ae81ff">\n</span><span style="color: #e6db74">"</span><span style="color: #f8f8f2">)])</span>
<span style="color: #75715e"># Compare first few lines: performance, P/E, PriceBook, Capitalization</span>
<span style="color: #66d9ef">if</span> <span style="color: #f8f8f2">old_lines[</span><span style="color: #ae81ff">0</span><span style="color: #f8f8f2">]</span> <span style="color: #f92672">==</span> <span style="color: #f8f8f2">new_lines[</span><span style="color: #ae81ff">0</span><span style="color: #f8f8f2">]</span> <span style="color: #f92672">and</span> <span style="color: #f8f8f2">old_lines[</span><span style="color: #ae81ff">1</span><span style="color: #f8f8f2">]</span> <span style="color: #f92672">==</span> <span style="color: #f8f8f2">new_lines[</span><span style="color: #ae81ff">1</span><span style="color: #f8f8f2">]</span> <span style="color: #f92672">and</span> <span style="color: #f8f8f2">old_lines[</span><span style="color: #ae81ff">2</span><span style="color: #f8f8f2">]</span> <span style="color: #f92672">==</span> <span style="color: #f8f8f2">new_lines[</span><span style="color: #ae81ff">2</span><span style="color: #f8f8f2">]</span> <span style="color: #f92672">and</span> <span style="color: #f8f8f2">old_lines[</span><span style="color: #ae81ff">3</span><span style="color: #f8f8f2">]</span> <span style="color: #f92672">==</span> <span style="color: #f8f8f2">new_lines[</span><span style="color: #ae81ff">3</span><span style="color: #f8f8f2">]</span> <span style="color: #f92672">and</span> <span style="color: #f8f8f2">old_lines[</span><span style="color: #ae81ff">4</span><span style="color: #f8f8f2">]</span> <span style="color: #f92672">==</span> <span style="color: #f8f8f2">new_lines[</span><span style="color: #ae81ff">4</span><span style="color: #f8f8f2">]:</span>
<span style="color: #66d9ef">print</span> <span style="color: #e6db74">"Nothing to update, the data is the same"</span>
<span style="color: #f8f8f2">sys</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">exit(</span><span style="color: #ae81ff">0</span><span style="color: #f8f8f2">)</span>
<span style="color: #66d9ef">else</span><span style="color: #f8f8f2">:</span>
<span style="color: #66d9ef">print</span> <span style="color: #e6db74">"Data changed, uploading updates..."</span>
<span style="color: #66d9ef">print</span> <span style="color: #f8f8f2">old_lines</span>
<span style="color: #66d9ef">print</span> <span style="color: #f8f8f2">new_lines</span>
<span style="color: #f8f8f2">update_chart_title(charteca_root,</span> <span style="color: #f8f8f2">api_key,</span> <span style="color: #f8f8f2">chart_id)</span>
<span style="color: #f8f8f2">update_chart_data(charteca_root,</span> <span style="color: #f8f8f2">api_key,</span> <span style="color: #f8f8f2">chart_id,</span> <span style="color: #f8f8f2">new_data)</span>
<span style="color: #f8f8f2">republish_chart(charteca_root,</span> <span style="color: #f8f8f2">api_key,</span> <span style="color: #f8f8f2">chart_id)</span>
<span style="color: #f8f8f2">sys</span><span style="color: #f92672">.</span><span style="color: #f8f8f2">exit(</span><span style="color: #ae81ff">0</span><span style="color: #f8f8f2">)</span>
</pre>
</div>
Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com3tag:blogger.com,1999:blog-2606138741797457457.post-8853741953749893272017-05-09T15:16:00.001-07:002017-11-24T09:04:16.526-08:00charte.ca number formattingUsers want to have the ability to tweak number formatting. No, users <b>need</b> this feature. Just imagine having labels like "1382323332" and "1340152688" on a diagram comparing Chinese and Indian population. Or imagine a US GDP chart with a "$20000000000000" mark on the vertical axis. Or imagine that your audience wants to see negative amounts of some specific currency: "-£349.3300".<br />
<br />
charte.ca supports number formatting for axis marks, data labels and callouts:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2PnfpVchaKjuyMVzfncmoJpVh6J7tj-O73Cfk6eKn5te84nBwgc8wLsrPLOaSbPzGF6tdj7lI0RB1Jmo3LmNhnEhDoJhZ5vvsg8IAwMeHrfNkf3kEQmuBz3PrWc3UL6wuQIt38aq8KQP3/s1600/2017-05-09-number+format-fields.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="294" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj2PnfpVchaKjuyMVzfncmoJpVh6J7tj-O73Cfk6eKn5te84nBwgc8wLsrPLOaSbPzGF6tdj7lI0RB1Jmo3LmNhnEhDoJhZ5vvsg8IAwMeHrfNkf3kEQmuBz3PrWc3UL6wuQIt38aq8KQP3/s320/2017-05-09-number+format-fields.png" width="320" /></a></div>
<br />
<br />
Since the rules of number formatting can be complicated, we decided not to invent our own mechanism for it. We just use the standard instead: <a href="https://www.ecma-international.org/ecma-402/1.0/" target="_blank">ECMAScript Internationalization API Specification</a>. It's a long specification, but the part that we are interested in boils down to <a href="https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/NumberFormat" target="_blank">this Mozilla document</a>. This document defines how Javascript developers can specify the locale and formatting options when converting numbers to strings. For example, an object that enables Japanese yen formatting can be instantiated as follows:<br />
<br />
<span style="color: #9fc5e8; font-family: "courier new" , "courier" , monospace;">new Intl.NumberFormat('ja-JP', { style: 'currency', currency: 'JPY' });</span><br />
<br />
and it can produce strings like "¥123,457".
charte.ca does not provide any programming platform for chart creators, but it allows to pass the locale 'ja-JP' and the option object { style: 'currency', currency: 'JPY' } as additional parameters to the {value} keyword used in axis marks, data labels and callouts. The convention is to extend the "value" keyword with a semicolon followed by the number format specification:<br />
<br />
<span style="color: #9fc5e8; font-family: "courier new" , "courier" , monospace;">NumberFormat:{locale:'<desired locale><desired locale="">', options:<desired options><desired options="">}</desired></desired></span><br />
<desired locale=""><desired options=""><br /></desired></desired>
<desired locale=""><desired options="">For example:</desired></desired><br />
<desired locale=""><desired options=""><br /></desired></desired>
<desired locale=""><desired options=""><span style="color: #9fc5e8; font-family: "courier new" , "courier" , monospace;">{value;NumberFormat:{locale:'en', options:{useGrouping: true }}}</span> will produce numbers with comma as a thousands separator</desired></desired><br />
<desired locale=""><desired options=""><br /></desired></desired>
<desired locale=""><desired options=""><span style="color: #9fc5e8; font-family: "courier new" , "courier" , monospace;">{value;NumberFormat:{locale:'en-US',options: style:'currency',currency:'GBP',minimumFractionDigits:4}}}</span> will produce British pound amounts with 4 digits after decimal point.</desired></desired><br />
<desired locale=""><desired options=""><br /></desired></desired>
<desired locale=""><desired options="">In the example below, the callout format</desired></desired><br />
<desired locale=""><desired options=""><br /></desired></desired>
<desired locale=""><desired options=""><span style="color: #9fc5e8; font-family: "courier new" , "courier" , monospace;">{series}: {value;NumberFormat:{locale:'en', options:{useGrouping: true }}}</span></desired></desired><br />
<desired locale=""><desired options=""><br /></desired></desired>
<desired locale=""><desired options="">is specified, which makes the callout for the "0-4" age group with value 1453708 to be formatted as "0-4: 1,453,708":
</desired></desired><br />
<desired locale=""><desired options=""><br /></desired></desired>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiZ1_qE-ShswaCtH0dlbKk5YJb8josaWcuv21mKRtEQJbu-qZDVwjIUR79RvO8aC8fpS5OnxpL7j1BjJ7ADoq_J1S4syiHeUXIZcj3iyn4bQ-TDEC0cShDkSwbUYLqoRIencTz3NaWPvmQ/s1600/2017-05-09-number+format-in-action.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="144" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgiZ1_qE-ShswaCtH0dlbKk5YJb8josaWcuv21mKRtEQJbu-qZDVwjIUR79RvO8aC8fpS5OnxpL7j1BjJ7ADoq_J1S4syiHeUXIZcj3iyn4bQ-TDEC0cShDkSwbUYLqoRIencTz3NaWPvmQ/s320/2017-05-09-number+format-in-action.png" width="320" /></a></div>
<desired locale=""><desired options=""><br /></desired></desired>
<desired locale=""><desired options=""><br /></desired></desired>Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-22721253906540994692017-05-07T15:02:00.002-07:002017-11-24T09:04:55.217-08:00Comparative histogram charts: population by age group<div class="separator" style="clear: both; text-align: left;">
Great news: <a href="http://charte.ca/">charte.ca</a> now supports comparative histograms. This kind of charts comes handy when you want to compare two or more groups of series side-by-side. In most cases, this type of charts is used to draw a <a href="https://en.wikipedia.org/wiki/Population_pyramid" target="_blank">population pyramid</a>, but it has other uses as well.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
The following chart shows country population by age group, for multiple years, comparing male and female population.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div style="background-image: url('https://lh3.googleusercontent.com/CRFZ2MH9rx_zj73-IRk2san9CrzNAwwZOLiaYLh5FiEXYxWmulE5N7YRennYwtjW9xt5nbRBTiHNLbjm2GS9Bxwd9qkco0IvIF3Hp1psyyJcj7CSajYjx8sZDOiJUxlNDS1gRmyCegloxeUb5JST4l5jN2cR-GDYksf3EItT64q4Se-Ft3lx2Clkeo_iW7iZ40Z3QcGBH-oui3srSSkDqvCQXbZd-2NKbnRJMueKiaknK5OioQGNKuL1T1HEeFm9HUekhMox6wiOp26VUWoaovTktFnfat3gya5N32EBW58zgDbszpsIGW1QiDsI-pgkIFR17Xbg83k_xLkkJg5KGI99L22Ecvz0id6d_PyH_CzPYJgZPm1sA9NSP9JFsreglOr4Si-xpHLCyBCwGx98smGemMGkvPChNP1k57CZkPXZXE9WrQUScId0iJ3NUf4PlXo0ej3zCbEgzaS3z8xI8PDJhUlYhnBXe0aRdlhfiUlvdu1EkYCgHJEx2OgBABGVdYHJOj09kfe5rkqqTV77Yp9lExVbuM2UtYKaCIAHQmoJPucIABcd_FI77AqHvnQMB3iTT0HbYyI_fkabnkoHQs2Yw2SWTuuqLRX_fi08771ihRzGimYl=w800-h600-no'); background-position: left top; height: 600px; width: 800px;">
<embed src="https://charteca.blob.core.windows.net/pubs/4PskCgpsTy3QYdq8" style="height: 600px; width: 800px;" type="image/svg+xml"></embed>
</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
This post discusses comparative histogram creation process step-by-step.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<a name='more'></a><div style="text-align: left;">
In "My charts" section of the editor, click the "New chart" button. Select grouped comparative histogram chart type:</div>
<br />
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjidJNND-wYL1xT9ETIpV_JvC5KFBiRASgF8NOdnmeGD6ZszysK5441Q1_FDFVxJ9xquFwAAhjbYM5axdtKZK_qwhIkBa1rNbGhwWnYFfCWTw3T7Gf-gBXJU9ByO3dQSRnbng38_1c4Re4f/s1600/new.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="244" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjidJNND-wYL1xT9ETIpV_JvC5KFBiRASgF8NOdnmeGD6ZszysK5441Q1_FDFVxJ9xquFwAAhjbYM5axdtKZK_qwhIkBa1rNbGhwWnYFfCWTw3T7Gf-gBXJU9ByO3dQSRnbng38_1c4Re4f/s320/new.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
The editor will create a chart with sample data. Now let's populate the chart with the <a href="https://docs.google.com/spreadsheets/d/16GwsK5htD4ZtdQj-Ep1mkZ8GdQcipCc3NMIlN55axDw/edit#gid=82786448" target="_blank">flat data from a spreadsheet which contains population data for a few selected countries</a> (provided by <a href="http://data.un.org/" target="_blank">UN data service</a>). Open the spreadsheet in the browser and copy all data rows to the clipboard. Now click "Import" button in the editor:</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1IaIXVqxSwuvaRoqmKds74mtD1haY4Wh_DZa9K7jRK5R6ELj873JyRqLKCGKRGV_TLkGCIj7W5VyPj115-muh83WZ7aOjwHF9ThPRgCf8Kg4FHUr6djqUQIwCn3oj6OKeY7TIccUw7XSx/s1600/import.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi1IaIXVqxSwuvaRoqmKds74mtD1haY4Wh_DZa9K7jRK5R6ELj873JyRqLKCGKRGV_TLkGCIj7W5VyPj115-muh83WZ7aOjwHF9ThPRgCf8Kg4FHUr6djqUQIwCn3oj6OKeY7TIccUw7XSx/s320/import.png" width="314" /></a></div>
<br />
The import wizard will offer you to enter your data. Just paste the data you copied from the spreadsheet over the existing tab- or comma-separated text:<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrfLmVpE88fbaMBEyhmzCksegPBDlHYwaUWMMammqfqJq4MHzFKanUx8ZUCSb3Jw2LQv226iJxEXB96CADrDSpb3NH-v5DN1AhT1A7UKHHreSfk-DxzCKObK6Ff2l6vtg8Dy2CrPhRIn87/s1600/step1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="247" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrfLmVpE88fbaMBEyhmzCksegPBDlHYwaUWMMammqfqJq4MHzFKanUx8ZUCSb3Jw2LQv226iJxEXB96CADrDSpb3NH-v5DN1AhT1A7UKHHreSfk-DxzCKObK6Ff2l6vtg8Dy2CrPhRIn87/s320/step1.png" width="320" /></a></div>
<br />
On the next step, tell the wizard how to build the pivot table from raw data: specify which flat data columns should be used for series (sex), sub-series (age group), category (year) and values (population):<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPPyK_bmNEncN2LnEA-WgBEy2XB0GKsUqxJfPihmM6j0plreEJUHQYWhpr8CoQjr2EOSmR2KUZYc4BS3CMdWk4UbDC7g5lVYQVg9Bn7x_IoZJ6nVdd9LHg5ig1S06fqcl9fJniBMMd3-oF/s1600/step2-arrows.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="139" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPPyK_bmNEncN2LnEA-WgBEy2XB0GKsUqxJfPihmM6j0plreEJUHQYWhpr8CoQjr2EOSmR2KUZYc4BS3CMdWk4UbDC7g5lVYQVg9Bn7x_IoZJ6nVdd9LHg5ig1S06fqcl9fJniBMMd3-oF/s320/step2-arrows.png" width="320" /></a></div>
<br />
On the next step, select a country to filter the data by (it happened to be Spain in this example):<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgT9UU3vBR5oYcBrPWp-QVQqli3vROf1Gzvyf-pXJC_fELj_Wf1aZonregcUDLgn7GxbjBJ0MyG3JROv1Hy5s8s7-x_zKGoDBaM_O1oy2bVeDXD3cP_ivCsjrlSH7hELFcwfeH815aGg9Rj/s1600/step3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="155" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgT9UU3vBR5oYcBrPWp-QVQqli3vROf1Gzvyf-pXJC_fELj_Wf1aZonregcUDLgn7GxbjBJ0MyG3JROv1Hy5s8s7-x_zKGoDBaM_O1oy2bVeDXD3cP_ivCsjrlSH7hELFcwfeH815aGg9Rj/s320/step3.png" width="320" /></a></div>
<br />
On the last step, make sure that series and categories headers are not sorted - we are assuming that flat data has them in the right order already:<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0ZWSF4qfJ4aOAXW-GY83VVwxTXQeAIZHa-sYr-mNKIuPUGScoDPOXsWjxGYl8kBHeBOrKKfL0_Z2Mi8nNpkcOXN_lF62CbV2d8CwTlYEJdXGg9eKt-txQJl3Xr6SkGLByg7tMtkFlcx_C/s1600/step5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="112" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0ZWSF4qfJ4aOAXW-GY83VVwxTXQeAIZHa-sYr-mNKIuPUGScoDPOXsWjxGYl8kBHeBOrKKfL0_Z2Mi8nNpkcOXN_lF62CbV2d8CwTlYEJdXGg9eKt-txQJl3Xr6SkGLByg7tMtkFlcx_C/s320/step5.png" width="320" /></a></div>
<br />
After you click "Done", the import wizard will create a pivot table from the flat data and populate the editor data grid. Save the chart, tweak chart appearance by changing colors for the Male and Female series, adjust chart size and font size and the chart is ready to be published!
<br />
<br />
P.S.<br />
<br />
For those who prefer the population pyramid style, here is a horizontal mirrored histogram presenting the same data:<br />
<br />
<div style="background-image: url('https://lh3.googleusercontent.com/CRFZ2MH9rx_zj73-IRk2san9CrzNAwwZOLiaYLh5FiEXYxWmulE5N7YRennYwtjW9xt5nbRBTiHNLbjm2GS9Bxwd9qkco0IvIF3Hp1psyyJcj7CSajYjx8sZDOiJUxlNDS1gRmyCegloxeUb5JST4l5jN2cR-GDYksf3EItT64q4Se-Ft3lx2Clkeo_iW7iZ40Z3QcGBH-oui3srSSkDqvCQXbZd-2NKbnRJMueKiaknK5OioQGNKuL1T1HEeFm9HUekhMox6wiOp26VUWoaovTktFnfat3gya5N32EBW58zgDbszpsIGW1QiDsI-pgkIFR17Xbg83k_xLkkJg5KGI99L22Ecvz0id6d_PyH_CzPYJgZPm1sA9NSP9JFsreglOr4Si-xpHLCyBCwGx98smGemMGkvPChNP1k57CZkPXZXE9WrQUScId0iJ3NUf4PlXo0ej3zCbEgzaS3z8xI8PDJhUlYhnBXe0aRdlhfiUlvdu1EkYCgHJEx2OgBABGVdYHJOj09kfe5rkqqTV77Yp9lExVbuM2UtYKaCIAHQmoJPucIABcd_FI77AqHvnQMB3iTT0HbYyI_fkabnkoHQs2Yw2SWTuuqLRX_fi08771ihRzGimYl=w800-h600-no'); background-position: left top; height: 600px; width: 800px;">
<embed src="https://charteca.blob.core.windows.net/pubs/Nv7LaWDhgxARR3ey" style="height: 600px; width: 800px;" type="image/svg+xml"></embed>
</div>
<br />
Please pay attention to the age groups: on the population pyramid chart, they are sorted in the descending order - greater values (100+) at the top, smaller values (0-4) at the bottom. On the grouped chart in the beginning of the post, they are sorted in ascending order: smaller values (0-4) on the left, greater values (100+) on the right.
<br />
<br />
P.P.S.<br />
<br />
Animated population pyramid (available since June 2017):<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<embed src="https://charteca.blob.core.windows.net/pubs/XZdutd9WeveRgtke" style="height: 720px; width: 720px;" type="image/svg+xml"></embed>
</div>
Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-9258026951298166122017-01-06T15:36:00.001-08:002017-05-19T13:12:42.020-07:00US and Canada demographics: immigration, temporary workers, refugees in 1935-2014<div style="text-align: justify;">
I was curious about the differences between American and Canadian immigration systems. After 2016 US elections, we hear a lot about those differences, but I could not find a good source with actual numbers compared. Let's do it here. As usual, <a href="https://docs.google.com/spreadsheets/d/1jraBsEQhjnewgScYsuSYyFy4eV6od9R39Ew9iIXrJWE" target="_blank">here is the Google Docs spreadsheet</a> with all relevant data. As usual, all charts below are interactive, just move your mouse over the line or the legend item.
</div>
<br />
<div style="text-align: justify;">
Let's start with the simplest part - total population numbers provided by Wikipedia:
</div>
<br />
<div style="background-position: left top; background: white; height: 600px; width: 600px;">
<embed src="https://charteca.blob.core.windows.net/pubs/8dGBVzhw5qMht392" style="height: 600px; width: 600px;" type="image/svg+xml"></embed>
</div>
<br />
<div style="text-align: justify;">
No surprises here - these countries are economically and culturally close, so population growth patterns are similar.</div>
<br />
<div style="text-align: justify;">
Let's have a look at natural population growth numbers pulled from <a href="https://esa.un.org/unpd/wpp/dataquery/" target="_blank">UNDESA (UN Department of Economic and Social Affairs)</a> and <a href="http://data.worldbank.org/indicator/SP.DYN.TFRT.IN" target="_blank">The World Bank</a>:
</div>
<a name='more'></a><br />
<div style="background-position: left top; background: white; height: 600px; width: 600px;">
<embed src="https://charteca.blob.core.windows.net/pubs/G7acGqb4zNDgqWKy" style="height: 600px; width: 600px;" type="image/svg+xml"></embed>
</div>
<br />
<div style="text-align: justify;">
Here we have an indication that, since early nineties, natural population growth in Canada is slower than in US. How does Canada keep its total population growth rate on par with its neighbor down South? Immigration may be the answer. The following chart shows the number of new permanent residents per year for both countries:
</div>
<br />
<div style="background-position: left top; background: white; height: 600px; width: 800px;">
<embed src="https://charteca.blob.core.windows.net/pubs/p2vtYUyQR6ZY2TuF" style="height: 600px; width: 800px;" type="image/svg+xml"></embed>
</div>
<br />
<div style="text-align: justify;">
The PR numbers are available for both countries and can be easily compared. Now to the most challenging part: temporary workers and refugees. I decided to focus on temporary resident entry statistics - this data is better structured for both countries. Alternative approach - counting temporary residents who are actually present in the country at the specific date of the year - looked less transparent to me.
</div>
<br />
<div style="text-align: justify;">
For the US, I counted in CW,E,F,GH,I,J,K,L,M,O,S,TN,TD,T,U,V visa holders, putting aside all "visitor" visas like B,C,D,NATO etc. I also did not count <a href="https://en.wikipedia.org/wiki/Border_Crossing_Card" target="_blank">Border Crossing Cards</a>. Below is the full list of the US non-immigrant visa types:
</div>
<br />
<div style="text-align: justify;">
<table border="1" style="font-size: small; text-align: left;">
<thead>
<tr><th style="width: 100px;">Visa type</th><th style="width: 100px;">Included</th><th>Description</th></tr>
</thead>
<tbody>
<tr><td>A</td><td></td><td>Foreign Government Official</td></tr>
<tr><td>B-1</td><td></td><td>Temporary Visitor for Business</td></tr>
<tr><td>B-1/B-2</td><td></td><td>Temporary Visitor for Business and Pleasure</td></tr>
<tr><td>B1/B2/BCC</td><td></td><td>Combination B1/B2 and Border Crossing Card</td></tr>
<tr><td>B-2</td><td></td><td>Temporary Visitor for Pleasure</td></tr>
<tr><td>C</td><td></td><td>Transit</td></tr>
<tr><td>C-1/D</td><td></td><td>Combination Transit/Crew Member</td></tr>
<tr><td>CW</td><td>Y</td><td>Commonwealth of Northern Mariana Islands Transitional Worker or Investor</td></tr>
<tr><td>D</td><td></td><td>Crew Member</td></tr>
<tr><td>E</td><td>Y</td><td>Treaty Trader or Investor</td></tr>
<tr><td>F</td><td>Y</td><td>Student</td></tr>
<tr><td>G</td><td>Y</td><td>Representative/Staff of International Organization</td></tr>
<tr><td>H</td><td>Y</td><td>Temporary Worker and Trainee (H-2A seasonal agriculture, H-2B - seasonal non-agriculture)</td></tr>
<tr><td>I</td><td>Y</td><td>Representative of Foreign Information Media</td></tr>
<tr><td>J</td><td>Y</td><td>Exchange Visitor</td></tr>
<tr><td>K</td><td>Y</td><td>Fiance(e) of U.S. Citizen</td></tr>
<tr><td>L</td><td>Y</td><td>Intracompany Transferee</td></tr>
<tr><td>M</td><td>Y</td><td>Vocational Student</td></tr>
<tr><td>N</td><td>Y</td><td>Certain Relatives of SK Special immigrants</td></tr>
<tr><td>NAFTA</td><td>Y</td><td>NAFTA Professional</td></tr>
<tr><td>NATO</td><td></td><td>NATO Official</td></tr>
<tr><td>O</td><td>Y</td><td>Person With Extraordinary Ability in the Sciences,Arts, Education, Business, or Athletics</td></tr>
<tr><td>P</td><td></td><td>Athlete, Artist or Entertainer</td></tr>
<tr><td>Q</td><td></td><td>International Cultural Exchange Program Participant</td></tr>
<tr><td>R</td><td></td><td>Person in a Religious Occupation</td></tr>
<tr><td>S</td><td>Y</td><td>Informant Possessing Information on Criminal Activity or Terrorism</td></tr>
<tr><td>T</td><td>Y</td><td>Victim of Severe Form of Trafficking in Persons</td></tr>
<tr><td>U</td><td>Y</td><td>Victim of Criminal Activity</td></tr>
<tr><td>V</td><td>Y</td><td>Nonimmigrant visa created to allow families to stay together while waiting for the processing of immigrant visas (https://www.uscis.gov/family/family-green-card-holders-permanent-residents/v-visa/v-nonimmigrant-visas)</td></tr>
</tbody>
</table>
</div>
<br />
<div style="text-align: justify;">
For Canada, I included all temporary residents that entered the country via IMO (International Mobility) program and TFWP (Temporary Foreign Worker) program. <a href="https://www.canada.ca/en/employment-social-development/services/foreign-workers/employers/overhaul.html" target="_blank">All IMO and TFWP sub-statuses</a> were used in the calculation. Those sub-statuses correlate with the choices I made in the US visa type list above.
</div>
<br />
<div style="text-align: justify;">
Refugee entries were easy to count. Unfortunately, the data is limited to 1997-2014 for US and 2005-2014 for Canada. I have combined PR, temporary workers and refugees numbers in the same chart.
</div>
<br />
<div style="background-position: left top; background: white; height: 600px; width: 800px;background:white;background-image: url('data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iODAwIiBoZWlnaHQ9IjgwMCIgeG1sbnM9Imh0dHA6Ly93d3cudzMub3JnLzIwMDAvc3ZnIiB4bWxuczp4bGluaz0iaHR0cDovL3d3dy53My5vcmcvMTk5OS94bGluayIgdmVyc2lvbj0iMS4xIj4NCg0KICA8cmVjdCB4PSIzODAiIHk9IjE4NSIgd2lkdGg9IjM4MCIgaGVpZ2h0PSIyMzUiIHJ4PSIxMCIgcnk9IjEwIiBmaWxsPSIjRTBFMEUwIiBvcGFjaXR5PSIxIi8+DQogIDx0ZXh0IHg9IjM5NSIgeT0iMjA1IiBmaWxsPSIjMjAyMDIwIiBmb250LWZhbWlseT0iVGFob21hIiBmb250LXNpemU9IjEycHgiPlRlbXBvcmFyeSB3b3JrZXJzLCBzdHVkZW50cyBhbmQgdGhlaXIgc3BvdXNlczwvdGV4dD4NCg0KICA8cmVjdCB4PSIyNDAiIHk9IjQ4NSIgd2lkdGg9IjUyMCIgaGVpZ2h0PSI3MCIgcng9IjEwIiByeT0iMTAiIGZpbGw9IiNFMEUwRTAiIG9wYWNpdHk9IjEiLz4NCiAgPHRleHQgeD0iMjYwIiB5PSI1MTAiIGZpbGw9IiMyMDIwMjAiIGZvbnQtZmFtaWx5PSJUYWhvbWEiIGZvbnQtc2l6ZT0iMTJweCI+UmVmdWdlZXM8L3RleHQ+DQoNCjwvc3ZnPg==');"">
<embed src="https://charteca.blob.core.windows.net/pubs/bDhE3kcQFgyxV4vz" style="height: 600px; width: 800px;" type="image/svg+xml"></embed>
</div>
<br />
<div style="text-align: justify;">
Lessons learned:
</div>
<br />
<div style="text-align: justify;">
<ul>
<li>temporary workers and refugees numbers are hard to find compared to PR statistics;</li>
<li>Canada relies heavily on permanent immigrants and temporary workers;</li>
<li>for both countries, the number of refugees arriving each year is much smaller than the number of new PRs and temporary workers and students.</li>
</ul>
</div>
<br />
<br />
<br />
<br />
Tools used: Microsoft Excel, <a href="https://www.charte.ca/" target="_blank">charte.ca</a> online chart editor
<br />
<br />
Sources:
<br />
<ul>
<li style="text-align: left;">US population - <a href="https://en.wikipedia.org/wiki/Demography_of_the_United_States">https://en.wikipedia.org/wiki/Demography_of_the_United_States</a></li>
<li style="text-align: left;">Canada population - <a href="https://en.wikipedia.org/wiki/Demographics_of_Canada">https://en.wikipedia.org/wiki/Demographics_of_Canada</a></li>
<li style="text-align: left;">Natural increase: UNDESA - <a href="https://esa.un.org/unpd/wpp/dataquery/">https://esa.un.org/unpd/wpp/dataquery/</a></li>
<li style="text-align: left;">Fertility rate: The World Bank - <a href="http://data.worldbank.org/indicator/SP.DYN.TFRT.IN">http://data.worldbank.org/indicator/SP.DYN.TFRT.IN</a></li>
<li style="text-align: left;">US new permanent residents: Department of Homeland Security - <a href="https://www.dhs.gov/sites/default/files/publications/Yearbook_Immigration_Statistics_2012.pdf">https://www.dhs.gov/sites/default/files/publications/Yearbook_Immigration_Statistics_2012.pdf
</a></li>
<li style="text-align: left;">Canada new permanent residents: Government of Canada - <a href="http://www.cic.gc.ca/english/resources/statistics/facts2014/permanent/index.asp#figure1">http://www.cic.gc.ca/english/resources/statistics/facts2014/permanent/index.asp#figure1
</a></li>
<li style="text-align: left;">US non-resident visa entries: US Department of state - <a href="https://travel.state.gov/content/dam/visas/Statistics/AnnualReports/FY2015AnnualReport/FY15AnnualReport-TableXVIA.pdf">https://travel.state.gov/content/dam/visas/Statistics/AnnualReports/FY2015AnnualReport/FY15AnnualReport-TableXVIA.pdf
</a></li>
<li style="text-align: left;">Canada IMO and TFWP entries: Government of Canada - <a href="http://www.edsc.gc.ca/eng/jobs/foreign_workers/reform/index.shtml">http://www.edsc.gc.ca/eng/jobs/foreign_workers/reform/index.shtml
</a></li>
<li style="text-align: left;">US refugee arrivals: Refugee Processing Center - <a href="http://www.wrapsnet.org/archives/">http://www.wrapsnet.org/archives/</a></li>
<li style="text-align: left;">Canada refugee arrivals: Citizenship and Immigration Canada (published by CBC news) - <a href="http://www.cbc.ca/news/canada/canada-s-refugees-by-the-numbers-the-data-1.3240640">http://www.cbc.ca/news/canada/canada-s-refugees-by-the-numbers-the-data-1.3240640
</a></li>
</ul>
<br />
Also, there is an excellent 2012 paper by Magali Barbieri and Nadine Ouellette: <a href="https://www.ncbi.nlm.nih.gov/pmc/articles/PMC3768295/" target="_blank">The Demography of Canada and the United States from the 1980s to the 2000s A Summary of Changes and a Statistical Assessment</a>, although I did not use any numbers from it.
Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com0tag:blogger.com,1999:blog-2606138741797457457.post-61780424734468161992016-12-18T21:30:00.000-08:002017-11-24T09:06:04.142-08:00Scatter/Bubble charts and regression analysis: median income and obesity levels<div style="text-align: justify;">
Is there a correlation between median income and obesity? I have put some numbers from a few data sources (see full list in the end of the post) in a single <a href="https://docs.google.com/spreadsheets/d/1qE2q7PNTijrBwnWV1Sp57fgM6X3Ve9KVTocY66rM1TI" target="_blank">GoogleDocs spreadsheet</a>.
I have created a new grouped bubble chart and imported the data using CSV/TSV wizard, using Region/Haplogroup column for grouping:
</div>
<br />
<div style="text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhE-LuqZd-H1JLg7_QSNPZhR2Zn2Ddb38UaksRfXc9Cr2B4ld5L-F5sgvzoxO59QJwfjfiltppWTXqsFAiHdzOptKClJVlNCTUKRuSXEqHrFi6GpWubJOW_qiNL8nfbQZd7rM4BwYUOUB2t/s1600/2016-12-20-income-obesity-import.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="256" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhE-LuqZd-H1JLg7_QSNPZhR2Zn2Ddb38UaksRfXc9Cr2B4ld5L-F5sgvzoxO59QJwfjfiltppWTXqsFAiHdzOptKClJVlNCTUKRuSXEqHrFi6GpWubJOW_qiNL8nfbQZd7rM4BwYUOUB2t/s320/2016-12-20-income-obesity-import.png" width="320" /></a>
</div>
<a name='more'></a><br />
<br />
<div style="text-align: justify;">
In the chart editor, I played with the new panel that allows to choose regression method and turn on and off group-by-group regression.
Finding correlation between incomes and obesity levels without grouping countries by region does is too challenging, so I decided to focus on running regressions in a few clusters of countries instead.
I used prevailing haplogroup data for grouping, so do not be surprised when you see Jamaica and Barbados in the African cluster, and Mongolia in the Europid cluster.
You will not see Oceania in this spreadsheet because of the lack of both income and haplogroup data.
And you will not see Switzerland here because Gallup did not publish income survey results for it.
</div>
<br />
<div style="text-align: justify;">
As expected, linear regression did not give good results. Logarithmic regression looked better, but the countries on the right end of the curve did not fit well in the picture. Polynomial worked the best (the chart is a bit oversize):
</div>
<br />
<div style="background-position: left top; background: white; height: 1000px; width: 1200px;">
<embed src="https://charteca.blob.core.windows.net/pubs/xRvcRB6hD9eyqxgr" style="height: 1000px; width: 1200px;" type="image/svg+xml"></embed>
</div>
<br />
<div style="text-align: justify;">
The pattern is clear: obesity level grows with median income up to the point when higher taxes kick in and drive obesity level down. This is NOT a scientific research, of course. But the chart works well to demonstrate the regression feature.
</div>
<br />
<br />
<br />
<br />
Tools used: Microsoft Excel, <a href="https://www.charte.ca/" target="_blank">charte.ca</a> online chart editor
<br />
<br />
Sources:
<br />
<ul>
<li style="text-align: left;">Median income: Gallup survey 2006-2012 - <a href="http://www.gallup.com/poll/166211/worldwide-median-household-income-000.aspx"> http://www.gallup.com/poll/166211/worldwide-median-household-income-000.aspx</a></li>
<li style="text-align: left;">Population: Wikipedia - <a href="https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)"> https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)</a></li>
<li style="text-align: left;">Obesity: WHO 2014 18+ male and female - <a href="http://apps.who.int/gho/data/node.main.A897A?lang=en">http://apps.who.int/gho/data/node.main.A897A?lang=en</a></li>
<li style="text-align: left;">Haplogroup information - <a href="http://atlas.xyvy.info/Haplogroup-Map-Interactive/majority-haplogroup-country-map">http://atlas.xyvy.info/Haplogroup-Map-Interactive/majority-haplogroup-country-map</a>,
<a href="https://en.wikipedia.org/wiki/Y-DNA_haplogroups_in_South_Asian_populations">https://en.wikipedia.org/wiki/Y-DNA_haplogroups_in_South_Asian_populations</a>,
<a href="https://en.wikipedia.org/wiki/Y-DNA_haplogroups_by_populations_of_East_and_Southeast_Asia">https://en.wikipedia.org/wiki/Y-DNA_haplogroups_by_populations_of_East_and_Southeast_Asia</a></li>
<li style="text-align: left;">Life expectancy: UN World Population Prospects 2015, published by Wikipedia - <a href="https://en.wikipedia.org/wiki/List_of_countries_by_life_expectancy">https://en.wikipedia.org/wiki/List_of_countries_by_life_expectancy</a></li>
</ul>
Adminhttp://www.blogger.com/profile/14265453423147005412noreply@blogger.com1