github_repos_commits_python.ipynb

{
  "cells": [
    {
      "metadata": {
        "_uuid": "1a4e4095d0aa8db597c7aab221dca56752f7a39b",
        "_cell_guid": "8fe83f5f-8aef-4d17-a484-bf2ab993dea5"
      },
      "cell_type": "markdown",
      "source": "# Get Started\n\nAfter forking this notebook, run the code in the following cell:"
    },
    {
      "metadata": {
        "_uuid": "f6ef073f89558eeb5d421de99c8f11b77ff029c0",
        "_cell_guid": "e020770e-e51c-4ea3-b08b-5e3b0c14823b",
        "collapsed": true,
        "trusted": true
      },
      "cell_type": "code",
      "source": "# import package with helper functions \nimport bq_helper\n\n# create a helper object for this dataset\ngithub = bq_helper.BigQueryHelper(active_project=\"bigquery-public-data\",\n                                              dataset_name=\"github_repos\")",
      "execution_count": 1,
      "outputs": []
    },
    {
      "metadata": {
        "_uuid": "3c099edebc77b1060333faab526e73dc61f81cf5",
        "_cell_guid": "f3c41ff4-22b9-43dd-866b-dcd833fa3251"
      },
      "cell_type": "markdown",
      "source": "Then write the code to answer the question below"
    },
    {
      "metadata": {
        "scrolled": true,
        "_uuid": "01d0e2e582062c446a37bd392dd206ac8216ff94",
        "trusted": true
      },
      "cell_type": "code",
      "source": "github.list_tables()\ngithub.head(\"sample_files\")",
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 2,
          "data": {
            "text/plain": "  ...\n0 ...\n1 ...\n2 ...\n3 ...\n4 ...\n\n[5 rows x 6 columns]",
            "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>repo_name</th>\n      <th>ref</th>\n      <th>path</th>\n      <th>mode</th>\n      <th>id</th>\n      <th>symlink_target</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>git/git</td>\n      <td>refs/heads/master</td>\n      <td>RelNotes</td>\n      <td>40960</td>\n      <td>62615ffa4e97803da96aefbc798ab50f949a8db7</td>\n      <td>Documentation/RelNotes/2.10.0.txt</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>np/ling</td>\n      <td>refs/heads/master</td>\n      <td>tests/success/plug_compose.t/plug_compose.ll</td>\n      <td>40960</td>\n      <td>0c1605e4b447158085656487dc477f7670c4bac1</td>\n      <td>../../../fixtures/all/plug_compose.ll</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>np/ling</td>\n      <td>refs/heads/master</td>\n      <td>fixtures/strict-par-success/parallel_assoc_lef...</td>\n      <td>40960</td>\n      <td>b59bff84ec03d12fabd3b51a27ed7e39a180097e</td>\n      <td>../all/parallel_assoc_left.ll</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>np/ling</td>\n      <td>refs/heads/master</td>\n      <td>fixtures/sequence/parallel_assoc_2tensor2_left.ll</td>\n      <td>40960</td>\n      <td>f29523e3fb65702d99478e429eac6f801f32152b</td>\n      <td>../all/parallel_assoc_2tensor2_left.ll</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>np/ling</td>\n      <td>refs/heads/master</td>\n      <td>fixtures/success/my_dual.ll</td>\n      <td>40960</td>\n      <td>38a3af095088f90dfc956cb990e893909c3ab286</td>\n      <td>../all/my_dual.ll</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {
        "_uuid": "8f2839f25d086af736a60e9eeb907d3b93b6e0e5",
        "_cell_guid": "b1076dfc-b9ad-4769-8c92-a6c4dae69d19"
      },
      "cell_type": "markdown",
      "source": "# Question\n\n#### 1)  How many commits (recorded in the \"sample_commits\" table) have been made in repos written in the Python programming language? (I'm looking for the number of commits per repo for all the repos written in Python.\n* You'll want to JOIN the sample_files and sample_commits questions to answer this.\n* **Hint:** You can figure out which files are written in Python by filtering results from the \"sample_files\" table using `WHERE path LIKE '%.py'`. This will return results where the \"path\" column ends in the text \".py\", which is one way to identify which files have Python code."
    },
    {
      "metadata": {
        "_uuid": "d629ff2d2480ee46fbb7e2d37f6b5fab8052498a",
        "_cell_guid": "79c7e3d0-c299-4dcb-8224-4455121ee9b0",
        "trusted": true
      },
      "cell_type": "code",
      "source": "# Your code here\nquery = \"\"\"\n        -- Select all the columns we want in our joined table\n        SELECT sf.repo_name, COUNT(sc.commit) AS number_of_commits\n        FROM `bigquery-public-data.github_repos.sample_files` as sf\n        -- Table to merge into sample_files\n        INNER JOIN `bigquery-public-data.github_repos.sample_commits` as sc \n            ON sf.repo_name = sc.repo_name -- what columns should we join on?\n        WHERE sf.path LIKE '%.py'\n        GROUP BY sf.repo_name\n        ORDER BY number_of_commits DESC\n        \"\"\"\n\ncommits_per_repo_python = github.query_to_pandas_safe(query, max_gb_scanned=6)\nprint(commits_per_repo_python)",
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "stream",
          "text": "               repo_name  number_of_commits\n0         torvalds/linux           23501556\n1  tensorflow/tensorflow            4128858\n2            apple/swift            4044664\n3         facebook/react              13750\n4       Microsoft/vscode               6909\n",
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "_uuid": "71bf031898b72c4fe1b1e8f66581b3fa135de12d",
        "_cell_guid": "7a5d8c3b-86d6-4c5c-bdd1-7df1bc96a0bd"
      },
      "cell_type": "markdown",
      "source": "# Feedback\nBring any questions or feedback to the [Learn Discussion Forum](kaggle.com/learn-forum).\n\n# Congratulations\nBy now, you know all the key components to use BigQuery and SQL effectively.\n\nWant to go play with your new powers?  Kaggle has BigQuery datasets available [here](https://www.kaggle.com/datasets?filetype=bigQuery)."
    }
  ],
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.6.6",
      "mimetype": "text/x-python",
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "pygments_lexer": "ipython3",
      "nbconvert_exporter": "python",
      "file_extension": ".py"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 1
}