we found some of the major concentration of open source coders around pretty cold places (Iceland, Sweden, Norway…). The question we want to solve today: Do programmers really prefer colder countries? Previously Open source coders per capita. Source: https://medium.com/@hoffa/github-top-countries-201608-13f642493773 First question to solve before we can correlate coders per capita to a country’s weather — First, let’s write the query, and then we’ll discuss if it makes sense: is it possible to get an “average temperature point” to define each country? #standardSQL
SELECT *, ROUND((fahr_avg-32)*5/9, 2) celsius_avg
FROM (
  SELECT country, COUNT(DISTINCT stn) stations, ROUND(AVG(temp), 2) fahr_avg
  FROM `bigquery-public-data.noaa_gsod.gsod2016` a
  JOIN `bigquery-public-data.noaa_gsod.stations` b
  ON a.stn=b.usaf AND a.wban=b.wban
  GROUP BY country
)
ORDER BY stations DESC Quick things we can see here: The US had 2,738 weather stations reporting data on the NOAA GSOD tables in 2016. Canada, Russia, Brazil, and Australia follow the US in number of stations, with 984, 938, 562, and 513. Russia is crazy cold, with an average yearly temperature overall below freezing. Meanwhile Brazil is the warmest in this sample, followed by Australia. Does it make sense to average all of these points and call it a country’s temperature? One of the supporting arguments is that weather stations are not evenly distributed across a country, but instead — this will weight our averages appropriately. weather stations concentrate around the biggest population centers A chart summarizing the average temperature for all the NOAA GSOD weather stations in each country: <a href="https://medium.com/media/5dab4a4672113419e8223500a869ac8c/href">https://medium.com/media/5dab4a4672113419e8223500a869ac8c/href</a> The chart makes sense — we see some countries with way larger dispersion than others, but their relative average position within each other matches our expectations (and yeah… Chad’s the hottest one). Now we can match this set with the GitHub’s “programmers per capita” metric: Yes — we can find an exponential trend line which matches our expectations: Some interesting observations: There’s a larger concentration of open source programmers around colder countries. Within the colder countries, some underperform the expected concentration of coders: Russia, Mongolia, Kazakhstan, Tajikistan, and North Korea. For warm weather and programmers, go to the countries that exceed our expectations: Australia, Brazil, United Arab Emirates, and Singapore. There are also some interesting findings if we group this data by continent: countries have the warmest weather and lowest concentration of coders. For more on this, . African see my report on Africa have the largest concentration of coders, and moderate weather: European countries In there’s a huge gap between the US, Canada, and the rest of the countries: North America - Warmer than Europe, but with lower concentrations of programmers: South America — above average temperature and number of coders, matching our expected curve: Oceania : It has some of the coldest and warmest countries. According to this chart the coldest Asian countries have the lowest concentrations of coders, while the hottest one (Singapore) has an outstanding number of them: Asia breaks our trends Data and queries: : ( ), ( ), — all on BigQuery. Data GitHub Archive Ilya Grigorik GHTorrent Georgios Gousios NOAA GSOD : , Google Sheets, ( ), , ( ). Tools BigQuery Exploratory Kan Nishida Tableau re:dash Arik Fraimovich #standardSQL
SELECT a.country, temp, ratio_unique_login, continent
FROM (
  SELECT c.country, ROUND(AVG(temp),2) temp, COUNT(DISTINCT stn) stations, ANY_VALUE(continent) continent
  FROM `bigquery-public-data.noaa_gsod.gsod2016` a
  JOIN `bigquery-public-data.noaa_gsod.stations` b
  ON a.stn=b.usaf AND a.wban=b.wban
  JOIN `gdelt-bq.extra.countryinfo2` c
  ON b.country=c.fips
  GROUP BY 1
  HAVING stations > 10 OR c.country='Singapore'
) a JOIN (
  SELECT c.country, ANY_VALUE(c.population) population
    , 10000*COUNT(DISTINCT login)/ANY_VALUE(c.population) ratio_unique_login 
  FROM `githubarchive.month.201608` a
  JOIN `ghtorrent-bq.ght_2017_04_01.users` b  
  ON a.actor.login=b.login
  JOIN `gdelt-bq.extra.countryinfo2` c   
  # http://download.geonames.org/export/dump/readme.txt 
  ON LOWER(c.iso)=b.country_code
  WHERE country_code != '\\N'
  AND population > 300000
  AND a.type='PushEvent'
  GROUP BY 1
) b
ON a.country=b.country
ORDER BY 3 DESC Next steps See other interesting weather posts: ’s , ’s . Lakshmanan V How to forecast demand with Google BigQuery, public datasets and TensorFlow Reto Meier Investigating Global Temperature Trends with BigQuery and Tableau Want more stories? Check my , , and subscribe to . And — every month you get a full terabyte of analysis for . Medium follow me on twitter reddit.com/r/bigquery try BigQuery free The top GitHub projects per country Which US cities are really the rainiest? I crunched the data.

Some coders like it hot — but most prefer colder climates

Too Long; Didn't Read

People Mentioned

Companies Mentioned

Coin Mentioned

@hoffa

RELATED STORIES