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

## Excelに書き出す
[前回](http://qiita.com/impepc/items/0f8df64fa39b995c1501)、ツイートのリストを取得出来ましたので、今回はその解析をするために曲名を取り出します。
ラッキーなことに多くの方が曲を1曲1曲改行してくれているのでCSVに書き出しやすかったです。

JSONからCSVに書き出す.rb

```ruby
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曲あげる<br>難しい・・・<br>まっすぐ<br>面皰(以下略)  |   |
||||||#好きなエビ曲10曲あげる|
||||||難しい・・・|
||||||まっすぐ|
||||||面皰|

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

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

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

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

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

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

```bash
$ 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に書き出し、次のようなスクリプトを実行して終了です。

```ruby
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です!が、ここで私はちょっと力尽きました。次回の更新はちょっと時間があきそうです。