<jupyter_start><jupyter_text>What are the repositories, avg project size, and avg  of stars?<jupyter_code>query1 = """
SELECT
  host_type,
  COUNT(*) repositories,
  ROUND(AVG(size),2) avg_size,
  ROUND(AVG(stars_count),2) avg_stars
FROM
  `bigquery-public-data.libraries_io.repositories`
GROUP BY
  host_type
ORDER BY
  repositories DESC
LIMIT
  1000;
        """
response1 = library.query_to_pandas_safe(query1)
response1.head(10)<jupyter_output><empty_output><jupyter_text>What are the top dependencies per platform?<jupyter_code>query2 = """
SELECT
  dependency_platform,
  COUNT(*) dependencies,
  APPROX_TOP_COUNT(dependency_name, 3) top_dependencies
FROM
  `bigquery-public-data.libraries_io.dependencies`
GROUP BY
  dependency_platform
ORDER BY
  dependencies DESC;
        """
response2 = library.query_to_pandas_safe(query2, max_gb_scanned=10)
response2.head(20)<jupyter_output><empty_output><jupyter_text>What are the top unmaintained or deprecated projects?<jupyter_code>query3 = """
SELECT
  name,
  repository_sourcerank,
  LANGUAGE,
  status
FROM
  `bigquery-public-data.libraries_io.projects_with_repository_fields`
WHERE
  status IN ('Deprecated',
    'Unmaintained')
ORDER BY
  repository_sourcerank DESC
LIMIT
  20;
        """
response3 = library.query_to_pandas_safe(query3, max_gb_scanned=10)
response3.head(20)<jupyter_output><empty_output><jupyter_text>Credit: Many functions are adapted from https://console.cloud.google.com/marketplace/details/libraries-io/librariesio<jupyter_code># Getting all dependencies of Openmct
query4 = """
SELECT *
 FROM 
  `bigquery-public-data.libraries_io.repository_dependencies` 
 WHERE 
 repository_name_with_owner="nasa/openmct" 
 LIMIT 
  50;
        """
openmct = library.query_to_pandas_safe(query4, max_gb_scanned=30)
openmct
query5 = """
SELECT *
 
 FROM 
  `bigquery-public-data.libraries_io.repository_dependencies` 
 
 WHERE 
 repository_name_with_owner ="nasa/mct" 
 
 LIMIT 
  1000
        """
mct = library.query_to_pandas_safe(query5, max_gb_scanned=30)
mct
query6 = """
SELECT *
 
 FROM 
  `bigquery-public-data.libraries_io.repository_dependencies` 
 
 WHERE 
 repository_name_with_owner ="nasa/earthdata-search"
 
 LIMIT 
  1000
        """
earthdata_search = library.query_to_pandas_safe(query6, max_gb_scanned=30)
earthdata_search
query8 = """
SELECT *
 
 FROM 
  `bigquery-public-data.libraries_io.repository_dependencies` 
 
 WHERE 
 repository_name_with_owner ="nasa/code-nasa-gov"


 LIMIT 
  1000
        """
code_nasa_gov = library.query_to_pandas_safe(query8, max_gb_scanned=30)
code_nasa_gov
query9 = """
SELECT *
 
 FROM 
  `bigquery-public-data.libraries_io.repository_dependencies` 
 
 WHERE 
 repository_name_with_owner ="nasa/apod-api"

 LIMIT 
  1000
        """
apod_api = library.query_to_pandas_safe(query9, max_gb_scanned=30)
apod_api
query10 = """
SELECT *
 
 FROM 
  `bigquery-public-data.libraries_io.repository_dependencies` 
 
 WHERE 
 repository_name_with_owner ="nasa/CrisisMappingToolkit"
 
 LIMIT 
  1000
        """
CrisisMappingToolkit = library.query_to_pandas_safe(query10, max_gb_scanned=30)
CrisisMappingToolkit
query11 = """
SELECT *
 
 FROM 
  `bigquery-public-data.libraries_io.repository_dependencies` 
 
 WHERE 
 repository_name_with_owner ="nasa/RHEAS"
 LIMIT 
  1000
        """
rheas = library.query_to_pandas_safe(query11, max_gb_scanned=30)
rheas 
query12 = """
SELECT *
 
 FROM 
  `bigquery-public-data.libraries_io.repository_dependencies` 
 
 WHERE 
 repository_name_with_owner ="nasa/astrobot"
 LIMIT 
  1000
        """
astrobot = library.query_to_pandas_safe(query12, max_gb_scanned=30)
astrobot 
query13 = """
SELECT *
 
 FROM 
  `bigquery-public-data.libraries_io.repository_dependencies` 
 
 WHERE 
 repository_name_with_owner ="nasa/dictionaries"
 
 LIMIT 
  1000
        """
nasa_dictionaries = library.query_to_pandas_safe(query13, max_gb_scanned=30)
nasa_dictionaries 
query14 = """
SELECT *
 
 FROM 
  `bigquery-public-data.libraries_io.repository_dependencies` 
 
 WHERE 
 repository_name_with_owner ="nasa/PyBlock"
 
 LIMIT 
  1000
        """
py_block = library.query_to_pandas_safe(query14, max_gb_scanned=30)
py_block 
import requests

r = requests.get('https://libraries.io/api/github/nasa/repositories?api_key=91f3f771438b366da564093e002a2c61')
project_names = r.json()
names = list(map(lambda x: x['full_name'], project_names))
names

query15 = """
SELECT *
 
 FROM 
  `bigquery-public-data.libraries_io.repository_dependencies` 
 
 WHERE 
 repository_name_with_owner IN ({names})
 
 LIMIT 
  1000
        """.format(names=', '.join('"' + name + '"' for name in names))
py_block = library.query_to_pandas_safe(query15, max_gb_scanned=30)
py_block<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09983043662568886, 'fraction_numerical': 0.03242899533700721, 'mean_word_length': 4.920953575909661, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '247973', 'license': 'BSD-Source-Code', 'n_tokens_mistral': 1872, 'n_tokens_neox': 1680, 'n_words': 261, 'path': 'secondKernel.ipynb', 'repo_name': 'BriantOliveira/Topic-Modeling-NASA-Github', 'size': '4718'}