#経済学のための実践的データ分析 4.1 SQL ことはじめ

一橋大学経済学部 28番教室
#経済学のための実践的データ分析 4.1 SQL ことはじめ

Transcript

1. 経済学のための実践的データ分析 4. SQL ことはじめ 28教室 経済学研究科 原泰史 yasushi.hara@r.hit-u.ac.jp
2. はじめに • 紙を配布するので, 最終レポートのグループ作成希望者は 5名を max にして, 学籍番号を記入してください
3. 確認事項 • 電源タップは足りているでしょうか？
4. アナウンスメント • 次回 • 「特許データベースの使い方」 • 次次回 • (人力や Web スクレイピングやRPA で)データを集めてきて処理をす るまでの長く険しい道」
5. アナウンスメントその2 • SciREX サマーキャンプ 2019 • みんなでディスカッションをして, 科学技術イノベーションに関わるプ レゼンテーションをまとめて、それを行政に届ける場 • 2019/8/26-8/28 • 場所: 政策研究大学院大学 (六本木) • 宿泊:ホテルアジア会館 • 参加費: • 無料（会場までの旅費、宿泊費は政策研究大学院大学が負担） ただし、交流会 費、1 日目・2 日目の昼食費は自己負担。 • 参加対象: • 現在大学に正規の学生として在籍中 https://scirex.grips.ac.jp/news/4260c9a7a4fae482a6c0 b9a7519c0df33b0ed912.pdf
6. アナウンスメントその2 https://scirex.grips.ac.jp/news/4260c9a7a4fae482a6c0b9a7519c0df33b0ed912.pdf
7. アナウンスメントその2 • タイムテーブル https://scirex.grips.ac.jp/news/4260c9a7a4fae482a6c0b9a7519c0df33b0ed912.pdf
8. 今日の内容. SQL ことはじめ • 大量のデータを運用し活用するためには、データベースを用い ると便利です。MySQL を用い、データから必要な情報を抽出 し、データ同士を接合する方法について簡単に解説を行い、続 いて実データを用いて解析を行います。 • MySQL Workbench および MySQL サーバのインストール方 法について解説し、データのSQL サーバへの展開方法について 解説します。
9. リレーショナル・データベース • Excel のようにひとつのテーブルですべてのデータを管理する のではなく, キーを介して複数のテーブルから構成されるデー タベース 6/9/2019 9 中間試験の成績 ・学籍番号 ・問1の得点 ・問nの得点 期末試験の成績 ・学籍番号 ・問1の得点 ・問nの得点 平常点の成績 ・学籍番号 ・出席点 ・小テストの得点 総合成績 ・学籍番号 ・総合得点(=中間試 験+期末試験+平常点) ・最終成績
10. 前回のリマインド
11. データに含まれている個人情報 • 特許データ • 発明者の所属, 自宅またはオフィスの住所 • 論文データ • 著者の所属, オフィスの住所, メールアドレス • 家計調査データ • 年収、家族構成、性別 etc… ⇒ 実証的に経済学の課題を理論に基づき解析するにはこれらのデータを 用いることが必要不可欠。だが、こうしたデータを使うときには、個人 情報への留意が重要。
12. Definition: Privacy and Confidentially • Privacy • Privacy encompasses not only the famous ‘right to be left alone’ or keeping one’s personal matters and relationships secret, but also the ability to share information selectivity but not publicly. • Confidentially • Confidentiality is preserving authorized restrictions on information access and disclosure, including means for protecting personal privacy and proprietary information.
13. 多変量解析におけるプライバシーの課題 • 特定のグループやサブサンプルにおける特性を抽出すると、最 終的には何処の誰か抽出出来てしまう • Ex. ) 特定の家計や所得のグループが特定のひとりの場合, 個人が特定 出来る • 具体例 • 国立大学法人一橋大学の役職員の報酬・給与等について • http://www.hit-u.ac.jp/guide/information/salary.html • 教授はともかく、該当する役職が１－２名の給与は公開されていない
14. Privacy Utility Tradeoff Initial Utility/Privacy Frontier Frontier after increase in external Data U* utility P2 P1 Privacy ・プライバシーと、データの可用性による利 便性の間にはトレードオフの関係 ・データが外部化されると、個人の再特定化 (reidentification) が容易になり、プライバ シーを確保するのは困難になる
15. 今日の内容 (105分) 1: データベースことはじめ 2: データベースを触ってみる 3: レポート(第一回) のご説明
16. データベースことはじめ
17. SQL を使うと 便利なこと • Q. “なんで SQL を使うん ですか？Excel かStata で いいんじゃないですか？” • A1. データ量 1270万件のデータを人間の 手やExcelで作業するのは無 理！(ときどきそういうこと をする学者さんいるけど) リレーショナルデータベース を使って処理したほうがはや い！ 2019/6/9 17
18. SQL を使うと便利なこと • A2. Excel の限界 • 1048576列 • これ以上は分割しないと ワークシートで処理できない • CPUのファン音が唸りをあげる • 腱鞘炎にもなる 2019/6/9 18
19. データを提供してくれるような Web ではなく SQL でデータを処理することのメリット • Web 版にくらべてレスポンスがはやい • （すべてにおいてはやいとはいっていない） • 自分のニーズに則したデータを取得できる • 他のデータベースとの接続が行い易くなる 2019/6/9 19
20. データベースの種類 • RDB(OLTP; Online Transaction Processing) • DocDB • グラフDB • Hadoop • RDB(DWH) Hadoop (HDFS+MapReduce) • Apache Hadoop • CloudEra • MapR • Hortonworks RDB(DWH) • Oracle Exadata • Teradata • Netezza • RedShift KVS/DocDB KVS • Cassandra • Redis DocDB • MongoDB • CouchBase RDB(OLTP) • Oracle • SQL Server • MySQL • PostgreSQL GraphDB • Neo4j • Datadog • OrientDB 引用: RDB技術者のためのNoSQLガイド スケールアウトできる スケールアウトできない スループット重視 オペレーション用途
21. システムエンジニアとして就職した場合 にデータベース運用者としてやること • データベースに係るインフラの整備 • データの整理 • データベースの設計 • データベースの運用 • Web フロントエンドとの接続
22. 昨今のデータセット • データ量 (Volume) が増大している • 処理速度 (Velocity) が増大している • 多様性 (Variety) が増加している • 正規化かつ、構造が固定されたデータ以外も扱うことが増えている • 具体例 • ソーシャルメディアに投稿されたテキスト • メタデータ • センシングデータ • メール • 取引データ 引用: RDB技術者のためのNoSQLガイド
23. 構造データと半構造データ データの分類 説明 データの例 非リレーショ ナルデータ 非構造データ バイナリや テキスト形 式など, データの構 造化が行わ れていない 半構造データ (ex. XML/JSON) 構造はある がスキーマ がない。頻 繁に構造が 変わる。 リレーショナ ルデータ 構造化データ (ex. RDBMS) スキーマが あり, 構造 が変わらな い。 電子 メール テキス ト・音声 データ システ ムログ オフィ ス文章 経理・財 務・人事 商品・ 在庫 営業・ CRM 決済・ 残高 センサ リング 情報 口コミ SNS 健康・ 医療 データ 統計 データ 行政 データ 他社保 有デー タ 引用: RDB技術者のためのNoSQLガイド
24. データモデルごとの違い。 引用: RDB技術者のためのNoSQLガイド
25. データモデルごとの違い (cont.) • キーバリューストア (KVS) • スケールアウトして, 大量データに対するクエリを高速に応答できる。 キーでアクセスするシンプルな使い方がメイン • ドキュメントDB • KVSの特徴に加えてJSONを扱う機能が豊富。スキーマレス (データ構 造を事前に定義する必要がなく、値の型も固定されない)。 • RDB • グラフDB • スケールアウトには適さない。RDB以上に複雑なデータ処理が可能。
26. グラフDB; Neo4j • 互いが接合している複雑なネットワーク上のデータ解析に向い ている • 経路探し, ソーシャルネットワーク上の関係性の解明 • 引用関係の分析 • 社会的な関係性の同定 • ログ解析のような処理には向いていない
27. グラフDBのデータ構造 :ラベル ノード 属性 {キー: バリュー, キー:バリュー} :ラベル ノード 属性 {キー: バリュー, キー:バリュー} :タイプ 属性 {キー: バリュー, キー:バリュー} 関係性 ラベル; 同じ種類のノードを識別するためのドメインの定義 ノード; RDB におけるレコードに相当。複数の属性を{キー:バリュー}で保持出来る 関係性; ノードとノードの間に存在, ノード間のつながりを表現する. 属性; RDB におけるカラム.
28. 「マトリックス」での関係性を示したグラフ
29. 社会科学{経済学, 経営学, 法学, 社会学} の 研究でグラフDBが利用できそうなケース • 企業間の取引関係の分析, SCM の分析 • 労使関係の分析 • 研究者同士の引用 (前方引用, 広報引用関係, 共著関係の分析 • 論文データベース • 特許データベース • 口コミの生成過程の分析
30. 主なデータベースの違い RDB(OTLP) KVS/DocDB RDB(DWH) Hadoop 重視する性能 ターンアラウンド タイム ターンアラウンド タイム スループット スループット 主な用途 オペレーション オペレーション 分析 分析 機能拡張モデル スケールアップ スケールアウト スケールアップ スケールアウト 応答時間 数ミリ秒 数ミリ秒 数秒-数分 数分-数時間 データモデル リレーショナルモ デル キーバリュー、 JSON リレーショナルモ デル 行データモデル データ量 -1TB -100TB -100TB -10PB 主に使うクエリ CRUD, トランザク ション CRUD ロード, 抽出, 集計 ロード, 集計 台数 1-2台 (正副構成) 3台-20台 1-2台 (正副構成) 10-100台 レプリケーション オプション デフォルト オプション デフォルト 引用: RDB技術者のためのNoSQLガイド
31. ということで、今回と次回は RDB データベースでの解析を お試し頂きたいと思います。
32. 研究用途のリレーショナルデータベース の具体例: PATSTAT • EPO の特許データベース • Web インターフェースも存在 • Raw Data 版は有償 • Web 版 PATSTAT は2ヶ月間無料 5/14/2015 32
33. PATSTATのデータ構造 http://documents.epo.org/projects/babylon/eponet.nsf/0/95da6bccf12e54a1c1257 aa1002e2d1d/$FILE/patstat_data%20elements_v1.1.pdf 5/14/2015 33
34. PATSTAT のデータ構造 5/14/2015 34
35. PATSTAT のモデル図 5/14/2015 35 • テーブルとテーブルをつなぎ合わせ るIDがあり • IDを介して複数のテーブルの関係性 (リレーショナル) が構築されている • これらのテーブルをつなぎ合わせる ことで、複雑なデータの解析を行う ことができる
36. 日本に属する組織または個人が2014年に出願した特許のう ち, 他国の組織または企業とどのくらいコラボレーションを しているのか調べてみる • クエリ • SELECT COUNT(*) AS numberOfCommonApplications, p1.doc_std_name as name1, p1.person_ctry_code as cc1, p2.doc_std_name as name2, p2.person_ctry_code as cc2 FROM tls206_person p1 JOIN tls207_pers_appln pa1 ON p1.person_id = pa1.person_id JOIN tls207_pers_appln pa2 ON pa1.appln_id = pa2.appln_id JOIN tls206_person p2 ON pa2.person_id = p2.person_id join tls201_appln p3 on pa1.appln_id = p3.appln_id WHERE p1.person_ctry_code = 'JP' AND p3.appln_filing_date >= '2014-01-01' AND p3.appln_filing_date <= '2014-12-31‘ AND pa1.appln_id > 0 AND pa2.appln_id > 0 AND p1.person_ctry_code <> p2.person_ctry_code GROUP by p1.doc_std_name, p1.person_ctry_code, p2.doc_std_name, p2.person_ctry_code ORDER BY numberOfCommonApplications DESC, p1.doc_std_name ASC, p2.doc_std_name ASC 5/14/2015 36 クエリ; データを取り出す上での約束事を明記 したもの
37. コラボレーション国ランキング US 855 (空白) 489 GB 131 DE 105 KR 101 CN 82 CA 59 SG 57 FR 57 NL 27 SA 20 CH 18 BE 18 IL 15 TH 14 LU 13 IN 11 5/14/2015 37
38. 企業ランキング PANASONIC CORP 100 SONY CORP 81 SHARP KK 46 KOBE STEEL LTD 41 FUJITSU LTD 36 TAKEDA PHARMACEUTICAL 28 NISSAN MOTOR 27 OTSUKA PHARMA CO LTD 23 HONDA MOTOR CO LTD 23 NIPPON STEEL & SUMITOMO METAL 20 TEXAS INSTRUMENTS JAPAN 20 TOSHIBA KK 20 CANON KK 20 HADANO HIROYUKI 19 NEC CORP 16 SHIONOGI & CO 14 SAWADA HARUKO 13 TOSHIBA TECHNO CT INC 13 SUGIO TOSHIYASU 13 IBM JAPAN 12 SATO YOSHITAKA 11 KYOCERA CORP 11 UNITIKA LTD 10 TATEISHI TAKAAKI 10 MASAKI TAKAKI 10 FUNAI ELECTRIC CO 10 5/14/2015 38
39. コラボレーション国x企業 国名 PANASON IC CORP SONY CORP SHARP KK KOBE STEEL LTD FUJI TSU LTD TAKEDA PHARMAC EUTICAL NISS AN MOT OR HONDA MOTOR CO LTD OTSUKA PHARMA CO LTD TOS HIB A KK TEXAS INSTRUME NTS JAPAN NIPPON STEEL & SUMITOMO METAL CA N O N KK 総 計 （空 白） 68 6 40 33 14 27 1 5 3 16 21 3 US 6 56 3 7 5 21 5 10 12 4 12 9 GB 9 3 17 8 7 5 1 50 CN 12 1 10 6 1 1 31 FR 1 4 10 15 KR 8 8 DE 3 4 7 SG 6 6 BE 3 1 4 MY 4 4 5/14/2015 39
40. データベースを使ってみる
41. 今日やってみること (60分～75分) 1. リレーショナルデータベース (MySQL) の環境をローカル (自 分の PC and/or Mac) に構築するため, MySQL Server と MySQL Workbench を導入する 2. Manaba からデータをダウンロードし, MySQL 上にデプロイ する 3. 展開したデータを MySQL Workbench を使って, 初期的な解 析を行う 4. JupyterNotebook からデータを読み込み初期的な解析をする • 時間内に終わらない場合には, 次回引き続き実習を行います。
42. 今日のレシピ • 標準化された特許データベース (の一部分; サンプルデータ) を Manaba 経由でお配りします • 先程の事例と同じように, 企業ごとの特性などを MySQL や python をつかって解析します
43. 使うデータ; 日本の特許データベース 6/9/2019 43 ・特許庁の手続きデータを工業所 有権情報・研修館が提供している 整理標準化データが元データ。 ・app_info を中心にデータが構成さ れるリレーショナル・データベース 方式
44. (1) 特許データベース (patR) • app_info 出願経過 テーブル 6/9/2019 44 フィール ド名 型 インデックス 内容（カッコ内は コード表インデッ クス） 1 app_num varchar(20) 出願番号：すべて半角(B0010) 2 count int(11) ワーク用カウンタ 3 title varchar(255) 発明の名称 4 app_date date 出願日 5 renewal_dat e date 更新日付：出願マスタ 6 id bigint(20) 元処理番号 7 pat_app_nu m varchar(10) 原出願記事番号(B0010) 8 app_type varchar(4) 原出願記事関連種別(B0310,C0025) 9 pub_num varchar(10) 公開番号 10 pub_date date 公開日 11 exam_pub_n um varchar(10) 公告番号 12 exam_pub_d ate date 公告日 13 intl_app_nu m varchar(12) 国際出願番号 14 pry_cnty char(2) 筆頭優先権主張国 15 num_claim_ app int(11) 請求項の数：出願時 16 num_claim_ examed int(11) 請求項の数：公告決定時 17 num_claim_r eg int(11) 請求項の数：登録査定時 18 reg_num varchar(19) 特許番号または登録番号 19 reg_date date 登録日 20 rej_rsn char(2) 拒絶理由条文コード(C0710) 21 cnln_cl char(2) 本権利抹消識別(C0780) 22 term_dat e date 本権利消滅年月日 23 pry_clai m_date date 優先権主張日 24 dspn_ex am_date date 審査最終処分日 25 dspn_ex am_code char(3) 審査最終処分種別コード(C0360) 26 apnum varchar( 13) MUL ‘JPP’をapp_numの先頭につけた文字列 27 fin_decn char(1) 査定種別コード(C0350) 28 fin_decn _date date 査定発送日 29 trans_su bm_date date 翻訳文提出日 30 trans_pu b_num varchar( 10) 公表番号 31 idp int(11) PRI 本テーブルの固有行番号 32 num_clai m_reg_i nfo int(11) 請求項の数（登録情報） 33 udate date ワーク用日付フィールド 34 IPC8 varchar( 255) 国際特許分類第８版 35 acc_exa m_mark char(1) 早期審査マーク(C0240)
45. (1)特許データベース (patR) • citation 引用情報 テーブル 6/9/2019 45 フィール ド名 型 インデッ クス 内容 （カッコ 内はコー ド表イン デック ス） 1 citing varchar(1 0) MUL 引用特許 出願番号 (B0010) 2 cited varchar(1 0) MUL 被引用特 許出願番 号(B0010) 3 type int(11) MUL 種別（1： 審査官引 用 2：特許公 報に記載 された引 用 3：上記両 方に記載 の引用） フィールド名 型 インデックス 内容（カッコ 内はコード表 インデック ス） 1 ids int(11) PRI 固有行番号 2 name text 氏名 3 addr text 住所 4 prefecture char(2) 住所の国県 コード (C0050) 5 id_num varchar(9) 出願人コード (B0070) 6 req_type char(1) 個法官別コー ド(C0070) 7 type int(11) ワーク用 8 name1024 varchar(1024 ) MUL 氏名のイン デックス文字 列 9 addr1024 varchar(1024 ) MUL 住所のイン デックス文字 列 • applicant 出願人
46. (1) patR • inventor 発明者テーブル 6/9/2019 46 フィールド名 型 インデックス 内容（カッコ 内はコード表 インデック ス） 1 name text 氏名 2 addr text 住所 3 req_type char(1) 個法官別コー ド(C0070) 4 organization Varchar(255) 所属する組織 の名称 5 ids int(11) PRI 固有行番号 6 name1024 varchar(1024 ) MUL 氏名のイン デックス文字 列 7 addr1024 varchar(1024 ) MUL 住所のイン デックス文字 列 フィールド名 型 インデックス 内容（カッコ内 はコード表イン デックス） 1 apnum varchar(13) MUL ‘JPP’+出願番号 (B0010) 2 app_num varchar(10) MUL 出願番号(B0010) 3 pub_num varchar(10) MUL 公開番号 4 intl_app_num varchar(12) MUL 国際出願番号 5 intl_pub_num varchar(12) MUL 国際公開番号 6 trans_pub_num varchar(10) MUL 公表番号 7 exam_pub_num varchar(10) MUL 公告番号 8 reg_num varchar(10) MUL 特許番号または 登録番号 9 ref_pub_num varchar(13) MUL 文献公開番号 10 ref_trans_pub_n um varchar(13) MUL 文献公表番号 11 ref_examd_pub_ num varchar(13) MUL 文献公告番号 12 ref_reg_num varchar(13) MUL 文献登録番号 13 ref_intl_pub_nu m varchar(13) MUL 文献国際公開番 号 • numbers 番号表テーブル
47. SQL を使ったデータ分析 • 材料 • MySQL サーバ: オープンソース型データベース。 • RDBMS (Relational Database Management System) • MySQL Workbench: MySQL の開発用コンソール • サンプルデータ • Manaba に展開済み • 7z 形式で圧縮しているため, 解凍が必要 2019/6/9 47
48. 1. MySQL サーバのイ ンストール • 次ページ以降を参照のこと Windows のみなさま • [講義資料] にアップしている, “macOS Mojave にMAMP or MySQL Serverと MySQL Workbench をインストールす る” を参照の上, MAMP をインストール するか, 個別に MySQL サーバと MySQL Workbench をインストールす ること Mac のみなさま
49. 1.MySQL サーバとMySQL Workbenchをダ ウンロードしよう • http://dev.mysql.co m/downloads/mys ql/ から、Mac版ま たは Win 版のバイ ナリをダウンロード する 2019/6/9 49
50. 1. MySQL サーバをダウンロードしよう • MSI Installer (32bit) をクリック する 2019/6/9 50 ・“No thanks, just start my download”をクリックする
51. 1-1. MySQL サーバをインストールしよう • Installer が開くので、 NEXT > をクリックする 2019/6/9 51
52. 1-1. MySQL サーバをインストールしよう • “I accept the license terms” チェックボッ クスを選択し て, NEXT > を クリックする 2019/6/9 52
53. 1-1. MySQL サーバをインストールしよう • Available Products から “MySQL Server – X64” と “MySQL Workbench” を選び ⇒ をクリックして、 Products/Features to be Installed に移 動させる 2019/6/9 53
54. 1-1. MySQL サーバをインストールしよう • Execute をクリックする 2019/6/9 54
55. 1-1. MySQL サーバをインストールしよう • インストールが行われる • Next > をクリック 2019/6/9 55
56. 1-1. MySQL サーバをインストールしよう • Next > をクリックする 2019/6/9 56
57. 1-1.MySQL サーバをインストールしよう • Config Type • Development machine • TCP/IP 3306 • Open Firewall Port for Network Access チェック ボックスを外す 2019/6/9 57
58. 1-1.MySQL サーバをインストールしよう • MySQL Root Password を 指定する • Repeat Password に同じ パスワードを指定すること 2019/6/9 58
59. 1-1. MySQL サーバをインストールしよう • Windows Serviceの設定 • 特に変更しない 2019/6/9 59
60. 1-1. MySQL サーバをインストールしよう • Plugins and Extensions • そのまま 2019/6/9 60
61. 1-1. MySQL サーバをインストールしよう • Execute をクリックす る 2019/6/9 61
62. 1-1. MySQL サーバをインストールしよう • Finish をクリックする 2019/6/9 62
63. 1-1. MySQL サーバをインストールしよう • Next > をクリックする • Finish をクリックする 2019/6/9 63
64. 1-2. MySQL Workbench でMySQL サーバ に接続する • MySQL Workbench を起動する (Windows 10 の場合) 2019/6/9 64
65. 1-2. MySQL Workbench で MySQL サーバ に接続する • + ボタンをクリックする 2019/6/9 65
66. 1-2. MySQL Workbench でMySQL サーバ に接続する • Password Vault をクリックする 2019/6/9 66
67. 1-2. MySQL Workbench でMySQL サーバ に接続する • Password を入力する • Test Connection をクリック する • 以下のメッセージが出ればOK 2019/6/9 67
68. 1-2. MySQL Workbench でMySQL サーバ に接続する • Connection Name を指定して, OK をクリックする 2019/6/9 68
69. 1-2. MySQL Workbench でMySQL サーバ に接続する • MyServer をクリックする 2019/6/9 69
70. 1-2. MySQL Workbench で MySQL サーバ に接続する • コンソールが開く 2019/6/9 70
71. 2. サンプルデータをダウンロードしよう • Manaba の「経済学のための 実践的データ分析」ページに アクセスし, TOPページの 「データダウンロード」欄を クリックする 2019/6/9 71
72. 2-2. 今日使うデータ(IIP パテントデータ ベース) • 特許庁の『整理標準化 デー タ』（2013 年度提供分ま で）を基に作成された研究用 特許データベース • 「1964000001」以降の出願 番号を持つ特許（出願）を含 む • 今回は, (ダウンロード時間の 都合上)一部分のみを切り出し たデータで解析します 2019/6/9 72
73. 2-2. IIP パテントデータベース 2019/6/9 73 • データベースの構造 出願番号(ida)に基づき紐付 けることができる
74. 2-2. IIP パテントデータベース • 出願テーブル • 出願番号 • 登録番号 • 出願日 • 登録日 • IPC番号 • 請求項 (claim の数) などが記載されている 2019/6/9 74
75. 2-3. IIP パテントデータベースをMySQL サーバにインポートする • 中身 • 「なんらかの意味をもった 何 らかのデータ (引用: 岡崎体 育)」 • これをMySQL サーバに取り 込む 2019/6/9 75
76. 2-3. データベースをMySQL サーバにインポートする • ここからしないといけないこと • (1) データベースをつくる • (2) テーブルをつくる 2019/6/9 76 76 中間試験の成績 ・学籍番号 ・問1の得点 ・問nの得点 期末試験の成績 ・学籍番号 ・問1の得点 ・問nの得点 平常点の成績 ・学籍番号 ・出席点 ・小テストの得点 総合成績 ・学籍番号 ・総合得点(=中間試 験+期末試験+平常点) ・最終成績 これがテーブ ル これがデータベース
77. 2-3. パテントデータベースをMySQL サー バにインポートする • MySQL Workbench を開く 2019/6/9 77
78. 2-3. パテントデータベースをMySQL サー バにインポートする • Manaba の”データ”から hara_space_ap2.7z をダウンロードする
79. 2-3. パテントデータベースをMySQL サー バにインポートする • ダウンロードした hara_space_ap2.7z を解凍する • 解凍するためにソフトウェアのインストールが必要 • Windows の場合 • https://sevenzip.osdn.jp/ から7zip をダウンロードしてインストール • Mac の場合 • The Unarchiver (https://itunes.apple.com/us/app/the- unarchiver/id425424353?mt=12&v0=WWW-NAUS-ITUHOME- NEWAPPLICATIONS&ign-mpt=uo%3D2) を App Store からインストール • 参考文献; https://qiita.com/ntkgcj/items/afe4863c40680d72a755 • 解凍し, SQL ファイルを取得する
80. 2-3. パテントデータベースをMySQL サー バにインポートする • Data Import/Restore をクリックする 2019/6/9 80
81. 2-3. パテントデータベースをMySQL サー バにインポートする Import from Self-Comtained File の ... から解凍したSQL ファイルを選択する “New” から新しいスキーマを作成する “Start Import” をクリックする
82. 2-3. パテントデータベースをMySQL サー バにインポートする • コーヒーかレッドブルでも飲んで待つ 2019/6/9 82
83. 2-3. パテントデータベースをMySQL サーバにイン ポートする • データのインポートが終 わった場合、以下のメッ セージが表示される • レコードがインポートさ れたことが表示されてい る 2019/6/9 83
84. これで、 ようやく分析の スタートライン 2019/6/9 84 さ あ み ん な 特 許 デ ー タ は も っ た か い く ぞ ！
85. 2-4. MySQL を使ってデータベースを解析し てみる 2019/6/9 85 ・とりあえず、データの 中身を観てみる ・”select * from ap;” とタ イプして、雷マークをク リックする ・数秒待つと、先ほどイ ンポートしたデータが表 示される
86. 2-4. MySQL を使ってIIP パテントデータ ベースを解析してみる • 出願された特許のIPC分類を カウントしてみる • エクスポートボタンをおして, Excel で処理してみる 2019/6/9 86 select class1,count(*) as count from ap2 Group by class1 order by count desc;
87. 2-4. MySQL を使ってIIP パテントデータ ベースを解析してみる • この命令は何をやっているの か？ 2019/6/9 87 select class1,count(*) as count from ap2 group by class1 order by count; Select – 次に書くデータを取得してね 今回の場合; class1 という変数ごとに , その件数を count してね. Count した値の変数名として, count と 名付けてね From – 次のテーブルからデータを取ってきてね 今回の場合; ap2 テーブルからデータを取ってきてね group by – 同じラベル同士の変数はグループを作成してね 今回の場合; class1 で記載された分類ごとにデータを集約してね Order by – 数字の大小で出力結果を並べ替えてね 今回の場合; count の値で並べ替えてね
88. 日本で, どのような分類の特許が出願さ れているのか確認できる 2019/6/9 88
89. Extended; Jupyternotebook から local の mysql サーバに接続してコマンドを実行する • 実行にあたり必要なパッケージ (pymysql と ipython-sql) をイ ンストールする
90. Extended; Jupyternotebook から local の mysql サーバに接続してコマンドを実行する • MySQL サーバに接続するための情報を指定する.
91. Extended ; Jupyternotebook から local の mysql サーバに接続してコマンドを実行する • データの中身を取り出し, 先頭から100行目までを抽出する.
92. Extended ; Jupyternotebook から local の mysql サーバに接続してコマンドを実行する • IPC 分類ごとにカウントする
93. 今日はここまで
94. 次回のレシピ • やりたいこと 1. 特定の企業の特許出願数を数える 2. 特定の企業 (企業名の変遷などを名寄せしたもの) ごとの特許出 願数やIPC分類を数える 3. 特定の産業ごとの特許出願数やIPC分類を数える 2019/6/9 94
95. 次回のレシピ • 材料 1. IIP パテントデータベース 2. NISTEP 企業名辞書 3. NISTEP 企業名辞書とIIP パテントデータベースの接合テーブル 2019/6/9 95
96. 材料1. 出願人テーブル • 出願人(applicant)テーブルの中身を確認する 2019/6/9 96 コラム名 変数の形式 主キー ida Int(10) YES seq Int(3) ida_seq Varchar(14) name Mediumtext address Mediumtext idname Varchar(10) country_pref Varchar(5) kohokan Varchar(3)
97. 材料1. 出願人テーブル • データの中身を確認してみる select * from applicant; 2019/6/9 97
98. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • やりたいこと • 特定の会社の年毎の出願数を知りたい • 特定の会社のIPC分類ごとの出願数を知りたい • そのためには • 出願テーブルと出願人テーブルを接合して、特定の会社が、どういう 特許を、いくつ出しているのか抽出する 2019/6/9 98
99. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • ER図 2019/6/9 99
100. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • MySQL Workbench 上で以下のクエリを打ち込む ・この命令は何をしているのか？ 1行目: idaとadateとrdateとclassとclaimとida_seqの情報を取ってきてね 2行目: ap テーブルを参照するよ 3行目: applicant テーブルも参照するよ. このとき, applicant の ida フィールドと ap の ida フィールドを キーにして, applicant のデータを取り出してね 4行目: applicant.name が 松下電器産業株式会社 なものを持ってきてね. 2019/6/9 100 select ap.ida, ap.adate, ap.rdate, ap.class1, ap.claim1, applicant.ida_seq from ap inner join applicant on applicant.ida = ap.ida where applicant.name like "松下電器産業株式会社";
101. SQL 構文の話 • SELECT: • 1 つ以上のテーブルから選択された行を取得するために使用する • Where: • 選択されるために行が満たす必要のある 1 つまたは複数の条件 • Join: • Inner Join:指定したカラムについて同じ値を持つレコード同士を結びつける • Left Join: • 左のテーブルを基準にして、指定したカラムについて同じ値を持つレコード同士を結びつ ける。値が右のテーブルにあり左のテーブルにない場合は INNER JOIN 同様結果に含まれ ないが、値が左のテーブルにあり右のテーブルにない場合は INNER JOIN と異なり 右の テーブルのカラムには全て NULL がパディングされ、結果に含まれる。 • Right Join: • 右のテーブルを基準にして、指定されたカラムについて同じ値を持つレコード同士を結び つける。 2019/6/9 101 https://dev.mysql.com/doc/refman/5.6/ja/
102. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • 以下のようなデータが出力される 2019/6/9 102
103. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • エクスポートする • フロッピーディスクのアイコンをクリックする 2019/6/9 103
104. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • ファイル名を指定して保存する 2019/6/9 104
105. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • Excelで開く 2019/6/9 105
106. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • ピボットテーブルを作る • [挿入]-[ピボットテーブル]を選 択 • ピボットテーブルの範囲が選 択されていることを確認し、 [OK] をクリックする 2019/6/9 106
107. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • ピボットテーブルが作成され る • フィールド名が項目に表示され る 2019/6/9 107
108. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • 行に class1, 値に ida を入れ て, ida の個数を数えるように 設定する • 降順に並べ替える • (「松下電器産業」の)IPC 分 類ごとの特許出願数が確認で きる 2019/6/9 108
109. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • グラフにする (Excel 2016 の 場合) • ピボットグラフをクリックする • [縦棒]を選び、OK をクリッ クする 2019/6/9 109 0 5000 10000 15000 20000 25000 30000 35000 40000 G11B G02F F23D G09F C04B G10H F16C G10K F23Q B23Q B05D B65B H03C C10M F02G F02P F17C G10B A61C A61D A63C A63H B31B F02N D01D F01D B24C G09D E04G F02C G06J A41B F42C D02G B63H
110. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • ツリーマップ 2019/6/9 110
111. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • 年ごとの出願数を求めてみる • 行に adate, 値はida (個数)を指 定する • Excel 2016 の場合, 自動的に年 を取り出してくれる • Excel2013 の場合は、自分で left 関数などを使って切り出す必要が ある • 四半期のデータは必要がないので 取り出す • 年毎の出願数が表示される 2019/6/9 111
112. 下ごしらえ1 出願テーブルと出願人テーブルの接合 • グラフにする 2019/6/9 112 0 2000 4000 6000 8000 10000 12000 14000 16000 18000 20000 1963年 1965年 1967年 1969年 1971年 1973年 1975年 1977年 1979年 1981年 1983年 1985年 1987年 1989年 1991年 1993年 1995年 1997年 1999年 2001年 2003年 2005年 2007年
113. 答え. 松下電気工業で検索したた め、パナソニックで出願されてい る特許が存在しない。 ↓ 対処方法. 企業名の「名寄せ」を行う 2019/6/9 113
114. 材料2. NISTEP企業名辞書 • 企業名, 企業名の変遷などを 納めたデータベース • “産業セクターのイノベーション分析・研究に用い るデータベースの中心に位置付けられ、特許情報 や国内営利企業（以下、「企業」と呼ぶ）に関す る各種調査情報など、外部データから指定した企 業に関する情報を抽出するためのハブとしての役 割を担う” • 日本の会社データ(東洋経済新報社), IIP パテン トデータベース, 証券コードなどとの接続が可 能 • “企業名の読み、本社所在地、業種など、外部デー タに含まれる数多くの企業から分析対象である企 業を正しく特定しデータ抽出するための支援、お よび、合併や企業名称の変遷を考慮したデータの 収集など、企業を中心としたイノベーション分 析・研究における核となる機能を持つ” • 使いみち • いくつかのデータベースをつなぎ合わせることで、 企業の活動を定量的に計測することが可能になる 2019/6/9 114 comp_id 出願人テーブル/ida_sequence 証券コード 又は EDINETコード 企業ID comp_id 外部データ 接続テーブル 東洋経済会社コード 日本の会社データ4万社 （東洋経済新報社） NISTEPによるファイル公開範囲 （一般財団法人知的財産研究所より入手のこと） （必要に応じて利用者が購入のこと） IIPパテントデータベース IIPパテントデータベース （2015年版） との接続用 日本の会社データ4万社との接続用 NISTEP企業名辞書 外部データ 接続テーブル 証券コード、EDINETコードを持つ 企業情報データ （財務・株価データなど） （必要に応じて利用者が準備のこと） 外部データ 接続テーブル （将来予定） 企業名と住所をキーと した汎用接続テーブル 企業統計調査データ など （必要に応じて利用者が準備のこと） 外 部 デ ー タ 外 部 デ ー タ NISTEP企業名辞書は、 Ver.2015_1よりRDB構 造に変更された
115. 材料2. NISTEP 企業名辞書 • http://www.nistep.go.jp/research/scisip/rd-and-innovation- on-industry 2019/6/9 115
116. 材料2. NISTEP 企業名辞書 • カバーする企業群 ①特許出願数累積100件以上 • IIPパテントデータベースの2014年版（iipdb20140417）において、1970年以降の企 業の変遷（名称変更、合併）を考慮した特許出願の集約を行い、累積出願数が100件 を超える企業を特定し掲載 • ②株式上場企業 • 2012年1月時点の全上場企業と以降2015年3月までに新規（又は再）上場した企業を 掲載 • ③特許出願数の伸び率大 • 近年起業し活躍するベンチャー企業など、条件①では取りこぼす可能性がある企業の 抽出を目的とする。 • 1970年以降の企業の変遷を考慮した年ごとの出願数を把握し、それらデータを用い て3年、5年、7年の各期間で1年ごと移動させた線形フィットを行い、大きな回帰係 数（出願数増分）を持つ企業を抽出 • 抽出企業には条件①および②から抽出した企業が含まれるが、それら企業を除いた中 から上位500社強を抽出して掲載を行っている。 2019/6/9 116
117. 材料2. NISTEP 企業名辞書 • 掲載企業数 2019/6/9 117 ①出願数100件以上 ②上場企業 ③出願数伸び率大 掲載企業 数 ✔ 569 ✔ 2,510 ✔ 533 ✔ ✔ 219 ✔ ✔ 1,422 ✔ ✔ 13 ✔ ✔ ✔ 1,080 三条件以外 148 合計 6,494
118. 材料2. NISTEP 企業名辞書 • 構成テーブル 2019/6/9 118 番号 テーブル名称 概要 論理名 物理名 1 企 業 名 辞 書 メ インテーブル 1_comp_nam e_main_TB L 企業名、企業id等のメインの情報、お よびパネルデータとして整備をする必 要がなく、最新の情報のみ保持すれば よいデータを保管 2 沿革テーブル 2_comp_histor y_TBL 名称変更や吸収合併などの事象が発生し た際に発生した年、事象の種類を保管 3 所 在 地 テ ー ブ ル 3_address_TB L 企業の所在地に関する情報を保管 本社、本店、移転など複数の住所情報の 保管、パネル化が可能 4 企 業 規 模 テ ー ブル 4_comp_size_ TBL 資本金、従業員数、中小企業基本法によ る企業規模情報を保管 規模測定年ごとのパネル化が可能 5 業 種 （ 証 券 コ ー ド 協 会 ） テーブル 5_ind_class_ts e_TBL 証券コード協議会の定める当該企業の業 種区分を保管 属する分類が変更された際のパネル化が 可能 6 業 種 （ 日 本 標 準 産 業 分 類 ） テーブル 6_ind_class_js ic_TBL 主業の日本標準産業分類を保管 属する分類が変更された際のパネル化が 可能 7 EDINETコード テーブル 7_edinet_code _TBL EDINETのコードを保管 コードが変更された際のパネル化が可能 8 証 券 コ ー ド テーブル 8_sec_code_T BL 証券コードを保管 コードが変更された際のパネル化が可能 9 連 結 企 業 テ ー ブル 9_consolidate _TBL 連結子会社である場合の親企業情報を保 管 連結関係の変化のパネル化が可能 10 データ登録条件マス ターテーブル 10_reg_reason_MT BL 企業が企業名辞書に登録された理由に関するマスター テーブル 11 企業名称使用開始事 象マスターテーブル 21_use_name_start_ event_MTBL 新設、旧名称からの名称変更等、企業名称の使用が開始 された場合の使用開始事象に関するマスターテーブル 12 企業名称使用終了事 象マスターテーブル 22_use_name_end_e vent_MTBL 名称変更、吸収合併など、企業名称の使用が終了した場 合の使用終了事象に関するマスターテーブル 13 事業所区分マスター テーブル 31_office_class_MT BL 住所情報の本社、本店、事業所等を判定するためのマス ターテーブル 14 業種（証券コード協 会）マスターテーブ ル 51_tse_MTBL 証券コード協議会の定める業種区分に関するマスター テーブル 15 業種（日本標準産業 分類）マスターテー ブル 61_jsic_MTBL 日本標準産業分類に関するマスターテーブル 平成25年10月改定・平成26年4月1日施行に準拠 16 企業連結事象発生マ スターテーブル 91_consolidate1_MT BL 連結事象が発生した場合の発生理由(子会社化等)に関す るマスターテーブル 17 企業連結事象終了マ スターテーブル 92_consolidate2_MT BL 連結事象が終了した場合の発生理由(他社の子会社と なった、独立した等)
119. 材料2. NISTEP 企業名辞書 • ER図 2019/6/9 119 企業名辞書メインテーブル （1_comp_name_main_TBL） 企業id 企業名称 ふりがな 法人格コード 英語名称 URL データ登録理由id データ登録日 データ更新日 沿革テーブル （2_comp_history_TBL） 企業id 名称使用開始年 名称使用開始事象id 事象発生前企業id 名称使用終了年 名称使用終了事象id 事象発生後企業id データ登録日 データ更新日 企業名称使用開始事象マスターテーブル （21_use_name_start_event_MTBL） 事象id 事象概要 データ登録日 データ更新日 1…N 連結企業テーブル （9_consolidate_TBL） 企業id 連結事象発生年 連結事象発生事象id 連結事象発生前連結企業id 連結先連結企業id 連結事象終了年 連結事象終了事象id 連結事象終了後連結企業id データ登録日 データ更新日 所在地テーブル （3_address_TBL） 企業id 所在地利用開始年 所在地利用終了年 本店・本社コード 所在地 都道府県コード 地方自治体コード 住所コード 緯度 経度 データ登録日 データ更新日 EDINETコードテーブル （7_edinet_code_TBL） 企業id EDINETコード確認年 EDINETコード データ登録日 データ更新日 企業規模テーブル （4_comp_size_TBL） 企業id 企業規模測定年 中小企業基本法 資本金階級 従業員数階級 データ登録日 データ更新日 業種（証券コード協会）テーブル （5_ind_class_tse_TBL） 企業id 東証33分類開始年 東証33分類終了年 東証33分類コード データ登録日 データ更新日 事業所区分マスターテーブル （31_office_class_MTBL） 本店・本社コード 概要 データ登録日 データ更新日 N…1 1…1 企業名称使用終了事象マスターテーブル （22_use_name_end_event_MTBL） 事象id 事象概要 データ登録日 データ更新日 1…1 1…1 企業連結事象発生マスターテーブル （91_consolidate1_MTBL） 事象id 事象概要 データ登録日 データ更新日 企業連結事象終了マスターテーブル （92_consolidate2_MTBL） 事象id 事象概要 データ登録日 データ更新日 1…1 1…1 証券コードテーブル （8_sec_code_TBL） 企業id 証券コード 上場市場 上場日 上場廃止日 ISINコード データ登録日 データ更新日 業種（日本標準産業分類）テーブル （6_ind_class_jsic_TBL） 企業id JSIC開始年 JSIC終了年 JSIC分類番号 データ登録日 データ更新日 業種（証券コード協会）マスターテーブル （51_tse_MTBL） 東証33分類コード 東証33分類版 東証33分類大分類 東証33分類小分類 データ登録日 データ更新日 業種（日本標準産業分類）マスターテーブル （61_jsic_MTBL） JSIC分類番号 JSIC版 JSIC大分類 JSIC中分類 JSIC小分類 データ登録日 データ更新日 1…1 1…1 データ登録条件マスターテーブル （10_reg_reason_MTBL） 理由id 登録理由 データ登録日 データ更新日 1…1
120. 材料2. NISTEP 企業名辞書 • RDB はわかりにくいので、EXCEL 版も提供されている • 企業ID, 沿革ID, EDINETコード, 証券コード, 企業規模, 産業分類 などが確認できる 2019/6/9 120
121. 材料2. NISTEP 企業名辞書 • 企業名辞書メインテーブル 2019/6/9 121 企業名辞書メインテーブル [1_comp_name_main_TBL] フィールド名 データ型 重複 NULL 主 キー 外部キー 説明 論理名 物理名 企業番号 comp_id 数値 （整数） N N Y 企業（企業名称ごと）に 固有に付与した番号 沿革番号 history_i d 数値 （整数） Y N 同一企業の変遷レコード をグループ化して扱うた めの番号 企業名称 comp_na me 文字列 Y Y 企業の名称（変遷名称も 含む） ふりがな read 文字列 Y Y 上記企業名称のふりがな 法人格 コード comp_co de 文字列 Y Y 企業の法人格を表すコー ド（下表参照） 英語名称 e_name 文字列 Y Y 企業の英語名称 URL url 文字列 Y Y 企業のウェブページの URL データ登 録理由番 号 reg_reas on_id 数値 （整数） Y Y データ登録理由マスター テーブルの理由番号 当該企業の辞書掲載条件 データ 登録日 reg_date 年月日 Y N データを本テーブルに登 録した日 データ 更新日 up_date 年月日 Y N 既登録データの情報更新 した日
122. 材料2. NISTEP 企業名辞書 • 企業規模テーブル 2019/6/9 122 企業規模テーブル [4_comp_size_TBL] フィールド名 データ型 重複 NULL 主 キー 外部キー 詳細 論理名 物理名 企業番号 comp_id 数値（整 数） Y N Y 企業名辞書メインテーブ ルの企業番号 企業（企業名称ごと）に 固有に付与した番号 企 業 規 模 測定年 judg_year YEAR Y N Y 企業規模を確認した年 中 小 企 業 基本法 comp_size _law 文字列 Y Y 中小企業基本法に準拠し 判定した企業規模 資本金 階級 comp_size _cap 文字列 Y Y 資本金の該当階級 100万円未満 100万円以上 1000万円以上 2000万円以上 5000万円以上 1億円以上 10億円以上 従 業 員 数 階級 comp_size _emp 文字列 Y Y 従業員数の該当階級 5人未満 5～29人 30～99人 100～299人 300～999人 1,000～4,999人 5,000人以上 データ 登録日 reg_date 年月日 Y N データを本テーブルに登 録した日 データ 更新日 up_date 年月日 Y N 既登録データの情報更新 した日
123. 材料2. NISTEP 企業名辞書 • 業績 (証券コード協会) テーブル 2019/6/9 123 業種（証券コード協会）テーブル [5_ind_class_tse_TBL] フィールド名 データ型 重複 NULL 主キー 外部キー 詳細 論理名 物理名 企業番号 comp_id 数値 （整数） Y N Y 企業名辞書メインテーブ ルの企業番号 企業（企業名称ごと）に 固有に付与した番号 業種分類開 始年 inds_year YEAR Y Y 証券コード協会の業種分 類の確認初年 業種分類終 了年 inde_year YEAR Y Y 証券コード協会の業種分 類の確認最終年 業 種 分 類 コード ind_code 数値（4 桁整数） Y N Y 業種（証券コード協会） マスターテーブルの分類 コード 証券コード協会の分類該 当業種 データ 登録日 reg_date 年月日 Y N データを本テーブルに登 録した日 データ 更新日 up_date 年月日 Y N 既登録データの情報更新 した日
124. 材料3. NISTEP 企業名辞書とIIP パテント データベースとの接続テーブル • 企業名辞書と外部データであるIIPパテントデータベース （2015年版）を連携させるための接続テーブル • 企業名辞書メインテーブルの企業idとIIPパテントデータベースの出願 人テーブルのida_seqフィールドを関係付け接続する 2019/6/9 124 フィールド名 データ型 説明 論理名 物理名 企業番号 comp_i d 数値（整 数） 企業（企業名称ごと）に固有に付 与した番号 IIP パ テ ン ト 出 願 番 号 ＋ 記 載 順序 ida_seq 文字列 上記企業番号の企業が出願人であ る特許
125. 材料3. NISTEP 企業名辞書とIIP パテント データベースとの接続テーブル • ER図 2019/6/9 125 企業名辞書メインテーブル （1_comp_name_main_TBL） 企業番号 企業名称 ふりがな 法人格コード 英語名称 URL データ登録理由id データ登録日 データ更新日 IIPパテントデータベースとの接 続テーブル 企業番号 I出願番号＋記載順序 ＩＩＰパテントデータベース 出願人テーブル 出願番号 記載順序 出願番号＋記載順序 出願人名 出願人住所 出願人番号 住所コード 個法官コード
126. 詳細 • 企業名辞書(ver2015_01)利用マニュアル を参照のこと 2019/6/9 126
127. やりたいこと2: 特定の企業 (企業名の変遷などを名寄せした もの) ごとの特許出願数やIPC分類を数える • NISTEP企業名辞書を使って、パナソニック/松下電器が出願し た特許をすべからくカウントする。 • このとき、IPC 分類やclaim などについても、IIP パテントデータベー スから情報を取得する。 2019/6/9 127
128. 特定の企業 (パナソニック; 名寄せ済み) の特許出願数やIPC分類を数える • 方法 1. NISTEP企業名辞書をSQL サーバにインポートする 2. NISTEP企業名辞書とIIPパテントデータベースの接続テーブルをSQL サーバにインポートする 3. NISTEP 企業名辞書を使い、パナソニック子会社の情報を把握する。 これにより、企業ID (comp_id) と沿革ID (history_id)情報を取得する 4. NISTEP企業名辞書とIIPパテントデータベースの接続テーブルに記載 されている comp_id 情報から、パナソニックが特許出願した ida_seq 情報を取り出す 5. Ida_seq に基づき、当該特許の出願年や公開年やclaim, IPC 情報を取 り出す 2019/6/9 128
129. 2-1. NISTEP企業名辞書をSQL サーバに インポートする 2019/6/9 129 Manaba からテーブルをダウンロード, 先程と同様にSQL サーバにインストールする
130. 2-1. NISTEP企業名辞書をSQL サーバに インポートする • select * from 1_comp_name_main_tbl で, データが読み込めた ことを確認する 2019/6/9 130
131. 2-2. NISTEP企業名辞書とIIPパテントデータ ベースの接続テーブルをSQL サーバにイン ポートする • Manaba からデータをダウンロードし, SQL にインポートする • 7z ファイルなので、適宜解凍する 2019/6/9 131
132. 2-2. NISTEP企業名辞書とIIPパテントデータ ベースの接続テーブルをSQL サーバにイン ポートする • SELECT * FROM iipdb2015a.ct_dic_iip; でデータを 読み込めたか確認する 2019/6/9 132
133. 2-3. パナソニックな企業群を history_id から特定する • comp_name が”パナソ ニック” な企業の、 history_id と comp_id を 確認する • NISTEP企業名辞書メイン テーブルを使う • Comp_id = 1 • History_id = 1006752 で あることを確認 • History_id = 1006752 であ る企業を探索する 2019/6/9 133
134. 2-3. パナソニックな企業群を history_id から特定する • History_id = 1006752 である企業 を検索する • パナソニック • 松下電器産業 • 松下電工 • 松下電子工業 • パナソニック電工 • 松下冷機 • 松下通信工業 • 松下電池工業 • 松下住設機器 • パナソニックモバイルコミュニケー ションズ • パナソニックモバイル が該当することがわかる 2019/6/9 134
135. 2-3.パナソニックな企業群を特定する • パナソニックが社名に含まれ る企業を探してみる • 37社存在することを確認 • とりあえず、history_id = 1006752 な企業群を対象にす る 2019/6/9 135
136. 2-4. NISTEP企業名辞書とiip パテント データベース接続テーブルを接合する • IIP パテントデータベース接続テーブルに、NISTEP企業名辞書 メインテーブルにある history_id と comp_name を接合し, 新 しいテーブルとして保存する • 一行目に create table ct_dic_iip2 と指定し, クエリの結果を新しい テーブルに保存する 2019/6/9 136
137. 2-4. NISTEP企業名辞書とiip パテント データベース接続テーブルを接合する • データの状態を確認 • 従来の接続テーブルに、history_id と comp_name の情報が追 加されている • この段階で、パナソニックが出願した特許の出願年と数が確認 できる 2019/6/9 137
138. 2-5. IIP パテントデータベースと接合し, パナソ ニック (名寄せ済み) の特許, IPC 分類情報を抽 出する • 2-4. でつくったテーブルと, IIP パテントデータベースのap テーブルをida で接合する • Left 関数を使い, ct_dic_iip2 テーブルの ida_seq について先頭から10 文字分取り出し, それをapテーブルのida とマッチさせる • History_id=1006752 のデータを取り出す 2019/6/9 138
139. 2-5. IIP パテントデータベースと接合し, パナソ ニック (名寄せ済み) の特許, IPC 分類情報を抽 出する • 結果が表示される 2019/6/9 139
140. 2-5. IIP パテントデータベースと接合し, パナソ ニック (名寄せ済み) の特許, IPC 分類情報を抽 出する • Excelでグラフにする • 各企業体ごとの特許数 2019/6/9 140 0 5000 10000 15000 20000 25000 <1984/1/5 1985年 1987年 1989年 1991年 1993年 1995年 1997年 1999年 2001年 2003年 2005年 2007年 2009年 2011年 2013年 松下冷機 松下電池工業 松下電子工業 松下電工 松下電器産業 松下通信工業 松下住設機器 パナソニック電工
141. 2-5. IIP パテントデータベースと接合し, パナソ ニック (名寄せ済み) の特許, IPC 分類情報を抽 出する 2019/6/9 141 0 5000 10000 15000 20000 25000 30000 35000 H01L H04M H04R A61B A47K E03D G01B B65D H01P E03C G01D B28B C09D B29B G06Q B25B F23C F23Q B66B B62M F16D F27B C12N F15B C07K B28D B41C B62H E01H B67D B60C B64D B66C B27L B07B D02G D21J G10D B64G C01D G10C F22G G03D A41B G21C F16S B44B B64C
142. 2-5. IIP パテントデータベースと接合し, パナソ ニック (名寄せ済み) の特許, IPC 分類情報を抽 出する 2019/6/9 142
143. レポートのお知らせ+ 成績評価の方法
144. レポートの内容 • 概要 • Manaba にアップロードされたデータセットからひとつを選び、 Jupyter Notebook 上で解析し, 重回帰分析を行う • レポートでは • (1) 被説明変数と説明変数の関係性 • (2) R二乗項の値 • (3) VIF 値による, 多重共線性のチェック などを報告した上で, 構築したモデルについて考察を行うこと • 提出期間: 2019/6/10 15:00:00 - 2019/6/17 13:15:00 (JST) • 提出方法: Manaba のレポート提出ページに, ipynbまたはPDF 形式で提出すること • ※. Word または LaTeX で, PDF でアウトプットすること
145. レポートの内容(2) • 引用文献がある場合, 末尾に明記すること • 文字数は問わない
146. データセット (その1) • FIFA19 Datasets; クロスセクションデータ • https://www.kaggle.com/karangadiya/fifa19 FIFA 19 に収録されている フットボールプレイヤー選手 の能力値や市場価値をまとめたデータ
147. データセット(その1)
148. データセット(その2) • Kansas City Barbeque Society Competition Results https://www.kaggle.com/jaysobel/kansas-city-barbeque-society- competition-results • カンサスシティのバー ベキューイベントの データを集計したもの
149. データセット(その2)
150. 分析について • どれを説明変数/被説明変数にするかはおまかせします • サラリー and/or 市場価値 and/or 能力? • ダミー変数の作成などもおまかせします • 必要あれば, 火曜日の講義で補足します
151. 考えうる仮説 • 選手の加齢は市場価値を押し下げる • クラブの移籍は選手のサラリーを押し上げる • 国際的な認知度は選手の市場価値を押し上げる • 現在の契約終了年までの期間が短いほど、市場価値を上げる • 南米出身の選手は、欧州出身の選手に比べサラリーの水準が低 い • Etc…
152. Manaba で配布した Notebook の解説 • データを取り込み, 重回帰分析まで行った結果を Jupyter Notebook に記述して, Manaba にアップロードしました • 被説明変数と説明変数を入れ替えれば, ご自身のレポートにお 使い頂けます
153. Manaba で配布した Notebook の解説 やっていること ・Excel にしたデータをJupyter Notebook に取り込む (同じディレクトリにデータを 入れておくこと) ・シートの1枚目 (0番目) を input_sheet_df にインポートする ・input_sheet_df.head(10) で, データの 10番目までを表示する Messi や Ronaldo, Suarez などのデータ が表示されていることが確認できる
154. Manaba で配布した Notebook の解説 やっていること ・説明変数と被説明変数をそれぞれの列か ら取り出す (.iloc [行, 列]で, 行を指定せず 列のみを指定する) ・scipy.stats.lingress(説明変数, 被説明変 数) で単回帰を回す ・散布図および, 単回帰分析の結果をアウ トプットする
155. Manaba で配布した Notebook の解説 被説明変数; overall (ゲーム上での総合評価) 説明変数; age (年齢) 年齢が高くなるほど, 総合的な評価が高くなる -> おそらく, 評価の高くない選手は引退している
156. Manaba で配布した Notebook の解説 sm.OLS(被説明変数, 定数項+説明変数) で回帰分析 を行い, 結果を表として出力する 被説明変数; overall (ゲーム上での総合評価) 説明変数; age (年齢) 定数項および説明変数はそれぞれプラスに有意だが, R修正項は高くない。
157. Manaba で配布した Notebook の解説 sm.OLS(被説明変数, 定数項+説明変数) で回帰分析 を行い, 結果を表として出力する 被説明変数; wage (年俸) 説明変数; age (年齢) 定数項はマイナス, 説明変数はプラスに有意だが, R修正項はかなり高くない。
158. Manaba で配布した Notebook の解説 • plt.scatter パッケージを用い, wage (年俸)とage(年齢) をプ ロットする • 30歳までは年俸は上昇してい くが, その後下方トレンドが あることが確認できる • いくつか外れ値があることが 確認できる
159. Manaba で配布した Notebook の解説 • Seaborn パッケージで, ヒス トグラムと散布図を同時にプ ロットする • 年齢はF分布, 年俸はべき乗分 布に近いことが確認できる • 外れ値はメッシやクリスティ アーノロナウドなど.
160. Manaba で配布した Notebook の解説 • 同様に, 重回帰分析を sm.OLS パッケージを用いて 行う • 説明変数が空の行を削除する (x_list.drop… 以下) • 年齢とレピュテーション, 契 約満期までの残り年数および 評価値はプラスに有意, 定数 項はマイナスに有意, 利き足 ダミーは有意であるとはいえ ないことが確認できる
161. Manaba で配布した Notebook の解説 • VIF値を確認する (第二回の講 義参照) • いずれも10は超えてないこと が確認できる
162. Manaba で配布した Notebook の解説 • sns.pairplot を使って, 変数間 の散布図および, ヒストグラ ムを表示する • .savefig を用いて, 出力結果を 保存する
163. わかること ・年俸の分布はかなりskew ・年齢と年俸の単回帰の傾きは, 右利きと 左利きでかなり違う(メッシエフェクト?) ・年齢が高くなると, 残りの契約期間は短 くなる（自明） ・国際的なレピュテーションと, 総合的な 評価は正の関係 ・年齢と総合的な評価もやはり正の関係 (生存バイアス)
164. みなさんにレポートでやって頂きたいこ と • Python + Jupyternotebook に慣れていただく • 被説明変数と説明変数の組み合わせを考えてみる • 変数を自分で作り出す • 与えられている92の変数から, 自分で変数を作成する • ある程度説明力 (ここではとりあえずR修正2乗値) の高い誘導 形 (reduced form) のモデルを作り出す
165. レポートの評価方法 • Excellent • データおよび適切な分析手法に基づき、独自の視点で十分に考察がなされている • Good • データおよび適切な分析手法に基づき、独自の視点で考察がなされている • Fair • データに対して分析が行われているが、一般的な範囲での考察にとどまる • Bad • 考察がなされていない，あるいはデータの解析が十分に行われていない • Failed • 断りなく他者の見解を引用している，その他不正行為に相当する
166. レポートの評価基準 • 説明の順序は適切か．特に重要な項目の抜けや漏れはないか． • 原典からの引用内容に事実誤認はないか． • 誤字脱字（誤植）はないか．文法上の誤りはないか．文法ルー ルが統一されているか． • 文章は明快か．誤解を招くような内容・表現はないか． • 参照，引用のルールに準拠しているか．剽窃はないか． • 不正行為に相当する事項はないか． • 提出期限を厳守しているか．
167. 成績評価(1) • 平常レポート (40パーセント; 必須) • 講義計画に示したように、複数の回で学生にはレポートを課します。 レポートは Word/PowerPoint形式のメールあるいは, github 経由で の提出が求められます(どの方法を採用するかは、初回の講義で決定し ます)。 • レポートには、(A.) 利用したデータセットとその内容, (B.) 分析の問 い, (C.) 分析手法, (D.) 分析結果 を明記する必要があります。ページ 数や文字数は問いませんが, これらの内容が含まれており, 講義中にア ナウンスする評価手法を満たす場合, 高い得点を得ることが出来ます。 • 平常点 (10パーセント) • 本講義は実習が多く含まれており, また取り扱うデータセットや内容も 多彩です。そのため、受講者同士が協力する必要があります。こうし た受講者の態度を評価するために、平常点を設けます。
168. 成績評価(2) • 最終レポート (40パーセント; 必須) • 講義の最終回では最終レポートの報告会を行います。受講者は3名から4名か ら構成されるグループで、最終レポートの報告を行う必要があります(人数は 受講者の人数により変更される可能性があります)。最終レポートでは、プレ ゼンテーションの方法を学生は自由に選ぶことが出来ます。PowerPoint 以 外にも、Prezi などのアクティブプレゼンテーションツールや、他のプレゼ ンテーション手法を用いることが出来ます(プレゼンテーションツールについ ては、必要であれば講義内で説明する機会を設けます)。最終レポートでは、 以下の点について評価を行います。 (A.) グループ内の役割分担 (B.) データセットのユニークさおよび、それが適切に処理されているか (C.) 分析手法のユニークさおよび、それが適切に解析されているか (D.) プレゼンテーションのユニークさ (E.) 質疑応答にうまくリプライすることが出来ているか • 最終レポートの360°グループ評価 (10パーセント) • 3. の最終レポートについて、グループの自己評価および他のグループからの 評価を行います。3. で挙げた評価ポイントに基づき、グループメンバーおよ び他のグループは評価を行います。
169. 次回. データベースを用いた分析(1) 特許 データベース [座学、実習] • 知的財産研究所が提供するIIP パテントデータベース (http://www.iip.or.jp/patentdb/)に基づき、特許データを具 体例として解析の実習を行います。 • 「どの企業が最も特許を出願しているのか？」、「どのような 分野に特許を出願しているのか？」など、具体的な問いを立て た上で、問いに基づきデータを抽出することを目指します。 • 分析結果については、レポートとして提出を行う必要がありま す。
170. 特許データでわかること • どの企業が知的財産を、どのくらい生産しているか • 無形資産をどのくらい形成しているのか • 企業の研究開発がどのように行われているのか
171. 1930s 1960s 1970s 1990s 2000s 青色LEDに至るまでの研究開発プロセス: 1910-2007 (1次引用すべて導入; 上位12社明記) 1980s 引用: 清水2001
172. 次次回. (人力や Web スクレイピングやRPA で)データを集めてきて処理をするまでの長く 険しい道 [座学、実習] • データといっても、そのほとんどは実のところ定型化されてい ません。Web サイトや書籍や国会図書館から手作業でひとつひ とつ入力し、あるいは、Web スクレイピングやRPA を用いて, Web から取得する必要があります。 • データ分析は、こうした事前の作業が作業全体の60-70パーセ ントを占めています。 • こうした手続きの具体例について、座学を用いて紹介した後、 雑誌や Web に掲載されたデータを取得する手法についてご紹 介します。
173. References (for today’s lecture) • PATSTAT ONLINE (beta) • http://www.epo.org/searching/subscription/patstat-online.html • SQL Self-Study Course • http://documents.epo.org/projects/babylon/eponet.nsf/0/55df36d61f27cd b2c1257b1600323d91/$FILE/patstat_introduction_sql_en.pdf • Data Elements of PATSTAT Raw Data • http://documents.epo.org/projects/babylon/eponet.nsf/0/95da6bccf12e5 4a1c1257aa1002e2d1d/$FILE/patstat_data%20elements_v1.1.pdf • 永田晃也（２００４）知的財産マネジメント, 中央経済社 • 後藤晃、元橋一之（2005） 特許データベースの開発とイノベーショ ン, https://database.iip.or.jp/patentdb/ • 中村健太 (2015) IIP パテントデータベース ユーザーマニュアル, https://database.iip.or.jp/patentdb/readme_j_2015july.pdf
174. 参考. TXT/CSV ファイルから SQL サーバにデータをイン ポートする
175. 参考. テキストデータからSQL にデータを インポートする場合 • Create a new schema in the connected server をクリックする 2019/6/9 175
176. 参考. テキストデータからSQL にデータを インポートする場合 • Name にデータベース名 を指定 • Apply をクリックする 2019/6/9 176 ・もういちど Apply をクリックする
177. 参考. テキストデータからSQL にデータをイ ンポートする場合 • Finish をクリックする 2019/6/9 177
178. 参考. テキストデータからSQL にデータをイ ンポートする場合 • 左下の Schema からデータのインポート先のスキーマをダブル クリックする 2019/6/9 178
179. 参考. テキストデータからSQL にデータ をインポートする場合 • Table を開き, “Create Table” をクリックする 2019/6/9 179
180. 参考. テキストデータからSQL にデータを インポートする場合 • Table Name を指定する: ap • Column Name/Data Type を それぞれ指定する • Ida は int(11) • その他は text を指定 • 設定が終わったら Apply をク リックする 2019/6/9 180
181. 参考. テキストデータからSQL にデータ をインポートする場合 • Apply をクリックする • Iippatentdb2015 という データベースの中に、 ap というテーブルが 作成される 2019/6/9 181
182. 参考. テキストデータからSQL にデータを インポートする場合 • CSV/TXT ファイルからデータを読み込み, SQL にインポートする • Load data ⇒ “” で囲んでいる箇所からデータを読み込んでね • Into table ⇒ iippatentdb2015.ap にデータを収納してね • Fields terminated by ‘¥t’ ⇒ データはタブ区切りになっているからね • Ignore 1 lines ⇒ 最初の一行目は無視してね 2019/6/9 182 load data local infile "C:¥¥Users¥¥ya- hara¥¥Downloads¥¥ap¥¥ap.txt“ into table iippatentdb2015.ap Fields terminated by '¥t’ ignore 1 lines
183. THANKS yasushi.hara@r.hit-u.ac.jp