[PostgreSQL, Greenplum] 상관관계 분석 (Correlation Coefficients using SQL, MADlib)

Greenplum and PostgreSQL Database 2020. 11. 29. 23:48
지난번 포스팅에서는 PostgreSQL, Greenplum database에서 SQL, MADlib 을 사용하여 연수형 데이터의 집계/ 요약통계량(aggregation/ summary statistics)을 구하는 방법(https://rfriend.tistory.com/580)을 소개하였습니다.

이번 포스팅에서는 PostgreSQL, Greenplum DB에서 SQL, Apache MADlib 을 사용하여 여러개의 연속형 변수들 간의 상관관계를 구하는 방법을 소개하겠습니다.

(1) SQL 로 두개 연속형 변수간 상관계수 구하기
(2) SQL 로 다수의 연속형 변수간 상관계수 행렬(Correlation Matrix) 구하기
(3) MADlib으로 다수의 연속형 변수간 상관계수 행렬 구하기
(4) MADlib으로 다수의 연속형 변수간 그룹별로 상관계수 행렬(Correlation Matrix by Group) 구하기



예제로 사용하도록 연속형 데이터 4개 칼럼과 범주형 데이터 1개를 가지고 iris 샘플 데이터를 테이블로 만들어보겠습니다.



-- Iris data table
DROP TABLE IF EXISTS iris;
CREATE TABLE iris (id INT, sepal_length FLOAT, sepal_width FLOAT,
                    petal_length FLOAT, petal_width FLOAT,
                   class_name TEXT);
INSERT INTO iris VALUES
(1,5.1,3.5,1.4,0.2,'Iris-setosa'),
(2,4.9,3.0,1.4,0.2,'Iris-setosa'),
(3,4.7,3.2,1.3,0.2,'Iris-setosa'),
(4,4.6,3.1,1.5,0.2,'Iris-setosa'),
(5,5.0,3.6,1.4,0.2,'Iris-setosa'),
(6,5.4,3.9,1.7,0.4,'Iris-setosa'),
(7,4.6,3.4,1.4,0.3,'Iris-setosa'),
(8,5.0,3.4,1.5,0.2,'Iris-setosa'),
(9,4.4,2.9,1.4,0.2,'Iris-setosa'),
(10,4.9,3.1,1.5,0.1,'Iris-setosa'),
(11,7.0,3.2,4.7,1.4,'Iris-versicolor'),
(12,6.4,3.2,4.5,1.5,'Iris-versicolor'),
(13,6.9,3.1,4.9,1.5,'Iris-versicolor'),
(14,5.5,2.3,4.0,1.3,'Iris-versicolor'),
(15,6.5,2.8,4.6,1.5,'Iris-versicolor'),
(16,5.7,2.8,4.5,1.3,'Iris-versicolor'),
(17,6.3,3.3,4.7,1.6,'Iris-versicolor'),
(18,4.9,2.4,3.3,1.0,'Iris-versicolor'),
(19,6.6,2.9,4.6,1.3,'Iris-versicolor'),
(20,5.2,2.7,3.9,1.4,'Iris-versicolor'),
(21,6.3,3.3,6.0,2.5,'Iris-virginica'),
(22,5.8,2.7,5.1,1.9,'Iris-virginica'),
(23,7.1,3.0,5.9,2.1,'Iris-virginica'),
(24,6.3,2.9,5.6,1.8,'Iris-virginica'),
(25,6.5,3.0,5.8,2.2,'Iris-virginica'),
(26,7.6,3.0,6.6,2.1,'Iris-virginica'),
(27,4.9,2.5,4.5,1.7,'Iris-virginica'),
(28,7.3,2.9,6.3,1.8,'Iris-virginica'),
(29,6.7,2.5,5.8,1.8,'Iris-virginica'),
(30,7.2,3.6,6.1,2.5,'Iris-virginica');



SELECT * FROM iris ORDER BY id LIMIT 5;




  (1) SQL 로 두개 연속형 변수간 상관계수 구하기


공분산(Covariance)은 두 변량이 각각의 평균으로부터 변화하는 방향 및 양에 대한 기대값입니다. PostgreSQL, Greenplum DB에서 모집단의 공분산(Population Covariance)는 COVAR_POP(Y, X) 함수를 사용하며, 샘플 공분산(Sample Covariance)은 COVAR_SAMP(Y, X) 함수를 사용합니다.

(* 공분산, 상관계수 참고: https://rfriend.tistory.com/126)


피어슨 상관계수(Pearson's Correlation Coefficient)는 공분산을 표준편차로 나누어서 표준화해준 값으로서, -1~1 사이의 값을 가집니다. 1에 가까울수록 두 변수간에 양의 선형관계가 있고, -1에 가까울수록 음의 선형관계가 있으며, 0이면 선형관계가 없다고 해석합니다. PostgreSQL, Greenplum DB에서는 CORR(Y, X) 함수를 사용합니다.



----- b/w Y and X
-- Population Covariance using SQL: covar_pop(Y, X)
SELECT COVAR_POP(sepal_length, sepal_width) FROM iris;


-- Sample Covariance using SQL: covar_samp(Y, X)
SELECT COVAR_SAMP(sepal_width, sepal_length) FROM iris;


-- Pearson's Correlation Coefficients using SQL: corr(Y, X)
SELECT CORR(sepal_width, sepal_length) FROM iris;




  (2) SQL 로 다수의 연속형 변수간 상관계수 행렬(Correlation Matrix) 구하기

먼저, 이해를 돕기 위해서 2단계로 나누어서 과정을 설명하자면요, 긴 형태(long format)로 두 연속형 변수 간의 상관계수를 구해보겠습니다. (두번째 단계에서 Pivoting 하는데 사용하는, sub query 의 내용임)


------ Correlation Coefficients among multiple variables
-- Correlation Matrix

-- (a) Calculating pairwise correlation using corr(Y, X) function

SELECT
    1 AS column_position
    , 'sepal_length' AS row
    , 'sepal_width' AS col
    , CORR(sepal_length, sepal_width) AS corr_coef
FROM iris
UNION
SELECT
    1 AS column_position
    , 'sepal_length' AS row
    , 'petal_length' AS col
    , CORR(sepal_length, petal_length) AS corr_coef
FROM iris
UNION
SELECT
    1 AS column_position
    , 'sepal_length' AS row
    , 'petal_width' AS col
    , CORR(sepal_length, petal_width) AS corr_coef
FROM iris
UNION
SELECT
    2 AS column_position
    , 'sepal_width' AS row
    , 'petal_length' AS col
    , CORR(sepal_width, petal_length) AS corr_coef
FROM iris
UNION
SELECT
    2 AS column_position
    , 'sepal_width' AS row
    , 'petal_width' AS col
    , CORR(sepal_width, petal_width) AS corr_coef
FROM iris
UNION
SELECT
    3 AS column_position
    , 'petal_length' AS row
    , 'petal_width' AS col
    , CORR(petal_length, petal_width) AS corr_coef
FROM iris;



위의 SQL query를 sub query 로 넣고, manual 하게 테이블을 Pivot 하여 우리가 원하는 상관계수 행렬 (correlation matrix)를 만들어보겠습니다. 대각행렬에서 우상단에만 상관계수를 나타내고 좌하단에는 0으로 채워넣기를 하였습니다.



-- (b) Pivoting the table to get a correlation matrix

SELECT
    column_position
    , row
    , SUM(CASE WHEN col='sepal_length' THEN corr_coef ELSE 0 END) AS sepal_length
    , SUM(CASE WHEN col='sepal_width' THEN corr_coef ELSE 0 END) AS sepal_width
    , SUM(CASE WHEN col='petal_length' THEN corr_coef ELSE 0 END) AS petal_length
    , SUM(CASE WHEN col='petal_width' THEN corr_coef ELSE 0 END) AS petal_width
FROM (
    SELECT
        1 AS column_position
        , 'sepal_length' AS row
        , 'sepal_width' AS col
        , CORR(sepal_length, sepal_width) AS corr_coef
    FROM iris
    UNION
    SELECT
        1 AS column_position
        , 'sepal_length' AS row
        , 'petal_length' AS col
        , CORR(sepal_length, petal_length) AS corr_coef
    FROM iris
    UNION
    SELECT
        1 AS column_position
        , 'sepal_length' AS row
        , 'petal_width' AS col
        , CORR(sepal_length, petal_width) AS corr_coef
    FROM iris
    UNION
    SELECT
        2 AS column_position
        , 'sepal_width' AS row
        , 'petal_length' AS col
        , CORR(sepal_width, petal_length) AS corr_coef
    FROM iris
    UNION
    SELECT
        2 AS column_position
        , 'sepal_width' AS row
        , 'petal_width' AS col
        , CORR(sepal_width, petal_width) AS corr_coef
    FROM iris
    UNION
    SELECT
        3 AS column_position
        , 'petal_length' AS row
        , 'petal_width' AS col
        , CORR(petal_length, petal_width) AS corr_coef
    FROM iris
) a
GROUP BY column_position, row
ORDER BY column_position;


원하는 상관계수행렬을 구하기는 했는데요, SQL query가 엄청 길어지고 또 복잡해졌습니다. 분석의 대상이 되는 연속형 데이터 칼럼 수가 늘어날수록 query가 더 길어지고 복잡해지는 구조여서 더 좋은 방법이 없을까 고민하게 됩니다. 짜잔~! 이럴때 PostgreSQL, Greenplum DB에서 쉽고 편하고 또 빠르게 분석하는데 쓸 수 있는 SQL 기반의 오픈소스 분석툴인 Apache MADlib이 있습니다!




  (3) MADlib으로 다수의 연속형 변수간 상관계수 행렬 구하기


위의 (2)번에서 pure SQL로 했던 상관계수 행렬 구하기를 Apache MADlib의 madlib.correlation() 함수를 사용하면 아래처럼 단 3줄이면 끝납니다! 비교할 수 없을 정도로 SQL query가 간소해졌습니다!



----- Correlation Matrix using MADlib
DROP TABLE IF EXISTS iris_corr_output, iris_corr_output_summary;
SELECT madlib.correlation( 'iris',             -- source table
                           'iris_corr_output',       -- output table
                           'sepal_length, sepal_width, petal_length, petal_width' -- target columns
                         );


/*                         
SELECT madlib.correlation( 'iris',        -- source table
                           'iris_corr_output',  -- output table
                           '*'                     -- for all columns
                         );                        
*/
                       


SELECT * FROM iris_corr_output ORDER BY column_position;






  (4) MADlib으로 다수의 연속형 변수간 그룹별로 상관계수 행렬구하기

       (Correlation Matrix by Group using Apache MADlib)


PostgreSQL, Greenplum DB에서 Apache MADlib을 사용했을 때 코드가 간소해지는점 말고 또 좋은 점 중의 하나는 그룹별 연산을 하기에도 굉장히 편리하다는 점입니다.


아래 예제는 class_name ('iris-setosa', 'iris-versicolor', 'iris-virginica') 의 그룹별로 상관계수 행렬을 구하기(correlation matrix by groups using MADlib) 입니다. 위의 (3)번 예제어서 'grouping column' 위치에 "class_name" 칼럼 이름만 추가하면 끝입니다. MADlib이 이처럼 "simple & powerful" 합니다. (만약 (2)번 방법으로 SQL로 일일이 group by 넣어서 query 짠다고 상상을 해보면 끔찍하지 않나요? ^^;)



-- Correlation Matrix by Groups using MADlib
DROP TABLE IF EXISTS iris_corr_grp_output, iris_corr_grp_output_summary;
SELECT madlib.correlation( 'iris'             -- source table
                           , 'iris_corr_grp_output' -- output table
                           , '*' -- target columns
                           , False -- verbose
                           , 'class_name' -- grouping column
                         );
                         


SELECT * FROM iris_corr_grp_output ORDER BY class_name, column_position;



[Reference]
* PostgreSQL 9.4: https://www.postgresql.org/docs/9.4/functions-aggregate.html
* Apache MADlib: https://madlib.apache.org/docs/v1.0/group__grp__correlation.html

이번 포스팅이 많은 도움이 되었기를 바랍니다 .
행복한 데이터 과학자 되세요!  :-)




728x90
반응형
저작자표시 비영리 변경금지

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

[Greenplum PostGIS] spatial_ref_sys 복제 테이블 생성하여 st_transform 함수 사용하기  (0) 2021.03.14
[Greenplum, PostgreSQL] SQL과 MADlib을 활용한 대용량데이터 선형 회귀분석 (Linear Regression using SQL, MADlib)  (0) 2020.12.01
[PostgreSQL, Greenplum] 연속형 데이터의 집계/ 요약 통계량 구하기  (0) 2020.11.28
[PostgreSQL, Greenplum] ipython-sql로 Jupyter Notebook에서 SQL query한 결과를 pandas DataFrame으로 가져오는 3가지 방법  (0) 2020.11.21
[PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법  (0) 2020.11.15
Posted by Rfriend
,

[Python pandas] 그룹 별 변수 간 상관관계 분석 (correlation with columns by groups)

Python 분석과 프로그래밍/Python 데이터 전처리 2018. 12. 24. 16:46

이번 포스팅에서는 그룹 별로 변수 간 상관관계 분석 (correlation with columns by groups) 하는 방법을 소개하겠습니다. 


Python pandas는 Pearson Correlation Coefficient를 구할 수 있는 corr(), corrwith() 함수를 제공합니다. 이들 함수를 groupby() 와 apply(lambda func)를 함께 사용함으로써 그룹 별 변수 간 상관계수를 구할 수 있습니다. 



[ 피어슨 상관계수 ( Pearson Correlation Coefficient) ]



먼저 예제로 사용할 'group 1'과 'group 2'의 2개의 그룹을 가진 간단한 DataFrame을 만들어보겠습니다. 




import numpy as np

import pandas as pd

from pandas import DataFrame


# making DataFrame with 4 random variables

np.random.seed(123) # for reproducibility

df= DataFrame(np.random.randn(10, 4), 

                    columns=['a', 'b', 'c', 'd'])

# setting index with 2 group, 'grp1' and 'grp2'

df['group'] = ['grp1', 'grp1', 'grp1', 'grp1', 'grp1', 

                  'grp2', 'grp2', 'grp2', 'grp2', 'grp2']


df = df.set_index('group')


df

abcd
group
grp1-1.0856310.9973450.282978-1.506295
grp1-0.5786001.651437-2.426679-0.428913
grp11.265936-0.866740-0.678886-0.094709
grp11.491390-0.638902-0.443982-0.434351
grp12.2059302.1867861.0040540.386186
grp20.7373691.490732-0.9358341.175829
grp2-1.253881-0.6377520.907105-1.428681
grp2-0.140069-0.861755-0.255619-2.798589
grp2-1.771533-0.6998770.927462-0.173636
grp20.0028460.688223-0.8795360.283627






  (1) 'd' 변수와 나머지 모든 변수 간 그룹 별 상관계수 구하기 : x.corrwith(x['d'])



# correlation with columns: corrwith()

corr_with_d = lambda x: x.corrwith(x['d'])


grouped = df.groupby('group')


grouped.apply(corr_with_d) 

abcd
group
grp10.8468220.0994170.0892051.0
grp20.3074770.832473-0.3904691.0




  (2) 'a'변수와 'd'변수 간 그룹 별 상관계수 구하기 : g['a'].corr[g['d'])



# inter-column correlation: corr()

corr_a_d = lambda g: g['a'].corr(g['d'])


grouped = df.groupby('group')


DataFrame(grouped.apply(corr_a_d))

0
group
grp10.846822
grp20.307477

 



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


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



728x90
반응형
저작자표시 비영리 변경금지

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

[Python pandas] 그룹 별 무작위 표본 추출 (random sampling by group)  (0) 2018.12.26
[Python pandas] 그룹 별 선형회귀모형 적합하기 (Group-wise Linear Regression)  (0) 2018.12.25
[Python Pandas] 동일 길이로 나누어서 범주 만들기 pd.cut(), 동일 개수로 나누어서 범주 만들기 pd.qcut()  (3) 2018.12.23
[Python pandas] 데이터프레임에 그룹 단위로 통계량을 집계해서 칼럼 추가하기 : df.groupby(['group']).col.transform('count')  (0) 2018.12.22
[Python Pandas] 결측값을 그룹 평균값으로 채우기 (Fill missing values by Group means)  (16) 2018.12.19
Posted by Rfriend
,

티스토리툴바