3. 공공데이터포털

3. 공공데이터포털의 OpenAPI 활용

3.1 공공데이터포털

3.1.1 공공데이터포털 데이터 검색

1. 로그인 및 회원가입 진행

1. 검색창 - ‘원하는 자료’ 검색

1. 검색 결과 - 오픈API 클릭

1. ‘공정거래위원회_가맹정보_업종별 가맹본부 변동현황 제공 서비스’ 클릭

3.1.2 공공데이터포털 데이터 탐색

• 데이터 조회하기

• OpenAPI 정보

• 상세기능 정보

3.1.3 OpenAPI 활용 신청

1. 검색창 - ‘원하는 자료’ 검색

1. 검색 결과 - 오픈API 클릭

1. 데이터 활용신청 클릭

1. 활용목적 선택 - 동의 체크 - 활용신청 클릭

1. 마이페이지 - 데이터 활용 - OpenAPI - 활용신청 현황

3.2 데이터 활용

3.2.1 URL과 매개변수

URL 확인

• URL
→ https:// + Base URL + 해당 GET URL

매개변수 확인

3.2.2 JSON 데이터 크롤링

라이브러리 호출

import json
import requests
import pandas as pd

데이터 불러오기

1. 공공데이터포털 OpenAPI - ServiceKey 입력

key = 'DecodingKey 입력'

1. URL, 매개변수 입력 - requests 요청

url = 'https://apis.data.go.kr/1130000/FftcIndutyJnghdqrtrsStatsService/getIndutyJnghdqrtrsOutStats?'
params = {'serviceKey' : key, 'pageNo' : '1', 'numOfRows' : '15', 'resultType' : 'json', 'yr' : '2021' }

response = requests.get(url, params=params)

1. 요청받은 데이터 확인

content = response.text
content

{"resultCode":"00","resultMsg":"NORMAL SERVICE","numOfRows":"15","pageNo":"1","totalCount":15,"items":[{"yr":"2021","indutyMlsfcNm":"기타 외국식","jnghdqrtrsIncCnt":60,"jnghdqrtrsIncRt":71.43,"newRgsJnghdqrtrsCnt":76,"newRgsJnghdqrtrsRt":47.5,"rgsRtrcnJnghdqrtrsCnt":16,"rgsRtrcnJnghdqrtrsRt":10,"jnghdqrtrsAvrgBsnPdVal":"5년 7개월"},{"yr":"2021","indutyMlsfcNm":"기타 외식","jnghdqrtrsIncCnt":407,"jnghdqrtrsIncRt":57.57,"newRgsJnghdqrtrsCnt":533,"newRgsJnghdqrtrsRt":43.62,"rgsRtrcnJnghdqrtrsCnt":108,"rgsRtrcnJnghdqrtrsRt":8.84,"jnghdqrtrsAvrgBsnPdVal":"6년 7개월"},{"yr":"2021","indutyMlsfcNm":"분식","jnghdqrtrsIncCnt":230,"jnghdqrtrsIncRt":65.16,"newRgsJnghdqrtrsCnt":308,"newRgsJnghdqrtrsRt":48.73,"rgsRtrcnJnghdqrtrsCnt":49,"rgsRtrcnJnghdqrtrsRt":7.75,"jnghdqrtrsAvrgBsnPdVal":"6년 1개월"},{"yr":"2021","indutyMlsfcNm":"서양식","jnghdqrtrsIncCnt":127,"jnghdqrtrsIncRt":115.45,"newRgsJnghdqrtrsCnt":150,"newRgsJnghdqrtrsRt":55.76,"rgsRtrcnJnghdqrtrsCnt":32,"rgsRtrcnJnghdqrtrsRt":11.9,"jnghdqrtrsAvrgBsnPdVal":"5년 6개월"},{"yr":"2021","indutyMlsfcNm":"아이스크림/빙수 ","jnghdqrtrsIncCnt":24,"jnghdqrtrsIncRt":80,"newRgsJnghdqrtrsCnt":33,"newRgsJnghdqrtrsRt":54.1,"rgsRtrcnJnghdqrtrsCnt":7,"rgsRtrcnJnghdqrtrsRt":11.48,"jnghdqrtrsAvrgBsnPdVal":"7년 7개월"},{"yr":"2021","indutyMlsfcNm":"음료 (커피 외)","jnghdqrtrsIncCnt":32,"jnghdqrtrsIncRt":53.33,"newRgsJnghdqrtrsCnt":43,"newRgsJnghdqrtrsRt":43,"rgsRtrcnJnghdqrtrsCnt":8,"rgsRtrcnJnghdqrtrsRt":8,"jnghdqrtrsAvrgBsnPdVal":"6년 4개월"},{"yr":"2021","indutyMlsfcNm":"일식","jnghdqrtrsIncCnt":166,"jnghdqrtrsIncRt":99.4,"newRgsJnghdqrtrsCnt":200,"newRgsJnghdqrtrsRt":53.76,"rgsRtrcnJnghdqrtrsCnt":39,"rgsRtrcnJnghdqrtrsRt":10.48,"jnghdqrtrsAvrgBsnPdVal":"5년 6개월"},{"yr":"2021","indutyMlsfcNm":"제과제빵","jnghdqrtrsIncCnt":81,"jnghdqrtrsIncRt":65.32,"newRgsJnghdqrtrsCnt":112,"newRgsJnghdqrtrsRt":49.34,"rgsRtrcnJnghdqrtrsCnt":22,"rgsRtrcnJnghdqrtrsRt":9.69,"jnghdqrtrsAvrgBsnPdVal":"7년 2개월"},{"yr":"2021","indutyMlsfcNm":"주점","jnghdqrtrsIncCnt":103,"jnghdqrtrsIncRt":46.61,"newRgsJnghdqrtrsCnt":154,"newRgsJnghdqrtrsRt":42.78,"rgsRtrcnJnghdqrtrsCnt":36,"rgsRtrcnJnghdqrtrsRt":10,"jnghdqrtrsAvrgBsnPdVal":"6년 11개월"},{"yr":"2021","indutyMlsfcNm":"중식","jnghdqrtrsIncCnt":130,"jnghdqrtrsIncRt":139.78,"newRgsJnghdqrtrsCnt":150,"newRgsJnghdqrtrsRt":62.24,"rgsRtrcnJnghdqrtrsCnt":18,"rgsRtrcnJnghdqrtrsRt":7.47,"jnghdqrtrsAvrgBsnPdVal":"4년 10개월"},{"yr":"2021","indutyMlsfcNm":"치킨","jnghdqrtrsIncCnt":218,"jnghdqrtrsIncRt":56.77,"newRgsJnghdqrtrsCnt":292,"newRgsJnghdqrtrsRt":44.44,"rgsRtrcnJnghdqrtrsCnt":55,"rgsRtrcnJnghdqrtrsRt":8.37,"jnghdqrtrsAvrgBsnPdVal":"7년 4개월"},{"yr":"2021","indutyMlsfcNm":"커피","jnghdqrtrsIncCnt":344,"jnghdqrtrsIncRt":106.5,"newRgsJnghdqrtrsCnt":399,"newRgsJnghdqrtrsRt":55.26,"rgsRtrcnJnghdqrtrsCnt":55,"rgsRtrcnJnghdqrtrsRt":7.62,"jnghdqrtrsAvrgBsnPdVal":"5년 10개월"},{"yr":"2021","indutyMlsfcNm":"패스트푸드","jnghdqrtrsIncCnt":86,"jnghdqrtrsIncRt":122.86,"newRgsJnghdqrtrsCnt":103,"newRgsJnghdqrtrsRt":59.88,"rgsRtrcnJnghdqrtrsCnt":16,"rgsRtrcnJnghdqrtrsRt":9.3,"jnghdqrtrsAvrgBsnPdVal":"7년 1개월"},{"yr":"2021","indutyMlsfcNm":"피자","jnghdqrtrsIncCnt":87,"jnghdqrtrsIncRt":64.44,"newRgsJnghdqrtrsCnt":112,"newRgsJnghdqrtrsRt":46.67,"rgsRtrcnJnghdqrtrsCnt":18,"rgsRtrcnJnghdqrtrsRt":7.5,"jnghdqrtrsAvrgBsnPdVal":"6년 7개월"},{"yr":"2021","indutyMlsfcNm":"한식","jnghdqrtrsIncCnt":815,"jnghdqrtrsIncRt":65.83,"newRgsJnghdqrtrsCnt":1160,"newRgsJnghdqrtrsRt":51.21,"rgsRtrcnJnghdqrtrsCnt":212,"rgsRtrcnJnghdqrtrsRt":9.36,"jnghdqrtrsAvrgBsnPdVal":"5년 7개월"}]}

데이터 변환

1. JSON 형식의 문자열을 Python 객체로 변환

json_load = json.loads(contents)
json_load

{'resultCode': '00', 'resultMsg': 'NORMAL SERVICE', 'numOfRows': '15', 'pageNo': '1', 'totalCount': 15, 'items': [{'yr': '2021', 'indutyMlsfcNm': '기타 외국식', 'jnghdqrtrsIncCnt': 60, 'jnghdqrtrsIncRt': 71.43, 'newRgsJnghdqrtrsCnt': 76, 'newRgsJnghdqrtrsRt': 47.5, 'rgsRtrcnJnghdqrtrsCnt': 16, 'rgsRtrcnJnghdqrtrsRt': 10, 'jnghdqrtrsAvrgBsnPdVal': '5년 7개월'}, {'yr': '2021', 'indutyMlsfcNm': '기타 외식', 'jnghdqrtrsIncCnt': 407, 'jnghdqrtrsIncRt': 57.57, 'newRgsJnghdqrtrsCnt': 533, 'newRgsJnghdqrtrsRt': 43.62, 'rgsRtrcnJnghdqrtrsCnt': 108, 'rgsRtrcnJnghdqrtrsRt': 8.84, 'jnghdqrtrsAvrgBsnPdVal': '6년 7개월'}, {'yr': '2021', 'indutyMlsfcNm': '분식', 'jnghdqrtrsIncCnt': 230, 'jnghdqrtrsIncRt': 65.16, 'newRgsJnghdqrtrsCnt': 308, 'newRgsJnghdqrtrsRt': 48.73, 'rgsRtrcnJnghdqrtrsCnt': 49, 'rgsRtrcnJnghdqrtrsRt': 7.75, 'jnghdqrtrsAvrgBsnPdVal': '6년 1개월'}, {'yr': '2021', 'indutyMlsfcNm': '서양식', 'jnghdqrtrsIncCnt': 127, 'jnghdqrtrsIncRt': 115.45, 'newRgsJnghdqrtrsCnt': 150, 'newRgsJnghdqrtrsRt': 55.76, 'rgsRtrcnJnghdqrtrsCnt': 32, 'rgsRtrcnJnghdqrtrsRt': 11.9, 'jnghdqrtrsAvrgBsnPdVal': '5년 6개월'}, {'yr': '2021', 'indutyMlsfcNm': '아이스크림/빙수 ', 'jnghdqrtrsIncCnt': 24, 'jnghdqrtrsIncRt': 80, 'newRgsJnghdqrtrsCnt': 33, 'newRgsJnghdqrtrsRt': 54.1, 'rgsRtrcnJnghdqrtrsCnt': 7, 'rgsRtrcnJnghdqrtrsRt': 11.48, 'jnghdqrtrsAvrgBsnPdVal': '7년 7개월'}, {'yr': '2021', 'indutyMlsfcNm': '음료 (커피 외)', 'jnghdqrtrsIncCnt': 32, 'jnghdqrtrsIncRt': 53.33, 'newRgsJnghdqrtrsCnt': 43, 'newRgsJnghdqrtrsRt': 43, 'rgsRtrcnJnghdqrtrsCnt': 8, 'rgsRtrcnJnghdqrtrsRt': 8, 'jnghdqrtrsAvrgBsnPdVal': '6년 4개월'}, {'yr': '2021', 'indutyMlsfcNm': '일식', 'jnghdqrtrsIncCnt': 166, 'jnghdqrtrsIncRt': 99.4, 'newRgsJnghdqrtrsCnt': 200, 'newRgsJnghdqrtrsRt': 53.76, 'rgsRtrcnJnghdqrtrsCnt': 39, 'rgsRtrcnJnghdqrtrsRt': 10.48, 'jnghdqrtrsAvrgBsnPdVal': '5년 6개월'}, {'yr': '2021', 'indutyMlsfcNm': '제과제빵', 'jnghdqrtrsIncCnt': 81, 'jnghdqrtrsIncRt': 65.32, 'newRgsJnghdqrtrsCnt': 112, 'newRgsJnghdqrtrsRt': 49.34, 'rgsRtrcnJnghdqrtrsCnt': 22, 'rgsRtrcnJnghdqrtrsRt': 9.69, 'jnghdqrtrsAvrgBsnPdVal': '7년 2개월'}, {'yr': '2021', 'indutyMlsfcNm': '주점', 'jnghdqrtrsIncCnt': 103, 'jnghdqrtrsIncRt': 46.61, 'newRgsJnghdqrtrsCnt': 154, 'newRgsJnghdqrtrsRt': 42.78, 'rgsRtrcnJnghdqrtrsCnt': 36, 'rgsRtrcnJnghdqrtrsRt': 10, 'jnghdqrtrsAvrgBsnPdVal': '6년 11개월'}, {'yr': '2021', 'indutyMlsfcNm': '중식', 'jnghdqrtrsIncCnt': 130, 'jnghdqrtrsIncRt': 139.78, 'newRgsJnghdqrtrsCnt': 150, 'newRgsJnghdqrtrsRt': 62.24, 'rgsRtrcnJnghdqrtrsCnt': 18, 'rgsRtrcnJnghdqrtrsRt': 7.47, 'jnghdqrtrsAvrgBsnPdVal': '4년 10개월'}, {'yr': '2021', 'indutyMlsfcNm': '치킨', 'jnghdqrtrsIncCnt': 218, 'jnghdqrtrsIncRt': 56.77, 'newRgsJnghdqrtrsCnt': 292, 'newRgsJnghdqrtrsRt': 44.44, 'rgsRtrcnJnghdqrtrsCnt': 55, 'rgsRtrcnJnghdqrtrsRt': 8.37, 'jnghdqrtrsAvrgBsnPdVal': '7년 4개월'}, {'yr': '2021', 'indutyMlsfcNm': '커피', 'jnghdqrtrsIncCnt': 344, 'jnghdqrtrsIncRt': 106.5, 'newRgsJnghdqrtrsCnt': 399, 'newRgsJnghdqrtrsRt': 55.26, 'rgsRtrcnJnghdqrtrsCnt': 55, 'rgsRtrcnJnghdqrtrsRt': 7.62, 'jnghdqrtrsAvrgBsnPdVal': '5년 10개월'}, {'yr': '2021', 'indutyMlsfcNm': '패스트푸드', 'jnghdqrtrsIncCnt': 86, 'jnghdqrtrsIncRt': 122.86, 'newRgsJnghdqrtrsCnt': 103, 'newRgsJnghdqrtrsRt': 59.88, 'rgsRtrcnJnghdqrtrsCnt': 16, 'rgsRtrcnJnghdqrtrsRt': 9.3, 'jnghdqrtrsAvrgBsnPdVal': '7년 1개월'}, {'yr': '2021', 'indutyMlsfcNm': '피자', 'jnghdqrtrsIncCnt': 87, 'jnghdqrtrsIncRt': 64.44, 'newRgsJnghdqrtrsCnt': 112, 'newRgsJnghdqrtrsRt': 46.67, 'rgsRtrcnJnghdqrtrsCnt': 18, 'rgsRtrcnJnghdqrtrsRt': 7.5, 'jnghdqrtrsAvrgBsnPdVal': '6년 7개월'}, {'yr': '2021', 'indutyMlsfcNm': '한식', 'jnghdqrtrsIncCnt': 815, 'jnghdqrtrsIncRt': 65.83, 'newRgsJnghdqrtrsCnt': 1160, 'newRgsJnghdqrtrsRt': 51.21, 'rgsRtrcnJnghdqrtrsCnt': 212, 'rgsRtrcnJnghdqrtrsRt': 9.36, 'jnghdqrtrsAvrgBsnPdVal': '5년 7개월'}]}

print(type(json_load))

<class 'dict'>

1. 필요한 데이터 추출해 데이터프레임 변환

df = pd.DataFrame()

if json_data['totalCount'] != 0:
    items = json_data['items']
    df = pd.DataFrame(items)
df

3.2.3 XML 데이터 크롤링

라이브러리 호출

import requests
import bs4
import pandas as pd

데이터 불러오기

1. 공공데이터포털 OpenAPI - ServiceKey 입력

key = 'DecodingKey 입력'

1. URL, 매개변수 입력 - requests 요청

url = 'https://apis.data.go.kr/1130000/FftcIndutyJnghdqrtrsStatsService/getIndutyJnghdqrtrsOutStats?'
params = {'serviceKey' : key, 'pageNo' : '1', 'numOfRows' : '15', 'resultType' : 'xml', 'yr' : '2022' }

response = requests.get(url, params=params)

1. 요청받은 데이터 확인

content = response.text
content

데이터 변환

1. XML 데이터 확인

1. 필요한 데이터 추출

xml_result = bs4.BeautifulSoup(content,'xml')
result = xml_result.findAll('item')
result

1. column, value 추출

• column 추출

column_list = []  # for문을 통해 추출한 column을 담을 빈 리스트
for column in result[0].find_all():
    column_list.append(column.name)

column_list

• value 추출

data_list = []
value_list = []
for i in result:
    for j in i.find_all():
        data_list.append(j.text)
    value_list.append(data_list)
    data_list = []

value_list

1. 데이터프레임 변환

df = pd.DataFrame(value_list, columns = column_list)
df

3.3.4 데이터 전처리 및 저장

데이터 전처리

• column명 변경

df.columns = ['기준년도', '업종_중분류명', '가맹본부_증가수', '가맹본부_증가율', '신규등록_가맹본부수', '신규등록_가맹본부율', '등록취소_가맹본부수', '등록취소_가맹본부율', '가맹본부_평균영업기간']
df

• 불필요한 column 삭제

df = df.drop('가맹본부_증가수', axis=1)
df = df.drop('가맹본부_증가율', axis=1)
df = df.drop('가맹본부_평균영업기간', axis=1)

df

• 데이터 형변환

df.dtypes

df['기준년도'] = df['기준년도'].astype(int)
df['신규등록_가맹본부수'] = df['신규등록_가맹본부수'].astype(int)
df['신규등록_가맹본부율'] = df['신규등록_가맹본부율'].astype(float)
df['등록취소_가맹본부수'] = df['등록취소_가맹본부수'].astype(int)
df['등록취소_가맹본부율'] = df['등록취소_가맹본부율'].astype(float)

df.dtypes

• 정렬

df.sort_values(by='등록취소_가맹본부수', ascending = True)

df.sort_values(by='신규등록_가맹본부율', ascending = False)

df = df.sort_values(by='신규등록_가맹본부율', ascending = False)

데이터 저장

df.to_csv('외식별_가맹정보.csv', index=False)

3.3 MySQL

3.3.1. MySQL 연동

1. 라이브러리호출

import pymysql

1. MySQL 연결 및 DB 생성

# MySQL 연결
conn = pymysql.connect(host = '호스트명',
                       port = 포트번호,
                       user = '유저명',
                       password = 'password',
                       charset='utf8')

# DB 생성
with conn:
    with conn.cursor() as cur:
        cur.execute('CREATE DATABASE test_db')
        conn.commit()

1. 생성한 DB 연결

conn = pymysql.connect(host = '호스트명',
                       port = 포트번호,
                       user = '유저명',
                       password = 'password',
											 db = 'test_db',
                       charset='utf8')

1. Table 생성

sql = '''create table category (
            category_name VARCHAR(50) primary key,
            year int,
            new_store int,
            new_store_per float,    
            cancel_store int,
            cancel_store_per float);'''

with conn.cursor() as cur:
    cur.execute(sql)
    conn.commit()

1. 생성된 Table 확인

sql = 'SHOW TABLES'

with conn.cursor() as cur:
    cur.execute(sql)
    for t in cur:
            print(t)

1. 데이터 저장

with conn.cursor() as cursor:
    for _, row in df.iterrows():
        sql = '''
        INSERT INTO category (category_name, year, new_store, new_store_per,
															cancel_store, cancel_store_per)
        VALUES (%s, %s, %s, %s, %s, %s)
        '''

        values = (
            row['업종_중분류명'], row['기준년도'], row['신규등록_가맹본부수'],
						row['신규등록_가맹본부율'], row['등록취소_가맹본부수'],
						row['등록취소_가맹본부율']
        )

        cursor.execute(sql, values)
        
    # 커밋
    conn.commit()

1. DB 연결 종료

conn.close()

3.3.2. MySQL 데이터 조회

1. DB 연결

conn = pymysql.connect(host = '호스트명',
                       port = 포트번호,
                       user = '유저명',
                       password = 'password',
											 db = 'DB명'
                       charset='utf8')

1. 조건문 실행

# query
query = '''SELECT *
            FROM category
            WHERE new_store >= 30
            ORDER BY new_store_per ;'''

# query 실행
with conn.cursor() as cursor:
    cursor.execute(query)
    result = cursor.fetchall()

# 결과 출력
print(f'조건에 일치하는 카테고리: 총 {len(result)}개')
result_df = pd.DataFrame(result, columns=['category_name', 'year', 'new_store', 'new_store_per', 'cancel_store', 'cancel_store_per'])
result_df

# query
query = '''SELECT *
            FROM category
            WHERE cancel_store_per >= 10
            ORDER BY cancel_store DESC
						LIMIT 5 ;'''

# query 실행
with conn.cursor() as cursor:
    cursor.execute(query)
    result = cursor.fetchall()

# 결과 출력
print(f'조건에 일치하는 카테고리: 총 {len(result)}개')
result_df = pd.DataFrame(result, columns=['category_name', 'year', 'new_store', 'new_store_per', 'cancel_store', 'cancel_store_per'])
result_df

1. DB 연결 종료

conn.close()