国会議事録のデータを用いて年代毎に話題になった言葉を表示する

はじめに

「国会議事録のデータ(10GB)をデータベースにいれて、何かしらの検索システムをつくって」という課題があったので、議事録の中から年代毎に話題になった言葉を表示するシステムをつくりました。

こんな感じ↓

作った理由としては、年代毎で話題になった言葉を見やすくすることで、その時の情勢やら歴史を知れるのかな～と思ったからです。

4ヵ月前に作ったので、記憶がおぼろげですが、メモ程度に残しておきます。
めちゃくちゃ焦ってつくったので、いろいろ適当です。

どうやったか

• データベースに格納しろということで、MySQLにデータを挿入
• データベースから発言内容を所得し、形態素解析をおこない名詞のみを抽出する
  • １年毎に名詞を出力し、分かち書きした文字列を作成する
• 10年分単位でTF-IDFを行う。
• TF-IDFの結果によって抽出された年ごとの特徴語をデータベースに格納
• データベースにアクセスするREST APIを作成
• フロント⇔API⇔データベースみたいな構造で作成
  

だいたいのやり方は上のようになるのですが、
ノートパソコンであること、10GBとという割と大容量のデータであることから、
苦戦したことが多々あるので、そういうことをメモ程度に残していこうとおもいます。

特徴語抽出については【特別連載】 さぁ、自然言語処理を始めよう！（第2回： 単純集計によるテキストマイニング）のサイトを参考にしました。

環境

ノートパソコンのローカルの環境で構築しました。

• MySQL5.6(Windows)
• Python3.6(Ubuntu18.04 LTS)
• フロント(Ubuntu18.04 LTS)
  • gulp
  • jQuery

国会議事録のデータはapi経由でとってこれるらしいですが、tsvファイルで成形されたものが配られたのでそれを使いました。

国会議事録のデータをMySQLに格納する

MySQLをインストールする

はじめはUbuntuにMySQLを入れようと試みていたのですが、いろいろと躓きました。
躓いたことに関してはWSLのUbuntu18.04.1LTSにMySQL5.7を入れたお話に書いてあります。　
そのあと、無事ubuntuにMySQLを入れることができたのですが、データを入れてもかなり不安定だったため、Windowsに切り替えました。

【MySQL】Windows 10にMySQLをインストールを参考にwindowsにMySQLをインストールしていきます。

• MySQLがある場所まで移動

>cd ¥mysql¥bin

• MySQLを起動

> net start mysql

• MySQLにログイン

> mysql -u root

国会議事録のデータをデータベースに格納する。

まずはじめにデータベースとテーブルを作成

> create database gijiroku

• 国会議事録のデータをすべて保管するテーブル

 CREATE TABLE gijiroku.origin (
     id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
     date DATE,
     nickname VARCHAR(25),
     fullname VARCHAR(25),
     position VARCHAR(25),
     dialogue LONGTEXT,
     siturl TEXT
 );

• 特徴語を保管するテーブル

 create table gijiroku.wakachi(
 id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
     date YEAR(4),
      wakachi LONGTEXT
     );

次に、originテーブルに議事録のデータをいれていきます。
ここで、はじめはinsertをつかってやろうとしてたのですが、到底終わらないということに気づき…(MySQLに大量のデータを入れるときに最適な方法は？を参考)
国会議事録のデータがtsvファイルで渡されていたことから、LOADを使いました。
また、10GBをいっきに処理するのはノートパソコンでは難しいと思い、データを4分割にして格納していきました。

 LOAD DATA LOCAL INFILE 'C:/tsvfiles/splitted_1.tsv' INTO TABLE `gijiroku`.`origin` FIELDS TERMINATED BY '\t'  LINES TERMINATED BY '\n' (@1,@2,@3,@4,@5,@6) SET date=@1, nickname=@2, fullname=@3, position=@4, dialogue=@5, siturl=@6;

LOAD DATA INFILEステートメントの中でカラムの順番とかをゴニョるを参考にしました。

データをすべて入れた段階で、総レコード数が3000万件ありました。

パーティショニングの設定

このままでは、selectするときにとんでもない時間がかかってしまうので、パーティショニングというものを行います。

• MySQL5.5でパーティショニング使って時系列のデータを分散する
• MySQLパーティショニングの設定、追加、削除、再構成

を参考にしました。

• プライマリキーの設定

> ALTER TABLE origin DROP PRIMARY KEY, ADD PRIMARY KEY(id, date);

• パーティションの設定

ALTER TABLE origin PARTITION BY RANGE(YEAR(date))(
PARTITION p1947 VALUES LESS THAN (1948),
PARTITION p1948 VALUES LESS THAN (1949),
PARTITION p1949 VALUES LESS THAN (1950),
...
PARTITION p2011 VALUES LESS THAN (2012),
PARTITION p2012 VALUES LESS THAN (2013),
PARTITION p2013 VALUES LESS THAN (2014)
);

これで、年代別にselectするときは、早く結果が出力しやすくなりました。

特徴語を抽出する

UbuntuにPipenvをインストール

ここからの言語処理は、【特別連載】 さぁ、自然言語処理を始めよう！（第2回： 単純集計によるテキストマイニング）を参考に、Pythonを使ってやっていきたいと思います。

Python自体まったく書かないため、よくわからないですが、ここ最近でてきたPipenvというPythonパッケージングツールを使うことにしました。
バージョンもすぐ切り替えられたりと、Pythonのめんどくさいところをまるっとやってくれる感じのツールになります。

Python の Pipenv を使ってみましたを参考にインストールしました。

• Pythonコードを個別に実行する

> pipenv run python main.py

形態素解析を行い、名詞を抽出する

Pythonの環境を整えることができたので、これからは形態素解析を行っていきたいと思います。
方法は【特別連載】 さぁ、自然言語処理を始めよう！（第2回： 単純集計によるテキストマイニング）とほぼ変わらないのですが、
データが大容量ということもあり、名詞出力とTf-idfを同時にやるのではなく、別に実行することにしました。
この時に、すべてのデータを扱うのではなく、10年分のデータを所得して、年毎の名詞を抽出し、一つのスペースに区切られた文字列をtsvファイルに出力するようにしました。（なので、出力されたtsvファイルは6つになります。）理由としては、tf-idfの処理を行うときに、処理落ちをさせないようするためです。

また、参考リンク通りにMecabをインストールすることができなかったので、ubuntu 18.04 に mecab をインストールのサイトを参考にインストールを行いました。

そのほかにも、元のコードでは大容量のデータを扱うには厳しいので、

• PythonでのMeCabを速くするtips
• Python高速化 【multiprocessing】【並列処理】

を参考に処理を高速化させました。

最終的にできたコードが以下です。

wakachi.py

# !/usr/bin/env python
# -*- coding:utf-8 -*-
import mysql.connector
import configparser
import MeCab
import numpy as np
from multiprocessing import Pool
import multiprocessing as multi
import pandas as pd


config = configparser.ConfigParser()
# 設定ファイルを読み込み
config.read('config.ini')

# databese
conn = mysql.connector.connect(
    host = config['detabase_server']['host'],
    port = config['detabase_server']['port'],
    user = config['detabase_server']['user'],
    password = config['detabase_server']['password'],
    database = config['detabase_server']['database'],
)

conn.ping(reconnect=True)
cursor = conn.cursor()


### MySQL上のデータ取得用関数
def fetch_target_day_n_random(target_day, n = 2000):
    sql = 'select dialogue from origin partition (p%s) where date_format(date, "%Y") = "%s";'
    cursor.execute(sql, [target_day,target_day])
    result = cursor.fetchall()
    l = [x[0] for x in result]
    return l
 
### MeCab による単語への分割関数 (名詞のみ残す)
tagger = MeCab.Tagger()
def split_text_only_noun(text):
 
    words = []
    for chunk in tagger.parse(text).splitlines()[:-1]:
        (surface, feature) = chunk.split('\t')
        if feature.startswith('名詞'):
            # print(surface)
            words.append(surface)
    return " ".join(words)
### メイン処理
docs_count = 20 # 取得数
# 処理する年代を書き込む
target_days = [
    2000,
    2001,
    2002,
    2003,
    2004,
    2005,
    2006,
    2007,
    2008,
    2009,
    2010,
    2011,
    2012,
    2013
]

data_frame = pd.DataFrame(index=[], columns=['date', 'wakachi'])
for target_day in target_days:
    # MySQL からのデータ取得
    txts = fetch_target_day_n_random(target_day, docs_count)
    p = Pool(multi.cpu_count())
    each_nouns=p.map(split_text_only_noun, txts)
    p.close()

    all_nouns = " ".join(each_nouns)
    series = pd.Series([target_day, all_nouns], index=data_frame.columns)
    data_frame = data_frame.append(series, ignore_index = True)

data_frame.to_csv("wakachi6.csv", index=False,header=True)#書き出し

target_daysは処理が終わるごとに、手作業で変えました…

TF-IDFを行う。

TF-IDFは参考資料とほぼ同様な処理の仕方ですが、MySQLにinsertする方法ではなく、一度、年代ごとの特徴語をtsvファイルに書き出してから、LOADする方法を行いました。

tfIdf.py

import numpy as np
from sklearn.feature_extraction.text import TfidfVectorizer
import pandas as pd

### TF-IDF の結果からi 番目のドキュメントの特徴的な上位 n 語を取り出す
def extract_feature_words(terms, tfidfs, i, n):
    tfidf_array = tfidfs[i]
    top_n_idx = tfidf_array.argsort()[-n:][::-1]
    words = [terms[idx] for idx in top_n_idx]
    return words
### 空白を境目にしてる
def extract_feature_words_string(terms, tfidfs, i, n):
    tfidf_array = tfidfs[i]
    top_n_idx = tfidf_array.argsort()[-n:][::-1]
    words = [terms[idx] for idx in top_n_idx]
    words_string = " ".join(words)
    return words_string

# 書き直す
filename = pd.read_csv('wakachi6.csv')

ldate =  filename['date']
lwakachi =  filename['wakachi']

# 追加
tokuchou = []

tfidf_vectorizer = TfidfVectorizer(
    use_idf=True,
    lowercase=False,
    max_df=6,
    sublinear_tf=True
)
tfidf_matrix = tfidf_vectorizer.fit_transform(lwakachi)

# index 順の単語のリスト
terms = tfidf_vectorizer.get_feature_names()
# TF-IDF 行列 (numpy の ndarray 形式)
tfidfs = tfidf_matrix.toarray()

data_frame = pd.DataFrame(index=[], columns=['date_year', 'tokuchou'])

for i in range(0, len(ldate)):
    stokens=extract_feature_words_string(terms, tfidfs, i, 20)

    series = pd.Series([ldate[i], stokens], index=data_frame.columns)
    data_frame = data_frame.append(series, ignore_index = True)

# csv書き出し
data_frame.to_csv("tokuchou.csv", index=False, mode='a', header=False)#追記
# data_frame.to_csv("tokuchou.csv", index=False,header=True)#書き出し

filename = pd.read_csv('wakachi6.csv')のところを形態素解析の処理で出力した6つのtsvファイルに手作業で書き換えて、すべてのデータに対しての特徴語をtokuchou.csvに書き出しました。

そしてLOADを用いて、tokuchouテーブルにデータを入れていきます。

LOAD DATA LOCAL INFILE 'C:/tokuchou.csv' INTO TABLE `gijiroku`.`tokuchou` FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n' IGNORE 1 LINES (@1,@2) SET date=@1, tokuchou=@2;

これでデータベース側は完成しました

Flaskを使ってデータベースにアクセスするAPIをつくる

これまた使ったことがないフレームワークでしたが、FlaskはWebアプリつくるなら扱いやすい！という噂を聞いていたので、つかってみました。

調べてみると、APIも一瞬で作れそうな感じです(【Flask】5分で作るめちゃくちゃ簡単なAPI【Python初心者】)

クロスドメインの問題で少し躓きましたが、FlaskでRESTfulAPIを作ってみたを参考にして、めちゃくちゃ適当ですが、できました。

main.py

# !/usr/bin/env python
# coding: utf-8

from flask import Flask, request, jsonify
from flask_restful import Resource, Api
from flask_restful.utils import cors
# mysql
import mysql.connector
import configparser

# 初期設定
app = Flask(__name__)
api = Api(app)
app.config['JSON_AS_ASCII'] = False #日本語文字化け対策
app.config["JSON_SORT_KEYS"] = False #ソートをそのまま
config = configparser.ConfigParser()
# 設定ファイルを読み込み
config.read('config.ini')

class Year(Resource):
    # GET時の挙動の設定
    @cors.crossdomain(origin='*')
    def get(self):
        conn = mysql.connector.connect(
            host = config['detabase_server']['host'],
            port = config['detabase_server']['port'],
            user = config['detabase_server']['user'],
            password = config['detabase_server']['password'],
            database = config['detabase_server']['database'],
        )
        connected = conn.is_connected()
        if (not connected):
            return make_response(jsonify({"DB ERROR": 'could not connect to db'}))
        conn.ping(reconnect=True)
        cur = conn.cursor()
        
        params = request.args
        if(params.get('year') is not None):
            year = params.get('year')
            cur.execute('select tokuchou from tokuchou where date = %s',[year])
            table = cur.fetchall()
            print(table)
            table=table[0][0].split(' ')

            result = { 
                "year":year,
                "tokuchou": table
            }
            send_msg = jsonify(result)

            return send_msg

class YearWord(Resource):
    # GET時の挙動の設定
    @cors.crossdomain(origin='*')
    def get(self,year):
        conn = mysql.connector.connect(
            host = config['detabase_server']['host'],
            port = config['detabase_server']['port'],
            user = config['detabase_server']['user'],
            password = config['detabase_server']['password'],
            database = config['detabase_server']['database'],
        )
        connected = conn.is_connected()
        if (not connected):
            return make_response(jsonify({"DB ERROR": 'could not connect to db'}))
        conn.ping(reconnect=True)
        cur = conn.cursor()        
        params = request.args
        word = params.get('word')
        word = '%'+word+'%'
        cur.execute('select * from origin partition(p%s) where dialogue like %s;',[year,word])
        table = cur.fetchall()

        result = { 
            "year":year,
            "word":word,
            "result": table
        }
        send_msg = jsonify(result)

        return send_msg


api.add_resource(Year, '/year')
api.add_resource(YearWord, '/yearWord/<int:year>')

if __name__ == '__main__':
    app.run()

APIはこれで完成しました。
JS側は、jQueryのajaxを利用してAPIにリクエストを投げて...というお決まりの方法でデータを所得しています。

最後に

大容量のデータを扱うのは初めてだったので、手探りで作ってた状態でした。(しかもあんまり時間がなかった…)
もっと効率化できた部分はあったと思います…

それと、もうWSLを使うのはやめようと思いました(笑)