年と週番号からその週の最初の日付を求める

#!/usr/bin/env python3

import os
import sys
import sqlite3
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime, date, timedelta
from dateutil.relativedelta import relativedelta


def get_heatmap(hm_png_filename, dbname, query, duration):

  conn = sqlite3.connect(dbname)
  cursor = conn.cursor()

  df = pd.read_sql_query(sql=query, con=conn)
  df["ymd"] = pd.to_datetime(df["ymd"])
  df["week"] = df["ymd"].dt.isocalendar().week
  df["year"] = df["ymd"].apply(lambda _: _.year)
  df["1st_of_the_week"] = df.apply(get_1st_of_the_week, axis=1)
  df["1st_of_the_week"] = df["1st_of_the_week"].apply(
      lambda _: str(_)).str[:10]

  print(df)
  print(df.dtypes)
  print()

  # クロス集計する
  x_key = "alarm_name"
  y_key = "1st_of_the_week"
  sum_key = "CNT"

  df_hm = pd.crosstab(  # heatmap用
      index=df[x_key],
      columns=df[y_key],
      values=df[sum_key],
      aggfunc="sum",
      margins=False,
  )

  print(df_hm)
  print()

  #
  # 欠損値は0で埋める
  #
  df_hm = df_hm.fillna(0)

  print(df_hm)
  print()

  # ===============================
  # heapmapとして可視化する
  # ===============================
  fig, ax = plt.subplots(figsize=(16, 9))
  heatmap = sns.heatmap(df_hm, cmap="Reds")  # Dataframeであるdf_hmとseabornを関連づける

  heatmap.plot()

  plt.title(title)
  plt.subplots_adjust(left=0.35, right=0.98, bottom=0.15, top=0.9)

  plt.savefig(hm_png_filename)  # 画像をファイルに保存

  print("%s created.\n" % hm_png_filename)

  # ===============================
  # SQLコネクションを閉じる(後始末)
  # ===============================
  conn.close()


def get_1st_of_the_week(x):
  year = x.iloc[4]
  week = x.iloc[3]
  d = str(year) + "-" + str(week)
  return datetime.strptime(d + "-1", "%Y-%W-%w")  # 月曜日を返す


def get_dbname_by_tag(tag):
  if tag == "EMO":
    dbname = "emo_alarm.db"
    dbtable = "emo_alarm_history"
  elif tag == "GINO":
    dbname = "gino_alarm.db"
    dbtable = "gino_alarm_history"

  return dbname, dbtable


def get_condition_by_duration(duration):
  today = datetime.today()

  if duration == "1m":
    start_date = today - relativedelta(months=1)
    condition = (
        "AND date(event_time) >= date('"
        + datetime.strftime(start_date, "%Y-%m-%d")
        + "') /** one month ago **/"
    )
  elif duration == "3m":
    start_date = today - relativedelta(months=3)
    condition = (
        "AND date(event_time) >= date('"
        + datetime.strftime(start_date, "%Y-%m-%d")
        + "') /** three months ago **/"
    )
  elif duration == "all":
    condition = "/** all thru the duration in which alarm data has been stored **/"

  return condition


if __name__ == "__main__":

  # 本当は辞書型にしてそれをイテレートしていくのがベターw
  for tag in ["EMO", "GINO"]:
    dbname, dbtable = get_dbname_by_tag(tag)

    for duration in ["1m", "3m", "all"]:
      condition = get_condition_by_duration(duration)

      print(dbname, duration, condition)

      hm_png_filename = "RESULT/" + sys.argv[0].replace(
          ".py", "_" + tag + "_" + duration + ".png"
      )
      title = (
          tag
          + ": Heatmap of weekly alarm count by alarm name\n"
          + "duration = "
          + duration
      )

      #
      # conditionはヒアドキュメントで差し込む
      #
      query = """
      select 
        alarm_name,
        substr(event_time, 1, 10) as ymd,
        count(*) as CNT
      from {dbtable}
      where status_change like '%OK to ALARM'
      {condition}
      group by alarm_name, ymd
      order by alarm_name, ymd
      """.format(
          condition=condition, dbtable=dbtable
      ).strip()

      get_heatmap(hm_png_filename, dbname, query, title)


# NOTE:
###
# ykishi@dezembro alarm % ipython
# Python 3.10.0 (default, Oct  6 2021, 01:11:32) [Clang 13.0.0 (clang-1300.0.29.3)]
# Type 'copyright', ' redits' or 'license' for more information
# IPython 7.28.0 -- An enhanced Interactive Python. Type '?' for help.
###
# In [1]: from datetime import datetime, date, timedelta
###
# In [2]: from dateutil.relativedelta import relativedelta
###
### In [3]: today = datetime.today()
###
### In [4]: today
### Out[4]: datetime.datetime(2021, 10, 9, 0, 25, 37, 777140)
###
### In [5]: one_month_ago = today - relativedelta(months=1)
###
### In [6]: datetime.strftime(one_month_ago, '%Y-%m-%d')
### Out[6]: '2021-09-09'
###
# In [7]:
###