R, Python 분석과 프로그래밍의 친구 (by R Friend)

'Left Join'에 해당되는 글 5건

2021.05.30 [PostgreSQL, Greenplum] JOIN 으로 여러개의 테이블, 뷰 연결하기
2021.03.01 [R 지리공간 데이터 분석] 두개의 지리 벡터 데이터 테이블을 dplyr 로 Join 하기 (Joining two geography vector data tables using R dplyr)
2021.01.31 [R data.table] 조건이 있는 상태에서 Key를 기준으로 데이터셋 합치기 (Conditional Joins)
2017.06.26 [SQL]웹으로 SQL 연습할 수 있는 웹사이트: w3schools.com/sql : 유통업체 ERD 및 Query 예시
2016.12.03 [Python pandas] Database처럼 DataFrame Join/Merge 하기 : pd.merge()

[PostgreSQL, Greenplum] JOIN 으로 여러개의 테이블, 뷰 연결하기

Greenplum and PostgreSQL Database 2021. 5. 30. 21:47

이번 포스팅에서는 PostgreSQL, Greenplum Database에서 여러개의 테이블을 Key 값을 기준으로 JOIN 구문을 사용하여 연결하는 다양한 방법을 소개하겠습니다. 그리고 두 테이블 내 관측치 간의 모든 가능한 조합을 반환해주는 CROSS JOIN 에 대해서도 마지막에 소개하겠습니다. (DB 종류에 상관없이 join SQL query는 거의 비슷합니다.)

(1) INNER JOIN

(2) LEFT JOIN

(3) RIGHT JOIN

(4) FULL JOIN

(5) 3개 이상 복수개의 테이블을 JOIN 으로 연결하기

(6) CROSS JOIN

먼저 예제로 사용한 간단한 2개의 테이블을 만들어보겠습니다. 두 테이블을 연결할 수 있는 공통의 Key값으로서 'id'라는 이름의 칼럼을 두 테이블이 모두 가지고 있습니다.

'tbl1' 과 'tbl2'는 Key 'id'를 기준으로 id = [2, 3, 4] 가 서로 동일하게 존재하며, 'tbl1'의 id = [1]은 'tbl1'에만 존재하고, 'tbl2'의 id = [5] 는 'tbl2'에만 존재합니다. 각 JOIN 방법 별로 결과가 어떻게 달라지는지 유심히 살펴보시기 바랍니다.

-- Creating two sample tables

-- sample table 1
DROP TABLE IF EXISTS tbl1;
CREATE TABLE tbl1 (
	id int
	, x text
) DISTRIBUTED RANDOMLY;

INSERT INTO tbl1 VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

SELECT * FROM tbl1 ORDER BY id;
--id  x
--1	a
--2	b
--3	c
--4   d


-- sample table 2
DROP TABLE IF EXISTS tbl2;
CREATE TABLE tbl2 (
	id int
	, y text
) DISTRIBUTED RANDOMLY;

INSERT INTO tbl2 VALUES (2, 'e'), (3, 'f'), (4, 'g'), (5, 'h');

SELECT * FROM tbl2 ORDER BY id;
--id  y
--2	e
--3	f
--4	g
--5	h

(1) INNER JOIN

INNER JOIN 은 두 테이블의 Key 값을 기준으로 교집합에 해당하는 값들만 반환합니다. 두 테이블에서 Key 값이 겹치지 않는 값들은 제거되었습니다.

--------------
-- INNER JOIN
--------------

SELECT a.id, a.x, b.y
FROM tbl1 AS a 
INNER JOIN tbl2 AS b 
ON a.id = b.id;
--id x y
--2	b	e
--3	c	f
--4	d	g

(2) LEFT OUTER JOIN

LEFT OUTER JOIN 은 왼쪽 테이블을 기준으로 Key값이 서로 같은 오른쪽 테이블의 값들을 왼쪽 테이블에 연결해줍니다. 아래의 예에서는 왼쪽의 'tbl1'의 값들은 100% 모두 있고, LEFT OUTER JOIN 으로 연결해준 오른쪽 'tbl2' 테이블의 경우 id = [5] 의 값이 제거된 채 id = [2, 3, 4] 에 해당하는 값들만 'tbl1'과 연결이 되었습니다. 그리고 왼쪽 'tbl1'에는 있지만 오른쪽 'tbl2'에는 없는 id = [1] 에 해당하는 값의 경우 y = [NULL] 값을 반환하였습니다.

-------------------
-- LEFT OUTER JOIN
-------------------

SELECT a.id, x, y
FROM tbl1 AS a 
LEFT OUTER JOIN tbl2 AS b 
ON a.id = b.id;
--id x y
--1	a	[NULL]
--2	b	e
--3	c	f
--4	d	g

(3) RIGHT OUTER JOIN

RIGHT OUTER JOIN 은 LEFT OUTER JOIN 과 정반대라고 생각하면 이해하기 쉽습니다. 이번에는 오른쪽 테이블을 기준으로 Key 값이 같은 왼쪽 테이블의 값을 오른쪽 테이블에 연결해줍니다.

아래 RIGHT OUTER JOIN 예에서는 오른쪽 테이블은 'tbl2'는 100% 모두 있고, 왼쪽 테이블 'tbl1'의 경우 'tbl2'와 Key 값이 동일한 id = [2, 3, 4] 에 해당하는 값들만 'tbl2'에 연결이 되었습니다. 'tbl2'에만 존재하고 'tbl1'에는 없는 id = [5] 의 경우 'tbl1'의 'x' 칼럼 값은 [NULL] 값이 됩니다.

--------------------
-- RIGHT OUTER JOIN
--------------------

SELECT a.id, x, y
FROM tbl1 AS a 
RIGHT OUTER JOIN tbl2 AS b 
ON a.id = b.id;
--id x y
--2	b	e
--3	c	f
--4	d	g
--5 [NULL]	h

(4) FULL JOIN

FULL JOIN은 양쪽 테이블 모두를 기준으로 Key 값이 같은 값들을 연결시켜 줍니다. 이때 한쪽 테이블에만 Key 값이 존재할 경우 다른쪽 테이블의 칼럼 값에는 [NULL] 값을 반환합니다.

제일 위에 있는 도식화 그림을 참고하시면 위의 INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN에 대해서 좀더 이해하기 쉬울 거예요.

---------------
-- FULL JOIN
---------------

SELECT a.id, x, y
FROM tbl1 AS a 
FULL JOIN tbl2 AS b
ON a.id = b.id;
--id x y
--1	a	[NULL]
--2	b	e
--3	c	f
--4	d	g
--5 [NULL] h

(5) 3개 이상의 복수개의 테이블을 JOIN 으로 연결하기

위의 (1)~(4) 는 2개의 테이블을 Key 값을 기준으로 JOIN 문으로 연결한 것이었습니다. 만약 3개 이상의 복수개의 테이블을 JOIN 으로 연결하고자 한다면 아래의 예처럼 JOIN 문과 연결 Key 값을 ON 으로 이어서 써주면 됩니다.

아래의 예는 'tbl1' 테이블을 기준으로 'tbl2', 'tbl3'를 'id' Key를 기준으로 LEFT OUTER JOIN 한 것입니다.

--------------------------------------------------------
-- LEFT OUTER JOIN with Multiple Tables
--------------------------------------------------------

-- creating the 3rd table
DROP TABLE IF EXISTS tbl3;
CREATE TABLE tbl3 (
	id int
	, z text
) DISTRIBUTED RANDOMLY;

INSERT INTO tbl2 VALUES (2, 'i'), (4, 'j'), (6, 'k'), (8, 'l');
SELECT * FROM tbl3 ORDER BY id;
--id  z
--2	i
--4	j
--6	k
--7	l


-- LEFT OUTER JOIN with 3 tables
SELECT a.id, x, y
FROM tbl1 AS a 
LEFT OUTER JOIN tbl2 AS b 
	ON a.id = b.id
LEFT OUTER JOIN tbl3 AS c	
	ON a.id = c.id
ORDER BY a.id;
--id x y z
--1	a	[NULL] [NULL]
--2	b	e i
--3	c	f [NULL]
--4	d	g j

(6) CROSS JOIN

위의 (1)~(5)까지의 JOIN은 두 테이블에 동일하게 존재하는 Key값을 기준으로 두 테이블을 연결하여 주었다면, 이제 CROSS JOIN 은 두 테이블의 모든 값들 간의 조합을 반환하며, 이때 Key 값은 필요없습니다. 가령 왼쪽 테이블에 m 개의 행이 있고, 오른쪽 테이블에 n 개의 행이 있다면 두 테이블의 CROSS JOIN은 m * n 개의 조합(combination)을 반환합니다.

실수로 행의 개수가 엄청나게 많은 두 테이블을 CROSS JOIN 하게 될 경우 시간도 오래 걸리고 자칫 memory full 나서 DB가 다운되는 경우도 있습니다. 따라서 CROSS JOIN 을 할 때는 지금 하려는 작업이 CROSS JOIN 요건이 맞는 것인지 꼭 한번 더 확인이 필요하며, 소요 시간이나 메모리가 여력이 되는지에 대해서도 먼저 가늠해볼 필요가 있습니다.

----------------
-- CROSS JOIN
----------------

SELECT a.id AS id_a, a.x, b.id AS id_b, b.y
FROM tbl1 AS a 
CROSS JOIN tbl2 AS b
ORDER BY a.id, b.id;
--id_a x id_b y
--1	a	2	e
--1	a	3	f
--1	a	4	g
--1	a	5	h
--2	b	2	e
--2	b	3	f
--2	b	4	g
--2	b	5	h
--3	c	2	e
--3	c	3	f
--3	c	4	g
--3	c	5	h
--4	d	2	e
--4	d	3	f
--4	d	4	g
--4	d	5	h

이번 포스팅이 많은 도움이 되었기를 바랍니다.

행복한 데이터 과학자 되세요! :-)

728x90

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

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum] SELECT 결과의 합집합 UNION, 교집합 INTERSECT, 차집합 EXCEPT (0)	2021.05.30
[PostgreSQL, Greenplum] SELECT 결과의 합집합 UNION, UNION ALL (0)	2021.05.30
[Greenplum] DBeaver SQL 대문자 기본 설정, 테마 설정, 폰트 크기 설정 (0)	2021.05.23
[Greenplum] PL/Python으로 일원분산분석을 병렬처리하기 (one-way ANOVA in parallel using PL/Python on Greenplum) (0)	2021.05.09
[Greenplum DB] Greenplum 에서 PL/Python으로 Random Forest 분산병렬처리하여 Feature Importance 계산하기 (0)	2021.04.18

Posted by Rfriend

[R 지리공간 데이터 분석] 두개의 지리 벡터 데이터 테이블을 dplyr 로 Join 하기 (Joining two geography vector data tables using R dplyr)

R 분석과 프로그래밍/R 지리공간데이터 분석 2021. 3. 1. 19:32

지난번 포스팅에서는 R 지리공간 벡터 데이터의 속성 정보에 대해서 Base R, dplyr, data.table 패키지를 사용하여 그룹별로 집계하는 방법(rfriend.tistory.com/624)을 소개하였습니다.

이번 포스팅에서는 dplyr 패키지를 사용하여 두 개의 지리공간 벡터 데이터 테이블을 Join 하는 여러가지 방법을 소개하겠습니다. [1] Database SQL에 이미 익숙한 분이라면 이번 포스팅은 매우 쉽습니다. 왜냐하면 dplyr 의 두 테이블 간 Join 이 SQL의 Join 을 차용해서 만들어졌기 때문입니다.

R의 sf 클래스 객체인 지리공간 벡터 데이터를 dplyr 의 함수를 사용해서 두 테이블을 join 하면 속성(attributes)과 함께 지리공간 geometry 칼럼과 정보도 join 된 후의 테이블에 자동으로 그대로 따라가게 됩니다.

(1) Mutating Joins : 두 테이블을 합쳐서 새로운 테이블을 생성하기

- (1-1) inner join

- (1-2) left join

- (1-3) right join

- (1-4) full join

(2) Filtering Joins : 두 테이블의 매칭되는 부분을 기준으로 한쪽 테이블을 걸러내기

- (2-1) semi join

- (2-2) anti join

(3) Nesting joins : 한 테이블의 모든 칼럼을 리스트로 중첩되게 묶어서 다른 테이블에 합치기

- (3-1) nest join

R dplyr 패키지가 두 테이블 Join 을 하는데 제공하는 함수는 inner_join(), left_join(), right_join(), full_join(), semi_join(), anti_join(), nest_join() 의 총 7개가 있으며, 이는 크게 (a) Mutating Joins, (b) Filtering Joins, (3) Nesting Joins의 3개의 범주로 분류할 수 있습니다.

[ R dplyr 패키지로 두 개의 테이블 Join 하기 (Joining two tables together using R dplyr) ]

(1) Mutating Joins

Mutation Joins 는 두 개의 테이블을 Key를 기준으로 Join 하여 두 개 테이블로 부터 가져온 (전체 또는 일부) 행과 모든 열로 Join 하여 새로운 테이블을 만들 때 사용합니다. 위의 그림에서 보는 바와 같이 왼쪽(Left Hand Side, LHS)의 테이블과 오른쪽(Right Hand Side, RHD)의 테이블로 부터 모두 행과 열을 가져와서 Join 된 테이블을 반환하며, 이때 왼쪽(LHS)와 오른쪽(RHS) 중에서 어느쪽 테이블이 기준이 되느냐에 따라 사용하는 함수가 달라집니다.

(1-1) inner join

먼저, 예제로 사용할 sf 클래스 객체로서, spData 패키지에서 세계 국가별 속성정보와 지리기하 정보를 가지고 있는 'world' 데이터셋, 그리고 2016년과 2017년도 국가별 커피 생산량을 집계한 coffee_data 데이터셋을 가져오겠습니다. "world" 데이터셋은 177개의 관측치, 11개의 칼럼을 가지고 있고, "coffee_data" 데이터셋은 47개의 관측치, 3개의 칼럼을 가지고 있습니다. 그리고 두 데이터셋은 공통적으로 'name_long' 이라는 국가이름 칼럼을 가지고 있으며, 이는 두 테이블을 Join 할 때 기준 Key 로 사용이 됩니다.

테이블 Join 을 위해 dplyr 패키지를 불러오겠습니다.

## ==================================
## GeoSpatial Data Analysis using R
## : Vector attribute joining
## : reference: https://geocompr.robinlovelace.net/attr.html
## ==================================

library(sf)
library(spData) # for sf data
library(dplyr)

## -- (a) world: World country pologons in spData
names(world)
# [1] "iso_a2"  "name_long" "continent" "region_un" "subregion" "type"  "area_km2"  "pop"  "lifeExp"   "gdpPercap"
# [11] "geom"

dim(world)
# [1] 177  11


## -- (b) coffee_data: World coffee productiond data in spData
## : estimated values for coffee production in units of 60-kg bags in each year
names(coffee_data)
# [1] "name_long"      "coffee_production_2016" "coffee_production_2017"

dim(coffee_data)
# [1] 47  3

dplyr 패키지의 테이블 Join 에 사용하는 함수들의 기본 구문은 아래와 같이 왼쪽(x, LHS), 오른쪽(y, RHS) 테이블, 두 테이블을 매칭하는 기준 칼럼(by), 데이터 source가 다를 경우 복사(copy) 여부, 접미사(suffix) 등의 매개변수로 구성되어 서로 비슷합니다.

## dplyr join syntax
library(dplyr)

## -- (a) Mutating Joins
inner_join(x, y, by = NULL, copy = FALSE, suffix = c(".x", ".y"), ...)
left_join(x, y, by = NULL, copy = FALSE, suffix = c(".x", ".y"), ...)
right_join(x, y, by = NULL, copy = FALSE, suffix = c(".x", ".y"), ...)
full_join(x, y, by = NULL, copy = FALSE, suffix = c(".x", ".y"), ...)

## -- (b) Filtering Joins
semi_join(x, y, by = NULL, copy = FALSE, ...)
anti_join(x, y, by = NULL, copy = FALSE, ...)

## -- (c) Nesting Joins
nest_join(x, y, by = NULL, copy = FALSE, keep = FALSE, name = NULL, ...)

inner join 은 두 테이블에서 Key 칼럼을 기준으로 서로 매칭이 되는 행에 대해서만, 두 테이블의 모든 칼럼을 반환합니다. 그럼, "world"와 "coffee_data" 두 데이터셋 테이블을 공통의 칼럼인 "name_long" 을 기준으로 inner join 해보겠습니다. 두 테이블에 공통으로 "name_long"이 존재하는 관측치가 45개가 있네요.

만약 두 테이블 x, y 에 다수의 매칭되는 값이 있을 경우에는, 모든 가능한 조합의 값을 반환하므로, 주의가 필요합니다.

dplyr 의 Join 함수들은 두 테이블 Join 의 기준이 되는 Key 칼럼 이름을 by 매개변수에 안써주면 두 테이블에 공통으로 존재하는 칼럼을 Key 로 삼아서 Join 을 수행하고, 콘솔 창에 'Joining, by = "name_long"' 과 같이 Key 를 출력해줍니다.

## -- (1) Mutating Joins
## -- (1-1) inner join
world_coffee_inner = inner_join(x = world,       # LHS
                                y = coffee_data, # RHS
                                by = "name_long" # joining key
                                )

## or shortly
world_coffee_inner = inner_join(world, coffee_data)
# Joining, by = "name_long"


dim(world_coffee_inner)
# [1] 45 13

nrow(world_coffee_inner)
# [1] 45

(1-2) left join

left join 은 왼쪽의 테이블(LHS, x)을 모두 반환하고 (기준이 됨), 오른쪽 테이블(RHS, y)은 왼쪽 테이블과 Key 값이 매칭되는 관측치에 대해서만 모든 칼럼을 왼쪽 테이블에 Join 하여 반환합니다. 만약 오른쪽 테이블(RHS, y)에 매칭되는 값이 없는 경우 x 테이블의 y에 해당하는 행은 NA 로 채워집니다.

아래 예에서는 왼쪽에 있는 "world" 테이블을 기준으로 오른쪽의 "coffee_data"를 공통으로 존재하는 'name_long' 칼럼을 Key로 해서 left join 을 한 것입니다. 12번째와 13번째 칼럼에 오른쪽 테이블인 "coffee_data" 에서 Join 해서 가져온 "coffee_production_2016", "coffee_production_2017"의 칼럼이 왼쪽 "world" 테이블에 Join 이 되었습니다.

plot() 함수로 다면(multi-polygons) 기하도형으로 구성된 세계 국가별 지도에 2017년도 커피 생산량을 시각화해보았습니다. 지리기학 벡터 데이터를 Join 했을 때 누릴 수 있는 geometry 칼럼을 사용할 수 있는 혜택이 되겠습니다.

## -- (1-2) left join
world_coffee_left = left_join(world, coffee_data)
# Joining, by = "name_long"

class(world_coffee_left)
# [1] "sf"         "tbl_df"     "tbl"        "data.frame"

names(world_coffee_left)
# [1] "iso_a2"                 "name_long"              "continent"              "region_un"             
# [5] "subregion"              "type"                   "area_km2"               "pop"                   
# [9] "lifeExp"                "gdpPercap"              "geom"                   "coffee_production_2016"
# [13] "coffee_production_2017"

plot(world_coffee_left["coffee_production_2017"])

두 테이블을 Join 할 때 기준이 되는 Key 칼럼의 이름이 서로 다른 경우 by 매개변수에 서로 다른 변수 이름을 구체적으로 명시해주면 됩니다. 아래 예에서는 오른쪽 "coffee_data" 테이블의 'name_long' 칼럼 이름을 'nm'으로 바꿔준 후에, by = c(name_long = "nm") 처럼 Join하려는 두 테이블의 서로 다른 이름의 Key 변수들을 명시해주었습니다.

## -- Using the 'by' argument to specify the joining variables
coffee_renamed = rename(coffee_data, nm = name_long)
world_coffee2 = left_join(world, coffee_renamed, 
                          by = c(name_long = "nm")) # specify the joining variables

names(world_coffee2)
# [1] "iso_a2"                 "name_long"              "continent"              "region_un"             
# [5] "subregion"              "type"                   "area_km2"               "pop"                   
# [9] "lifeExp"                "gdpPercap"              "geom"                   "coffee_production_2016"
# [13] "coffee_production_2017"

(1-3) right join

right join 은 오른쪽 테이블(RHS, y) 을 전부 반환하고, 왼쪽 테이블 (LHS, x) 은 오른쪽(y) 테이블과 매칭이 되는 값에 대해서만 모든 칼럼을 Join 해서 반환합니다. Key 칼럼을 기준으로 왼쪽 테이블에 없는 값은 NA 처리가 되어 오른쪽 테이블에 Join 됩니다. (위의 그림 도식을 참고하세요).

만약 왼쪽과 오른쪽 테이블에 다수의 매칭되는 값들이 있을 경우 매칭되는 값들의 모든 조합으로 Join 됩니다. 아래 예에서 Join 의 기준이 되는 Key 를 명기해주는 매개변수 by = 'name_long' 는 두 테이블에 공통으로 존재하므로 생략 가능합니다.

## -- (1-3) right join: return all rows from y, and all columns from x.
world_coffee_right = right_join(x = world, 
                                y = coffee_data, 
                                by = 'name_long')


dim(world) # -- left
# [1] 177  11

dim(coffee_data) # -- right
# [1] 47  3

dim(world_coffee_right) # -- right join
# [1] 47 13

(1-4) full join

full Join 은 왼쪽 (LHS, x)과 오른쪽(RHS, y)의 모든 행과 열을 반환합니다.

## -- (1-4) full join: return all rows and all columns from both x and y.
world_coffee_full = full_join(x = world, 
                              y = coffee_data, 
                              by = 'name_long')

dim(world_coffee_full)
# [1] 179  13


names(world_coffee_full)
# [1] "iso_a2"        "name_long"   "continent"    "region_un"             
# [5] "subregion"     "type"        "area_km2"     "pop"                   
# [9] "lifeExp"       "gdpPercap"   "geom"         "coffee_production_2016"
# [13] "coffee_production_2017"

어느 한쪽 테이블에서 버려지는 값이 없으며, 만약 왼쪽이나 오른쪽 테이블에 없는 값이면 "NA" 처리됩니다. 아래의 왼쪽 "world" 테이블과 오른쪽의 "coffee_data" 테이블 간에 서로 매칭되지 않는 부분은 "NA"가 들어가 있음을 알 수 있습니다.

## Where there are not matching values, returns 'NA' for the one missing.
head(world_coffee_full[, c(2:3, 9:13)], 10)

# Simple feature collection with 10 features and 6 fields
# geometry type:  MULTIPOLYGON
# dimension:      XY
# bbox:           xmin: -180 ymin: -55.25 xmax: 180 ymax: 83.23324
# geographic CRS: WGS 84
# # A tibble: 10 x 7
# name_long   continent   lifeExp gdpPercap                                        geom coffee_productio~ coffee_productio~
#   <chr>       <chr>         <dbl>     <dbl>                 <MULTIPOLYGON [arc_degree]>             <int>             <int>
#  1 Fiji        Oceania        70.0     8222. (((180 -16.06713, 180 -16.55522, 179.3641 ~                NA                NA
#  2 Tanzania    Africa         64.2     2402. (((33.90371 -0.95, 34.07262 -1.05982, 37.6~                81                66
#  3 Western Sa~ Africa         NA         NA  (((-8.66559 27.65643, -8.665124 27.58948, ~                NA                NA
#  4 Canada      North Amer~    82.0    43079. (((-122.84 49, -122.9742 49.00254, -124.91~                NA                NA
#  5 United Sta~ North Amer~    78.8    51922. (((-122.84 49, -120 49, -117.0312 49, -116~                NA                NA
#  6 Kazakhstan  Asia           71.6    23587. (((87.35997 49.21498, 86.59878 48.54918, 8~                NA                NA
#  7 Uzbekistan  Asia           71.0     5371. (((55.96819 41.30864, 55.92892 44.99586, 5~                NA                NA
#  8 Papua New ~ Oceania        65.2     3709. (((141.0002 -2.600151, 142.7352 -3.289153,~               114                74
#  9 Indonesia   Asia           68.9    10003. (((141.0002 -2.600151, 141.0171 -5.859022,~               742               360
# 10 Argentina   South Amer~    76.3    18798. (((-68.63401 -52.63637, -68.25 -53.1, -67.~                NA                N

(2) Filtering Joins

Filtering Joins 은 두 테이블의 매칭되는 값을 기준으로 한쪽 테이블의 값을 걸러내는데 사용합니다.

(2-1) semi join

semi join 은 왼쪽(LHS, x)과 오른쪽(RHS, y) 테이블의 서로 매칭되는 값에 대해 왼쪽(LHS, x)의 모든 칼럼을 반환합니다. 이때 매칭 여부를 평가하는데 사용되었던 오른쪽 테이블(RHS, y)의 값은 하나도 가져오지 않으며, 단지 왼쪽 테이블(x)을 걸러내느데(filtering)만 사용하였다는 점이 위의 (1-2) Left Join 과 다른 점입니다. (위의 도식을 참고하세요)

## -- (2) Filtering joins
## -- (2-1) semi join
## : return all rows from x where there are matching values in y, 
## : keeping just columns form x.

world_coffee_semi = semi_join(world, coffee_data)
# Joining, by = "name_long"

dim(world_coffee_semi)
# [1] 45 11

names(world_coffee_semi)
# [1] "iso_a2"    "name_long" "continent" "region_un" "subregion" "type"  "area_km2"  "pop"      
# [9] "lifeExp"   "gdpPercap" "geom"

(2-2) anti join

anti join 은 왼쪽 테이블(LHS, x)과 오른쪽 테이블(RHS, y)의 매칭되는 부분을 왼쪽 테이블(LHS, x)에서 걸러낸 x의 모든 칼럼을 반환합니다. 이때 매칭 여부를 평가하는데 사용되었던 오른쪽(RHS, y) 테이블의 값은 하나도 가져오지 않으며, 단지 왼쪽 테이블(x)을 걸러내는데(filtering)만 사용합니다.

위의 (2-1)의 semi join 은 x와 y의 매칭되는 부분의 x값만을 반환하였다면, 이번 (2-2)의 anti join 은 반대로 x와 j의 매칭이 안되는 부분의 x값만을 반환하는게 다릅니다. (y 값은 안가져오는 것은 semi join 과 anti join 이 동일함.)

## -- (6) anti join
## : return all rows from x where there are not matching values in y, 
## : keeping just columns from x.
world_coffee_anti = anti_join(world, coffee_data)
# Joining, by = "name_long"

dim(world_coffee_anti)
# [1] 132  11

names(world_coffee_anti)
# [1] "iso_a2"    "name_long" "continent" "region_un" "subregion" "type"      "area_km2"  "pop"      
# [9] "lifeExp"   "gdpPercap" "geom"

(3) Nesting Joins

(3-1) nest join

nest join 은 왼쪽 테이블(LHS, x)의 모든 행과 열을 반환하며, 이때 오른쪽(RHS, y)의 매칭되는 부분의 모든 칼럼의 값들을 list 형태로 중첩되게 묶어서 왼쪽 x 테이블에 join 해줍니다. 즉, 오른쪽 y 테이블의 매칭되는 값들의 칼럼이 여러개 이더라도 왼쪽 x 테이블에 join 이 될 때는 1개의 칼럼에 list 형태로 오른쪽 y 테이블의 여러개 칼럼의 값들이 묶여서 join 됩니다.

## -- (3) Nesting joins
## -- (3-1) nest join
## : eturn all rows and all columns from x. Adds a list column of tibbles. 
## : Each tibble contains all the rows from y that match that row of x. 
world_coffee_nest = nest_join(world, coffee_data)
# Joining, by = "name_long"

dim(world_coffee_nest)
# [1] 177  12

names(world_coffee_nest)
# [1] "iso_a2"      "name_long"   "continent"   "region_un"   "subregion"   "type"        "area_km2"   
# [8] "pop"         "lifeExp"     "gdpPercap"   "geom"        "coffee_data"


head(world_coffee_nest[, 10:12], 3)
# Simple feature collection with 3 features and 2 fields
# geometry type:  MULTIPOLYGON
# dimension:      XY
# bbox:           xmin: -180 ymin: -18.28799 xmax: 180 ymax: 27.65643
# geographic CRS: WGS 84
# # A tibble: 3 x 3
# gdpPercap                                                                                geom coffee_data    
# <dbl>                                                         <MULTIPOLYGON [arc_degree]> <list>         
# 1     8222. (((180 -16.06713, 180 -16.55522, 179.3641 -16.80135, 178.7251 -17.01204, 178.5968 ~ <tibble [0 x 2~
# 2     2402. (((33.90371 -0.95, 34.07262 -1.05982, 37.69869 -3.09699, 37.7669 -3.67712, 39.2022~ <tibble [1 x 2~
# 3       NA  (((-8.66559 27.65643, -8.665124 27.58948, -8.6844 27.39574, -8.687294 25.88106, -1~ <tibble [0 x 2~

말로만 설명하면 잘 이해가 안될 듯 하여 아래에 nest_join(world, coffee_data) 된 테이블의 아웃풋을 화면 캡쳐하였습니다. nest join 된 후의 테이블에서 오른쪽의 "coffee_data" 라는 1개의 칼럼에 보면 list(coffee_proeuction_2016 = 81, coffee_proeuction_2017 = xx) 라고 해서 "coffee_data" 에 들어있는 2개의 칼럼이 1개의 리스트 형태의 칼럼에 중첩이 되어서 들어가 있음을 알 수 있습니다.

다음번 포스팅에서는 Join 했을 때 Join 의 기준이 되는 Key 값이 일부 표준화가 안되어서 제대로 Join 이 안될 경우에 정규 표현식(Regular expression)을 사용해서 Join 하는 방법(rfriend.tistory.com/626)을 소개하겠습니다.

[Reference]

[1] [dplyr] join two tables together: dplyr.tidyverse.org/reference/join.html

[2] [Geocomputation with R] Attritube data operations: geocompr.robinlovelace.net/attr.html

이번 포스팅이 많은 도움이 되었기를 바랍니다.

행복한 데이터 과학자 되세요! :-)

728x90

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

'R 분석과 프로그래밍 > R 지리공간데이터 분석' 카테고리의 다른 글

[R 지리공간 데이터 분석] 지리 벡터 데이터의 새로운 속성 만들기, 지리정보 제거하기 (creating attributes and removing spatial information) (0)	2021.03.04
[R 지리공간 데이터 분석] 문자열 매칭으로 Key를 매칭해서 테이블 Join 하기 (Joining two tables using a string matching by R stringr, dplyr) (0)	2021.03.01
[R 지리공간 데이터 분석] 지리공간 벡터의 속성 데이터를 그룹별로 집계하기 (vector attribute aggregation by group) (0)	2021.03.01
[R 지리공간 데이터 분석] 벡터 데이터의 속성 정보 가져오기 (subset attributes from vector data) (0)	2021.02.21
[R 지리공간 데이터 분석] 공간의 단위 (Spatial Units) (2)	2021.02.14

Posted by Rfriend

[R data.table] 조건이 있는 상태에서 Key를 기준으로 데이터셋 합치기 (Conditional Joins)

R 분석과 프로그래밍/R 데이터 전처리 2021. 1. 31. 23:36

지난 포스팅에서는 R data.table 패키지에서 선형회귀 모델의 오른쪽 부분의 변수 조합을 .SD, .SDcols, lapply(), sapply()를 사용해서 간단하게 단 몇줄의 코드로 처리하는 방법(rfriend.tistory.com/609)을 소개하였습니다.

이번 포스팅에서는 R data.table 패키지에서 '조건이 있는 상태에서 Key를 기준으로 데이터셋을 Left Join 하는 방법 (Conditional Joins)'을 소개하겠습니다. 그리고 base R이나 dplyr 대비 data.table의 조건이 있는 경우의 데이터셋끼리 병합이 얼마나 간단한지 비교를 해보겠습니다. 이번 포스팅은 R data.table의 Vignettes 을 참조하였습니다.

(1) data.table을 이용한 조건이 있는 경우의 Left Join

(2) dplyr을 이용한 조건이 있는 경우의 Left Join 비교

먼저, data.table 패키지를 불러오고, 예제로 사용할 데이터로 Lahman 패키지에 들어있는 야구 투구 통계 데이터인 'Pitching' 데이터셋과 야구 팀 성적 통계 데이터인 'Teams' 데이터셋을 참조해서 data.table과 data.frame으로 만들어보겠습니다.

library(data.table)

## Lahman database on baseball
#install.packages("Lahman")
library(Lahman)

data("Pitching")

## data.frame
Pitching_df <- data.frame(Pitching)


## data.table
setDT(Pitching)

str(Pitching)
# Classes 'data.table' and 'data.frame':	47628 obs. of  30 variables:
#   $ playerID: chr  "bechtge01" "brainas01" "fergubo01" "fishech01" ...
# $ yearID  : int  1871 1871 1871 1871 1871 1871 1871 1871 1871 1871 ...
# $ stint   : int  1 1 1 1 1 1 1 1 1 1 ...
# $ teamID  : Factor w/ 149 levels "ALT","ANA","ARI",..: 97 142 90 111 90 136 111 56 97 136 ...
# $ lgID    : Factor w/ 7 levels "AA","AL","FL",..: 4 4 4 4 4 4 4 4 4 4 ...
# $ W       : int  1 12 0 4 0 0 0 6 18 12 ...
# $ L       : int  2 15 0 16 1 0 1 11 5 15 ...
# $ G       : int  3 30 1 24 1 1 3 19 25 29 ...
# $ GS      : int  3 30 0 24 1 0 1 19 25 29 ...
# $ CG      : int  2 30 0 22 1 0 1 19 25 28 ...
# $ SHO     : int  0 0 0 1 0 0 0 1 0 0 ...
# $ SV      : int  0 0 0 0 0 0 0 0 0 0 ...
# $ IPouts  : int  78 792 3 639 27 3 39 507 666 747 ...
# $ H       : int  43 361 8 295 20 1 20 261 285 430 ...
# $ ER      : int  23 132 3 103 10 0 5 97 113 153 ...
# $ HR      : int  0 4 0 3 0 0 0 5 3 4 ...
# $ BB      : int  11 37 0 31 3 0 3 21 40 75 ...
# $ SO      : int  1 13 0 15 0 0 1 17 15 12 ...
# $ BAOpp   : num  NA NA NA NA NA NA NA NA NA NA ...
# $ ERA     : num  7.96 4.5 27 4.35 10 0 3.46 5.17 4.58 5.53 ...
# $ IBB     : int  NA NA NA NA NA NA NA NA NA NA ...
# $ WP      : int  7 7 2 20 0 0 1 15 3 44 ...
# $ HBP     : int  NA NA NA NA NA NA NA NA NA NA ...
# $ BK      : int  0 0 0 0 0 0 0 2 0 0 ...
# $ BFP     : int  146 1291 14 1080 57 3 70 876 1059 1334 ...
# $ GF      : int  0 0 0 1 0 1 1 0 0 0 ...
# $ R       : int  42 292 9 257 21 0 30 243 223 362 ...
# $ SH      : int  NA NA NA NA NA NA NA NA NA NA ...
# $ SF      : int  NA NA NA NA NA NA NA NA NA NA ...
# $ GIDP    : int  NA NA NA NA NA NA NA NA NA NA ...
# - attr(*, ".internal.selfref")=<externalptr> 


data("Teams")
setDT(Teams)
str(Teams)
# Classes 'data.table' and 'data.frame':	2925 obs. of  48 variables:
#   $ yearID        : int  1871 1871 1871 1871 1871 1871 1871 1871 1871 1872 ...
# $ lgID          : Factor w/ 7 levels "AA","AL","FL",..: 4 4 4 4 4 4 4 4 4 4 ...
# $ teamID        : Factor w/ 149 levels "ALT","ANA","ARI",..: 24 31 39 56 90 97 111 136 142 8 ...
# $ franchID      : Factor w/ 120 levels "ALT","ANA","ARI",..: 13 36 25 56 70 85 91 109 77 9 ...
# $ divID         : chr  NA NA NA NA ...
# $ Rank          : int  3 2 8 7 5 1 9 6 4 2 ...
# $ G             : int  31 28 29 19 33 28 25 29 32 58 ...
# $ Ghome         : int  NA NA NA NA NA NA NA NA NA NA ...
# $ W             : int  20 19 10 7 16 21 4 13 15 35 ...
# $ L             : int  10 9 19 12 17 7 21 15 15 19 ...
# $ DivWin        : chr  NA NA NA NA ...
# $ WCWin         : chr  NA NA NA NA ...
# $ LgWin         : chr  "N" "N" "N" "N" ...
# $ WSWin         : chr  NA NA NA NA ...
# $ R             : int  401 302 249 137 302 376 231 351 310 617 ...
# $ AB            : int  1372 1196 1186 746 1404 1281 1036 1248 1353 2571 ...
# $ H             : int  426 323 328 178 403 410 274 384 375 753 ...
# $ X2B           : int  70 52 35 19 43 66 44 51 54 106 ...
# $ X3B           : int  37 21 40 8 21 27 25 34 26 31 ...
# $ HR            : int  3 10 7 2 1 9 3 6 6 14 ...
# $ BB            : int  60 60 26 33 33 46 38 49 48 29 ...
# $ SO            : int  19 22 25 9 15 23 30 19 13 28 ...
# $ SB            : int  73 69 18 16 46 56 53 62 48 53 ...
# $ CS            : int  16 21 8 4 15 12 10 24 13 18 ...
# $ HBP           : int  NA NA NA NA NA NA NA NA NA NA ...
# $ SF            : int  NA NA NA NA NA NA NA NA NA NA ...
# $ RA            : int  303 241 341 243 313 266 287 362 303 434 ...
# $ ER            : int  109 77 116 97 121 137 108 153 137 166 ...
# $ ERA           : num  3.55 2.76 4.11 5.17 3.72 4.95 4.3 5.51 4.37 2.9 ...
# $ CG            : int  22 25 23 19 32 27 23 28 32 48 ...
# $ SHO           : int  1 0 0 1 1 0 1 0 0 1 ...
# $ SV            : int  3 1 0 0 0 0 0 0 0 1 ...
# $ IPouts        : int  828 753 762 507 879 747 678 750 846 1548 ...
# $ HA            : int  367 308 346 261 373 329 315 431 371 573 ...
# $ HRA           : int  2 6 13 5 7 3 3 4 4 3 ...
# $ BBA           : int  42 28 53 21 42 53 34 75 45 63 ...
# $ SOA           : int  23 22 34 17 22 16 16 12 13 77 ...
# $ E             : int  243 229 234 163 235 194 220 198 218 432 ...
# $ DP            : int  24 16 15 8 14 13 14 22 20 22 ...
# $ FP            : num  0.834 0.829 0.818 0.803 0.84 0.845 0.821 0.845 0.85 0.83 ...
# $ name          : chr  "Boston Red Stockings" "Chicago White Stockings" "Cleveland Forest Citys" "Fort Wayne Kekiongas" ...
# $ park          : chr  "South End Grounds I" "Union Base-Ball Grounds" "National Association Grounds" "Hamilton Field" ...
# $ attendance    : int  NA NA NA NA NA NA NA NA NA NA ...
# $ BPF           : int  103 104 96 101 90 102 97 101 94 106 ...
# $ PPF           : int  98 102 100 107 88 98 99 100 98 102 ...
# $ teamIDBR      : chr  "BOS" "CHI" "CLE" "KEK" ...
# $ teamIDlahman45: chr  "BS1" "CH1" "CL1" "FW1" ...
# $ teamIDretro   : chr  "BS1" "CH1" "CL1" "FW1" ...
# - attr(*, ".internal.selfref")=<externalptr>

(1) data.table을 이용한 조건이 있는 경우의 Left Join

(1-1) 참여 경기 수 조건 하에 팀별 연도별 평균자책점 순위 (rank_in_team)

(조건) G > 5 : 6개 이상의 경기(G, game)에 참여하여 공을 던진 투수에 한하여,

(연산) ERA (평균 자책점, Earned Run Average)의 순위를 구해서 rank_in_team 변수를 만들되,

(그룹 기준) 'teamID' & 'yearID' 그룹 별로 ERA 순위(frank(ERA))를 구하여라.

조건, 연산, 그룹 기준이 모두 Pitching[조건, 연산, 그룹 기준] 의 구문으로 해서 단 한줄로 모두 처리가 가능합니다. 간결함의 끝판왕이라고나 할까요!

## -- rank in team using frank() function by teamID & yearID groups
## to exclude pitchers with exceptional performance in a few games,
##   subset first; then define rank of pitchers within their team each year
##   (in general, we should put more care into the 'ties.method' of frank)
Pitching[G > 5, rank_in_team := frank(ERA), by = .(teamID, yearID)]

head(Pitching)
#    playerID yearID stint teamID lgID  W  L  G GS CG SHO SV IPouts   H  ER HR BB SO BAOpp   ERA
# 1: bechtge01   1871     1    PH1   NA  1  2  3  3  2   0  0     78  43  23  0 11  1    NA  7.96
# 2: brainas01   1871     1    WS3   NA 12 15 30 30 30   0  0    792 361 132  4 37 13    NA  4.50
# 3: fergubo01   1871     1    NY2   NA  0  0  1  0  0   0  0      3   8   3  0  0  0    NA 27.00
# 4: fishech01   1871     1    RC1   NA  4 16 24 24 22   1  0    639 295 103  3 31 15    NA  4.35
# 5: fleetfr01   1871     1    NY2   NA  0  1  1  1  1   0  0     27  20  10  0  3  0    NA 10.00
# 6: flowedi01   1871     1    TRO   NA  0  0  1  0  0   0  0      3   1   0  0  0  0    NA  0.00
#    IBB WP HBP BK  BFP GF   R SH SF GIDP rank_in_team
# 1:  NA  7  NA  0  146  0  42 NA NA   NA           NA
# 2:  NA  7  NA  0 1291  0 292 NA NA   NA            1
# 3:  NA  2  NA  0   14  0   9 NA NA   NA           NA
# 4:  NA 20  NA  0 1080  1 257 NA NA   NA            1
# 5:  NA  0  NA  0   57  0  21 NA NA   NA           NA
# 6:  NA  0  NA  0    3  1   0 NA NA   NA           NA

(1-2) 팀내 순위 조건하에 다른 데이터셋에 앴는 팀 성적 (team_performance) Left join

(조건) Pitching 데이터셋에서 팀/연도별로 순위가 1등인 투수에 한해 (rank_in_team == 1)

(연산) Teams 데이터셋의 순위(Rank)를 가져다가 Pitching 데이터셋에 team_performance 이름의 변수로 만들되,

(병합 기준) Pitching 과 Teams 데이터셋의 'teamID'와 'yearID'를 기준으로 매칭하시오.

Pitching[rank_in_team == 1, # condition for rows
         team_performance := Teams[.SD, Rank, 
                                   on = c('teamID', 'yearID')] # left join by keys
         ]


head(Pitching)
#    playerID yearID stint teamID lgID  W  L  G GS CG SHO SV IPouts   H  ER HR BB SO BAOpp   ERA
# 1: bechtge01   1871     1    PH1   NA  1  2  3  3  2   0  0     78  43  23  0 11  1    NA  7.96
# 2: brainas01   1871     1    WS3   NA 12 15 30 30 30   0  0    792 361 132  4 37 13    NA  4.50
# 3: fergubo01   1871     1    NY2   NA  0  0  1  0  0   0  0      3   8   3  0  0  0    NA 27.00
# 4: fishech01   1871     1    RC1   NA  4 16 24 24 22   1  0    639 295 103  3 31 15    NA  4.35
# 5: fleetfr01   1871     1    NY2   NA  0  1  1  1  1   0  0     27  20  10  0  3  0    NA 10.00
# 6: flowedi01   1871     1    TRO   NA  0  0  1  0  0   0  0      3   1   0  0  0  0    NA  0.00
#    IBB WP HBP BK  BFP GF   R SH SF GIDP rank_in_team team_performance
# 1:  NA  7  NA  0  146  0  42 NA NA   NA           NA               NA
# 2:  NA  7  NA  0 1291  0 292 NA NA   NA            1                4
# 3:  NA  2  NA  0   14  0   9 NA NA   NA           NA               NA
# 4:  NA 20  NA  0 1080  1 257 NA NA   NA            1                9
# 5:  NA  0  NA  0   57  0  21 NA NA   NA           NA               NA
# 6:  NA  0  NA  0    3  1   0 NA NA   NA           NA               NA

(2) dplyr을 이용한 조건이 있는 경우의 Left Join 비교

(2-1) 참여 경기 수 조건하에 팀별 연도별 평균자책점 순위 (rank_in_team)

이제 위의 (1-1)에서 data.table로 수행했던 것과 동일한 과업을 dplyr 패키지로 수행해보겠습니다.

(조건) G > 5 : 6개 이상의 경기(G, game)에 참여하여 공을 던진 투수에 한하여,

(연산) ERA (평균 자책점, Earned Run Average)의 순위를 구해서 rank_in_team 변수를 만들되,

(그룹 기준) 'teamID' & 'yearID' 그룹 별로 ERA 순위(frank(ERA))를 구하여라.

1단계에서 G > 5 라는 조건으로 filter() 를 하여 그룹별로 rank_in_team 을 구해서 별도의 'rank_in_team_df' data.frame을 만든 후에, --> 2단계에서 이를 원본 'Pitching_df'에 left_join() 을 해서 left join 병합을 해주었습니다. G > 5 라는 조건(condition)이 들어감으로써 2단계로 나누어서 진행이 되다보니 코드가 길어졌습니다.

## -- doing the same operation using 'dplyr'
library(dplyr)

## -- rank in team by teamID & yearID group using dense_rank() window function
rank_in_team_df <- Pitching_df %>% 
  filter(G > 5) %>% 
  group_by(teamID, yearID) %>% 
  mutate(rank_in_team = dense_rank(ERA)) %>% 
  select(playerID, lgID, teamID, yearID, stint, rank_in_team)


## left outer join {dplyr}
Pitching_df <- left_join(Pitching_df, 
                         rank_in_team_df, 
                         by = c('playerID', 'lgID', 'teamID', 'yearID', 'stint'))

# ## left outer join {base}
# Pitching_df <- merge(x = Pitching_df, 
#                      y = rank_in_team_df, 
#                      by = c('playerID', 'lgID', 'teamID', 'yearID', 'stint'), 
#                      all.x = TRUE, all.y = FALSE)


head(Pitching_df)
# playerID yearID stint teamID lgID  W  L  G GS CG SHO SV IPouts   H  ER HR BB SO BAOpp   ERA IBB
# 1 bechtge01   1871     1    PH1   NA  1  2  3  3  2   0  0     78  43  23  0 11  1    NA  7.96  NA
# 2 brainas01   1871     1    WS3   NA 12 15 30 30 30   0  0    792 361 132  4 37 13    NA  4.50  NA
# 3 fergubo01   1871     1    NY2   NA  0  0  1  0  0   0  0      3   8   3  0  0  0    NA 27.00  NA
# 4 fishech01   1871     1    RC1   NA  4 16 24 24 22   1  0    639 295 103  3 31 15    NA  4.35  NA
# 5 fleetfr01   1871     1    NY2   NA  0  1  1  1  1   0  0     27  20  10  0  3  0    NA 10.00  NA
# 6 flowedi01   1871     1    TRO   NA  0  0  1  0  0   0  0      3   1   0  0  0  0    NA  0.00  NA
# WP HBP BK  BFP GF   R SH SF GIDP rank_in_team
# 1  7  NA  0  146  0  42 NA NA   NA           NA
# 2  7  NA  0 1291  0 292 NA NA   NA            1
# 3  2  NA  0   14  0   9 NA NA   NA           NA
# 4 20  NA  0 1080  1 257 NA NA   NA            1
# 5  0  NA  0   57  0  21 NA NA   NA           NA
# 6  0  NA  0    3  1   0 NA NA   NA           NA

(2-2) 팀내 순위 조건하에 다른 데이터셋에 앴는 팀 성적 (team_performance) Left join

이제 위의 (1-2)에서 data.table로 했던 것과 똑같은 과업을 dplyr로 해보겠습니다.

(조건) Pitching 데이터셋에서 팀/연도별로 순위가 1등인 투수에 한해 (rank_in_team == 1) (연산) Teams 데이터셋의 순위(Rank)를 가져다가 Pitching 데이터셋에 team_performance 이름의 변수로 만들되,
(병합 기준) Pitching 과 Teams 데이터셋의 'teamID'와 'yearID'를 기준으로 매칭하시오.

아래처럼 dplyr로 하게 되면 '팀/연도별로 순위가 1등인 투수에 한해(rank_in_team==1)' 라는 조건을 충족시키기 위해서 조건절을 포함한 ifelse() 절을 한번 더 수행해줘야 합니다.

## -- merging team performance
Pitching_df <- left_join(Pitching_df, 
                         Teams[, c('teamID', 'yearID', 'Rank')], 
                         by = c('teamID', 'yearID'))

## condition: rank_in_team == 1
Pitching_df$team_performance <- ifelse(Pitching_df$rank_in_team == 1, 
                                       Pitching_df$Rank, # if TRUE
                                       NA) # if FALSE

head(Pitching_df)
# bechtge01   1871     1    PH1   NA  1  2  3  3  2   0  0     78  43  23  0 11  1    NA  7.96  NA
# 2 brainas01   1871     1    WS3   NA 12 15 30 30 30   0  0    792 361 132  4 37 13    NA  4.50  NA
# 3 fergubo01   1871     1    NY2   NA  0  0  1  0  0   0  0      3   8   3  0  0  0    NA 27.00  NA
# 4 fishech01   1871     1    RC1   NA  4 16 24 24 22   1  0    639 295 103  3 31 15    NA  4.35  NA
# 5 fleetfr01   1871     1    NY2   NA  0  1  1  1  1   0  0     27  20  10  0  3  0    NA 10.00  NA
# 6 flowedi01   1871     1    TRO   NA  0  0  1  0  0   0  0      3   1   0  0  0  0    NA  0.00  NA
# WP HBP BK  BFP GF   R SH SF GIDP rank_in_team Rank team_performance
# 1  7  NA  0  146  0  42 NA NA   NA           NA    1               NA
# 2  7  NA  0 1291  0 292 NA NA   NA            1    4                4
# 3  2  NA  0   14  0   9 NA NA   NA           NA    5               NA
# 4 20  NA  0 1080  1 257 NA NA   NA            1    9                9
# 5  0  NA  0   57  0  21 NA NA   NA           NA    5               NA
# 6  0  NA  0    3  1   0 NA NA   NA           NA    6               NA

위에서 소개했던 똑같은 과업을 수행하는 data.table과 dplyr의 조건있는 데이터셋 병합(conditional left join) 예제 코드를 나란히 제시해서 비교해보면 아래와 같습니다. data.table이 dplyr (혹은 base R) 대비 조건있는 데이터셋 병합의 경우 비교할 수 없을 정도로 훨~씬 코드가 간결합니다!

구분

data.table

dplyr

(1)

conditional

left join

by key

w/ a list

library(data.table)

Pitching[G > 5, # condition

rank_in_team := frank(ERA),

by = .(teamID, yearID)]

library(dplyr)

rank_in_team_df <- Pitching_df %>%

filter(G > 5) %>%

group_by(teamID, yearID) %>%

mutate(rank_in_team = dense_rank(ERA)) %>%

select(playerID, lgID, teamID, yearID, stint, rank_in_team)

## left outer join {dplyr}

Pitching_df <- left_join(

Pitching_df,

rank_in_team_df,

by = c('playerID', 'lgID', 'teamID', 'yearID', 'stint'))

(2)

conditional

left join

by key

w/ a data.table

Pitching[rank_in_team == 1,          team_performance := Teams[           .SD, Rank,
         on = c('teamID', 'yearID')]
         ]

## -- merging team performance

Pitching_df <- left_join(

Pitching_df,

Teams[, c('teamID', 'yearID', 'Rank')],

by = c('teamID', 'yearID'))

## condition: rank_in_team == 1

Pitching_df$team_performance <- ifelse(

Pitching_df$rank_in_team == 1,

Pitching_df$Rank, # if TRUE

NA) # if FALSE

[ Reference ]

* R data.table vignettes 'Using .SD for Data Analysis'
: cran.r-project.org/web/packages/data.table/vignettes/datatable-sd-usage.html

이번 포스팅이 많은 도움이 되었기를 바랍니다.

행복한 데이터 과학자 되세요! :-)

728x90

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

'R 분석과 프로그래밍 > R 데이터 전처리' 카테고리의 다른 글

[R data.table] 그룹별 최소값 행, 최대값 행 가져오기 (Group Optima) (0)	2021.01.31
[R data.table] .SD[], by를 사용해 그룹별로 부분집합 가져오기 (Group Subsetting) (0)	2021.01.31
[R data.table] 선형회귀 모델의 오른쪽 부분(model's right-hand side)의 변수 조합을 간단하게 다루기 (4)	2021.01.31
[R data.table] .SDcols 로 일부 칼럼 가져오기 (Column subsetting using .SDcols) (6)	2021.01.31
[R data.table] 그룹별 관측치 개수 별로 DataTable을 구분해서 생성하기 (0)	2021.01.30

Posted by Rfriend

[SQL]웹으로 SQL 연습할 수 있는 웹사이트: w3schools.com/sql : 유통업체 ERD 및 Query 예시

Hive 2017. 6. 26. 23:13

지난번 포스팅에서는 웹으로 간단하게 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

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

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

728x90

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

'Hive' 카테고리의 다른 글

[R] R로 Oracle DB 접근 방법, R로 Presto 접근 방법 (R Oracle DB connect, R Presto DB connect) (2)	2017.08.29
[SQL] 웹으로 SQL 연습할 수 있는 online site : w3schools.com/sql (2)	2017.06.23
[Hive] R로 Hive에 접속해서 데이터 추출하기, R DB connect (R, Hive dbConnect) : rJava, RJDBC, dbConnect(), dbGetQuery(), R로 PostgreSQL 사용하는 방법, R로 MySQL 접속해서 사용하는 방법 (4)	2016.09.03
[Hive] 뷰(View), 갭슐화(encapsulation) (0)	2016.06.26
[Hive] 조인 문 : INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL JOIN, CARTESIAN PRODUCT JOIN, MAP-SIDE JOIN, UNION ALL (0)	2016.06.26

Posted by Rfriend

[Python pandas] Database처럼 DataFrame Join/Merge 하기 : pd.merge()

Python 분석과 프로그래밍/Python 데이터 전처리 2016. 12. 3. 23:31

데이터 분석을 하다 보면 여기 저기 흩어져 있는 데이터를 특정한 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    B 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 에 대해서는 다음번 포스팅에서 소개하도록 하겠습니다.

728x90

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

'Python 분석과 프로그래밍 > Python 데이터 전처리' 카테고리의 다른 글

[Python pandas] DataFrame 결측값 여부 확인, 결측값 개수 : isnull(), notnull(), df.isnull().sum(), df.notnull().sum(), df.isnull().sum(1), df.notnull().sum(1) (0)	2016.12.07
[Python pandas] DataFrame을 index 기준으로 합치기 (merge, join on index) (3)	2016.12.06
[Python pandas] DataFrame과 Series 합치기 : pd.concat(), append() (3)	2016.11.30
[Python pandas] 여러개의 동일한 형태 DataFrame 합치기 : pd.concat() (2)	2016.11.28
[Python pandas] DataFrame의 index 재설정(reindex) 와 결측값 채우기(fill in missing values) (4)	2016.11.27

Posted by Rfriend

이전 1 다음

R, Python 분석과 프로그래밍의 친구 (by R Friend)

'Left Join'에 해당되는 글 5건

[PostgreSQL, Greenplum] JOIN 으로 여러개의 테이블, 뷰 연결하기

(1) INNER JOIN

(2) LEFT OUTER JOIN

(3) RIGHT OUTER JOIN

(4) FULL JOIN

(5) 3개 이상의 복수개의 테이블을 JOIN 으로 연결하기

(6) CROSS JOIN

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[R 지리공간 데이터 분석] 두개의 지리 벡터 데이터 테이블을 dplyr 로 Join 하기 (Joining two geography vector data tables using R dplyr)

(1) Mutating Joins : 두 테이블을 합쳐서 새로운 테이블을 생성하기

(2) Filtering Joins : 두 테이블의 매칭되는 부분을 기준으로 한쪽 테이블을 걸러내기

(3) Nesting joins : 한 테이블의 모든 칼럼을 리스트로 중첩되게 묶어서 다른 테이블에 합치기

[ R dplyr 패키지로 두 개의 테이블 Join 하기 (Joining two tables together using R dplyr) ]

(1) Mutating Joins

(1-1) inner join

(1-2) left join

(1-3) right join

(1-4) full join

(2) Filtering Joins

(2-1) semi join

(2-2) anti join

(3) Nesting Joins

(3-1) nest join

[Reference]

'R 분석과 프로그래밍 > R 지리공간데이터 분석' 카테고리의 다른 글

[R data.table] 조건이 있는 상태에서 Key를 기준으로 데이터셋 합치기 (Conditional Joins)

지난 포스팅에서는 R data.table 패키지에서 선형회귀 모델의 오른쪽 부분의 변수 조합을 .SD, .SDcols, lapply(), sapply()를 사용해서 간단하게 단 몇줄의 코드로 처리하는 방법(rfriend.tistory.com/609)을 소개하였습니다.

(1) data.table을 이용한 조건이 있는 경우의 Left Join

(2) dplyr을 이용한 조건이 있는 경우의 Left Join 비교

(1) data.table을 이용한 조건이 있는 경우의 Left Join

(1-1) 참여 경기 수 조건 하에 팀별 연도별 평균자책점 순위 (rank_in_team)

(조건) G > 5 : 6개 이상의 경기(G, game)에 참여하여 공을 던진 투수에 한하여,

(연산) ERA (평균 자책점, Earned Run Average)의 순위를 구해서 rank_in_team 변수를 만들되,

(그룹 기준) 'teamID' & 'yearID' 그룹 별로 ERA 순위(frank(ERA))를 구하여라.

조건, 연산, 그룹 기준이 모두 Pitching[조건, 연산, 그룹 기준] 의 구문으로 해서 단 한줄로 모두 처리가 가능합니다. 간결함의 끝판왕이라고나 할까요!

(1-2) 팀내 순위 조건하에 다른 데이터셋에 앴는 팀 성적 (team_performance) Left join

(조건) Pitching 데이터셋에서 팀/연도별로 순위가 1등인 투수에 한해 (rank_in_team == 1)

(연산) Teams 데이터셋의 순위(Rank)를 가져다가 Pitching 데이터셋에 team_performance 이름의 변수로 만들되,

(병합 기준) Pitching 과 Teams 데이터셋의 'teamID'와 'yearID'를 기준으로 매칭하시오.

(2) dplyr을 이용한 조건이 있는 경우의 Left Join 비교

(2-1) 참여 경기 수 조건하에 팀별 연도별 평균자책점 순위 (rank_in_team)

이제 위의 (1-1)에서 data.table로 수행했던 것과 동일한 과업을 dplyr 패키지로 수행해보겠습니다.

(조건) G > 5 : 6개 이상의 경기(G, game)에 참여하여 공을 던진 투수에 한하여,

(연산) ERA (평균 자책점, Earned Run Average)의 순위를 구해서 rank_in_team 변수를 만들되,

(그룹 기준) 'teamID' & 'yearID' 그룹 별로 ERA 순위(frank(ERA))를 구하여라.

(2-2) 팀내 순위 조건하에 다른 데이터셋에 앴는 팀 성적 (team_performance) Left join

이제 위의 (1-2)에서 data.table로 했던 것과 똑같은 과업을 dplyr로 해보겠습니다.

아래처럼 dplyr로 하게 되면 '팀/연도별로 순위가 1등인 투수에 한해(rank_in_team==1)' 라는 조건을 충족시키기 위해서 조건절을 포함한 ifelse() 절을 한번 더 수행해줘야 합니다.

[ Reference ]

* R data.table vignettes 'Using .SD for Data Analysis' : cran.r-project.org/web/packages/data.table/vignettes/datatable-sd-usage.html

이번 포스팅이 많은 도움이 되었기를 바랍니다.

행복한 데이터 과학자 되세요! :-)

'R 분석과 프로그래밍 > R 데이터 전처리' 카테고리의 다른 글

[SQL]웹으로 SQL 연습할 수 있는 웹사이트: w3schools.com/sql : 유통업체 ERD 및 Query 예시

'Hive' 카테고리의 다른 글

[Python pandas] Database처럼 DataFrame Join/Merge 하기 : pd.merge()

'Python 분석과 프로그래밍 > Python 데이터 전처리' 카테고리의 다른 글

카테고리

태그목록

티스토리툴바

* R data.table vignettes 'Using .SD for Data Analysis'
: cran.r-project.org/web/packages/data.table/vignettes/datatable-sd-usage.html