毎日テキストマイニング

180日間、毎日テキストマイニングをするブログ

2018/7/7【15日目】辞書型に変換して並び替えを行う

昨日からの分析を続けていきます。 ちなみに昨日の段階での重要そうな単語ランキングトップ10はこちらです。

'co'
'https'
'express'
'お願い'
'今日'
'原宿'
'最後'
'brz'
'buenísimo'
'ch'

ひとまずデータ量を増やすために、SQLを叩きます。2018年6月7日から7月7日までの30日分のtweetデータを得られました。ちなみに、この期間内のデータ数は1411件でした。

https://を取り除く前処理

昨日の段階で、'co’、’https’が段違いに重要になってしまっていますので、前処置としてhttps : //から始まってcoで終わるものは消しておきましょう。 正規表現で書くとこんな感じになるそうです。

(https?)(:\/\/[-_.!~*\'()a-zA-Z0-9;\/?:\@&=+\$,%#]+)

Python正規表現を使う場合、re.sub関数を使うので、reをimportしておきます。

そして、MeCabで単語にバラす前に処理しておきます。コードはこんな感じになりました。

import re

for content in os.listdir('test_dir'):
    with open('test_dir/' + content, 'r') as read_file, open('henkan/' + content, 'w') as write_file:
        for line in read_file:
            reline = re.sub('(https?)(:\/\/[-_.!~*\'()a-zA-Z0-9;\/?:\@&=+\$,%#]+)', '', line)
            tagger = MeCab.Tagger()
            tagger.parse('')
            node = tagger.parseToNode(reline)
            while node:
                if node.feature.startswith('名詞'):
                    write_file.write(node.surface + '\n')
                node = node.next

あと、気になりましたのが、出力をスコアが高い順に変更したいです。今のままですと、どう頑張っても並び替えをさせる方法が思いつかないので、単語とスコアを使って辞書型に変換したいとおもいます。 辞書型に変換するにはdic関数を使えばいいようです。ただ、今回の場合、まずはzip関数でリストをタプルに変換する必要があるようですので、まずはzip関数から実行します。Value側の数字はインクリメントで増やしていきます。

i += 0
dic = dict(zip(terms, X_train_tf[i].toarray()[0]))
print(dic)

実行結果

{'10': 0.0, '12': 0.0, '16': 0.0, '17': 0.0, '18': 0.0, '1994': 0.0, '20': 0.037796447300922721, '2018': 0.037796447300922721, '21': 0.0, '215': 0.037796447300922721, '22': 0.0, '23': 0.0, '26': 0.0, '30': 0.037796447300922721, '35': 0.0, '48': 0.0, '50': 0.0, '755': 0.0, 'akb': 0.0, 'akbingo': 0.0, 'akbshow': 0.0, 'ameba’:(中略)

あとはこれを並び替えすればいいのですが、辞書型の並び替えにはOrderedDictを使うとのこと。コードはほとんど公式のままです。このままだと0が最初にきてしまいますので、reverseを付けて大きい数字から並ぶようにします。

list.sort() と sorted() の両方とも reverse パラメータを 真偽値として受け付けます。このパラメータは降順ソートを行うかどうかの フラグとして利用されます。 例えば、学生のデータを age の逆順で得たい場合:

from collections import OrderedDict
ranking = OrderedDict(sorted(d.items(), key=lambda x:x[1], reverse=True))
print(ranking)

実行結果

OrderedDict([('公演', 0.30237157840738177), ('ちゃん', 0.22677868380553634), ('今日', 0.22677868380553634), ('日本', 0.22677868380553634), ('さん', 0.18898223650461363), ('たくさん', 0.18898223650461363), ('サッカー', 0.18898223650461363), ('皆さん', 0.18898223650461363), 

これでランキング形式ができました。午後こそは分析を始めたいと思います。

2018/7/6【14日目】AKBの呟やきをtf-idfで分析していく

Scikit-learnの使い方も大方わかり始めたので、AKBメンバーの呟きに適用していきたいと思います。

ファイルの読み込み

tf-idfで分析をするのに、複数のドキュメントが必要なので、Pythonで複数のファイルを読み込めるようにしたいと思います。

Pythonでファイルの読込み→別のファイルに書き出し、の動作を行うにはosモジュールを使えば良さそうなので今回はosモジュールを調べてみました。

osモジュールの基本的な使い方

まずはimportから。

import os

importができたら、Pythonの公式ドキュメントから拾ってきた適当な関数を実行してみます。

test = os.name
print(test)

実行結果

posix

posixがよくわからないですが、実行できました。一応、Posixとは次のようなものだそうです。

POSIX(ポシックス、ポジックス、英: Portable operating system interface)は、各種UNIXを始めとする異なるオペレーティングシステム (OS) 実装に共通のアプリケーションプログラミングインタフェース (API) を定め、移植性の高いアプリケーションソフトウェアの開発を容易にすることを目的としてIEEEが策定したAPI規格である。

続いて、ディレクトリを作ってみます。

os.mkdir('test_dir')

簡単ですね。

ファイルの読み込み

os.listdir(path=‘.’)を使えばできそうです。

path で指定されたディレクトリ内のエントリ名が入ったリストを返します。リスト内の順番は不定です。特殊エントリ '.' および '..' は、それらがディレクトリ内に存在してもリストには含められません。

さっき作ったtest_dirフォルダーにAKBの呟きを3日分入れておきましたので、 os.listdir('test_dir’)で中身をみてみます。

os.listdir('test_dir')
['20180704.csv', '20180702.csv', '20180703.csv']

リスト形式でできたので、これをfor文で順に読み込んでいいような気がします。 readとwriteを同時に行うのは,で区切って並列に書けばいいので、こんなコードになりました。csvファイルを読み込んだら、MeCabtagger.parse(‘’)を使って単語ごとに分解していきます。

for content in os.listdir('test'):
    with open('test/' + content, 'r') as read_file, open('henkan/' + content, 'w') as write_file:
        for line in read_file:
            tagger = MeCab.Tagger()
            tagger.parse('')
            node = tagger.parseToNode(line)
            while node:
                if node.feature.startswith('名詞'):
                    write_file.write(node.surface + '\n')
                node = node.next

実行結果のcsvファイル

tweet
"
今日
#
山根
涼
羽
カフェ
買い物
☕??
大切
同期
時間

全てのcsvファイルが1個1個ちゃんと単語に分解されています。これで、ドキュメント数と単語を確保できたはずです。 これを昨日勉強したscikit-learnのチュートリアルに沿ってデータを入力していきます。まずはimportからやっていきます。

from sklearn.feature_extraction.text import CountVectorizer
count_vectorizer = CountVectorizer(input='filename')

CountVectorizer関数に渡した引数は生のテキストを詠みこむのに必要だそう です。

’filename'の場合、fitの引数として渡されるシーケンスは、解析するための生のコンテンツを取得するために読み込みが必要なファイル名のリストになると予想されます。

count_vectorizer.fit_transformにデータを入れるためのファイル名が欲しいので、1行1行分解したcsvのファイル名を取得するためにfor文で回して用意します。その変数をcount_vectorizerに引数として渡します。

files = ['henkan/' + content for content in os.listdir('test')]
X_train_counts = count_vectorizer.fit_transform(files)
X_train_counts.shape

実行結果

(4, 552)

お、数は少ないですが、チュートリアル通りに返ってきてますね。 これをTfidfTransformerに入れてみます。

from sklearn.feature_extraction.text import TfidfTransformer

tf_transformer = TfidfTransformer(use_idf=False).fit(X_train_counts)
X_train_tf = tf_transformer.transform(X_train_counts)
print(X_train_tf)

実行結果

  (0, 469)   0.0816496580928
  (0, 229)    0.0816496580928
  (0, 228)    0.0816496580928
  (0, 311)    0.0816496580928
  (0, 519)    0.0816496580928
  (0, 94) 0.0816496580928
  (0, 26) 0.0816496580928
  (0, 42) 0.0816496580928
  (0, 471)    0.0816496580928
  (0, 468)    0.0816496580928
  (0, 445)    0.0816496580928
  (0, 464)    0.163299316186

どうやらtf-idf分析ができたらしいです。 試しにぱっと見でスコアが高いものを見てみます。 数値化した単語を戻すにはcount_vectorizer.get_feature_names()を使えばいいそうです。それを[]で番号を指定してあげれば単語が戻ってきます。

terms = count_vectorizer.get_feature_names()
terms[464] #(0, 68)   0.163299316186
'最後'
terms[68]     #(0, 68)   0.163299316186
'express'
terms[20]     #(3, 20)   0.707106781187
’34’

うーん、”最後”が重要そうなのはわかりますが、'express’、’34’が重要というのは明らかに間違いぽいですね。明日はもう少し精度を上げて、AKBの呟きを分析したいと思います。

今日の結果

今日の呟きは40件でした。呟かれた言葉はこんな感じです。

'いい': 4, '嬉しい': 3, '楽しい': 2, 'すごい': 2, '可愛い': 2, '面白い': 2, '良い': 2, 'かっこいい': 1, 'かわいい': 1, 'おもしろい': 1, 'たのしい': 1, '切ない': 1, '寂しい': 1, 'ない': 1, '優しい': 1, 'いたい': 1, '恥ずかしい': 1, 'うれしい': 1, '多い': 1, '新しい': 1})

'公演': 16, 'チーム': 13, '初日': 10, 'さん': 10, '込山': 9, 'こと': 7, '今日': 6, '配信': 5, 'よう': 5, 'いい': 4, 'の': 4, '時': 4, '無事': 4, '嬉しい': 3, 'ユニット': 3, '全部': 3, '前': 3, '方': 3, '私': 3, 'たくさん': 3, '怜': 3, 'ちゃん': 3, 'ゴリラ': 3, 'ん': 3, '一緒': 3,

'する': 29, '公演': 16, 'チーム': 13, '初日': 10, 'さん': 10, '込山': 9, 'てる': 9, '見る': 9, 'こと': 7, '今日': 6, 'くださる': 6, 'いただく': 6, '配信': 5, 'よう': 5, '終わる': 5, 'やる': 5, 'せる': 5, 'なる': 5,

今日勉強したこと

  • osモジュールの基本的な使い方
  • readとwriteを同時に行う方法

2018/7/3【13日目】Scikit-learnのチュートリアルを行う。

Tf-idf分析を実装していきたいとおもいます。 0から実装していく力はないので、機械学習ライブラリのScikit-learnを使用していきたいと思います。とはいえ、自分はScikit-learnに触るのは初めてなので、まずは公式のチュートリアルを行いたいと思います。かなりざっくりしてます。

Scikit-learnとは

Scikit-learnはPythonの有名な機械学習ライブラリです。自分でアルゴリズムを実装しなくても、Scikit-learnに投げれば機械学習をしてくれます。今回は下記のチュートリアルをやっていきます。

Working With Text Data Working With Text Data — scikit-learn 0.19.1 documentation

チュートリアルの中では20種類のドキュメントを読み込んで、下記のことを学ぶらしいです。 ファイルのロード 特徴ベクトルを見つける 分類するためのモデルを見つける 特徴と分類の有効な状態を見つける

Scikit-learnのインストール

自分の場合はアナコンダPythonを入れてたはずなので、下記のコマンド。

conda install scikit-learn

それ以外の人はpipでインストールしてください。

pip install -U scikit-learn

インストールできたら、さすがにターミナルでコードを書いていくのはきついので、Jupyter Notebookに移動。

$ jupyter notebook

データセットのロード

Jupyter Notebookに移動したら、今回のチュートリアルで使用するデータセットをロードします。

from sklearn.datasets import fetch_20newsgroups
categories = ['alt.atheism', 'soc.religion.christian', 'comp.graphics', 'sci.med']
twenty_train = fetch_20newsgroups(subset='train', categories=categories, shuffle=True, random_state=42)

f:id:rimt:20180706002528p:plain

これで、データセットがロードできます。categoriesでロードするカテゴリーを限定しているらしいです。 いつロードが終わっているのかタイミングがよくわかりませんが、ロードが完了したかどうかを下記のコマンドで確認してみます。

twenty_train.target_names
['alt.atheism', 'comp.graphics', 'sci.med', 'soc.religion.christian']

ちゃんと結果が返ってきました。

データセットの中身を見ていく

無事にロードできましたので、データセットの中身を見ていきます。

print("\n".join(twenty_train.data[0].split("\n")[:10]))

0番目にあるデータを10行目まで表示しろというコードです。 実行結果はこんな感じです。

From: sd345@city.ac.uk (Michael Collier)
Subject: Converting images to HP LaserJet III?
Nntp-Posting-Host: hampton
Organization: The City University
Lines: 14

Does anyone know of a good way (standard PC application/PD utility) to
convert tif/img/tga files into LaserJet III format.  We would also like to
do the same, converting to HPGL (HP plotter) files.

機械学習をさせるには、カテゴリー別のデータを用意する必要がり、このデータセットではtarget[に入っているようです。サンプルに沿ってカテゴリーIDを表示してみます。

twenty_train.target[:70]

実行結果。

array([1, 1, 3, 3, 3, 3, 3, 2, 2, 2, 3, 1, 0, 0, 1, 1, 2, 0, 3, 0, 3, 0, 3,
       1, 1, 1, 3, 3, 2, 2, 2, 3, 2, 3, 2, 3, 0, 0, 0, 1, 3, 0, 1, 1, 2, 0,
       3, 3, 1, 2, 1, 2, 0, 0, 2, 1, 2, 3, 0, 1, 0, 3, 1, 2, 1, 1, 2, 0, 3,
       1])

カテゴリーIDが振られた番号が表示されます。どうやらカテゴリーIDは4つしかないようですね。

テキストを分析する前の前処理

テキストを機械学習で分析するには、まずはテキストを数値化する必要があるそうです。 この数値化をするのがBag of Wordsという手法。これは各単語に整数を割り当てて分析しやすくする手法です。Scikit-learnのCountVectorizerというクラスで実装できます。

from sklearn.feature_extraction.text import CountVectorizer
count_vect = CountVectorizer()
X_train_counts = count_vect.fit_transform(twenty_train.data)
X_train_counts.shape

実行結果

(2257, 35788)

上から順に覚え書き

  • count_vect = CountVectorizer()

    CountVectorizer()関数を代入。Bag of Wordsで特徴量を作るのに使用。

  • X_train_counts = count_vect.fit_transform(twenty_train.data)

    fit_transformメソッドは行列を対角化しているそうです。(対角化????、後で調べます)

  • X_train_counts.shape

    出力。2257×35788の行列が出力された。

■カテゴリー毎に分析をする 昨日勉強していたtf-idf分析はscikit-learnを使えば簡単に実装できます。

from sklearn.feature_extraction.text import TfidfTransformer

tf_transformer = TfidfTransformer(use_idf=False).fit(X_train_counts)
X_train_tf = tf_transformer.transform(X_train_counts)

これで終わりで、単語毎に特徴を付けられたそうです。その特徴から、新しい文章がどのカテゴリーに入るのかを分類します。

from sklearn.naive_bayes import MultinomialNB

docs_new = ['this is pen', 'Dayily text mining', 'Happy Hour', 'Visiting Japan']
X_new_counts = count_vect.transform(docs_new)
X_new_tfidf = tfidf_transformer.transform(X_new_counts)
clf = MultinomialNB().fit(X_train_tfidf, twenty_train.target)

predicted = clf.predict(X_new_tfidf)

for doc, category in zip(docs_new, predicted):
    print('%r => %s' % (doc, twenty_train.target_names[category]))

実行結果

'this is pen' => alt.atheism
'Dayily text mining' => soc.religion.christian
'Happy Hour' => soc.religion.christian
'Visiting Japan' => comp.graphics

上から順に覚書。

  • from sklearn.naive_bayes import MultinomialNB

    多項式のナイーブベイズ分類器を使うとの宣言。

  • docs_new = ['this is pen', 'Dayily text mining', 'Happy Hour', 'Visiting Japan']

    新しい言葉を追加。勝手に自分が作りました。

  • X_new_counts = count_vect.transform(docs_new)

    新しく入れた文章をBag of Wordsで整数化。

  • X_new_tfidf = tfidf_transformer.transform(X_new_counts)

    新しく整数化した言葉をtf-idf分析を行う。これでtf-idf分析ができました。試しにX_new_tfidf をprintしてみます。

print(X_new_tfidf)
  (0, 32270) 0.212686048795
  (0, 24748)    0.960792673368
  (0, 18474)    0.177882217916
  (1, 32098)    1.0
  (2, 16897)    0.772575633751
  (2, 16141)    0.634922743438
  (3, 34396)    0.735762763641
  (3, 18662)    0.677239363622

単語が整数化されていますので、どの単語かわからないですが、スコア化されているぽいですね。

  • clf = MultinomialNB().fit(X_train_tfidf, twenty_train.target)

    上の方で作っていたテストデータを学習させる

  • predicted = clf.predict(X_new_tfidf)

    新しく入れた文章を学習したモデルに入れる。predict() メソッドは予測のためのメソッド。

  • for doc, category in zip(docs_new, predicted):

    print('%r => %s' % (doc, twenty_train.target_names[category])) ループで結果の出力をする

パイプライン

今までの流れを簡単にできるようにパイプラインという仕組みがあるらしい。

from sklearn.pipeline import Pipeline
text_clf = Pipeline([('vect', CountVectorizer()),
                     ('tfidf', TfidfTransformer()),
                     ('clf', MultinomialNB()),
                    ])
text_clf.fit(twenty_train.data, twenty_train.target)

実行結果

Pipeline(memory=None,
     steps=[('vect', CountVectorizer(analyzer='word', binary=False, decode_error='strict',
        dtype=<class 'numpy.int64'>, encoding='utf-8', input='content',
        lowercase=True, max_df=1.0, max_features=None, min_df=1,
        ngram_range=(1, 1), preprocessor=None, stop_words=None,
        strip...inear_tf=False, use_idf=True)), ('clf', MultinomialNB(alpha=1.0, class_prior=None, fit_prior=True))])

今の状態ですと逆にわからないですね。

結果のテスト

scikit-learnを使えばテストも簡単にできるらしいです。こんな感じのコードです。

import numpy as np
twenty_test = fetch_20newsgroups(subset='test',
                                 categories=categories, shuffle=True, random_state=42)
docs_test = twenty_test.data
predicted = text_clf.predict(docs_test)
np.mean(predicted == twenty_test.target)
0.83488681757656458

この場合テストデータがあって、それと照らし合わしているぽいです。 サンプルを一通り見ただけですが、ひとまず今日はこの辺りで。

今日の結果

今日の呟きは46件でした。内容はこんな感じです。

{'楽しい': 5, '嬉しい': 3, 'すごい': 3, '良い': 2, '可愛い': 2, '早い': 2, 'よろしい': 1, '恥ずかしい': 1, '仲良い': 1, 'あやい': 1, '心強い': 1, 'やばい': 1, 'いい': 1, '悲しい': 1, 'おいしい': 1, '多い': 1, '何気ない': 1, 'おもしろい': 1, '欲しい': 1, '面白い': 1, 'はやい': 1}

'ちゃん': 17, '今日': 12, '公演': 10, 'さん': 10, 'ん': 9, 'たくさん': 8, '初日': 6, '目撃': 6, '者': 6, 'こと': 6, '楽しい': 5, '昨日': 5, '髪型': 5, '写真': 4, 'メンバー': 4, '佐藤': 4, 'チーム': 4, '収録': 4, '詩織': 4, '誰': 4, '嬉しい': 3, 'すごい': 3, 'の': 3, 'お待ち': 3, 'みなさん': 3, '前': 3, '時': 3, '顔': 3, '智代': 3, '梨': 3, '声': 3, '出演': 3, '詩': 3, '私': 3, '本': 3, '目': 3, '人見': 3, '音': 3

'する': 23, 'ちゃん': 17, '今日': 12, '公演': 10, 'さん': 10, 'ん': 9, 'たくさん': 8, 'てる': 7, '初日': 6, '目撃': 6, '者': 6, 'こと': 6, '楽しい': 5, '昨日': 5, '髪型': 5, 'くれる': 5, '見る': 5, 'くださる': 5, 'すぎる': 5, '写真': 4, 'メンバー': 4, '佐藤': 4, 'チーム': 4, '収録': 4, '詩織': 4, '誰': 4, '観る': 4, 'いる': 4, 'いただく': 4, 'ある': 4, 'みる': 4, '嬉しい':

今日勉強したこと

  • scikit-learnを触ってみる
  • sampleを一通り試してみる
  • Bag of Wordsという手法
  • ナイーブベイズ分類器というアルゴリズム

2018/7/4【12日目】TF-IDF分析を理解する、まずは対数から

昨日の段階で一通りデータの保存、検索の土台ができました。そこで、ずっとやりたかったTF-IDF分析を始めていきたいと思います。

TF-IDF分析とは

自然言語処理の本を見ているとよく出てきます。Webを見てみると初歩中初歩の分析だとか書かれてるくらいです。この分析をすると、ある文書中の単語の重要度がわかるそうです。

TF-IDF分析のWikipediaの解説を見てみます(Hatenaでの数式の書き方がわからなかったので、かなり見にくいです)。

tf-idfは、文書中に含まれる単語の重要度を評価する手法の1つであり、主に情報検索やトピック分析などの分野で用いられている。 tf-idfは、tf(英: Term Frequency、単語の出現頻度)とidf(英: Inverse Document Frequency、逆文書頻度)の二つの指標に基づいて計算される。

なるほど、全くわからないですね。 数式で書くとこんな感じになるらしいです。

f:id:rimt:20180705004346p:plain

n_ijは文書d_jにおける単語t_iの出現回数、Σ_nkjは文書d_jにおけるすべての単語の出現回数の和、|D|は総文書数、|{d:〜}|は単語t_iを含む文書数である。そのため、idfは一種の一般語フィルタとして働き、多くの文書に出現する語(一般的な語)は重要度が下がり、特定の文書にしか出現しない単語の重要度を上げる役割を果たす。

数1までしかやっていない文系にはとてもきつい。まずこのlogというのがよくわかっていないので、まずlogから調べてみます。

logとは

まずは公式から。日本語でいうと対数というんですね。

a^x =b <-> x = log(a)b

例えば5x =25なんて数式があったら、x =log(5)25

それでこれで何を表しているのかと言うと、 ある決まった数を何回かけたか現す数 だそうです。

上の数の場合、5を何回かけたら25になるか、という問題。5x5 =25なので、x=2になります。(a)のところを底と言って、bを真数と言うそうです。指数に変換すると次の式になります。 52 =25

これで何が便利なのかというと、大きい数をわかりやすくできるとのことです。有名なのが、マグニチュードマグニチュードのエネルギーは次のように計算するようです。

log(10)E = 4.8 + 1.5M

指数にすると

E = 104.8 + 1,5Mこんな感じ。M1の場合

E = 10 x 4.8 x 10 x (1.5 x 1)

E = 63.095 x 31.6(約32倍) E = 1993.802 になるらしいです。エネルギーの数値が大きすぎるので(1993.802)、対数を使用しM1と表しているようです。

tf-idfに戻る

もう一回例を考えながらWikiの公式を見てみます。

tfの場合

データマイニングという言葉が1つの文書の中で5回でてきて、その文書における単語の数の和が1000だとsるとtfというのは5/1000で0.005になりますね。

idfの場合

5点の文書があって、その5点の中にデータマイニングという言葉を含んでいる文書が2点ある、となるとidfはlog(a)2.5ですね。それでこのlogというのは、大きくなりすぎる数字を全体的に小さくする役割なので、底(a)はなんでもいいらしいです(1.5くらいにするといいらしいです)。ネットで見ただけの知識なので、そのうち論文でも見てみます。と、いうわけで底を1.5として計算すると2.26です。

tf-idfの計算

tf-idfの式はtfとidfをかければいいだけの話なので、この場合tf-df分析でのデータマイニングの数値は0.0113です。 これだけだけ「なんのこっちゃ」という感じですが、この分析を行うと「その文書における、その単語の重要度」がわかるらしいです。つまり、重要なほどこの数値が高くなるそうです。

ここで終わるのも中途半端ですが、明日実装して色々確かめていきたいと思います。

今日の結果

今日のつぶやき件数は42件でした。 形容詞を見てみるとこんな感じです。

'嬉しい': 8, '楽しい': 5, 'すごい': 3, '良い': 3, '可愛い': 2, '凄い': 2, '面白い': 2, 'かわいい': 1, '高い': 1, 'いい': 1, 'くい': 1, '弱い': 1, '珍しい': 1, '多い': 1, '遅い': 1, 'ない': 1, '難しい': 1, '若い': 1

嬉しいというつぶやきが8件もあります、どうしたんですかね?パッと見た感じですが、番組に出れて嬉しいな、という発言ですね。下記のメンバーが発言しています。

  • '912_komiharu'
  • 'makiho_1019'
  • 'o_megu1112'
  • 'seina_fuku48'

今日学んだこと

  • tf-idf分析の考え方
  • 対数の計算方法

2018/7/3【11日目】SQL構文を覚えよう

とりあえずAKBメンバーのデータを30件ずつ保存しました。 データベースには1032件の呟きが保存されています。 テーブルの中身はこんな感じです。

id,created_data,name,tweet,count_tweets,count_follows,count_followers,count_favolites,count_lists,users_favorites,retweet,favorite,datesp

1,"2018-07-02 22:05:22",48_asainanami,”テキストの内容”,93,91,10037,81,264,81,175,789,"2018-07-03 08:41:51"

今日はこのテーブルを使ってSQLを学んで行きたいと思います。

SQL

投稿者を指定

これは簡単ですね。whereの後ろにnameで指定すればいいだけです。

SELECT * FROM tweet WHERE name = '48_asainanami';

行数を数える

行数を数えるにはCount関数を使うらしいです。投稿者を48_asainanamiに指定して行数を数えてみます。

SELECT COUNT(*) FROM tweet WHERE name = '48_asainanami';

実行結果

30

時期を指定する

散々苦労したcreated_dataを使用します。 時期を指定する場合はBETWEEN ANDを使う。

select * from tweet where created_data between '2018-06-01' and '2018-06-30' and favorite >= 900;

ついでに条件の追加もANDで行う、条件のうちどちらかでも満たせばいいものはORでつなぐ。

何日から先を検索する

created_dataを大なり小なりで比較させて出します。 基本的に文字列はすべてシングルクォートで囲みます。

select * from tweet where created_data <= '2018-06-01';

文字検索

LIKEを使う

select * from tweet where tweet LIKE '%アイドル%';

%の入れ方で検索方法が変わるので注意。この%は0文字以上の任意の文字を表している。 ’%hoge’:hogeから始まる文章を検索する ‘hoge%’:hogeで終わる文章を検索する ‘%hoge%’:hogeを含む文章を検索する

任意の文字列を検索する

INを使います。IDなどを複数検索する時はこれを使う。

select * from tweet where id in (1,5,8);

並び替え

ORDER BY hoge ASC/DESCを使う

select * from tweet where id in (1,5,8) order by tweet ASC;

ASCが昇順、DESCが降順を指定する

サブクエリ

SELECTの中にSELECTを入れることができる、これをサブクエリというらしいです。 いい例が思い浮かばず実行は保留。

長さを測る

length(tweet)を使って、テキスト量を数えることができる。

select 'テキスト量', length(tweet) from tweet;

実践

7月3日分をデータベースに入れましたので、摘出してみます。

select Count(*) from tweet where  created_data >= '2018-07-03';

実行結果

73
select  TWEET from tweet where  created_data >= '2018-07-03';

実行結果

TWEET
"あああああああ相手のゴール綺麗に、、選手の皆さんお疲れ様でした!✨✨✨初めてこんな時間まで起きてスポーツ観戦しました!あー1限頑張るぞ!!!!"
"だ悔しい、、、追いつかれたぁ""あれ、入っちゃうかー、、、、、うーん”
(中略)

ということで、本日は73件の投稿でした。 形容詞を見てみますと、日本代表選手を見ているメンバーが多かったみたいですね。

'悔しい': 4, 'すごい': 4, 'ない': 4, 'かっこよい': 3, 'いい': 3, 'よい': 2, '良い': 2, '高い': 2, '楽しい': 2, '凄い': 1, '難しい': 1, 'たのしい': 1, 'よろしい': 1, '強い': 1, '暑い': 1, '涼しい': 1, '小さい': 1, '近い': 1, 'かわいい': 1, 'ゆるい': 1, '嬉しい': 1, '懐かしい': 1, '可愛い': 1, 'やばい': 1})

今日勉強したこと

-基礎的なSQL文の書き方

2018/7/2【10日目の捕捉】Twitter APIのcreated_atをUNIX時間に変換してDATETIMEに保存する

昨日、Twitterが吐き出すcreated_atを文字列型であるvarcharに保存したのですが、これ、検索に全く使えないですね笑 しっかりtime型で保存できるように、UNIX時間 → 日本時間に変換したいと思います。 Pythonで時間を操作するにはtimeモジュールとdatetimeモジュールを使うらしいです。これはもともとPythonに附属されてますので、pip installしないで良いそうです。

Python公式ページ https://docs.python.jp/3/library/time.html

コードはこんな感じです。 まず、time.strptimeを使って、Twitterが返す時刻に合わせて時間を解釈します。第一引数にTwitter APIが返す引数、第二引数にTwitter APIが持っている情報を示します。

printするとこんなのが出力されます。

time.struct_time(tm_year=2018, tm_mon=7, tm_mday=2, tm_hour=19, tm_min=54, tm_sec=22, tm_wday=0, tm_yday=183, tm_isdst=-1)

↑この形式をUTC のstruct_timeというらしいですが、calendar.timegm関数に入れれば、エポックからの秒数に変換されます。それで、これをtime.localtime関数に入れると日本時間に変換されます。後は出力の形式をtime.strftime関数に入れれば完成です。

import sys, json, time, calendar

                time_utc = time.strptime(created_data, '%a %b %d %H:%M:%S +0000 %Y')
                unix_time = calendar.timegm(time_utc)
                time_local = time.localtime(unix_time)
                japan_time = time.strftime("%Y-%m-%d %H:%M:%S", time_local)

実行結果。

2018-07-02 13:05:22

これで、DATETIME('0000-00-00 00:00:00')に保存できそうですね。 データベースのテーブルを再生成して、Pythonを実行してみます。

create table tweet3(
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
created_data DATETIME NOT NULL,
name varchar(50) NOT NULL,
tweet varchar(255) UNIQUE NOT NULL,
count_tweets int,
count_follows int,
count_followers int,
count_favolites int,
count_lists int, 
users_favorites int,
retweet int,
favorite int,
datesp DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

無事に保存できました。 本当は日本戦の前に完成させたかったです。

2018/7/2【10日目】変数の中身をMySQLにINSERTする(utf8mb4の設定)

昨日、データベースにデータを入れられることができたので、今日はTwiiterのデータを入れていきたいとおもいます。

複数の変数を入れるサンプルコードはOracleの公式ページにありましたので、これを参考にします。

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

insert_stmt = (
  "INSERT INTO employees (emp_no, first_name, last_name, hire_date) "
  "VALUES (%s, %s, %s, %s)"
)
data = (2, 'Jane', 'Doe', datetime.date(2012, 3, 23))
cursor.execute(insert_stmt, data)

select_stmt = "SELECT * FROM employees WHERE emp_no = %(emp_no)s"
cursor.execute(select_stmt, { 'emp_no': 2 })

↓書き直したコード

sql =  (
    "INSERT INTO akb_test5 (name, tweet) "
     "VALUES (%s, %s)"
)
data = (name, name)
crsr.execute(sql, data)
 db.commit()

Tweetの内容に絵文字が入っているためデータを保存できなかったので、代わりに変数nameを保存します。 これで、MySQLの中は、

name tweet
48_asainanami   48_asainanami

となりました。

データが保存できる事が確認できたので、他の変数も入れていきます。 コードはこんな感じになりました。

for j in range(3):
    res = twitter.get(url, params = params)
    if res.status_code == 200:
        timeline = json.loads(res.text)

        # 各ツイートの内容を保存
        for i in range(len(timeline)):
            if i == len(timeline)-1:
                created_data = timeline[i]['created_at']
                name = timeline[i]["user"]["screen_name"]
                tweet = timeline[i]['text']
                count_tweets = timeline[i]["user"]["statuses_count"]
                count_follows = timeline[i]["user"]["friends_count"]
                count_followers = timeline[i]["user"]["followers_count"]
                count_favolites = timeline[i]["user"]["favourites_count"]
                count_lists = timeline[i]["user"]["listed_count"]
                users_favorites = timeline[i]["user"]["favourites_count"]
                retweet = timeline[i]["retweet_count"]
                favorite = timeline[i]["favorite_count"]
                params['max_id'] = timeline[i]['id']-1

            sql =  (
              "INSERT INTO akb_test5 (created_data, name, tweet, count_tweets, count_follows, count_followers, count_favolites, count_lists, users_favorites, retweet, favorite)"
              "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            )

            data = (created_data, name, tweet, count_tweets, count_follows, count_followers, count_favolites, count_lists, users_favorites, retweet, favorite)
            crsr.execute(sql, data)
            db.commit()

crsr.close()

実行結果

mysql.connector.errors.DataError: 1292 (22007): Incorrect time value: 'Sun Jul 01 09:45:46 +0000 2018' for column 'created_data' at row 1

もちろん型が合わないというエラーが返ってきます。 データベースの型を一個一個修正していきます。

型の修正にはALTERコマンドを使えばいいらしいですね。書式は次のようです。

ALTER TABLE テーブル名前 MODIFY フィールドの名前 データ型;

最初のcreated_dataからやっていきます。こいつはdata型じゃないよ、というエラーを起こしています。日付型で合うのを探したのですが、どうやら合うのがないようですので、created_dataは文字列型で保存すること にします。

ALTER TABLE akb_test5 MODIFY created_data varchar(50);

どうやら成功したようです。 さっきのPythonのコードを実行してみます。

実行結果

mysql.connector.errors.DatabaseError: 1366 (HY000): Incorrect string value: '\xF0\x9F\x91\x97\xF0\x9F...' for column 'tweet' at row 1

created_dataからtweetに移ったようなので、created_dataは保存できるようになったようです。 tweetのエラーは絵文字が対応していないとのことでした。設定していますutf8をutf8mb4に変更します。

utf8mb4に変更するには次のコマンドのようです。

SET character_set_database=utf8mb4;

実行と確認です。

SET character_set_database=utf8mb4;
show variables like 'character%’;

これでcharacter_set_databaseがtf8mb4;になりました。再びPythonコード実行。

mysql.connector.errors.DatabaseError: 1366 (HY000): Incorrect string value: '\xF0\x9F\x91\x97\xF0\x9F...' for column 'tweet' at row 1

エラー内容が変わらずでした。 いろいろやってみましたが解決できなかったのでデータベースを作り直してみました。

create database akb_tweet_test2 default character set utf8mb4;
use akb_tweet_test2;
create table tweet2(
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
created_data varchar(50) NOT NULL,
name varchar(50) NOT NULL,
tweet varchar(255) UNIQUE NOT NULL,
count_tweets int,
count_follows int,
count_followers int,
count_favolites int,
count_lists int, 
users_favorites int,
retweet int,
favorite int,
datesp DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

これでもエラー内容は変わらず。。。 ALTERコマンドを使って、カラム単位での変更もできるようなので、これを試してみます。

ALTER DATABASE akb_tweet_test CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

これでもエラーは修正できず。。。

show variables like '%char%';

このコマンドで文字コードを確認すると、どうやら

character_set_client | utf8

これがutf8mb4になっておらず。。。いろいろ試してみましたが、結局うまくいったのはPython側から文字コードを指定してあげる事でした。以下、完成コードです。

for j in range(3):
    res = twitter.get(url, params = params)
    if res.status_code == 200:
        timeline = json.loads(res.text)

        # 各ツイートの本文を表示
        for i in range(len(timeline)):
            if i == len(timeline)-1:
                created_data = timeline[i]['created_at']
                name = timeline[i]["user"]["screen_name"]
                tweet = timeline[i]['text']
                count_tweets = timeline[i]["user"]["statuses_count"]
                count_follows = timeline[i]["user"]["friends_count"]
                count_followers = timeline[i]["user"]["followers_count"]
                count_favolites = timeline[i]["user"]["favourites_count"]
                count_lists = timeline[i]["user"]["listed_count"]
                users_favorites = timeline[i]["user"]["favourites_count"]
                retweet = timeline[i]["retweet_count"]
                favorite = timeline[i]["favorite_count"]
                params['max_id'] = timeline[i]['id']-1

            sql =  (
              "INSERT INTO tweet (created_data, name, tweet, count_tweets, count_follows, count_followers, count_favolites, count_lists, users_favorites, retweet, favorite)"
              "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
            )

            data = (created_data, name, tweet, count_tweets, count_follows, count_followers, count_favolites, count_lists, users_favorites, retweet, favorite)
            crsr.execute('SET NAMES utf8mb4')
            crsr.execute("SET CHARACTER SET utf8mb4")
            crsr.execute("SET character_set_connection=utf8mb4")
            crsr.execute(sql, data)
            db.commit()

crsr.close()

これでひとまずデータベースに保存することができました。 いろいろ検索してこのデータベースの使い心地を確かめたいと思います。

今日の結果

今日は最小の26件でした。これといった特徴もありませんでした。 f:id:rimt:20180703002506p:plain

形容詞 '楽しい': 3, 'ない': 2, '多い': 1, 'よい': 1, '切ない': 1, '恥ずかしい': 1, '可愛い': 1, '嬉しい': 1, '面白い': 1, 'すごい': 1, 'いい': 1, 'よろしい': 1, 'すっごい': 1

名詞 'ちゃん': 8, '今日': 7, '公演': 5, '人': 5, '中': 4, '目': 4, '出演': 4, '楽しい': 3, '時': 3, '背景': 3, 'の': 3, 'こと': 3, '感動': 3,

動詞 'する': 16, '見る': 9, 'ちゃん': 8, '今日': 7, '公演': 5, '人': 5, 'てる': 5, '中': 4, '目': 4, '出演': 4, '思う': 4, 'せる': 4, '楽しい': 3, '時': 3, '背景': 3, 'の': 3, 'こと': 3, '感動': 3, '来る': 3, 'くれる': 3, 'なる': 3, '変える': 3, 'すぎる': 3, 'くる': 3, '頂く': 3

今日勉強した事

  • 変数をデータベースへ保存する仕方
  • 文字コードの変更