データベースを用いた良動画発掘方法など

なんとなく始まった「当ブログ・春のデータ分析祭り」、最後はデータベースの活用法で締めたいと思います。

データベースは、色々と工夫をすることにより趣味趣向に合わせた動画を発掘するのに非常に役立ちます。
そこで今回は、ボカロタグの2017年2月うｐ分のデータベースを用いてその一例をご紹介します。

例１：マイリストの多い順に並べてみる

まずは試しに、マイリストの多い順に並べてみます。

①　セルG1（などデータベースの任意のセル）をクリック → データ → 並べ替え


②　赤丸のとおり、マイリスト・降順・タイトル と選んでOK


これで、マイリストの多い順に並べ替えました。マイリストランキングと言ったところですね。



例２：「もっと評価されるべき」動画を探してみる

この状態から、データ的に「もっと評価されるべき」動画を探してみます。
と言っても明確な定義はないので、今回は「再生数1,000未満だけど、マイリストの非常に多いもの」をもっと評価されるべき動画と捉えてみます。

例1ですでに「マイリストの多い順」に並んでいるので・・・

①　E1の▼をクリック → 赤丸のとおり、1000を入力・より小さいを選び、OK をクリック


これで、再生数1,000未満だけどマイリストの多い動画 ≒ もっと評価されるべき動画 が抽出されました。


さらに、再生数を500未満にしたり10,000未満にしたりすれば、また違った結果が出て面白いと思います。


例３：データベースを元に戻す

ここで、データベースを元に戻してみます。

①　データ → フィルタ → すべて表示 とクリック　（※例２－①のようにフィルタ機能を使った場合。使ってないなら、この作業は不要です）


②　例１のように、データ → 並べ替え から 日時・昇順・タイトル と選んでOK


これで無事、元の状態（うｐ日時順のデータベース）に戻りました。（厳密にはちょっと違いが生じますが、ほぼ誤差の範囲内です）


元の状態に戻すのに、上記の方法以外では「セーブをしないで終了→再びファイルを開く」という力業もあります。お好みに合わせてご使用ください。


例４：マイリス率の高い順に並べてみる

①　まずはマイリス率の式をセルH2に作ります。（単純にマイリスを再生数で割ると再生数0の時にエラーが出るので、一工夫しています。）


②　このままだと「0」表示になるので、H2を右クリック → セルの書式設定 → 以下のように設定すると・・・


③　パーセント表示になったので、それをデータベース全体（H2からH3284まで）にコピペします。


④　例１のように、データ → 並べ替え から 宣伝・降順・タイトル と選んでOK（なおラベル（H1）は宣伝のままですが、気になる場合はマイリス率などに変更してもOKです）


⑤　マイリス率の高い順に並び替えが出来ました！ただ、このままだと再生数が極端に低い動画が有利なので・・・


⑥　例２－①のように「再生数100以上」でフィルタしてみました。これを「再生数1000以上」とかいじってみるのも、また面白いと思います。



例５：マイリス率の中央値を調べてみる

せっかくなので、マイリス率の中央値を調べてみましょう。
と言っても作業は簡単で、例３－１のようにフィルタを外した後（データ → フィルタ → すべて表示 とクリック）、
すでにマイリス率順に並んでいる状態なので、その中央のデータを調べればOKです。

結果、該当データ（3283個中の1642位）のマイリス率は2.3%でした。


よく「ボカロはマイリス率5%欲しい」なんて意見がありますが、現在、それはとんでもなく上位の数値な訳ですね。
ちなみにこのデータベースでマイリス率5%以上は285動画、率にして8.6%しかありませんでした。


例６：「ききいる○○うた」動画を探してみる

最後にもう一例、データからききいる○○うたを探してみましょう。
「ききいるミクうた」などのききいる系≒「思わずコメントするのを忘れてしまうほどいい歌」なので、「コメントが少なく、マイリストが多い動画」を探せばOKです。

①　まず、例３のとおりに元の状態に戻して、さらにH列のデータを全消去します。

②　あとは例４と手順はほぼ同じです。H2に式を作る（マイリス÷コメントで、数値が大きいほどききいる系になる） → 全体に貼付け → 並べ替えで・・・


③　こうなりました！と思ったら、上位がニコカラに占められる結果になりました。


④　それはそれで一つの結果ですが、ニコカラを省いたバージョンも作ってみます。A1の▼をクリック → ニコカラ → 含まない → OK で・・・


⑤　ききいる○○うたリスト、ニコカラ抜きバージョンが出来ました。


⑥　さらに、コメント5以上でフィルタをかけてみました。時期的に、MMD杯の動画が多く入ってきております。


⑦　なので、④の条件に「MMDを含まない」も追加してみました。これで、目標の「ききいる○○うたリスト」にかなり近づいたと思います。

　※なおMMD入りのタイトルが一つ残っているのは、条件で除いたのがMMD（半角）のみであり、全角の「ＭＭＤ」は除けなかったためです。


以上、データベースを用いた良動画発掘方法でした。

他にも、「コメントとマイリストがほぼ同数で、どちらも100を超えている元気な動画」とか「動画の長さが1分未満でコメントが非常に多いネタ動画」など、
アイデア次第で色々と発掘できると思います。
当ブログでは、2015年12月うｐ分以降のタグ別・月間いろいろ調査各ページからそれぞれのデータベースをダウンロードできるので、
ぜひ色々と試していただければと思います。
あ、蛇足ですがどれだけダウンロードされてもブログ主にはびた一文入ってこないので、安心(?)してダウンロードしてくださいませ＾＾

それでは、今月はこの辺で。
以前にお伝えしたとおり、当ブログは5月更新分をもって休眠期間に入る予定ですが、よろしければそれまでお付き合いのほど、どうぞよろしくお願いいたします。

タグ別・月間いろいろ調査 けものフレンズ 2017年2月うｐ分編

話題の「けものフレンズ」について、2017年2月うｐ分を調査してみました。
まずは、このグラフをご覧ください。


うｐ日別の、うｐ数と再生数中央値のグラフです。

よくアニメ版けものフレンズの記事を読むと、「2月のある日、突然大ブレイクした」という記述があるのですが、ニコ動でもそれが見てとれます。
2月の1日から6日のうｐ数推移は「1、3、6、1、3、7」と一桁だったのに対し、その後の一週間は「14、40、42、70、87、92、89」と急激に増え、
そのまま月末まで高い水準をキープしております。ブログ主はまるで詳しくないのですが、8日前後に何かあった・・・のか？

また、このような大ヒットアニメがあると、ブームに火が点く以前にうｐされた動画が極端に多く再生されるのも特徴的だと思います。
2/1～2/5までを見ると、2/3を除いて再生数中央値が3万以上ですし、2/4なんてうｐ数が1で20万再生されたものだから、グラフがぶっ壊れた状態になっていますｗ
ヒットしそうなアニメを見極め、数が少ないうちにうｐができれば、デイリーランキングの上位を狙うのも可能かも知れません・・・もっとも、それが難しいのですが＾＾；

・中央値は 再生数：2,098 コメント：28 マイリスト：15 で、いずれも非常に高い水準を誇っています。

・うｐ数は1,799で、月頭のうｐが少なかったことを考えると、かなり健闘していると言えます。ちなみに1月のうｐ数は32なので、実に56倍増（!）ということになります。

・再生数ベストテンは、ミリオン超えが6つと大変なことになっております。
  中でも「ねずみフレンズ　～ようこそディズニーランドへ～」はトリプルミリオンで、再生・コメ・マイリス1位の三冠を達成しました！おめでとうございます！
  でも本当に、目線入れたからセーフ・・・なのか！？


その他、各データは以下のとおりです。






・再生数ベストテン

順位	タイトル	うｐ日	長さ	再生数	コメント	マイリスト
1位	ねずみフレンズ　～ようこそディズニーランドへ～	2017/02/26	0:01:30	3,272,505	60,139	46,011
2位	【オーイシマサヨシ】けものフレンズ OP主題歌【仮歌】	2017/02/09	0:01:32	2,460,973	13,351	42,288
3位	ふわああぁ！いらっしゃぁい！よぉこそぉ↑ジャパリカフェへ～！	2017/02/08	0:01:49	1,692,693	13,791	18,023
4位	けものはいてものけものはいない　ただしつけもの、テメーはダメだ	2017/02/09	0:00:39	1,334,723	3,060	5,795
5位	けっとうフレンズ	2017/02/20	0:01:29	1,168,230	5,974	13,804
6位	つくだフレンズ ～ようこそバトルドームへ～	2017/02/22	0:01:32	1,013,763	11,794	24,167
7位	いんどフレンズ	2017/02/11	0:01:29	894,092	4,867	11,995
8位	けものフレンズ5話 問題のシーン	2017/02/08	0:02:02	696,615	4,308	783
9位	すごーい！フレンズ使いはひかれあう運命なんだね！	2017/02/15	0:01:48	670,356	3,260	7,800
10位	TVアニメ『けものフレンズ』主題歌「ようこそジャパリパークへ / どうぶつビスケッツ×PPP」	2017/02/08	0:01:32	607,351	7,750	10,491

・各一位の動画

再生数	3,272,505		ねずみフレンズ　～ようこそディズニーランドへ～
コメント	60,139		ねずみフレンズ　～ようこそディズニーランドへ～
マイリスト	46,011		ねずみフレンズ　～ようこそディズニーランドへ～

［諸データ］



・データベースダウンロード
http://yahoo.jp/box/QLiPDO

データベースの作り方の一例

「○○タグのデータベースを作りたい！」という方はあまり多くないと思いますが、もしかしたら需要があるかも知れない？ので作り方の一例を書いてみようと思います。
手順は、だいたい以下の通りです。

１　ニコニコ超検索から生データをエクセルにコピペ
２　生データをデータベースに変換する式を一つ作成
３　それを全体にコピペしてデータベース完成

では、実際にやってみます。

１　ニコニコ超検索から生データをエクセルにコピペ

ニコニコ超検索でデータベースを作りたいデータを抽出し、1ページ目から最終ページまでひたすらエクセルに貼付けていきます。（例：けものフレンズ 2月うｐ分）

①　超検索のページで、なにもないところにポインタを合わせて全体をコピー（右クリック→A→右クリック→C）します。

　※この例では1,800件と抽出上限の1,600件を超えているので、数回に分けて（2/1～2/14、2/15～2/28 に分ける等）処理する必要があります。

②　それをエクセルにテキスト形式で貼付けます。例としてセルB1に貼りつける時は、B1を右クリック→形式を選択して貼り付け→テキスト→OK になります。


③　以下、①に戻り次のページをコピーし、エクセルに貼付けます。2回目以降は、エクセルのB列の一番下に貼付けていきます。
　（ブログ主の環境（Win8.1、エクセル2002）で、2回目はB1248貼付けになりました） これを、全データ分繰り返します。

④　全部データを貼り終えたら、いったん保存をしておきます。（ファイル名はなんでもOK）


２　生データをデータベースに変換する式を一つ作成

ここのハードルが高いのですが、クリア出来ればデータベースは完成したも同然です。

まず現段階で、生データは以下のような状態になっていると思います。（なお、データの内容はテキトーなものに変えております。）


これを、以下のような横一列のデータに変換したいところで・・・


色々とすっ飛ばして、変換式（の一例）は以下の通りになります。


各セルの式の内容は、以下のとおりです。

D25　(①)　タイトル（B29)を参照する。
E25　(②)　日時データ（B25）の右から16文字（2017-02-12 23:54）を抜き出し、数値にする。
F25　(③)　長さデータ（B26）を60で割って適正値にする。
G・H・I25　(④～⑥)　再生・コメ・マイリストの各データ（B30、B31、B32）について、4文字目から99文字分抜き出し（例：B30の場合は660）、数値にする。
C25　(⑦)　B30の左2文字が「再生」で且つB32の左2文字が「マイ」の場合は1、それ以外は0を表示する。

この中では⑦のチェックの存在意義が分かりにくいと思いますが、とりあえず先に進みます。一応、ここでも保存しておきましょう。

なお、このような作業の厄介なところに、環境によって生データの状態が変わってくることがあります。
要するに、PCやOSやエクセルのバージョンが変わったりすると上記とは違ったデータ配列になり、当然変換式も個々に対応する必要があります。
これはエクセル慣れしてないと非常に苦戦するかも知れませんが、出来るようになると一気にエクセルが楽しくなるので、ぜひ挑戦してみてください。


３　それを全体にコピペしてデータベース完成

①　まず、先ほど作った式（セルC25:I25）をコピーして、データ全体（B列の一番下のデータがあるところまで）に貼付けます。


②　この状態のまま、コピー→値貼付け（右クリック→形式を選択して貼付け→値→OK）をしておきます。（これをしないと、後にフリーズする可能性があります）

③　セルB1をクリック → データ → フィルタ → オートフィルタ とクリック


④　セルC1の▼をクリック → 1をクリックすれば・・・


⑤　余計なデータが消えて、データベースの原型が抽出されました！先ほどの⑦の式は、この為に仕込んでおいたものなのです。


⑥　あとはD～I行をコピーして、新しいファイルに値貼付け＆体裁を整えれば、データベースの完成です！


ちなみに日時は、Bを右クリック→セルの書式設定→日付から以下のものをクリック、


長さは、Cを右クリック→セルの書式設定→ユーザー定義から以下のものをクリック（無い場合は h:mm:ss と直接入力）で書式を選択すればOKです。


⑦　さらに、上記③と同様にA1をクリック→オートフィルタ

⑧　B2をクリック → ウィンドウ → ウィンドウ枠の固定をすると、


より便利で、データ抽出も可能なデータベースが完成します。


以上です。
データベース作成は、決して簡単ではないうえに手間もかかるのですが、データ好きな人なら色々と分析できてとても楽しいので、気が向いたらぜひチャレンジしてみてください。
また、PCの環境などにより上記のとおり行ってもうまくデータベースが出来ない場合もあります。どうかご了承ください。
なお、けものフレンズタグの2月うｐ分データベースを置いておきます。どうぞ参考にしてみてください。（これも、気が向いたら記事にするかも知れません。→ しました）

・けものフレンズタグ2月うｐ分データベースダウンロード
http://yahoo.jp/box/QLiPDO

意外と簡単？月間中央値の調査方法

「このブログ、特定のタグばっか調査してるけど、たまには○○タグも調査して欲しいよ」と思ってる方もおられるのではないでしょうか。
実は、ニコニコ超検索サイトを使えば月間中央値はかなり簡単に求めることが出来るので、例をもってやり方を書いてみようと思います。

例１：「動物」タグ・2017年2月うｐ分の再生数中央値を求めてみる

手順は、
１　月のうｐ数を調べる
２　中央が何位か、そして何ページか把握する
３　該当ページで確認する
・・・となります。

１　月のうｐ数を調べる

ニコニコ超検索で、以下の通り入力します。



① 動物　② タグにチェック　③ 2017/02/01　④ 2017/02/28　⑤ 再生数多い順を選択　これらを入力後に、⑥ 検索　を押します。

結果、月間うｐ数は1,026ということが分かりました。ニコニコ超検索（というかニコニコ運営側の仕組み）での抽出可能の上限は1,600なので、その点は大丈夫そうです。


２　中央が何位か、そして何ページか把握する

まず、中央が何位かはうｐ数を2で割れば簡単に出ます。

1,026位 ÷ 2 ＝ 513位

513位を調べれば、中央値が分かることになります。
そして、ニコニコ超検索は1ページに50件データがあるので・・・

513 ÷ 50 ＝ 10 あまり13

つまり10ページではおさまり切らないので、11ページ目を見れば513位が分かります。


３　該当ページで確認する

あとは該当ページで確認すればOKですが、ここで上記のページのアドレスを見てみると・・・

http://gokulin.info/search/result.php?q=%E5%8B%95%E7%89%A9&targets=tagsExact&date=on&d_min=2017%2F02%2F01&d_max=2017%2F02%2F28&v_min=&v_max=&c_min=&c_max=&m_min=&m_max=&_sort=-viewCounter&page=1

URLの最後の「page=1」をいじれば、すぐに11ページを見れそうな予感！やってみます。



ハイ出ました！513位は再生：194 で、これが「動物」タグ・2017年2月うｐ分の再生数中央値（4/24現在）ということになりそうです。
（なお、この記事のリンク先を表示しても再生数の増加や動画の削除などにより、結果は変わってくるのでご了承ください。）

もし結果が合っているか確かめたい場合、今度は再生数少ない順にして513位を調べればOKです。きっと同じ数字になるハズです。



513位は再生：193！同じ数値になりませんでした＼(^o^)／
これにはちゃんと理由があって、1,026データのど真ん中は513位ではなく、厳密に言えば513位と514位の中間になります。
なので、今回の中央値は 193.5 というのが正解になる訳です。

とまあノイズもありましたが、こんな感じでわりと簡単に月間中央値を出すことが出来ます。もう1例、行ってみましょう。


例２：「けものフレンズ」タグ・2017年2月うｐ分の再生数中央値を求めてみる

１　月のうｐ数を調べる



おっと、今度は1,799件ということでサイト上限の1,600を超えてしまいました。今回は、調査できないのか？
答えは、「余裕で調査できる」になります。



図にしたらこんな感じですが、要するに「全データの抽出は出来ないけど、再生数順の並び替えは出来ているので、中央値の900位が分かればOK」ということです。
さらに言えば、多い順・少ない順の1,600位までが分かることになるので、3,200データまでは中央値の調査が可能ということになります。

あとは、例1の動物と同様に調査するだけです。


２　中央が何位か、そして何ページか把握する

1,799 ÷ 2 ＝ 899.5　→　中央値は900位
900 ÷ 50 ＝ 18　→　18ページを見ればOK


３　該当ページで確認する

・再生数多い順



・再生数少ない順



今度はどちらも同じ数値になりました！再生：2,098が、「けものフレンズ」タグ・2017年2月うｐ分の再生数中央値（4/24現在）ということになります。

いかがでしたでしょうか。もちろん、同じ手順でコメントとマイリストの中央値を求めることも出来ます。
また期間も月間に限らず、3,200件以下ならどんな広い期間の調査も可能です。

問題は、調査対象データが3,200件を超えた場合。
この場合の解決法は・・・

１　例えば月間5,000うｐなら、1～18日など3,200件の範囲で調べる
２　1～15日、16～31日など複数調べ、その中央値同士のさらに中央値を求める(1～15日が 203 、16～31日が 207 なら 205 と言う風に、ただしこれは推計値)
３　いっそ全データを収集してデータベースを作り、それから中央値を調べる（データベースの作り方はこちら）

・・・などになると思います。
ゲームタグみたいな月間うｐ数が50,000を超えるお化けタグは正直お手上げなところがありますが、
それ以外のほとんどのタグはわりと短時間で中央値を調べることが出来るはずなので、もし興味を持たれたら、ぜひ一度試されてみてはいかがでしょうか。
それでニコニコ統計の楽しさを知って頂ければ、統計員として非常に嬉しく思う次第です。

タグ別・月間いろいろ調査 例のアレ 2017年2月うｐ分編

タグ別・月間いろいろ調査、例のアレ 2017年2月うｐ分編です。
今回の概要は、以下のとおりです。

・各中央値の推移

  中央値は 再生数：1,743 コメント：41 マイリスト：7 で、前回の 1,525-33-6 と比べていずれも上昇しました。

・再生数中央値とうｐ数の推移

  うｐ数は 4,496 で、前回 4,693 から -4.2% の減少でした。需給関係から、うｐ数減→各中央値増になったのかも知れません。

・月間最多うｐ日は2/26(日)でした（⇒グラフ3）。でもやっぱりと言うか、イベントも何も特にありませんでした。

・再生数ベストテン、とても例のアレらしくて良いですｗ 何が例のアレらしいかと言うと、再生数1位の動画が、ベストテンの中でマイリストがダントツで少ないのですｗ
  こういう訳の分からないところも、例のアレの魅力の一つだと勝手に思ってます。

その他、各データは以下のとおりです。






・再生数ベストテン

順位	タイトル	うｐ日	長さ	再生数	コメント	マイリスト
1位	けものフレンズ5話 問題のシーン	2017/02/08	0:02:02	689,117	4,289	780
2位	岡山ドバーランド04.Planet Coaster	2017/02/11	0:17:27	564,349	17,567	5,430
3位	ばかものフレンズ	2017/02/15	0:01:43	561,842	3,158	5,042
4位	ケツデカピングー　第４話「ケツデカとアコーディオン」修正版	2017/02/03	0:07:09	439,522	5,196	2,076
5位	迫真宝島部　一転攻勢の裏技.shadowverse	2017/02/04	0:06:49	421,829	2,318	9,323
6位	ケツデカピングー　第５話「ケツデカのつららで音楽」	2017/02/05	0:06:38	395,757	6,452	2,498
7位	ケツデカピングー　第７話「ケツデカピングーとトイレ」	2017/02/19	0:07:52	390,759	9,961	2,477
8位	101人目に乗ったら大丈夫し゛ゃなかった先輩	2017/02/23	0:00:25	355,287	1,796	3,541
9位	BIG HSI	2017/02/08	0:01:12	353,736	1,824	5,083
10位	野獣先輩の声にしか聞こえない曲	2017/02/25	0:01:12	352,505	1,994	2,013

・各一位の動画

再生数	689,117		けものフレンズ5話 問題のシーン
コメント	17,567		岡山ドバーランド04.Planet Coaster
マイリスト	16,685		【第18回MMD杯本選】トイストーリー

［諸データ］



・データベースダウンロード
http://yahoo.jp/box/jmh4Ew