ㅅㅇ
실습 _ 맛집 정보 DB 만들기 _ mysql, pymysql, python, pandas 본문
지금까지 캠프에서 배웠던 내용을 복습도 할 겸 구상부터 결과물까지 혼자 구현 해보고 싶어서
간단한 실습을 해보기로 했다.
지금까지 내가 방문했던 음식점에 대한 데이터가 담긴 DB 생성, 데이터 분석을 진행할 것이다.
일단, 데이터 처리에서 내가 지금 배웠던 내용으로 할 수 있는 것은 크게 세가지이다.
첫째, MySQL 내에서 데이터 처리
둘째, Pymysql 로 db 불러올 때 원하는 데이터 추출
셋째, 일단 Pymysql로 싹 다 불러와서 판다스로 데이터 처리 해주기.
일단, 오늘 실습은 지금까지 배웠던 내용에 대한 복습이 위주이기에 첫번째, 세번째 두 방법을 모두 해보았다.
전체적인 실습 계획이다.
- 다이어그램 구상 – 구글 draw
- DB 생성 – MySQL
- ERD 생성 – MySQL workbench
- 데이터 수집, 삽입 – MySQL
- 데이터 조회, 처리 – MySQL workbench
- 가상환경 만들기 - conda
- pymysql DB 연동 - conda jupyter
- 데이터 전처리, 추출, CSV 파일 저장 – Pandas
1. 다이어그램 구상 – 구글 drawio
대략적 틀은 구글 drawio 로 구상하였다.
저번 팀플 플로우 차트 때부터 사용한 툴인데 상당히 편하다.
구상한 내용이 직관적으로 잘 보이고 사용법도 쉽다.
아이템 구상, 계획할 때 탁월한 툴인 것 같다.
근데 DB를 만드는 과정에서 컬럼에 대한 것은 많이 바꾸긴 했다 .
< 테이블 >
음식점 정보 Restaurant_INFO
주소 정보 Address
리뷰 정보 Review
전 메뉴 정보 Menu_Full
메인 메뉴 정보 및 점수 Menu_Main
음료 구체적 정보 Beverage
2. DB 생성, 테이블 생성 – MySQL
분명 일이주 전에 배웠던 MySQL 인데 기억이 안 나는 건 왜일까 ㅎ
필기 해두었던 것을 참고해서 작성해보았다.
DB 생성, 테이블 생성을 진행하였다.
create database food_diary;
use food_diary;
drop table if exists Address;
drop table if exists Menu_Main;
drop table if exists Review;
drop table if exists Beverage;
drop table if exists Menu_Full;
drop table if exists Restaurant_INFO;
show tables;
/* 음식점 정보 */
create table Restaurant_INFO(
Rest_ID int PRIMARY KEY, /* 음식점_ID */
Name VARCHAR(20) NOT NULL,
Category VARCHAR(20) NOT NULL);
/* 주소 정보 */
create table Address(
Address_ID int PRIMARY KEY, /* 주소_ID FK*/
Rest_ID int NOT NULL, /*음식점_ID FK*/
Country VARCHAR(20) NOT NULL,
City VARCHAR(20),
Address VARCHAR(40),
Phone_Number VARCHAR(15),
CONSTRAINT fk_Address_Restaurant FOREIGN KEY (Rest_ID) REFERENCES Restaurant_INFO(Rest_ID) on delete cascade
);
/* 리뷰 정보 */
create table Review(
Review_ID VARCHAR(15) PRIMARY KEY, /* 리뷰_ID (PK) */
Rest_ID int NOT NULL, /*음식점_ID FK */
Grade int, /* 평점 : 10 점 기준 */
Num_Visits int NOT NULL, /* 방문 횟수 : 안 갔으면 0 표시 */
CONSTRAINT fk_Review_Restaurant FOREIGN KEY (Rest_ID) REFERENCES Restaurant_INFO(Rest_ID) on delete cascade
);
/* 전 메뉴 정보 */
create table Menu_Full(
Menu_Full_ID int PRIMARY KEY,
Menu_name VARCHAR(40) NOT NULL,
Type VARCHAR(40) NOT NULL,
Price int,
Rest_ID int NOT NULL,
CONSTRAINT fk_Menu_Restaurant FOREIGN KEY (Rest_ID) REFERENCES Restaurant_INFO(Rest_ID) on delete cascade
);
/* 메인 메뉴 정보 및 점수 */
create table Menu_Main(
Menu_Main_ID int PRIMARY KEY,
Menu_Full_ID int NOT NULL,
Grade int,
CONSTRAINT fk_MENU_MAIN_FULL FOREIGN KEY (Menu_Full_ID) REFERENCES Menu_Full(Menu_Full_ID) on delete cascade
);
/* 음료 구체적 정보 */
create table Beverage(
Beverage_ID int PRIMARY KEY,
Menu_Full_ID int NOT NULL,
Type VARCHAR(30),
CONSTRAINT fk_MENU_Beverage FOREIGN KEY (Menu_Full_ID) REFERENCES Menu_Full(Menu_Full_ID) on delete cascade
);
3. ERD 생성 – MySQL workbench
생성한 DB의 ERD 를 만들어보았다. 이는 workbench로 쉽게 구현할 수 있었다.
4. 데이터 수집, 삽입 – MySQL
이제 값 삽입을 진행하였다. 이전에 카카오지도에 저장해둔 가게 정보를 바탕으로 넣었다.
넣는데 손 아파 죽는 줄 ㅎ,, 잘 못 넣어서 고치고 또 고치고 했다,,
많은 데이터를 직접 삽입하는 건 진짜 할 짓이 못되는 듯 ㅎㅎ
괜히 욕심 부려서 테이블과 컬럼을 많이 만들어서 ,, 처음인데 작게 만들 것 그랬다.
그래도 다 하니깐 뿌듯했다 ㅎㅎ
아래 코드는 Restaurant_INFO 테이블의 데이터 삽입 중 일부다.
/* 음식점 정보 */
INSERT INTO Restaurant_INFO VALUES(100, "함지곱창전문", "고기");
INSERT INTO Restaurant_INFO VALUES(101, "낙성곱창", "고기");
INSERT INTO Restaurant_INFO VALUES(102, "제일곱창", "고기");
INSERT INTO Restaurant_INFO VALUES(103, "전주전집", "술집");
INSERT INTO Restaurant_INFO VALUES(104, "올레요회센터", "해산물");
INSERT INTO Restaurant_INFO VALUES(105, "이수회관", "해산물");
INSERT INTO Restaurant_INFO VALUES(106, "와촌", "한식");
INSERT INTO Restaurant_INFO VALUES(107, "떡도리탕", "한식");
5. Workbench 데이터 조회
아래는 음식점을 category 별로 나눠 그 범주 내 리뷰평점의 평균을 매겨본 코드이다.
select Category, avg(Grade)
from restaurant_info r join review re on r.Rest_ID = re.Rest_ID
group by Category;
이렇게 평점의 평균을 매겨보았다. 그와중에 술집이 제일 높네 ^~^,,
양식 안 좋아하는 것도 티나고 ㅎ
사실 데이터 삽입이 너무 힘들어 20개의 데이터로만 진행한거라 정확성이 높다 판단하긴 어렵지만
나의 음식 특성이 충분히 들어나는 것 같긴 하다 ㅎㅎ
시간 날 때마다 데이터 넣어 어느정도 쌓이면
나의 취향 맛집 분석를 목적으로 해봐야겠다.
그리고 또, Address 지역구 별 등록한 음식점 갯수를 조회해보았다.
select Address, count(Address)
from restaurant_info r join address a on r.Rest_ID = a.Rest_ID
group by Address;
등록한 게 왜 다 짧게라도 거주했던 곳들인지 ㅎ
난 동네 내에서 노는 걸 좋아하나보다.. 몰랐네..
6. 가상환경 만들기
쥬피터노트북에서 판다스를 이용해 데이터를 분석하기 전에 가상환경을 만들어 주었다.
7. pymysql DB 연동 - conda jupyter
데이터 전처리, 추출, CSV 파일 저장 – Pandas
mysql에서 바로 데이터 처리를 하여 csv 파일로 저장하면 되지만,
복습이 목적이니 만들어준 db의 데이터 모두를 뽑아와 판다스로 조회, 처리하려고 한다.
쥬피터노트북 내에 pymysql 패키지를 깔고 db와 연동하여 db의 모든 테이블을 select 해 저장하였다.
import pymysql
import pandas as pd
# DB 연동
with pymysql.connect(host='-', port = -, user = '-', password = '-', db = 'food_diary', charset = 'utf8') as connection:
with connection.cursor() as cursor:
cursor.execute("select * from Restaurant_INFO")
result = cursor.fetchall()
cursor.execute("select * from Review")
result1 = cursor.fetchall()
cursor.execute("select * from Address")
result2 = cursor.fetchall()
# 데이터프레임 생성
Rest_df = pd.DataFrame(result, columns = ["Rest_ID", "name", "category"])
Rev_df = pd.DataFrame(result1, columns = ["Review_ID", "Rest_ID", "Grade", "Num_Visits"])
Addr_df = pd.DataFrame(result2, columns = ["Address_ID", "Rest_ID", "Country", "City", "Address", "Phone_Number"])
Rest_df.set_index("Rest_ID", inplace = True)
Rev_df.set_index("Review_ID", inplace = True)
Addr_df.set_index("Address_ID", inplace = True)
# csv 파일로 저장
Rest_df.to_csv('Restaurant_INFO.csv')
Rev_df.to_csv('Review.csv')
Addr_df.to_csv('Address.csv')
print("end")
간단하게 조회, 추출해보았다.
주 조회는 가게 정보와 리뷰 정보를 다루었으며, 이를 위해 두 데이터프레임을 합쳐주었다.
Rev_col = Rev_df.columns
Rev_col
# Rest_df 와 # Rev_df 테이블 합치기
for i in Rev_col:
print(Rev_df[i])
print('-------')
Rest_df[i] = list(Rev_df[i]) # list로 바꿔서 넣어줘야 함. Series 안 넣어짐.
# 카테고리 별 리뷰평점의 평균과 방문횟수의 평균
# 평점 평균 기준 내림차순
Rest_df.groupby("category")[["Grade", "Num_Visits"]].mean().sort_values("Grade", ascending = False)
# 카테고리 별 리뷰평점 평균이 3.3 이상인 음식점 조회
# 이름, 카테고리, 평점, 방문횟수만
코드 가독성 떨어져서 변수에 담아서 구현
R1 = Rest_df.groupby("category").filter(lambda x : x["Grade"].mean() > 3.3 )
R1[["name", "category", "Grade", "Num_Visits"]]
# Rest_df.groupby("category").filter(lambda x :Rest_df.groupby("category").filter(lambda x : x["Grade"].mean() > 3.3 )[["name", "category", "Grade", "Num_Visits"]] x["Grade"].mean() > 3.3 )
# 평점 4 이상, 방문횟수 4회 이상 음식점 조회.
# 이름, 카테고리, 평점, 방문횟수만
# 이름이 index
# 정렬 이름 오름차순
코드 가독성 떨어져서 변수에 담아서 구현
R1 = Rest_df.loc[(Rest_df["Grade"] >= 4 ) & (Rest_df["Num_Visits"] >=4), ["name", "category", "Grade", "Num_Visits"] ]
R1 = R1.set_index("name")
R1.sort_values("name")
# Rest_df.loc[(Rest_df["Grade"] >= 4 ) & (Rest_df["Num_Visits"] >=4), ["name", "category", "Grade", "Num_Visits"] ].set_index("name").sort_values("name")
일단, 데이터 전처리 없이 모든 데이터프레임을 csv 파일로 내보고
배웠던 데이터 조회, 정렬, 집계, 그룹화, 일괄처리까지 적용하여 몇 가지 데이터 처리를 진행해보았다.
사실 데이터 넣는 게 너무 오래걸려서.. 오히려 데이터 처리 실습을 많이 하지 못했다 ㅠㅠ..
그와중에 기억 잘 안나서 필기 찾아보면서 진행 ,,
돌아서면 까먹으니 계속 실습 돌려줘야겠다..
그리고 원하는 바 없이 데이터만 만드니
내가 이 데이터를 가지고 어떤 분석 처리를 해야 할까가 떠오르지 않았다.
다음엔 db 설계 전부터 데이터 처리 목적을 명확히 두고 실습을 진행해봐야겠다.
또, 데이터 수를 많이 하진 못하더라도 복잡한 관계성을 가진 db 테이블을 한번 만들어도 봐야겠다.
아직은 배운 내용이 적어 데이터 분석 외 db를 활용해 내가 할 수 있는 실습이 적은데
새로운 내용을 배우는 대로 db를 다양하게 써먹을 수 있는 실습을 해봐야겠다.
오늘은 Mysql 설계 위주로 진행해보았으니, 다음 실습은 기존의 빅데이터 자료를 가지고 분석 실습을 진행해봐야겠다.
확실히 내가 직접 설계한 db로 데이터 분석을 하기엔 무리가 있는 것 같다.
공공 데이터 사이트 내 데이터로 진행해봐야겠다.