[Python] 데이터 병합(Join) - pandas.merge

import pandas as pd

import numpy as np

from pandas import Series, DataFrame

# 데이터 병합(Join) - pandas.merge

df1.merge?

df1.merge(right, how='inner', on=None, left_on=None, right_on=None, left_index=False, right_index=False, 

             sort=False, suffixes=('_x', '_y'), copy=True, indicator=False, validate=None)

# 주요 옵션

# how : join 방법 (default는 inner join), 

         - Outer Join은 'outer' / Inner Join은 'inner' / Left Join은  'left' / Right Join은 'right'

# on : join할 key (같은 key 값을 갖을 경우)

         - join할 키가 다수인 경우 리스트 형식으로 전달

# left_on, right_on : 각 join할 key (서로 다른 key 값을 갖을 경우)

# left_index, right_index : join할 key가 인덱스일 경우

# sort : 조인 키 정렬

df1 = DataFrame(['a','b','c','e'], columns=['name'])

df2 = DataFrame([100, 200, 140, 160], index=['a','b','c','d'], columns=['price'])

pd.merge(df1, df2, left_on='name', right_index=True)   # right_on 대신 right_index를 사용, inner join

pd.merge(df1, df2, left_on='name', right_index=True, how='left')    # left join

pd.merge(df1, df2, left_on='name', right_index=True, how='right')    # right join

pd.merge(df1, df2, left_on='name', right_index=True, how='outer')    # outer join

# Q1.

import pandas as pd

import numpy as np

from pandas import Series, DataFrame

from numpy import nan as NA

# Oracle query 함수

def get_query(sql, ip='127.0.0.1', port='1521', sid='orcl',id='scott', passwd='oracle') :

    import pandas as pd

    import cx_Oracle

    import os

    os.putenv('NLS_LANG', 'KOREAN_KOREA.KO16MSWIN949')

    txt = 'cx_Oracle.connect("'+ id + '/' + passwd + '@' + ip + ':' + port +'/' + sid + '")'

    con = eval(txt)

    return pd.read_sql(sql, con=con)

 

### oracle에서 student테이블과 exam_01 테이블을 파이썬으로 불러온 후, 

각 학생의 정보와 시험성적을 모두 갖는 데이터프레임 생성, 학년별 평균점수을 구하시오

stu = get_query('select * from student')

exam = get_query('select * from exam_01')

data1 = pd.merge(stu, exam, on='STUDNO')

data2 = data1[['STUDNO','GRADE','TOTAL']]

data2.set_index(['GRADE','STUDNO']).unstack(level=0).mean()

          GRADE                               # 1. GRADE, STUDNO 컬럼을 인덱스로 설정

TOTAL  1        85.6                         # 2. GRADE를 컬럼화

          2        84.2                         # 3. GRADE별 평균

          3        86.8

          4        81.6

dtype: float64

# Q2.

### emp2 데이터를 불러온 후

emp2 = get_query('select * from emp2')

# 1) position이 존재하지 않는 대상('-')의 position을 na 처리

emp2['POSITION'].isnull() 

0     False

1     False

2     False

...

17     True        # 쿼리로 불러오면서 자동으로 None을 NaN으로 처리

18     True

19     True

Name: POSITION, dtype: bool

# 2) age 컬럼을 만들고 나이를 계산하여 추가하여라

f1 = lambda x : int(str(x)[:4])

emp2['AGE'] = 2019 - emp2['BIRTHDAY'].map(f1)

emp2

 

# 3) 1970년대에 태어난 사람의 이름과 pay를 출력

f2 = lambda x : str(x)[:3] + '0' == '1970'

emp2.loc[emp2['BIRTHDAY'].map(f2),['NAME','PAY','BIRTHDAY']]

# 4) pay2 컬럼을 만들고 상위관리자의 연봉과의 차이를 계산하여 추가

data3 = pd.merge(emp2, emp2, left_on = 'PEMPNO', right_on = 'EMPNO')   # self join

emp2['PAY2'] = data3['PAY_y'] - data3['PAY_x']

emp2

 

# 5) top(data,column,n)을 입력하면 data의 column이 큰 순서대로 n개 행을 보여주는 함수 생성

top = lambda data,column,n : data.sort_values(by=column, ascending=False).iloc[:n]

top(emp2, 'PAY', 5)

# Q3.

### gogak, gift 테이블을 불러와서 각 고객이 가져갈 수 있는 최대 상품 출력

# cut 메서드를 사용하지 않고 일반 사용자 정의 함수 선언

gogak = get_query('select * from gogak')

gift = get_query('select * from gift')

 

gift.loc[(gift['G_START'] < 900) & (900 < gift['G_END']),'GNAME'].values[0]   # 이 방식을 함수에 적용

'참치세트'

f2 = lambda x : gift.loc[(gift['G_START'] < x) & (x < gift['G_END']),'GNAME'].values[0]

gogak['POINT'].map(f2)

0     양쪽문냉장고

1       참치세트

2     주방용품세트

3       참치세트

4       샴푸세트

5       샴푸세트

6     세차용품세트

7     주방용품세트

8     LCD모니터

9     세차용품세트

10      샴푸세트

11      참치세트

12    산악용자전거

13    세차용품세트

14    산악용자전거

15    LCD모니터

16       노트북

17       노트북

18     벽걸이TV

19     벽걸이TV

Name: POINT, dtype: object

참고: KIC 캠퍼스 머신러닝기반의 빅데이터분석 양성과정