SELECT `device_id`,count(if(`gender_id`=0,1,null)) as count0,count(if(`gender_id`=1,1,null)) as count1 FROM `tables` group by `device_id` order by `device_id`

↓

$this->Table->virtualFields['count0'] = 0;
$this->Table->virtualFields['count1'] = 0;
$tables = $this->Table->find('all', array(
　　'fields'=> array('Table.device_id', 'count(if(gender_id=0,1,null)) as Table__count0', 'count(if(gender_id=1,1,null)) as Table__count1'), // エイリアスにする
　　'group' => array('Table.device_id'), // GROUP BYのフィールド
　　 'order' => array('Table.device_id'),
　　'recursive' => -1, // int
));

なぜ、virtualFieldsを利用するかというと使用しないとTableのdevice_idと同じ配列に入れないためです。
集計をするためには同じ配列に入っていたほうが良いので、virtualFieldsを利用しました。

参考資料

CakePHPで GROUP BY や SUM,COUNT とかを取る場合のバーチャルフィールド(virtual fields)利用
 Virtual fields

2019-12-20

sqlのcount使う方法と注意点

mysql study

sqlでcount()を使用するときに、罠にはまってしまったので、書いておきます。

[ポイント]
複数の条件でのcount()の使い方
　→if文を利用する。条件に合致しなかったときはnullにする必要がある

たとえば、以下のようなテーブル(tables)があるとします。
f:id:harucharuru:20191220144829p:plain
f:id:harucharuru:20191220150643p:plain

一つの要素でのカウント

device_idごとの数を数える場合は、次のようなSQLを実行します。
　カウントした値を入れる要素はcountとする(count(`device_id`) as count )
　device_id順に並べる(order by `device_id`)
これは問題なくできました。

SELECT `device_id`,count(`device_id`) as count FROM `tables` group by `device_id` order by `device_id`

f:id:harucharuru:20191220145059p:plain

複数要素でのカウント

では、device_idごとにgender_idがそれぞれ0と1のものの値をカウントしてみましょう。
そこで以下のように実行してみると、、、

SELECT `device_id`,count(`gender_id`=0) as count0,count(`gender_id`=1) as count1 FROM `tables` group by `device_id` order by `device_id`

f:id:harucharuru:20191220145632p:plain
count(`gender_id`=0) as count0,count(`gender_id`=1) as count1としているのに、結果が同じになってしまいました。

どうやらif文を使う必要があるようなので、使ってみます。

SELECT `device_id`,count(if(`gender_id`=0,1,0)) as count0,count(if(`gender_id`=1,1,0)) as count1 FROM `tables` group by `device_id` order by `device_id`

f:id:harucharuru:20191220145632p:plain
それでも同じ結果になってしまいました。どうやら、if文で条件文に合致しなくても0としてしまうと、数としてカウントしてしまうみたいです。
そこで、0ではなくnullにしてみます。

SELECT `device_id`,count(if(`gender_id`=0,1,null)) as count0,count(if(`gender_id`=1,1,null)) as count1 FROM `tables` group by `device_id` order by `device_id`

f:id:harucharuru:20191220150746p:plain
できました！よかった！！！！

2019-12-15

pythonでmySQLに接続する

study python mysql

備忘録です。自分のメモです。

mySQL接続

#database接続
import mysql.connector
dbh = mysql.connector.connect(
        host='ホスト名',
        port='3306',
        db='データベース名',
        user='ユーザ名',
        password='パスワード',
        charset='utf8'
)
cur = dbh.cursor()

データ取得：select文

#data取得
cur.execute("SELECT `id`,`comment` FROM `テーブル名`")
for row in cur.fetchall():
    print(row[0])

cur.executeの後にselect文を書きます。データは cur.fetchall()に入っているので、一つ一つ取り出す場合はfor文を用います。
一番初めの要素(今回でいうとid)を表示する場合は、row[0]。

データアップデート：update文

    sql = "UPDATE `テーブル名` SET `要素名`=%s,`要素名`=%s,`要素名`=%s WHERE `id`=%s"
    val = (labelnum, result[1][0], str(modified), int(row[0]))
    try :
        cur.execute(sql, val)
    except :
        print('Duplicate entry for key PRIMARY')
    dbh.commit()

2019-12-15

fastTextで文章分類(python)

study textmining python

文章を分類したくなったので、fastTextを用いて分類することにしました。

[ポイント]
・pythonでfastTextを利用
・テキスト分類

データ

今回は元々収集していたtweetデータを用いて分類していきます。今回私は、3つのカテゴリに分割しました。
ラベル1:人が呟いたもの
ラベル2:人が呟いたけれど何かのアプリケーション、botを介している？
ラベル3:企業が宣伝でtweet

以下のようなcsvファイルを作成します。今回私が学習に利用するデータは全部で200件です。ファイル名はtrain_data.csvとします。
f:id:harucharuru:20191215200922p:plain

形式は以下のような感じです。
ラベル(__label__*),tweetの文章　　　　 *には数字が入ります。

__label__1,江戸時代の五街道における宿場で東海道五十三次の中間点となる27番目の宿場は袋井宿ですが中山道六十九次の中間点となる35番目の宿場はどこでしょう？
__label__2,"湖西.浜松.磐田.袋井.森,掛川,菊川,御前崎,牧之原,吉田,川根,島田,藤枝,焼津,静岡,富士,富士宮,沼津,三島,清水,長泉,裾野,御殿場,小山,伊豆,伊豆の国,熱海,伊東,函南,河津,西伊豆,南伊豆,松崎,下田... #静岡"
__label__3,駐車場並列2台付！オール洋室に変更済み！ 袋井市　堀越のアパート 家賃42000円 https://t.co/QR5L8fcAih #fukuroi #袋井 の #賃貸 物件 https://t.co/nr9HoXqsTW https://t.co/DMUrBFoOtx

環境設定

fastTextのインストール

git clone https://github.com/facebookresearch/fastText.git

f:id:harucharuru:20191215201413p:plain

cd fastText
make

f:id:harucharuru:20191215201539p:plain

pip install cython
pip install fasttext

f:id:harucharuru:20191215201501p:plain

MeCabをインストール

私はすでに入っていたので、いれていませんが入れる場合は以下のコマンドを実行

pip install mecab-python3

MeCab確認

MeCabがきちんと動くかどうか確認します。ちゃんと分かち書き(単語と単語の間にスペースを入れる作業)ができていることが確認できました。

#MeCab確認(python)
import sys
import MeCab
mecab = MeCab.Tagger ("-Owakati")
text = mecab.parse ("すもももももももものうち ")
print(text)

f:id:harucharuru:20191215201819p:plain

文章分類プログラム(python)

学習用データ作成

学習用データの文章を分かち書きしていきます。流れはcsv読み込み→分かち書き→txt保存です。
csvデータを一気に読み込み、一文ずつ分かち書きを行い保存しています。

#csv読み込み→分かち書き→txt保存
import sys
import MeCab
import csv

#csv読み込み
csv_file = open("train_data.csv", "r", errors="", newline="" )
csv_data = csv.reader(csv_file, delimiter=",", doublequote=True, lineterminator="\r\n", quotechar='"', skipinitialspace=True)


mecab = MeCab.Tagger ("-Owakati") #分かち書き設定
list_row = [] #分かち書き結果を保存
path_w = 'train_data.txt' #分かち書き済み学習用データ保存ファイル名


for row in csv_data:
    text = mecab.parse (row[1])
    list_row.append((row[0]+'  '+text).rstrip("\n"))
    
f = open(path_w, 'w')
for x in list_row:
    f.write(str(x) + "\n")
f.close()

すると以下のようなtxtファイルが出力されます。わかりにくいですが、きちんと分かち書きされています。
f:id:harucharuru:20191215202321p:plain

__label__1  江戸 時代 の 五 街道 における 宿場 で 東海道 五 十 三 次 の 中間 点 と なる 27 番目 の 宿場 は 袋井 宿 です が 中山道 六 十 九 次 の 中間 点 と なる 35 番目 の 宿場 は どこ でしょ う ？ 
__label__2  湖西 . 浜松 . 磐田 . 袋井 . 森 , 掛川 , 菊川 , 御前崎 , 牧之原 , 吉田 , 川根 , 島田 , 藤枝 , 焼津 , 静岡 , 富士 , 富士宮 , 沼津 , 三 島 , 清水 , 長泉 , 裾野 , 御殿場 , 小山 , 伊豆 , 伊豆 の 国 , 熱海 , 伊東 , 函南 , 河津 , 西伊豆 , 南伊豆 , 松崎 , 下田 ... # 静岡 
__label__3  駐車 場 並列 2 台 付 ！ オール 洋室 に 変更 済み ！ 袋井 市 　 堀越 の アパート 家賃 42000 円 https :// t . co / QR 5 L 8 fcAih # fukuroi # 袋井 の # 賃貸 物件 https :// t . co / nr 9 HoXqsTW https :// t . co / DMUrBFoOtx

学習

fastTextを用いて学習していきます。学習するには以下のコードを実行するだけです。学習回数(epoch, loss...)などは自分で最適だと思う値にしてください。
すると、model.binと言う名前のモデルが作成されます。

#fastTextで学習
import fasttext as ft

model = ft.train_supervised("train_data.txt", label_prefix='__label__',epoch=1000, loss="hs")
model.save_model("model.bin")

f:id:harucharuru:20191215212134p:plain

分類

分類するには以下のコードを実行するだけ！

model.predict('*******ここに文章を入れます***** ')

例えば
f:id:harucharuru:20191215202757p:plain

もし、ラベル名と精度を出力したければ、以下のようにすれば良いです。

result = model.predict('フッピー 可愛い')
print(result[0][0] + ':' + str(result[1][0]))

この時、result[0][0] にラベル、result[1][0]に精度が入っています。
f:id:harucharuru:20191215202912p:plain

参考資料

New release of python module · fastText

このブログを見てやろうと思った
機械学習で大量のテキストをカテゴリ別に分類してみよう！ - WonderPlanet Tech Blog

そのほか
pythonでfasttextを使って文書分類してみた - Qiita
fasttextを用いた言語判定 - Out-of-the-box
fastextでテキスト分類したい - Qiita

csv読み込み書き込み
pythonでのcsvファイルの読み込み - Qiita
Pythonでファイルの読み込み、書き込み（作成・追記） | note.nkmk.me
pythonでlistをファイルに保存し、読み込む方法(numpyも同様！) - 技術メモ集

2019-12-15

MySQLのtextで改行をなくす

study mysql

twitterデータをapiを用いてデータベースに保存してるのですが、改行が含まれているデータがあり分析するのに厄介でした。
そこで、MySQLでの改行データの改行をなくす方法を示します。

[ポイント]
MySQLでの改行データの改行をなくす

現在のデータは以下のような形です。
idはint型、commentはtext型になっています。commentにtweetが入っています。
f:id:harucharuru:20191215140459p:plain

commentの中を見てみると、改行が含まれています。これを削除していきます。
f:id:harucharuru:20191215140649p:plain

commentの削除するには、replaceを利用していきます。

SELECT `id`, replace(replace(replace(`comment`, '\r\n', ''), '\r', ''), '\n', '') AS `comment` FROM `テーブル名`

すると、改行をなくすことができました。
f:id:harucharuru:20191215141015p:plain

参考

[SQL]レコードから改行を除去する｜ Developers.IO

2019-12-13

ちょっとだけテキストマイニング-形態素解析

textmining study python

例えばチャットbotを作るとき、どの単語をどの文章ラベルとして登録すればいいのか迷ったりしませんか？
最近はなんとなくで教師データを入れておけば、自動的に学習してくれますが、一度自分で分析しておくことで、なんとなく関係性を確認することができると思います。
ということで、簡単な文章分析(自然言語処理)を行なっていく方法を書いておきます。

[ポイント]
Pythonで形態素解析を行う

使用するデータ

今回使用するデータは、HPに落ちているよくある質問やQ&Aの文章を使用していきます。
(私はよくあるご質問／袋井市ホームページを参考に作成)
以下のようなcsvデータを作成します。名前はfaq.csvとします。
f:id:harucharuru:20191208163507p:plain

Pythonでプログラム

今回は、Pythonの勉強も兼ねて作成を行なっています。
プログラムは以下の通りです。後で詳しく説明します。

import csv
import pprint
import pandas as pd

#使用データ
with open('faq.csv') as f:
    reader = csv.reader(f)
    list_data = [row for row in reader]
print(list_data)

csv_input = pd.read_csv(filepath_or_buffer="大学のFAQについて2.csv", sep=",")
print(csv_input[['質問内容','ラベル']]) 

#Janomeのインストール
#!pip install janome

from janome.tokenizer import Tokenizer
import collections
t = Tokenizer()

#形態素解析
for index , csv_input in list_data:
    print(index)
    for token in t.tokenize(index):
        print(token)
    print('-----------------------------------------')

import collections
#単語語数のみ拾う
for index, csv_input  in list_data:
    print(csv_input+' : '+ index)
    c = collections.Counter(t.tokenize(index, wakati=True))
    print(c)
    print([token.surface for token in t.tokenize(index)
       if token.part_of_speech.startswith('名詞') or token.part_of_speech.startswith('動詞')])
    print('-----------------------------------------')

import collections

#分析するラベル
label = '介護'
labeltoken = list()
for index, csv_input  in list_data:
    if csv_input == label:
        print(csv_input+' : '+ index)
        for token in t.tokenize(index):
            if token.part_of_speech.startswith('名詞') or token.part_of_speech.startswith('動詞'):
                print(token.surface)
                labeltoken.append(token.surface)
        print('-----------------------------------------')

print(labeltoken)
from collections import Counter
c = Counter(labeltoken)
print(c)

#!pip install japanize-matplotlib
import numpy as np
import matplotlib.pyplot as plt
import japanize_matplotlib

plt.figure(figsize=(36, 15), dpi=100)
left = np.array(list(c.keys()))
height = np.array(list(c.values()))
plt.bar(left, height)

データの取り込み

csvデータを取り込みます。プログラムでは2種類の取り込み方法を書いていますが、分析に使用しているのは下記のものです。
f:id:harucharuru:20191213140749p:plain

形態素解析のツールインポート

今回は、Janomeというアプリケーションを使用して形態素解析します。
インストールをしていない場合はインストールします。
f:id:harucharuru:20191213140945p:plain
mocobeta.github.io

形態素解析

まずは一文だけ形態素解析をしました。その後、含まれている単語数をカウントして表示しています。
f:id:harucharuru:20191213141107p:plain

これを応用していくことで、ラベルごとの分析ができます。*ごめんなさい。説明がめんどくさくなってしまいました…
今回は名詞と動詞のみを抽出してグラフを書いています。出力すると以下のような棒グラフが出てきます。
f:id:harucharuru:20191213150744p:plain

簡単に分析する方法としておすすめ

簡単にテキストマイニングしてくれるサイトがあります。今回は複数種類のデータを分析する方法について書きましたが、1種類のみであれば以下のサイトがおすすめです。(プログラムも書かなくていい！)
textmining.userlocal.jp
今回使用したデータの"ごみ・リサイクル"というラベルのデータを分析すると以下のようになりました。
AIテキストマイニング by ユーザーローカル
ワードクラウド(出現しやすい単語を表示)以外にも共起ネットワークを表示してくれるので、単語ごとの関係性がわかりやすいです。
f:id:harucharuru:20191208164757p:plain
f:id:harucharuru:20191208164817p:plain