[데이터 전처리- 날짜함수 예제] 날짜 함수를 사용한 연령계산(나이계산) & Round 함수(반올림 함수) - 74

 

74. Display your age in days.

 

* 현재 날짜(오늘 날짜) 기준으로 나이를 계산하시오.

---

• 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크

• [날짜 함수]날짜 함수를 사용한 연령계산(나이계산) & Round 함수(반올림 함수)

Oracle : Round 함수 파이썬 Pandas : today() strftime() - 날짜 포맷, 지역표준시, UTC(그리니치 표준시) 기준, relativedelta() R 프로그래밍 : Sys.Date() R Dplyr Package : R sqldf Package : Round 함수 Python pandasql Package : Round 함수, datetime(), Date(), julianday() 날짜 함수, Cast() R data.table Package : SAS Proc SQL : intck(), year(), TODAY(), CEIL(), YRDIF() SAS Data Step : TODAY(), DATE(), input(), MDY() 함수, intck(), Year() 함수, YRDIF(), DATDIF() Python Dfply Package : datetime.today(), strftime() 날짜 포맷, time.localtime(), time.gmtime() 파이썬 Base 프로그래밍 :

 

---

1. Oracle(오라클)

sysdate와 to_date() 함수

 

Oracle Programming

select round(sysdate-to_date('15-aug-1947')) age_1947
from   dual

 

 

---

2. Python Pandas(파이썬)

• 참고 : 오늘 날짜 출력

Python Programming

from datetime import datetime
today = datetime.today().strftime('%Y-%m-%d')
print(today)

import time
t = time.time()

# 지역표준시
today = time.strftime("%Y-%m-%d", time.localtime(t))
print( today )

# UTC(그리니치 표준시) 기준
today = time.strftime("%Y-%m-%d", time.gmtime(t))
print( today )

 

Results

2021-03-12
2021-03-12
2021-03-11

 

---

relativedelta 함수를 사용하여서 날짜 사이의 차이를 계산한다. 반환된 개체에서 년도에 해당하는 years를 추출한다.

Python Programming

from dateutil.relativedelta import relativedelta

difference = relativedelta(datetime.today(), datetime(2013, 10, 20))

display(difference)
difference.years

 

Results

relativedelta(years=+7, months=+4, days=+20, hours=+8, minutes=+5, seconds=+32, microseconds=+388625)





7

 

---

• 날짜의 년도를 추출하여 년도간 차이를 계산한다.

Python Programming

datetime.today().year - datetime(2013, 10, 20).year

 

Results

8

 

 

---

3. R Programming (R Package)

• string Vs Base 함수 : https://stringr.tidyverse.org/articles/from-base.html

날짜에서 년도를 추출하여 년도간 차이를 계산한다.

R Programming

%%R

( year(Sys.Date()) - year(as.Date('2013-10-20')) )

 

Results

[1] 8

 

 

---

4. R Dplyr Package

 

---

5. R sqldf Package

• [참고] SQL DB별 문자 함수 비교 : [링크] SQL Dialects Reference/Functions and expressions/String functions

 

2개 날짜 사이의 차이를 계산한다.

R Programming

%%R

sqldf("select round(datetime('now')-DATE('2013-10-20'))  age,
              julianday('now') - julianday('2013-10-20') days,
              Cast ((JulianDay('now') - JulianDay('2013-10-20')) As Integer) days_int")

 

Results

  age     days days_int
1   8 2699.974     2699

 

 

---

6. Python pandasql Package

 

DATE() 함수

 

Python Programming

ps.sqldf("select round(datetime('now')-DATE('2013-10-20')) age")

 

Results

	age
0	8.0

 

 

---

7. R data.table Package

 

---

8. SAS Proc SQL

• DUAL 함수 지원 안 함;

SAS Programming

%%SAS sas

PROC SQL;
  CREATE TABLE STATSAS_1 AS
    select intck('year', input('20131020',yymmdd8.), TODAY())                  AS YEAR_GAP_1,
           year(TODAY()) - year(input('20131020',yymmdd8.))                    AS YEAR_GAP_2,
           CEIL(YRDIF(input('20131020',yymmdd8.), TODAY(), 'ACT/ACT') )        AS YEAR_GAP_3,
           CEIL(DATDIF(input('20131020',yymmdd8.), TODAY(), 'ACT/ACT') / 365 ) AS YEAR_GAP_4
    FROM   EMP(OBS=1);
QUIT;
PROC PRINT;RUN;

 

Results

OBS	YEAR_GAP_1	YEAR_GAP_2	YEAR_GAP_3	YEAR_GAP_4
1	8	8	8	8

 

 

---

9. SAS Data Step

 

Intck() 함수, Yrdif() 함수, Datdif() 함수

 

SAS Programming

%%SAS sas

DATA STATSAS_2;
     * 오늘 날짜와 YEAR 함수;
     THIS_YEAR_1 = YEAR(TODAY());
     THIS_YEAR_2 = YEAR(DATE());

     format base_date base_date_1 yymmddn8.;
     base_date   = input('20131020',yymmdd8.);
     base_date_1 = mdy(10,20,2013);

     year_gap_1 = intck('year', input('20131020',yymmdd8.), TODAY());

     /* 참고 */
     year_gap_2 = year(TODAY()) - year(input('20131020',yymmdd8.));
     year_gap_3 = CEIL(YRDIF(input('20131020',yymmdd8.), TODAY(), 'ACT/ACT') );        * 년도 GAP;
     year_gap_4 = CEIL(DATDIF(input('20131020',yymmdd8.), TODAY(), 'ACT/ACT') / 365 ); * 날짜GAP;
RUN;

PROC PRINT;RUN;

 

Results

OBS	THIS_YEAR_1	THIS_YEAR_2	base_date	base_date_1	year_gap_1	year_gap_2	year_gap_3	year_gap_4
1	2021	2021	20131020	20131020	8	8	8	8

 

 

 

---

10. Python Dfply Package

 

Python Programming

from datetime import datetime
import time

emp >> mutate(today     = datetime.today().strftime('%Y-%m-%d'),
              local_day = time.strftime("%Y-%m-%d", time.localtime( time.time() )),
              utc_day   = time.strftime("%Y-%m-%d", time.gmtime( time.time() )))  >> \
  select(~X.job,~X.mgr,~X.sal,~X.comm) >> head()

 

Results

	empno	ename	hiredate	deptno	today	local_day	utc_day
0	7369	SMITH	1980/12/17	20	2021-05-04	2021-05-04	2021-05-04
1	7499	ALLEN	1981/02/20	30	2021-05-04	2021-05-04	2021-05-04
2	7521	WARD	1981/02/22	30	2021-05-04	2021-05-04	2021-05-04
3	7566	JONES	1981/04/02	20	2021-05-04	2021-05-04	2021-05-04
4	7654	MARTIN	1981/09/28	30	2021-05-04	2021-05-04	2021-05-04

 

 

---

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