Google ColabでPandasの結果をスプレッドシートに上書き

Google Colabからの認証で簡単になってたので使いやすい

docs.gspread.org

Pandasからの書き込みも簡単になってた

docs.gspread.org

!pip install -U gspread

import pandas as pd

cities = [
    "今治市",
    "新居浜市",
    "西条市",
    "四国中央市",
    "上島町",
    "松山市",
    "伊予市",
    "東温市",
    "久万高原町",
    "松前町",
    "砥部町",
    "宇和島市",
    "八幡浜市",
    "大洲市",
    "西予市",
    "内子町",
    "伊方町",
    "松野町",
    "鬼北町",
    "愛南町",
]

df0 = pd.read_csv(
    "https://docs.google.com/spreadsheets/d/e/2PACX-1vRDgDbpuBUvGcK7TJKiwMSIfs5yi95qmERjZ_iXHMWSRDlarjwUoDVdvpXOB8a2zpnwpq4Vj9VBHJcf/pub?gid=969792757&single=true&output=csv",
    index_col=0,
    parse_dates=True,
)

df0.drop("愛媛県", axis=1, inplace=True)

df1 = df0.diff().dropna(how="all").astype(int)

df1["日付"] = df1.index.strftime("%Y-%m-%d")

df2 = df1.reindex(columns=["日付"] + cities)

data = [df2.columns.values.tolist()] + df2.values.tolist()

from google.colab import auth

auth.authenticate_user()

import gspread
from oauth2client.client import GoogleCredentials

gc = gspread.authorize(GoogleCredentials.get_application_default())

sh = gc.open_by_key("xxxxxxxxxx")

wks = sh.get_worksheet(0)

wks.update("A1", data)