import pandas as pd import numpy as np import requests,io from bs4 import BeautifulSoup import urllib.parse from datetime import datetime from datetime import timedelta import streamlit as st import uuid import warnings warnings.filterwarnings('ignore') # Importing Custom Modules from folder_management import create_folder, remove_files_folder from sqlite_database import create_insert_table, query_table ''' This Module will, for a particular keyword and publisherid combo query data from bizrate.com store it in bizrate.db. And then generate Top 50 recommendation from it and Store it in RecSysData.db ''' # Function Definitions def tag_to_list(tag_name, soup): if tag_name == 'Image': tag_list = soup.find_all(tag_name, {'xsize': '400'}) else: tag_list = soup.find_all(tag_name) return pd.Series([item.text for item in tag_list]) def loadRSS(filepath, keyword, publisherid): url = filepath resp = requests.get(url) with open('bizrate/' + keyword + "_" + publisherid + '.xml', 'wb') as f: f.write(resp.content) def remove_dollar_comma(row): try: row = row.split('$')[1] row = row.replace(',', '') row = float(row) except AttributeError: return row return row def url_decode(url): return urllib.parse.unquote(url.split('?t=')[1]) def query_bizrate(keyword, publisherid='726189', search_results=500): file_path = 'http://catalog.bizrate.com/services/catalog/v1/api/product?apiKey=36874cbf9d18804f1a3d23b1a774dcce' \ '&publisherId={}&placementId=1&categoryId=&keyword={' \ '}&productId=&productIdType=&offersOnly=true&merchantId=&brandId=&biddedOnly=&minPrice=&maxPrice' \ '=&minMarkdown=&zipCode=&freeShipping=&start=0&results={' \ '}&startOffers=0&resultsOffers=0&sort=relevancy_desc&attFilter=&attWeights=&attributeId' \ '=&resultsAttribute=10&resultsAttributeValues=10&showAttributes=&showProductAttributes' \ '=&minRelevancyScore=1000&maxAge=&showRawUrl=&showUnitPricing=&useSecureImageDomain' \ '=&useSecureLinkDomain=&reviews=none&format=xml&callback=callback'.format(publisherid, keyword, search_results) loadRSS(file_path, keyword, publisherid) # stores data as xml file in RecSysData Folder with open('bizrate/' + keyword + "_" + publisherid + '.xml', 'r', errors='ignore') as f: file = f.read() soup = BeautifulSoup(file, 'xml') # cols = ['title', 'Brand', 'mature', 'description', 'manufacturer', 'url', 'Image', 'Skus', 'upc', 'gtin', 'ean13', # 'detailUrl', # 'price', 'originalPrice', 'markdownPercent', 'totalPrice', 'bidded', 'merchantProductId', # 'merchantName', 'merchantLogoUrl', 'condition', 'stock', 'shipAmount', 'shipType', 'relevancy'] cols = ['title', 'Brand', 'url', 'Image', 'Skus', 'price', 'originalPrice', 'markdownPercent', 'totalPrice', 'condition', 'stock', 'relevancy'] df_product = pd.DataFrame({'title': pd.Series(dtype='object'), 'Brand': pd.Series(dtype='object'), 'url': pd.Series(dtype='object'), 'Image': pd.Series(dtype='object'), 'Skus': pd.Series(dtype='object'), 'price': pd.Series(dtype='object'), 'originalPrice': pd.Series(dtype='object'), 'markdownPercent': pd.Series(dtype='object'), 'totalPrice': pd.Series(dtype='object'), 'condition': pd.Series(dtype='object'), 'stock': pd.Series(dtype='object'), 'relevancy': pd.Series(dtype='object')}) for col in cols: df_product[col] = tag_to_list(col, soup) df_product.dropna(subset=['Skus'], inplace=True) df_product['Skus'] = df_product['Skus'].astype('object') # Inserting the DataFrame into the bizrate DB into the corresponding table create_insert_table(db_name='bizrate', table_name=keyword + "_" + publisherid, df=df_product) def generate_clickreport(api_key='36874cbf9d18804f1a3d23b1a774dcce', publisher_id='726189'): today = datetime.utcnow().date() previous_day = today - timedelta(days=5) start_date = previous_day.strftime("%Y-%m-%d") end_date = today.strftime("%Y-%m-%d") list_of_dates = [d.strftime('%Y-%m-%d') for d in pd.date_range(start=start_date, end=end_date, freq='D')] # print(list_of_dates) df_click = pd.DataFrame({}, columns=['report_date', 'publisher_id', 'campaign_id', 'placement_id', 'rid', 'clicks', 'earnings', 'cpc']) for reportDate in list_of_dates: url = 'https://publisher-api.connexity.com/api/reporting/getClickReport?publisherId={}&reportDate={}&apiKey={}'.format( publisher_id, reportDate, api_key) # print(url) urlData = requests.get(url).content rawData = pd.read_csv(io.StringIO(urlData.decode("utf-8"))) df_click= pd.concat([df_click, rawData]) # Vertical Stacking # Data Transformation df_click[['keyword', 'Skus']] = df_click['rid'].str.split("_", expand=True) df_click = df_click[['report_date', 'publisher_id', 'campaign_id', 'placement_id', 'rid', 'keyword', 'Skus', 'clicks', 'earnings', 'cpc']] create_insert_table(db_name='clickReport', table_name='clickReport', df=df_click) def recommend(keyword, publisherid='726189', campaign_id='test_campaign', relevancy_filter=False, price_filter=True, discount_filter=False, condition_filter='NEW', stock_filter='IN', n_rec=50): # Querying required data from the bizrate database df_product = query_table(db_name='bizrate', table_name=keyword + "_" + publisherid) df_product.to_csv(keyword + "_" + publisherid + '.csv', index=False) '''df_sku = pd.read_excel('df_sku.xlsx') sku_list = list(df_product['Skus'].unique()) df_session = pd.read_excel('df_session.xlsx') session_id = list(df_session['ID'])[-1] for sku in sku_list: df_row = pd.DataFrame({'SessionID': session_id, 'Keyword': selected_keyword, 'Skus': sku, 'Count': 0}, index=[0]) df_sku = pd.concat([df_sku, df_row], ignore_index=True) df_sku.to_excel('df_sku.xlsx', index = False)''' df_product['price'] = df_product['price'].map(remove_dollar_comma) df_product['originalPrice'] = df_product['originalPrice'].map(remove_dollar_comma) df_product['totalPrice'] = df_product['totalPrice'].map(remove_dollar_comma) # df_product['shipAmount'] = df_product['shipAmount'].map(remove_dollar_comma) df_product['markdownPercent'] = df_product['markdownPercent'].astype('float') df_product['relevancy'] = df_product['relevancy'].astype('float') optional_tracking_string = '&af_campaign_id={}&af_rid={}_'.format(campaign_id, keyword) df_product['url'] = df_product['url'] + df_product['Skus'].map(lambda x: optional_tracking_string + x) # Sorting the Results df_product = df_product.sort_values(by=['relevancy', 'price', 'markdownPercent'], ascending=[relevancy_filter, price_filter, discount_filter], na_position='last') # Filtering the Results # condition_filter = st.sidebar.selectbox("Condition of the Product: ", list(df_product['condition'].unique())) # stock_filter = st.sidebar.selectbox("Stock of Products: ", list(df_product['stock'].unique())) #filter_condition = (df_product['condition'] == condition_filter) & (df_product['stock'] == stock_filter) filter_condition = df_product['stock'] == stock_filter df_product = df_product[filter_condition] # Top N Recommendations top_n_rec = df_product.head(n_rec) # Inserting the DataFrame into the bizrate DB into the corresponding table create_insert_table(db_name='RecSysData', table_name=keyword + "_" + publisherid, df=top_n_rec) # Main Program # selected_keyword = 'aquaman' # publisher_id = '725895' # query_bizrate(selected_keyword, publisher_id, 100) # recommend(selected_keyword, publisher_id) # list_keywords = ['aquaman', 'superman', 'batman', 'shoes', 'electronics', 'wallet', 'movies', 'books'] # # for selected_keyword in list_keywords: # query_bizrate(selected_keyword) # recommend(selected_keyword)