Ruby
Excel
AppleScript
osascript

#好きなエビ曲を10曲あげる で統計をまなぶ(その2. Excel編)

Excelに書き出す

前回、ツイートのリストを取得出来ましたので、今回はその解析をするために曲名を取り出します。
ラッキーなことに多くの方が曲を1曲1曲改行してくれているのでCSVに書き出しやすかったです。

JSONからCSVに書き出す.rb

require 'json'
require 'csv'

# すべてのツイートを格納するリスト
timelines = []
# コレクションを格納したリストをすべてのツイートを格納
%w{1.json 2.json 3.json 4.json 5.json 6.json 7.json 8.json}.each {|file_name|
    File.open(file_name) {|file|
        json = JSON.load(file)

        json["response"]["timeline"].each { |tweet|
            tweet_data = json["objects"]["tweets"][tweet["tweet"]["id"]]
            user_data = json["objects"]["users"][tweet_data["user"]["id_str"]]
            timelines.push(
                [tweet_data["id_str"], tweet_data["created_at"], user_data["id_str"], user_data["screen_name"], tweet_data["text"]]
            )
        }
    }
}

# CSVにツイートを書き出す
CSV.open("./list.csv", "wb") do |csv|
    timelines.each { |tweet|
        csv << tweet
        text = tweet[4]
        # ツイートを改行で分解、1行1行出力する
        song_list = text.split(/\n/)
        song_list.each { |song|
            csv << ['','','','','',song]
        }
    }
end

このスクリプトをすることで次のようなCSVを出力することを念頭においています。

ツイートID 日時 ユーザID ユーザ名 ツイート 曲候補
xxxxxxx Tue Aug 31, 12:31:45+00:00:00 2017 1234567890 ファミリー No.1 #好きなエビ曲10曲あげる
難しい・・・
まっすぐ
面皰(以下略)
#好きなエビ曲10曲あげる
難しい・・・
まっすぐ
面皰

これをExcel(実際にはLibreOffice)に書き出して、名寄せと曲名でない行を消していきます。

名寄せをする

「まっすぐ」、「梅」みたいな簡単な曲ならば、そのままでいいのですが、「スパヒロ」(スーパーヒーロ)、「仮契約」(仮契約のシンデレラ)みたいな略称もありますし、「U.B.U.」みたいに「.」があったりなかったりする曲もあります。これらを曲名を正式な名称に統一します。

ここで、長年SEをやっていたExcel職人の腕の見せどころです。

  1. 正式な曲名と同じ場合はそのままにする
  2. 曲名が違った場合、正しい曲名にする

正式な曲名と同じ場合はそのままにする

まずiTunesから自分が持っている曲のリストを取得します(動作環境はmacOSのみです)。

$ osascript -e '
tell application "iTunes"
    repeat with t in tracks whose artist is "私立恵比寿中学"
        log (name of t as text)
    end repeat
    repeat with r in tracks whose album artist is "私立恵比寿中学"
        log (name of t as text)
    end repeat
end tell' > track_list.txt 2>&1
$ sort track_list.txt | uniq | pbcopy

これでクリップボードに曲名一覧がとれたので、ExcelにSongsシートに貼り付けます。そして、曲名の候補の横に次のような関数を入れます。
=IF(NOT(ISNA(VLOOKUP([曲名候補のセル],Songs!A$1:B$209,1,0))),[曲名候補のセル],"")
これは、Songsシートの中に曲名候補のセルと同じ名前のセルがあった場合、そのセルを出力、なかった場合、空文字を出力するようにします。
(ちなみに[曲名候補]のセルの部分は正しいセル名に変換してください)

曲名が違った場合、正しい曲名にする

先の方法で正しい曲名が得られなかった場合、正しい曲名に直す必要がありますが、何回も同じ名前を修正するのが面倒です。ですから、すでに今までに同じ文字列(曲名候補)があった場合、それを再利用にします。

=IF([曲名候補セル]<>"",[曲名候補セル],IF(NOT(ISNA(INDEX(F$1:F$65535,MATCH([曲名候補セル],F$1:F$65535,0),3))),INDEX(F$1:F65535,MATCH([曲名候補セル],F$1:$65535,0),3),""))

コレは次のような表をイメージしています

F G H I J
1 曲名候補 正式名称にマッチしたときのセル 名寄せしたセル 名寄せを探すセル 正式曲名
2
3 ナチュメロ ナチュメロらんでぶー ナチュメロらんでぶー
4 ナチュメロ ナチュメロらんでぶー ナチュメロらんでぶー
  • F2の曲名候補がSongsシートにあれば、そのまま利用します
  • G2のようにSongsシートになければ、H3列に正式な曲名を入れます。するとF4のようにI4列に自動的に正式曲名が入るようにします
  • あとはG列かI列に値が入って入ればJ列に入るようにします

こうすることで7時間程度で7000曲の名寄せが完了しました。

Rなどに読み込みやすい形にする

最後にCSVに書き出し、次のようなスクリプトを実行して終了です。

require 'csv'

CSV.open("名寄せ済みファイル.csv", "wb",
        :write_headers => true,
        :headers => ["tweet_id", "date", "user_id", "user_name", "track"]
    ) { |output|
    CSV.open("解析結果.csv") { |csv|
        info = {}
        csv.each { |line|
            if (line[0] != nil)
                # 先頭行に値が入っている場合はツイートのデータのIDなどを格納する
                info = {
                    "tweet_id" => line[0],
                    "date" => line[1],
                    "user_id" => line[2],
                    "user_name" => line[3],
                    "tweet" => line[4]
                }
            else
                # 先頭行がない場合は曲名の行なので先ほど取得したデータと一緒に出力する
                output << [
                    info["tweet_id"], info["date"], info["user_id"],
                    info["user_name"],
                    line[8]
                ]
            end
        }
    }
}

さぁ、ここからRです!が、ここで私はちょっと力尽きました。次回の更新はちょっと時間があきそうです。