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.
