전자공시(DART) 데이터 기초분석

import pandas as pd
from datetime import date, timedelta

%matplotlib inline
import matplotlib.pyplot as plt
plt.rcParams['figure.figsize'] = (12, 6)
plt.rcParams["font.family"] = 'NanumBarunGothic'

import getpass
import mysql.connector
from sqlalchemy import create_engine

pwd = getpass.getpass()
engine = create_engine('mysql+mysqlconnector://woosa7:'+pwd+'@localhost/findb', echo=False)

sql = "SELECT count(*) FROM stock_dart"
df = pd.read_sql(sql, con=engine)
df

# 공시 테이블 구조 확인

sql = "DESC stock_dart"
df = pd.read_sql(sql, con=engine)
df

company_name = 'NAVER'

# 최근 공시 10개
sql = "SELECT date, title, link FROM stock_dart WHERE corp_name = '%s' ORDER BY date DESC limit %d" % (company_name, 10)
df = pd.read_sql(sql, con=engine)
df

# 특정 월의 보고서
sql = "SELECT date, title, link FROM stock_dart WHERE corp_name='%s' and date like '%s%%'" % (company_name, "2017-01")
df = pd.read_sql(sql, con=engine)
df

# 2012년 이후 사업보고서
sql = "SELECT date, title, link FROM stock_dart WHERE corp_name='%s' and title like '%%%s%%' and date > '%s'"\
      % (company_name, "사업보고서", "2012")
df = pd.read_sql(sql, con=engine)
df

# 사업보고서 확인 - 링크 클릭하면 보고서 페이지로 이동

for ix, r in df.iterrows():
    print(r['title'])
    print(r['link'])

# 년도별 공시 추세

# 전체 공시
sql = "SELECT date, title FROM stock_dart WHERE corp_name = '%s'" % (company_name)
df = pd.read_sql(sql, con=engine)
df.shape

df.set_index(pd.to_datetime(df['date']), inplace=True)
df['n'] = 1   # 갯수 카운트 하기 위한 값 설정.
df.head()

df_m_sum = df['n'].resample('A').sum().dropna()
df_m_sum.head()

# 보고서 공시
sql = "SELECT date, title FROM stock_dart WHERE corp_name = '%s' and title like '%%%s%%'" % (company_name, "보고서")
df2 = pd.read_sql(sql, con=engine)
df2.shape

df2.set_index(pd.to_datetime(df2['date']), inplace=True)
df2['n'] = 1
df_r_sum = df2['n'].resample('A').sum().dropna()
df_r_sum.head()

# plotting

df_m_sum.plot()
df_r_sum.plot()
plt.legend(["전체 공시","보고서"])