PER PBR 활용08 : 산업별 PER PBR 분석하기(mysql 과 excel)

데이터 확인

PER PBR 활용07 : 기업별 산업코드 PER PBR 데이터 생성 프로그램 구조에서 우리는 testperpbr이라는 mysql 테이블을 만들었다. mysql 워크벤치에 접속해서 아래와 같이 명령문을 치면 그림 1과 같은 데이터를 확인할 수 있다.

select * from testperpbr

그림1: MYSQL WORKBENCH에서 데이터 조회

아래 그림 2와 같이 mysql워크벤치에서 디스켓 모양을 눌러서 엑셀이나 csv 형식으로 데이터를 저장하면 아래와 같은 파일을 얻을 수 있다. MYSQL에서 query문을 통한 데이터 분석이 힘들다면 아래 업로드한 파일을 통해서 엑셀로 데이터 분석을 해도 된다.

그림2: 데이터 엑셀, CSV로 뽑아내기

perpbrtot.xlsx

0.19MB

 

간혹 엑셀이나 csv 형식으로 데이터를 저장하면 컬럼이나 데이터가 깨져서 저장되는 문제가 발생한다. 이는 encoding 문제로 python에서 encoding을 설정해서 아래와 같은 코드를 짜면 데이터가 깨지는 문제가 없이 엑셀 파일을 만들 수 있다.

 

 

# 라이브러리 import
import pandas as pd
import pymysql
import sqlalchemy as db
pymysql.install_as_MySQLdb()

id = '나의 mysql ID'
pw = '나의 mysql 비번'
dbname = 'sql 스키마 이름'

def readTable(table:str):
	# local host ip
    hostip = '127.0.0.1'
    # mysql local port 번호
    port = '3306'
    # 정보를 이용해서 connection string을 만듦
    db_conn_str = "mysql+mysqldb://" + id + ":" + pw + "@" + hostip + ":" + port +"/"+ dbname
    # create_engine 을 해서 db_conn 인스턴스 생성
    db_conn = db.create_engine(db_conn_str, encoding='utf-8')
    # SQL문 만들기(읽을 테이블명은 함수에서 변수로 입수)
    query = "SELECT * FROM {}".format(table)
    # SQL문과 db_conn 인스턴스로 데이터프레임 읽어와서 df에 저장
    df = pd.read_sql_query(query, db_conn)
    # 생성할 엑셀파일명 미리 설정
    excel_file = "perpbrtot.xlsx"
    # 데이터프레임 df 를 엑셀파일로 변환
    df.to_excel(excel_file, index=False)
    # db 연결 종료
    db_conn.dispose()

if __name__=="__main__":
	# 읽어낼 테이블 명을 "testperpbr"로 넣어서 readTable 함수 실행
    readTable("testperpbr")

 

MYSQL에서 데이터 분석

산업별 PER, PBR

일반 파일로 변환할 때 번거로움, 데이터 관리의 용이성, 데이터 용량이 커도 일정한 속도가 난다는 장점 때문에 보통 대량의 데이터 분석은 sql 서버에서 이뤄진다. MYSQL을 사용하는 경우 mysql워크벤치에서 바로 그림 1과 같이 query문을 날려서 데이터를 확인할 수 있다.

산업별 PER, PBR을 구할 경우에는 아래와 같이 간단한 쿼리문을 통해서 데이터를 뽑아낼 수 있다. 

 

/*PER을 구하는 SQL문*/
/*select 뒤에는 내가 원하는 컬럼 명 입력, 여기에서는 avg함수로 per의 평균을 구함*/
select  산업코드, 산업명, round(avg(per), 4) 평균per
/*from 뒤에는 내가 원하는 테이블 명 입력*/
from testperpbr
/*산업코드로 묶어낸 데이터의 average(평균) per을 구하는 것임*/
group by 산업코드

/*PBR을 구하는 SQL문*/
/*select 뒤에는 내가 원하는 컬럼 명 입력, 여기에서는 avg함수로 pbr의 평균을 구함*/
select  산업코드, 산업명, round(avg(pbr), 4) 평균pbr
/*from 뒤에는 내가 원하는 테이블 명 입력*/
from testperpbr
/*산업코드로 묶어낸 데이터의 average(평균) pbr을 구하는 것임*/
group by 산업코드

 

데이터를 뽑으면 아래 그림 3처럼 화면에서 확인이 가능하다.

그림 3: 산업별 PER, PBR 데이터 뽑아내기

이 데이터를 엑셀로 전환하면 아래와 같다.

per_anal_01.xlsx

0.01MB

 

pbr_anal_01.xlsx

0.01MB

 

주식시장(KOSPI, KOSDAQ, KONEX)을 고려한 산업별 PER, PBR 분석

단순히 산업별 PER, PBR을 구하는 것이 아니라 주식시장별로 구분해서 산업별 PER, PBR을 계산해볼 수도 있다. query 문을 아래와 같이 변형하면 간단하게 산출이 가능하다. 

 

/*PER 구하는 SQL문*/
/*select 뒤에는 내가 원하는 컬럼 명 입력, 여기에서는 avg함수로 per의 평균을 구함*/
select 시장구분, 산업코드, 산업명, round(avg(per),4)
/*from 뒤에는 내가 원하는 테이블 명 입력*/
from testperpbr
/*시장구분, 산업코드를 동시에 그룹으로 묶어냄*/
group by 시장구분, 산업코드
/*데이터 출력시 정렬순서 시장구분 컬럼기준으로 내림차순*/
order by 시장구분 desc

/*PBR 구하는 SQL문*/
/*select 뒤에는 내가 원하는 컬럼 명 입력, 여기에서는 avg함수로 pbr의 평균을 구함*/
select 시장구분, 산업코드, 산업명, round(avg(pbr),4)
/*from 뒤에는 내가 원하는 테이블 명 입력*/
from testperpbr
/*시장구분, 산업코드를 동시에 그룹으로 묶어냄*/
group by 시장구분, 산업코드
/*데이터 출력시 정렬순서 시장구분 컬럼기준으로 내림차순*/
order by 시장구분 desc

 

데이터를 뽑으면 아래 그림 4처럼 확인이 가능하다.

그림4: 주식시장을 고려한 산업별 PER, PBR 분석

이 데이터를 엑셀로 전환하면 아래와 같다.

per_anal_02.xlsx

0.02MB

 

pbr_anal_02.xlsx

0.02MB