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

데이터셋에 이상치가 있으면 모델을 훈련시킬 때 적합된 모수에 큰 영향을 줍니다. 따라서 탐색적 데이터 분석을 할 때 이상치(outlier)를 찾고 제거하는 작업이 필요합니다. 

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 SQL의 PERCENTILE_DISC() WITHIN GROUP (ORDER BY) 함수를 사용해서, 사분위수와 IQR 에 기반하여 이상치를 찾고 제거하는 방법(Identifying and removing Outliers by the upper and lower limit based on Quartiles and IQR(Interquartile Range))을 소개하겠습니다. 

요약통계량의 평균과 표준편차는 이상치에 매우 민감합니다. 따라서 정규분포가 아니라 이상치가 존재하여 한쪽으로 치우친 분포에서는 (average +-3 * standard deviation) 범위 밖의 값을 이상치로 간주하는 방법은 적합하지 않을 수 있습니다. 반면, 이상치에 덜 민감한 사분위수와 IQR 를 이용하여 이상치를 찾고 제거하는 방법은 간단하게 구현하여 사용할 수 있습니다. 

먼저, 예제로 사용할 데이터셋 테이블을 만들어보겠습니다. 부산과 서울의 지역(region) 그룹별로 seller_id 별 판매금액(amt) 을 칼럼으로 가지며, 판매금액에 이상치(outliler)를 포함시켰습니다. 

---------------------------------------------------------------------------------
-- Removing Outliers based on Quartiles and IQR using SQL 
---------------------------------------------------------------------------------

-- creating a sample dataset with outliers
DROP TABLE IF EXISTS reg_sales;
CREATE TABLE reg_sales (
	region text NOT NULL
	, seller_id int NOT NULL
	, amt int
);

INSERT INTO reg_sales VALUES 
('Busan', 1, 10) -- outlier
, ('Busan', 2, 310)
, ('Busan', 3, 350)
, ('Busan', 4, 380)
, ('Busan', 5, 390)
, ('Busan', 6, 430)
, ('Busan', 7, 450)
, ('Busan', 8, 450)
, ('Busan', 9, 3200) -- outlier
, ('Busan', 10, 4600) -- outlier
, ('Seoul', 1, 20) -- outlier
, ('Seoul', 2, 300)
, ('Seoul', 3, 350)
, ('Seoul', 4, 370)
, ('Seoul', 5, 380)
, ('Seoul', 6, 400)
, ('Seoul', 7, 410)
, ('Seoul', 8, 440)
, ('Seoul', 9, 460)
, ('Seoul', 10, 2500) -- outlier
;

SELECT * FROM reg_sales ORDER BY region, amt;

--region|seller_id|amt |
--------+---------+----+
--Busan |        1|  10|
--Busan |        2| 310|
--Busan |        3| 350|
--Busan |        4| 380|
--Busan |        5| 390|
--Busan |        6| 430|
--Busan |        8| 450|
--Busan |        7| 450|
--Busan |        9|3200|
--Busan |       10|4600|
--Seoul |        1|  20|
--Seoul |        2| 300|
--Seoul |        3| 350|
--Seoul |        4| 370|
--Seoul |        5| 380|
--Seoul |        6| 400|
--Seoul |        7| 410|
--Seoul |        8| 440|
--Seoul |        9| 460|
--Seoul |       10|2500|

PostgreSQL, Greenplum 에서 PERCENTILE_DISC() 함수를 사용하여 사분위수(quartiles)와 IQR(Interquartile Range)를 구할 수 있습니다.  아래 예에서는 지역(region) 별로 1사분위수(Q1), 중앙값(median), 3사분위수(Q3), IQR (Interquartile Range) 를 구해보았습니다. 

IQR (Interquartile Range) = Q3 - Q1

-- Quartiles by region groups
-- Interquartile Range (IQR) = Q3-Q1 
-- : relatively robust statistic compared to range and std dev for the measure of spread.
SELECT 
	region
	, PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY amt) AS q1
	, PERCENTILE_DISC(0.50) WITHIN GROUP (ORDER BY amt) AS median
	, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY amt) AS q3
	, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY amt)  - 
		PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY amt) AS iqr
FROM reg_sales
GROUP BY region 
ORDER BY region;

--region|q1 |median|q3 |iqr|
--------+---+------+---+---+
--Busan |350|   390|450|100|
--Seoul |350|   380|440| 90|

사분위수와 IQR 를 이용하여 이상치를 찾는 방식(identifying outliers by upper and lower limit based on quartiles and IQR using SQL in PostgreSQL)은 아래와 같습니다. (포스팅 상단의 도식 참조) 

* Upper Limit = Q1 - 1.5 * IQR

* Lower Limit = Q3 + 1.5 * IQR

if value > Upper Limit then 'Outlier'

or if value < Lower Limit then 'Outlier'

-- Identifying outliers by the upper and lower limit based on Quartiles and IQR as:
-- : Lower Limit = Q1 – 1.5 * IQR
-- : Upper Limit = Q3 + 1.5 * IQR

WITH stats AS (
	SELECT 
	region
	, PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY amt) AS q1
	, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY amt) AS q3
	, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY amt)  - 
		PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY amt) AS iqr
	FROM reg_sales
	GROUP BY region
)
SELECT 
	r.*
FROM reg_sales AS r
LEFT JOIN stats AS s ON r.region = s.region 
WHERE r.amt < (s.q1 - 1.5 * s.iqr) OR r.amt > (s.q3 + 1.5 * s.iqr) -- identifying outliers
ORDER BY region, amt;

--region|seller_id|amt |
--------+---------+----+
--Busan |        1|  10|
--Busan |        9|3200|
--Busan |       10|4600|
--Seoul |        1|  20|
--Seoul |       10|2500|

아래의 예에서는 사분위수와 IQR에 기반하여 이상치를 제거 (Removing outliers by upper and lower limit based on quartiles and IQR using SQL in PostgreSQL) 하여 보겠습니다. 

-- Removing outliers by the upper and lower limit based on Quartiles and IQR as:
-- : Lower Limit = Q1 – 1.5 * IQR
-- : Upper Limit = Q3 + 1.5 * IQR

WITH stats AS (
	SELECT 
	region
	, PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY amt) AS q1
	, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY amt) AS q3
	, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY amt)  - 
		PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY amt) AS iqr
	FROM reg_sales
	GROUP BY region
)
SELECT 
	r.*
FROM reg_sales AS r
LEFT JOIN stats AS s ON r.region = s.region 
WHERE r.amt > (s.q1 - 1.5 * s.iqr) AND r.amt < (s.q3 + 1.5 * s.iqr) -- removing outliers
ORDER BY region, amt;

--region|seller_id|amt|
--------+---------+---+
--Busan |        2|310|
--Busan |        3|350|
--Busan |        4|380|
--Busan |        5|390|
--Busan |        6|430|
--Busan |        7|450|
--Busan |        8|450|
--Seoul |        2|300|
--Seoul |        3|350|
--Seoul |        4|370|
--Seoul |        5|380|
--Seoul |        6|400|
--Seoul |        7|410|
--Seoul |        8|440|
--Seoul |        9|460|

[ Reference ]

* PostgreSQL PERCENTILE_DISC() function 

  : https://www.postgresql.org/docs/9.4/functions-aggregate.html

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

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

그동안 Python NumPy의 단일 배열 unary ufuncs 에 대해서 알아보았습니다.

(http://rfriend.tistory.com/300, http://rfriend.tistory.com/301)

이번 포스팅에서는 그동안 배웠던 unary ufuncs 들을 요리조리 조합하고 응용하는 한가지 예를 들어보겠습니다.

- 절대값을 구하는 함수 np.abs(x)

- 배열 원소의 부호를 판별하는 np.sign(x) 함수

- 배열의 1개 이상의 원소가 참(True) 인지 평가하는 np.any(x) 함수

를 이용해서 특이값, 이상치(outlier)를 탐색하고, indexing해서 다른 값으로 대체하는 방법을 소개하겠습니다.

먼저 numpy, pandas, matplotlib.pyplot 모듈을 불러오고, 평균이 0, 표준편차가 1인 정규분포로 부터 난수 10,000만개를 생성해보겠습니다.

다음으로 dexcribe() 메서드를 사용해서 x의 기술통계량을 알아보고, 히스토그램으로 분포를 살펴보겠습니다. min과 max 값을 보면 평균 0을 중심으로 해서 -3 (-3 sigma)과 +3 (+3 sigma)을 벗어나는 관측치가 있음을 알 수 있습니다.

정규분포를 띠는 데이터셋의 경우 평균으로 부터 +3 sigma, -3 sigma 를 벗어나는 데이터의 경우 전체 데이터셋 중 99%가 존재하는 구간을 벗어나는 특이값, 이상값(outlier)로 간주할 수 있습니다.

이번 예제에서 x는 평균이 '0'이고 표준편차가 '1'인 정규분포를 따르므로, np.abs(x)와 any(1) 함수를 조합해서 사용하면 1만개의 관측치를 가지는 x로부터 +- 3 sigma를 벗어나는 특이값만 쏙 빼올 수 있습니다.  count() 메소드로 특이값 개수도 세볼 수 있구요.

이번에는 np.sign(x) 함수를 곁들여 사용하여 +- 3 sigma를 벗어나는 관측치값을 모두 +-3 으로 대체해보겠습니다. (즉, +3 sigma보다 큰 특이값은 +3으로 대체, -3 sigma 보다 작은 특이값은 -3으로 대체) 

x.describe()로 요약통계량을 살펴보니 min -3, max 3으로 바뀌었지요? 히스토그램도 -3 ~ +3 까지 분포로 바뀌었구요.

통계 분포 방법 외에 특이값 찾는 방법이 서너가지 더 있는데요, 그건 다음번에 기회 될 때 별도로 포스팅을 하겠습니다. (언제가 될지는 기약 못하겠네요. ^^;)

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