テキストマイニング

香港 国家安全維持法:  5ちゃんねる での 書き込み動向（2020-07-19）では香港国家安全維持法に係る某掲示板の 書き込み分析 を行うためのデータを収集・公開しました。せっかく 5ちゃんねる （以下「某掲示板」）の4週間分のデータを集めた(*)ので、これを MySQL に流し込んで利用する方法を共有します。

いろいろ考えながらデータをいじるときの Pandas の恩恵は計り知れませんが、やることが決まっている状況でデータの集計的な作業を行う場合には、データベース（以下「DB」）を使った方がサクサク進むので、ご参考まで。

(*) 単発プロジェクトなので、以降のデータ収集は予定していません。

全部書いてから気が付いたんですが、以下と同じことは Microsoft Access に CSV のデータを読み込ませてもできるんじゃないかと 😅

MySQL DBの準備

データベース作成

適当な名前で DB を用意してください。
たとえば、foo_db　という名前のDBを作るとして、ターミナルから

mysql> CREATE DATABASE foo_db

とやれば良いのですが、レンタルサーバーの場合は、ウェブブラウザで会員ページからしかDBを作成できない場合あります。その場合はそのページで適当な名前のDBを作ってください。

既に WordPress 用の DBがあるならそこにテーブルを加えても良いのですが、予期せぬ誤操作で大事なDBを壊してしまうと悲惨すぎますから、新しく別のDBを作った方が良いでしょう（君もしくは君のメンバーが既存DBを使おうとして壊しちゃっても例によって当局はいっさい関知しないのでそのつもりで。成功を祈る）。

ここでは、書き込み分析 専用に nplus_db という名前のDBを新たに作りました。

mysql> CREATE DATABASE nplus_db;
Query OK, 1 row affected (0.04 sec)
/* ちゃんと出来たかチェック */
mysql> SHOW DATABASES;
+----------------------+
| Database             |
+----------------------+
| information_schema   |
| nplus_db             |
+----------------------+
2 rows in set (0.31 sec)

/* 上で作ったDBを使う */
mysql> USE nplus_db;
Database changed

テーブル作成

サンプルデータの構造に合わせてテーブルをつくるSQL文は次のとおりです。
テーブルの名前は np_msg_flag としました。
SQLに接続した状態で、SQLのプロンプトに以下(*1)をコピペでOKです。

(*1) CREATE TABLE … PRIMARY KEY (timestamp, id)); を一気に Copy & Paste で行けます。

CREATE TABLE np_msg_flag (
  timestamp TIMESTAMP NOT NULL,
  id VARCHAR(16) NOT NULL,
  name VARCHAR(64),
  thread_id INT NOT NULL,
  kwd000 TINYINT, kwd001 TINYINT, kwd002 TINYINT, kwd003 TINYINT,
  kwd004 TINYINT, kwd005 TINYINT, kwd006 TINYINT, kwd007 TINYINT,
  kwd008 TINYINT, kwd009 TINYINT, kwd010 TINYINT, kwd011 TINYINT,
  kwd012 TINYINT, kwd013 TINYINT, kwd014 TINYINT, kwd015 TINYINT,
  kwd016 TINYINT, kwd017 TINYINT, kwd018 TINYINT, kwd019 TINYINT,
  kwd020 TINYINT, kwd021 TINYINT, kwd022 TINYINT, kwd023 TINYINT,
  kwd024 TINYINT, kwd025 TINYINT, kwd026 TINYINT, kwd027 TINYINT,
  kwd028 TINYINT, kwd029 TINYINT, kwd030 TINYINT, kwd031 TINYINT,
  msglen INT NOT NULL,
  PRIMARY KEY (timestamp, id)
);
Query OK, 0 rows affected (0.02 sec)

/* うまく行ったかな？ */
mysql> SHOW TABLES;
+----------------------+
| Tables_in_nplus_db   |
+----------------------+
| np_msg_flag          |
+----------------------+
1 row in set (0.00 sec)
mysql> DESC np_msg_flag;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| timestamp | timestamp   | NO   | PRI | NULL    |       |
| id        | varchar(16) | NO   | PRI | NULL    |       |
| name      | varchar(64) | YES  |     | NULL    |       |
| thread_id | int(11)     | NO   |     | NULL    |       |
| kwd000    | tinyint(4)  | YES  |     | NULL    |       |
| kwd001    | tinyint(4)  | YES  |     | NULL    |       |
| kwd002    | tinyint(4)  | YES  |     | NULL    |       |
| kwd003    | tinyint(4)  | YES  |     | NULL    |       |
| kwd004    | tinyint(4)  | YES  |     | NULL    |       |
| kwd005    | tinyint(4)  | YES  |     | NULL    |       |
| kwd006    | tinyint(4)  | YES  |     | NULL    |       |
| kwd007    | tinyint(4)  | YES  |     | NULL    |       |
| kwd008    | tinyint(4)  | YES  |     | NULL    |       |
| kwd009    | tinyint(4)  | YES  |     | NULL    |       |
| kwd010    | tinyint(4)  | YES  |     | NULL    |       |
| kwd011    | tinyint(4)  | YES  |     | NULL    |       |
| kwd012    | tinyint(4)  | YES  |     | NULL    |       |
| kwd013    | tinyint(4)  | YES  |     | NULL    |       |
| kwd014    | tinyint(4)  | YES  |     | NULL    |       |
| kwd015    | tinyint(4)  | YES  |     | NULL    |       |
| kwd016    | tinyint(4)  | YES  |     | NULL    |       |
| kwd017    | tinyint(4)  | YES  |     | NULL    |       |
| kwd018    | tinyint(4)  | YES  |     | NULL    |       |
| kwd019    | tinyint(4)  | YES  |     | NULL    |       |
| kwd020    | tinyint(4)  | YES  |     | NULL    |       |
| kwd021    | tinyint(4)  | YES  |     | NULL    |       |
| kwd022    | tinyint(4)  | YES  |     | NULL    |       |
| kwd023    | tinyint(4)  | YES  |     | NULL    |       |
| kwd024    | tinyint(4)  | YES  |     | NULL    |       |
| kwd025    | tinyint(4)  | YES  |     | NULL    |       |
| kwd026    | tinyint(4)  | YES  |     | NULL    |       |
| kwd027    | tinyint(4)  | YES  |     | NULL    |       |
| kwd028    | tinyint(4)  | YES  |     | NULL    |       |
| kwd029    | tinyint(4)  | YES  |     | NULL    |       |
| kwd030    | tinyint(4)  | YES  |     | NULL    |       |
| kwd031    | tinyint(4)  | YES  |     | NULL    |       |
| msglen    | int(11)     | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
37 rows in set (0.00 sec)

データのインポート

CSVファイルに収められた 書き込み分析 用データを MySQL のテーブルにインポートします。SQL文は次のとおりです（*2）。

(*2) これも、LOAD DATA … IGNORE 10 ROWS; を一気に Copy & Paste で行けます。

ソースの CSV ファイル は np_2020W27.csv, np_2020W28.csv, np_2020W29.csv, np_2020W30.csv の4本ですから、この作業を4回繰り返します。

CSV ファイルを置いたディレクトリに自分がいる状態でSQLに接続するのがお奨めです。

そうすればそこが作業ディレクトリ（current directory）になりますから、~/your_path/foo.csvの部分は単純にfoo.csvで良くなるので楽です。

LOAD DATA
LOCAL INFILE '~/your_path/np_2020W27.csv'  /* 以下、28, 29, 30 を load. */
INTO TABLE msg_flagged
CHARACTER SET 'utf8mb4' /* これが何気に大事。忘れるとハマります。 */
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 10 ROWS; /* サンプルデータの先頭10行の能書きを skip する。 */

/* レコード数のチェック */
mysql> SELECT count(*) FROM np_msg_flag;
+----------+
| count(*) |
+----------+
|  3533575 |
+----------+
1 row in set (1.49 sec)

/* 以上で準備完了。お疲れ様でした🍻 */

トラブル・シューティング

😱 予想されるトラブルは、次の2つです。

• Data truncated for column ‘timestamp’ at row #の Warning が出る。
  • これは、元のCSVの TIMESTAMP が timezone 情報を含んでいるためです。MySQL は timestamp データ読み込み時にデータを強制的に UTCに変換した上で DBに保存します。そして、読み出し時にはDBサーバーの timezone 設定に合わせて表示します。このため、元ファイルの +09:00 の部分は trancate されるので、その警告です。警告とは言っても、情報そのものは無傷で保存されるので問題はありません。
• ERROR 1148 (42000): The used command is not allowed with this MySQL versionみたいな感じの Error が出る。
  • 私が使っているレンタルサーバーのMySQL（5.7.29）では Error が出ず気がつかなかったのですが、今回ローカルマシンのMySQL（8.0.21）でテストしたら Error になったので気がつきました。
  • ERROR 1148 (42000)でGoogleるといっぱい出てくるポピュラーなお悩み相談のようで、要はセキュリティー対策強化のため、MySQL のバージョン 8 以降は LOCAL INFILE はデフォルトでは許可しない設定になったということらしいです(*)。

(*) と思ったら、MySQL Workbench だとなんか bug っぽい話もありますね。まあ、あまり悩んでも仕方ない。動いてるからいいや😺
Bug #91891 Workbench 8.0.12 no longer allows LOAD DATA LOCAL INFILE

😃 解決策は次の２点に集約されます。

1. 自分（クライアント）側の設定変更:
   MySQLにログインするときに、–local-infile=1のオプションをつける。
   $ mysql -u [username] -p --local-infile=1
2. DBサーバ側の設定変更:
   MySQLに接続した状態で、以下のコマンドを打ち込む。
   mysql> SET GLOBAL local_infile = 1;
   • ただし、これだと次回ログイン時にデフォルトに戻ってしまうので、設定を固定したいときは、
   • mysql> SET PERSIST local_infile= 1; とすればOKです。

動作テスト

書き込み分析 のテスト用に、32個のフラグを全部 1 にしたラベル付きの書き込みを2件放流しておきました。

これをキャプチャするためにはWHERE kwd000 * kwd001 * … * kwd031 = 1 で捕まえられる理屈ですが、実はそこまでする必要はなくて、
WHERE kwd000 * kwd001 * kwd002 * kwd003 = 1であっさり逮捕されてしまいました😭

1594772979 は某掲示板某板某スレッドのURLの一部ですから、これを探して、書き込み日時2020-07-15 18:33:14 をヒントに検索すると標識をつけて放流した 書き込み をキャッチできます。ただし、

• 某掲示板の日時表示は、yyyy/mm/dd(曜) HH:MM:SS.SS です。
• MySQL の出力は1秒の桁で丸められてしまうので、SSの整数1桁目が異なる場合があります。

SELECT thread_id, timestamp
FROM np_msg_flag
WHERE kwd000 * kwd001 * kwd02 * kwd03 = 1;

+------------+---------------------+
| thread_id  | timestamp           |
+------------+---------------------+
| 1594772979 | 2020-07-15 18:33:14 |
| 1594772979 | 2020-07-19 02:14:16 |
+------------+---------------------+
2 rows in set (3.11 sec)

遊び方

今回は keywords がお仕着せ・固定で恐縮ですが、それなりに楽しく遊んでいただけたら幸いです。例えば…

• ID別の書き込み数、時間、頻度の集計・分析
• メッセージ本文の長さの集計・分析
• Keywordsの共起（1件のメッセージの中でのKeywordsの組み合わせ）

などを見ると意外に面白いことに気がつきます。なにか気になる傾向があったら、動作テストと同じやりかたで元の書き込みを探し、原文を確認すると更に気づくことがあるかもしれません。

で、何か面白いことを発見した場合、それは自分の心の中にしまっておくのが吉（きち）かも。気付きを公表すると、それが観測対象に影響を与えるかもしれないので。

Enjoy!

サンプルデータ置き場

• np_2020W27.csv.zip [13 MB]
• np_2020W28.csv.zip [15 MB]
• np_2020W29.csv.zip [13 MB]
• np_2020W30.csv [14 MB]
• Preview