# Copyright 2024 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# https://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
Analyzing package downloads from PyPI with BigQuery DataFrames#
In this notebook, you’ll use the PyPI public dataset and the deps.dev public dataset to visualize Python package downloads for a package and its dependencies.
# Choose a package which you want to visualize.
package_name = "pandas"
import bigframes.pandas as bpd
# Use `ordering_mode="partial"` for more efficient query generation, but
# some pandas-compatible methods may not be possible without a total ordering.
bpd.options.bigquery.ordering_mode = "partial"
Counting downloads and tracking dependencies#
The PyPI file_downloads table contains a row for each time there is a download request for a package. The deps.dev Dependencies table contains a row for each dependency of each package.
When ordering_mode = "partial", read_gbq_table creates a DataFrame representing the table, but the DataFrame has no native ordering or index.
import bigframes.enums
# Without ordering_mode = "partial" it is recommended that you set
# the "filters" parameter to limit the number of rows subsequent queries
# have to read.
pypi = bpd.read_gbq_table(
"bigquery-public-data.pypi.file_downloads",
# Using ordering_mode = "partial" changes the default index to a "NULL"
# index, meaning no index is available for implicit joins.
#
# Setting this explicitly avoids a DefaultIndexWarning.
index_col=bigframes.enums.DefaultIndexKind.NULL,
)
deps = bpd.read_gbq_table(
"bigquery-public-data.deps_dev_v1.Dependencies",
index_col=bigframes.enums.DefaultIndexKind.NULL,
)
Limit to the most recent 7 days of data#
The PyPI and deps.dev tables are partitioned by date. Query only the most recent 7 days of data to reduce the number of bytes scanned.
Just as with the default ordering mode, filters can be describe in a pandas-compatible way by passing a Boolean Series to the DataFrame’s __getitem__ accessor.
import datetime
now = datetime.datetime.now(datetime.timezone.utc)
last_7_days = now - datetime.timedelta(days=7)
last_30_days = now - datetime.timedelta(days=30)
pypi = pypi[pypi["timestamp"] > last_7_days]
deps = deps[deps["SnapshotAt"] > last_30_days] # deps are refreshed less frequently
deps = deps[deps["System"] == "PYPI"]
⚠ Warning
Without ordering_mode = "partial", these filters do not change the number of bytes scanned. Instead, add column and row filters at “read” time. For example,
import datetime
last_7_days = datetime.datetime.now(datetime.timezone.utc) - datetime.timedelta(days=7)
# Without ordering_mode = "partial", one must limit the data at "read" time to reduce bytes scanned.
pypi = bpd.read_gbq_table(
"bigquery-public-data.pypi.file_downloads",
columns=["timestamp", "project"],
filters=[("timestamp", ">", last_7_days)],
)
head() is not available when no ordering has been established. It fails with OrderRequiredError. Use peek() instead to download a sample of the data. This will be much more efficient, as the query doesn’t need to order all rows to determine which are first.
# Warning: Ensure bpd.options.bigquery.ordering_mode = "partial" or else
# this query() will cause a full table scan because of the sequential index.
assert bpd.options.bigquery.ordering_mode == "partial"
pypi.peek()
| timestamp | country_code | url | project | file | details | tls_protocol | tls_cipher | |
|---|---|---|---|---|---|---|---|---|
| 0 | 2024-09-18 18:15:04+00:00 | US | /packages/ff/c8/4cd4b2834012ffc71ae3fd69187f08... | aiobreaker | {'filename': 'aiobreaker-1.2.0-py3-none-any.wh... | {'installer': {'name': 'pip', 'version': '21.1... | TLSv1.3 | TLS_AES_128_GCM_SHA256 |
| 1 | 2024-09-18 18:29:50+00:00 | US | /packages/21/8e/4562029e179226051cd4aa3135444d... | aiobotocore | {'filename': 'aiobotocore-1.3.0.tar.gz', 'proj... | {'installer': {'name': 'pip', 'version': '24.1... | TLSv1.2 | ECDHE-RSA-AES128-GCM-SHA256 |
| 2 | 2024-09-18 18:22:14+00:00 | US | /packages/11/16/4226e59bb72e096d9809ccedf349a1... | aiobotocore | {'filename': 'aiobotocore-2.0.1.tar.gz', 'proj... | {'installer': {'name': 'pip', 'version': '24.2... | TLSv1.2 | ECDHE-RSA-AES128-GCM-SHA256 |
| 3 | 2024-09-18 18:22:08+00:00 | US | /packages/11/16/4226e59bb72e096d9809ccedf349a1... | aiobotocore | {'filename': 'aiobotocore-2.0.1.tar.gz', 'proj... | {'installer': {'name': 'pip', 'version': '24.2... | TLSv1.2 | ECDHE-RSA-AES128-GCM-SHA256 |
| 4 | 2024-09-18 18:29:22+00:00 | US | /packages/54/b7/453119271cc4c36b07fdeab9b0ff25... | aiobotocore | {'filename': 'aiobotocore-2.3.3.tar.gz', 'proj... | {'installer': {'name': 'pip', 'version': '24.1... | TLSv1.2 | ECDHE-RSA-AES128-GCM-SHA256 |
deps.peek()
| SnapshotAt | System | Name | Version | Dependency | MinimumDepth | |
|---|---|---|---|---|---|---|
| 0 | 2024-08-29 04:39:16.121656+00:00 | PYPI | zxkane-cdk-construct-simple-nat | 0.2.89 | {'System': 'PYPI', 'Name': 'attrs', 'Version':... | 2 |
| 1 | 2024-08-29 04:39:16.121656+00:00 | PYPI | zxkane-cdk-construct-simple-nat | 0.2.82 | {'System': 'PYPI', 'Name': 'attrs', 'Version':... | 2 |
| 2 | 2024-08-29 04:39:16.121656+00:00 | PYPI | zxkane-cdk-construct-simple-nat | 0.2.88 | {'System': 'PYPI', 'Name': 'attrs', 'Version':... | 2 |
| 3 | 2024-08-29 04:39:16.121656+00:00 | PYPI | zxkane-cdk-construct-simple-nat | 0.2.91 | {'System': 'PYPI', 'Name': 'attrs', 'Version':... | 2 |
| 4 | 2024-08-29 04:39:16.121656+00:00 | PYPI | zxkane-cdk-construct-simple-nat | 0.2.77 | {'System': 'PYPI', 'Name': 'attrs', 'Version':... | 2 |
Find dependencies for pandas#
Use assign to add columns to the DataFrame after a scalar operations, such as extracting a sub-field from a STRUCT column.
Because the DataFrame has no index, this does not work if the new column belongs to a different table expression.
deps = deps.assign(DependencyName=deps["Dependency"].struct.field("Name"))
Use an aggregation to identify the unique DependencyNames for the pandas package. Note: drop_duplicates() is not supported, as the order-based behavior such as keep="first" is not applicable when using ordering_mode = "partial".
A DataFrame with no index still supports aggregation operations. Set as_index=False to keep the GROUP BY keys as regular columns, instead of turning them into an index.
package_deps = deps[deps["Name"] == package_name].groupby(["Name", "DependencyName"], as_index=False).size()
package_deps.peek()
Count downloads for pandas and its dependencies#
The previous step created pandas_deps with all the dependencies of pandas but not pandas itself.
Combine two DataFrames with the same column names with the bigframes.pandas.concat function.