Python (파이썬) DimDate 테이블 만들기

데이터프레임 생성까지의 예제입니다.

기본적으로 csv로 출력하도록 하였으나 바로 DB로 밀어 넣고 싶다면 sqlalchemy를 사용하면 됩니다.

 

1. 샘플 - 작은 DimDate

import pandas as pd
pd.set_option('display.max_row', 100)

def create_date_table(start, end):
    start_ts = pd.to_datetime(start).date()

    end_ts = pd.to_datetime(end).date()

    # record timetsamp is empty for now
    dates =  pd.DataFrame(columns=['Record_timestamp'],
        index=pd.date_range(start_ts, end_ts))
    dates.index.name = 'Date'

    days_names = {
        i: name
        for i, name
        in enumerate(['월', '화', '수', '목', '금', '토', '일'])
    }

    dates['DayName'] = dates.index.dayofweek.map(days_names.get)
    dates['Week'] = dates.index.week
    dates['Month'] = dates.index.month
    dates['Quarter'] = dates.index.quarter
    dates['YearHalf'] = dates.index.month.map(lambda mth: 1 if mth <7 else 2)
    dates['Year'] = dates.index.year
    dates.reset_index(inplace=True)
#     dates.index.name = 'DateKey'
    dates.drop(['Record_timestamp'], axis=1, inplace=True)
    return dates

def fullDateKor(inColumn):
    tmpDate = str(inColumn)[:10].split('-')
    outValue = tmpDate[0] + "년 " + tmpDate[1] + "월 " + tmpDate[2] + "일"
    
    return outValue

def dateKey(inColumn):
    return str(inColumn)[:10].replace('-', '')

df = create_date_table(start='1900-01-01', end='2100-12-31')

df['FullDateKor'] = df.Date.apply(fullDateKor)
df['DateKey'] = df.Date.apply(dateKey)

df

# df.to_csv("./DimDate.csv", index=False)

 

2. 샘플 - 큰 DimDate

import pandas as pd
pd.set_option('display.max_row', 500)
pd.set_option('display.max_columns', 500)

def create_date_table(start, end):
    start_ts = pd.to_datetime(start).date()

    end_ts = pd.to_datetime(end).date()

    # record timetsamp is empty for now
    dates =  pd.DataFrame(columns=['Record_timestamp'],
        index=pd.date_range(start_ts, end_ts))
    dates.index.name = 'FullDateAlternateKey'

    months_names_kor = {
        i+1: name
        for i, name
        in enumerate(['1월','2월','3월','4월','5월','6월','7월','8월','9월','10월','11월','12월'])
    }
    
    months_names_eng = {
        i+1: name
        for i, name
        in enumerate(['January','February','March','April','May','June','July','August','September','October','November','December'])
    }
    
    days_names_kor = {
        i: name
        for i, name
        in enumerate(['월요일', '화요일', '수요일', '목요일', '금요일', '토요일', '일요일'])
    }
    
    days_names_eng = {
        i: name
        for i, name
        in enumerate(['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'])
    }
    
    # 월
    dates['MonthNumberOfYear'] = dates.index.month
    dates['KoreanMonthName'] = dates.index.month.map(months_names_kor.get)
    dates['EnglishMonthName'] = dates.index.month.map(months_names_eng.get)

    # 일
    dates['DayNumberOfWeek'] = dates.index.dayofweek.map(lambda x: x + 2 if x < 6 else 1) # {월 : 0 ~ 일 : 6}을 {일 : 1 ~ 토 : 7}로 바꾼다.
    dates['KoreanDayNameOfWeek'] = dates.index.dayofweek.map(days_names_kor.get)
    dates['EnglishDayNameOfWeek'] = dates.index.dayofweek.map(days_names_eng.get)
    
    dates['CalendarYear'] = dates.index.year # 연도
    dates['CalendarYearHalf'] = dates.index.month.map(lambda mth: 1 if mth <7 else 2) # 반기
    dates['CalendarQuarter'] = dates.index.quarter # 분기

    dates['WeekNumberOfYear'] = dates.index.week # 주차 : 1 ~ 52
    dates['DayNumberOfMonth'] = dates.index.day # 월별 일 : 1 ~ 31
    dates['DayNumberOfYear'] = dates.index.dayofyear # 연간 일수 : 1 ~ 365
    
    dates.reset_index(inplace=True)
    dates.index.name = 'DateKey'
    dates.drop(['Record_timestamp'], axis=1, inplace=True)
    return dates


def dateKey(inColumn):
    return str(inColumn)[:10].replace('-', '')

def fullDateKor(inColumn):
    tmpDate = str(inColumn)[:10].split('-')
    outValue = tmpDate[0] + "년 " + tmpDate[1] + "월 " + tmpDate[2] + "일"
    
    return outValue

df = create_date_table(start='1900-01-01', end='2100-12-31')

df['DateKey'] = df.FullDateAlternateKey.apply(dateKey)
df['FullDateKor'] = df.FullDateAlternateKey.apply(fullDateKor)

# 컬럼 순서 재정렬
df = df.reindex(columns=['DateKey'
                         ,'FullDateAlternateKey'
                         ,'FullDateKor'
                         
                         ,'MonthNumberOfYear'
                         ,'KoreanMonthName'
                         ,'EnglishMonthName'
                         
                         ,'DayNumberOfWeek'
                         ,'KoreanDayNameOfWeek'
                         ,'EnglishDayNameOfWeek'
                         
                         ,'CalendarYear'
                         ,'CalendarYearHalf'
                         ,'CalendarQuarter'
                         
                         ,'WeekNumberOfYear'
                         ,'DayNumberOfMonth'
                         ,'DayNumberOfYear'
                        ])

df

# df.to_csv("./DimDate.csv", index=False, encoding='UTF-8')
# pd.read_csv('./DimDate.csv')

(클릭 시 원본 확인 가능)

 

SqlAlchemy 사용법 확인하기

Python (파이썬) SqlAlchemy 모듈 (engine)

 

 

Tag. date dimension, date 차원, date 테이블, dim date