[날짜 함수] 날짜 함수를 사용한 기준월 사이의 기간(Gap) 계산

포스팅 목차

123. Display name of those employees who are going to retire 31-dec-99. If the maximum job is period is 18 years?

* 1999년 12월 31일 일자로 퇴직자 명단을 출력하시오. 퇴직자는 근무기간이 18년을 초과하는 직원.

파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
날짜 :12 / 13 / 73 / 123번
[날짜 함수]날짜 함수를 사용한 기준월 사이의 기간(Gap) 계산
데이터 전처리 - SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql

Oracle : To_date()
파이썬 Pandas : apply(), Lambda 함수, relativedelta.relativedelta(), dt.todatetime(), dt.days
R 프로그래밍 : difftime(), as.Date()
R Dplyr Package : difftime(), as.Date()
R sqldf Package : strftime() 날짜 포맷, round()
Python pandasql Package : strftime() 날짜 포맷, round(), typeof() 변수 타입 확인
R data.table Package : difftime(), as.Date()
SAS Proc SQL : intck(), Input 함수(문자형 날짜를 날짜형으로), Year(), Ceil(), YRDIF(), Datdif()
SAS Data Step : intck(), Input 함수(문자형 날짜를 날짜형으로), Year(), Ceil(), YRDIF(), Datdif()
Python Dfply Package : @make_symbolic & def 사용자 정의 함수(to_datetime), dt.datetime()
파이썬 Base 프로그래밍 :

1. Oracle(오라클)

입사일과 1999년 12월 31일 사이의 날짜 차이를 계산 후 365일로 나눈 년수가 18년을 초과하는 직원들을 선택한다.

Oracle Programming

select * 
from   emp 
where  (to_date('31-dec-1999')-hiredate)/365>18;

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
8	7839	KING	PRESIDENT	NaN	1981/11/17	5000	NaN	10

2. Python Pandas(파이썬)

relativedelta 함수를 사용하여서 입사일자와 기준일자(1999년 12월 31일) 사이의 경과 년수가 18년을 초과하는 직원들의 정보를 출력한다.

Python Programming

import datetime as dt
import dateutil.relativedelta as relativedelta

emp[emp.apply(lambda row: relativedelta.relativedelta( dt.datetime(1999, 12, 31), 
                                                        pd.to_datetime(row['hiredate'], format='%Y %m %d')).years, axis=1) >= 18].head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30

입사일자와 기준일자(1999년 12월 31일)의 일자 차이를 계산 한 후 18년 * 365일 보다 큰 직원들의 정보를 출력한다.

Python Programming

emp[ (dt.datetime(1999, 12, 31) - pd.to_datetime(emp['hiredate'])).dt.days > 18*365 ].head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30

3. R Programming (R Package)

입사일자와 기준일자(1999년 12월 31일)의 일자 차이를 계산 한 후 18년 * 365일 보다 큰 직원들의 정보를 출력한다.

R Programming

%%R

emp[ difftime(as.Date('1999-12-31'), emp$hiredate) / 365 > 18 , ] %>% head()

Results

# A tibble: 6 x 8
  empno ename  job        mgr hiredate     sal  comm deptno
  <dbl> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl>
1  7369 SMITH  CLERK     7902 1980-12-17   800    NA     20
2  7499 ALLEN  SALESMAN  7698 1981-02-20  1600   300     30
3  7521 WARD   SALESMAN  7698 1981-02-22  1250   500     30
4  7566 JONES  MANAGER   7839 1981-04-02  2975    NA     20
5  7654 MARTIN SALESMAN  7698 1981-09-28  1250  1400     30
6  7698 BLAKE  MANAGER   7839 1981-03-01  2850    NA     30

4. R Dplyr Package

difftime 함수를 사용하여서 입사일자와 기준일자(1999년 12월 31일)의 일자 차이를 계산 한 후 365일로 나눈 년수가 18년을 초과하는 직원들을 선택한다.

R Programming

%%R

emp %>% 
  filter( difftime(as.Date('1999-12-31'), hiredate) / 365 > 18 ) %>%
  head()

Results

# A tibble: 6 x 8
  empno ename  job        mgr hiredate     sal  comm deptno
  <dbl> <chr>  <chr>    <dbl> <date>     <dbl> <dbl>  <dbl>
1  7369 SMITH  CLERK     7902 1980-12-17   800    NA     20
2  7499 ALLEN  SALESMAN  7698 1981-02-20  1600   300     30
3  7521 WARD   SALESMAN  7698 1981-02-22  1250   500     30
4  7566 JONES  MANAGER   7839 1981-04-02  2975    NA     20
5  7654 MARTIN SALESMAN  7698 1981-09-28  1250  1400     30
6  7698 BLAKE  MANAGER   7839 1981-03-01  2850    NA     30

5. R sqldf Package

입사일자와 기준일자(1999년 12월 31일)의 경과 기간을 초단위로 계산 후 다시 년도로 재환산 하여서 경과 년수가 18년을 초과하는 직원들을 선택한다. (year_gap 확인)

현재 hiredate가 “real” 형태로 저장되어 있음. / sqlite에는 날짜형이 존재 안 함 (129번 sqldf)

R Programming

 - base_date_1 : 1970년 1월 1일 0시를 기준으로 초를 카운트하여 날짜를 계산
 - base_date_2 : 1970년 1월 1일 자정 이후 86400초(24시*60분*60초)가 경과되었다.
 - year_gap    : 입사일자와 기준일자(1999년 12월 31일) 차이를 초 단위로 계산 후 다시 년도로 재환산 한다. (날짜 형태로 변환 후 비교)
 - year_gap2   : 입사일자와 기준일자(1999년 12월 31일)의 년도를 계산 후 년도의 차이를 계산한다.

%%R

sqldf("select empno,
              ename,
              strftime('%s','1970-01-01')                       base_date_1,
              strftime('%s','1970-01-02')                       base_date_2,
              strftime('%s', hiredate * 3600 * 24, 'unixepoch') chg_date,
              ( round(strftime('%s','1999-12-31') - strftime('%s', hiredate * 3600 * 24, 'unixepoch')) /60/60/24)/365 year_gap,
              strftime('%Y', hiredate * 3600 * 24, 'unixepoch')                                year,
              strftime('%Y','1999-12-31')                                                      year1,
              strftime('%Y','1999-12-31') - strftime('%Y', hiredate * 3600 * 24, 'unixepoch')  year_gap2
       from emp
       where ( round(strftime('%s','1999-12-31') - strftime('%s', hiredate * 3600 * 24, 'unixepoch')) /60/60/24)/365 > 18") %>% head()

Results

  empno  ename base_date_1 base_date_2  chg_date year_gap year year1 year_gap2
1  7369  SMITH           0       86400 345859200 19.04932 1980  1999        19
2  7499  ALLEN           0       86400 351475200 18.87123 1981  1999        18
3  7521   WARD           0       86400 351648000 18.86575 1981  1999        18
4  7566  JONES           0       86400 355017600 18.75890 1981  1999        18
5  7654 MARTIN           0       86400 370483200 18.26849 1981  1999        18
6  7698  BLAKE           0       86400 352252800 18.84658 1981  1999        18

6. Python pandasql Package

hiredate가 텍스트 형태로 입력되어 있어서 날짜형으로 변환 후 기준일자와의 일자 차이를 계산 후 경과 년수가 18년을 초과하는 직원 정보를 출력한다.

Python Programming

ps.sqldf(" select empno,ename,                            \
                  strftime('%s','1999-12-31') base_date,  \
                  ROUND( strftime('%s','1999-12-31') - strftime('%s',(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2))) ) /60/60/24    day_gap, \
                  (ROUND( strftime('%s','1999-12-31') - strftime('%s',(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2))) ) /60/60/24)/365 year_gap, \
                  strftime('%Y/%m/%d','1999-12-31')- hiredate year_gap, \
                  typeof(hiredate) date_type                            \
           from emp \
           where (ROUND( strftime('%s','1999-12-31') - strftime('%s',(substr(hiredate,1,4)||'-'||substr(hiredate,6,2)||'-'||substr(hiredate,9,2))) ) /60/60/24)/365 > 18").head()

Results

	empno	ename	base_date	day_gap	year_gap	year_gap	date_type
0	7369	SMITH	946598400	6953.0	19.049315	19	text
1	7499	ALLEN	946598400	6888.0	18.871233	18	text
2	7521	WARD	946598400	6886.0	18.865753	18	text
3	7566	JONES	946598400	6847.0	18.758904	18	text
4	7654	MARTIN	946598400	6668.0	18.268493	18	text

7. R data.table Package

입사일자와 기준일자(1999년 12월 31일)의 일자 차이를 계산 한 후 365일로 나눈 년수가 18년을 초과하는 직원들을 선택한다.

R Programming

%%R

DT          <- data.table(emp)

DT[ (difftime(as.Date('1999-12-31'), emp$hiredate) / 365 > 18), ][1:5, ]

Results

   empno  ename      job  mgr   hiredate  sal comm deptno
1:  7369  SMITH    CLERK 7902 1980-12-17  800   NA     20
2:  7499  ALLEN SALESMAN 7698 1981-02-20 1600  300     30
3:  7521   WARD SALESMAN 7698 1981-02-22 1250  500     30
4:  7566  JONES  MANAGER 7839 1981-04-02 2975   NA     20
5:  7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400     30

8. SAS Proc SQL

입사일과 1999년 12월 31일 사이의 날짜 차이를 계산 후 365일로 나눈 년수가 18년을 초과하는 직원들을 선택한다.

12/74번(그대로) ;

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select empno,
           ename,
           intck('year', hiredate, input('19991231',yymmdd8.))                 AS YEAR_GAP_1,
           year(input('19991231',yymmdd8.)) - year(hiredate)                   AS YEAR_GAP_2,
           CEIL( YRDIF(hiredate,input('19991231',yymmdd8.), 'ACT/ACT') )       AS YEAR_GAP_3,
           CEIL(DATDIF(hiredate,input('19991231',yymmdd8.), 'ACT/ACT') / 365 ) AS YEAR_GAP_4
    from   emp A
    WHERE  YRDIF(hiredate,input('19991231',yymmdd8.), 'ACT/ACT') > 18;
QUIT;
PROC PRINT data=STATSAS_1(obs=5);RUN;

Results

OBS	empno	ename	YEAR_GAP_1	YEAR_GAP_2	YEAR_GAP_3	YEAR_GAP_4
1	7369	SMITH	19	19	20	20
2	7499	ALLEN	18	18	19	19
3	7521	WARD	18	18	19	19
4	7566	JONES	18	18	19	19
5	7654	MARTIN	18	18	19	19

9. SAS Data Step

SAS Programming

%%SAS sas

DATA STATSAS_2;
 SET EMP;
     Day_Gap_1 = input('19991231',yymmdd8.) - hiredate;
     Day_gap_2 = datdif(hiredate, input('19991231',yymmdd8.), 'act/act');

     YEAR_GAP_1 = intck('year', hiredate, input('19991231',yymmdd8.));
     YEAR_GAP_2 = year(input('19991231',yymmdd8.)) - year(hiredate);
     YEAR_GAP_3 = CEIL( YRDIF(hiredate,input('19991231',yymmdd8.), 'ACT/ACT') );
     YEAR_GAP_4 = CEIL(DATDIF(hiredate,input('19991231',yymmdd8.), 'ACT/ACT') / 365 );

     KEEP ENAME EMPNO DAY_: YEAR_:;
     WHERE YRDIF(hiredate,input('19991231',yymmdd8.), 'ACT/ACT') > 18;
RUN;
PROC PRINT data=STATSAS_2(obs=5);RUN;

Results

OBS	empno	ename	Day_Gap_1	Day_Gap_2	YEAR_GAP_1	YEAR_GAP_2	YEAR_GAP_3	YEAR_GAP_4
1	7369	SMITH	6953	6953	19	19	20	20
2	7499	ALLEN	6888	6888	18	18	19	19
3	7521	WARD	6886	6886	18	18	19	19
4	7566	JONES	6847	6847	18	18	19	19
5	7654	MARTIN	6668	6668	18	18	19	19

10. Python Dfply Package

to_datetime() 사용자 함수 생성

https://github.com/kieferk/dfply/issues/27

mutate does not work with pandas.to_datetime · Issue #27 · kieferk/dfply

I have a DataFrame for which hub2['time'] = pd.to_datetime(hub2.timestamp) works, but when I write hub2 >> mutate(time=pd.to_datetime(X.timestamp)) I get the error Traceback (most rec...

github.com

Python Programming

import datetime as dt

@make_symbolic
def to_datetime(series):
    return pd.to_datetime(series, format='%Y %m %d')

emp >> filter_by( (dt.datetime(1999, 12, 31) - to_datetime ( X.hiredate )).dt.days > 18*365 ) >> head()

Results

	empno	ename	job	mgr	hiredate	sal	comm	deptno
0	7369	SMITH	CLERK	7902.0	1980/12/17	800	NaN	20
1	7499	ALLEN	SALESMAN	7698.0	1981/02/20	1600	300.0	30
2	7521	WARD	SALESMAN	7698.0	1981/02/22	1250	500.0	30
3	7566	JONES	MANAGER	7839.0	1981/04/02	2975	NaN	20
4	7654	MARTIN	SALESMAN	7698.0	1981/09/28	1250	1400.0	30

relativedelta.relativedelta() 함수를 dfply에서 직접 계산 구현 못해서 Pandas에서 구현 후 bind_cols() 함수로 추가

Python Programming

import datetime as dt
import dateutil.relativedelta as relativedelta

@make_symbolic
def to_datetime(series):
    return pd.to_datetime(series, format='%Y %m %d')


# emp.assign(year_gap= emp.apply(lambda row: relativedelta.relativedelta( dt.datetime(1999, 12, 31), 
#                                                                         pd.to_datetime(row['hiredate'], format='%Y %m %d')).years, axis=1) )


emp >> mutate( base_date = dt.datetime(1999, 12, 31), hire_date = to_datetime ( X.hiredate ) )  >> \
  bind_cols ( emp.assign(year_gap= emp.apply(lambda row: relativedelta.relativedelta( dt.datetime(1999, 12, 31), 
                                                         pd.to_datetime(row['hiredate'], format='%Y %m %d')).years, axis=1) ) >> select(X.year_gap) ) >> \
  filter_by(X.year_gap >= 18 )             >>\
  select(~X.job, ~X.mgr, ~X.deptno)        >>\
  head()

Results

	empno	ename	hiredate	sal	comm	base_date	hire_date	year_gap
0	7369	SMITH	1980/12/17	800	NaN	1999-12-31	1980-12-17	19
1	7499	ALLEN	1981/02/20	1600	300.0	1999-12-31	1981-02-20	18
2	7521	WARD	1981/02/22	1250	500.0	1999-12-31	1981-02-22	18
3	7566	JONES	1981/04/02	2975	NaN	1999-12-31	1981-04-02	18
4	7654	MARTIN	1981/09/28	1250	1400.0	1999-12-31	1981-09-28	18

[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트

저작자표시 비영리 변경금지

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

[문자 함수] 특정 문자열의 길이를 측정 - Length 함수 - 125 (오라클 SQL, R, Python, SAS) (0)	2022.12.14
[수치형 함수] 나머지 반환 함수-Mod 함수 - 124 (오라클 SQL, R, Python, SAS) (0)	2022.12.14
[비상관 서브쿼리(최대값)] 특정 조건을 만족하는 데이터 추출 - 결측치 처리 - 122 (오라클 SQL, R, Python, SAS) (0)	2022.12.13
[비상관 서브쿼리(최대값, 최소값)] 특정 조건을 만족하는 데이터 추출 - 121 (오라클 SQL, R, Python, SAS) (0)	2022.12.12
[비상관 서브쿼리(Not in 연산자)] 특정 조건에 해당하는 데이터 삭제 - 120 (오라클 SQL, R, Python, SAS) (0)	2022.12.11

[날짜 함수] 날짜 함수를 사용한 기준월 사이의 기간(Gap) 계산 - 123 (오라클 SQL, R, Python, SAS)

123. Display name of those employees who are going to retire 31-dec-99. If the maximum job is period is 18 years?

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

10. Python Dfply Package

to_datetime() 사용자 함수 생성

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

댓글

티스토리툴바

[날짜 함수] 날짜 함수를 사용한 기준월 사이의 기간(Gap) 계산 - 123 (오라클 SQL, R, Python, SAS)

123. Display name of those employees who are going to retire 31-dec-99. If the maximum job is period is 18 years?

1. Oracle(오라클)

2. Python Pandas(파이썬)

3. R Programming (R Package)

4. R Dplyr Package

5. R sqldf Package

6. Python pandasql Package

7. R data.table Package

8. SAS Proc SQL

9. SAS Data Step

10. Python Dfply Package

to_datetime() 사용자 함수 생성

'통계프로그램 비교 시리즈 > 프로그래밍비교(Oracle,Python,R,SAS)' 카테고리의 다른 글

관련글

댓글

티스토리툴바