ユーザーブロマガは2021年10月7日(予定)をもちましてサービスを終了します

【ニコニコランキング】Twitter集計導入のいろはにほへと
閉じる
閉じる

新しい記事を投稿しました。シェアして読者に伝えましょう

×

【ニコニコランキング】Twitter集計導入のいろはにほへと

2017-06-25 08:23

    毎度ありがとうございます

    ニコニコランキング制作をしていて、なにか視聴者の興味を引くような
    項目を追加できたらいいなー...と考えていたのですが

    そこで思いついたのが
    「動画IDを含むツイート数を集計して表示する」
    というものです


    *-* *-* *-*


    ◆使用ツール


    EXCEL
    一応2013以降を推奨しますが、なんでも良いと思います


    cmd


    Python
    3.x.xで良いんじゃないでしょうか


    *-* *-* *-*


    ◆導入の壁

    ニコニコランキング制作を行うユーザーであれば、ニコニコAPIの取得は
    試してみたことがある方も少なくはないと思います

    ニコニコAPIの場合だと、EXCEL2013以降の表計算ソフトであれば
    WEBSERVICE関数というものを使用すると比較的容易に取得ができます


    F列にWEBSERVICE関数を置き、B/G/H列でFILTERXML関数を用いて
    タイトルなどの情報を取得しています

    しかし、TwitterのAPIは
    取得の際にOAuth認証というものを行わないといけません
    そのあたりの知識が皆無である場合は、大きなハードルとなります(自分はなりました)

    そのあたりに関しては、VBAよりもPythonのほうが情報がたくさんあって
    参考にしやすいので 今回は認証・検索はPythonで行うことにしました


    *-* *-* *-*


    ◆検索までの流れ

    NRMなどで作成したテキストをEXCELに読み込む

    それをもとにPython用プログラムファイル(.py)を作成

    .pyファイルを呼び出すためのバッチファイルを作成

    EXCELからバッチファイルを実行して結果をcsvに保存

    csvをEXCELに読み込み、ツイート数を解析

    という感じになります

    めんどくさい!こんなの手動でやってられない!
    ということで、マクロに書き起こしていきます


    *-* *-* *-*


    ◆全容


    作業台となるxlsmはだいたいこんな感じです
    「CAMP」・・・実行&結果表示画面
    「twcsv.py」・・・Pythonプログラムファイル
    「TEMP」・・・csvの内容を保管


    *-* *-* *-*


    ◆技術的な話

    ・NRMなどで作成したテキストをEXCELに読み込む

    Sub Yomikomi()

    Dim wb_A As Workbook, wb_B As Workbook
    Dim ws_A As Worksheet, ws_B As Worksheet

    Set wb_A = ThisWorkbook
    Set ws_A = wb_A.Sheets("CAMP")
    Set wb_B = Workbooks.Open(Range("B30").Value) 'B30で指定したパスのtxtを開く
    Set ws_B = wb_B.Sheets(1)

    ws_B.Range("A1:A27").Copy 'txtの動画IDをコピー
    ws_A.Activate
    ws_A.Paste Destination:=Range("C2") 'コピーしたのをC2に貼り付け
    wb_B.Close SaveChanges:=False

    End Sub

    読み込んでコピペするだけなのでこんなもんです

    ・それをもとにPython用プログラムファイル(.py)を作成

    #!/user/bin/env python
    # -*- coding: utf-8 -*-

    from requests_oauthlib import OAuth1Session
    import csv
    import json
    import sys, codecs

    C_KEY = '**********'
    C_SECRET = '**********'
    A_KEY = '*****-**********'
    A_SECRET = '**********'

    def Search_words():
     url = "https://api.twitter.com/1.1/search/tweets.json?ほにゃらら"
     tw = OAuth1Session(C_KEY,C_SECRET,A_KEY,A_SECRET)
     req = tw.get(url)
     tweets = json.loads(req.text)

     f = open("tweetsearch.csv" , "ab")
     writer = csv.writer(f)

     for tweet in tweets["statuses"]:
      id = (tweet["user"]["screen_name"].encode("utf-8"))
      time = (tweet["id"])

      writer.writerow([id, time])

     f.close()

     return Search_words

    Search_words()

    参考:Python で Twitter API にアクセス - http://qiita.com/yubais/items/dd143fe608ccad8e9f85

    緑字で書かれている行(C_KEY/C_SECRET/A_KEY/A_SECRET)に関しては
    APIトークンと呼ばれ、自分のTwitterアカウントのものを事前に
    取得しておかないといけません

    参考:Twitter REST APIの使い方 - https://syncer.jp/Web/API/Twitter/REST_API/

    同じく緑字で書かれているurl = ~については、ここが検索URLとなるので
    生成するためにEXCELの関数を用います

    =" url = ""https://api.twitter.com/1.1/search/tweets.json?
    q="&CAMP!A2&
    IF(LEN(TEMP!B100)=0,"","&max_id="&TEMP!B100-1000)&"
    &lang=ja
    &result_type=mixed
    &count=100
    """

    見栄えの問題で改行していますが、これで1つの関数となります
    2行目が検索キーワード
    3行目がオフセット
    6行目がツイート取得数です

    Twitter側の制限により一回に取得できるツイート数は100が上限です
    100以上の検索結果がある場合、複数回に分けて取得を行う必要があります

    2回目以降の取得は、3行目に存在するmax_idを指定して行います
    ツイート取得の際にA列にユーザーID、B列にツイートIDを書き出しているので
    セルB100の値-1000を指定してやることで、最新のツイートから数えて
    101個目のツイートから取得をすることが可能となります

    ・それをもとにPython用プログラムファイル(.py)を作成
    ・.pyファイルを呼び出すためのバッチファイルを作成
    ・EXCELからバッチファイルを実行して結果をcsvに保存
    ・csvをEXCELに読み込み、ツイート数を解析
    これら一連の流れをマクロに書き起こしたものが以下になります

    Sub pyHozon()

    Dim Search As Integer

    For Search = 2 To 28 'セルC2~C28までの動画IDを検索するための引数

     Range("A2").Value = Range("C" & Search).Value '検索したいIDをA2にコピー
     ActiveWorkbook.Sheets("TEMP").Range("A1:B100").ClearContents '検索結果リセット

     '.pyの保存
     Set ws = ActiveWorkbook.Sheets("twcsv.py")
     datFile = CurrentDir & "1.py"
     FileNo = FreeFile()
     Open datFile For Output As #FileNo
      For i = 1 To 37
       Print #FileNo, ws.Cells(i, 1).Text
      Next
     Close #FileNo

     'バッチファイルの保存
     datFile = CurrentDir & "1py.bat"
     FileNo = FreeFile()
     Open datFile For Output As #FileNo
      Print #FileNo, "@echo off"
      Print #FileNo, "pushd %0\.."
      Print #FileNo, "python 1.py"
     Close #FileNo

     'バッチファイルの実行
     Dim batFileName As String
     Dim ShellObject As Object

     batFileName = CurrentDir & "1py.bat"
     Set ShellObject = CreateObject("WScript.Shell")
     ShellObject.Run batFileName, 1, True

     '出力されたcsvを読み込む
     Dim wb_A As Workbook, wb_B As Workbook
     Dim ws_A As Worksheet, ws_B As Worksheet

     Set wb_A = ActiveWorkbook
     Set ws_A = wb_A.Sheets("TEMP")
     Set wb_B = Workbooks.Open(CurrentDir & "tweetsearch.csv")
     Set ws_B = wb_B.Sheets(1)

     Dim RANK1 As Variant

     RANK1 = ws_A.Range("A1:B100")
     For i = 1 To 100
      For j = 1 To 2
       RANK1(i, j) = ws_B.Cells(i, j).Value
      Next
     Next
     ws_A.Range("A1:B100") = RANK1
     wb_B.Close SaveChanges:=False

     'ツイートIDの書式設定
     Columns("B").NumberFormatLocal = "0"

     hnd = 0 'ツイート数の合計を出すための変数

     '1回の取得数が100以下になるまで試行
     Do While Len(Sheets("TEMP").Range("B100").Value) <> 0
      Kill CurrentDir & "1.py" '初回検索分のpyファイル削除

      '.pyの保存
      Set ws = ActiveWorkbook.Sheets("twcsv.py")
      datFile = CurrentDir & "1.py"
      FileNo = FreeFile()
      Open datFile For Output As #FileNo
       For i = 1 To 37
        Print #FileNo, ws.Cells(i, 1).Text
       Next
      Close #FileNo

      Kill CurrentDir & "1py.bat"
      Kill CurrentDir & "tweetsearch.csv"

      'バッチファイルの保存
      datFile = CurrentDir & "1py.bat"
      FileNo = FreeFile()
      Open datFile For Output As #FileNo
       Print #FileNo, "@echo off"
       Print #FileNo, "pushd %0\.."
       Print #FileNo, "python 1.py"
      Close #FileNo

      'バッチファイルの実行
      batFileName = CurrentDir & "1py.bat"
      Set ShellObject = CreateObject("WScript.Shell")
      ShellObject.Run batFileName, 1, True

      wb_A.Sheets("TEMP").Range("A1:B100").ClearContents '初回検索結果削除

      '出力されたcsvを読み込む
      Set wb_A = ActiveWorkbook
      Set ws_A = wb_A.Sheets("TEMP")
      Set wb_B = Workbooks.Open(CurrentDir & "tweetsearch.csv")
      Set ws_B = wb_B.Sheets(1)

      RANK1 = ws_A.Range("A1:B100")
      For i = 1 To 100
       For j = 1 To 2
        RANK1(i, j) = ws_B.Cells(i, j).Value
       Next
      Next
      ws_A.Range("A1:B100") = RANK1
      wb_B.Close SaveChanges:=False

      'ツイートIDの書式設定
      Columns("B").NumberFormatLocal = "0"

      hnd = hnd + 100 'ツイート数の合計を出すための変数

     Loop

     '検索用ファイル削除
     Kill CurrentDir & "1.py"
     Kill CurrentDir & "1py.bat"
     Kill CurrentDir & "tweetsearch.csv"

     'ツイート数の合計出力
     Sheets(1).Range("E" & Search).Value = "=" & hnd & "+COUNTA(TEMP!A1:A100)"
     Sheets(1).Range("E" & Search).Value = Sheets(1).Range("E" & Search).Value

    Next Search

    Range("A2").ClearContents
    Sheets(1).Activate

    End Sub

    解説をするのは流石に骨が折れるので、コメントアウトの部分を見たり
    ググったりしながら理解を深めて頂くようお願いします...

    以上の処理が正常に完了すると、各動画のIDを含むツイート数が
    それぞれ取得できます

    最後に、もとのテキストファイルにツイート数情報を
    付記して保存をすれば完成となります

    *-* *-* *-*


    ◆くわえて

    NRMでフレーム画像を作成するときに今回の値を読み込むように
    レイアウトファイルを設定してやれば
    フレームにツイート数を表示させることができます



    いかがでしょうか、Twitter集計

    私の気力不足で説明がしきれていない部分もあり
    わからねえよカス等言われても仕方ないですが...
    ランキング作者の皆さんはこれを機に検討してみてはどうでしょう


    広告
    コメントを書く
    コメントをするには、
    ログインして下さい。