42RZdcKa

1import redis
2
3PG_CONN_STRING = 'host=172.31.255.100 dbname=softcube \
4                  user=admin password=P@$$w0rd88'
5import psycopg2
6import psycopg2.extras
7import itertools
8import math
9import operator
10from io import StringIO
11from ast import literal_eval
12
13INTERSECTIONS = 10
14
15redis_pool = redis.ConnectionPool(host='172.31.255.50', port=50131, db=0)
16redis_conn = redis.Redis(connection_pool=redis_pool)
17
18def get_categories_products():
19    productkeys = redis_conn.keys('prod:*')
20    productkeys = [key.decode('utf-8') for key in productkeys]
21    products = []
22    pipe = redis_conn.pipeline()
23    for key in productkeys:
24        if 'sim' not in key and 'tmp' not in key:
25            pipe.hget(key, 'category')
26            products.append(key.replace('prod:', ''))
27    categories = pipe.execute()
28    products_categories = {}
29    for index, prod in enumerate(products):
30        category = categories[index]
31        if category:
32            category = category.decode('utf-8')
33            if '[' in category:
34                category_list = literal_eval(category)
35                if len(category) > 1:
36                    product_cats = []
37                    for cat in category_list:
38                        if '/' not in cat:
39                            product_cats.append(cat)
40                    product_cats = ', '.join(product_cats)
41                else:
42                    product_cats = category_list
43                    product_cats = ', '.join(product_cats)
44            else:
45                product_cats = category
46            products_categories[prod] = product_cats
47    return products_categories
48
49
50def get_user_events(event):
51    keys = redis_conn.keys('{}:*'.format(event))
52    keys = [key.decode('utf-8') for key in keys]
53    print(len(keys))
54    pipe = redis_conn.pipeline()
55    for key in keys:
56        if 'http' not in key and 'user' not in key and 'prod' not in key:
57            pipe.zrange(key, 0, -1)
58    users_clicks = pipe.execute()
59    return users_clicks
60
61def similar_categories(products_category, users_clicks):
62    categories = {}
63    categories_pairs = {}
64    for user_clicks in users_clicks:
65        user_clicks = [click.decode('utf-8') for click in user_clicks]
66        user_categories = []
67        for product in user_clicks:
68            product_category = products_category.get(product, None)
69            if product_category:
70                category_cnt = categories.setdefault(product_category, 0)
71                categories[product_category] = category_cnt + 1
72                user_categories.append(product_category)
73        if len(user_categories) > 1:
74            user_categories_pairs = all_pairs(user_categories)
75            for pair in user_categories_pairs:
76                if pair[0] != pair[1]:
77                    pair_cnt = categories_pairs.setdefault(pair, 0)
78                    categories_pairs[pair] = pair_cnt + 1
79    return categories, categories_pairs
80
81
82def similar_categories_redis(products_category, users_clicks):
83    init_key = 'sim_cat'
84    sim_key = 'sim_cats'
85    pipe = redis_conn.pipeline()
86    for user_clicks in users_clicks:
87        user_clicks = [click.decode('utf-8') for click in user_clicks]
88        user_categories = []
89        for product in user_clicks:
90            product_category = products_category.get(product, None)
91            if product_category:
92                pipe.zincrby(init_key, product_category)
93                print(init_key)
94        if len(user_categories) > 1:
95            user_categories_pairs = all_pairs(user_categories)
96            for pair in user_categories_pairs:
97                if pair[0] != pair[1]:
98                    sim_key_tmp = '{}:{}'.format(sim_key, pair[0])
99                    print(sim_key_tmp)
100                    pipe.zincrby(sim_key_tmp, pair[1])
101    pipe.execute()
102    return
103
104def all_pairs(lst):
105    result = []
106    for p in itertools.permutations(lst):
107        i = iter(p)
108        result.append(list(zip(i,i))[0])
109    return result
110
111def similar_categories_dict(products_category, users_clicks):
112    categories = {}
113    categories_pairs = {}
114    for user_clicks in users_clicks:
115        user_clicks = [click.decode('utf-8') for click in user_clicks]
116        user_categories = []
117        for product in user_clicks:
118            product_category = products_category.get(product, None)
119            if product_category:
120                # category_cnt = categories.setdefault(product_category, 0)
121                # categories[product_category] = category_cnt + 1
122                user_categories.append(product_category)
123        user_categories = list(set(user_categories))
124        for category in user_categories:
125            #new two lines
126            category_cnt = categories.setdefault(category, 0)
127            categories[category] = category_cnt + 1
128            #if 'Аксессуары' not in category:
129            category_dict = categories_pairs.setdefault(category, {})
130            for similar_category in user_categories:
131                if category != similar_category:
132                    sim_cnt = category_dict.setdefault(similar_category, 0)
133                    category_dict[similar_category] = sim_cnt + 1
134                categories_pairs[category] = category_dict
135    return categories, categories_pairs
136
137def calc_similarities(categories, categories_pairs):
138    result = []
139    for category, similar_categories in categories_pairs.items():
140        category_cnt = categories.get(category, 100000000000)
141        category_result = []
142        for sim_cat, intersect in similar_categories.items():
143            sim_cat_cnt = categories.get(sim_cat, 100000000000)
144            if intersect > INTERSECTIONS:
145                similarity = intersect/math.sqrt(category_cnt*sim_cat_cnt)
146                category_result.append((sim_cat, float(similarity)))
147        result.append((category, category_result))
148    filtered_result = filter_result(result)
149    return filtered_result
150
151def filter_result(result):
152    new_result = []
153    for category, similar_list in result:
154        similar_list_new = sorted(
155            similar_list, key=operator.itemgetter(1), reverse=True)
156        new_result.append((category, similar_list_new[:20]))
157    return new_result
158
159def create_table(table_name):
160    pg_client = psycopg2.connect(PG_CONN_STRING)
161    pg_cursor = pg_client.cursor(cursor_factory=psycopg2.extras.DictCursor)
162    query = """
163    drop table if exists {};
164    create table {}(
165    category text,
166    similar_category text,
167    score numeric
168    )""".format(table_name, table_name)
169    pg_cursor.execute(query)
170    pg_client.commit()
171    pg_cursor.close()
172
173def upload_data(data, table_name):
174    create_table(table_name)
175    export_data = StringIO()
176    for category, similar_list in data:
177        for sim_cat, score in similar_list:
178            export_data.write('{}\t{}\t{}\r\n'.format(
179                category, sim_cat, score))
180    pg_client = psycopg2.connect(PG_CONN_STRING)
181    pg_cursor = pg_client.cursor(cursor_factory=psycopg2.extras.DictCursor)
182    export_data.seek(0)
183    pg_cursor.copy_from(
184        export_data,
185        table_name,
186        columns=('category', 'similar_category', 'score')
187    )
188    pg_client.commit()
189    export_data.close()
190
191if __name__ == '__main__':
192    event = 'sale'
193
194    products_category = get_categories_products()
195
196    users_clicks = get_user_events(event)
197
198    categories, categories_pairs = similar_categories_dict(
199        products_category, users_clicks)
200
201    similar_data = calc_similarities(categories, categories_pairs)
202
203    table_name = 'allo_similar_sales'
204
205    upload_data(similar_data, table_name)