[홀로서기 #03] SQL EDA, Python으로 쉽게 시각화하기

---

# 홀로서기 기획 연재물은 최근 개인 프로젝트를 진행하면서 겪은 어려움들을 기록한 지극히 개인적인 콘텐츠입니다.

---

SQL로 데이터를 훑어보는 일

  AWS 데이터베이스를 쓰면서도, 대용량 데이터에 빠르게 접근할 수 있는, 파이썬보다 직관적인 언어인 SQL! 최근 필자는 mysql을 통해 자전거 수요량 예측 프로젝트를 수행하고 있다. Bulk insert와 View를 통해서, 대용량 데이터와 친해진 후에는, 쿼리를 써 내려가면서, 데이터가 어떻게 생겼는지 확인하고 있다. 그러던 중에, 매번 Python으로만 EDA를 하던 습관이 남아, 데이터를 그려보고 싶다는 생각을 했다. 아쉽게도,  SQL 환경에서는 간단하게 그리는 것은 테이블 집계(Groupby)정도만 가능한 것을 깨달았다. (물론, 내가 모르는 것일 수 있지만, 내 구글링으로는 그렇다.)

  보통 사람들이 SQL에서 데이터를 훑다가 시각화를 필요로 하는 경우에 Excel로 불러와서 진행하는 것 같았다. 마케터가 많아서 그런건가 싶기도 한데, Excel을 수도 없이 많이 써본 입장에서, (필자는 마케터로 1년 반 정도 일했다), Excel이 시각화에 적합한 툴은 아니라고 생각한다. 오히려 데이터 자체를 바라보거나 직관적으로 정리하는 용도에 유용한 편이지.. 그래서, Python을 연계해서 데이터를 시각화해볼 방법이 없는지 고민했고, 기존에 SQL을 Python에서 불러왔던 것들을 조금 더 발전시켜 시각화가 가능하다고 판단이 되어 시도했다. 다행히(?) 성공적이었다.

 

파이썬에서 본격적으로 쿼리 쓰기

  [홀로서기 #1]에서 잠깐 다루었지만, mysql 라이브러리를 임포트해서 Python 환경에서 SQL을 사용할 수 있다. 나는 AWS 데이터베이스에서 데이터를 가져오고 있으니, 그 기준으로 코드를 작성할 예정이다. 혹시 로컬 환경에서 작업 중인 사람이라면, 이런 게 있구나, 하고 구글링을 통해 쉽게 찾아볼 수 있을 것이라고 생각한다. 아무튼, Python에서 쿼리를 실행해서 데이터를 담고, 그 데이터를 Pandas 데이터프레임으로 만들고, 그 데이터프레임으로 Seaborn 시각화를 진행하는 것이 기본적인 절차이다.

Import "mysql" > Query on Python > To DataFrame > Visualization

 

1) Import "mysql" : Python과 데이터베이스를 연결

# Library
import mysql.connector
import seaborn as sns
from tabulate import tabulate
import matplotlib.pyplot as plt
%matplotlib inline

  기본적으로 SQL을 위한 파이썬 시각화를 위해서는, 이 정도의 라이브러리를 쓰는 편이다. 'tabulate' 모듈은 우리가 터미널에서 sql 쿼리를 실행했을 때 보이는 테이블 형태를 그대로 구현하는 모듈이다. 아래에서 더 자세하게 설명할 예정이다. 시각화는 Seaborn을 사용하지만, Jupyter notebook에 바로 시각화하기 위해서는, plt와 inline을 미리 선언해주고 시작하는 것이 좋다.

 

conn_aws = mysql.connector.connect(
    host = "[host address]",
    port = [port],
    user = "[user name]",
    password = "[password]", 
    database = "[database name]"
)
cur = conn_aws.cursor(buffered=True)

  이제 데이터베이스를 연결한다. 필자는 AWS에 접속하여 데이터를 가져올 예정이다. 위에 []로 표시된 정보를 입력하면 데이터베이스를 파이썬에서 연결할 수 있다. "buffered=True"는 데이터가 대용량일 경우에 넣어줄 것! 

 

2) Query on python : 쿼리를 파이썬에서 실행

 

※ Tip : .sql 파일 만들어두기

- 여기서 필자의 팁을 알려주자면 VS Code에 sql 파일을 따로 만들어서 쿼리만 작성해두길 추천한다.

- 시각화에 진행하는 많은 쿼리들을 그때그때 작성하는 것도 좋지만,

- 터미널에 실행한 쿼리들을 파이썬에 또 다시 작성하려고 하면 시간 낭비다.

- 그리고 프로젝트를 한다면, 하루만에 끝나지 않을테니, 터미널을 닫고 여는 경우도 있을 것 아닌가.

- 기록해둬라. 꼭 기록해둬라. 당신의 시간은 소중하다.

쿼리만 따로 저장해두는 sql 파일. 적어두고 복사-붙여넣기로 여러모로 쓸모 많다.

  Bulk Insert 에서 한번 본 적은 있을거다. sql 작성 -> execute -> fetchall이 파이썬에서의 SELECT를 위한 기본 절차다.

sql = """(
    SELECT stopnum AS 정류소번호
    , count(*) AS 대여기록
    FROM Gangnam_stops
    GROUP BY stopnum
)"""

cur.execute(sql)
result = cur.fetchall()

  이렇게 실행하면 지금, result에 내가 작성한 쿼리에 맞게 데이터들이 담겨있는 상태라고 보면 된다. 이제 이 데이터를 Pandas의 DataFrame 형태로 바꿔주면 된다.

 

3) To DataFrame : Result를 데이터프레임으로 바꿔주기

data = pd.DataFrame(result, columns=['정류소번호','대여건수'])
print(tabulate(data, headers='keys', tablefmt='psql'))

  현재 내 result에는 정류소번호별로 대여기록이 얼마나 되는지 COUNT로 집계된 값이 담겨 있다. 본인이 쓴 쿼리라면, SQL로 실행했을 때, 이 쿼리가 어떻게 실행될지 머릿속으로 그려지는 상태라고 생각한다. 물론 그려지지 않아도 된다. 그냥 저 쿼리를 터미널로 실행해보면 된다.

  쿼리의 결과물인 테이블을 데이터프레임으로 만들었을 때, 컬럼명이 무엇인지 지정해주어야 한다. 나의 경우에는 '정류소번호'와 '대여건수'로 지정했다. 여기서 이 표를 출력해보면 시각화 자료에 더욱 뒷받침이 되는데, SQL 테이블 형태로 출력하게 도와주는 모듈이 tabulate 모듈이다. 내가 만든 DataFrame(data)을 넣어주고, header와 tablefmt을 위와 동일하게 넣으면 된다.

tabulate를 쓰면 이렇게 출력된다.

 

4) Visualization : 시각화하기

sns.barplot(data=data, x='정류소번호', y='대여건수')
plt.show()

Seaborn의 Barplot으로 시각화했다.

  (초)간단 코드로 시각화를 하면 된다. 누구를 보여주는 거라면, plt.figure(figsize=(20, 10))의 형태로 그래프를 더욱 크게 그리면 되겠지만, 간단히 데이터 모양이 어떻게 생겼는지 보고 싶은 거라면, 이 정도면 충분하다.  Barplot 말고도, 이제는 data가 데이터프레임이기 때문에, 그동안 썼던 모든 그래프 형태를 활용해서 시각화할 수 있다. Excel 말고, 제발 파이썬으로 시각화해주세요. 다들..

 

 

---

Photo : Unsplash, "Data Science"

 

SQL로 JOIN, VIEW, Aggrgation 진행해서 데이터 뽑고, 시각화로 전체적인 그림을 그려보고 싶으면, Python을 혼용하면 좋을 것 같다.

특히, 프로젝트면, 누군가에게 데이터에 대해 소개할 일이 있을테니, 더욱 유용한 부분이 될 것이다.

다음 화에서는 프로젝트 후반부를 향해 달려보도록 하겠다. 홀로서기 #3 끝. 

 

---