<jupyter_start><jupyter_text><jupyter_code>!pip install pymysql
!pip install git+https://github.com/coreylynch/pyFM
import pandas as pd
import pymysql
import pymysql.cursors
from functools import reduce
import numpy as np
import pandas as pd
import uuid
import datetime
from pyfm import pylibfm
from sklearn.feature_extraction import DictVectorizer
from sklearn.metrics.pairwise import pairwise_distances
np.set_printoptions(precision=3)
np.set_printoptions(suppress=True)
def get_connection():
    return pymysql.connect(host='rm-2zeqqm6994abi7b6dqo.mysql.rds.aliyuncs.com',
                               user='noone',
                               password='<PASSWORD>#$',
                               db='recsys',
                               port=3306,
                               charset ='utf8',
                               use_unicode=True)
df = pd.read_sql_query("select * from movie", get_connection())
#df_exp = df.head(1)<jupyter_output><empty_output><jupyter_text>对type, actors, director, trait四个类型数据分别构建一个频度统计字典，用于之后的one-hot编码<jupyter_code>def get_dim_dict(df, dim_name):
  type_list = list(map(lambda x:x.split('|') ,df[dim_name]))
  type_list = [x for l in type_list for x in l]
  def reduce_func(x, y):
    for i in x:
      if i[0] == y[0][0]:
        x.remove(i)
        x.append(((i[0],i[1] + 1)))
        return x
    x.append(y[0])
    return x
  l = filter(lambda x:x != None, map(lambda x:[(x, 1)], type_list))
  type_zip = reduce(reduce_func, list(l))
  #type_list = sorted(list(set(type_list)))
  #type_zip = zip(list(range(len(type_list))), type_list)
  #print(len(type_zip))
  #print(type_zip)
  type_dict = {}
  for i in type_zip:
    type_dict[i[0]] = i[1]
  return type_dict
type_dict = get_dim_dict(df, 'type')
actors_dict = get_dim_dict(df, 'actors')
director_dict = get_dim_dict(df, 'director')
trait_dict = get_dim_dict(df, 'trait')
#director_dict['吉姆·弗尔']
#actors_dict['马克·莱昂纳蒂']<jupyter_output><empty_output><jupyter_text>删除无用或冗余维度<jupyter_code>df_ = df.drop(['ADD_TIME', 'enable', 'rat', 'id', 'name'], axis=1)
#df_.head()<jupyter_output><empty_output><jupyter_text>将电影数据转换为字典列表，由于演员和导演均过万维，实际计算时过于稀疏，当演员或导演只出现一次时，标记为冷门演员或导演<jupyter_code>movie_dict_list = []
for i in df_.index:
  movie_dict = {}
  #type
  for s_type in df_.iloc[i]['type'].split('|'):
    movie_dict[s_type] = 1
  #actors
  for s_actor in df_.iloc[i]['actors'].split('|'):
    if actors_dict[s_actor] < 2:
      movie_dict['other_actor'] = 1
    else:
      movie_dict[s_actor] = 1
  #regios
  movie_dict[df_.iloc[i]['region']] = 1
  #director
  for s_director in df_.iloc[i]['director'].split('|'):
    if director_dict[s_director] < 2:
      movie_dict['other_director'] = 1
    else:
      movie_dict[s_director] = 1
  #trait
  for s_trait in df_.iloc[i]['trait'].split('|'):
    movie_dict[s_trait] = 1
  movie_dict_list.append(movie_dict)<jupyter_output><empty_output><jupyter_text>使用DictVectorizer进行向量化，做One-hot编码<jupyter_code>v = DictVectorizer()
X = v.fit_transform(movie_dict_list)
X.shape
movie_dict_list[0:1]
#pd.DataFrame(data=X.toarray(), columns=v.feature_names_).T.head(5)<jupyter_output><empty_output><jupyter_text>计算cosine相似度<jupyter_code>item_similarity = pairwise_distances(X, metric='cosine')
#item_similarity.shape<jupyter_output><empty_output><jupyter_text>测试代码，查看相似度的准确性<jupyter_code>compare_index = 3
index = 0
_max_index = 0
_max = 1
for i in item_similarity[compare_index]:
  if i < _max and i != 0:
    _max = i
    _max_index = index
  index = index + 1
print(_max_index, _max)
index_of_sim = _max_index
df.iloc[index_of_sim]
movie_dict_list[index_of_sim]
df_106 = pd.DataFrame(data=X.todense()[index_of_sim], columns=v.feature_names_)
df_0 = pd.DataFrame(data=X.todense()[compare_index], columns=v.feature_names_)
df_diff = pd.concat([df_0, df_106], axis=0, ignore_index=True)
#df_diff.reindex(['0','1'])
df_diff = df_diff.T
print(df_diff[(df_diff[0] != 0) | (df_diff[1] != 0)])
#df.iloc[index_of_sim]
#movie_dict_list[0]
#df.iloc[compare_index]
#item_similarity.shape
# li_dict = {1:0.1, 2:0.2, 5:0.4, 8:0.3}
# min(li_dict.values())
# dict(filter(lambda x:x[1] != 0.4 ,li_dict.items()))
df_sim = pd.DataFrame(data=item_similarity)
item_similarity.shape[0]<jupyter_output><empty_output><jupyter_text>最相似的前200个电影<jupyter_code>df_sim.nsmallest(200, 6600)[6600].to_dict().items()
#df_sim.head(1)<jupyter_output><empty_output><jupyter_text>插入一条基于内容推荐的电影关系数据，即，将基于相似度计算得出的电影插入数据库中的ibmovie表，此函数中主要参数分别代表：原电影ID，与该电影类似的推荐电影ID，与该电影类似的推荐电影评分，相似度等 ibmovie表：用于对外提供一个电影的相似电影集合<jupyter_code>def insert_one_ibmovie(id_,
                      movieid,
                      recmovieid,
                      recmovierat,
                      simrat,
                      time,
                      enable, connection):
  sql = 'insert into ibmovie (id, movieid, recmovieid, recmovierat, simrat, time, enable) values (\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\')' % (id_, movieid, recmovieid, recmovierat, simrat, time, enable)
  #print(sql)
  try:
    with connection.cursor() as cursor:
        cout=cursor.execute(sql)
  except Exception as e:
    print('exception:'+str(e))
    try:
      connection.close()
    except:
      pass<jupyter_output><empty_output><jupyter_text>Test code<jupyter_code># _conn = get_connection()
# insert_one_ibmovie(uuid.uuid4(), '1', '2', '6', '0.1', datetime.datetime.now(), '1', _conn)
# _conn.commit()
# _conn.close()
# df_sim.nsmallest(10, 0)[0]<jupyter_output><empty_output><jupyter_text>辅助函数，统计在ibmovie表中一个电影的相似电影数量<jupyter_code>def get_recmovie_cnt_by_movieid(movieid, connection):
  sql = 'select count(*) from ibmovie group by movieid having movieid=\'%s\'' % movieid
  try:  
    with connection.cursor() as cursor:
      cout=cursor.execute(sql)
      if cout == 0:
        return 0
      return cursor.fetchone()[0]  
  except Exception as e:
    print('exception@get_recmovie_by_movieid:'+str(e))
    try:
      connection.cursor().close()
      connection.close()
      print('closed')
    except Exception as e1:
      print('exception1@get_recmovie_by_movieid:' + str(e1))
    connection = get_connection()
cnt = get_recmovie_cnt_by_movieid('10746430_', get_connection())
print(cnt)
df.iloc[2850]
start_index = 2849
start_index = 2961<jupyter_output><empty_output><jupyter_text>离线处理ibmovie表，当有新的电影出现时，增量处理逻辑如下（这部分逻辑在recsys_core中实现）：* 1.新电影的相似电影集合，即生成200个与新电影的相似电影* 2.增量处理原有电影与新电影的相似度，每一个原有电影与新电影计算相似度，如果相似度大于某个原有电影的相似电影集合中相似度最小的那个，则插入新电影作为某原有电影的推荐电影。<jupyter_code>def process_offline_compute_by_cosdis(rec_per_num):
  rec_per_num = rec_per_num + 1
  connection = get_connection()
  for i in range(start_index, item_similarity.shape[0]):
    df_sim_p = df_sim.nsmallest(rec_per_num, i)
    df_sim_p = df_sim_p[i]
    movie_id = df.iloc[i]['id']
    recmovie_cnt = get_recmovie_cnt_by_movieid(movie_id, connection)
    if recmovie_cnt == 200:
      print('org...')
      continue
    print('new...')
    time_now = datetime.datetime.now()
    for rec_movie_item in df_sim_p.to_dict().items():
      if rec_movie_item[0] != i:
        rec_movie_index = rec_movie_item[0]
        rec_movie_sim = rec_movie_item[1]
        rec_movie_id = df.iloc[rec_movie_index]['id']
        rec_movie_rat = df.iloc[rec_movie_index]['rat']
        insert_one_ibmovie(id_=uuid.uuid4(), movieid=movie_id, recmovieid=rec_movie_id, recmovierat=rec_movie_rat, simrat=rec_movie_sim, time=time_now, enable='1', connection=connection)
    connection.commit()
process_offline_compute_by_cosdis(rec_per_num=200)
# df_sim_3_dict = df_sim_3[4].to_dict()
# for i in df_sim_3_dict.items():
#   print(i)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14040870138431114, 'fraction_numerical': 0.01964403427818062, 'mean_word_length': 4.54127100073046, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 50, 'https://': 1, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '615659', 'license': 'MIT', 'n_tokens_mistral': 3317, 'n_tokens_neox': 3317, 'n_words': 448, 'path': 'exp/3_recbycontent_v2.ipynb', 'repo_name': 'CJuanvip/liulu', 'size': '7583'}