이번 포스팅에서는 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)을 소개하겠습니다.


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

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



728x90
반응형
Posted by R Friend Rfriend

댓글을 달아 주세요

DataFrame을 가지고 분석을 진행하다 보면 대부분의 경우 결측값(missing value)이 골치거리로 따라 다닙니다.

 

데이터가 원래 수집 혹은 측정이 안되었을 수도 있고, 다수의 DataFrame을 서로 병합하는 과정에서 결측값이 생길 수도 있으며, index를 재설정(reindex)하는 경우에도 결측값이 생길 수 있습니다.

 

이처럼 다양한 이유로 인해서 생기는 결측값은 분석 오류가 발생시키거나 혹은 왜곡시킬 위험이 있습니다. 따라서 분석할 DataFrame을 생성했으면 결측값(missing value)이 있는지 여부에 대해서 꼭 확인하고 조치하여야 합니다.

 

이번 포스팅에서는 Python pandas의 isnull(), notnull() 메소드를 활용해서 결측값이 있는지 여부를 확인하는 방법을 소개하겠습니다.

 

 

 

 

Python pandas에서는 결측값을 'NaN' 으로 표기하며, 'None'도 결측값으로 인식합니다.

 

 

먼저 결측값이 있는 DataFrame을 만들어보겠습니다.

 

 

# making DataFrame with missing values

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': [0.5, 2.2, 3.6, 0.4]})


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

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

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


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


In [6]: df_all

Out[6]:

     A    B KEY    C    D
0   A0  0.5  K0  NaN  NaN
1   A1  2.2  K1  NaN  NaN
2   A2  3.6  K2   C2   D2
3   A3  0.4  K3   C3   D3
NaN  NaN  K4   C4   D4
NaN  NaN  K5   C5   D5

 

 

 

 

  (1) DataFrame 전체의 결측값 여부 확인 : df.isnull(), isnull(df), df.notnull(), notnull(df)

 

isnull() 메소드는 관측치가 결측이면 True, 결측이 아니면 False boollean 값을 반환합니다.

notnull() 메소드는 관측치가 결측이면 False, 결측이 아니면 True를 반환합니다.(isnull() 과 정반대)

 

isnull(DataFrame) 과 DataFrame.isnull() 은 동일한 값을 반환하며, notnull(DataFrame)과 DataFrame.notnull() 역시 동일한 의미의 script 입니다.

 

 

In [7]: pd.isnull(df_all)

Out[7]:

       A      B    KEY      C      D
0  False  False  False   True   True
1  False  False  False   True   True
2  False  False  False  False  False
3  False  False  False  False  False
4   True   True  False  False  False
5   True   True  False  False  False


In [8]: df_all.isnull()

Out[8]:

       A      B    KEY      C      D
0  False  False  False   True   True
1  False  False  False   True   True
2  False  False  False  False  False
3  False  False  False  False  False
4   True   True  False  False  False
5   True   True  False  False  False


In [9]: pd.notnull(df_all)

Out[9]:

       A      B   KEY      C      D
0   True   True  True  False  False
1   True   True  True  False  False
2   True   True  True   True   True
3   True   True  True   True   True
False  False  True   True   True
False  False  True   True   True

 

In [10]: df_all.notnull()

Out[10]:

       A      B   KEY      C      D
0   True   True  True  False  False
1   True   True  True  False  False
2   True   True  True   True   True
3   True   True  True   True   True
False  False  True   True   True
False  False  True   True   True

 

 

 

  (2) 특정 변수, 원소에 결측값 추가하기, 결측값 여부 확인하기 : indexing & None

 

아래 예시의 'df_all' DataFrame 에서 ['A', 'B'] 칼럼의 ['0', '1'] index 위치에 있는 관측치에 'None'을 할당하여 결측치를 만들어보았습니다.

 

'A'칼럼의 경우 'string' 데이터 형식인데요, 'None'을 할당하니 'None'으로 입력되었습니다.  반면에, 'B' 칼럼의 경우 'float' 데이터 형식인데요, 'None'을 할당하니 'NaN'으로 자동으로 입력되었습니다.

 

DataFrame의 행, 열을 기준으로 indexing을 하고 싶을 때는 DataFrame.ix[[row1, row2], ['col1', 'col2']] 을 사용하면 됩니다.  아래 예시를 참고하세요.

 

 

In [11]: df_all

Out[11]:

     A        B  KEY    C    D
0   A0      0.5  K0  NaN  NaN
1   A1      2.2  K1  NaN  NaN
2   A2      3.6  K2   C2   D2
3   A3      0.4  K3   C3   D3
NaN  NaN  K4   C4   D4
NaN  NaN  K5   C5   D5

 

In [12]: df_all.ix[[0, 1], ['A', 'B']] = None


In [13]: df_all

Out[13]:

      A         KEY    C      D
None  NaN  K0  NaN  NaN
None  NaN  K1  NaN  NaN
2    A2     3.6   K2   C2   D2
3    A3     0.4   K3   C3   D3
4   NaN  NaN  K4   C4   D4
5   NaN  NaN  K5   C5   D5


In [14]: df_all[['A', 'B']].isnull()

Out[14]:

       A      B
0   True   True
1   True   True
2  False  False
3  False  False
4   True   True
5   True   True

 

 

 

 

  (3) 칼럼별 결측값 개수 구하기 : df.isnull().sum()

 

 

# counting missing value numbers for all columns

In [15]: df_all.isnull().sum()

Out[15]:

A      2
B      2
KEY    0
C      2
D      2
dtype: int64

 

# counting missing value numbers for 'A' column

In [16]: df_all['A'].isnull().sum()

Out[16]: 2

 

 

 

 

반대로, 칼럼별 결측값이 아닌 값의 개수를 구하려면 df.notnull().sum() 을 사용하면 됩니다.

 

 

# counting notnull value numbers for all columns

In [17]: df_all.notnull().sum()

Out[17]:
A      4
B      4
KEY    6
C      4
D      4
dtype: int64

 

 

 

 

  (4) 행(row) 단위로 결측값 개수 구하기 : df.isnull().sum(1)

       행(row) 단위로 실측값 개수 구하기 : df.notnull().sum(1)

 

 

In [18]: df_all

Out[18]:

     A    B KEY    C    D
0   A0  0.5  K0  NaN  NaN
1   A1  2.2  K1  NaN  NaN
2   A2  3.6  K2   C2   D2
3   A3  0.4  K3   C3   D3
4  NaN  NaN  K4   C4   D4
5  NaN  NaN  K5   C5   D5


In [19]: df_all['NaN_cnt'] = df_all.isnull().sum(1)


In [20]: df_all['NotNull_cnt'] = df_all.notnull().sum(1)


In [21]: df_all

Out[21]:

     A      B    KEY   C     D     NaN_cnt   NotNull_cnt
0   A0    0.5   K0  NaN  NaN      2            4
1   A1    2.2   K1  NaN  NaN      2            4
2   A2    3.6   K2   C2    D2        0            6
3   A3    0.4   K3   C3    D3        0            6
4  NaN  NaN  K4   C4    D4        2            4
5  NaN  NaN  K5   C5    D5        2            4

 

 

 

 

다음번 포스팅에서는 결측값 연산에 대해서 소개하겠습니다.

 

 

728x90
반응형
Posted by R Friend Rfriend

댓글을 달아 주세요