포스팅 목차
148. Employees hire on 15th of any month are paid on the last Friday of that month. Those hired after the 15th are paid on the last Friday of the following month. Print a list of employees, their hiredate and first pay date. Sort on day of hiredate
* 15일 이전에 입사한 직원은 해당월의 마지막 금요일에 급여가 지급되고, 15일 이후에 입사한 직원은 다음 달 마지막 금요일에 급여가 지급된다. 직원들의 입사일자와 입사 후 첫 번째 지급된 급여일자를 출력하고, 입사일자의 일자를 기준으로 정렬하시오
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- [날짜 함수] 특정 조건에 따라 해당 날짜 반환
- 데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql
|
1. Oracle(오라클)
입사일자에서 일자 부분을 추출하여서 15일 이전이면 당월 마지막 금요일의 일자를 산출하고, 15일 이후이면 다음 달 마지막 금요일의 일자를 산출한다.
| Oracle Programming |
SELECT ENAME, HIREDATE,
CASE WHEN TO_CHAR(hiredate,'DD')<=15
THEN NEXT_DAY(LAST_DAY(hiredate)-7,'friday')
ELSE NEXT_DAY(LAST_DAY(ADD_months(hiredate,1))-7,'friday') END "payDATE"
FROM EMP
order by substr(HIREDATE,7,2);
2. Python Pandas(파이썬)
[참고] 일정 일자 경과 후 일자와 요일 반환하기
- last_day : pandas.tseries.offsets.MonthEnd 함수를 사용하여서 당월의 마지막 일자를 반환한다.
- before_day : 7*Day() 함수를 사용하여서 당월 마지막 일자를 기준으로 1주일 이전 날짜를 반환한다.
- week_day_1 : pd.DateOffset(weekday=FR(0)) 함수를 사용하여서 당월 마지막 금요일의 일자를 반환한다. ( MonthEnd(1) - 7*Day() )가 당월 마지막 일자 1주일 이전 날짜를 반환하고, 이후 금요일 일자를 반환한다.
- week_day_2 : 1 * Week(weekday=4) 함수를 사용하여서 당월 마지막 금요일의 일자를 반환한다. ( MonthEnd(1) - 7*Day() )가 당월 마지막 일자 1주일 이전 날짜를 반환하고, 이후 금요일 일자를 반환한다.
- Next_day_1 : pd.DateOffset(months=1) 함수를 사용하여서 입사일자의 다음달 동일한 일자를 계산하고, (MonthEnd(1) - 7*Day() + pd.DateOffset(weekday=FR(0))) 함수를 사용하여서 다음달 마지막 금요일의 일자를 산출한다.
- Next_day_2 : pd.DateOffset(months=1) 함수를 사용하여서 입사일자의 다음달 동일한 일자를 계산하고, ( MonthEnd(1) - 7Day() + 1Week(weekday=4 ) 함수를 사용하여서 다음달 마지막 금요일의 일자를 산출한다.
| Python Programming |
from pandas.tseries.offsets import Day,Week,MonthEnd
from dateutil.relativedelta import TH
from dateutil.relativedelta import FR
import copy
withmooc =copy.copy(emp)
withmooc['last_day'] = pd.to_datetime(withmooc['hiredate'], format="%Y/%m") + MonthEnd(1)
withmooc['before_day'] = pd.to_datetime(withmooc['hiredate'], format="%Y/%m") + MonthEnd(1) - 7*Day()
withmooc['week_day_1'] = pd.to_datetime(withmooc['hiredate'], format="%Y/%m") + MonthEnd(1) - 7*Day() + pd.DateOffset(weekday=FR(0)) # 경고메시지(PerformanceWarning)가 출력 됨
withmooc['week_day_2'] = pd.to_datetime(withmooc['hiredate'], format="%Y/%m") + MonthEnd(1) - 7*Day() + 1*Week(weekday=4) # 4: 금요일
withmooc['Next_day_1'] = pd.to_datetime(withmooc['hiredate'], format="%Y/%m") + pd.DateOffset(months=1)+ MonthEnd(1) - 7*Day() + pd.DateOffset(weekday=FR(0)) # 경고메시지(PerformanceWarning)가 출력 됨
withmooc['Next_day_2'] = pd.to_datetime(withmooc['hiredate'], format="%Y/%m") + pd.DateOffset(months=1)+ MonthEnd(1) - 7*Day() + 1*Week(weekday=4) # 4: 금요일
withmooc[['empno','hiredate','last_day','before_day','week_day_1','week_day_2','Next_day_1','Next_day_2']].head(7)
| Results |
C:\Users\BACK\anaconda3\lib\site-packages\pandas\core\arrays\datetimes.py:691: PerformanceWarning: Non-vectorized DateOffset being applied to Series or DatetimeIndex
warnings.warn(
| empno | hiredate | last_day | before_day | week_day_1 | week_day_2 | Next_day_1 | Next_day_2 | |
| 0 | 7369 | 1980/12/17 | 1980-12-31 | 1980-12-24 | 1980-12-26 | 1980-12-26 | 1981-01-30 | 1981-01-30 |
| 1 | 7499 | 1981/02/20 | 1981-02-28 | 1981-02-21 | 1981-02-27 | 1981-02-27 | 1981-03-27 | 1981-03-27 |
| 2 | 7521 | 1981/02/22 | 1981-02-28 | 1981-02-21 | 1981-02-27 | 1981-02-27 | 1981-03-27 | 1981-03-27 |
| 3 | 7566 | 1981/04/02 | 1981-04-30 | 1981-04-23 | 1981-04-24 | 1981-04-24 | 1981-05-29 | 1981-05-29 |
| 4 | 7654 | 1981/09/28 | 1981-09-30 | 1981-09-23 | 1981-09-25 | 1981-09-25 | 1981-10-30 | 1981-10-30 |
| 5 | 7698 | 1981/03/01 | 1981-03-31 | 1981-03-24 | 1981-03-27 | 1981-03-27 | 1981-04-24 | 1981-04-24 |
| 6 | 7782 | 1981/01/09 | 1981-01-31 | 1981-01-24 | 1981-01-30 | 1981-01-30 | 1981-02-27 | 1981-02-27 |
[정답-1] pandas.tseries.offsets 과 lambda 방식 : Week(weekday=4) 함수 사용
입사일자에서 일자(day) 부분을 추출하여서 15일 이전이면 당월 마지막 금요일의 일자를 산출하고, 15일 이후이면 다음 달 마지막 금요일의 일자를 산출한다.
| Python Programming |
from pandas.tseries.offsets import Day,Week,MonthEnd
import copy
withmooc =copy.copy(emp)
withmooc['payDATE'] = withmooc.apply(lambda row : pd.to_datetime(row['hiredate'], format="%Y/%m") + MonthEnd(1) - 6*Day() + 0*Week(weekday=4) # 4: 금요일
if pd.to_datetime(row['hiredate']).day <= 15
else pd.to_datetime(row['hiredate'], format="%Y/%m") + MonthEnd(2) - 6*Day() + 0*Week(weekday=4),
axis=1)
withmooc[['empno','hiredate','payDATE']].head(7)
| Results |
| empno | hiredate | payDATE | |
| 0 | 7369 | 1980/12/17 | 1981-01-30 |
| 1 | 7499 | 1981/02/20 | 1981-03-27 |
| 2 | 7521 | 1981/02/22 | 1981-03-27 |
| 3 | 7566 | 1981/04/02 | 1981-04-24 |
| 4 | 7654 | 1981/09/28 | 1981-10-30 |
| 5 | 7698 | 1981/03/01 | 1981-03-27 |
| 6 | 7782 | 1981/01/09 | 1981-01-30 |
[참고] relativedelta
: 현재일자(‘today’)와 현재일자 이후 가장 빠른 금요일의 날짜(‘next_friday’)를 반환한다.
| Python Programming |
import datetime as DT
import dateutil.relativedelta as REL
today = DT.date.today()
print(today)
rd = REL.relativedelta(days=1, weekday=REL.FR)
next_friday = today + rd
print(next_friday)
| Results |
2021-04-01
2021-04-02
[정답-2] lambda 함수 방식 : weekday=FR(0) 함수 사용
입사일자(‘hiredate’)에서 일자(day) 부분을 추출하여서 15일 이전이면 당월 마지막 금요일의 일자를 산출하고, 15일 이후이면 다음 달 마지막 금요일의 일자를 산출한다.
| Python Programming |
from dateutil.relativedelta import relativedelta, FR
import copy
withmooc =copy.copy(emp)
withmooc['payDATE'] = withmooc.apply(lambda row: pd.to_datetime(row['hiredate'], format='%Y %m %d') + relativedelta(day=31, days=-6, weekday=FR(0))
if pd.to_datetime(row['hiredate']).day <= 15
else pd.to_datetime(row['hiredate'], format='%Y %m %d') + relativedelta(months=1, day=31, days=-6, weekday=FR(0)),
axis=1 )
withmooc.head(7)
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | payDATE | |
| 0 | 7369 | SMITH | CLERK | 7902.0 | 1980/12/17 | 800 | NaN | 20 | 1981-01-30 |
| 1 | 7499 | ALLEN | SALESMAN | 7698.0 | 1981/02/20 | 1600 | 300.0 | 30 | 1981-03-27 |
| 2 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 | 1981-03-27 |
| 3 | 7566 | JONES | MANAGER | 7839.0 | 1981/04/02 | 2975 | NaN | 20 | 1981-04-24 |
| 4 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 | 1981-10-30 |
| 5 | 7698 | BLAKE | MANAGER | 7839.0 | 1981/03/01 | 2850 | NaN | 30 | 1981-03-27 |
| 6 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 | 1981-01-30 |
[정답] 조건문 - relativedelta과 List comprehension 방식 : weekday=FR(0) 함수 사용
입사일자(‘hiredate’)에서 일자(day) 부분을 추출하여서 15일 이전이면 당월 마지막 금요일의 일자를 산출하고, 15일 이후이면 다음 달 마지막 금요일의 일자를 산출한다.
| Python Programming |
from dateutil.relativedelta import relativedelta, FR
import copy
withmooc =copy.copy(emp)
withmooc['payDATE'] = [pd.to_datetime(x, format='%Y %m %d') + relativedelta(day=31, days=-6, weekday=FR(0))
if pd.to_datetime(x).day <= 15
else pd.to_datetime(x, format='%Y %m %d') + relativedelta(months=1, day=31, days=-6, weekday=FR(0))
for x in withmooc['hiredate']]
withmooc.head(7)
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | payDATE | |
| 0 | 7369 | SMITH | CLERK | 7902.0 | 1980/12/17 | 800 | NaN | 20 | 1981-01-30 |
| 1 | 7499 | ALLEN | SALESMAN | 7698.0 | 1981/02/20 | 1600 | 300.0 | 30 | 1981-03-27 |
| 2 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 | 1981-03-27 |
| 3 | 7566 | JONES | MANAGER | 7839.0 | 1981/04/02 | 2975 | NaN | 20 | 1981-04-24 |
| 4 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 | 1981-10-30 |
| 5 | 7698 | BLAKE | MANAGER | 7839.0 | 1981/03/01 | 2850 | NaN | 30 | 1981-03-27 |
| 6 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 | 1981-01-30 |
[정답] relativedelta과 함수 사용 방식
lambda() 함수 내에 if ~ else 조건문을 사용하여서 근무일자(‘hiredate’)의 일자(day) 부분을 기준으로 급여일을 계산하는 함수를 생성한 후, 입사일자에 set_color() 함수를 적용하여서 입사일 기준 최초 급여 수령 일자를 출력한다.
| Python Programming |
withmooc = copy.copy(emp)
withmooc
def set_color(row):
if pd.to_datetime(row).day <= 15 :
chg_day = pd.to_datetime(row, format='%Y %m %d') + relativedelta(day=31, days=-6, weekday=FR(0))
else:
chg_day = pd.to_datetime(row, format='%Y %m %d') + relativedelta(months=1, day=31, days=-6, weekday=FR(0))
return chg_day
# withmooc = withmooc.assign(payDATE=withmooc['hiredate'].apply(set_color)) # assign
withmooc['payDATE'] = withmooc['hiredate'].apply(lambda x : set_color(x))
withmooc.head(7)
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | payDATE | |
| 0 | 7369 | SMITH | CLERK | 7902.0 | 1980/12/17 | 800 | NaN | 20 | 1981-01-30 |
| 1 | 7499 | ALLEN | SALESMAN | 7698.0 | 1981/02/20 | 1600 | 300.0 | 30 | 1981-03-27 |
| 2 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 | 1981-03-27 |
| 3 | 7566 | JONES | MANAGER | 7839.0 | 1981/04/02 | 2975 | NaN | 20 | 1981-04-24 |
| 4 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 | 1981-10-30 |
| 5 | 7698 | BLAKE | MANAGER | 7839.0 | 1981/03/01 | 2850 | NaN | 30 | 1981-03-27 |
| 6 | 7782 | CLARK | MANAGER | 7839.0 | 1981/01/09 | 2450 | NaN | 10 | 1981-01-30 |
3. R Programming (R Package)
- if~else 구문은 날짜형을 수치형으로 반환
[참고] 행렬 또는 데이터프레임의 모든 행에 일괄적으로 함수 적용하기
: [링크] Apply a function to every row of a matrix or a data frame
| R Programming |
%%R
a = data.frame(t(iris[1:10,1:3]))
b = iris[1:10,1:3]
print(a)
print(b)
print( apply(b, 1 , prod) ) # 관측치 행별 변수의 곱
print( vapply(a, prod, 0) ) # 열기준 관측치의 곱
print( sapply(a, prod) ) # 열기준 관측치의 곱
print( apply(iris[1:10,1:3], 1 , prod) ) # 관측치 행별 변수의 곱
print( vapply(data.frame(t(iris[1:10,1:3])), prod, 0) ) # 열기준 관측치의 곱
print( sapply(data.frame(t(iris[1:10,1:3])), prod) ) # 열기준 관측치의 곱
| Results |
X1 X2 X3 X4 X5 X6 X7 X8 X9 X10
Sepal.Length 5.1 4.9 4.7 4.6 5.0 5.4 4.6 5.0 4.4 4.9
Sepal.Width 3.5 3.0 3.2 3.1 3.6 3.9 3.4 3.4 2.9 3.1
Petal.Length 1.4 1.4 1.3 1.5 1.4 1.7 1.4 1.5 1.4 1.5
Sepal.Length Sepal.Width Petal.Length
1 5.1 3.5 1.4
2 4.9 3.0 1.4
3 4.7 3.2 1.3
4 4.6 3.1 1.5
5 5.0 3.6 1.4
6 5.4 3.9 1.7
7 4.6 3.4 1.4
8 5.0 3.4 1.5
9 4.4 2.9 1.4
10 4.9 3.1 1.5
1 2 3 4 5 6 7 8 9 10
24.990 20.580 19.552 21.390 25.200 35.802 21.896 25.500 17.864 22.785
X1 X2 X3 X4 X5 X6 X7 X8 X9 X10
24.990 20.580 19.552 21.390 25.200 35.802 21.896 25.500 17.864 22.785
X1 X2 X3 X4 X5 X6 X7 X8 X9 X10
24.990 20.580 19.552 21.390 25.200 35.802 21.896 25.500 17.864 22.785
1 2 3 4 5 6 7 8 9 10
24.990 20.580 19.552 21.390 25.200 35.802 21.896 25.500 17.864 22.785
X1 X2 X3 X4 X5 X6 X7 X8 X9 X10
24.990 20.580 19.552 21.390 25.200 35.802 21.896 25.500 17.864 22.785
X1 X2 X3 X4 X5 X6 X7 X8 X9 X10
24.990 20.580 19.552 21.390 25.200 35.802 21.896 25.500 17.864 22.785
if ~ else 조건문을 사용하여서 근무일자(‘hiredate’)의 일자(day) 부분을 기준으로 급여일을 계산하는 사용자 정의 함수(SalPay.function)를 생성한 후, 입사일자에 SalPay.function 함수를 적용하여서 입사일 기준 최초 급여 수령 일자를 출력한다.
- rollback 함수 : 기준일자 이전 월의 마지막 날짜를 반환한다.
- lubridate::ceiling_date : 기준일자를 사용자가 지정한 올림 기준 인수에 따라 올림하여서 해당 날짜를 반환한다.
- 첫 번째 조건문 : 당월 마지막 일자에서 1주일 이전 날짜를 검색하고, 이 날짜를 기준으로 이후 가장 가까운 금요일로 날짜를 올림하여 해당일자를 반환한다. (week_start : 7 = Sunday, 1 = Monday, 5 = Friday)
- 두 번째 조건문 : 다음달 마지막 일자에서 1주일 이전 날짜를 검색하고, 이 날짜를 기준으로 이후 가장 가까운 금요일로 날짜를 올림하여 해당일자를 반환한다. (week_start : 7 = Sunday, 1 = Monday, 5 = Friday)
- ifelse은 날짜형(date)을 수치형(num)으로 반환하기 때문에 계산된 급여 수령 일자(‘payDATE’)의 데이터 자료형을 입사일자(‘hiredate’)의 데이터 자료형인 날짜(‘date’)로 변환한다.
- [링크] TRUNC(date) Oracle Function [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table]
- [링크] ROUND(date) Oracle Function [Oracle, Pandas, R Prog, Dplyr, Sqldf, Pandasql, Data.Table]
| R Programming |
%%R
library(lubridate)
withmooc <- emp
SalPay.function <- function(x) {
if (lubridate::day(x) <= 15)
{
var=(lubridate::ceiling_date( (lubridate::rollback(x + months(1)) - days(7)) , unit = "weeks" ,week_start=5))
return (var)
} else {
var=(lubridate::ceiling_date( (lubridate::rollback(x + months(2)) - days(7)) , unit = "weeks" ,week_start=5))
return (var)
}
}
# withmooc$payDATE <- vapply(withmooc$hiredate, FUN=SalPay.function,0)
withmooc$payDATE <- sapply(withmooc$hiredate, FUN=SalPay.function)
class(withmooc$payDATE) <- class(withmooc$hiredate) # payDATE에 hiredate의 날짜형을 지정
withmooc[1:7, ]
| Results |
# A tibble: 7 x 9
empno ename job mgr hiredate sal comm deptno payDATE
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <date>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1981-01-30
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-03-27
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-03-27
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-04-24
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981-10-30
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981-03-27
7 7782 CLARK MANAGER 7839 1981-01-09 2450 NA 10 1981-01-30
ifelse 조건문을 사용하여서 입사일자(‘hiredate’)에서 일자(day) 부분을 추출하여서 15일 이전이면 당월 마지막 금요일의 일자를 산출하고, 15일 이후이면 다음 달 마지막 금요일의 일자를 산출한다.
- ifelse은 날짜형(date)을 수치형(num)으로 반환하기 때문에 계산된 급여 수령 일자(‘payDATE’)의 데이터 자료형을 입사일자(‘hiredate’)의 데이터 자료형인 날짜(‘date’)로 변환한다.
| R Programming |
%%R
withmooc <- emp
withmooc['payDATE'] = base::ifelse(day(withmooc$hiredate) <= 15 ,(lubridate::ceiling_date( (rollback(withmooc$hiredate + months(1)) - days(7)) , unit = "weeks" ,week_start=5)),
ifelse(day(withmooc$hiredate) > 15, (lubridate::ceiling_date( (rollback(withmooc$hiredate + months(2)) - days(7)) , unit = "weeks" ,week_start=5)),
NA))
#str(withmooc) # 데이터 구조 확인
class(withmooc$payDATE) <- class(withmooc$hiredate) # 날짜형 지정
withmooc[1:5, ]
| Results |
# A tibble: 5 x 9
empno ename job mgr hiredate sal comm deptno payDATE
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <date>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1981-01-30
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-03-27
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-03-27
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-04-24
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981-10-30
dplyr::if_else 조건문을 사용하여서 입사일자(‘hiredate’)에서 일자(day) 부분을 추출하여서 15일 이전이면 당월 마지막 금요일의 일자를 산출하고, 15일 이후이면 다음 달 마지막 금요일의 일자를 산출한다.
| R Programming |
%%R
withmooc <- emp
withmooc['payDATE'] = dplyr::if_else(day(withmooc$hiredate) <= 15 ,(ceiling_date( (rollback(withmooc$hiredate + months(1)) - days(7)) , unit = "weeks" ,week_start=5)),
dplyr::if_else(day(withmooc$hiredate) > 15, (ceiling_date( (rollback(withmooc$hiredate + months(2)) - days(7)) , unit = "weeks" ,week_start=5)),
ymd(NA)))
withmooc[1:5, ]
| Results |
# A tibble: 5 x 9
empno ename job mgr hiredate sal comm deptno payDATE
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <date>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1981-01-30
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-03-27
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-03-27
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-04-24
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981-10-30
data.table::fifelse 조건문을 사용하여서 입사일자(‘hiredate’)에서 일자(day) 부분을 추출하여서 15일 이전이면 당월 마지막 금요일의 일자를 산출하고, 15일 이후이면 다음 달 마지막 금요일의 일자를 산출한다.
| R Programming |
%%R
withmooc <- emp
withmooc['payDATE'] = data.table::fifelse(day(withmooc$hiredate) <= 15 ,(ceiling_date( (rollback(withmooc$hiredate + months(1)) - days(7)) , unit = "weeks" ,week_start=5)),
data.table::fifelse(day(withmooc$hiredate) > 15, (ceiling_date( (rollback(withmooc$hiredate + months(2)) - days(7)) , unit = "weeks" ,week_start=5)),
ymd(NA)))
withmooc[1:5, ]
| Results |
# A tibble: 5 x 9
empno ename job mgr hiredate sal comm deptno payDATE
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <date>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1981-01-30
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-03-27
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-03-27
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-04-24
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981-10-30
4. R Dplyr Package
dplyr::if_else 조건문을 사용하여서 입사일자(‘hiredate’)에서 일자(day) 부분을 추출하여서 15일 이전이면 당월 마지막 금요일의 일자를 산출하고, 15일 이후이면 다음 달 마지막 금요일의 일자를 산출한다.
| R Programming |
%%R
withmooc <- emp
withmooc %>%
mutate(payDATE = dplyr::if_else(day(hiredate) <= 15 ,(ceiling_date( (rollback(hiredate + months(1)) - days(7)) , unit = "weeks" ,week_start=5)),
dplyr::if_else(day(hiredate) > 15, (ceiling_date( (rollback(hiredate + months(2)) - days(7)) , unit = "weeks" ,week_start=5)),
ymd(NA)))
) %>% head()
| Results |
# A tibble: 6 x 9
empno ename job mgr hiredate sal comm deptno payDATE
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <date>
1 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1981-01-30
2 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-03-27
3 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-03-27
4 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-04-24
5 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981-10-30
6 7698 BLAKE MANAGER 7839 1981-03-01 2850 NA 30 1981-03-27
5. R sqldf Package
입사일자에서 일자 부분을 추출하여서 15일 이전이면 당월 마지막 금요일의 일자를 산출하고, 15일 이후이면 다음 달 마지막 금요일의 일자를 산출한다.
- ‘weekday 5’ : 다음 금요일 (day of week 0-6 with Sunday==0)
| R Programming |
%%R
sqldf("SELECT hiredate,
strftime('%d', datetime(hiredate * 3600 * 24,'unixepoch')) this_day,
strftime('%w', datetime(hiredate * 3600 * 24,'unixepoch')) week_day,
date(datetime(hiredate * 3600 * 24,'unixepoch'), 'localtime','+1 months','start of month', '-1 days') last_day,
case when strftime('%d', datetime(hiredate * 3600 * 24,'unixepoch')) <= '15'
then date(datetime(hiredate * 3600 * 24,'unixepoch'), 'localtime','+1 months','start of month', '-7 days', 'weekday 5')
else date(datetime(hiredate * 3600 * 24,'unixepoch'), 'localtime','+2 months','start of month', '-7 days', 'weekday 5') end payDATE
FROM emp
order by substr(HIREDATE,7,2);") %>% head()
| Results |
hiredate this_day week_day last_day payDATE
1 1980-12-17 17 3 1980-12-31 1981-01-30
2 1981-02-20 20 5 1981-02-28 1981-03-27
3 1981-02-22 22 0 1981-02-28 1981-03-27
4 1981-04-02 02 4 1981-04-30 1981-04-24
5 1981-09-28 28 1 1981-09-30 1981-10-30
6 1981-03-01 01 0 1981-03-31 1981-03-27
7 1981-01-09 09 5 1981-01-31 1981-01-30
8 1982-12-09 09 4 1982-12-31 1982-12-31
9 1981-11-17 17 2 1981-11-30 1981-12-25
10 1981-09-08 08 2 1981-09-30 1981-09-25
11 1983-01-12 12 3 1983-01-31 1983-01-28
12 1981-12-03 03 4 1981-12-31 1981-12-25
13 1981-12-03 03 4 1981-12-31 1981-12-25
14 1982-01-23 23 6 1982-01-31 1982-02-26
6. Python pandasql Package
| Python Programming |
ps.sqldf(" SELECT hiredate, \
case when strftime('%d', date(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2))) <= '15' \
then date(date(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2)), 'localtime','+1 months','start of month', '-7 days', 'weekday 5') \
else date(date(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2)), 'localtime','+2 months','start of month', '-7 days', 'weekday 5') end payDATE \
FROM emp \
order by substr(HIREDATE,7,2); ").head()
| Results |
| hiredate | payDATE | |
| 0 | 1981/01/09 | 1981-01-30 |
| 1 | 1981/11/17 | 1981-12-25 |
| 2 | 1983/01/12 | 1983-01-28 |
| 3 | 1982/01/23 | 1982-02-26 |
| 4 | 1980/12/17 | 1981-01-30 |
7. R data.table Package
data.table::fifelse 조건문을 사용하여서 입사일자(‘hiredate’)에서 일자(day) 부분을 추출하여서 15일 이전이면 당월 마지막 금요일의 일자를 산출하고, 15일 이후이면 다음 달 마지막 금요일의 일자를 산출한다.
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
salgrade_DT <- data.table(salgrade)
DT[, payDATE := dplyr::if_else(day(hiredate) <= 15 ,(ceiling_date( (rollback(hiredate + months(1)) - days(7)) , unit = "weeks" ,week_start=5)),
dplyr::if_else(day(hiredate) > 15, (ceiling_date( (rollback(hiredate + months(2)) - days(7)) , unit = "weeks" ,week_start=5)),
ymd(NA))),][1:5, ]
| Results |
empno ename job mgr hiredate sal comm deptno payDATE
1: 7369 SMITH CLERK 7902 1980-12-17 800 NA 20 1981-01-30
2: 7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30 1981-03-27
3: 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 1981-03-27
4: 7566 JONES MANAGER 7839 1981-04-02 2975 NA 20 1981-04-24
5: 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 1981-10-30
8. SAS Proc SQL
| SAS Programming |
%%SAS sas
data back;
format week_sun week_mon week_tue week_wed week_thu week_fri week_sat yymmddn8.;
week_sun = intnx( 'week.1', mdy(4,11,2021) , 1);
week_mon = intnx( 'week.2', mdy(4,11,2021) , 1);
week_tue = intnx( 'week.3', mdy(4,11,2021) , 1);
week_wed = intnx( 'week.4', mdy(4,11,2021) , 1);
week_thu = intnx( 'week.5', mdy(4,11,2021) , 1);
week_fri = intnx( 'week.6', mdy(4,11,2021) , 1);
week_sat = intnx( 'week.7', mdy(4,11,2021) , 1);
run;
PROC SQL;
CREATE TABLE STATSAS_1 AS
Select empno,
hiredate,
day(hiredate) as hiredate_day,
intnx('week',hiredate,1) as week_day format=yymmddn8.,
intnx ('month', hiredate, 0, 'e' ) as last_day format=yymmddn8.,
case when day(hiredate) <= 15
then intnx( 'week.6', intnx ( 'month', hiredate, 0, 'e' ) , 0)
else intnx( 'week.6', intnx ( 'month', hiredate, +1, 'e' ) , 0) end as payDATE format=yymmddn8.
from emp e;
QUIT;
PROC PRINT data=STATSAS_1(obs=5);RUN;
| Results |
| OBS | empno | hiredate | hiredate_day | week_day | last_day | payDATE |
| 1 | 7369 | 1980-12-17 | 17 | 19801221 | 19801231 | 19810130 |
| 2 | 7499 | 1981-02-20 | 20 | 19810222 | 19810228 | 19810327 |
| 3 | 7521 | 1981-02-22 | 22 | 19810301 | 19810228 | 19810327 |
| 4 | 7566 | 1981-04-02 | 2 | 19810405 | 19810430 | 19810424 |
| 5 | 7654 | 1981-09-28 | 28 | 19811004 | 19810930 | 19811030 |
9. SAS Data Step
| SAS Programming |
%%SAS sas
DATA STATSAS_2;
set emp;
hiredate_day = day(hiredate);
format week_day last_day payDATE yymmddn8.;
week_day = intnx('week',hiredate,1);
last_day = intnx ('month', hiredate, 0, 'e' );
if day(hiredate) <= 15 then payDATE = intnx( 'week.6', intnx ( 'month', hiredate, 0, 'e' ) , 0);
else payDATE = intnx( 'week.6', intnx ( 'month', hiredate, +1, 'e' ) , 0);
keep empno hiredate: week_day last_day payDATE;
RUN;
PROC PRINT data=STATSAS_2(obs=5);RUN;
| Results |
| OBS | empno | hiredate | hiredate_day | week_day | last_day | payDATE |
| 1 | 7369 | 1980-12-17 | 17 | 19801221 | 19801231 | 19810130 |
| 2 | 7499 | 1981-02-20 | 20 | 19810222 | 19810228 | 19810327 |
| 3 | 7521 | 1981-02-22 | 22 | 19810301 | 19810228 | 19810327 |
| 4 | 7566 | 1981-04-02 | 2 | 19810405 | 19810430 | 19810424 |
| 5 | 7654 | 1981-09-28 | 28 | 19811004 | 19810930 | 19811030 |
10. Python Dfply Package
| Python Programming |
from pandas.tseries.offsets import Day,Week,MonthEnd
from dateutil.relativedelta import TH
from dateutil.relativedelta import FR
import copy
emp >> \
select(X.empno,X.hiredate) >> \
mutate( last_day = make_symbolic(pd.to_datetime)(X.hiredate, format="%Y/%m") + MonthEnd(1) ,
before_day = make_symbolic(pd.to_datetime)(X.hiredate, format="%Y/%m") + MonthEnd(1) - 7*Day(),
week_day_1 = make_symbolic(pd.to_datetime)(X.hiredate, format="%Y/%m") + MonthEnd(1) - 7*Day() + pd.DateOffset(weekday=FR(0)) , # 경고메시지(PerformanceWarning)가 출력 됨
week_day_2 = make_symbolic(pd.to_datetime)(X.hiredate, format="%Y/%m") + MonthEnd(1) - 7*Day() + 1*Week(weekday=4) , # 4: 금요일
Next_day_1 = make_symbolic(pd.to_datetime)(X.hiredate, format="%Y/%m") + pd.DateOffset(months=1)+ MonthEnd(1) - 7*Day() + pd.DateOffset(weekday=FR(0)), # 경고메시지(PerformanceWarning)가 출력 됨
Next_day_2 = make_symbolic(pd.to_datetime)(X.hiredate, format="%Y/%m") + pd.DateOffset(months=1)+ MonthEnd(1) - 7*Day() + 1*Week(weekday=4) # 4: 금요일
) >> \
head()
| Results |
C:\Users\BACK\anaconda3\lib\site-packages\pandas\core\arrays\datetimes.py:691: PerformanceWarning: Non-vectorized DateOffset being applied to Series or DatetimeIndex
warnings.warn(| empno | hiredate | last_day | before_day | week_day_1 | week_day_2 | Next_day_1 | Next_day_2 | |
| 0 | 7369 | 1980/12/17 | 1980-12-31 | 1980-12-24 | 1980-12-26 | 1980-12-26 | 1981-01-30 | 1981-01-30 |
| 1 | 7499 | 1981/02/20 | 1981-02-28 | 1981-02-21 | 1981-02-27 | 1981-02-27 | 1981-03-27 | 1981-03-27 |
| 2 | 7521 | 1981/02/22 | 1981-02-28 | 1981-02-21 | 1981-02-27 | 1981-02-27 | 1981-03-27 | 1981-03-27 |
| 3 | 7566 | 1981/04/02 | 1981-04-30 | 1981-04-23 | 1981-04-24 | 1981-04-24 | 1981-05-29 | 1981-05-29 |
| 4 | 7654 | 1981/09/28 | 1981-09-30 | 1981-09-23 | 1981-09-25 | 1981-09-25 | 1981-10-30 | 1981-10-30 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
반응형
댓글