이번 포스팅에서는 PostgreSQL, Greenplum database에서 SQL, MADlib 함수, PL/R, PL/Python을 사용해서 연속형 데이터에 대한 요약통계량을 구하는 방법을 소개하겠습니다.  무척 쉬운 내용이므로 쉬어가는 코너 정도로 가볍게 생각해주시면 좋겠습니다. ^^


PostgreSQL, Greenplum database 에서 연속형 데이터에 대해 그룹별로, 

(1) SQL 로 요약통계량 구하기

(2) Apache MADlib 으로 요약통계량 구하기





참고로, 이번 포스팅에서는 PostgreSQL 9.4, Greenplum 6.10.1 버전을 사용하였으며, PostgreSQL 9.4 버전보다 낮은 버전을 사용하면 최빈값(mode), 사분위부(percentile) 구하는 함수를 사용할 수 없습니다. 


먼저, 예제로 사용하기 위해 '나이'의 연속형 데이터와 '성별'의 범주형 데이터 (그룹)를 가진 간단한 테이블을 만들어보겠습니다. 결측값(missing value)도 성별 그룹별로 몇 개 넣어봤습니다. 



DROP TABLE IF EXISTS cust;

CREATE TABLE cust (id INT, age INT, gender TEXT);

INSERT INTO cust VALUES

(1,NULL,'M'),

(2,NULL,'M'),

(3,25,'M'),

(4,28,'M'),

(5,27,'M'),

(6,25,'M'),

(7,26,'M'),

(8,29,'M'),

(9,25,'M'),

(10,27,'M'),

(11,NULL,'F'),

(12,23,'F'),

(13,25,'F'),

(14,23,'F'),

(15,24,'F'),

(16,26,'F'),

(17,23,'F'),

(18,24,'F'),

(19,22,'F'),

(20,23,'F');

 




 (1) SQL로 연속형 데이터의 그룹별 요약통계량 구하기


함수가 굳이 설명을 안해도 될 정도로 간단하므로 길게 설명하지는 않겠습니다. 


표준편차 STDDEV() 와 분산 VARIANCE() 함수는 표본표준편차(sample standard deviation), 표본분산(sample variance) 를 계산해줍니다. 만약 모표준편차(population standard deviation), 모분산(population variance)를 구하고 싶으면 STDDEV_POP(), VAR_POP() 함수를 사용하면 됩니다. 


PostgreSQL 9.4 버전 이상부터 최빈값(MODE), 백분위수(Percentile) 함수가 생겨서 정렬한 후에 집계하는 기능이 매우 편리해졌습니다. (MODE(), PERCENTILE_DISC() 함수를 사용하지 않고 pure SQL로 최빈값과 백분위수를 구하려면 query 가 꽤 길어집니다.)



SELECT

    gender AS group_by_value

    , 'age' AS target_column

    , COUNT(*) AS row_count

    , COUNT(DISTINCT age) AS distinct_values

    , AVG(age)

    , VARIANCE(age)

    , STDDEV(age)

    , MIN(age)

    , PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY age) AS first_quartile

    , MEDIAN(age)

    , PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY age) AS third_quartile

    , MAX(age)

    , MODE() WITHIN GROUP (ORDER BY age) -- over PostgreSQL 9.4

FROM cust

WHERE age IS NOT NULL

GROUP BY gender

ORDER BY gender;





성별 그룹별로 연령(age) 칼럼의 결측값 개수를 구해보겠습니다. 

결측값 개수는 WHERE age IS NULL 로 조건절을 주고 COUNT(*)로 행의 개수를 세어주면 됩니다. 



SELECT 

    gender

    , COUNT(*) AS missing_count

FROM cust

WHERE age IS NULL

GROUP BY gender

ORDER BY gender;


Out[5]:
gendermissing_count
F1
M2





위의 집계/ 요약통계량과 결측값 개수를 하나의 조회 결과로 보려면 아래처럼 Join 을 해주면 됩니다.



WITH summary_tbl AS (
    SELECT
        gender AS group_by_value
        , 'age' AS target_column
        , COUNT(*) AS row_count
        , COUNT(DISTINCT age) AS distinct_values
        , AVG(age)
        , VARIANCE(age)
        , STDDEV(age)
        , MIN(age)
        , PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY age) AS first_quartile
        , MEDIAN(age)
        , PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY age) AS third_quartile
        , MAX(age)
        , MODE() WITHIN GROUP (ORDER BY age)
    FROM cust
    WHERE age IS NOT NULL
    GROUP BY gender
    ORDER BY gender
), missing_tbl AS (
    SELECT
        gender AS group_by_value
        , COUNT(*) AS missing_count
    FROM cust
    WHERE age IS NULL
    GROUP BY gender
)
SELECT a.*, b.missing_count
FROM summary_tbl a LEFT JOIN missing_tbl b USING(group_by_value)
;

 




  (2) Apache MADlib으로 연속형 데이터의 그룹별 요약통계량 구하기


Apache MADlib의 madlib.summary() 함수를 사용하면 단 몇 줄의 코드만으로 위의 (1)번에서 SQL 집계 함수를 사용해서 길게 짠 코드를 대신해서 매우 깔끔하고 간단하게 구할 수 있습니다. 


아래는 (1)번의 결과를 얻기위해 성별(gender) 연령(age) 칼럼의 집계/요약데이터를 구하는 madlib.summary() 함수 예시입니다. 


Target columns 위치에는 1 개 이상의 분석을 원하는 연속형 데이터 칼럼을 추가로 넣어주기만 하면 되므로 (1) 번의 pure SQL 대비 훨씬 편리한 측면이 있습니다! 


그리고 그룹별로 구분해서 집계/요약하고 싶으면 Grouping columns 위치에 기준 칼럼 이름을 넣어주기만 하면 되므로 역시 (1)번의 pure SQL 대비 훨씬 편리합니다!



DROP TABLE IF EXISTS cust_summary;

SELECT madlib.summary('cust'     -- Source table

                      ,'cust_summary'   -- Output table

                      , 'age'                -- Target columns

                      , 'gender'            -- Grouping columns

);






madlib.summary() 함수의 결과 테이블에서 조회할 수 있는 집계/요약통계량 칼럼 리스트는 아래와 같습니다. 



SELECT column_name

FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_SCHEMA = 'public'

        AND TABLE_NAME    = 'cust_summary'

    ORDER BY ORDINAL_POSITION;

Out[7]:
column_name
group_by
group_by_value
target_column
column_number
data_type
row_count
distinct_values
missing_values
blank_values
fraction_missing
fraction_blank
positive_values
negative_values
zero_values
mean
variance
confidence_interval
min
max
first_quartile
median
third_quartile
most_frequent_values
mfv_frequencies

 



[Reference]

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

* Apache MADlib summary function: https://madlib.apache.org/docs/v1.11/group__grp__summary.html



다음번 포스팅에서는 PostgreSQL, Greenplum에서 SQL과 Apache MADlib을 이용하여 상관계수, 상관계수 행렬을 구하는 방법(https://rfriend.tistory.com/581)을 소개하겠습니다.


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

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



Posted by R Friend Rfriend

댓글을 달아 주세요

데이터 분석을 하다 보면 변수들 간의 척도 (scale) 가 서로 다른 경우 직접적으로 상호 비교를 할 수가 없습니다.  모델링에서는 척도(scale)가 다름으로 인해서 모수의 왜곡이 생길 수도 있습니다.

 

따라서 모델링 작업에 들어가기 전에 변수들 간의 척도가 다른 경우에는 보통 표준화(scale standization)를 진행합니다.

 

표준화 중에서도 모집단이 '정규분포 (normal distribution, Gaussian distribution)을 따르는 경우 평균이 0, 표준편차는 1 인 표준정규분포(standard normal distribution)로 표준화 하는 방법을 많이 사용합니다. 

 

이번 포스팅에서는

 

 - Numpy : z = (x - mean())/std()

 - scipy.stats : zscore()

 - sklearn.preprocessing : StandardScaler().fit_transform()

 

의 모듈, method를 이용한 표준정규분포 표준화 (mean removal and variance scaling, mean = 0, std = 1)에 대해서 소개하겠습니다.

 

 

 

 

실습에 필요한 모듈을 importing하고 예제 Dataset을 만들어보겠습니다.

 

 

In [1]: import numpy as np


In [2]: data = np.random.randint(30, size=(6, 5))


In [3]: data

Out[3]:

array([[ 3,  5, 14, 24, 24],
       [ 3,  9,  1, 20,  3],
       [10,  5, 11, 17, 28],
       [26,  9, 20, 10,  8],
       [15,  7,  1, 24,  2],
       [15, 19, 10, 13,  2]])

 

 

 

표준정규분포로 표준화하는 3가지 방법을 차례대로 소개하겠습니다.

 

 

  (1) Numpy 를 이용한 표준화 : z = (x - mean())/std()

 

칼럼마다 각각의 평균, 표준편차를 적용해서 표준화를 하려면 mean(data, axis=0), std(data, axis=0) 처럼 'axis=0' 을 설정해주면 됩니다.

 

 

# (1) Using numpy, z = (x-mean)/std

In [4]: from numpy import *


In [5]: data_standadized_np = (data - mean(data, axis=0)) / std(data, axis=0)


In [6]: data_standadized_np

Out[6]:

array([[-1.13090555, -0.84016805,  0.66169316,  1.14070365,  1.19426502],
       [-1.13090555,  0.        , -1.24986486,  0.38023455, -0.75998683],
       [-0.25131234, -0.84016805,  0.22056439, -0.19011728,  1.56650347],
       [ 1.75918641,  0.        ,  1.54395071, -1.5209382 , -0.29468877],
       [ 0.37696852, -0.42008403, -1.24986486,  1.14070365, -0.85304644],
       [ 0.37696852,  2.10042013,  0.07352146, -0.95058638, -0.85304644]])

 

# check of 'mean=0', 'standard deviation=1'

In [7]: mean(data_standadized_np, axis=0)

Out[7]:

array([ -5.55111512e-17,   0.00000000e+00,   9.25185854e-18,
         0.00000000e+00,   3.70074342e-17])


In [8]: std(data_standadized_np, axis=0)

Out[8]: array([ 1.,  1.,  1.,  1.,  1.])

 

 

  • 평균(mean): np.mean(arr)
  • 표준편차(standard deviation): np.std(arr)
  • 분산(variance): np.var(arr)


import numpy as np

arr = np.array([0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10])


print('mean:', np.mean(arr))

print('standard deviation:', np.std(arr))

print('variance:', np.var(arr))


mean: 5.0
standard deviation: 3.1622776601683795
variance: 10.0

 


 


 

  (2) scipy.stats 을 이용한 표준화 : ss.zscore()

 

 

# (2) Standardization using zscore() of scipy.stats

In [9]: import scipy.stats as ss


In [10]: data_standadized_ss = ss.zscore(data)


In [11]: data_standadized_ss

Out[11]:

array([[-1.13090555, -0.84016805,  0.66169316,  1.14070365,  1.19426502],
       [-1.13090555,  0.        , -1.24986486,  0.38023455, -0.75998683],
       [-0.25131234, -0.84016805,  0.22056439, -0.19011728,  1.56650347],
       [ 1.75918641,  0.        ,  1.54395071, -1.5209382 , -0.29468877],
       [ 0.37696852, -0.42008403, -1.24986486,  1.14070365, -0.85304644],
       [ 0.37696852,  2.10042013,  0.07352146, -0.95058638, -0.85304644]])

 

 

 

 

  (3) sklearn.preprocessing 을 이용한 표준화 : StandardScaler().fit_transform()

 

 

In [12]: from sklearn.preprocessing import StandardScaler


In [13]: data_standadized_skl = StandardScaler().fit_transform(data)

 

In [14]: data_standadized_skl

Out[14]:

array([[-1.13090555, -0.84016805,  0.66169316,  1.14070365,  1.19426502],
       [-1.13090555,  0.        , -1.24986486,  0.38023455, -0.75998683],
       [-0.25131234, -0.84016805,  0.22056439, -0.19011728,  1.56650347],
       [ 1.75918641,  0.        ,  1.54395071, -1.5209382 , -0.29468877],
       [ 0.37696852, -0.42008403, -1.24986486,  1.14070365, -0.85304644],
       [ 0.37696852,  2.10042013,  0.07352146, -0.95058638, -0.85304644]])

 

 

 

다음번 포스팅에서는 데이터셋에 Outlier 가 들어있을 때 Robust하게 표준화할 수 있는 방법으로서 sklearn.preprocessing.robust_scale, sklearn.preprocessing.RobustScaler 을 소개하겠습니다.

 

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

 

 

 

Posted by R Friend Rfriend

댓글을 달아 주세요