コピペでできるサクッとデータ分析。「Googleスプレッドシート × Pythonで“週データ”を自動集計して“月トレンド”の可視化」─ Colab × Plotlyで一瞬グラフ化する方法。 #102

# @title 2-1.Googleアカウント認証
from google.colab import auth
from google.auth import default
import gspread

# 認証（最初の1回だけポップアップが出る）
auth.authenticate_user()
creds, _ = default()
gc = gspread.authorize(creds)

# 📂 スプレッドシートのURLを指定（自分のURLに置き換え）
SPREADSHEET_URL = "https://docs.google.com/spreadsheets/d/{XXXXXXX}/edit?gid=0#gid=0"

# ▼ 接続
spreadsheet = gc.open_by_url(SPREADSHEET_URL)
print("✅ 接続OK：", spreadsheet.title)

# @title 2-1. データの読み込み：ビュー数
sheet = spreadsheet.worksheet("サマリー_月")  # ワークシート名を指定
rows = sheet.get_all_values()

# 1行目をヘッダーとして DataFrame を作成
df_view_mon = pd.DataFrame(rows[1:], columns=rows[0])

# 余白行の除去（空白行をすべて削除）
df_view_mon = df_view_mon.replace('', pd.NA).dropna(how='all').reset_index(drop=True)

# 数値カラムを数値化（エラー時は NaN に）
for col in ["ビュー", "コメント", "スキ"]:
    df_view_mon[col] = pd.to_numeric(df_view_mon[col], errors="coerce")

# 内容を確認
display(df_view_mon.head())
print("✅ 作成したデータフレーム：df_view_mon")

sheet = spreadsheet.worksheet("シート1")

# @title 3-1.月×ビューの折れ線グラフ
import plotly.graph_objects as go

# ビュー列を数値に
df_view_mon["ビュー"] = pd.to_numeric(df_view_mon["ビュー"], errors="coerce")

# 折れ線グラフ作成
fig = go.Figure()
fig.add_trace(go.Scatter(
    x=df_view_mon["週"],
    y=df_view_mon["ビュー"],
    mode="lines+markers",
    name="ビュー",
    line=dict(width=3)
))

fig.update_layout(
    title="週ごとのビュー推移",
    xaxis_title="月",
    yaxis_title="ビュー数",
    hovermode="x unified",
    template="plotly_white",
    xaxis=dict(autorange="reversed")  # 👈 ここがポイント！ 最新日を右に設定。
)

fig.update_traces(hovertemplate="%{y:,}")
fig.show()

# @title 3-4.月×ビュー＋スキ＋コメント（二軸・棒グラフ）
import plotly.graph_objects as go

# 数値変換
for col in ["ビュー", "コメント", "スキ"]:
    df_view_mon[col] = pd.to_numeric(df_view_mon[col], errors="coerce")

# 図の作成
fig = go.Figure()

# --- 左軸：ビュー（折れ線） ---
fig.add_trace(go.Scatter(
    x=df_view_mon["週"],
    y=df_view_mon["ビュー"],
    mode="lines+markers",
    name="ビュー（左軸）",
    line=dict(width=3, color="royalblue"),
    yaxis="y1"
))

# --- 右軸：スキ（棒グラフ） ---
fig.add_trace(go.Bar(
    x=df_view_mon["週"],
    y=df_view_mon["スキ"],
    name="スキ（右軸・棒）",
    marker_color="mediumseagreen",
    opacity=0.6,
    yaxis="y2"
))

# --- 右軸：コメント（棒グラフ） ---
fig.add_trace(go.Bar(
    x=df_view_mon["週"],
    y=df_view_mon["コメント"],
    name="コメント（右軸・棒）",
    marker_color="orange",
    opacity=0.6,
    yaxis="y2"
))

# --- レイアウト設定 ---
fig.update_layout(
    title="月ごとのパフォーマンス推移（ビュー×スキ×コメント）",
    xaxis_title="月",
    yaxis=dict(
        title="ビュー数",
        titlefont=dict(color="royalblue"),
        tickfont=dict(color="royalblue")
    ),
    yaxis2=dict(
        title="スキ／コメント数",
        titlefont=dict(color="darkgreen"),
        tickfont=dict(color="darkgreen"),
        overlaying="y",
        side="right"
    ),
    barmode="group",  # スキとコメントを横並びに
    hovermode="x unified",
    template="plotly_white",
    xaxis=dict(autorange="reversed")  # 最新週を右に
)

fig.update_traces(hovertemplate="%{y:,}")
fig.show()

# @title 4-1. 月ごとの反応率（スキ率・コメント率）
import numpy as np
import pandas as pd
import plotly.graph_objects as go

# 元データをコピー
df = df_view_mon.copy()

# 数値化
for col in ["ビュー", "コメント", "スキ"]:
    df[col] = pd.to_numeric(df[col], errors="coerce")

# 反応率（ビューが0や欠損の行はNaN）
df["スキ率"] = np.where(df["ビュー"] > 0, df["スキ"] / df["ビュー"], np.nan)
df["コメント率"] = np.where(df["ビュー"] > 0, df["コメント"] / df["ビュー"], np.nan)

# 折れ線グラフ（%表示）
fig = go.Figure()
fig.add_trace(go.Scatter(
    x=df["週"], y=df["スキ率"] * 100,
    mode="lines+markers", name="スキ率(%)"
))
fig.add_trace(go.Scatter(
    x=df["週"], y=df["コメント率"] * 100,
    mode="lines+markers", name="コメント率(%)"
))

fig.update_layout(
    title="月ごとの反応率（スキ率・コメント率）",
    xaxis_title="月",
    yaxis_title="率(%)",
    hovermode="x unified",
    template="plotly_white",
    xaxis=dict(autorange="reversed")  # 最新を右に
)
fig.update_traces(hovertemplate="%{y:.2f}%")
fig.show()

# @title 4-2. ビュー × スキ率・コメント率（二軸：棒＋折れ線）
import numpy as np
import plotly.graph_objects as go

# 数値変換
for col in ["ビュー", "コメント", "スキ"]:
    df_view_mon[col] = pd.to_numeric(df_view_mon[col], errors="coerce")

# 反応率計算（ビューが0のときはNaN）
df_view_mon["スキ率"] = np.where(df_view_mon["ビュー"] > 0, df_view_mon["スキ"] / df_view_mon["ビュー"], np.nan)
df_view_mon["コメント率"] = np.where(df_view_mon["ビュー"] > 0, df_view_mon["コメント"] / df_view_mon["ビュー"], np.nan)

# --- グラフ作成 ---
fig = go.Figure()

# 左軸：ビュー（折れ線）
fig.add_trace(go.Scatter(
    x=df_view_mon["週"],
    y=df_view_mon["ビュー"],
    mode="lines+markers",
    name="ビュー（左軸）",
    line=dict(width=3, color="royalblue"),
    yaxis="y1"
))

# 右軸：スキ率（棒）
fig.add_trace(go.Bar(
    x=df_view_mon["週"],
    y=df_view_mon["スキ率"] * 100,
    name="スキ率(%)（右軸）",
    marker_color="mediumseagreen",
    opacity=0.7,
    yaxis="y2"
))

# 右軸：コメント率（棒）
fig.add_trace(go.Bar(
    x=df_view_mon["週"],
    y=df_view_mon["コメント率"] * 100,
    name="コメント率(%)（右軸）",
    marker_color="orange",
    opacity=0.7,
    yaxis="y2"
))

# --- レイアウト設定 ---
fig.update_layout(
    title="月ごとのビュー × 反応率（スキ率・コメント率）",
    xaxis_title="週",
    yaxis=dict(
        title="ビュー数",
        titlefont=dict(color="royalblue"),
        tickfont=dict(color="royalblue"),
    ),
    yaxis2=dict(
        title="反応率(%)",
        titlefont=dict(color="darkgreen"),
        tickfont=dict(color="darkgreen"),
        overlaying="y",
        side="right"
    ),
    barmode="group",
    hovermode="x unified",
    template="plotly_white",
    xaxis=dict(autorange="reversed")  # 最新週を右へ
)

fig.update_traces(hovertemplate="%{y:.2f}")
fig.show()