신한카드 데이터 분석 2.1: 전처리 과정을 통해 데이터 파악하기 with DataGrip

 

이번 포스트에서는,

• 데이터 전처리 개념과 과정 이해
• 전처리 해보기
• 전처리 과정을 통해 데이터 파악하기

쥬르륵

---

1. 데이터 전처리

데이터 전처리는 분석을 위해 수집한 데이터를 분석에 적합한 형태로 가공하는 것이다.

가공된 데이터는 분석 모델을 구축하고 정확한 분석 결과를 제공해 준다.

전처리 과정에서 생각해야 할 것은,

• 불필요한 데이터 제거 🙅🏻
• 결측치나 이상치 처리 🔍

---

2. 데이터 전처리 과정

1. 데이터 정제: 결측치, 이상치를 처리하여 결과에 영향을 미치는 오류 방지
2. 데이터 변환: 날짜, 문자열 변환
3. 데이터 필터링: 조건에 따른 데이터 추출
4. 데이터 정렬: 분석에 필요한 순서대로 데이터 정렬
5. 데이터 그룹화: 그룹별로 분류 후 통계 정보 추출
6. 데이터 병합 및 분할
7. 데이터 샘플링: 샘플 데이터 추출 후 분석
8. 데이터 집계: 그룹별 집계 정보 추출

---

3. Do it!

실무에서는 데이터 전처리를 어떠한 방식으로 진행하는지 잘 모르기에, 이번 포스트에서 보여지는 일련의 과정들이 허접하고 어설퍼 보일 듯하다. 그래도 앞서 설명한 데이터 전처리 과정을 나름대로 따라가보고자 한다.

 

이제 정제가 필요한 부분에 대하여 가공을 해보고자 한다.

1) 데이터 정제 - 결측치 및 이상치 처리

데이터 정제가 필요하다고 생각한 부분은,

• salesBySexAge_in 👉🏻 '성별, '연령대별' 컬럼 NULL값 제거
• salesByAddress_in 👉🏻 '고객주소시군구' 컬럼 NULL값 제거
• 외국인 테이블에는 NULL값 ❌

데이터를 훑어 보았을 때 이용자의 ‘성별’, ‘연령대별’, ‘고객주소시군구’ 컬럼에 NULL값이 존재하는 것을 확인하였다.

각 테이블에 NULL값이 약 50개 정도 되는 것을 확인하였고, 이는 결과를 도출하는 데 영향을 준다고 생각하여 삭제하였다.

# 데이터 정제 - null값 제거하기
# 'DELETE FROM 'table name' WHERE 'column name' IS NULL;' 형식으로 특정 데이터 삭제하기.
DELETE FROM salesBySexAge_in WHERE `성별(SEX_CCD)` IS NULL;
DELETE FROM salesBySexAge_in WHERE `연령대별(AGE_GB)` IS NULL;
DELETE FROM salesByAddress_in WHERE `고객주소시군구(SGG)` IS NULL;

 

DELETE문을 통해 NULL값이 제거된 것을 확인할 수 있다.

 

2) 데이터 변환

데이터 변환이 필요하다고 생각한 부분은,

• salesByAddress_in 👉🏻 '고객주소광역시', '고객주소시군구' 컬럼 주소지 통일 ex) 서울특별시 → 서울, 경기도 → 경기
• 내국인 모든 테이블 👉🏻 '기준년월', '월별' 컬럼 '%Y-%m-%d' 형식으로 변경
• 외국인 모든 테이블 👉🏻 '기준년월', '월별' 컬럼 '%Y-%m-%d' 형식으로 변경

이용자의 주소지 컬럼은 대부분 ‘서울’, ‘경기’, ‘인천’ 등 ✌🏻두 글자✌🏻로 구성되어 있다.

몇몇 컬럼은 ‘서울특별시’, ‘강원도’, ‘경기도’로 되어 있는 것을 보고 통일할 필요가 있다고 생각하였다.

 

UPDATE문을 통해 특정 데이터를 변환해 주었다.

# 데이터 변환 - 주소지 형식 통일하기
# 'UPDATE 'table name' SET '반환값' WHERE '조건';' 형식으로 특정 데이터 변경하기.
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '서울' WHERE `고객주소광역시(SIDO)` = '서울특별시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '강원' WHERE `고객주소광역시(SIDO)` = '강원도';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소광역시(SIDO)` = '경기도';
UPDATE salesByAddress_in SET `고객주소시군구(SGG)` = '강동구' WHERE `고객주소시군구(SGG)` = '강동구서울';

 

그리고 업종 분류 테이블 외 모든 테이블에는 ‘기준년월’, ‘일별’이라는 날짜 컬럼이 존재한다.

해당 컬럼들은 연속된 숫자 형태로 나열되어 있기 때문에 DATE_FORMAT()을 이용하여 형식 변경이 필요하다고 생각하였다.

여기서 ‘일별’ 컬럼은 년월일을 다 보여주기 때문에 해당 컬럼만 ‘%Y-%m-%d’ 형식으로 변경하고자 한다.

# 데이터 변환 - 날짜 포맷 변경하기
# UPDATE 'table name' SET 'column name' = DATE_FORMAT('column name', '%Y-%m-%d'); 형식으로 변경.
UPDATE salesByTime_in SET `일별(TS_YMD)` = DATE_FORMAT(`일별(TS_YMD)`, '%Y-%m-%d');
UPDATE salesByTime_out SET `일별(TS_YMD)` = DATE_FORMAT(`일별(TS_YMD)`, '%Y-%m-%d');
UPDATE salesBySexAge_in SET `일별(TS_YMD)` = DATE_FORMAT(`일별(TS_YMD)`, '%Y-%m-%d');
UPDATE salesByAddress_in SET `일별(TS_YMD)` = DATE_FORMAT(`일별(TS_YMD)`, '%Y-%m-%d');
UPDATE salesByCountry_out SET `일별(TS_YMD)` = DATE_FORMAT(`일별(TS_YMD)`, '%Y-%m-%d');

 

특정 컬럼만 ‘%Y-%m-%d’ 형식으로 변경 것을 확인할 수 있다.

 

3) 데이터 그룹화

다음으로 GROUP BY 함수를 이용하여 그룹별로 조회해보는 과정을 통해 데이터를 파악하고자 한다.

• 「salesByTime_in」요일별 카드이용건수와 카드이용액 ▶︎ 토요일이 가장 높은 것으로 출력됨

# 요일별 카드이용건수 조회.
SELECT SUM(`카드이용건수(USECT_CORR)`) AS '카드이용건수', `요일(DAW)`
FROM salesByTime_in
GROUP BY `요일(DAW)`
ORDER BY SUM(`카드이용건수(USECT_CORR)`) DESC;

# 요일별 카드이용액 조회.
SELECT SUM(`카드이용금액계(AMT_CORR)`) AS '카드이용액', `요일(DAW)`
FROM salesByTime_in
GROUP BY `요일(DAW)`
ORDER BY SUM(`카드이용금액계(AMT_CORR)`) DESC;

 

• 「salesByAddress_in」국내 시도별 카드이용건수와 카드이용액 ▶︎ 서울이 다른 지역보다 압도적으로 높은 것으로 출력

# 시도별 카드이용건수 및 카드이용액.
SELECT SUM(`카드이용금액계(AMT_CORR)`) AS '카드이용액',
       SUM(`카드이용건수(USECT_CORR)`) AS '카드이용건수',
       `고객주소광역시(SIDO)`
FROM salesByAddress_in
GROUP BY `고객주소광역시(SIDO)`
ORDER BY SUM(`카드이용금액계(AMT_CORR)`) DESC, SUM(`카드이용건수(USECT_CORR)`) DESC;

 

서울 지역구별 카드이용건수와 카드이용액 조회 결과,

• 서울 내 위치한 지역구 외에 경기 지역들도 조회되는 것을 확인.
• UPDATE문을 통해 수정할 필요가 있다고 생각.

# 서울 지역구별 카드이용건수 및 카드이용액. (수정전)
SELECT `고객주소시군구(SGG)`,
       SUM(`카드이용금액계(AMT_CORR)`) AS '카드이용액',
       SUM(`카드이용건수(USECT_CORR)`) AS '카드이용건수'
FROM salesByAddress_in
WHERE `고객주소광역시(SIDO)` = '서울'
GROUP BY `고객주소시군구(SGG)`
ORDER BY SUM(`카드이용금액계(AMT_CORR)`) DESC, SUM(`카드이용건수(USECT_CORR)`) DESC;

 

UPDATE문을 통해 경기 지역들의 `고객주소광역시(SIDO)` 컬럼의 데이터를 '경기'로 변경하였다.

# 서울로 표기되어 있는 지역들을 경기로 수정하기.
# 'UPDATE 'table name' SET '반환값' WHERE '조건';' 형식으로 특정 데이터 변경하기.
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '김포시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '포천시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '고양시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '시흥시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '하남시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '성남시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '남양주시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '의정부시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '용인시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '안산시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '구리시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '부천시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '수원시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '광명시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '화성시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '광주시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '평택시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '안양시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '군포시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '의왕시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '과천시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '이천시';
UPDATE salesByAddress_in SET `고객주소광역시(SIDO)` = '경기' WHERE `고객주소시군구(SGG)` = '오산시';

 

(수정 후) 국내 시도별 및 서울 지역구별 카드이용건수와 카드이용액 결과 테이블 조회 결과,

• 경기와 서울의 카드이용액은 비슷한 수치를 기록하고 있으나, 경기의 이용건수가 조금 더 높음.
• 서울내 지역구 중에서 카드이용액은 중랑구, 카드이용건수는 강남구가 가장 높은 것으로 확인.

 

• 「salesBySexAge_in」연령대별 카드이용액과 카드이용건수 ▶︎ 남성이 근소한 차이로 높게 출력됨.
• 「salesBySexAge_in」성별 카드이용액과 카드이용건수 ▶︎ 30대가 가장 높게 출력됨.

# 성별 카드이용액 및 카드이용건수
SELECT `성별(SEX_CCD)`,
       SUM(`카드이용금액계(AMT_CORR)`) AS '카드이용액',
       SUM(`카드이용건수(USECT_CORR)`) AS '카드이용건수'
FROM salesBySexAge_in
GROUP BY `성별(SEX_CCD)`
ORDER BY SUM(`카드이용금액계(AMT_CORR)`) DESC, SUM(`카드이용건수(USECT_CORR)`) DESC;

# 연령별 카드이용액 및 카드이용건수
SELECT `연령대별(AGE_GB)`,
       SUM(`카드이용금액계(AMT_CORR)`) AS '카드이용액',
       SUM(`카드이용건수(USECT_CORR)`) AS '카드이용건수'
FROM salesBySexAge_in
GROUP BY `연령대별(AGE_GB)`
ORDER BY SUM(`카드이용금액계(AMT_CORR)`) DESC, SUM(`카드이용건수(USECT_CORR)`) DESC;

 

 

이번 포스트에서 국내 이용자들을 대상으로 집단별 카드이용금액과 카드이용건수를 조회해 보았다.

그러나, 업종별 분류코드 테이블(typeOfBusiness_in)과의 JOIN을 통해 세부적으로 그룹화할 필요가 있다고 생각하였다.

 

다음 포스트에서는,

• 국내 이용자들에 이어서 국외 이용자들의 집단별 카드이용금액과 카드이용건수 조회
• 국내외 이용자들의 매출액 테이블과 업종별 분류 코드 테이블 JOIN
• 더 구체적인 그룹화를 통한 데이터 파악

 

 

🔗 관련 링크

서울시 빅데이터 캠퍼스 - https://bigdata.seoul.go.kr/main.do

원 데이터 확인(이전 포스트) - https://justinaofjune.tistory.com/59

데이터 전처리 개념과 과정 - https://chaheekwon.tistory.com/entry/데이터-전처리의-개념과-중요성

데이터 전처리의 개념과 중요성