[Py2.7] 엑셀 및 SQL, API를 활용한 데이터 분석 사례 - dBRang

 

 

/*
-- Title : [Py2.7] IPython을 통한 특허-기업 데이터 분석 사례 - dBRang
-- Reference : dbrang.com
-- Key word : 엑셀 sql api excel pandas dataframe beautifulsoup numpy pymssql 특허 patent kipris xml
                  merge excelwriter to_excel pivot_table
*/

 

-- Figure

 

-- Script

# -*- coding: utf-8 -*-
 
import os, sys
import pandas as pd
from bs4 import BeautifulSoup
import urllib2
import pymssql
import numpy as np
 
# ********************************************
# -- user_company 엑셀 호출
#    df_user_company
# ********************************************
print("-" * 100 + "[1]") # ----- #
 
atcompanyfile = "c:\\samples\\wops\\user_company.xlsx"
df_user_company = pd.read_excel(atcompanyfile, sheetname="Sheet1")
df_user_company.head(5)
 
 
# ********************************************
# -- user_patent 엑셀 호출
#    df_user_patent
# ********************************************
print("-" * 100 + "[2]") # ----- #
 
atpatent1file = "c:\\samples\\wops\\user_patent.xlsx"
df_user_patent = pd.read_excel(atpatent1file, sheetname="Sheet1")
df_user_patent.head(5)
 
 
# ********************************************
# -- company DF 통계 및 컬럼 타입 확인
# ********************************************
print("-" * 100 + "[3]") # ----- #
 
df_user_company.describe()
df_user_company.dtypes
 
 
# ********************************************
# -- user_patent에서 skey 기준으로 행정 정보 가져오기
#    df_wops_admininfo
# ********************************************
print("-" * 100 + "[4]") # ----- #
 
df_wops_admininfo = pd.DataFrame(columns=("skey","curr_assn","curr_rept_assn","curr_rept_cd","expired_dt","lgl_stts"))
 
conn = pymssql.connect('servername', 'accountname', 'password', 'dbname')
cur = conn.cursor()
 
idx = 0
cnt_user_patent = len(df_user_patent)
 
for k in range(cnt_user_patent):
    qry = 'exec up_ttt_admininfo @skey=' + str(df_user_patent["skey"][k])
 
    cur.execute(qry)
    rows = cur.fetchone()
 
    while rows:
        df_wops_admininfo.loc[idx] = [rows[0], rows[1], rows[2], rows[3], rows[4], rows[5]]
        rows = cur.fetchone()
 
        idx += 1
 
# -- 타입 변경 : float64 to int64
df_wops_admininfo["skey"] = df_wops_admininfo["skey"].astype(long)
 
df_wops_admininfo.head(5)
 
conn.close()
 
 
# ********************************************
# -- user_patent에서 skey 기준으로 패밀리 정보 가져오기
#    df_wops_family
# ********************************************
print("-" * 100 + "[5]") # ----- #
 
df_wops_family = pd.DataFrame(columns=("skey","seq","fmly_skey","fkey","fmly_num","ctry_cnt","kr_cnt","us_cnt","jp_cnt"))
 
conn = pymssql.connect('servername', 'accoutnname', 'password', 'dbname')
cur = conn.cursor()
 
idx = 0
cnt_user_patent = len(df_user_patent)
 
for k in range(cnt_user_patent):
    qry = 'exec up_ttt_family @skey=' + str(df_user_patent["skey"][k])
 
    cur.execute(qry)
    rows = cur.fetchone()
 
    while rows:
        df_wops_family.loc[idx] = [rows[0], rows[1], rows[2], rows[3], rows[4], rows[5], rows[6], rows[7], rows[8]]
        rows = cur.fetchone()
 
        idx += 1
 
# -- 타입 변경 : float64 to int64
df_wops_family["skey"] = df_wops_family["skey"].astype(long)
df_wops_family["fmly_skey"] = df_wops_family["fmly_skey"].astype(long)
 
df_wops_family.head(5)
 
conn.close()
 
 
# ********************************************
# -- user_patent에서 skey 기준으로 동일 IPC 특허 정보 가져오기
# ********************************************
print("-" * 100 + "[6]") # ----- #
 
df_wops_ipcpat = pd.DataFrame(columns=("skey","ipc","ctry_cd","doc_kind","appl_num","appl_dt","publ_num","publ_dt","grt_num","grt_dt"))
 
conn = pymssql.connect('servername', 'accoutnname', 'password', 'dbname')
cur = conn.cursor()
 
idx = 0
cnt_user_company = len(df_user_company)
 
for f in range(cnt_user_company):
    qry = "exec up_ttt_ipcpat @ipc='" + str(df_user_company["ipc"][f]) + \
          "'," + "@start_year='20150101', @end_year='20151231'"
 
    cur.execute(qry)
    rows = cur.fetchone()
 
    while rows:
        df_wops_ipcpat.loc[idx] = [rows[0], rows[1], rows[2], rows[3], rows[4], rows[5], rows[6], rows[7], rows[8], rows[9]]
        rows = cur.fetchone()
 
        idx += 1
 
# -- 타입 변경 : float64 to int64
df_wops_ipcpat["skey"] = df_wops_ipcpat["skey"].astype(long)
 
df_wops_ipcpat.head(5)
 
conn.close()
 
 
# ********************************************
# -- KIPRIS PLUS에서 기술 정보 획득위한 API 호출하여 DF에 저장
#    df_kipris_techdeal
# ********************************************
print("-" * 100 + "[7]") # ----- #
 
kiprisURL1 = u"http://plus.kipris.or.kr/kipo-api/ipmarket/CommonTradePatentInfoService/getPatentPatentee?pem_user="
kiprisURL2 = u"&ServiceKey=Ffoec2gBOXnRp6nZC2ecN7r83RIn5=T2VSFfcBKpE7k="
 
df_kipris_techdeal = pd.DataFrame(columns=("com_nm", "no", "cr_dt", "deal_ti"))
 
idx = 0
 
for i in range(df_user_company.shape[0]):
    com_nm = urllib2.quote(df_user_company["com_nm"][i].encode("utf-8"))
    kiprisURL = kiprisURL1 + com_nm + kiprisURL2
 
    kiprisPage = urllib2.urlopen(kiprisURL)
    kiprisSoup = BeautifulSoup(kiprisPage, "html.parser")
 
    rowcnt = len(kiprisSoup.find_all("item"))
 
    if (rowcnt != 0):
        for j in range(rowcnt):
            df_kipris_techdeal.loc[idx] = [df_user_company["com_nm"][i],
                                           j + 1,
                                           kiprisSoup.find_all("createdate")[j].text,
                                           kiprisSoup.find_all("seltitle")[j].text
                                          ]
            idx += 1
 
df_kipris_techdeal.head(5)
 
 
# ********************************************
# -- user_patent와 wops_ipcpat 아래 Concatenate
# ********************************************
print("-" * 100 + "[8]") # ----- #
 
pd.concat([df_user_patent, df_wops_ipcpat])
 
 
# ********************************************
# -- user_patent와 wops_ipcpat 아래 Concatenate를 새로운 DF로 생성
#    df_new_patlist
# ********************************************
print("-" * 100 + "[9]") # ----- #
 
df_new_patlist = pd.concat([df_user_patent, df_wops_ipcpat])
 
df_new_patlist.head(5)
 
 
# ********************************************
# -- new_patlist를 통해서 KSIC 정보 추출
#    df_new_ksic
# ********************************************
print("-" * 100 + "[10]") # ----- #
 
# -- 기존 인덱스 초기화
df_new_patlist = df_new_patlist.reset_index(drop=True)
 
# -- KSIC 추출
df_new_ksic = pd.DataFrame(columns=("ipc", "pr_val", "ksic", "biz_nm"))
 
conn = pymssql.connect('servername', 'accoutnname', 'password', 'dbname')
cur = conn.cursor()
 
idx = 0
cnt_new_patlist = len(df_new_patlist)
 
for g in range(cnt_new_patlist):
    qry = "exec up_ttt_ksic @ipc='" + str(df_new_patlist["ipc"][g]) + "';"
 
    cur.execute(qry)
    rows = cur.fetchone()
 
    while rows:
        df_new_ksic.loc[idx] = [rows[0], rows[1], rows[2], rows[3]]
        rows = cur.fetchone()
 
        idx += 1
 
df_new_ksic.head(5)
 
conn.close()
 
# ********************************************
# -- new_patlist와 new_ksic와의 Merge(inner)
# ********************************************
print("-" * 100 + "[11]") # ----- #
 
# -- new_patlist.ipc를 4자리로 절삭(안하고 아래 left_on 처럼 가능)
# df_new_patlist["ipc4"] = df_new_patlist["ipc"].str[:4]
 
pd.merge(df_new_patlist, df_new_ksic, how="inner", left_on=df_new_patlist["ipc"].str[:4], right_on="ipc")
 
 
# ********************************************
# -- new_patlist와 new_ksic와의 Merge(inner) 된 결과의 다운로드
# ********************************************
print("-" * 100 + "[12]") # ----- #
 
# -- DF 새로 생성
df_new_download = pd.merge(df_new_patlist, df_new_ksic, how="inner", left_on=df_new_patlist["ipc"].str[:4], right_on="ipc")
 
# -- 다운로드
excelOutPath = "C:\\samples\\wops\\new_patlist_ksic.xlsx"
writer = pd.ExcelWriter(excelOutPath, engine="xlsxwriter")
 
df_new_download.to_excel(writer, sheet_name="Sheet1", index=True)
 
writer.save()
 
 
# ********************************************
# -- user_patent와 wops_admininfo을 이용한 1:1 관계
# ********************************************
print("-" * 100 + "[12]") # ----- #
 
# -- 몇몇 ROW 삭제
df_user_patent = df_user_patent.drop(2, axis = 0)
df_user_patent = df_user_patent.drop(3, axis = 0)
df_user_patent = df_user_patent.drop(4, axis = 0)
 
df_wops_admininfo = df_wops_admininfo.drop(5, axis = 0)
df_wops_admininfo = df_wops_admininfo.drop(6, axis = 0)
df_wops_admininfo = df_wops_admininfo.drop(7, axis = 0)
 
# -- merge(inner)
pd.merge(df_user_patent, df_wops_admininfo, how="inner", left_index=True, right_index=True).head(5)
 
# - merge(outer)
pd.merge(df_user_patent, df_wops_admininfo, how="left", left_index=True, right_index=True).head(5)
pd.merge(df_user_patent, df_wops_admininfo, how="right", left_index=True, right_index=True).head(5)
 
 
# ********************************************
# -- df_wops_ipcpat 데이터셋 피벗팅
# ********************************************
print("-" * 100 + "[13]") # ----- #
 
pd.pivot_table(df_wops_ipcpat, index=["ctry_cd", "doc_kind"], values=["appl_dt","publ_dt"], columns=["ipc"], aggfunc=np.min)
 

 

 

-- Files

 

user_company.xlsx

다운로드

user_patent.xlsx

다운로드