TwitterAPIv2_get_following_and_save_google_spreadsheets.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "TwitterAPIv2_get_following_and_save_google_spreadsheets.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/webbigdata-jp/python_sample/blob/main/TwitterAPIv2_get_following_and_save_google_spreadsheets.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "-glO6k96Sx9b"
      },
      "source": [
        "# Twitterで自分がフォローしているフォロー先一覧を取得してCSV及び、Googleスプレッドシートに保存するPythonスクリプト\n",
        "\n",
        "**前提条件**  \n",
        "TwitterのAPIを使う為に開発者登録をする方法(2021年版)  \n",
        "(https://webbigdata.jp/page-640/page-11005/page-10971)  \n",
        "を参考にTwitterのDeveloper Account登録をしてBearer Tokenを取得済である事\n",
        "\n",
        "**大まかな流れ**\n",
        "\n",
        "(1)自分のusername(@hogeのhogeの部分)からuser_id(ユーザを一意に特定する番号)を得る\n",
        "\n",
        "(2)user_idとBearer Tokenを使って自分がフォローしているユーザの一覧を得てCSVに保存する\n",
        "\n",
        "(3)CSVから取得したフォロー先ユーザの情報をGoogle Spread Sheetに保存する\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ZTyf4KMASbJd"
      },
      "source": [
        "## (1)自分のusernameからuser_idを取得\n",
        "\n",
        "参考情報\n",
        "\n",
        "Twitter社が提供している公式サンプル\n",
        "\n",
        "https://github.com/twitterdev/Twitter-API-v2-sample-code/blob/main/User-Lookup/get_users_with_bearer_token.py"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "pcL23ZYMOgBO"
      },
      "source": [
        "import requests\n",
        "import os\n",
        "import json\n",
        "\n",
        "# ★TODO：自分のBearer Tokenに差し替えてください。\n",
        "bearer_token = \"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\"\n",
        "\n",
        "def create_url():\n",
        "    # ★TODO：XXXXXXを自分のusernames(@hogehogeのhogehogeの部分)に差し替えてください。\n",
        "    user_fields = \"user.fields=description,created_at\"\n",
        "    usernames = \"usernames=XXXXXXX\"\n",
        "    url = \"https://api.twitter.com/2/users/by?{}&{}\".format(usernames, user_fields)\n",
        "    return url\n",
        "\n",
        "def bearer_oauth(r):\n",
        "    r.headers[\"Authorization\"] = f\"Bearer {bearer_token}\"\n",
        "    r.headers[\"User-Agent\"] = \"v2UserLookupPython\"\n",
        "    return r\n",
        "\n",
        "def connect_to_endpoint(url):\n",
        "    response = requests.request(\"GET\", url, auth=bearer_oauth,)\n",
        "    print(response.status_code)\n",
        "    if response.status_code != 200:\n",
        "        raise Exception(\n",
        "            \"Request returned an error: {} {}\".format(\n",
        "                response.status_code, response.text\n",
        "            )\n",
        "        )\n",
        "    return response.json()\n",
        "\n",
        "def main():\n",
        "    url = create_url()\n",
        "    json_response = connect_to_endpoint(url)\n",
        "    print(json.dumps(json_response, indent=4, sort_keys=True))\n",
        "\n",
        "if __name__ == \"__main__\":\n",
        "    main()\n",
        "\n",
        "'''\n",
        "実行すると、以下のように表示されるので\"id\": \"xxxxxxxxxxxxxxxxxxxx\"のxxxxxxxxxxxxの部分をメモっておく\n",
        "200\n",
        "{\n",
        "    \"data\": [\n",
        "        {\n",
        "            \"created_at\": \"yyyy-mm-ddThh:mm:ss.000Z\",\n",
        "            \"description\": \"xxxxxxxxxxxxxxxxxxx\",\n",
        "            \"id\": \"xxxxxxxxxxxxxxxxxxxxxxxxx\",\n",
        "            \"name\": \"XXXXX\",\n",
        "            \"username\": \"XXXX\"\n",
        "        }\n",
        "    ]\n",
        "}\n",
        "'''"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xUY6i-tSSaLW"
      },
      "source": [
        "## (2)user_idとBearer Tokenを使ってフォロー先のユーザ情報を得る\n",
        "\n",
        "参考情報\n",
        "Twitter社が提供している公式サンプル\n",
        "\n",
        "https://github.com/twitterdev/Twitter-API-v2-sample-code/blob/main/Follows-Lookup/followers_lookup.py\n",
        "\n",
        "https://developer.twitter.com/en/docs/twitter-api/users/follows/api-reference/get-users-id-following\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8ueS31WjTdNR"
      },
      "source": [
        "import requests\n",
        "import os\n",
        "import json\n",
        "import pandas as pd\n",
        "\n",
        "# ★TODO：自分のBearer Tokenに差し替えてください。\n",
        "bearer_token = \"xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\"\n",
        "\n",
        "def create_url():\n",
        "    # ★TODO：xxxxxxxxxxxxxの部分を(1)で取得した自分のuser_idに差し替えてください。\n",
        "    # また、このAPIは連続して実行すると\n",
        "    # 「Request returned an error: 429 Rate limit exceeded」とエラーになる事があります。\n",
        "    user_id = xxxxxxxxxxxxxxxxxxx\n",
        "    return \"https://api.twitter.com/2/users/{}/following\".format(user_id)\n",
        "\n",
        "def get_params(pagination_token=\"\"):\n",
        "    if pagination_token != \"\":\n",
        "      return {\"user.fields\": \"id,name,username,public_metrics\", \"max_results\": \"1000\", \"pagination_token\": pagination_token}\n",
        "    else:\n",
        "      return {\"user.fields\": \"id,name,username,public_metrics\", \"max_results\": \"1000\"}\n",
        "\n",
        "def bearer_oauth(r):\n",
        "    r.headers[\"Authorization\"] = f\"Bearer {bearer_token}\"\n",
        "    r.headers[\"User-Agent\"] = \"v2FollowersLookupPython\"\n",
        "    return r\n",
        "\n",
        "def connect_to_endpoint(url, params):\n",
        "    response = requests.request(\"GET\", url, auth=bearer_oauth, params=params)\n",
        "    # print(response.status_code)\n",
        "    if response.status_code != 200:\n",
        "        raise Exception(\n",
        "            \"Request returned an error: {} {}\".format(\n",
        "                response.status_code, response.text\n",
        "            )\n",
        "        )\n",
        "    return response.json()\n",
        "\n",
        "def save_range_list(json_response, range_list):\n",
        "  cell_list = []\n",
        "  for v in json_response['data']:\n",
        "    # userを一意に特定できる不変のID\n",
        "    cell_list.append(v['id'])\n",
        "    # プロフィール画面などに出て来る名称\n",
        "    cell_list.append(v['name'])\n",
        "    # Twitterアカウントの@hogehogeのhogehogeの部分\n",
        "    cell_list.append(v['username'])\n",
        "    cell_list.append(v['public_metrics']['followers_count'])\n",
        "    cell_list.append(v['public_metrics']['listed_count'])\n",
        "    cell_list.append(v['public_metrics']['tweet_count'])\n",
        "    range_list.append(cell_list)\n",
        "    cell_list = []\n",
        "\n",
        "def main():\n",
        "    url = create_url()\n",
        "    params = get_params()\n",
        "    json_response = connect_to_endpoint(url, params)\n",
        "    #print(json.dumps(json_response, indent=4, sort_keys=True))\n",
        "\n",
        "    range_list = [['id', 'name', 'username', 'followers_count', 'listed_count','tweet_count']]\n",
        "    save_range_list(json_response, range_list)\n",
        "\n",
        "    # 15分に15回までの連続アクセス対策\n",
        "    import time\n",
        "    cnt = 1\n",
        "    time.sleep(60)\n",
        "    \n",
        "    while (json_response.get(\"meta\") and json_response.get(\"meta\").get(\"next_token\")):\n",
        "      params = get_params(json_response['meta']['next_token'])\n",
        "\n",
        "      # network error 対策\n",
        "      for _ in range(3):  # 最大3回リトライ\n",
        "        try :\n",
        "          json_response = connect_to_endpoint(url, params)\n",
        "        except Exception as e:\n",
        "          # ConnectionResetError: [Errno 104] Connection reset by peer\n",
        "          print(e)\n",
        "          time.sleep(60)\n",
        "        else:\n",
        "          break  # 失敗しなかった時はループを抜ける\n",
        "      else:\n",
        "        print(\"retry fail\")\n",
        "\n",
        "      save_range_list(json_response, range_list)\n",
        "      print(cnt)\n",
        "      cnt += 1\n",
        "      time.sleep(61)\n",
        "\n",
        "\n",
        "    # /content/df_following.csvとして保存する\n",
        "    df = pd.DataFrame(range_list)\n",
        "    df.to_csv(\"/content/df_following.csv\", header=False, index=False)\n",
        "\n",
        "\n",
        "if __name__ == \"__main__\":\n",
        "    main()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Q-_pjKjXFjpM"
      },
      "source": [
        "## (3)Google スプレッドシートに保存する\n",
        "\n",
        "参考情報：\n",
        "Google スプレッドシートを操作するためのライブラリgspread  \n",
        "https://docs.gspread.org/en/latest/index.html"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "iZ8d78xWNi4A"
      },
      "source": [
        "# ディフォルトで入っているgspreadはVersionが古い。\n",
        "# version 3.7以上でないと「AttributeError: 'Worksheet' object has no attribute 'update'」\n",
        "# とエラーがでてworksheet.updateに失敗する。そのためアップデートを行う。\n",
        "# ★TODO：このセル実行後は上段メニューから「ランタイム」→「ランタイムを再起動」で再起動する事\n",
        "! pip install -U gspread"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AFQ8GIlSFjEC"
      },
      "source": [
        "import gspread\n",
        "import pandas as pd"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Uq5R6fw6Gaac"
      },
      "source": [
        "from google.colab import auth\n",
        "from google.auth import default\n",
        "from oauth2client.client import GoogleCredentials\n",
        "import gspread\n",
        "\n",
        "auth.authenticate_user()\n",
        "creds, _ = default()\n",
        "gc = gspread.authorize(creds)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AROMZCVFYZ9o"
      },
      "source": [
        "workbook = gc.create('Twitter_user_list_following')\n",
        "\n",
        "# CSVの読み込み\n",
        "df = pd.read_csv(\"/content/df_following.csv\")\n",
        "worksheet = workbook.get_worksheet(0)\n",
        "\n",
        "# idを数値のままにしておくと巨大すぎてGoogleスプレッドシートで末尾が000に切り捨てられてしまうため\n",
        "df = df.astype({'id': str})\n",
        "\n",
        "# 空の項目(NaN)があると「APIError: {'code': 400, 'message': 'Invalid JSON payload received. Unexpected token～}」\n",
        "# のエラーがでて失敗するのでNaNを埋める\n",
        "df.fillna('', inplace=True)\n",
        "\n",
        "worksheet.update([df.columns.values.tolist()] + df.values.tolist())\n"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EAgwXEeJp_wA"
      },
      "source": [
        "\n",
        "スプレッドシートは( https://drive.google.com/drive/my-drive) に出来ます。  \n",
        "\n",
        "Colabのランタイムをリセットしないとゴミ箱に入れたファイルに書き込んだり、\n",
        "どのスプレッドシートに書き込まれたのかわからなくなってしまったり、\n",
        "Requested entity was not foundとエラーが出る事があります。\n",
        "その場合はランタイムを再起動したり、以下の方法で捜し出してください。\n",
        "\n",
        "\n",
        "上のセルを実行した際に  \n",
        "{'spreadsheetId': 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx',  \n",
        "・・・と表示されると思いますが、  \n",
        "スプレッドシートのURLのyyyyyyyyyyyyyyyyyyyyyyyyyyyの部分を置き換えると直接アクセスできます。  \n",
        "https://docs.google.com/spreadsheets/d/yyyyyyyyyyyyyyyyyyyyyyyyyyy/edit#gid=0\n",
        "\n"
      ]
    }
  ]
}