データーの違いから加工ロジックを検討する

「【Plotly】GraphObjectsを使う」ではグラフ描画に必要なデータ構造を確認したためデータベースからデータを取得しグラフ描画用のデータに変換するロジックを考えてゆきます。

前回のプログラムでGraph Objectsに描画した際に用いたディクショナリーの形式のデータは以下のような形にしました。

{
  "検索キーワード1":{
    "日付":['2021-05-09', '2021-05-08', '2021-05-07', '2021-05-06', '2021-05-05'],
    "サイト":{
      "サイトA記事1":[1, 2, None, 2, 1],
      "サイトB記事2":[2, 1, 2, None, None],
      "サイトC記事3":[None, None, 1, 1, 2]
    }
  },
  "検索キーワード2":{
    "日付":['2021-05-08', '2021-05-07', '2021-05-06', '2021-05-05', '2021-05-04'],
    "サイト":{
      "サイトB記事2":[1, 2, 2, 1, 2],
      "サイトD記事4":[2, 1, None, None, None],
      "サイトE記事5":[None, None, 1, 2, 1]
    }
  },
}

ディクショナリの階層が上から「検索キーワード」、「日付」、「直近順位が高いサイト順」となっているため、SQLのソート順は「検索キーワード」「日付(降順)」「順位」「サイト記事タイトル」という順番にすればよさそうです。(日付を降順にしているのは、直近のランキングが高いグラフから最初に描くことで、グラフの凡例を直近のランキング順に揃えるためです)

サイトの順位が圏外のときにはサイト順位配列(サイトA記事1などの右側の配列)にNoneが入ってくるため、サイト順位配列の初期化時に日付の数だけNoneで初期化しておきます。特定日付のサイト順位がわかった段階で、サイト順位配列の該当箇所に順位をセットしてNoneを正しい順位で上書きします。 これでデータベースの順位データがない圏外時にはサイト順位配列にNone残るため、データー欠損時には折れ線が結ばれることがなくなるはずです。

日付の数だけサイト順位配列のサイズが必要なので日付配列(検索キーワードごとにある"日付"の右側の配列)は全体のデータを取得するSQLとは別に、事前取得し配列に格納することにします。 これで日付の配列の数がわかるためサイト順位配列をNoneで初期化することができます。

上記のデータをソート順を考えてデータベースからデータを取得します。

ER図とテーブル定義は「【Plotly】データベースからデータを読み込んで可視化する」を参考にしてください。

以下のようなデータが取得されてくる想定です。

取得データサンプル

キーワード	日付(降順)	順位	ドキュメントIDとタイトル
検索キーワード1	2021-05-09	1	サイトA 記事1
検索キーワード1	2021-05-09	2	サイトB 記事2
検索キーワード1	2021-05-08	1	サイトB 記事2
検索キーワード1	2021-05-08	2	サイトA 記事1
検索キーワード1	2021-05-07	1	サイトC記事3
検索キーワード1	2021-05-07	2	サイトB記事2
検索キーワード1	2021-05-06	1	サイトC記事3
検索キーワード1	2021-05-06	2	サイトA記事1
検索キーワード1	2021-05-05	1	サイトA記事1
検索キーワード1	2021-05-05	2	サイトC記事3
検索キーワード2	2021-05-08	1	サイトB記事2
検索キーワード2	2021-05-08	2	サイトD記事4
検索キーワード2	2021-05-07	1	サイトD 記事4
検索キーワード2	2021-05-07	2	サイトB記事2
検索キーワード2	2021-05-06	1	サイトE記事5
検索キーワード2	2021-05-06	2	サイトB記事2
検索キーワード2	2021-05-05	1	サイトB記事2
検索キーワード2	2021-05-05	2	サイトE記事5
検索キーワード2	2021-05-04	1	サイトE記事5
検索キーワード2	2021-05-04	2	サイトB記事2

これを最初に述べたディクショナリ形式に格納すれば良いわけです。

実装

SQLを組み立てる部分は以下のようになります。

        result = session.query(
             TSearchM.keywords
            ,TSearch.search_m_id
            ,TDoc.id
            ,TDoc.title
            ,TSearch.search_datetime
            ,TRanking.ranking
        ).join(
            TSearch,TSearchM.id == TSearch.search_m_id
        ).join(
            TRanking,TSearch.id == TRanking.search_id
        ).join(
            TDoc,TRanking.doc_id == TDoc.id
        ).order_by(
            TSearchM.keywords
            ,TSearch.search_datetime.desc()
            ,TRanking.ranking
            ,TDoc.title
        ).all()

特に難しい部分はないのではないかと思います。前述の表との違いは、キーワードの横に表示する検索マスタのid(TSearch.search_m_id)と、タイトルの横に表示するid(TDoc.id)を取得している点です。グラフ表示時に同じタイトルの記事があった際に、見る人が混乱しないように［］の中にidを表示したいと思い、idも一緒に取得してくることにしました。 ソート順も前述した通りです。日時は降順にするため.desc()をつけています。 ここで取得したデータをディクショナリに格納してゆく部分が以下となります。

        graph_dic={}
        for raw in result:
            graph_keyword='['+str(raw.search_m_id)+']'+ raw.keywords
            if graph_keyword not in graph_dic:
                # キーワードが存在しない場合。新しいグラフの描画
                # 日付を取得
                date_axis=[]
                result = session.query(
                    TSearch.search_datetime
                ).filter(
                    TSearch.search_m_id == raw.search_m_id
                ).order_by(
                    TSearch.search_datetime.desc()
                ).all()

                for dates in result:
                    date_axis.append(dates.search_datetime)

                site_dic={}
                keyword_dic={
                    "日付": date_axis
                    ,"サイト": site_dic
                }

                graph_dic[graph_keyword]=keyword_dic

            title = '[' + str(raw.id) + ']' + raw.title
            
            if title not in site_dic:
                # ここでランキング順位をすべてNoneでリセットしておく
                # python 3.7以降はdictが順序を保持するようになったためシンプルに突っ込む
                ranking_updown = [None] * len(date_axis)
                site_dic[title] = ranking_updown

            # 順位を取得
            ranking_updown = site_dic[title]
            # 日付と同じインデックス(順番の配列)に順位を入れる
            ranking_updown[date_axis.index(raw.search_datetime)] = raw.ranking

graph_dicというディクショナリーの中にデータを格納しています。最初に空のディクショナリーを作成してその中にデータを格納しています。 forループの中がgraph_dicにデータを入れてゆく処理です。 以下の部分でキーワードがgraph_dicに存在しているか確認しています。

if graph_keyword not in graph_dic:

もし存在している場合には、キーワード個別の初期化は済んでいるためif文の中は通りません。 それまでにそれまでにないキーワードの場合、if文の中で検索キーワード用のディクショナリーを新しく生成してgraph_dicに追加する処理をおこなっています。 まず検索順位を取得した日時の配列(日付配列)を取得しています。

新しくサイト(記事)の順位を生成する際には、この日付配列に対応する順位配列をNoneで初期化することになります。 初期化するかどうかの判定部分が以下になります。

if title not in site_dic:

もし既出でないサイトの場合、このif文の中に入って日付配列に対応する順位配列が作成されNoneで初期化されます。
新しくレコードを読み取る度に、存在するサイト(記事)があれば、対応する順位配列を読み取った順位で上書き、記事が存在しない場合には、新しく記事を作成し順位をセットします。
GraphObjectsではそのまま順番に読み取れば良いディクショナリーのデータ構造になっているため、この後の処理が非常に簡単になります。

 

取得したデータのプロット

この部分に関しては特に注意する事項はありません。難しいと感じる方は以前の記事「【Plotly】GraphObjectsを使う」が参考になるとおもいます。

def plot_datas(plotdatas:dict):
    for keyword, datas in plotdatas.items():
        fig = go.Figure()
        for key, data in datas.items():
            if key == "日付":
                xvalues = data
            elif key == "サイト":
                site_dic = data

        for site, rank in site_dic.items():
            fig.add_trace(go.Scatter(
                x=xvalues,
                y=rank,
                name=site
            ))
        fig.update_yaxes(autorange='reversed',dtick=5)
        fig.update_xaxes(tickformat="%Y-%m-%d",dtick='1 Day')
        fig.update_layout(title=keyword,xaxis_title="日付",yaxis_title="順位")
        fig.show()

取得したディクショナリをループで回してPlotlyで次々とグラフを描画しています。

まとめ

データの可視化をおこなう際に注意することは、可視化するために格納する先のデータ構造を意識してデータベースからデータを抽出することです。特に格納先の階層を意識したソート順を意識してみてください。

参考

今回使ったソースコードの全量を張っておきます。

# -*- coding: utf-8 -*-

import sqlalchemy
from sqlalchemy import Column, Integer, String, Date, Float, DateTime, distinct, desc
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.schema import UniqueConstraint
import plotly.graph_objects as go

Base = declarative_base()

class TSearchM(Base):
    __tablename__ = 't_search_m'
    id = Column(Integer, primary_key=True, autoincrement=True)
    keywords = Column(String(256), nullable=False)


class TSearch(Base):
    __tablename__ = 't_search'
    id = Column(Integer, primary_key=True, autoincrement=True)
    search_m_id = Column(Integer, nullable=False)
    search_datetime = Column(DateTime, nullable=False)

class TRanking(Base):
    __tablename__ = 't_ranking'
    __table_args__ = (UniqueConstraint('search_id','ranking'),{})    
    id = Column(Integer, primary_key=True, autoincrement=True)
    search_id = Column(Integer, nullable=False)
    doc_id = Column(Integer, nullable=False)
    ranking = Column(Integer, nullable=False)

class TDoc(Base):
    __tablename__ = 't_doc'
    id = Column(Integer, primary_key=True, autoincrement=True)
    link_url = Column(String(2083), nullable=False)
    title = Column(String(128))
    mypage_flg = Column(Integer, nullable=False)

def selectRanking():
    """
    ランキングに出でくるサイトを全部洗い出し最新ランキングの高い順に並び替えた上でdictionaryにセットする
    """
    connect_string = "sqlite:///ranking.sqlite3"
    engine = sqlalchemy.create_engine(connect_string, echo=True) # SQLとデータを出力したい場合はecho=Trueにする

    try:
        session = scoped_session(
                    sessionmaker(
                        autocommit = False,
                        autoflush = True,
                        bind = engine))

        Base.query = session.query_property()

        result = session.query(
             TSearchM.keywords
            ,TSearch.search_m_id
            ,TDoc.id
            ,TDoc.title
            ,TSearch.search_datetime
            ,TRanking.ranking
        ).join(
            TSearch,TSearchM.id == TSearch.search_m_id
        ).join(
            TRanking,TSearch.id == TRanking.search_id
        ).join(
            TDoc,TRanking.doc_id == TDoc.id
        ).order_by(
            TSearchM.keywords
            ,TSearch.search_datetime.desc()
            ,TRanking.ranking
            ,TDoc.title
        ).all()

        graph_dic={}
        for raw in result:
            graph_keyword='['+str(raw.search_m_id)+']'+ raw.keywords
            if graph_keyword not in graph_dic:
                # キーワードが存在しない場合。新しいグラフの描画
                # 日付を取得
                date_axis=[]
                result = session.query(
                    TSearch.search_datetime
                ).filter(
                    TSearch.search_m_id == raw.search_m_id
                ).order_by(
                    TSearch.search_datetime.desc()
                ).all()

                for dates in result:
                    date_axis.append(dates.search_datetime)

                site_dic={}
                keyword_dic={
                    "日付": date_axis
                    ,"サイト": site_dic
                }

                graph_dic[graph_keyword]=keyword_dic

            title = '[' + str(raw.id) + ']' + raw.title
            
            if title not in site_dic:
                # ここでランキング順位をすべてNoneでリセットしておく
                # python 3.7以降はdictが順序を保持するようになったためシンプルに突っ込む
                ranking_updown = [None] * len(date_axis)
                site_dic[title] = ranking_updown

            # 順位を取得
            ranking_updown = site_dic[title]
            # 日付と同じインデックス(順番の配列)に順位を入れる
            ranking_updown[date_axis.index(raw.search_datetime)] = raw.ranking

    except Exception:
        raise
    else:
        session.close()
    finally:
        engine.dispose()
    return graph_dic

def plot_datas(plotdatas:dict):
    for keyword, datas in plotdatas.items():
        fig = go.Figure()
        for key, data in datas.items():
            if key == "日付":
                xvalues = data
            elif key == "サイト":
                site_dic = data

        for site, rank in site_dic.items():
            fig.add_trace(go.Scatter(
                x=xvalues,
                y=rank,
                name=site
            ))
        fig.update_yaxes(autorange='reversed',dtick=5)
        fig.update_xaxes(tickformat="%Y-%m-%d",dtick='1 Day')
        fig.update_layout(title=keyword,xaxis_title="日付",yaxis_title="順位")
        fig.show()


graph_dic=selectRanking()
plot_datas(graph_dic)