Cách phân tích dữ liệu trong Google Sheets bằng Python: Hướng dẫn từng bước

3. Thiết lập kết nối

Khi đang ở trong một workbook, nhấp View > Databases và nhấp biểu tượng +. Bạn sẽ thấy tổng quan về tất cả công nghệ cơ sở dữ liệu mà DataLab cho phép kết nối:

Chọn Google Sheets. Bây giờ bạn sẽ được yêu cầu cấp cho DataCamp quyền truy cập các tệp trong Google Drive của bạn.

Nhấp “Grant permission to Google Drive.” Cuối cùng, một trình chọn tệp sẽ xuất hiện:

Chọn tệp Spreadsheet mà bạn muốn đọc dữ liệu. Đây có thể là bản sao Google Sheet mẫu mà chúng tôi đã cung cấp hoặc Google Sheet của riêng bạn.

4. Truy vấn tệp Google Sheets

Giờ bạn đã có thể truy vấn tệp Google Sheets vừa kết nối! Bạn sẽ làm điều này với một ô SQL, một khối xây dựng trong DataLab để truy vấn cơ sở dữ liệu bằng SQL. Nếu bạn đã tạo workbook từ mẫu cung cấp, bạn sẽ có sẵn ô SQL này trong notebook. Nếu bạn bắt đầu từ đầu, bạn có thể thêm nó bằng cách mở launcher và nhấp “SQL”:

Trong chính ô SQL, hãy nhấp vào danh sách thả xuống Source ở góc trên bên trái.

Chọn bảng tính bạn muốn phân tích (trong ví dụ là "Unicorn Companies"). Tiếp theo, viết truy vấn SQL sau:

SELECT * FROM unicorn_companiesĐược hỗ trợ bởi

Nếu bạn nhấp Run, truy vấn này sẽ lấy tất cả các hàng trong sheet unicorn_companies bên trong Google Sheet Unicorn Companies, và lưu kết quả vào một Pandas DataFrame mới df:

Pandas DataFrame là một cấu trúc dữ liệu dạng bảng mà bạn có thể dùng trong Python để lưu trữ và thao tác dữ liệu. Hãy thêm một ô Python và chạy đoạn mã sau, tính trung vị vốn huy động cho các công ty kỳ lân trong bộ dữ liệu theo từng quốc gia:

median_funding_df = df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df
Được hỗ trợ bởi

Trong ô SQL ở trên, chúng ta dùng một truy vấn SQL đơn giản để lấy toàn bộ dữ liệu, nhưng bạn cũng có thể dùng một câu lệnh SQL tinh vi hơn để, chẳng hạn, lấy 10 công ty kỳ lân giá trị nhất tại Hoa Kỳ:

SELECT *
FROM unicorn_companies
WHERE country = 'United States'
ORDER BY valuation DESC
LIMIT 10
Được hỗ trợ bởi

Chỉ có vậy thôi! Để biết thêm mẹo nâng cao về cách tinh chỉnh truy vấn SQL, ví dụ để thu hẹp vào một phần của sheet, hãy xem bài viết tài liệu DataLab tương ứng.

Như đã đề cập, kết nối liền mạch với Google Sheets này chỉ có thể dùng để đọc dữ liệu từ Google Sheet. Nếu bạn muốn vừa đọc dữ liệu vừa ghi kết quả trở lại Google Sheets, xem phần tiếp theo giải thích cách tận dụng Google Sheets API để có thiết lập linh hoạt tối đa.

Sử dụng Google Sheets API

Chọn phương án này nếu bạn muốn vừa đọc dữ liệu từ Google Sheet vừa ghi kết quả tính toán trong Python trở lại cùng Google Sheet hoặc một Google Sheet khác.

Như đã nêu ở phần mở đầu, mức linh hoạt tăng lên sẽ đi kèm chi phí thiết lập phức tạp hơn. Cụ thể, các bước chúng ta sẽ thực hiện gồm:

• Định cấu hình Google Service Account
• Tạo một bảng tính Google chứa dữ liệu
• Tạo một workbook mới
• Lưu thông tin xác thực service account trong DataLab
• Đọc dữ liệu Google Sheets
• Phân tích dữ liệu bằng Python
• Ghi kết quả vào Google Sheets

Hãy lần lượt đi sâu vào từng bước.

1. Định cấu hình Google Service Account

Để truy cập dữ liệu trong Google Sheets bằng lập trình, bạn cần tạo một Google service account, là loại tài khoản đặc biệt được chương trình dùng để truy cập tài nguyên của Google như bảng tính. Sau đó, bạn sẽ dùng service account này để kết nối DataLab với Google Sheets.

Bạn chỉ cần thiết lập Google service account này một lần cho mỗi tài khoản Google mà bạn muốn truy cập bảng tính; bạn có thể bỏ qua bước này ở lần sau khi làm việc với dữ liệu bảng tính trong cùng tài khoản.

Làm theo các bước dưới đây để tạo service account và tạo thông tin xác thực cần thiết.

• Hãy đảm bảo bạn đã đăng nhập bằng tài khoản Google của mình.
• Đi tới Google API Library
• Tạo một dự án mới bằng cách nhấp vào danh sách thả xuống trên thanh điều hướng.
• Tìm “Google Sheets API” và bật nó. Quá trình này có thể mất đến 10 giây.
• Trong thanh điều hướng “APIs and services” bên trái, vào thẻ “Credentials”
• Nhấp “+ CREATE CREDENTIALS” và chọn “Service Account”
• Ở bước đầu (chi tiết service account), đặt tên cho service account, ví dụ “gsheet-operator” và nhấp “Create and continue”
• Ở bước thứ hai, chọn vai trò “Owner” và nhấp “Continue”
• Ở bước thứ ba, không thay đổi gì và nhấp “Done”
• Quay lại trang Credentials, nhấp vào service account bạn vừa tạo.
• Vào thẻ Keys, nhấp “Add Key > Create new key”
• Chọn “JSON,” rồi nhấp “Create.” Tệp JSON chứa thông tin xác thực service account sẽ tự động được tải xuống máy tính của bạn.

Giờ bạn đã có một service account và một tệp JSON thông tin xác thực! Mở thư mục Tải xuống hoặc nơi tệp JSON được tải về, mở nó và xem. Nó sẽ trông như thế này:

{
  "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"
}
Được hỗ trợ bởi

Có một trường “client_email” trong đó: gsheets-operator@<google-project-name>.iam.gserviceaccount.com. Hãy sao chép email này vào clipboard; bạn sẽ cần nó ở bước tiếp theo.

2. Tạo một bảng tính Google chứa dữ liệu

Trước khi có thể phân tích dữ liệu trong bảng tính, bạn cần đảm bảo mình có một bảng tính chứa dữ liệu. Nếu bạn chưa có sẵn tập dữ liệu, hãy bắt đầu từ Google Sheet mà chúng tôi đã chuẩn bị cho hướng dẫn này: Mở bảng tính mẫu và khi đã vào Google Sheet, nhấp “File > Make a copy,” đặt tên và nhấp “Make a copy.” Nếu bạn đã có bảng tính với dữ liệu muốn phân tích, chỉ cần mở bảng tính đó.

Dù bạn làm việc với bản sao của bảng tính mẫu hay bảng tính của riêng mình, bạn cần cấp quyền truy cập cho Google service account đã tạo ở bước đầu tiên vào bảng tính:

• Nhấp “Share”
• Thêm email của service account mà bạn đã sao chép vào clipboard ở bước trước làm editor cho bảng tính (ví dụ gsheets-operator@<google-project-name>.iam.gserviceaccount.com)
• Nhấp “Send”

Ổn rồi, service account: xong. Bảng tính Google với quyền truy cập đúng: xong. Đã đến lúc viết một chút mã Python!

3. Tạo một workbook mới

Tiếp theo là tạo một dự án dữ liệu mới trong DataLab, còn gọi là workbook. Bạn có thể tạo một workbook trống từ đầu, hữu ích nếu bạn muốn tự viết toàn bộ mã, hoặc tạo một workbook đã có sẵn toàn bộ mã.

4. Lưu thông tin xác thực Service Account trong DataLab

Để sử dụng tệp JSON thông tin xác thực service account trong workbook vừa tạo, bạn cần lưu nó trong DataLab.

Bạn có thể sao chép và dán nội dung JSON vào một ô mã trong workbook vừa tạo:

Đây không phải là cách an toàn và không được khuyến nghị. Nếu bạn chia sẻ notebook với người khác, họ có thể thấy thông tin xác thực dạng văn bản thuần. Khi đó họ có thể mạo danh bạn và truy cập các tài nguyên mà service account này có quyền.

Cách làm này cũng buộc bạn phải lặp lại đoạn mã trong mọi workbook mà bạn muốn dùng để truy cập dữ liệu bảng tính. Nếu muốn xoay vòng thông tin xác thực, bạn sẽ phải vào sửa từng workbook.

Cách an toàn và có khả năng mở rộng hơn là lưu JSON trong một biến môi trường. Khi bạn kết nối biến môi trường này, nó sẽ khả dụng trong phiên Python của bạn. Trong workbook của bạn:

• Nhấp thẻ “Environment” ở bên trái
• Nhấp biểu tượng dấu cộng cạnh “Environment Variables”
• Trong cửa sổ “Add Environment Variables”:
  • Đặt “Name” là GOOGLE_JSON
  • Đặt “Value” là toàn bộ nội dung tệp JSON service account đã tải xuống. Bạn có thể mở tệp JSON, chọn tất cả, sao chép vào clipboard rồi dán vào trường Value.
  • Đặt “Environment Variables Set Name” là “Google Service Account” (tên này có thể tùy ý)

Sau khi điền xong, nhấp “Create,” “Next,” và cuối cùng “Connect.” Phiên DataLab của bạn sẽ khởi động lại và GOOGLE_JSON sẽ khả dụng như một biến môi trường trong workbook. Bạn có thể kiểm tra bằng cách tạo một ô Python với đoạn mã sau và chạy nó:

import os
google_json = os.environ["GOOGLE_JSON"]
print(google_json)Được hỗ trợ bởi

Nếu bạn muốn tái sử dụng cùng thông tin xác thực service account trong một workbook khác, bạn không cần thiết lập lại biến môi trường: bạn có thể dùng lại biến môi trường đó trong các workbook khác.

5. Đọc dữ liệu Google Sheets

Giờ đến phần thú vị: viết mã Python để kết nối với dữ liệu Google Sheets.

Cài đặt và import các gói cần thiết

Bắt đầu bằng cách cài đặt và import tất cả gói cần thiết để thiết lập kết nối với tài khoản Google API của bạn. Chúng ta sẽ dùng google-auth để xác thực với Google API, gspread để giao tiếp dễ dàng với Google spreadsheets và pandas để thao tác dữ liệu dạng bảng trong Python.

%%capture
!pip install gspread
from google.oauth2 import service_account
import pandas as pd
import gspread
import json
import osĐược hỗ trợ bởi

Thiết lập client với thông tin xác thực có phạm vi

Nạp biến môi trường GOOGLE_JSON và lưu vào một biến:

import os
google_json = os.environ["GOOGLE_JSON"]Được hỗ trợ bởi

Tạo một đối tượng thông tin xác thực từ JSON service account:

service_account_info = json.loads(google_json)
credentials = service_account.Credentials.from_service_account_info(service_account_info)
Được hỗ trợ bởi

Gán ‘phạm vi API’ phù hợp cho thông tin xác thực để có quyền tương tác với bảng tính Google Sheet:

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds_with_scope = credentials.with_scopes(scope)Được hỗ trợ bởi

Tạo một client mới với thông tin xác thực đã gán phạm vi:

client = gspread.authorize(creds_with_scope)Được hỗ trợ bởi

Đọc dữ liệu bảng tính vào DataFrame

Khi client đã sẵn sàng, chúng ta có thể đọc dữ liệu từ bảng tính đã tạo trước đó.

Đầu tiên, tạo một thể hiện sheet mới từ URL bảng tính:

spreadsheet = client.open_by_url('https://docs.google.com/spreadsheets/d/spreadsheet_id/edit#gid=0')
Được hỗ trợ bởi

Tiếp theo, chọn worksheet (tab trong bảng tính Google) mà bạn muốn lấy dữ liệu. Lấy sheet đầu tiên bằng chỉ số 0:

worksheet = spreadsheet.get_worksheet(0)
Được hỗ trợ bởi

Trước hết, lấy tất cả bản ghi dưới dạng JSON:

records_data = worksheet.get_all_records()
Được hỗ trợ bởi

Sau đó, chuyển đổi các bản ghi JSON thành một pandas DataFrame:

records_df = pd.DataFrame.from_dict(records_data)
records_df
Được hỗ trợ bởi

Thành công!

6. Phân tích dữ liệu bảng tính trong Python

Giờ bạn có thể dùng Python để phân tích dữ liệu đã đọc từ bảng tính Google. Đoạn mã tối giản dưới đây giả định bạn đang làm việc với bảng tính mẫu. Nếu bạn làm với tập dữ liệu khác, các bước này có thể không áp dụng. Hãy xem khóa học Data Manipulation with pandas của DataCamp nếu bạn muốn tìm hiểu sâu hơn!

Hãy tính trung vị vốn huy động theo quốc gia:

median_funding_df = records_df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df
Được hỗ trợ bởi

7. Ghi kết quả vào Google Sheets

Nếu bạn muốn người dùng bảng tính mà bạn đang cộng tác có thể thấy kết quả công việc ngay trong bảng tính, bạn có thể ghi trả kết quả phân tích Python vào một worksheet (tức là tab) mới bên trong bảng tính gốc.

Trong ví dụ này, chúng ta tạo một worksheet mới median_country nếu chưa tồn tại, rồi chèn nội dung của DataFrame median_funding_df, bao gồm cả hàng tiêu đề với tên cột. Đoạn mã này được tạo với sự hỗ trợ của AI Assistant!

# Convert the median_funding_df DataFrame to a list of lists
medians_list = median_funding_df.values.tolist()

# Add the header row to the list
medians_list.insert(0, median_funding_df.columns.tolist())

# Check if the 'median_by_country' sheet already exists
sheet_titles = [sheet.title for sheet in spreadsheet.worksheets()]
sheet_name = "median_by_country"
if sheet_name not in sheet_titles:
    # Create a new worksheet named 'median_by_country' in the Google Sheet
    medians_sheet = spreadsheet.add_worksheet(title=sheet_name, rows=len(medians_list), cols=len(medians_list[0]))
else:
    medians_sheet = spreadsheet.worksheet(sheet_name)

# Write the medians_list to the new worksheet
medians_sheet.clear()
medians_sheet.insert_rows(medians_list, row=1)Được hỗ trợ bởi

Khi quay lại bảng tính, bạn sẽ thấy một worksheet mới với dữ liệu từ DataFrame! Việc ‘ghi trả’ kết quả chỉ khả thi nếu bạn dùng cách kết nối dựa trên Google Sheets API; không áp dụng với trình kết nối Google Sheets tích hợp của DataCamp được giới thiệu trước đó.

Kết luận

Tùy nhu cầu, việc phân tích dữ liệu trong Google Sheets bằng Python có thể rất liền mạch hoặc cần khá nhiều bước thiết lập ban đầu. Nếu bạn chỉ muốn đọc dữ liệu từ một sheet, hãy dùng trình kết nối tích hợp sẵn và bạn sẽ bắt đầu trong chưa đến năm lần nhấp chuột. Nếu muốn vừa đọc vừa ghi dữ liệu với Google Sheets, đáng để bật Google Sheets API và thiết lập một service account.

Ở cả hai trường hợp, khi đã cấu hình đúng, truy cập dữ liệu từ Google Sheets hầu như dễ như truy vấn một tệp CSV. Ưu điểm lớn là bạn có thể làm việc đúng nơi các bên liên quan đang ở: khả năng cao một số đồng nghiệp của bạn rất ưa dùng bảng tính.

Tạm biệt thời kỳ xuất CSV thủ công tốn công và chào đón những quy trình tự động, mạnh mẽ để làm việc với dữ liệu Google Sheets mà không cần rời khỏi DataLab.