지난번 포스팅에서는 웹으로 간단하게 SQL 을 연습할 수 있는 온라인 사이트로서 


w3schools.com/sql 과 테이블을 쿼리해서 데이터 항목을 알아보았습니다. 


주문, 주문상세 테이블과 고객, 상품, 상품카테고리, 상품제공업체, 배송업체, 종업원의 기준정보 테이블이 있는 것으로 봐서 유통업체의 데이터임을 알 수 있습니다. 이벤트나 프로모션 정보 테이블, 온라인이나 모바일 등의 채널 이용 정보 테이블, 고객등급/고객세분화 정보 테이블,  결제수단 정보 테이블 등... 뭐, 유통업체라면 더 많은 테이블이 있어야 겠지만서도, SQL 연습하라고 만든 가상의 약식 데이터 DB 테이블이므로 '이 정도도 어디야'하고 감사하면 사용하면 좋겠습니다. 


ERD (Entity Relationship Diagram)이 없어서 테이블, 데이터 간의 관계를 한 눈에 파악하는 것이 어려웠는데요, 시간 좀 내서 아래처럼 ERD 그려보았습니다. 


[ 유통업체 ERD (Entity Relationship Diagram) ]


* https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all  에 있는 테이블별 칼럼을 보고 추측해서 ERD 그린 것임.  SQL 연습하려는 분은 이 ERD 참고해서 테이블 간 join 해서 분석하면 됨. 



테이블을 여러개 Join 해서 통계량 집계하고 정렬하는 예를 들어보겠습니다. 



 
[SQL Query 문제] 


"제품 카테고리 중 'Dairy Products', 'Grains/Cereals', 'Seafood', 'Condiments' 카테고리에 대해서 카테고리별로 판매가 일어난(주문이 발생한) 제품들의 가격의 합계, 총 주문 발생 회수, 제품들의 가격의 평균을 구하시오.  


단, 카테고리별 제품 가격의 합계가 1,100 이상인 경우만 집계 결과를 제시하되, 

가격의 합계를 기준으로 내림차순으로 정렬하여 제시하시오."


SELECT e.CategoryName AS CategoryName, 

        SUM(e.Price) AS Price_sum, 

        COUNT(*) AS Order_cnt, 

        AVG(e.Price) AS Price_avg

    FROM 

    (SELECT c.OrderID, c.ProductID, c.Price, c.CategoryID, d.CategoryName  -- sub query 2

     FROM (SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID  -- sub query 1

           FROM OrderDetails a

           INNER JOIN Products b ON a.ProductID = b.ProductID) c

     LEFT JOIN Categories d ON c.CategoryID = d.CategoryID) e

     WHERE e.CategoryName IN ('Dairy Products', 'Grains/Cereals', 'Seafood', 'Condiments')

     GROUP BY e.CategoryName

     HAVING Price_sum > 1100

     ORDER BY Price_sum DESC;   

 

 

Number of Records: 3

====================================================

CategoryName        Price_sum        Order_cnt           Price_avg

====================================================

Dairy Products         2863.2                  100               28.63

Seafood                  1345.17                    67               20.07

Condiments           1121.5                    49               22.88




위의 문제가 너무 복잡하고, SQL Query도 SUM(), COUNT(), AVG() 등의 aggregation 함수, FROM 절에 Sub Query 랑 INNER JOIN, LEFT JOIN 이 들어가 있고, WHERE 조건절, GROUP BY, HAVING, ORDER BY 등 어지간한 SQL 기능이 망라되어 있어서 복잡하긴 합니다. 


Query가 잘 이해가 안되면 Sub Query를 하나씩 순차적으로 실행시켜보면서 결과를 확인해보면 한결 이해하기가 쉽습니다. 


예를 들어보자면, 위의 Query를 가장 안에 위치한 Sub Query 부터 하나씩 아래에 풀어보겠습니다. 


=========================================================================


[ sub query 1]


OrderDetails 테이블(a)Products 테이블(b)ProductID key를 기준으로 INNER JOIN으로 교집합을 구해서 Products 테이블에서 상품의 가격과 카테고리ID 데이터를 가져왔습니다. (테이블 구분하기 편하라고 a, b 라는 alias name 별명을 부여해서 변수 앞에 b.Price 처럼 붙여서 사용합니다)  상위 5개만 예시로 가져오겠습니다. 



SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID

           FROM OrderDetails a

           INNER JOIN Products b ON a.ProductID = b.ProductID

           LIMIT 5;

 

 

OrderID   ProductID   Price   CategoryID

10248 11                 21         4

10248 42                 14         5

10248 72                 34.8         4

10249 14                 23.25 7

10249 51                 53         7





=========================================================================


[sub query 2]


위의 'sub query 1' 결과 테이블(c)에다가 CategoryID key를 기준으로 Categories 테이블(d)을 LEFT JOIN 하여 Categories 테이블에 있는 CategoryName 칼럼을 붙여서 가져왔습니다.  CategoryName 을 붙여 와야지 원래의 SQL Query 문제에 나와있는 'CategoryName별 판매상품 가격의 합계, 판매(주문)회수, 평균판매가격'을 구할 수 있겠지요?



SELECT c.OrderID, c.ProductID, c.Price, c.CategoryID, d.CategoryName

     FROM (SELECT a.OrderID, a.ProductID, b.Price, b.CategoryID

           FROM OrderDetails a

           INNER JOIN Products b ON a.ProductID = b.ProductID) c

     LEFT JOIN Categories d ON c.CategoryID = d.CategoryID

     LIMIT 5;

 


c.OrderID   c.ProductID  c.Price  c.CategoryID   d.CategoryName

10248 11                 21         4                 Dairy Products

10248 42                 14         5                 Grains/Cereals

10248 72                 34.8         4                 Dairy Products

10249 14                 23.25 7                 Produce

10249 51                 53         7                 Produce

 




========================================================================


아래 Query 는 CASE WHEN ~ THEN ... ELSE ... END 문으로 연속형 변수(continuous variable)를 범주형 변수(categorical variable) 로 변환하는 예제 Query 입니다.  아래처럼 '가격대(Price_grp)' 변수를 만든 후에 위에 'SQL Query' 문제에서 사용했던 Query 를 사용해서 다른 응용을 할 수 있습니다. 



SELECT a.OrderID, a.ProductID, b.Price, 

       CASE WHEN b.Price >= 40 THEN '1_over_40'

                WHEN b.Price < 40 AND b.Price >= 20 THEN '2_20_40'

                ELSE '3_under_20' END Price_grp

    FROM OrderDetails a

    INNER JOIN Products b ON a.ProductID = b.ProductID

    LIMIT 10;

 


OrderID ProductID Price Price_grp

10248 11       21         2_20_40

10248 42       14         3_under_20

10248 72       34.8         2_20_40

10249 14       23.25         2_20_40

10249 51       53         1_over_40

10250 41       9.65         3_under_20

10250 51       53         1_over_40

10250 65       21.05         2_20_40

10251 22       21         2_20_40

10251 57       19.5         3_under_20




많은 도움이 되었기를 바랍니다. 


이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾸욱 눌러주세요. ^^



Posted by R Friend R_Friend

데이터 분석을 하다 보면 여기 저기 흩어져 있는 데이터를 특정한 Key를 기준으로 병합해서 분석해야 하는 경우가 매우 많습니다.

 

지난번 포스팅에서는 DataFrame을 pandas의 concat() 함수를 이용해서 합치는 방법, append() 함수를 사용해서 합치는 방법을 소개하였습니다.

 

이번 포스팅에서는 SQL을 사용해서 Database의 Table 들을 Join/Merge 하는 것과 유사하게 Python pandas의 pd.merge() 함수를 사용해서 DataFrame을 Key 기준으로 inner, outer, left, outer join 하여 합치는 방법을 소개하도록 하겠습니다.

 

SQL을 사용하는데 익숙한 분석가라면 매우 쉽고 빠르게 이해하실 수 있을 것입니다. 그리고 Python의 merge() 기능은 메모리 상에서 매우 빠르게 작동함으로 사용하는데 있어 불편함이 덜할 것 같습니다.

 

 

 

 

 

pandas merge 함수 설정값들은 아래와 같이 여러개가 있는데요, 이중에서  'how'와 'on'은 꼭 기억해두셔야 합니다.

 

 

pd.merge(left, right, # merge할 DataFrame 객체 이름
             how='inner', # left, rigth, inner (default), outer
             on=None, # merge의 기준이 되는 Key 변수
             left_on=None, # 왼쪽 DataFrame의 변수를 Key로 사용
             right_on=None, # 오른쪽 DataFrame의 변수를 Key로 사용
             left_index=False, # 만약 True 라면, 왼쪽 DataFrame의 index를 merge Key로 사용
             right_index=False, # 만약 True 라면, 오른쪽 DataFrame의 index를 merge Key로 사용
             sort=True, # merge 된 후의 DataFrame을 join Key 기준으로 정렬
             suffixes=('_x', '_y'), # 중복되는 변수 이름에 대해 접두사 부여 (defaults to '_x', '_y'
             copy=True, # merge할 DataFrame을 복사
             indicator=False) # 병합된 이후의 DataFrame에 left_only, right_only, both 등의 출처를 알 수 있는 부가 정보 변수 추가

 

 

 

먼저, pandas, DataFrame library를 importing 한 후에, 2개의 DataFrame을 만들어보겠습니다.

 

 

In [1]: import pandas as pd


In [2]: from pandas import DataFrame


In [3]: df_left = DataFrame({'KEY': ['K0', 'K1', 'K2', 'K3'],

   ...: 'A': ['A0', 'A1', 'A2', 'A3'],

   ...: 'B': ['B0', 'B1', 'B2', 'B3']})

   ...:


In [4]: df_right = DataFrame({'KEY': ['K2', 'K3', 'K4', 'K5'],

   ...: 'C': ['C2', 'C3', 'C4', 'C5'],

   ...: 'D': ['D2', 'D3', 'D4', 'D5']})

   ...:


In [5]: df_left

Out[5]:

    A   B KEY
0  A0  B0  K0
1  A1  B1  K1
2  A2  B2  K2
3  A3  B3  K3


In [6]: df_right

Out[6]:

    C   D KEY
0  C2  D2  K2
1  C3  D3  K3
2  C4  D4  K4
3  C5  D5  K5

 

 

 

 

 

'how' 의 left, right, inner, outer 별로 위에서 만든 'df_left'와 'df_right' 두 개의 DataFrame을 'KEY' 변수를 기준으로 merge 해보겠습니다.  SQL join에 익숙하신 분이라면 쉽게 이해할 수 있을 것입니다.

 

 

  (1) Merge method : left (SQL join name : LEFT OUTER JOIN)

 

 

In [7]: df_merge_how_left = pd.merge(df_left, df_right,

   ...: how='left',

   ...: on='KEY')

   ...:


In [8]: df_merge_how_left

Out[8]:

     A   B   KEY   C     D
0  A0  B0  K0  NaN  NaN
1  A1  B1  K1  NaN  NaN
2  A2  B2  K2   C2   D2
3  A3  B3  K3   C3   D3 

 

 

 

 

  (2) Merge method : right (SQL join name : RIGHT OUTER JOIN)

 

 

In [9]: df_merge_how_right = pd.merge(df_left, df_right,

   ...: how='right',

   ...: on='KEY')


In [10]: df_merge_how_right

Out[10]:

     A    KEY   C   D
0   A2   B2  K2  C2  D2
1   A3   B3  K3  C3  D3
2  NaN  NaN  K4  C4  D4
3  NaN  NaN  K5  C5  D5

 

 

 

 

  (3) Merge method : inner (SQL join name : INNER JOIN)

 

 

In [11]: df_merge_how_inner = pd.merge(df_left, df_right,

    ...: how='inner', # default

    ...: on='KEY')

    ...:


In [12]: df_merge_how_inner

Out[12]:

    A   B KEY   C   D
0  A2  B2  K2  C2  D2
1  A3  B3  K3  C3  D3

 

 

 

 

  (4) Merge method : outer (SQL join name : FULL OUTER JOIN)

 

 

In [13]: df_merge_how_outer = pd.merge(df_left, df_right,

    ...: how='outer',

    ...: on='KEY')

    ...:


In [14]: df_merge_how_outer

Out[14]:

     A    B  KEY    C    D
0   A0   B0  K0  NaN  NaN
1   A1   B1  K1  NaN  NaN
2   A2   B2  K2   C2   D2
3   A3   B3  K3   C3   D3
4  NaN  NaN  K4   C4   D4
5  NaN  NaN  K5   C5   D5

 

 

 

[참고] Hive 조인 문 : INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN, CARTESIAN PRODUCT JOIN, MAP-SIDE JOIN, UNION ALL : http://rfriend.tistory.com/216

 

 

 

  (5) indicator = True : 병합된 이후의 DataFrame에 left_only, right_only, both 등의 

                              출처를 알 수 있는 부가정보 변수 추가

 

 

In [15]: pd.merge(df_left, df_right, how='outer', on='KEY',

    ...: indicator=True)

Out[15]:

     A    B KEY    C    D      _merge
0   A0   B0  K0  NaN  NaN   left_only
1   A1   B1  K1  NaN  NaN   left_only
2   A2   B2  K2   C2   D2        both
3   A3   B3  K3   C3   D3        both
4  NaN  NaN  K4   C4   D4  right_only
5  NaN  NaN  K5   C5   D5  right_only

 

 

 

위에서는 indicator=True로 했더니 '_merge'라는 새로운 변수가 생겼습니다.

 

이 방법 외에도, 아래처럼 indicator='변수 이름(예: indicator_info)'을 설정해주면, 새로운 변수 이름에 indicator 정보가 반환됩니다.

 

 

In [16]: pd.merge(df_left, df_right, how='outer', on='KEY',

    ...: indicator='indicator_info')

Out[16]:

     A    B KEY    C    D indicator_info
0   A0   B0  K0  NaN  NaN      left_only
1   A1   B1  K1  NaN  NaN      left_only
2   A2   B2  K2   C2   D2           both
3   A3   B3  K3   C3   D3           both
4  NaN  NaN  K4   C4   D4     right_only
5  NaN  NaN  K5   C5   D5     right_only

 

 

 

 

  (6) 변수 이름이 중복될 경우 접미사 붙이기 : suffixes = ('_x', '_y')

 

'B'와 'C' 의 변수 이름이 동일하게 있는 두 개의 DataFrame을 만든 후에, KEY를 기준으로 합치기(merge)를 해보겠습니다.  변수 이름이 중복되므로 Data Source를 구분할 수 있도록 suffixes = ('string', 'string') 을 사용해서 중복되는 변수의 뒷 부분에 접미사를 추가해보겠습니다.  default는 suffixes = ('_x', '_y') 입니다.

 

 

# making DataFrames with overlapping columns

In [17]: df_left_2 = DataFrame({'KEY': ['K0', 'K1', 'K2', 'K3'],

    ...: 'A': ['A0', 'A1', 'A2', 'A3'],

    ...: 'B': ['B0', 'B1', 'B2', 'B3'],

    ...: 'C': ['C0', 'C1', 'C2', 'C3']})


In [18]: df_right_2 = DataFrame({'KEY': ['K0', 'K1', 'K2', 'K3'],

    ...: 'B': ['B0_2', 'B1_2', 'B2_2', 'B3_2'],

    ...: 'C': ['C0_2', 'C1_2', 'C2_2', 'C3_2'],

    ...: 'D': ['D0_2', 'D1_2', 'D2_2', 'D3_3']})

    ...:


In [19]: df_left_2

Out[19]:

    A   B   C KEY
0  A0  B0  C0  K0
1  A1  B1  C1  K1
2  A2  B2  C2  K2
3  A3  B3  C3  K3


In [20]: df_right_2

Out[20]:

      B     C     D KEY
0  B0_2  C0_2  D0_2  K0
1  B1_2  C1_2  D1_2  K1
2  B2_2  C2_2  D2_2  K2
3  B3_2  C3_2  D3_3  K3

 


# adding string suffixes to apply to overlapping columns

In [21]: pd.merge(df_left_2, df_right_2, how='inner', on='KEY',

    ...: suffixes=('_left', '_right'))

    ...:

Out[21]:

    A B_left C_left KEY B_right C_right     D
0  A0     B0     C0  K0    B0_2    C0_2  D0_2
1  A1     B1     C1  K1    B1_2    C1_2  D1_2
2  A2     B2     C2  K2    B2_2    C2_2  D2_2
3  A3     B3     C3  K3    B3_2    C3_2  D3_3

 


# suffixes defaults to ('_x', '_y') 

In [22]: pd.merge(df_left_2, df_right_2, how='inner', on='KEY')

    ...:

Out[22]:

    A B_x C_x KEY   B_y   C_y     D
0  A0  B0  C0  K0  B0_2  C0_2  D0_2
1  A1  B1  C1  K1  B1_2  C1_2  D1_2
2  A2  B2  C2  K2  B2_2  C2_2  D2_2
3  A3  B3  C3  K3  B3_2  C3_2  D3_3

 

 

 

 

 

left_on, right_on, left_index, right_index 에 대해서는 다음번 포스팅에서 소개하도록 하겠습니다.

 

Posted by R Friend R_Friend

빅데이터 환경에서 사이즈가 어마무시하게 큰 테이블 간 JOIN은 부하가 매우 크므로 되도록이면 데이터 스키마 설계를 할 때 JOIN을 하지 않고도 분석을 할 수 있도록 요건을 반영하곤 합니다. ARRAY, MAP, STRUCT 등의 컬렉션 데이터형이 정규화를 깨더라도 빅데이터 환경에서 사용되는 이유입니다.

 

하지만, 아무리 스키마 설계를 잘 한다고 해도 분석이 복잡해지면 JOIN을 안하고 모든 것을 처리하기가 힘든 것도 사실입니다.

 

이번 포스팅에서는 두 개 이상의 테이블을 좌, 우로 서로 연결해서 분석할 때 사용하는 Hive 조인 문으로

 

 - INNER JOIN

 - LEFT OUTER JOIN

 - RIGHT OUTER JOIN

 - FULL OUER JOIN

 - CROSS JOIN(CARTESIAN PRODUCT JOIN)

 - MAP-SIDE JOIN  과

 

두 개 이상의 테이블을 상, 하로 서로 붙여서 분석할 때 사용하는

 - UNION ALL

 

문에 대해서 알아보겠습니다.

 

기존에 관계형 DB를 사용했던 분이라면 아마 쉽게 이해할 수 있을 내용들입니다.

 

 

  • 내부 조인 (INNER JOIN)

 

[쿼리 예시]


SELECT a.var1, a.var2, b.var2
    FROM mytable_1 a JOIN mytable_2 b 
    ON a.var1 = b.var1 -- EQUI-JOIN 만 가능하며, NON-EQUI JOIN은 불가
    WHERE a.prd_cd = 'ABC' AND b.prd_cd = 'DEF';

 


SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2 -- 2 JOIN keys
    WHERE a.prd_cd = 'ABC';

 

   

SELECT a.var1, a.var2, b.var3, c.var4
    FROM mytable_1 a JOIN mytable_1 b ON a.var1 = b.var1
                            JOIN mytable_1 c ON a.var1 = c.var1
    WHERE (a.prd_cd = 'ABC' AND b.prd_cd = 'DEF')

              AND (c.prd_cd = 'GHI');

 

 

 

 

  • 왼쪽 세미 조인 (LEFT SEMI-JOIN)

--   : 오른쪽 테이블에서 ON의 술어를 만족하는 레코드를 찾으면 왼쪽 테이블의 레코드를 반환

--   : 내부 조인(INNER JOIN)을 최적화한 특수한 형태로, 내부 조인보다 빠름

--   : 왼쪽 세미 조인만 지원하며, 오른쪽 세미 조인은 미지원

 

SELECT a.var1, a.var2, a.var3
    FROM mytable_1 a LEFT SEMI JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2;

 

 

 

  • 왼쪽 외부 조인 (LEFT OUTER JOIN)

 

[ 쿼리 예시 ]

 

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a LEFT OUTER JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2
    WHERE a.prd_cd = 'ABC';

 

 

 

  • 오른쪽 외부 조인 (RIGHT OUTER JOIN)

 

[ 쿼리 예시 ]

 

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a RIGHT OUTER JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2
    WHERE a.prd_cd = 'ABC';

 

 

 

  • 완전 외부 조인 (FULL OUTER JOIN)

 

[ 쿼리 예시 ]

 

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a FULL OUTER JOIN mytable_2 b 
    ON a.var1 = b.var1 AND a.var2 = b.var2
    WHERE a.prd_cd = 'ABC';

 

 

 

  • 교차 조인 (CROSS JOIN (CARTESIAN PRODUCT JOIN))

 

[ 쿼리 예시]

 

SELECT * FROM mytable_1 JOIN mytable_2;

 

 

쿼리 문이 JOIN 문 중에서 제일 간단합니다.  그런데 속도는 제일 느리고 또 위험하기도 합니다. ^^;

 

A*B 개의 ROW를 반환(가령 A 테이블이 100개 row, B 테이블이 1000개 row를 가지고 있다면 A JOIN B 는 100 * 1,000 = 100,000 개 row 출력)하므로 출력량도 제일 많습니다. 뿐만 아니라 다른 JOIN문과 달리 CORSS JOIN (CARTESIAN PRODUCT JOIN)은 병렬처리도 안되므로 느리기까지 합니다.

 

SELECT 문을 가지고 시스템을 다운시키기가 쉽지 않은데요, SELECT 문으로 혹시 HIVE가 다운되거나 성능이 현격히 저하되었다면 JOIN 걸 때 ON, WHERE 절을 빼먹고 CROSS JOIN (Cartesian Product JOIN)을 실수로 쿼리를 날린게 아닌가 하고 범인 후보로 의심을 해볼 수 있습니다.

 

CROSS JOIN 걸 때는 '내가 원하는 output/분석 목적이 CROSS JOIN 맞나?'를 재차 확인하고 쿼리를 날리기 바랍니다.  특히 'BIG~ 데이터' 환경에서는 말이지요.

 

 

 

  • Map-Side JOIN

 

[ 쿼리 예시 ]

 

hive> set hive.auto.convert.join=true; -- default is false

 

SELECT a.var1, a.var2, a.var3, b.var4
    FROM mytable_1 a JOIN mytable_2 b -- 큰 테이블은 왼쪽, 크기가 작은 테이블은 오른쪽!
    ON a.var1 = b.var1 AND a.var2 = b.var2
    WHERE a.prd_cd = 'ABC';

 

 

 

맵 사이드 조인을 하려면 먼저 set hive.auto.convert.join=true; 로 설정을 바꿔줘야 합니다.

맵 사이드 조인은 이름에서 알 수 있는 것처럼, 크기가 작은 테이블을 메모리에 캐시하고, 큰 테이블은 맵퍼로 보내 후에, 캐시한 작은 테이블로부터 일치하는 모든 것을 찾아낼 수 있기 때문에 맵에서 모든 조인을 할 수 있습니다. 

  => 일반 JOIN 과 달리 리듀스 단계를 건너뛸 수 있어서 속도가 빠른 장점이 있습니다. 성능 최적화를 위해 알아두면 좋은 JOIN 방법입니다.  

 

 

이상으로 JOIN 문을 마칩니다.

 


 

 

두 개 이상의 테이블을 합칠 때 사용하는 UNION ALL 입니다.

  • UNION ALL

 

-- 두 개 이상의 테이블을 위, 아래로 합치기
-- subquery 는 같은 수의 칼럼, 각 칼럼의 Data Type 동일해야 함

 

[ 쿼리 예시 ]

 

SELECT a.var1, a.var2, a.var3
    FROM (
        SELECT a1.var1, a1.var2, a1.var3, 'A1' as source
        FROM mytable a1
    UNION ALL
        SELECT a2.var1, a2.var2, a2.var3, 'A2' as source
        FROM mytable a2
    ) a
SORT BY a.var1 ASC, a.var2 DESC;

 

 

이상으로 HIVE에서 테이블 연결하거나 합쳐서 분석할 때 사용하는 JOIN문, UNION ALL에 대해서 알아보았습니다.

 

다음번 포스팅에서는 하이브 뷰(VIEW)에 대해서 알아보겠습니다.

 

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡'를 꾸욱 눌러주세요.

 

 

Posted by R Friend R_Friend

분석을 진행하다 보면 하나의 데이터 셋에서 변수를 생성, 제거, 변환하는 작업 못지않게 새로운 데이터 셋을 기존의 데이터 셋과 결합하는 작업 또한 빈번합니다.  이번 포스팅에서는 rbind(), cbind(), merge()함수를 활용해서 데이터 프레임 결합하는 방법에 대해서 알아보도록 하겠습니다.

 

예전에 포스팅 했던 R 행렬 함수(☞ 바로가기) 에서 rbind(), cbind()를 다루었던 적이 있는데요, 데이터 프레임도 행렬에서의 데이터 결합과 동일하며, 복습하는 차원에서 한번 더 짚어 보고, key값 기준으로 결합하는 merge()에 대해서 추가로 알아보도록 하겠습니다.

 

 

 R 데이터 프레임 결합 : rbind(), cbind(), merge()

 

[ rbind(), cbind(), merge() 함수 비교 ]

 

 

 

(1) 행 결합 (위 + 아래) : rbind(A, B)

 

먼저 실습에 사용할 데이터 프레임 두개(cust_mart_1, cust_mart_2)를 생성해 보겠습니다.

 

> ## 데이터 프레임 생성
> cust_id <- c("c01","c02","c03","c04")
> last_name <- c("Kim", "Lee", "Choi", "Park")
> cust_mart_1 <- data.frame(cust_id, last_name)
> 
> cust_mart_1
  cust_id last_name
1     c01       Kim
2     c02       Lee
3     c03      Choi
4     c04      Park
> 
> 
> cust_mart_2 <- data.frame(cust_id = c("c05", "c06", "c07"), 
+                           last_name = c("Bae", "Kim", "Lim"))
> 
> cust_mart_2
  cust_id last_name
1     c05       Bae
2     c06       Kim
3     c07       Lim

 

 

다음으로 두개의 데이터 프레임(cust_mart_1, cust_mart_2)을 세로 행 결합 (위 + 아래) 해보도록 하겠습니다.

 

> ## (1) 행 결합 (위 + 아래) rbind(A, B) > cust_mart_12 <- rbind(cust_mart_1, cust_mart_2) > > cust_mart_12 cust_id last_name 1 c01 Kim 2 c02 Lee 3 c03 Choi 4 c04 Park 5 c05 Bae 6 c06 Kim 7 c07 Lim

 

 

rbind()는 row bind 의 약자입니다. rbind()를 무작정 외우려고 하지 마시고, row bind의 약자라는걸 이해하시면 됩니다. 

위의 행 결합 rbind()를 하기 위해서는 결합하려는 두개의 데이터 셋의 열의 갯수와 속성, 이름이 같아야만 합니다. 

 

아래의 예시 처럼 만약 칼럼의 갯수가 서로 다르다면 (cust_mart_12는 열이 2개, cust_mart_3은 열이 3개) 열의 갯수가 맞지 않는다고 에러 메시지가 뜹니다.

 

> cust_mart_3 <- data.frame(cust_id = c("c08", "c09"), 
+                           last_name = c("Lee", "Park"), 
+                           gender = c("F", "M"))
> cust_mart_3
  cust_id last_name gender
1     c08       Lee      F
2     c09      Park      M
> rbind(cust_mart_12, cust_mart_3)
Error in rbind(deparse.level, ...) : 
  numbers of columns of arguments do not match

 

 

아래의 예처럼 칼럼의 이름(cust_mart_12 는 cust_id, last_name 인 반면, cust_mart_4는 cust_id, first_name)이 서로 다르다면 역시 에러가 납니다.

 

> cust_mart_4 <- data.frame(cust_id = c("c10", "c11"), 
+                           first_name = c("Kildong", "Yongpal"))
> cust_mart_4
  cust_id first_name
1     c10    Kildong
2     c11    Yongpal
> rbind(cust_mart_12, cust_mart_4)
Error in match.names(clabs, names(xi)) : 
  names do not match previous names 

 

 

(2) 열 결합 (왼쪽 + 오른쪽) : cbind(A, B)

 

> ## (2) 열 결합 cbind(A, B)
> 
> cust_mart_5 <- data.frame(age = c(20, 25, 19, 40, 32, 39, 28), 
+                           income = c(2500, 2700, 0, 7000, 3400, 3600, 2900))
> 
> cust_mart_12
  cust_id last_name
1     c01       Kim
2     c02       Lee
3     c03      Choi
4     c04      Park
5     c05       Bae
6     c06       Kim
7     c07       Lim
> cust_mart_5
  age income
1  20   2500
2  25   2700
3  19      0
4  40   7000
5  32   3400
6  39   3600
7  28   2900
> 
> cust_mart_125 <- cbind(cust_mart_12, cust_mart_5)
> cust_mart_125
  cust_id last_name age income
1     c01       Kim  20   2500
2     c02       Lee  25   2700
3     c03      Choi  19      0
4     c04      Park  40   7000
5     c05       Bae  32   3400
6     c06       Kim  39   3600
7     c07       Lim  28   2900

 

cbind()는 column bind의 약자입니다.   cbind()도 열 결합을 하려고 하면 서로 결합하려는 두 데이터셋의 관측치가 행이 서로 동일 대상이어야만 하고, 행의 갯수가 서로 같아야만 합니다

 

만약, cbind()를 하는데 있어 행의 갯수가 서로 다르다면 아래의 예처럼 에러 메시지가 뜹니다.

 

> cust_mart_6 <- data.frame(age = c(34, 50), + income = c(3600, 5100)) > cust_mart_6 age income 1 34 3600 2 50 5100 > cbind(cust_mart_125, cust_mart_6) Error in data.frame(..., check.names = FALSE) : arguments imply differing number of rows: 7, 2

 

 

 

(3) 동일 key 값 기준 결합 : merge(A, B, by='key)

 

두개의 데이터셋을 열 결합할 때 동일 key 값을 기준으로 결합을 해야 할 때가 있습니다.  cbind()의 경우 각 행의 관찰치가 서로 동일 대상일 때 그리고 갯수가 같을 때 가능하다고 했는데요, 만약 각 행의 관찰치가 서로 동일한 것도 있고 그렇지 않은 것도 섞여 있다면 그때는 cbind()를 사용하면 안됩니다.  이때는 동일 key 값을 기준으로 결합을 해주는 merge(A, B, by='key')를 사용해야만 합니다.

 

아래의 cbind()의 잘못된 예를 하나 보시겠습니다.

 

> cust_mart_12
  cust_id last_name
1     c01       Kim
2     c02       Lee
3     c03      Choi
4     c04      Park
5     c05       Bae
6     c06       Kim
7     c07       Lim
> 
> cust_mart_7 <- data.frame(cust_id = c("c03", "c04", "c05", "c06", "c07", "c08", "c09"), 
+                           buy_cnt = c(3, 1, 0, 7, 3, 4, 1))
> 
> cust_mart_7
  cust_id buy_cnt
1     c03       3
2     c04       1
3     c05       0
4     c06       7
5     c07       3
6     c08       4
7     c09       1
> cust_mart_127_cbind <- cbind(cust_mart_12, cust_mart_7)
> cust_mart_127_cbind
  cust_id last_name cust_id buy_cnt
1     c01       Kim     c03       3
2     c02       Lee     c04       1
3     c03      Choi     c05       0
4     c04      Park     c06       7
5     c05       Bae     c07       3
6     c06       Kim     c08       4
7     c07       Lim     c09       1
 
 

 

cust_mart_12 와 cust_mart_7 의 두 개의 데이터 프레임의 관측치가 서로 같은 것(cust_id 가 c03 ~ c07)도 있는 반면, 서로 다른 것(cust_id 가 c01~c02, c08~c09)도 있습니다.  이런 데이터 셋을 cbind()로 결합시켜버리면 엉뚱한 데이터 셋이 생성되어 버립니다. Oh no~!!!!!

 

이런 경우에는 동일한 key 값을 기준으로 결합을 시켜주는 merge(A, B, by='key')가 답입니다.

SQL에 익숙한 분들은 잘 아시겠지만, merge에는 기준을 어느쪽에 두고 어디까지 포함하느냐에 따라 Inner Join, Outer Join, Left Outer Join, Right Outer Join 등의 4가지 종류가 있습니다.  이를 도식화하면 아래와 같습니다.

 

[ merge() 함수의 join 종류 ]

 

 

위에 제시한 4가지 join 유형별로 merge() 함수 사용예를 들어보겠습니다.

 

(3-1) merge() : Inner Join 

 

> # (3-1) merge() : Inner Join > cust_mart_127_innerjoin <- merge(x = cust_mart_12, + y = cust_mart_7, + by = 'cust_id') > > cust_mart_127_innerjoin cust_id last_name buy_cnt 1 c03 Choi 3 2 c04 Park 1 3 c05 Bae 0 4 c06 Kim 7 5 c07 Lim 3

 

 

(3-2) merge() - Outer Join

 

> # (3-2) merge() : Outer Join > cust_mart_127_outerjoin <- merge(x = cust_mart_12, + y = cust_mart_7, + by = 'cust_id', + all = TRUE) > > cust_mart_127_outerjoin cust_id last_name buy_cnt 1 c01 Kim NA 2 c02 Lee NA 3 c03 Choi 3 4 c04 Park 1 5 c05 Bae 0 6 c06 Kim 7 7 c07 Lim 3 8 c08 <NA> 4 9 c09 <NA> 1 

 

 

(3-3) merge() : Left Outer Join

 

> # (3-3) merge() : Left Outer Join
> cust_mart_127_leftouter <- merge(x = cust_mart_12, 
+                                  y = cust_mart_7, 
+                                  by = 'cust_id', 
+                                  all.x = TRUE)
> 
> cust_mart_127_leftouter
  cust_id last_name buy_cnt
1     c01       Kim      NA
2     c02       Lee      NA
3     c03      Choi       3
4     c04      Park       1
5     c05       Bae       0
6     c06       Kim       7
7     c07       Lim       3 

 

 

(3-4) merge() : Right Outer Join

 

> # (3-4) merge : Right Outer Join
> cust_mart_127_rightouter <- merge(x = cust_mart_12, 
+                                  y = cust_mart_7, 
+                                  by = 'cust_id', 
+                                  all.y = TRUE)
> 
> cust_mart_127_rightouter
  cust_id last_name buy_cnt
1     c03      Choi       3
2     c04      Park       1
3     c05       Bae       0
4     c06       Kim       7
5     c07       Lim       3
6     c08      <NA>       4
7     c09      <NA>       1 

 

 

이상 merge() 함수의 4가지 유형의 join 에 대하여 알아보았습니다.  마지막으로, merge() 함수는 2개의 데이터 셋의 결합만 가능하며, 3개 이상의 데이터 셋에 대해서 key 값 기준 merge() 결합을 하려고 하면 에러가 나는 점 유의하시기 바랍니다.

 

> merge(cust_mart_12, cust_mart_5, cust_mart_7, by = 'cust_id')
Error in fix.by(by.x, x) : 
  'by' must specify one or more columns as numbers, names or logical

 

따라서 데이터 프레임 2개씩을 key 값 기준으로 순차적으로 merge() 해나가야 합니다.

 

이상으로 데이터 프레임의 결합에 대해서 마치도록 하겠습니다. 

많은 도움 되었기를 바랍니다.

 

이번 포스팅이 도움이 되었다면 아래의 '공감 ~♡' 단추를 꾸욱 눌러주세요.^^

 

Posted by R Friend R_Friend