recommend.py

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)