Notes

データ処理

  • 前回、私たちはHogwarts家の好みの調査を調べ、PythonでCSVファイルからデータを集計しました。
  • 今回は、あなたのお気に入りのテレビ番組とそのジャンルについて、さらにデータを集めます。
  • 何百もの回答があれば、ウェブベースの表計算アプリケーションであるGoogle Sheetsで回答を見て、行と列にデータを表示することができます。
  • 「コメディ (Comedy)」のように、1つのジャンルが選択されている回答もあれば、「犯罪、ドラマ (Crime, Drama)」のように、複数のジャンルがあり、1つのセルに複数のジャンルが表示されており、カンマで区切られている回答もあります。
  • Google Sheets、AppleのNumbers、Microsoft Excelなどのスプレッドシートアプリを使えば、以下のことができます。
    • データをソートする。
    • データを行と列に格納する。各追加エントリは行で、各エントリのプロパティ (タイトルやジャンルなど) は列です。
    • 列を選択して、データのスキーマ (フォーマット) を事前に決定する。
  • データベースは、データを格納するファイルまたはプログラムです。
  • CSVファイルはフラットファイル・データベースで、各列のデータはカンマで区切られ、各行は新しい行に表示され、単純にファイルとして保存されます。
    • CSVの一部のデータにカンマ自体が含まれている場合は、混乱を防ぐため、通常は文字列として引用符で囲まれます。
    • 表計算プログラムの数式と計算は、プログラム自体に組み込まれます。CSVファイルに保存できるのは生の静的な値のみです。
  • 「ファイル」 > 「ダウンロード」 を使用してスプレッドシートからデータを含むCSVファイルをダウンロードし、ファイルツリーにドラッグアンドドロップしてIDEにアップロードし、スプレッドシートのデータと一致するカンマ区切りの値を持つテキストファイルであることを確認します。

クリーニング

  • まず、favorites.pyを書き、ライブラリと抽象化のためのツールとしてCではなくPythonを選択します。
import csv

with open(Favorite TV Shows - Form Responses 1.csv) as file:

    reader = csv.reader(file)
    next(reader)
    for row in reader:
        print(row[1])
  • ファイルを開き、Pythonでwitキーワードを使用して各行のタイトルを出力できることを確認します。このキーワードを使用すると、ファイルのスコープが終了した後、インデントに基づいてファイルが閉じられます。
    • openはデフォルトで読み取りモードを使用しますが、コードで明確にするために、rを明示的に追加します。
    • csvライブラリには、ここで使われている変数readerを作成するreader関数があります。
    • 最初の行はヘッダ行であるため、nextを呼び出して最初の行をスキップし、ループを使用して各行の2番目の列 (タイトル) を出力します。
  • これを改善するために、DictReaderという辞書リーダーを使用して、各行から辞書を作成し、各列に名前でアクセスできるようにします。また、この場合はヘッダ行をスキップする必要はありません。DictReaderが自動的にヘッダ行を使用するからです。
import csv

with open("Favorite TV Shows - Form Responses 1.csv", "r") as file:
    reader = csv.DictReader(file)

    for row in reader:
        print(row["title"])
  • CSVの最初の行には列の名前があるため、データの各列にもラベルを付けることができます。
  • ここで、回答に含まれるすべての固有のタイトルを見てみましょう。
import csv

titles = set()

with open("Favorite TV Shows - Form Responses 1.csv", "r") as file:
    reader = csv.DictReader(file)

    for row in reader:
        titles.add(row["title"])

for title in titles:
    print(title)
  • titlesというセットを作成し、各行のタイトルの値を追加します。セットに対してaddを呼び出すと、重複が自動的にチェックされ、一意の値のみが存在することが確認されます。
    • 次に、forループを使用してセット内の要素を繰り返し処理し、各要素を出力します。
  • タイトルをソートするには、ループをfor title in sorted(titles)に変更すれば、繰り返し処理を行う前にセットをソートすることができます。
  • 大文字と小文字または句読点が異なる場合は、タイトルが異なると見なされるので、すべて大文字のタイトルをtitles.add(row["title"].upper())で追加して、タイトルをクリーンアップします。
  • 前後のスペースも削除する必要があるので、titles.add(row["title"].strip().upper()) を追加してタイトルから空白を取り除き、そして大文字に変換します。
  • これでデータが標準化され、タイトルのリストはより見やすくなっています。

カウント

  • セットの代わりに辞書を使用して、各タイトルの表示回数をカウントできます。キーはタイトル、値は各タイトルの表示回数をカウントする整数です。
import csv

titles = {}

with open("Favorite TV Shows - Form Responses 1.csv", "r") as file:
    reader = csv.DictReader(file)

    for row in reader:
        title = row["title"].strip().upper()
        if title not in titles:
            titles[title] = 0
        titles[title] += 1

for title in sorted(titles):
    print(title, titles[title])
  • ここでは、まずタイトルを見たことがないかどうかを確認します (not in titlesかどうか) 。その場合は初期値を0に設定し、そのたびに値を1ずつ安全に増やすことができます。
    • 最後に、辞書のキーと値をprintの引数として渡すことで出力することができます。
  • ループを次のように変更することで、ディクショナリ内の値でソートできます。
...
def f(title):
    return titles[title]

for title in sorted(titles, key=f, reverse=True):
...
  • 関数fを定義し、titles[title]を使って辞書内のタイトルのカウントを返します。sorted関数は、その関数をキーとして辞書の要素をソートします。また、reverse=Trueを渡すと、最小値から最大値ではなく、最大値から最小値にソートされます。
    • つまり、これで最も人気のある番組が表示されます。
  • 実際には、次の構文を使用して、同じ行で関数を定義できます。
for title in sorted(titles, key=lambda title: titles[title], reverse=True):
  • 名前を持たず、引数をいくつか取り、すぐに値を返すラムダ (匿名関数) を渡します。

検索

  • タイトルを検索してその人気を報告するプログラムを書くことができます。
import csv

title = input("Title: ").strip().upper()

with open("Favorite TV Shows - Form Responses 1.csv", "r") as file:
    reader = csv.DictReader(file)

    counter = 0
    for row in reader:
        if row["title"].strip().upper() == title:
            counter += 1

print(counter)
  • ユーザに入力を求め、CSVファイルを開きます。1つのタイトルだけを検索するので、1つの変数counterを増分します。
    • 各行をチェックしながら、入力とデータの両方を標準化した後、一致をチェックします。
  • すべての行を見る必要があるので、この実行時間はO(n)です。

リレーショナル・データベース

  • リレーショナル・データベースは、最終的にはファイルにデータを保管するプログラムですが、データをより効率的に検索および保管できるようにデータ構造が追加されています。
  • もう1つのプログラミング言語であるSQL ( 「sequel」 のように発音します) 、Structured Query Languageを使用すると、データを格納するスプレッドシートのような、多くのリレーショナル・データベースおよびそのテーブルとやり取りすることができます。
  • ここではSQLをサポートする多数の使用可能なプログラムの1つであるSQLiteという一般的なデータベース・プログラムを使用します。その他のデータベース・プログラムには、Oracle Database、MySQL、PostgreSQL、Microsoft Accessなどがあります。
  • SQLiteは、データを効率的に表現する0と1でバイナリファイルに保存します。データのテーブルは、コマンドラインプログラムsqlite3を使用して操作します。
  • CS50 IDEでいくつかのコマンドを実行して、CSVファイルを 「show」 というテーブルにインポートします。
~/ $ sqlite3
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .mode csv
sqlite> .import 'Favorite TV Shows (Responses) - Form Responses 1.csv' shows
  • CSVファイルの行に基づいて、SQLiteはデータと列を持つテーブルをデータベースに作成します。
    • SQLiteをCSVモードに設定し、.importコマンドを使用してファイルからテーブルを作成します。
  • データを操作する場合、一般的にリレーショナル・データベースでサポートされる4つのタイプの操作が必要です。
    • CREATE作成
    • READ読取
    • UPDATE更新
    • DELETE削除

SQL

  • SQLでは、これらの各操作を実行するコマンドは次のとおりです。
    • CREATEINSERT
      • たとえば、新しいテーブルを作成するには、CREATE TABLE table (column type, ...); とします。ここで、tableは新しいテーブルの名前で、columnは列の名前とその型です。
    • SELECT
      • SELECT column FROM table;
    • UPDATE
    • DELETE
  • 新しいテーブルのスキーマを.schemaで確認できます。
sqlite> .schema
CREATE TABLE shows(
  "Timestamp" TEXT,
  "title" TEXT,
  "genres" TEXT
);
  • .importがリストされているCREATE TABLE ...コマンドを使用して、showsというテーブルを作成していることがわかります。このテーブルでは、列名がCSVのヘッダー行から自動的にコピーされ、型はテキストと見なされます。
  • 次のコマンドを使用して列を選択できます。
sqlite> SELECT title FROM shows;
title
...
"Madam Secretary"
"Game of Thrones"
"Still Game"
  • SQLキーワードは慣習的に大文字化されており、行のタイトルはCSVからの順序で出力されます。
    • SELECT Timestamp, title FROM shows;で複数のカラムを選択することもできます (TimestampはCSVで大文字にされています) 。またはSELECT * FROM shows;ですべての列を表示できます。
  • SQLは、データのカウントとサマリに使用できる多くの関数をサポートしています。
    • AVG
    • COUNT
    • DISTINCT: 重複のない個別の値を取得
    • LOWER
    • MAX
    • MIN
    • UPPER
  • 以前と同じようにタイトルをクリーンアップし、大文字に変換して一意の値のみを出力できます。
sqlite> SELECT DISTINCT(UPPER(title)) FROM shows;
title
...
"GREY'S ANATOMY"
"SCOOBY DOO"
"MADAM SECRETARY"
  • クエリを変更する (clauses) を追加することもできます。
    • WHERE: 厳密な条件での結果の一致
    • LIKE: あいまいな条件での結果の一致
    • ORDER BY: 何らかの方法で結果を並べ替える
    • LIMIT: 結果数の制限
    • GROUP BY: 何らかの方法で結果をグループ化する
  • タイトルで行をフィルタしてみましょう。
sqlite> SELECT title FROM shows WHERE title = "The Office";
title
...
"The Office"
"The Office"
"The Office"
  • しかし、他にも検索したいエントリがあるので、以下を使用します。
sqlite> SELECT title FROM shows WHERE title LIKE "%Office%";
title
...
office
"The Office"
"the office "
"The Office"
  • 文字%は、0個以上の他の文字のプレースホルダです。
  • タイトルで並べ替えできます。
sqlite> SELECT DISTINCT(UPPER(title)) FROM shows ORDER BY UPPER(title);
...
X-FILES
"ZETA GUNDAM"
"ZONDAG MET LUBACH"
  • 同じタイトルをグループ化して、表示される回数を数えることもできます。
sqlite> SELECT UPPER(title), COUNT(title) FROM shows GROUP BY UPPER(title);
...
"THE OFFICE",23
...
"TOP GEAR",1
...
"TWIN PEAKS",4
...
  • 回数で並べ替えできます。
sqlite> SELECT UPPER(title), COUNT(title) FROM shows GROUP BY UPPER(title) ORDER BY COUNT(title);
...
"THE OFFICE",23
FRIENDS,26
"GAME OF THRONES",33
  • 最後にDESC を追加すると、結果が降順で表示されます。
  • LIMIT 10を追加すると、上位の10行が表示されます。
sqlite> SELECT UPPER(title), COUNT(title) FROM shows GROUP BY UPPER(title) ORDER BY COUNT(title) DESC LIMIT 10;
UPPER(title),COUNT(title)
"GAME OF THRONES",33
FRIENDS,26
"THE OFFICE",23
...
  • 最後に、各タイトルから空白文字を削除し、その関数をネストします。
sqlite> SELECT UPPER(TRIM(title)), COUNT(title) FROM shows GROUP BY UPPER(TRIM(title)) ORDER BY COUNT(title) DESC LIMIT 10;
UPPER(title),COUNT(title)
"GAME OF THRONES",33
FRIENDS,26
"THE OFFICE",23
...
  • 最後に、.save shows.dbを使用してデータをファイルに保存します。このコマンドを実行すると、IDEに保存内容が表示されます。
  • 人気のある番組を表示するのに、Pythonでは何十行ものコードが必要でしたが、今では (長い) 1行のSQLしか必要としません。
  • SQLiteのコマンドラインインターフェースを使っていますが、SQLクエリを使ってより視覚的に結果を見ることができるグラフィカルなプログラムもあります。

テーブル

  • genres列には同じフィールドに複数のジャンルがあるので、LIKEを使用して、特定のジャンルを含むすべてのタイトルを取得します。
sqlite> SELECT title FROM shows WHERE genres LIKE "%Comedy%";
...
  • しかし、ジャンルはまだカンマ区切りのリストとして保存されており、あまりきれいではありません。例えば、 「音楽 (Music)」 と 「ミュージカル (Musical)」 の両方のジャンルが含まれている場合、 「音楽(Music)」 のジャンルだけでタイトルを選択することは困難です。
  • INSERT INTO table (column, ...)を使用して、テーブルにデータを手動で挿入できます。
  • VALUES(value, ...);.
    • たとえば、次のように指定できます。
sqlite> INSERT INTO shows (Timestamp, title, genres) VALUES("now", "The Muppet Show", "Comedy, Musical");
  • UPDATE table SET column = value WHERE condition;で行を更新することができます。たとえば、
sqlite> UPDATE shows SET genres = "Comedy, Drama, Musical" WHERE title = "The Muppet Show";
  • DELETE FROM table WHERE condition; : で条件に一致する全ての行を削除することもできます。:
sqlite> DELETE FROM shows WHERE title LIKE "Friends";
  • 次に、SQLを使用してCSVデータをテーブルにインポートする独自のPythonプログラムを次のように設計して作成します。
table named shows with columns id and title, table named genre with columns show_id and genre, with arrow from show_id to id
  • この設計では、データを正規化することで、冗長性を減らして単一の真となるデータソースを確保します。
    • たとえば、showsという名前のテーブルがあり、idtitleを持っています。また、各番組のidを使用してジャンルと番組を関連付ける別のテーブルgenreもあります。番組のtitleを複数回保存する必要はありません。
    • 番組を複数のジャンルに関連付けるために、genreテーブルに複数の行を追加することもできます。
  • SQLにも、データの保存に使用する領域の量を最適化するための独自のデータ型があり、テーブルを手動で作成するときに指定する必要があります。
    • BLOB: 「バイナリ・ラージ・オブジェクト」 、ファイルを表すであろう生のバイナリ・データ
    • INTEGER BLOB
    • NUMERIC: 日付や時刻のように、数字に似ているが完全な数字とは言えないもの
    • REAL: 浮動小数点値
    • TEXT: 文字列など
  • 列には、追加の属性を指定することもできます。
    • NOT NULL:何らかの値が必要であることを指定します。
    • UNIQUE:そのカラムの値がテーブル内のすべての行で一意でなければならないことを意味します。
    • PRIMARY KEY: 上記のid列のように、各行を一意に識別するために使用されます。
    • FOREIGN KEY:上記のshow_id列のように、他のテーブルの列を参照します。
  • CS50ライブラリのSQL機能を使用してクエリを簡単に作成できますが、Python用のライブラリは他にもあります。
import csv

from cs50 import SQL

open("shows.db", "w").close()
db = SQL("sqlite:///shows.db")

db.execute("CREATE TABLE shows (id INTEGER, title TEXT, PRIMARY KEY(id))")
db.execute("CREATE TABLE genres (show_id INTEGER, genre TEXT, FOREIGN KEY(show_id) REFERENCES shows(id))")

with open("Favorite TV Shows - Form Responses 1.csv", "r") as file:
    reader = csv.DictReader(file)
    for row in reader:
        title = row["title"].strip().upper()

        id = db.execute("INSERT INTO shows (title) VALUES(?)", title)

        for genre in row["genres"].split(", "):
            db.execute("INSERT INTO genres (show_id, genre) VALUES(?, ?)", id, genre)
  • まず、shows.dbファイルを開いて閉じ、ファイルが作成されていることを確認します。
    • 次に、SQLで作成したデータベースを格納するdb変数を作成します。このデータベースは、作成したばかりのデータベース・ファイルを受け取ります。
    • 次は、SQLコマンドを文字列として記述し、db.executeを呼び出して実行します。ここでは、上記で設計したとおりに2つのテーブルを作成し、各テーブルに必要な各列の名前、タイプ、およびプロパティを示します。
    • これで元のCSVファイルを1行ずつ読み、タイトルを取得し、db.executeを使って各行に対してINSERTコマンドを実行できます。ここで、プレースホルダ?を使用し、置換する変数を渡します。その後、行ごとに自動的に作成されたidを返します。これは、このidを主キーとして宣言したためです。
    • 最後に、各行のgenre文字列をカンマで分割し、show_idのIDを使用してそれぞれをgenresテーブルに挿入します。
  • このプログラムを実行すると、各番組のIDとタイトル、また番組のIDとジャンルが表示できます。
sqlite> SELECT * FROM shows;
...
511 | MADAM SECRETARY
512 | GAME OF THRONES
513 | STILL GAME
sqlite> SELECT * FROM genres;
...
511 | Drama
512 | Action
512 | Adventure
512 | History
512 | Thriller
512 | War
513 | Comedy
  • id 512の番組 「GAME OF THRONES」 には、5つのジャンルが関連付けられています。
  • たとえば、すべてのミュージカルを検索するには、次のコマンドを実行します。
sqlite> SELECT show_id FROM genres WHERE genre = "Musical";
...
422
435
468
  • このクエリをネストして、show IDのリストからタイトルを取得できます。
sqlite> SELECT title FROM shows WHERE id IN (SELECT show_id FROM genres WHERE genre = "Musical");
title
BREAKING BAD
...
THE LAWYER
MY BROTHER, MY BROTHER, AND ME
  • 括弧内の最初のクエリが最初に実行され、その結果が外部クエリで使用されます。
  • showsのすべての行には「THE OFFICE」 というタイトルの番組と、関連するすべてのジャンルを見つけることができます。
sqlite> SELECT DISTINCT(genre) FROM genres WHERE show_id IN (SELECT id FROM shows WHERE title = "THE OFFICE") ORDER BY genre;
genre
...
Comedy
Documentary
...
  • 3番目のテーブルを追加すると、テーブルのデザインをさらに改善できます。
  • 各ジャンル名が一度だけ保存され、新しいテーブルであるshows_genresという結合テーブルには、ジャンルにリンクする外部キーが含まれます。これは多対多の関係であり、1つの番組が多くのジャンルを持つことができ、1つのジャンルが多くの番組に属することができます。
    • ジャンル名を変更する必要がある場合、複数行ではなく1行を変更するだけで済みます。
  • ここで、カラムにサブタイプがあることがわかりました。
    • INTEGER
      • smallint:より少ないビット数
      • integer
      • bigint: より多いビット数
    • NUMERIC
      • boolean
      • date
      • datetime
      • numeric(scale,precision): 固定桁数の数値
      • time
      • timestamp
    • REAL
      • real
      • double precision: 2倍のビット数
    • TEXT
      • char(n): 固定された数の文字
      • varchar(n): 固定されていない数の文字, 最大nまで
      • text, 文字数制限がない文字列

IMDb

  • IMDb (Internet Movie Database) では、データセットをTSV (タブ区切り値) ファイルとしてダウンロードできます。
  • このようなデータセットを1つインポートすると、次のスキーマを持つテーブルが表示されます。
  • genres テーブルはgenres 列が繰り返されるため重複がありますが、stars とwriters テーブルはpeople内の行を結合し、それらの関係に基づいてテーブルを表示します。
  • SELECT COUNT(*) FROM shows;とすると、テーブルには15万以上の番組がありますが、大量のデータではインデックスを使用できます。インデックスを使用すると、データベースプログラムに追加のデータ構造を作成して、対数時間で検索およびソートできるようになります。
sqlite> CREATE INDEX title_index ON shows (title);
  • これらのデータ構造は、Cで見たバイナリツリーのように一般的にBツリーであり、ノードは線形よりも高速に検索できるように構成されています。
    • インデックスの作成には時間がかかりますが、作成後はクエリをより速く実行できます。
  • データが異なるテーブルに分かれている場合、JOIN コマンドを使用してクエリ内でデータを組み合わせることができます。
sqlite3> SELECT title FROM people
    ...> JOIN stars ON people.id = stars.person_id
    ...> JOIN shows ON stars.show_id = shows.id
    ...> WHERE name = "Steve Carell";
...
The Morning Show
LA Times: the Envelope
  • JOIN 構文を使用すると、外部キーに基づいてテーブルを仮想的に結合し、そのカラムを1つのテーブルのように使用できます。
  • インデックスを作成した後は、JOINコマンドもより高速に実行されます。

いくつかの問題

  • SQLインジェクション攻撃と呼ばれるもので、誰かがデータベース上で実行する入力に自分のコマンドを注入 (インジェクト) できるというものです。
  • ユーザをログに記録するクエリはrows = db.execute("SELECT * FROM users WHERE username = ? AND password = ?"のようになります。?プレースホルダを使用していることで、SQLライブラリが入力をエスケープしたり、危険な文字がコマンドの一部として解釈されないようにします。
  • これとは対照的に、次のような書式設定された文字列のSQLクエリがあるとします。
f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"
  • ユーザーがmalan@harvard.edu'--と入力すると、クエリは次のようになります。
 f"SELECT * FROM users WHERE username = 'malan@harvard.edu'--' AND password = '{password}'"
  • この問い合わせは実際にはusername = 'malan@harvard.edu'という行を、パスワードをチェックせずに選択します。これは--が行の残りをSQLのコメントに変えるからです。
  • データベースのもう1つの問題は、マルチスレッド環境のコードが各スレッドで結合されたり、混合されたりする競合状態です。
  • 例えば、人気のある投稿にはたくさんの 「いいね!」 が付きます。サーバは、likeの数を増やし、データベースに現在のlikeの数を問い合わせ、1を追加し、データベースの値を更新しようとするでしょう。
rows = db.execute("SELECT likes FROM posts WHERE id = ?", id);
likes = rows[0]["likes"]
db.execute("UPDATE posts SET likes = ? WHERE id = ?", likes + 1, id);
  • しかし、複数のサーバを持つアプリケーションの場合、それぞれが同時にlikeを追加しようとするかもしれません。2つの異なるユーザーに応答する2つのサーバーでは、コードの最初の行が各サーバーで同時に実行されるため、最初のlikeの数が同じになる場合があります。そうすると、2つの別々の増分があったとしても、両方が同じ新しいlikeの数を設定してしまいます。
  • この問題を解決するために、SQLはトランザクションをサポートしています。トランザクションでは、次のような構文を使用して、特定のアクションが同時に発生することが保証されるように、データベース内の行をロックできます。
    • BEGIN TRANSACTION
    • COMMIT
    • ROLLBACK
  • たとえば、上記の問題は次の方法で解決できます。
db.execute("BEGIN TRANSACTION")
rows = db.execute("SELECT likes FROM posts WHERE id = ?", id);
likes = rows[0]["likes"]
db.execute("UPDATE posts SET likes = ? WHERE id = ?", likes + 1, id);
db.execute("COMMIT")
  • データベースはその間の全ての問い合わせが同時に実行されることを保証します。
  • もう1つの例は、2人のルームメイトが寮で冷蔵庫を共有している場合でしょう。最初のルームメートが帰ってきて、冷蔵庫に牛乳がないのを見ました。そして、最初のルームメイトが牛乳を買いに店に行き、店にいる間に2人目のルームメイトが家に帰ってきて、牛乳がないのを見て、また別の店に牛乳を買いに行きます。すると後で、冷蔵庫に牛乳が2つ入っています。
  • この問題を解決するには、冷蔵庫をロックして、両方のルームメイトが戻ってくるまで牛乳があるかどうかチェックできないようにします。