如何使用 Python 在 Google 表格中分析数据：分步指南

3. 建立连接

进入工作簿后，点击 View > Databases，再点击+图标。您将看到 DataLab 支持连接的所有数据库技术概览：

选择 Google Sheets。随后系统会请求您授予 DataCamp 访问您 Google Drive 文件的权限。

点击“Grant permission to Google Drive”。最后会出现文件选择器：

选择您希望读取数据的电子表格文件。可以是我们提供的示例 Google 表格的副本，也可以是您自己的 Google 表格。

4. 查询 Google 表格文件

现在您可以查询刚刚连接的 Google 表格文件了！这将通过 SQL 单元完成，这是 DataLab 中用 SQL 查询数据库的基本构件。如果您基于提供的模板创建工作簿，笔记本中已包含此 SQL 单元；若您从零开始创建工作簿，可打开启动器并点击“SQL”添加：

在 SQL 单元中，点击左上角的 Source 下拉菜单。

选择您要分析的电子表格（示例中为“Unicorn Companies”）。接着，编写以下 SQL 查询：

SELECT * FROM unicorn_companies技术支持

点击 Run 后，该查询会提取 Unicorn Companies Google 表格中 unicorn_companies 工作表的所有行，并将结果存入新的 Pandas DataFrame df：

pandas 的 DataFrame 是一种表格状的数据结构，您可以在 Python 中用它来存储和处理数据。我们添加一个 Python 单元，执行以下代码，按国家/地区计算该数据集中独角兽公司的募资中位数：

median_funding_df = df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df
技术支持

在上面的 SQL 单元中，我们使用了直接抓取全部数据的简单查询；您也可以使用更复杂的 SQL 语句，例如获取美国最具价值的前 10 家独角兽公司：

SELECT *
FROM unicorn_companies
WHERE country = 'United States'
ORDER BY valuation DESC
LIMIT 10
技术支持

就是这么简单！若想获得更多优化 SQL 查询的技巧，例如聚焦于某个工作表的部分区域，请参阅相应的 DataLab 文档。

如前所述，此与 Google 表格的无缝连接仅可用于从 Google 表格读取数据。如果您希望既读取数据又将结果写回 Google 表格，请继续阅读下一部分，了解如何借助 Google Sheets API 实现最大化的灵活性。

使用 Google Sheets API

如果您既想从 Google 表格读取数据，又希望将 Python 计算结果写回同一或另一份 Google 表格，请选择此选项。

正如引言中提到的，更高的灵活性意味着更复杂的初始配置。具体步骤包括：

• 配置 Google 服务账号
• 创建包含数据的 Google 电子表格
• 创建新工作簿
• 在 DataLab 中保存您的服务账号凭据
• 读取 Google 表格数据
• 在 Python 中分析数据
• 将结果写入 Google 表格

我们将分别介绍上述每一步。

1. 配置 Google 服务账号

若要以编程方式访问 Google 表格数据，您需要创建一个 Google 服务账号。这是一种特殊账号，供程序访问 Google 资源（如电子表格）。随后，您将使用该服务账号把 DataLab 与 Google 表格连接起来。

每个您希望访问其电子表格的 Google 账号只需设置一次服务账号；下次在同一账号中处理电子表格数据时可跳过此步。

请按照以下步骤创建服务账号并生成所需凭据。

• 确保您已登录 Google 账号。
• 前往 Google API Library
• 点击导航栏中的下拉菜单创建新项目。
• 搜索“Google Sheets API”并启用。此过程可能需要最多 10 秒。
• 在左侧的“APIs and services”导航栏中，进入“Credentials”标签页
• 点击“+ CREATE CREDENTIALS”，选择“Service Account”
• 第一步（服务账号详情）中，为服务账号命名，例如“gsheet-operator”，然后点击“Create and continue”
• 第二步中，选择“Owner”角色并点击“Continue”
• 第三步中，无需更改任何内容，点击“Done”
• 回到 Credentials 页面后，点击刚创建的服务账号。
• 进入 Keys 标签页，点击“Add Key > Create new key”
• 选择“JSON”，然后点击“Create”。包含您服务账号凭据的 JSON 文件会自动下载到您的电脑。

现在您已拥有服务账号和一份 JSON 凭据文件！前往下载文件夹（或文件保存位置），打开并查看，内容应类似如下：

{
  "type": "service_account",
  "project_id": "<your-project-name>",
  "private_key_id": "<something-private>",
  "private_key": "-----BEGIN PRIVATE KEY-----\nM<some-very-private-stuff\n",
  "client_email": "[email protected]",
  "client_id": "123456789012345678901",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/gsheets-operator%40<project-name>.iam.gserviceaccount.com"
}
技术支持

其中包含一个“client_email”字段：gsheets-operator@<google-project-name>.iam.gserviceaccount.com。将该邮箱地址复制到剪贴板；下一步将用到。

2. 创建包含数据的 Google 电子表格

在分析电子表格数据之前，先确保您有一份包含数据的电子表格。如果没有现成的数据集，可使用我们为本教程准备的 Google 表格：打开示例电子表格，进入 Google 表格后，点击“File > Make a copy”，指定名称并点击“Make a copy”。如果您已有想要分析的数据表，直接打开该表即可。

无论您使用的是示例表的副本还是自有电子表格，都需要为第一步创建的 Google 服务账号授予访问权限：

• 点击“Share”
• 将上一步复制到剪贴板的服务账号邮箱以编辑者身份添加到电子表格（例如 gsheets-operator@<google-project-name>.iam.gserviceaccount.com）
• 点击“Send”

好的，服务账号：完成。具备正确访问权限的 Google 电子表格：完成。现在可以开始编写 Python 代码了！

3. 创建新工作簿

接下来在 DataLab 中创建一个新的数据项目（亦称工作簿）。您可以从零创建一个空白工作簿，适合希望自行编写全部代码的情况；或创建一个已包含全部代码的工作簿。

4. 在 DataLab 中保存服务账号凭据

要在新建的工作簿中使用服务账号凭据 JSON，您需要将其保存到 DataLab。

您可以把 JSON 的内容复制粘贴到新建工作簿的代码单元中：

此做法不安全且不推荐。如果您将笔记本分享给他人，他们能以明文看到凭据，从而冒用您的身份访问该服务账号所能访问的资源。

这种方式还要求您在每个需要访问电子表格数据的工作簿中重复粘贴代码。若需轮换凭据，您还得逐一修改每个工作簿。

更安全且可扩展的方式是将 JSON 存入所谓的环境变量。连接该环境变量后，它将可用于您的 Python 会话。在工作簿中：

• 点击左侧的“Environment”标签
• 在“Environment Variables”旁点击加号图标
• 在“Add Environment Variables”弹窗中：
  • 将“Name”设为 GOOGLE_JSON
  • 把下载的服务账号 JSON 文件的完整内容粘贴到“Value”。方法是打开 JSON 文件，全选并复制到剪贴板，然后粘贴到 Value 字段。
  • 将“Environment Variables Set Name”设为“Google Service Account”（实际上可自定义）

填写完毕后，点击“Create”、“Next”，最后点击“Connect”。您的 DataLab 会话会重启，GOOGLE_JSON 现已作为环境变量在工作簿中可用。您可通过创建一个 Python 单元并运行以下代码进行验证：

import os
google_json = os.environ["GOOGLE_JSON"]
print(google_json)技术支持

若您希望在其他工作簿中复用相同的服务账号凭据，无需再次设置环境变量：可直接在其他工作簿中复用该环境变量。

5. 读取 Google 表格数据

精彩部分来了：编写 Python 代码连接到 Google 表格数据。

安装并导入所需软件包

首先安装并导入连接到 Google API 账号所需的软件包。我们将使用 google-auth 进行 Google API 身份验证，使用 gspread 便捷地与 Google 电子表格交互，并使用 pandas 在 Python 中处理表格数据。

%%capture
!pip install gspread
from google.oauth2 import service_account
import pandas as pd
import gspread
import json
import os技术支持

使用限定范围的凭据创建客户端

加载 GOOGLE_JSON 环境变量并存入变量：

import os
google_json = os.environ["GOOGLE_JSON"]技术支持

基于服务账号凭据的 json 创建凭据对象：

service_account_info = json.loads(google_json)
credentials = service_account.Credentials.from_service_account_info(service_account_info)
技术支持

为凭据分配正确的“API 范围”（scope），以便具备与 Google 电子表格交互的适当权限：

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds_with_scope = credentials.with_scopes(scope)技术支持

使用这些带有范围的凭据创建新客户端：

client = gspread.authorize(creds_with_scope)技术支持

将电子表格数据读取为 DataFrame

客户端就绪后，我们即可读取先前创建的电子表格中的数据。

首先，通过电子表格 URL 创建一个新的 sheet 实例：

spreadsheet = client.open_by_url('https://docs.google.com/spreadsheets/d/spreadsheet_id/edit#gid=0')
技术支持

接着，定位到您想获取数据的工作表（即 Google 电子表格中的标签页）。我们用索引 0 获取第一张表：

worksheet = spreadsheet.get_worksheet(0)
技术支持

先将所有记录获取为 JSON：

records_data = worksheet.get_all_records()
技术支持

然后将该 JSON 记录转换成 pandas DataFrame：

records_df = pd.DataFrame.from_dict(records_data)
records_df
技术支持

成功！

6. 在 Python 中分析电子表格数据

现在，您可以使用 Python 分析从 Google 电子表格读取的数据。下面的最小示例代码假定您使用的是示例电子表格。若您使用其他数据集，以下步骤可能不适用。若想深入学习，请查看 DataCamp 的 pandas 数据处理课程！

让我们按国家/地区计算募资中位数：

median_funding_df = records_df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df
技术支持

7. 将结果写入 Google 表格

如果您希望与您协作的电子表格用户能在表格中直接看到您的分析成果，可以将 Python 分析结果写回原电子表格中的一个新的工作表（即标签页）。

在本示例中，我们会在尚不存在时创建名为 median_country 的新工作表，然后写入 DataFrame median_funding_df 的内容（包括列名表头）。这段代码是在 AI Assistant 的帮助下生成的！

# 将 DataFrame median_funding_df 转为列表的列表
medians_list = median_funding_df.values.tolist()

# 将表头行添加到列表开头
medians_list.insert(0, median_funding_df.columns.tolist())

# 检查是否已存在名为 'median_by_country' 的工作表
sheet_titles = [sheet.title for sheet in spreadsheet.worksheets()]
sheet_name = "median_by_country"
if sheet_name not in sheet_titles:
    # 在 Google 表格中创建名为 'median_by_country' 的新工作表
    medians_sheet = spreadsheet.add_worksheet(title=sheet_name, rows=len(medians_list), cols=len(medians_list[0]))
else:
    medians_sheet = spreadsheet.worksheet(sheet_name)

# 将 medians_list 写入新工作表
medians_sheet.clear()
medians_sheet.insert_rows(medians_list, row=1)技术支持

返回电子表格查看，您会看到一个包含 DataFrame 数据的新工作表！这种将结果“写回”的能力仅在使用基于 Google Sheets API 的连接方式时可用；若使用本教程前面介绍的 DataCamp 内置 Google 表格连接器，则不支持写回。

结语

根据您的需求，使用 Python 在 Google 表格中开展数据分析可以非常顺畅，也可能需要一定的前期配置。如果您只需从表格读取数据，使用内置连接器即可，几次点击就能上手。若您既要读取数据也要写回数据，启用 Google Sheets API 并设置服务账号会更合适。

两种情况下，一旦配置就绪，从 Google 表格访问数据几乎像查询 CSV 文件一样容易。更大的优势在于，您可以在利益相关方最熟悉的环境中开展协作：很可能您的一些同事对电子表格情有独钟。

告别繁琐的 CSV 导出，迎接强大而自动化的工作流，让您无需离开 DataLab，即可高效处理 Google 表格数据。