時系列データの日付整理

いつもの冒頭

import pandas as pd
from datetime import datetime as dt, timedelta
import numpy as np
import matplotlib
import matplotlib.pyplot as plt
import scipy.optimize
%matplotlib inline

dfの日付データをiso formatに近い感じに直す
・//で整数の商（スラッシュしましょう）、%で余り（ぱいせんが余りを食べる）

print(dfNewRc.index.tolist())
# ['1QFY14', '1QFY15', '1QFY16', '1QFY17', '2QFY14', '2QFY15', '2QFY16', '2QFY17', '3QFY14', '3QFY15', '3QFY16', '3QFY17', '4QFY14', '4QFY15', '4QFY16', '4QFY17']
retList = dfNewRc.index.tolist()
retList = [ str( int('20'+ str( int(retList[i][4:6]) ))+ int(retList[i][0:1]) //4) +'-{:0=2}'.format(int(retList[i][0:1]) %4 *3+3) for i in range(0,len(retList))]
print(retList)
# ['2014-06', '2015-06', '2016-06', '2017-06', '2014-09', '2015-09', '2016-09', '2017-09', '2014-12', '2015-12', '2016-12', '2017-12', '2015-03', '2016-03', '2017-03', '2018-03']
dfNewRc['Date']=retList
dfNewRc=dfNewRc.set_index('Date').sort_values(by=['Date'], ascending=True)

こちらはiso-formatの日付のリストを四半期ごとのものに直すfunction

def ToQuarterly(listDate, endOfFY=3):
    convtList= [[0,3,3,3,6,6,6,9,9,9,12,12,12],[0,1,4,4,4,7,7,7,10,10,10,1,1],[0,2,2,5,5,5,8,8,8,11,11,11,2]]
    m= [convtList[endOfFY%3][int(eachDate[5:7])] for eachDate in listDate]
    y= [int(eachDate[0:4])+1 if (endOfFY%3 != 0 and int(eachDate[5:7]) > (9+endOfFY%3)) else int(eachDate[0:4]) for eachDate in listDate]
    return [str(y[i])+'-{:0=2}'.format(m[i]) for i in range(0, len(listDate))]
# 例えば11月締なら['2004-01', '2004-02', '2004-03']が['2004-02', '2004-02', '2004-05']になる。

この関数を用いて以下のようにすることで、四半期ごとの合計を取り出せる

dfNewRc_forsum = dfNewRc
dfNewRc_forsum.index = ToQuarterly(dfNewRc.index)
dfNewRc_forsum.index.names = ['sumby']
dfNewRc_forsum=dfNewRc_forsum.sum(level='sumby')

移動平均をとる方法

df.rolling(window=30, min_periods=30).mean()

xxヶ月後を出す関数。

def months_plus(dt_date, i):
    """
    >>> months_plus(dt(2018,10,30),3)
    datetime.datetime(2019, 1, 30, 0, 0)
    """
    ret=dt_date.year*12+dt_date.month+i
    ret= dt(ret // 12 if ret % 12 !=0 else ret // 12-1, ret % 12 if ret % 12 !=0 else 12, 1)
    ret2=ret.year*12+ret.month+1
    end_of_month=(dt(ret2 // 12 if ret2 % 12 !=0 else ret2 // 12-1, ret2 % 12 if ret2 % 12 !=0 else 12, 1)-timedelta(days=1)).day
    return dt(ret.year,ret.month,min(dt_date.day,end_of_month ))

日付を指定の期間ごとに割る関数

from datetime import timedelta, datetime as dt

def period_div(date_a, date_b, delta = timedelta(hours=2)):
    """
    >>> period_div(dt(2016, 8, 9),dt(2016,8,10),delta = timedelta(hours=12))
    [datetime.datetime(2016, 8, 9, 12, 0), datetime.datetime(2016, 8, 10, 0, 0)]
    """
    timestamps = []
    date_x = date_a
    while date_x < date_b:
      date_x += delta
      timestamps.append(date_x)
    return timestamps

datetimeが休日の場合に1を返す関数

from datetime import datetime as dt,timedelta
import requests

def dt_holiday(dt_date):
    '''
    return str 'holiday' if given datetime is a holiday
    '''
    url=r'http://s-proj.com/utils/checkHoliday.php?kind=h&date='
    url+=dt_date.strftime('%Y%m%d')
    response = requests.get(url)
    ans=0
    if response.text=='holiday':
            ans=1
    return ans

その月の最後の日を返す関数

from datetime import datetime as dt, timedelta

def last_date(dt_date):
    """dt(2018,11,5)-->return dtt(2018,11,30)"""
    dt_date=dt(dt_date.year,dt_date.month+1,1)
    dt_date-=timedelta(days=1)
    return dt_date