Top 30 projects from March 2017 (non forked, no bot counting) with projects hints from top 30 201604-201703

README.txt

This is a top 30 projects from March 2017.
It uses the same algorithm as Top 30 201604 - 201703 but skips all activity of users with name LIKE '%bot%' and takes only 2016-03 data.
Non forked repos, no bot activity, all others the same as in previous top 30s.

`data.csv` is a file generated by BigQuery `query.sql`.
I've manually (iterative) added projects to repos there (query doesn't return CSV "project" column - because there is no such data in BigQuery's githubarchive table)
Created ruby tool `analysis.rb` that takes intput from `data.csv` and `hint.csv` (hint.csv is output of top 30 last year bigquery with manually added projects)
hint.csv is used there: https://docs.google.com/spreadsheets/d/1IDkNpQ1Xa_zIsf8askHswxwW1GceXNoStdtnRm_PJVs/edit#gid=1169691230

`analysis.csv` loads hint.csv and creates mapping repo --> project.
Then loads `data.csv` (data for 201703) and takes project from there (manually added) if no project it lookups hint.csv, and if no projects found it is blank.
Tool generates projects.csv output which is an input for google sheet: https://docs.google.com/spreadsheets/d/1WO1NCzeso7_srYPMS3nspnAeqOImPDReAaOyZSIPJE4/edit#gid=278095662

Tool is used iteratively and each time I'm updating data.csv with new projects besed on analysis and visiting github websites.
Finally list of top 30 projects is used for generating chart and finally I'm manually adding each prject's URL.

analysis.rb

require 'csv'
require 'pry'

project_hints = {}
CSV.foreach('./hint.csv', headers: true) do |row|
  h = row.to_h
  project = h['project']
  next unless project
  repo = h['repo']
  project_hints[repo] = project
end

#puts 'Hints from hints.csv'
#p project_hints

orgs = {}
projects = {}
CSV.foreach('./data.csv', headers: true) do |row|
  h = row.to_h
  k = h['project']
  unless k
    rep = h['repo']
    h['project'] = project_hints[rep] if project_hints.key? rep
  end
  k = h['project']
  if k
    projects[k] = 0 unless projects.key?(k)
    projects[k] += 1
  end
  k = h['org'] unless k
  k = h['repo'] unless k
  next unless k
  orgs[k] = { items: [] } unless orgs.key? k
  h.each do |p, v|
    vi = v.to_i
    vis = vi.to_s
    h[p] = vi if vi > 0 && vis == v
  end
  orgs[k][:items] << h
end

orgs.each do |name, org|
    org[:sum] = {}
    org[:items].each do |repo|
        repo.each do |k, v|
          if v.is_a?(String)
            if repo['org'] && k == 'repo' && v.include?('/')
              v = v.split('/')[1]
            end
            if org[:sum].key? k
              org[:sum][k] = '' if org[:sum][k].nil?
              org[:sum][k] += '+' + v
            else
              org[:sum][k] = v 
            end
          elsif v.is_a?(Integer)
            org[:sum][k] = 0 unless org[:sum].key? k
            org[:sum][k] += v
          else
            org[:sum][k] = nil
          end
        end
    end
    new_org = org[:sum]['org']
    org[:sum]['org'] = new_org.split('+').uniq.join('+') if new_org
    new_prj = org[:sum]['project']
    org[:sum]['project'] = new_prj.split('+').uniq.join('+') if new_prj
end

orgs_arr = []
orgs.each do |name, org|
  orgs_arr << [name, org[:sum]['activity'], org]
end

res = orgs_arr.sort_by { |item| -item[1] }

puts "Defined projects: "
prjs = []
projects.keys.sort.each do |k|
  prjs << "#{k}: #{projects[k]}"
end
prjs = prjs.join(', ')
puts prjs

tops = res[0..30].map { |it| it[0] }.join(', ')
puts tops
puts 'Now debug values res[0][2][:items][0][\'project\'] = \'proj_name\' and save'
puts 'res[0..20].map { |it| it[0] }'
binding.pry

CSV.open("projects.csv", "w", headers: res[0][2][:sum].keys) do |csv|
  csv << res[0][2][:sum].keys
  res.each do |row|
    csv << row[2][:sum].values
  end
end

res2 = []
res3 = []
res.each do |line|
  org = line[2]
  org[:items].each do |repo|
    res2 << [repo['repo'], repo['activity'], repo]
    res3 << [repo['repo'], repo['activity'], repo]
  end
  res3 << [org[:sum]['repo'], org[:sum]['activity'], org[:sum]]
end

res2 = res2.sort_by { |item| -item[1] }
res3 = res3.sort_by { |item| -item[1] }

CSV.open("combined.csv", "w", headers: res3[0][2].keys) do |csv|
  csv << res3[0][2].keys
  res3.each do |row|
    csv << row[2].values
  end
end

data.csv

hint.csv

projects.csv

query.sql

SELECT
  org.login as org,
  repo.name as repo,
  count(*) as activity,
  SUM(IF(type = 'IssueCommentEvent', 1, 0)) as comments,
  SUM(IF(type = 'PullRequestEvent', 1, 0)) as prs,
  SUM(IF(type = 'PushEvent', 1, 0)) as commits,
  SUM(IF(type = 'IssuesEvent', 1, 0)) as issues,
  EXACT_COUNT_DISTINCT(JSON_EXTRACT(payload, '$.commits[0].author.email')) AS authors
from (
  select * from 
    [githubarchive:month.201703],
  )
WHERE
  type in ('IssueCommentEvent', 'PullRequestEvent', 'PushEvent', 'IssuesEvent')
  AND (type = 'PushEvent' OR (type != 'PushEvent' AND JSON_EXTRACT_SCALAR(payload, '$.action') in ('created', 'opened', 'reopened')))
  AND repo.id not in (
    SELECT INTEGER(JSON_EXTRACT(payload, '$.forkee.id'))
    FROM
      [githubarchive:month.201703],
      [githubarchive:month.201702],
      [githubarchive:month.201701],
    WHERE type = 'ForkEvent'
  )
  AND LOWER(actor.login) not like '%bot%'
  AND actor.login != 'tgstation-server'
  AND actor.login NOT IN (
    SELECT
      actor.login
    FROM (
      SELECT
        actor.login,
        COUNT(*) c
      FROM
      [githubarchive:month.201603]
      WHERE
        type = 'IssueCommentEvent'
      GROUP BY
        1
      HAVING
        c > 200
      ORDER BY
      2 DESC
    )
  )
GROUP BY 
  org, repo
HAVING 
  authors > 10
  and comments > 20
  and prs > 10
  and commits > 20
  and issues > 10
ORDER BY 
  activity desc
limit 1000;