<jupyter_start><jupyter_text>Get StartedAfter forking this notebook, run the code in the following cell:<jupyter_code># import package with helper functions 
import bq_helper

# create a helper object for this dataset
github = bq_helper.BigQueryHelper(active_project="bigquery-public-data",
                                              dataset_name="github_repos")<jupyter_output><empty_output><jupyter_text>Then write the code to answer the question below<jupyter_code>github.list_tables()
github.head("sample_files")<jupyter_output><empty_output><jupyter_text>Question 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.* You'll want to JOIN the sample_files and sample_commits questions to answer this.* **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.<jupyter_code># Your code here
query = """
        -- Select all the columns we want in our joined table
        SELECT sf.repo_name, COUNT(sc.commit) AS number_of_commits
        FROM `bigquery-public-data.github_repos.sample_files` as sf
        -- Table to merge into sample_files
        INNER JOIN `bigquery-public-data.github_repos.sample_commits` as sc 
            ON sf.repo_name = sc.repo_name -- what columns should we join on?
        WHERE sf.path LIKE '%.py'
        GROUP BY sf.repo_name
        ORDER BY number_of_commits DESC
        """

commits_per_repo_python = github.query_to_pandas_safe(query, max_gb_scanned=6)
print(commits_per_repo_python)<jupyter_output>repo_name  number_of_commits
0         torvalds/linux           23501556
1  tensorflow/tensorflow            4128858
2            apple/swift            4044664
3         facebook/react              13750
4       Microsoft/vscode               6909
{'fraction_non_alphanumeric': 0.06596051998074146, 'fraction_numerical': 0.0182956186807896, 'mean_word_length': 3.3022774327122155, 'pattern_counts': {'":': 0, '<': 12, '<?xml version=': 0, '>': 12, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '481886', 'license': 'MIT', 'n_tokens_mistral': 623, 'n_tokens_neox': 576, 'n_words': 206, 'path': 'github_repos_commits_python.ipynb', 'repo_name': 'douglaswchung/sql-practice', 'size': '2077'}