pythonでSBM研究２

livedoorClipでデータマイニングエントリの続きです。なかなか分析のところまでたどり着かないのですが、今日はデータの下敷きみたいなのをSQLiteに登録します。
livedoorClipにはユーザidとurl、タグなどが格納されていますが、このユーザ、URL、タグを一意にするテーブルを下敷きとして作っておく必要が出てきました。

以下のソースを走らせてみるとタグが111,366件、URLが217,892件、ユーザ数が25,370件はいりました。これらのレコードには嵩み（count）を取ってあり、この嵩みを元に距離計算していく事でグループを振り分けたりする事が出来てくる訳です。

簡単に説明すると、AというURLにタギングされたスコアとBというURLにタギングされたスコアを距離計算して近いものをグループ化していくという作業です。

※BOOKMARKというテーブルに対するinsertをコメントアウトしてありますが、これを入れると157万件インサートされるため、SQLiteBrowserなどでは（重くて）データを見れなくなってしまいます。

# -*- coding: utf-8 -*-
import csv
import sqlite3
import re
import time
DBNAME = "ldclip.db"
def initDB():
conn = sqlite3.connect( DBNAME )
try:
conn.executescript("""CREATE TABLE BOOKMARK(
IDX INTEGER,
ID INTEGER,
URL TEXT,
CREATED TIMESTAMP,
TAGS TEXT,
PRIMARY KEY (IDX)
);""")
conn.executescript("""CREATE INDEX IDX_BOOKMARK ON BOOKMARK(
IDX, ID, URL
);""")
conn.executescript("""CREATE TABLE USERMGR(
ID INTEGER PRIMARY KEY ON CONFLICT REPLACE,
COUNT INTEGER
);""")
conn.executescript("""CREATE INDEX IDX_USERMGR ON USERMGR(
ID
);""")
conn.executescript("""CREATE TABLE URLMGR(
URL TEXT PRIMARY KEY ON CONFLICT REPLACE,
COUNT INTEGER
);""")
conn.executescript("""CREATE INDEX IDX_URLMGR ON URLMGR(
URL
);""")
conn.executescript("""CREATE TABLE TAGMGR(
TAG TEXT PRIMARY KEY ON CONFLICT REPLACE,
COUNT INTEGER
);""")
conn.executescript("""CREATE INDEX IDX_TAGMGR ON TAGMGR(
TAG
);""")
except Exception, ex:
conn.commit()
finally:
conn.close()
initDB()
conn = sqlite3.connect( DBNAME )
conn.text_factory = str
filename = "ldclip.csv"
csvfile = open( filename )
commitCnt = 0
reg = re.compile( r"\[|\]")
for row in csv.reader( csvfile ):
cur = conn.cursor()
#    conn.execute("INSERT INTO BOOKMARK( ID, URL, CREATED, TAGS ) VALUES( ?, ?, ?, ? )", ( row[ 0 ], row[ 1 ], row[ 2 ], row[ 3 ], ) )
count = cur.execute("select count(*) count from urlmgr where url=?",( row[ 1 ], ) ).fetchone()[ 0 ]
if count > 0:
conn.execute( "update urlmgr set count=count+1 where url=?", (row[ 1 ], ) )
else:
conn.execute("INSERT INTO urlMGR( url, count ) VALUES( ?, 1 )", ( row[ 1 ], ) )
count = cur.execute( "select count(*) from usermgr where id=?", (row[ 0 ], ) ).fetchone()[ 0 ]
if count > 0:
conn.execute( "update usermgr set count=count+1 where id=?", ( row[ 0 ], ) )
else :
conn.execute( "insert into usermgr( id, count ) values( ?, 1 )", ( row[ 0 ], ) )
replaceRow = row[ 3 ]
reglst = re.compile(r"\[.*?[^]]\]").findall( row[ 3 ] )
for word in reglst:
word = word.strip()
if len( word ) > 0:
word = reg.sub( "", word ).strip().lower()
count = cur.execute( "select count(*) from tagmgr where tag=?", ( word, ) ).fetchone()[ 0 ]
if count > 0:
conn.execute( "update tagmgr set count=count+1 where tag=?", ( word, ) )
else :
conn.execute("INSERT INTO TAGMGR( TAG, count ) VALUES( ?, 1 )", ( word, ) )
replaceRow = reg.sub( "", row[ 3 ].replace( word, "" ) )
wordList = replaceRow.split( " " )
for word in wordList:
word = word.strip().lower()
if len( word ) > 0:
count = cur.execute( "select count(*) from tagmgr where tag=?", ( word, ) ).fetchone()[ 0 ]
if count > 0:
conn.execute( "update tagmgr set count=count+1 where tag=?", ( word, ) )
else:
conn.execute("INSERT INTO TAGMGR( TAG, count ) VALUES( ?, 1 )", ( word, ) )
commitCnt += 1
if commitCnt % 10000 == 0:
print commitCnt
cur.close()
conn.commit()
cur.close()
conn.commit()
csvfile.close()

このデータを下敷きに、タグ分布からURLのグルーピング、ブックマーク先のURLからユーザどうしのグルーピングをとる事が出来そうです。
ただ、今回のスクリプトでもレコード投入するのに数分かかっていて、１０万件単位×１０万件単位の変数でグルーピングがちゃんと最後まで出来るかどうかはやってみないと解りません。多分、データを適宜落としていくか、中間データをストレージに落としながら最終的な分析を行っていくカタチになるのかなという感じです。
というわけで、次回は実際のクラスタリングに迫りたいと思います。