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職人の腕の見せどころです。
- 正式な曲名と同じ場合はそのままにする
- 曲名が違った場合、正しい曲名にする
正式な曲名と同じ場合はそのままにする
まず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です!が、ここで私はちょっと力尽きました。次回の更新はちょっと時間があきそうです。