[Excel포폴_서울시 2022 상권 분석]_1_공공데이터 수집 및 전처리

Hyejin Beck·2023년 12월 28일
1

Excel & Sheet

목록 보기
15/22

코로나시기(2020~2022)보다 최근 경제가 더 안좋아졌다는 이야기가 많이 들린다.
그래서 알아보는

서울시 폐업점포 데이터 분석

  • 기획의도
    최근 서울시 상권 내 폐업률이 올라가는지와 폐업 상권에 대한 데이터 분석
  • 분석방향
    지역별 폐업룔 통계 분석
    업종별 폐업에 미치는 요인 상관 분석
    서울시 지도로 확인하는 폐업률 확인 등

물론 엑셀 파일이 익숙해져서, 작업은 엑셀로 하지만
완료 후 구글시트로 웹 게시 형태로 공유할 예정입니다.
서울시 상권 폐업률 분석 프로젝트

csv파일을 xlsx로 변환한 뒤, 모두 엑셀로 진행합니다.

데이터 변환

원본 csv파일을 jupyterlab(vscode)를 통하여 xlsx 형태로 변환해줍니다. 

# 필요 모듈 설치
!pip install xlsxwriter

# 필요 모듈 불러오기 
import xlsxwriter
import pandas as pd

# 파일 불러오기
data = data.read_csv('./Desktop/서울시 상권분석서비스(상권변화지표-행정동).csv', encoding='euc_kr')

# 엑셀로 저장하기
data.to_excel('상권변화지표.xlsx', index=False, engine='xlsxwriter')

데이터 확인

상권영역

총 425개 raws

인구밀도

상권변화

총 8075개 raws
영업 점포와 폐업 점포의 영업기간 평균

  • LL(다이나믹) : 도시재생 및 신규 개발 상권으로 창업 진출입시 세심한 주의가 필요한 상권(지역), 특정시점 사업체의 영업기간이 서울시 평균 생존영업기간보다 낮고, 서울시 평균 폐업영업기간보다 낮은 상권(지역)

  • LH(상권확장) : 경쟁력 있는 신규 창업 우위 상권(지역), 특정 시점 사업체의 영업기간이 서울시 평균 생존영업기간보다 낮고, 서울시 평균 폐업영업기간보다 높은 상권(지역)

  • HL(상권축소) : 경쟁력 있는 기존 업체 우위 상권(지역), 특정 시점 사업체의 영업기간이 서울시 평균 생존영업기간보다 높고, 서울시 평균 폐업영업기간보다 낮은 상권(지역)

  • HH(정체) : 창업 진출입시 세심한 주의 상권(지역), 특정 시점 사업체의 영업기간이 서울시 평균 생존영업기간보다 높고, 서울시 평균 폐업영업기간보다 높은 상권(지역)

  • (서울)운영영업개월평균: 해당 (서울시전체의)행정동의 운영중 점포들의 평균 영업 개월 수

  • (서울)폐업영업개월평균: 해당 (서울시전체의)행정동의 폐업한 점포들의 평균 영업 개월 수

  • 운영 영업 개원은 생존한 사업체의 평균 영업기간

  • 폐업 영업 개원은 폐업한 사업체의 평균 영업기간

상권변화지표는 서울시를 기준으로 해당하는 행정동의
운영영업개월평균이 낮거나 같다면 L 높으면 H
폐업영업개월평균이 낮거나 같다면 L 높으면 H
로 두고 두 문자열을 합하여 산출

상권점포

총 141,334개 raws
2022년을 4분기로 나누어 1~4분기의 행정동으로 나뉜
각 서비스 업종 별 점포수/ 개업률 / 폐업률 등

경도 위도 확인

영역과 인구데이터를 통해
정확한 경도 위도 데이터는 따로 구분해둔 것이 없어서, 위의 데이터와 잘 맞는지 확인해야합니다.

비율은 다르나, 얼추 맞는 것 같습니다.
분산형 차트 > 데이터 선택 > 추가

그래도 한 번 더 5개 행정동을 기준삼아 확인해줍니다.

그래프 비율이나 크기는 조절해가며 맞춰주니 그럴싸해보입니다.

데이터 병합

가장 데이터가 많은 상권변화지표 데이터를 기준으로 2022년 1분기부터 2023년 3분기(데이터상 최신) 까지 데이터 먼저 가져와줍니다.
그때그때 필요한것 기준으로 vlookup 등을 통해 불러와줍니다.

오타 체크

피벗테이블로 차트화 하기 전, 데이터 확인을 다시 한번 해보겠습니다.

종로1·2·3·4가동 또는 종로1?2?3?4가동 -> 종로1.2.3.4가동
면목3.8동 등 은근 텍스트가 서로 일치하지 않는 행정동이 많습니다.

아예 =substitude(셀,기존,변화)로 적용시켜줍니다.
· 또는 ? 가 없는 셀들은 그냥 기존대로 그대로 나옵니다.

=SUBSTITUTE(SUBSTITUTE($C2,"·","."),"?",".")

카테고리 간소화

서비스 업종 코드명이 100개입니다.
(한식음식점, 중식음식점, 자동차수리, 자동차미용 등등)

이 중 해당 지역 거주하거나 일하고 있는 사람들이 생활하며 소비하는 업종으로 별도 분류합니다. (주류도매, 모터사이클수리, 전자상거래업, 녹음실 등 제외)

=if(F2셀에 있는 값이 Q4부터 Q13안에 해당하는 값이라면, 모두 Q3값으로 바꾸고, 아니라면 그대로 F2) 을 이용해서 서비스업종 일부 그룹화를 하겠습니다.

어느 범위(복수) 안에 해당하는 값들(복수)이 있다면
=countif(범위,셀)으로 일치하는 값이 있는지 먼저 세야 합니다.
있다면 1이니까 >0 으로 필터를 거쳐, 바꿀값을 지정, 아니라면 그대로

=IF(COUNTIF($Q$4:$Q$13,$F2)>0,$Q$3,IF(COUNTIF($R$4:$R$22,$F2)>0,$R$3,$F2))

이런식으로 해당서비스업종이 어느목록에 속한다면 "바꿀 업종명" 아니라면 다시 if문으로 써줍니다.

=IF(COUNTIF($Q$4:$Q$13,$F2)>0,$Q$3,     만약 $해당$범위 내에서 $F2가 포함된다면, $Q$3으로 수정
    IF(COUNTIF($R$4:$R$22,$F2)>0,$R$3,    아니라면 $해당$범위 내에 $F2가 포함된다면, $R$3으로 수정
        IF(COUNTIF($S$4:$S$7,$F2)>0,$S$3,    아니라면 $해당$범위 내에 $F2가 포함된다면, $S$3으로 수정
            IF(COUNTIF($T$4:$T$12,$F2)>0,$T$3,""   아니라면 $해당$범위 내에 $F2가 포함된다면, $T$3으로 수정하고 없으면 빈 값
            ))))
            
참고로 F2는 F3, F4, F5 이렇게 줄줄이 내려가줄거니까 F만 고정 
나머지는 $모두$고정

파이썬으론 할 줄 아는데, 엑셀로는 노가다를 해야하나 순간 좌절했지만
생각해보니 파이썬 코드 형태와 거의 똑같습니다.
그냥 코드가 길어보일 뿐입니다.

상권변화지표를 보니, 분기 흐름에 따라 상권변화지표가 달라지는 지역도 있습니다. 자치구별 분기에 따라 변화되는 상권지표를 보고 싶습니다.

시간 흐름에 따른 변화를 보기 위해 상권변화를 수치로 바꾸는 인코딩을 하겠습니다.

변화상권 차트

인코딩(문자열변환)

상권확장 (신규창업 우위 상권) --> 4
상권축소 (기존업체 우위 상권) --> 3
다이나믹 (신규개발 상권) --> 2
정체 (창업 비추천) --> 1 

=IF($F3="상권확장",4,
    IF($F3="상권축소",3,
        IF($F3="다이나믹",2,1)))
        
"4" 로 바꾸면 문자열로 되어 안됩니다. 추후 평균등 수치 불가

히트맵

조건부 서식으로 새규칙 만들어서 색상 선택해줍니다.

자치구별 각 분기마다 행정동 평균 상권지표입니다.
강남구가 무조건 행정동평균상권지표가 제일 높을 줄 알았는데,
의외로 성북구가 높네요.

자치구를 슬라이서로 빼내줘서,
버튼을 누른 자치구에 해당하는 행정동별 평균 상권 지표를 보겠습니다.


생각보다 성북구의 많은 행정동들이 신규 창업에 우위를 가지고 있습니다!

추가로 피벗테이블을 만들어 확인도 해보았습니다.

폐업업종 차트

기존 개업/폐업되는 서비스업종 데이터를 보기 좋게 전처리 해주었습니다.
(vlookup으로 여러 데이터 불러오기, 서비스업종 코드를 좀 더 간단하게 그룹화하기 등)
여기에 개업 대비 폐업 비중이 궁금하여 새 열을 수식 넣어주겠습니다.

개업점포수 대비 폐업점포수 비중 

             폐업점포수
   ( ------------------------ ) * 100
       개업점포수 + 폐업점포수

= ( 폐업점포수 / (개업점포수 + 폐업점포수 )) * 100

그러나 폐업점포수와 개업점포수가 0인 곳은 #DIV/0! 으로 오류가 납니다.
iferror를 통해 오류가 난다면 0으로 반환

=IFERROR([폐업점포수] / ([개업점포수] + [폐업점포수]) * 100, 0)

공통 슬라이서 (보류)

두 개의 다른 데이터로 만들어진 두 개의 피벗테이블을
하나의 슬라이서(자치구) 로 공통화 하고 싶습니다.

두 데이터 기준이 달라, 데이터 병합은 어렵지만
슬라이서는 하나로 병합할 수 있을 것 같습니다.

검색해보니, 아예 다른 데이터로 만들어진 두 피벗테이블은 필드가 달라 하나의 슬라이서로 연결이 어려울 것 같습니다. (보고서 연결이 안됨)

함수 복습

averageif

조건이 있는 평균 값 구하기 =averageif(범위, 조건)

범위내 0을 제외한 값들의 평균을 구하세요
=averageif(범위,"<>0)

범위내에 해당하는 값의 평균을 구하세요
=averageif(기준범위,"기준값",평균범위)
쉽게 말해, 기준범위(남,여,남,남,여,남,,)중에 "남" 에 해당하는 값들의 범위내 평균

averageifs

여러가지 조건이 있는 평균 값 구하기 =averageifs(평균범위,기준범위1,기준1,기준범위2,기준2)

기준범위1중에서 기준1 + 기준범위2중에서 기준2인 값의 평균범위내 평균을 구하세요.
=averageifs(평균범위,기준범위1,기준1,기준범위2,기준2)

sumifs

여러 조건을 만족하는 범위의 합계 구하기 =sumifs(합계범위,조건범위1,조건1,조건범위2,조건2)

조건범위1중에서 조건1 + 조건범위2중에서 조건2일때, 합계범위의 총합을 구하세요
=sumifs(합계범위,조건범위1,조건1,조건범위2,조건2)

셀병합 &

=셀&""&셀

profile
Hyejin Beck
데이터기반 스토리텔링을 통해 인사이트를 얻습니다.
이전 포스트

[Excel초격차] 따릉이 대여소별 대여량 분석(averageifs,sumifs,지도시각화)

다음 포스트

[Excel포폴_서울시 2022 상권 분석]_2_데이터 분석과 시각화

0개의 댓글

관련 채용 정보