포스팅 목차
140. Display those employees whose grade is equal to any number of Sal but not equal to first number of Sal?
* 직원의 급여가 속한 급여등급을 조회 후 급여(‘sal)에서 급여 등급(‘grade’)에 해당하는 자리수를 선택하여 급여 등급과 비교하여 서로 같고, 급여 등급과 급여의 첫 자리를 비교하여 서로 다른 직원들의 정보를 출력하시오.
- 파이썬 & R 패키지 호출 및 예제 데이터 생성 링크
- Cross Join 유사문제 : 97 / 103 / 113
- [데이터 추출] 특정 조건을 만족하는 데이터 추출 - 비등가조인(NON-EQUI JOIN) 데이터 결합, 문자열 추출(Substr)
- 데이터 전처리 (SQL, Pandas, R Prog, Dplyr, Dfply, SQLDF, PANDASQL, DATA.TABLE, SAS, Proc Sql)
|
1. Oracle(오라클)
emp테이블과 salgrade를 비등가 조인으로 결합하여 급여에 해당하는 급여등급을 선택한다. 급여(‘sal)에서 급여 등급(‘grade’)에 해당하는 자리수를 선택한 후 급여 등급과 비교하여 서로 같고, 급여 등급과 급여의 첫 자리를 비교하여 서로 다른 직원들의 정보를 출력한다.
| Oracle Programming |
select ename,sal,grade ,substr(sal,grade,1) sal_sub
from emp, salgrade
where sal between losal and hisal
and grade = substr(sal,grade,1)
and grade!= substr(sal,1,1)
2. Python Pandas(파이썬)
- 방식1 : pandas와 apply-lambda
- merge 에서 비등가 조인이 지원이 되지 않아서 cross join(카테시안 곱)을 수행 후 query() 함수를 사용하여 급여가 포함된 losal과 hisal의 범위를 선택하여 급여 등급(‘grade’)을 선택한다.
- 급여에서 첫 자리의 수치를 선택하여 var1으로 저장하고, 급여(‘sal)에서 앞에서 선택된 급여 등급(‘grade’)에 해당하는 위치에 존재하는 자리수를 선택하여 var2로 저장한 후 query() 함수에 외부변수 var1과 var2를 참조하여서 급여등급(‘grade’)과 비교하여 해당 조건에 맞는 직원들의 정보를 추출한다.
- 칼럼을 기준으로 substring(slice) 하는 방식을 pandas 문법으로 바로 처리 못함(sal보다 큰 자리를 substr하는 경우 에러가 발생-함수나 lambda 방식)
- 외부 변수/외부환경 변수 참조(external object) - pandas.DataFrame.query [링크]
| Python Programming |
temp = pd.merge(emp.assign(foo=1), salgrade.assign(foo=1), on ='foo').query(" sal.between(losal, hisal)", engine='python').reset_index()
var1 = pd.to_numeric(temp['sal'].astype('str').str.slice(0,1).fillna(0))
print(var1.head())
var2 = temp.apply(lambda x: pd.to_numeric(pd.Series(x.sal, dtype="string").astype('str').str.slice(x.grade-1,x.grade)).replace(np.NaN,0), axis=1)[0]
print(var2.head())
temp.query("grade != @var1 & grade == @var2", engine='python')
| Results |
0 8
1 1
2 1
3 2
4 1
Name: sal, dtype: int64
0 8.0
1 0.0
2 2.0
3 5.0
4 2.0
Name: 0, dtype: float64
| index | empno | ename | job | mgr | hiredate | sal | comm | deptno | foo | grade | losal | hisal | |
| 2 | 11 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 | 1 | 2 | 1201 | 1400 |
| 4 | 21 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 | 1 | 2 | 1201 | 1400 |
급여('sal)에서 앞에서 선택된 급여 등급('grade')에 해당하는 위치에 존재하는 자리수
- 방식2 : apply - lambda 방식
- merge 에서 비등가 조인이 지원이 되지 않아서 cross join(카테시안 곱)을 수행 후 query() 함수를 사용하여 급여가 포함된 losal과 hisal의 범위를 선택하여 급여 등급(‘grade’)을 선택한다.
- apply() 와 Lambda 함수를 사용하여서 급여 등급과 급여의 첫 자리를 비교하여 서로 다르고, 급여(‘sal)에서 급여 등급(‘grade’)에 해당하는 자리수를 선택한 후 급여 등급과 비교하여 서로 같은 직원들의 정보를 출력한다. str.slice 함수에서 급여 등급에 해당하는 위치에 존재하는 자리수가 없는 경우(급여등급 5등급) 에러를 처리하기 위하여 조건문을 사용하여 분기 처리한다.
| Python Programming |
temp = pd.merge(emp.assign(foo=1), salgrade.assign(foo=1), on ='foo').query(" sal.between(losal, hisal)", engine='python').reset_index()
temp[ temp.apply(lambda x : x.grade != (pd.Series(x.sal, dtype="string").str.slice(0,1).astype(int)),axis=1)[0] &
temp.apply(lambda x : (x.grade == (pd.Series(x['sal'], dtype="string").str.slice(x['grade']-1,x['grade']).astype(int))[0])
if (pd.Series(x['sal'], dtype="string").astype(str).str.len()[0] ) > x['grade']-1
else 0,
axis=1) ]
| Results |
| index | empno | ename | job | mgr | hiredate | sal | comm | deptno | foo | grade | losal | hisal | |
| 2 | 11 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 | 1 | 2 | 1201 | 1400 |
| 4 | 21 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 | 1 | 2 | 1201 | 1400 |
- 방식3 : 위의 조건문 대신에 pd.to_numeric 함수를 사용하여 형변환 후 null값(급여등급 5등급에 해당하는 위치의 자리수 미존재)을 np.NaN으로 1차 변경 후 replace 함수를 사용하여 0으로 변경
- merge 에서 비등가 조인이 지원이 되지 않아서 cross join(카테시안 곱)을 수행 후 query() 함수를 사용하여 급여가 포함된 losal과 hisal의 범위를 선택하여 급여 등급(‘grade’)을 선택한다.
- apply() 와 Lambda 함수를 사용하여서 급여 등급과 급여의 첫 자리를 비교하여 서로 다르고, 급여(‘sal)에서 급여 등급(‘grade’)에 해당하는 자리수를 선택한 후 급여 등급과 비교하여 서로 같은 직원들의 정보를 출력한다.
| Python Programming |
temp = pd.merge(emp.assign(foo=1), salgrade.assign(foo=1), on ='foo').query(" sal.between(losal, hisal)", engine='python').reset_index()
temp[ temp.apply(lambda x : x.grade != ( pd.Series(x.sal, dtype="string").str.slice(0,1).astype(int)),axis=1)[0] &
temp.apply(lambda x : x.grade == ( pd.to_numeric( pd.Series(x['sal'], dtype="string").str[x['grade']-1:x['grade']] ).replace(np.NaN,0) )[0] ,
axis=1) ]
| Results |
| index | empno | ename | job | mgr | hiredate | sal | comm | deptno | foo | grade | losal | hisal | |
| 2 | 11 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 | 1 | 2 | 1201 | 1400 |
| 4 | 21 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 | 1 | 2 | 1201 | 1400 |
- [참고] astype 형변환
| Python Programming |
emp['sal'].astype(str).str.slice(1,3).astype(int).head()
| Results |
0 0
1 60
2 25
3 97
4 25
Name: sal, dtype: int32
- [참고] apply 함수를 사용하여서 형변환
| Python Programming |
temp['sal'].apply(str).str.slice(1,3).apply(int).head()
| Results |
0 0
1 60
2 25
3 97
4 25
Name: sal, dtype: int64
3. R Programming (R Package)
merge 에서 비등가 조인이 지원이 되지 않아서 cross join(카테시안 곱)을 수행 후 급여가 포함된 losal과 hisal의 범위를 선택하여 급여 등급(‘grade’)을 선택한다. stringr::str_sub() 함수를 사용하여 급여(‘sal)에서 앞에서 선택한 급여 등급(‘grade’)에 해당하는 자리수를 선택한 후 급여 등급과 비교하여 서로 같고, 급여 등급과 급여의 첫 자리를 비교하여 서로 다른 직원들의 정보를 출력한다.
| R Programming |
%%R
library(stringr)
subset(merge(emp, salgrade, by= character(),all.x=TRUE, all.y=TRUE),
subset= (between(sal, losal, hisal) & grade!= stringr::str_sub(emp$sal,1,1) & grade == stringr::str_sub(sal,grade,grade) ) , )
| Results |
empno ename job mgr hiredate sal comm deptno grade losal hisal
17 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 2 1201 1400
19 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 2 1201 1400
merge 에서 비등가 조인이 지원이 되지 않아서 cross join(카테시안 곱)을 수행 후 급여가 포함된 losal과 hisal의 범위를 선택하여 급여 등급(‘grade’)을 선택한다. base::substr() 함수를 사용하여 급여(‘sal)에서 앞에서 선택한 급여 등급(‘grade’)에 해당하는 자리수를 선택한 후 급여 등급과 비교하여 서로 같고, 급여 등급과 급여의 첫 자리를 비교하여 서로 다른 직원들의 정보를 출력한다.
| R Programming |
%%R
subset( merge(emp, salgrade,by= character(),all.x=TRUE, all.y=TRUE),
between(sal, losal, hisal) & grade!=substr(sal,1,1) & grade == substr(sal,grade,grade) )
| Results |
empno ename job mgr hiredate sal comm deptno grade losal hisal
17 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 2 1201 1400
19 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 2 1201 1400
4. R Dplyr Package
full_join() 함수를 통해 cross join(카테시안 곱)을 수행 후 급여가 포함된 losal과 hisal의 범위를 선택하여 급여 등급(‘grade’)을 선택한다. stringr::str_sub() 함수를 사용하여 급여(‘sal)에서 앞에서 선택한 급여 등급(‘grade’)에 해당하는 자리수를 선택한 후 급여 등급과 비교하여 서로 같고, 급여 등급과 급여의 첫 자리를 비교하여 서로 다른 직원들의 정보를 출력한다.
| R Programming |
%%R
emp %>%
dplyr::full_join(salgrade, by = character()) %>%
dplyr::filter( between(sal, losal, hisal ) & grade!= stringr::str_sub(emp$sal,1,1) & grade == stringr::str_sub(sal,grade,grade) )
| Results |
# A tibble: 3 x 11
empno ename job mgr hiredate sal comm deptno grade losal hisal
<dbl> <chr> <chr> <dbl> <date> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 7521 WARD SALESMAN 7698 1981-02-22 1250 500 30 2 1201 1400
2 7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30 2 1201 1400
3 7876 ADAMS CLERK 7788 1983-01-12 1100 NA 20 1 700 1200
5. R sqldf Package
emp테이블과 salgrade를 비등가 조인으로 결합하여 급여에 해당하는 급여등급을 선택한다. 급여(‘sal)에서 급여 등급(‘grade’)에 해당하는 자리수를 선택한 후 급여 등급과 비교하여 서로 같고, 급여 등급과 급여의 첫 자리를 비교하여 서로 다른 직원들의 정보를 출력한다.
| R Programming |
%%R
sqldf(" select ename,sal, b.grade ,
substr(a.sal,1,1) first_str,
substr(sal,b.grade,1) sal_grade_pos
from emp a, salgrade b
where sal between losal and hisal
and grade = substr(sal,grade,1)
and grade!= substr(sal,1,1) ")
| Results |
ename sal grade first_str sal_grade_pos
1 WARD 1250 2 1 2
2 MARTIN 1250 2 1 2
6. Python pandasql Package
emp테이블과 salgrade를 비등가 조인으로 결합하여 급여에 해당하는 급여등급을 선택한다. 급여(‘sal)에서 급여 등급(‘grade’)에 해당하는 자리수를 선택한 후 급여 등급과 비교하여 서로 같고, 급여 등급과 급여의 첫 자리를 비교하여 서로 다른 직원들의 정보를 출력한다.
| Python Programming |
ps.sqldf(" select ename,sal,grade ,substr(sal,grade,1) sal_grade_pos \
from emp,salgrade \
where sal between losal and hisal \
and grade!=substr(sal,1,1) and grade = substr(sal,grade,1) ")
| Results |
| ename | sal | grade | substr(sal,grade,1) | |
| 0 | WARD | 1250 | 2 | 2 |
| 1 | MARTIN | 1250 | 2 | 2 |
7. R data.table Package
emp테이블과 salgrade를 비등가 조인으로 결합하여 급여가 포함된 급여등급을 선택하고, 급여(‘sal)에서 앞에서 선택된 급여 등급(‘grade’)에 해당하는 위치에 존재하는 자리수를 선택한 후 급여 등급과 비교하여 서로 같고, 급여 등급과 급여의 첫 자리를 비교하여 서로 다른 직원들의 정보를 출력한다.
| R Programming |
%%R
DT <- data.table(emp)
dept_DT <- data.table(dept)
salgrade_DT <- data.table(salgrade)
DT[salgrade_DT, nomatch=NULL, on = .( sal >= losal , sal <= hisal), .(x.empno,x.ename,x.sal,grade)][grade!=substr(x.sal,1,1) & grade == substr(x.sal,grade,grade),]
| Results |
x.empno x.ename x.sal grade
1: 7521 WARD 1250 2
2: 7654 MARTIN 1250 2
8. SAS Proc SQL
emp테이블과 salgrade를 비등가 조인으로 결합하여 급여에 해당하는 급여등급을 선택한다. 급여(‘sal)에서 급여 등급(‘grade’)에 해당하는 자리수를 선택한 후 급여 등급과 비교하여 서로 같고, 급여 등급과 급여의 첫 자리를 비교하여 서로 다른 직원들의 정보를 출력한다.
| SAS Programming |
%%SAS sas
PROC SQL;
create table STATSAS_1 as
select ename,sal,grade ,
substr(strip(put(sal,6.)),grade,1) as sal_substr,
input( substr(strip(put(sal,6.)),grade,1) ,8.) as sub_num,
input( substr(strip(put(sal,6.)),1,1), 8.) as sal_first /* sal의 첫번째 자리 추출 후 숫자형으로 변경 */
from emp, salgrade
where sal between losal and hisal
and grade = input( substr(strip(put(sal,6.)),grade,1), 8.)
and grade NE input( substr(strip(put(sal,6.)),1,1), 8.);
QUIT;
PROC PRINT;RUN;
| Results |
| OBS | ename | sal | grade | sal_substr | sub_num | sal_first |
| 1 | WARD | 1250 | 2 | 2 | 2 | 1 |
| 2 | MARTIN | 1250 | 2 | 2 | 2 | 1 |
9. SAS Data Step
- DATA STEP(CROSS JOIN-카테시안 곱);
| SAS Programming |
%%SAS sas
DATA STATSAS_3;
SET EMP;
DO I=1 TO KOBS;
SET salgrade NOBS=KOBS POINT=I;
IF LENGTH( strip(put(sal,6.)) ) >= GRADE THEN DO;
sal_substr = substr(strip(put(sal,6.)),grade,1);
sub_num = input( substr(strip(put(sal,6.)),grade,1) ,8.);
END;
sal_first = input( substr(strip(put(sal,6.)),1,1), 8.);
IF SAL >= losal AND SAL <= hisal
and grade = input( substr(strip(put(sal,6.)),grade,1), 8.)
and grade NE input( substr(strip(put(sal,6.)),1,1), 8.) THEN OUTPUT;
END;
RUN;
PROC PRINT;RUN;
| Results |
| OBS | empno | ename | job | mgr | hiredate | sal | comm | deptno | grade | losal | hisal | sal_sub | strsub_num | sal_first |
| 1 | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 | 2 | 1201 | 1400 | 2 | 2 | 1 |
| 2 | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 | 2 | 1201 | 1400 | 2 | 2 | 1 |
10. Python Dfply Package
| Python Programming |
emp.assign(foo=1) >> \
full_join(salgrade.assign(foo=1), by="foo") >> \
select(X.empno,X.sal,X.grade,X.losal,X.hisal) >> \
mutate(var1=X.apply(lambda x : x.grade != ( pd.Series(x.sal, dtype="string").str.slice(0,1).astype(int)),axis=1)[0],
var2=X.apply(lambda x : x.grade==( pd.to_numeric( pd.Series(x['sal'], dtype="string").str[x['grade']-1:x['grade']] ).replace(np.NaN,0))[0],axis=1) ) >> \
head(10)
| Results |
| empno | sal | grade | losal | hisal | var1 | var2 | |
| 0 | 7369 | 800 | 1 | 700 | 1200 | True | False |
| 1 | 7369 | 800 | 2 | 1201 | 1400 | True | False |
| 2 | 7369 | 800 | 3 | 1401 | 2000 | True | False |
| 3 | 7369 | 800 | 4 | 2001 | 3000 | True | False |
| 4 | 7369 | 800 | 5 | 3001 | 9999 | True | False |
| 5 | 7499 | 1600 | 1 | 700 | 1200 | False | True |
| 6 | 7499 | 1600 | 2 | 1201 | 1400 | True | False |
| 7 | 7499 | 1600 | 3 | 1401 | 2000 | True | False |
| 8 | 7499 | 1600 | 4 | 2001 | 3000 | True | False |
| 9 | 7499 | 1600 | 5 | 3001 | 9999 | True | False |
| Python Programming |
emp.assign(foo=1) >> \
full_join(salgrade.assign(foo=1), by="foo") >> \
filter_by( X.sal.between(X.losal,X.hisal) ,
X.apply(lambda x : x.grade != ( pd.Series(x.sal, dtype="string").str.slice(0,1).astype(int)),axis=1)[0],
X.apply(lambda x : x.grade == ( pd.to_numeric( pd.Series(x['sal'], dtype="string").str[x['grade']-1:x['grade']] ).replace(np.NaN,0))[0],axis=1)
)
| Results |
| empno | ename | job | mgr | hiredate | sal | comm | deptno | foo | grade | losal | hisal | |
| 11 | 7521 | WARD | SALESMAN | 7698.0 | 1981/02/22 | 1250 | 500.0 | 30 | 1 | 2 | 1201 | 1400 |
| 21 | 7654 | MARTIN | SALESMAN | 7698.0 | 1981/09/28 | 1250 | 1400.0 | 30 | 1 | 2 | 1201 | 1400 |
[SQL, Pandas, R Prog, Dplyr, SQLDF, PANDASQL, DATA.TABLE] SQL EMP 예제로 만나는 테이블 데이터 전처리 방법 리스트
반응형
댓글