{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nfrom sklearn.cluster import KMeans\nimport scipy.sparse as sparse\n\nimport os\nimport re\nimport gc\nimport pickle\nimport datetime\n\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from IPython.display import display, HTML\n\ndef print_df(df, num=None):\n    if num is None:\n        num = df.shape[0]\n#     print(df.shape)\n    display(HTML(df.iloc[:num, :].to_html()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PATH = '/kaggle/input/test-recsys/kaggle_tab_1345'\n\norders = pd.read_csv(os.path.join(PATH, 'tab_1_orders.csv'))\ncategories = pd.read_csv(os.path.join(PATH, 'tab_3_categories.csv'))\nuserprof = pd.read_csv(os.path.join(PATH, 'tab_4_user_profiles.csv'))\nprodprop = pd.read_csv(os.path.join(PATH, 'tab_5_product_properties.csv'))\ncity = pd.read_csv('/kaggle/input/test-recsys/tab_6_city.csv')\n\nPATH = '/kaggle/input/test-recsys/'\nsubmission = pd.read_csv(os.path.join(PATH, 'sample_submission.csv'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\norders = pd.merge(orders, city, on='store_id')\norders['order_created_time'] = pd.to_datetime(orders['order_created_time'], format='%Y-%m-%d %H:%M:%S')\nnow = pd.to_datetime('today')\norders['days_ago'] = orders['order_created_time'].apply(lambda x: (now - x).days)\norders['week'] = orders['order_created_time'].apply(lambda x: f'{x.year}-{x.week}' if len(str(x.week))==2 else f'{x.year}-0{x.week}')\norders['week_date'] = orders['week'].apply(lambda x: datetime.datetime.strptime(x + '-1', \"%Y-%W-%w\"))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"covid = pd.read_csv('/kaggle/input/covid19/owid-covid-data.csv', low_memory=False)\n\ncovid = covid.loc[covid.location=='Russia', ['date', 'total_cases']]\ncovid.columns = ['week_date', 'total_cases']\ncovid['week_date'] = pd.to_datetime(covid['week_date'], format='%Y-%m-%d')\ncovid = covid[covid['week_date'].isin(orders['week_date'])]\ncovid['total_cases_log'] = covid['total_cases'].apply(lambda x: np.log(x) if x != 0 else 0)\n# covid","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"temp = orders.groupby(['retailer']).order_id.nunique()\nlargest_shops = temp.sort_values(ascending=False).index.values[:5]\n\ntemp = orders.groupby(['week_date', 'retailer'], as_index=False).order_id.nunique()\ntemp = pd.merge(temp, covid, on='week_date', how='left')\ntemp = temp.loc[3:]\nfor shop in largest_shops:\n    shop_df = temp[temp.retailer==shop]\n    \n    fig, ax1 = plt.subplots(figsize=(20, 5))\n    \n    x_values = [str(x).split(' ')[0] for x in shop_df.week_date]\n    color='tab:red'\n    ax1.plot(x_values, shop_df.order_id, color=color)\n    xlabels = ax1.get_xticklabels()\n    ax1.set_xticklabels(xlabels, rotation=40)\n    ax1.set_ylabel('Orders count', color=color, fontsize=14)\n    ax1.tick_params(axis='y', labelcolor=color)\n\n    ax2 = ax1.twinx()\n    color = 'tab:blue'\n    ax2.plot(x_values, shop_df.total_cases_log, color=color)\n    ax2.set_ylabel('Covid-19 total cases in Russia, ln', color=color, fontsize=14)\n    \n    plt.title(f'Amount of orders by week in {shop}', fontsize=15)\n    ax1.grid(True);","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"largest_retailers = orders.retailer.value_counts()[:10].index\nplt.figure(figsize=(15, 5))\nsns.countplot(x='retailer', hue='platform', data=orders[orders.retailer.isin(largest_retailers)])\nplt.xlabel('retailer', fontsize=14)\nplt.ylabel('count', fontsize=14)\nplt.grid();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"orders.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"result = pd.DataFrame(columns=['city_name', f'{largest_retailers[0]}'])\nfor ret in largest_retailers:\n    temp = orders[orders.retailer==ret].groupby('city_name', as_index=False).order_id.nunique()\n    temp.columns = ['city_name', ret]\n    if result.empty:\n        result = temp.copy()\n    else:\n        result = pd.merge(result, temp, on='city_name', how='outer')\nresult.sort_values(by=list(largest_retailers), ascending=False, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for ret in ['METRO', 'Лента', 'Ашан']:\n    temp = result[['city_name', ret]].sort_values(by=ret, ascending=False)\n    temp.to_excel(f'{ret}.xlsx', index=False)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# print(categories.shape)\ncategories.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def get_age(x):\n    now = 2020\n    if type(x)==str:\n        year_birth = re.search(r'\\d\\d\\d\\d', x)\n        if year_birth is not None:\n            return now - int(year_birth.group(0))\n        else:\n            return 0\n    else:\n        return 0\n        \nuserprof['age'] = userprof['bdate'].apply(get_age)\nuserprof.drop('bdate', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"userprof.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(20, 5))\nsns.distplot(userprof.loc[userprof.gender=='male', ['age']], label='male', bins=100)\nsns.distplot(userprof.loc[userprof.gender=='female', ['age']], label='female', bins=100)\nplt.xlabel('Age', fontsize=14)\nplt.legend(fontsize=14)\nplt.title('Distribution of ages by gender', fontsize=15);","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"prodprop.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(prodprop.shape)\nprint(len(np.unique(prodprop.product_id)))\nlen(np.unique(prodprop.property_name))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## User-feature matrix"},{"metadata":{},"cell_type":"markdown","source":"#### RFMV"},{"metadata":{"trusted":true},"cell_type":"code","source":"# create a function that returns a score--0 to 4 based on the quartile ranking\n## these score can be easily interpreted by the K-Mean algorithm instead of the actual value of RFMV\n\n### recency score: the lower the recency, the higher the score \ndef RecencyScore(i, col, rfmv_dict):\n    if i <= rfmv_dict[col][0.25]:\n        return 4\n    elif i <= rfmv_dict[col][0.50]:\n        return 3\n    elif i <= rfmv_dict[col][0.75]: \n        return 2\n    else:\n        return 1\n    \n# F, M, V: In contrast to Recency, the higher the quantile value, the higher the score    \ndef FMVScore(i, col, rfmv_dict):\n    if i <= rfmv_dict[col][0.25]:\n        return 1\n    elif i <= rfmv_dict[col][0.50]:\n        return 2\n    elif i <= rfmv_dict[col][0.75]: \n        return 3    \n    else:\n        return 4\n\ndef rfmv(df):\n    df = df.groupby('user_id').agg(Recency=('days_ago', 'min'),\n                                   Frequency=('days_ago', 'count'),\n                                   Monetary=('price', 'sum'),\n                                   Variety=('product_id', 'nunique'))\n\n    ### we take the 25%, 50%, and 75% quantile for each of R, F, M, and V and store them into dict\n    rfmv_quantiles = df.quantile(q = [0.25, 0.5, 0.75]).to_dict()\n    \n    df['Recency'] = df['Recency'].apply(RecencyScore, args=('Recency', rfmv_quantiles))\n    df['Frequency'] = df['Frequency'].apply(FMVScore, args=('Frequency', rfmv_quantiles))\n    df['Monetary'] = df['Monetary'].apply(FMVScore, args=('Monetary', rfmv_quantiles))\n    df['Variety'] = df['Variety'].apply(FMVScore, args=('Variety', rfmv_quantiles))\n\n    df['Total_Score'] = df['Recency'] + df['Frequency'] + df['Monetary'] + df['Variety']\n    df.drop(['Recency', 'Frequency', 'Monetary', 'Variety'], axis=1, inplace=True)\n    \n#     # We use elbow method to find the optimal number of clusters\n#     wcss = []\n#     for i in range(2,10):\n#         kmeans = KMeans(n_clusters=i, \n#                         init='k-means++')\n#         kmeans.fit(df)\n#         wcss.append(kmeans.inertia_)\n\n#     # Plot the graph for Elbow Method\n#     plt.plot(range(2,10), wcss)\n#     plt.title('Elbow graph')\n#     plt.xlabel('Cluster number')\n#     plt.ylabel('WCSS')\n#     plt.show()\n    \n    # Apply KMean clustering using the optimal number with the \"elbow\" on the graph above\n    kmeans = KMeans(n_clusters=4, \n                    init='random', \n                    random_state=42)\n\n    df['Cluster'] = kmeans.fit_predict(df)\n\n\n    df.drop(['Total_Score'], axis=1, inplace=True)\n    df.reset_index(inplace=True)\n    \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# %%time\n\nFLAG_MAKE_PRODUCTS = True\n\nif FLAG_MAKE_PRODUCTS:\n    path = '/kaggle/input/test-recsys'\n    folders = [[os.path.join(path, 'sbermarket_tab_2_1')],\n               [os.path.join(path, f'sbermarket_tab_2_{i}')for i in range(2, 5)],\n               [os.path.join(path, f'sbermarket_tab_2_{i}') for i in range(5, 7)]]\n\n    сolumns_prod = ['user_id', 'order_id', 'price', 'quantity', 'product_id']\n    products = pd.DataFrame(columns=сolumns_prod)\n\n    for list_folder in folders:\n        for folder in list_folder:\n            for filename in os.listdir(folder):\n                products = pd.concat([products, pd.read_csv(os.path.join(folder, filename), usecols=сolumns_prod)], axis=0)\n\n        # leave only those who are in sample submission\n        products = products[products.user_id.isin(submission.Id)]\n\n    print('sanity check:', submission.Id.isin(products.user_id).mean())\n    products = pd.merge(products, orders[['user_id', 'order_id', 'days_ago']], on=['user_id', 'order_id'], how='left')\n    products['days_ago'].fillna(products['days_ago'].median(), inplace=True)\n    print('products shape:', products.shape)\n\n    # get full sum payed\n    products['price'] = products['price']*np.where(products['quantity'].isna(), 1, products['quantity'])\n    products.drop('quantity', axis=1, inplace=True)\n    print('sanity check:', submission.Id.isin(products.user_id).mean())\n    print(products.shape)\n    # RFMV\n    products = rfmv(products)\n\n    products = pd.merge(products, userprof, on='user_id', how='left')\n    print(products.shape)\n\n\n#     dummies = pd.get_dummies(products['Cluster'])\n#     dummies.columns = ['Cluster_1', 'Cluster_2', 'Cluster_3', 'Cluster_4']\n#     products = pd.concat([products.drop(['Cluster'], axis=1), dummies], axis=1)\n    \n#     products.to_csv('user-feature-mat.csv', index=False)    \n\nelse:\n    products = pd.read_csv('/kaggle/input/output/user-feature-mat.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"dummies = pd.get_dummies(products['Cluster'])\ndummies.columns = ['Cluster_1', 'Cluster_2', 'Cluster_3', 'Cluster_4']\ntemp = pd.concat([products.drop(['Cluster'], axis=1), dummies], axis=1)\ntemp = temp.drop_duplicates(subset=['user_id'])\n\ntemp.to_csv('FINAL_dummy.csv', index=False)\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"user_f  = sparse.coo_matrix(temp[['user_id', 'Cluster_1', 'Cluster_2', 'Cluster_3', 'Cluster_4']]) \nsparse.save_npz('TRY.npz', user_f)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"user_features = sparse.load_npz('/kaggle/working/TRY.npz')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}