<jupyter_start><jupyter_text>基于 Surprise 的音乐推荐系统learn: - https://zhuanlan.zhihu.com/p/56834797 - https://surprise.readthedocs.io/en/stable/getting_started.html<jupyter_code>DB = "/Volumes/shared/murecom/intro/spotify/playlists.db"
DATA_COUNT = 10000  # 取多少数据出来用

import sqlite3
import pandas as pd

conn = sqlite3.connect(DB)

pt = pd.read_sql(f"SELECT * FROM playlist_tracks LIMIT {DATA_COUNT}", conn)
pt<jupyter_output><empty_output><jupyter_text>搞成 Surprise 能用的数据样式：> user 对 item 评分 (rating)<jupyter_code># 播放列表 as 用户
# 曲目 as 电影
pt = pt.rename(columns={"playlist_id": "userID", "track_id": "itemID"})
# 歌在播放列表里，就是用户给歌打了一分
pt = pt.join(pd.Series([1] * len(pt), name="rating"))

pt<jupyter_output><empty_output><jupyter_text>分一下训练集和测试集：(TODO: 其实不用分，后面不用这个，而是用 surprise.Trainset.build_testset 和 build_anti_testset)<jupyter_code># 洗牌
pt = pt.sample(frac=1)

# 分 10% 出来做测试集
_test_data_rate = 0.1
train_end_idx = int(len(pt) * (1 - _test_data_rate))

pt_train = pt[:train_end_idx].reset_index(drop=True)
pt_test = pt[train_end_idx:].reset_index(drop=True)

print(f"{pt_train.shape=}\n{pt_test.shape=}")<jupyter_output>pt_train.shape=(9000, 3)
pt_test.shape=(1000, 3)<jupyter_text>在训练集上训练，针对歌单（user）推荐：<jupyter_code># https://surprise.readthedocs.io/en/stable/getting_started.html#use-a-custom-dataset

from surprise import KNNBaseline
from surprise import Reader, Dataset

# custom dataset
reader = Reader(rating_scale=(0, 1))
train_data = Dataset.load_from_df(
    pt_train[['userID', 'itemID', 'rating']],
    reader)
trainset = train_data.build_full_trainset()

sim_options = {
    'user_based': False  # compute  similarities between items
}

# 算法、训练

algo = KNNBaseline(sim_options=sim_options)
algo.fit(trainset)<jupyter_output>Estimating biases using als...
Computing the msd similarity matrix...
Done computing similarity matrix.<jupyter_text>在测试集上测试：<jupyter_code>from surprise import Trainset

reader = Reader(rating_scale=(0, 1.0))
test_data = Dataset.load_from_df(
    pt_test[['userID', 'itemID', 'rating']],
    reader)
testset = trainset.build_testset()
algo.test(testset, verbose=False)
testset = trainset.build_anti_testset()
algo.test(testset, verbose=False)
sample = pt_train.sample(1).reset_index(drop=True)
sample
sim = algo.get_neighbors(iid=algo.trainset.to_inner_iid(sample['itemID'][0]), k=5)
algo.trainset.n_items
algo.trainset.n_users
def find_artists(track_id: str) -> list:
    ca = conn.cursor()  # get artists
    ca.execute(
        "select a.name from artists a inner join track_artists ta on a.id = ta.artist_id where ta.track_id=?",
        (track_id,))
    a = [a[0] for a in ca.fetchall()]
    ca.close()
    return a


def find_sim(track_id, k=5):
    """ 找和 track_id 曲目最相近的 k 首歌

    :return: list of tracks: len=(k+1), [0] 是输入的 track_id
    """
    sim = algo.get_neighbors(iid=algo.trainset.to_inner_iid(track_id), k=k)

    c = conn.cursor()

    track_ids = [track_id] + list(
        map(algo.trainset.to_raw_iid, sim))
    print(track_ids)

    tracks = []
    for tid in track_ids:
        c.execute(f"SELECT * FROM tracks WHERE id = '{tid}'")
        tk = c.fetchall()
        if len(tk) < 1:
            print("fetch track: len(tk) < 1:", tk)
            continue
        tk = tk[0]
        tracks.append(tk + (find_artists(tid),))
    c.close()

    return [{"id": r[4], "name": r[5], "artists": r[-1]} for r in tracks]<jupyter_output><empty_output><jupyter_text>前面的测试结果奇怪不重要，关键是得到了正确的距离关系，用 find_sim 对例如这首 `3SNczkrn6bUm6zZM8i5XDe`: Shout Baby - Ryokuoushoku Shakai 进行推荐。得到了不错的结果：<jupyter_code>find_sim(sample['itemID'][0])
c = conn.cursor()
c.execute("SELECT * FROM playlist_tracks INNER JOIN playlists p on p.id = playlist_tracks.playlist_id WHERE track_id='3SNczkrn6bUm6zZM8i5XDe'")
c.fetchall()
c = conn.cursor()
c.execute("SELECT * FROM playlist_tracks INNER JOIN playlists p on p.id = playlist_tracks.playlist_id WHERE track_id='1BqTpWakURvMLPKVn3XIRp'")
c.fetchall()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14028868067865283, 'fraction_numerical': 0.015953405925550772, 'mean_word_length': 5.076923076923077, 'pattern_counts': {'":': 5, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '323131', 'license': 'Apache-2.0', 'n_tokens_mistral': 1727, 'n_tokens_neox': 1717, 'n_words': 283, 'path': 'surprise.ipynb', 'repo_name': 'cdfmlr/murecom-intro', 'size': '3949'}