<div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="https://www.r-bloggers.com/r-developers-are-the-happiest-based-on-analyzing-stack-overflow/"></div></div><div></div></div><div></div></div>
<div><em><strong>This post was written by <a target="_blank" rel="nofollow" href="https://twitter.com/srobtweets">Sara Robinson</a> and originally <a target="_blank" rel="nofollow" href="https://hackernoon.com/which-programming-languages-have-the-happiest-and-angriest-commenters-ebe91b3852ed#.5j0qkm174">published on Medium</a>, and is being re-published here since it is particularly relevant to the R community. The post analyzes all Stack Overflow comments using Google BigQuery to determine which developers are happiest and angriest.  If you have comments or ideas for future analysis, find me on Twitter <a target="_blank" data-href="https://twitter.com/srobtweets" rel="nofollow" href="https://twitter.com/srobtweets">@SRobTweets</a>.</strong></em></div>
<p>It’s officially winter, so what could be better than drinking hot chocolate while querying the new <a target="_blank" data-href="https://cloud.google.com/blog/big-data/2016/12/google-bigquery-public-datasets-now-include-stack-overflow-q-a" rel="nofollow" href="https://cloud.google.com/blog/big-data/2016/12/google-bigquery-public-datasets-now-include-stack-overflow-q-a">Stack Overflow dataset in BigQuery</a>? It has every Stack Overflow question, answer, comment, and more — which means endless possibilities of data crunching. Inspired by <a data-user-id="279fe54c149a" data-action-value="279fe54c149a" target="_blank" data-href="https://medium.com/u/279fe54c149a" data-action-type="hover" rel="nofollow" href="https://medium.com/u/279fe54c149a" data-anchor-type="2" data-action="show-user-card">Felipe Hoffa</a>’s <a target="_blank" data-href="https://medium.com/@hoffa/always-end-your-questions-with-a-stack-overflow-bigquery-and-other-stories-2470ebcda7f#.3b7kldtci" rel="nofollow" href="https://medium.com/@hoffa/always-end-your-questions-with-a-stack-overflow-bigquery-and-other-stories-2470ebcda7f#.3b7kldtci">post</a> on how response time varies by tag, I wanted to look at the <a target="_blank" data-href="https://bigquery.cloud.google.com/table/bigquery-public-data:stackoverflow.comments?pli=1&amp;tab=schema" rel="nofollow" href="https://bigquery.cloud.google.com/table/bigquery-public-data:stackoverflow.comments?pli=1&tab=schema">comments table</a> (53 million rows!).</p>
<h3>The happiest Stack Overflow tags <img alt="🙂" class="wp-smiley" src="https://s.w.org/images/core/emoji/2/72x72/1f642.png"></h3>
<p>To measure happy comments I looked at comments with “thank you”, “thanks”, “awesome” or “:)” in the body. I limited the analysis to tags with more than 500,000 comments. Here’s <a target="_blank" data-href="https://bigquery.cloud.google.com:443/savedquery/513927984416:b3cc596db5ed4ab4bf03fde8f04087c4" rel="nofollow" href="https://bigquery.cloud.google.com/savedquery/513927984416:b3cc596db5ed4ab4bf03fde8f04087c4">the query</a>:</p>
<pre>#standardSQL
SELECT 
  tag, 
  ROUND((COUNT(case when comment_text like '%thanks%' or comment_text like '%:)%' or comment_text like '%thank you%' or comment_text like '%awesome%' then 1 end) / COUNT(*)) * 100,2) as percent_happy, 
  COUNT(*) total_comments
  FROM (
    SELECT
      LOWER(a.text) as comment_text, 
      SPLIT(b.tags, '|') as tags 
    FROM `bigquery-public-data.stackoverflow.comments` a
    JOIN `bigquery-public-data.stackoverflow.posts_questions` b
    ON a.post_id = b.id
    UNION ALL
    SELECT
      LOWER(b.text) as comment_text, 
      SPLIT(c.tags, '|') as tags 
    FROM `bigquery-public-data.stackoverflow.posts_answers` a
    JOIN (
     SELECT post_id, text FROM `bigquery-public-data.stackoverflow.comments`
    ) b
    ON a.id = b.post_id
    JOIN `bigquery-public-data.stackoverflow.posts_questions` c
    ON c.id = a.parent_id
), UNNEST(tags) tag
GROUP BY 1
HAVING total_comments > 500000
ORDER BY percent_happy DESC</pre>
<p>Here’s the result in BigQuery:</p>
<figure>
<div>
<div></div>
<div data-image-id="1*xxi4Z2h8aoT2xcFKKaK5VA.png" data-width="702" data-action-value="1*xxi4Z2h8aoT2xcFKKaK5VA.png" data-height="586" data-scroll="native" data-action="zoom"><canvas height="61" width="75"></canvas><img data-src="https://i1.wp.com/cdn-images-1.medium.com/max/1200/1*xxi4Z2h8aoT2xcFKKaK5VA.png?w=456&amp;ssl=1" src="https://i1.wp.com/cdn-images-1.medium.com/max/1200/1*xxi4Z2h8aoT2xcFKKaK5VA.png?w=456&ssl=1" data-recalc-dims="1"></div>
</div>
</figure>
<p>And the chart:</p>
<p><a target="_blank" rel="nofollow" href="https://i2.wp.com/www.r-bloggers.com/wp-content/uploads/2017/01/happiest-comments-on-SO.png?ssl=1"><img sizes="(max-width: 450px) 100vw, 450px" alt="happiest-comments-on-so" class="wp-image-145050" src="https://i2.wp.com/www.r-bloggers.com/wp-content/uploads/2017/01/happiest-comments-on-SO.png?resize=450%2C283&ssl=1" data-recalc-dims="1"></a></p>
<p>R, Ruby, HTML / CSS, and iOS are the communities with the happiest commenters according to this list. People who ask questions about XML and regular expressions also seem particularly thankful for help. If you’re curious, here are the 15 highest scoring happy comments that were short enough to fit in a screenshot (and their associated tags) :</p>
<figure>
<div>
<div></div>
<div data-image-id="1*rQOP7OCjc1_8wes4kdywUA.png" data-width="1455" data-action-value="1*rQOP7OCjc1_8wes4kdywUA.png" data-height="523" data-scroll="native" data-action="zoom"><canvas height="26" width="75"></canvas><img data-src="https://i1.wp.com/cdn-images-1.medium.com/max/1200/1*rQOP7OCjc1_8wes4kdywUA.png?w=456&amp;ssl=1" src="https://i1.wp.com/cdn-images-1.medium.com/max/1200/1*rQOP7OCjc1_8wes4kdywUA.png?w=456&ssl=1" data-recalc-dims="1"></div>
</div>
</figure>
<p>But because people sometimes get angry on the internet, you’re probably wondering…</p>
<h3>The angriest Stack Overflow tags <img alt="🙁" class="wp-smiley" src="https://s.w.org/images/core/emoji/2/72x72/1f641.png"></h3>
<p>For angry comments, I counted those with “wrong”, “horrible”, “stupid”, or “:(” in the body. The SQL is the same as above with the search terms swapped out. Here’s the result:</p>
<figure>
<div>
<div></div>
<div data-image-id="1*gGEnb-skq7OH5gJGfd7V9A.png" data-width="732" data-action-value="1*gGEnb-skq7OH5gJGfd7V9A.png" data-height="594" data-scroll="native" data-action="zoom"><canvas height="60" width="75"></canvas><img data-src="https://i1.wp.com/cdn-images-1.medium.com/max/1200/1*gGEnb-skq7OH5gJGfd7V9A.png?w=456&amp;ssl=1" src="https://i1.wp.com/cdn-images-1.medium.com/max/1200/1*gGEnb-skq7OH5gJGfd7V9A.png?w=456&ssl=1" data-recalc-dims="1"></div>
</div>
</figure>
<p>And the chart:</p>
<p><a target="_blank" rel="nofollow" href="https://i0.wp.com/www.r-bloggers.com/wp-content/uploads/2017/01/angriest-comments-on-SO.png?ssl=1"><img sizes="(max-width: 450px) 100vw, 450px" alt="angriest-comments-on-so" class="wp-image-145049" src="https://i0.wp.com/www.r-bloggers.com/wp-content/uploads/2017/01/angriest-comments-on-SO.png?resize=450%2C288&ssl=1" data-recalc-dims="1"></a></p>
<p>Clearly the angriest comments are those related to C derivatives. Many programming concepts also wound up here: multithreading, arrays, algorithms, and strings. And here are the highest scoring angry comments:</p>
<figure>
<div>
<div></div>
<div data-image-id="1*480AXyGHjJ8KQHjZBTirtQ.png" data-width="1610" data-action-value="1*480AXyGHjJ8KQHjZBTirtQ.png" data-height="520" data-scroll="native" data-action="zoom"><canvas height="23" width="75"></canvas><img data-src="https://i2.wp.com/cdn-images-1.medium.com/max/1200/1*480AXyGHjJ8KQHjZBTirtQ.png?w=456&amp;ssl=1" src="https://i2.wp.com/cdn-images-1.medium.com/max/1200/1*480AXyGHjJ8KQHjZBTirtQ.png?w=456&ssl=1" data-recalc-dims="1"></div>
</div>
</figure>
<p>This analysis is not perfect, as the comment “that one’s so stupid it underflows and becomes awesome” appears in both lists. That’s where a machine learning tool like the <a target="_blank" data-href="https://cloud.google.com/natural-language" rel="nofollow" href="https://cloud.google.com/natural-language">Natural Language API</a> would come in handy.</p>
<p>Between the two lists there were only a few tag overlaps. The most excitable tags (I’m interpreting tags that showed up in both the happy and angry list as ‘excitable’) are: ios, iphone, objective-c, and regex questions. And while the internet may seem like a dark place sometimes, there appears to be roughly six happy comments for every angry one.</p>
<h3>What’s next?</h3>
<p>Dive into the <a target="_blank" data-href="https://bigquery.cloud.google.com/dataset/bigquery-public-data:stackoverflow?pli=1" rel="nofollow" href="https://bigquery.cloud.google.com/dataset/bigquery-public-data:stackoverflow?pli=1">Stack Overflow dataset</a>, or check out some of these awesome posts to get inspired:</p>
<ul>
<li><a target="_blank" data-href="https://cloud.google.com/blog/big-data/2016/12/google-bigquery-public-datasets-now-include-stack-overflow-q-a" rel="nofollow" href="https://cloud.google.com/blog/big-data/2016/12/google-bigquery-public-datasets-now-include-stack-overflow-q-a">Stack Overflow dataset announcement</a></li>
<li><a target="_blank" data-href="http://thedatalane.com/playing-with-stack-overflow-dataset-on-google-bigquery/" rel="nofollow" href="http://thedatalane.com/playing-with-stack-overflow-dataset-on-google-bigquery/">Playing with Stack Overflow on BigQuery</a></li>
<li><a target="_blank" data-href="https://medium.com/@hoffa/always-end-your-questions-with-a-stack-overflow-bigquery-and-other-stories-2470ebcda7f#.ymekumual" rel="nofollow" href="https://medium.com/@hoffa/always-end-your-questions-with-a-stack-overflow-bigquery-and-other-stories-2470ebcda7f#.ymekumual">Always end your questions with a ‘?’</a></li>
</ul>
<p>If you have comments or ideas for future analysis, find me on Twitter <a target="_blank" data-href="https://twitter.com/srobtweets" rel="nofollow" href="https://twitter.com/srobtweets">@SRobTweets</a>.</p>
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/SB0jPfE6n3M">