[PostGRES] 종로구 신한카드 소비 데이터 쿼리, 그리고 상관관계 분석

서울빅데이터캠퍼스에 방문하면 신한카드 소비 데이터를 받을 수 있다.

내가 가지고 있는 건 종로구의 50m 단위의 카드 소비에 관련된 데이터고,

집계 방법은... 솔직히 모른다.

 

일단 이번 쿼리의 목적은

종로구 기준으로 그리드에 포함된 DTP, NDTP의 비율과 소비의 성, 연령별 패턴의 관계성을 살펴보는 것이다.

 

연구에 쓸 수 있는 범위 내에서 query 를 날려보자.

 

/* 식품, 미용, 학원, 헬스클럽 등에 관련된 업종 코드이다
("SB_UPJONG" LIKE 'sb001' OR "SB_UPJONG" LIKE 'sb001' OR "SB_UPJONG" LIKE 'sb001' OR 
"SB_UPJONG" LIKE 'sb002' OR "SB_UPJONG" LIKE 'sb003' OR "SB_UPJONG" LIKE 'sb004' OR 
"SB_UPJONG" LIKE 'sb005' OR "SB_UPJONG" LIKE 'sb006' OR "SB_UPJONG" LIKE 'sb007' OR 
"SB_UPJONG" LIKE 'sb008' OR "SB_UPJONG" LIKE 'sb039' OR "SB_UPJONG" LIKE 'sb040' OR 
"SB_UPJONG" LIKE 'sb047' OR "SB_UPJONG" LIKE 'sb025' OR "SB_UPJONG" LIKE 'sb029')
*/

CREATE TABLE blk_sobi_total AS
(SELECT "BLK_CD", "SB_UPJONG", "SEX_CCD",  "CLN_AGE",  --50m 블록, 세부업종, 성별, 연령대에 대한 정보가 포함되고
		avg("TS_AT"::numeric) avg_at, avg("TS_CNT"::numeric) avg_cnt --총 결제액과 결제 횟수가 포함된다. 왜인지 결제횟수는 최소 5.0으로 맞춰져 있는 듯 하다
FROM jongno_sobi
WHERE "TA_YM" = 202106 AND "APV_TS" <= 23 AND "APV_TS" >= 17 --연도와 시간대를 포함할 수 있다. 나는 퇴근 후 오후 5시부터 밤 11시까지로 잡았다.
GROUP BY "BLK_CD", "SB_UPJONG", "SEX_CCD",  "CLN_AGE")  --최대한 많은 category 를 기준으로 평균을 보고자 했다

 

그리고 빌딩 기준으로 geometry 를 설정해뒀던 DTP 데이터를 point로 바꿔보자.

왜냐하면 이번 연구부터는 빌딩 단위의 정보보다는 geographical location 단위의 정보가 더 중요하니까.

 

사실 베스트는 NAVER 에 기록된 좌표를 싹 얻어서 그걸 중심으로 DTP 의 좌표를 만들고,

그렇지 못한 NDTP 들을 빌딩의 centroid 로 설정하는 방식이 제일 좋겠지만,

이건 데이터의 한계이기도, 크롤링을 또 해야한다는 비극이기도, 추후 통합하는 그리드의 사이즈에 따라 달라지는 선택의 문제이기도 하니까

 

그냥 centroid를 쓰겠다.

ALTER TABLE "DTP_jongno" RENAME TO dtp_jongno; --QGIS 로 불러오면 꼭 이걸 해줘야 번거로움이 줄어든다

ALTER TABLE dtp_jongno ADD COLUMN pt_geom GEOMETRY;

UPDATE dtp_jongno
SET pt_geom = ST_Transform(ST_SetSRID(ST_MakePoint(x, y), 5174), 5179) --좌표가 있으면 이걸 쓰고
WHERE x is NOT null AND x != 0;

UPDATE dtp_jongno
SET pt_geom = ST_centroid(geom) -- 없으면 그냥 centroid 를 쓰겠다
WHERE x is null OR x = 0;

-- 이후에 확인한 결과이지만, 2가지 문제가 발견된다
-- 1. 대부분의 인허가 데이터들의 좌표는 그냥 빌딩의 centroid 로 기록되어있다
-- 2. 의외로 빌딩 정보와 인허가 데이터상의 좌표 정보가 매칭되지 않는 경우가 많다.
-- 3. 이에, 그냥 centroid를 쓰는 게 더 정확하다는 사실을 확인했다

 

그리고 기준 그리드 내에 포함되는 포인트의 갯수를 세어주자.

-----------------------------------Thank you Chat GPT!--------------
CREATE TABLE dtp_count_cd AS
(SELECT 
    g.index, -- Replace 'grid_id' with the primary key or unique identifier of your grid table
    COUNT(p.*) AS ndtp_cnt
FROM 
    grid_cd g --나는 50m 그리드 shp를 따로 만들어서 활용했다
LEFT JOIN 
    (SELECT * FROM dtp_jongno WHERE digi_type = 0) p
ON 
    ST_Within(p.pt_geom, g.geom) -- 'geom' is the geometry column in both tables
GROUP BY 
    g.index);
	
ALTER TABLE dtp_count_cd ADD COLUMN dtp_cnt INT;
ALTER TABLE dtp_count_cd ADD COLUMN adtp_cnt INT;

UPDATE dtp_count_cd
SET dtp_cnt = dtp_within.dtp_cnt
FROM (SELECT 
		g.index, -- Replace 'grid_id' with the primary key or unique identifier of your grid table
		COUNT(p.*) AS dtp_cnt
	FROM 
		grid_cd g
	LEFT JOIN 
		(SELECT * FROM dtp_jongno WHERE digi_type = 1) p
	ON 
		ST_Within(p.pt_geom, g.geom) -- 'geom' is the geometry column in both tables
	GROUP BY 
		g.index) dtp_within;
		
UPDATE dtp_count_cd
SET dtp_cnt = dtp_within.adtp_cnt
FROM (SELECT 
		g.index, -- Replace 'grid_id' with the primary key or unique identifier of your grid table
		COUNT(p.*) AS adtp_cnt
	FROM 
		grid_cd g
	LEFT JOIN 
		(SELECT * FROM dtp_jongno WHERE digi_type = 2) p
	ON 
		ST_Within(p.pt_geom, g.geom) -- 'geom' is the geometry column in both tables
	GROUP BY 
		g.index) dtp_within
        
ALTER TABLE dtp_count_cd ADD COLUMN ndtp_r numeric;
UPDATE dtp_count_cd
SET ndtp_r = ndtp_cnt / (ndtp_cnt + dtp_cnt + adtp_cnt + 0.001); --0으로 나누는 게 안된다

 

 

이제 재료를 만들었으니 간단히 python 으로 분석해보자

 

import psycopg2 as psql
import pandas as pd
from sqlalchemy import create_engine
import seaborn as sns
import matplotlib.pyplot as plt

#####################
SQL, 데이터프레임을 읽어오는 코드는 너어무 귀찮으니까 생략한다
#####################

######################업종별 분류
upjongs = ['SB001', 'SB002','SB003','SB004','SB005','SB006','SB007','SB008',
           'SB025','SB029','SB039','SB040','SB047',]
blk_sobi = blk_sobi[blk_sobi['SB_UPJONG'].isin(upjongs)]

######################연령대별 분류
blk_10s = blk_sobi[(blk_sobi['CLN_AGE'] == '10대')]
blk_20s = blk_sobi[(blk_sobi['CLN_AGE'] == '20대초') | (blk_sobi['CLN_AGE'] == '20대후')]
blk_30s = blk_sobi[(blk_sobi['CLN_AGE'] == '30대초') | (blk_sobi['CLN_AGE'] == '30대후')]
blk_40s = blk_sobi[(blk_sobi['CLN_AGE'] == '40대초') | (blk_sobi['CLN_AGE'] == '40대후')]
blk_50s = blk_sobi[(blk_sobi['CLN_AGE'] == '50대초') | (blk_sobi['CLN_AGE'] == '50대후')]
blk_60s = blk_sobi[(blk_sobi['CLN_AGE'] == '60대초') | (blk_sobi['CLN_AGE'] == '60대후')]
blk_70s = blk_sobi[(blk_sobi['CLN_AGE'] == '70대이상') | (blk_sobi['CLN_AGE'] == '70대후')]
blk_xs = blk_sobi[(blk_sobi['CLN_AGE'] == 'X')]

######################블록별 평균
blk_10s_avg = blk_10s.groupby('BLK_CD').mean().reset_index().rename(columns = {'avg_at': '10s_at', 'avg_cnt': '10s_cnt'})
blk_20s_avg = blk_20s.groupby('BLK_CD').mean().reset_index().rename(columns = {'avg_at': '20s_at', 'avg_cnt': '20s_cnt'})
blk_30s_avg = blk_30s.groupby('BLK_CD').mean().reset_index().rename(columns = {'avg_at': '30s_at', 'avg_cnt': '30s_cnt'})
blk_40s_avg = blk_40s.groupby('BLK_CD').mean().reset_index().rename(columns = {'avg_at': '40s_at', 'avg_cnt': '40s_cnt'})
blk_50s_avg = blk_50s.groupby('BLK_CD').mean().reset_index().rename(columns = {'avg_at': '50s_at', 'avg_cnt': '50s_cnt'})
blk_60s_avg = blk_60s.groupby('BLK_CD').mean().reset_index().rename(columns = {'avg_at': '60s_at', 'avg_cnt': '60s_cnt'})
blk_70s_avg = blk_70s.groupby('BLK_CD').mean().reset_index().rename(columns = {'avg_at': '70s_at', 'avg_cnt': '70s_cnt'})
blk_xs_avg = blk_xs.groupby('BLK_CD').mean().reset_index().rename(columns = {'avg_at': 'xs_at', 'avg_cnt': 'xs_cnt'})

######################분석을 위한 데이터프레임 병합 (무지성 merge 사용도 의미가 있다)
blk_merged = blk_10s_avg.merge(blk_20s_avg, on = 'BLK_CD').merge(blk_30s_avg, on = 'BLK_CD').merge(blk_40s_avg, on = 'BLK_CD').merge(blk_50s_avg, on = 'BLK_CD').merge(blk_60s_avg, on = 'BLK_CD').merge(blk_70s_avg, on = 'BLK_CD').merge(blk_xs_avg, on = 'BLK_CD')

######################가장 기초적인 correlation 분석
#blk_aggr.drop(columns = ['BLK_CD', 'geom'])
corr_matrix = blk_aggr.drop(columns = ['BLK_CD', 'geom']).corr(method='spearman')

plt.figure(figsize=(20, 20))
sns.heatmap(corr_matrix, annot=True, cmap='coolwarm', fmt=".2f", linewidths=0.5)
plt.title("Correlation Heatmap with Annotations")
plt.show()

 

일단 핵심은, 소비와 ndtp 비율과는 상관이 1도 없다는 사실.