02_load_coffeesalesdata.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 02: Load the Coffee sales data from SUEDHANG\n",
    "\n",
    "#### First create an dataframe with rows for each day, then fill it with the corresponding sales data for that day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from utils.utils import add_delimiters\n",
    "\n",
    "# Create a dataframe with rows for every day in the years 2021 to 2022, add additional date columns and \n",
    "df = pd.DataFrame(pd.date_range('2021-01-01', '2022-12-31', ), columns=['date'])\n",
    "df['day_of_week'] = df['date'].dt.day_name()\n",
    "df['month'] = df['date'].dt.month_name()\n",
    "df['year'] = df['date'].dt.year\n",
    "df['week_of_year'] = df['date'].dt.isocalendar().week\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Define the specific names of the relevant rows/columns/variables in the raw data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "category_index = \"Unnamed: 0\"\n",
    "sales_date_index = \"Unnamed: 1\"\n",
    "total_count_index = \"Unnamed: 2\"\n",
    "total_sales_index = \"Unnamed: 3\"\n",
    "\n",
    "years = [\"2021\", \"2022\"]\n",
    "months = [\"01jan\", \"02feb\", \"03mar\", \"04apr\", \"05may\", \"06jun\", \"07jul\", \"08aug\", \"09sep\", \"10oct\", \"11nov\", \"12dec\"]\n",
    "\n",
    "# load a previously manually curated list of coffee products\n",
    "coffeeproducts_text = open(\"coffeeproducts.txt\",'r', encoding=\"utf-8\").readlines()\n",
    "coffeeproducts = ['Getränke', 'Coffee']\n",
    "coffeeproducts.extend(str(row.strip()) for row in coffeeproducts_text)\n",
    "print(\"all_categories\", coffeeproducts)\n",
    "\n",
    "coffeeproducts_list_sales = [f\"{coffeeproduct}_sales\" for coffeeproduct in coffeeproducts]\n",
    "coffeeproducts_list_count = [f\"{coffeeproduct}_count\" for coffeeproduct in coffeeproducts]\n",
    "df[coffeeproducts_list_sales] = 0.0\n",
    "df[coffeeproducts_list_count] = 0.0\n",
    "\n",
    "# remove the \"total amount\" rows from the list of coffee products\n",
    "coffeeproducts_list_sales = coffeeproducts_list_sales[2:]\n",
    "coffeeproducts_list_count = coffeeproducts_list_count[2:]\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load the sales data for all files, write it to its corresponding date into the dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for year in years:\n",
    "    for month in months:\n",
    "        data_dir = os.path.join('raw_coffeesalesdata', year, month)\n",
    "        data_names = os.listdir(data_dir)\n",
    "        for day_file in data_names:\n",
    "            data = pd.read_csv(add_delimiters(os.path.join(data_dir, day_file), delimiter=';'), sep=';', encoding=\"utf-8\")\n",
    "            data_date = data[sales_date_index][1]\n",
    "            data_date = pd.to_datetime(data_date, format='%d.%m.%Y').strftime('%Y-%m-%d')\n",
    "            # merge the main_categories and all_categories together\n",
    "            for category in coffeeproducts:\n",
    "                try:\n",
    "                    df.loc[df['date'] == data_date, f\"{category}_sales\"] = data.loc[data[category_index] == category, total_sales_index].values[0]\n",
    "                    df.loc[df['date'] == data_date, f\"{category}_count\"] = data.loc[data[category_index] == category, total_count_index].values[0]\n",
    "                except IndexError:\n",
    "                    #print(f\"No {category} sold on {data_date}\")\n",
    "                    pass\n",
    "\n",
    "# sum up all the total over coffee products\n",
    "df[\"Coffee_sales\"] = df[coffeeproducts_list_sales].apply(pd.to_numeric, errors='coerce').sum(axis=1)\n",
    "df[\"Coffee_count\"] = df[coffeeproducts_list_count].apply(pd.to_numeric, errors='coerce').sum(axis=1)\n",
    "\n",
    "# remove the rows where no coffee was sold\n",
    "df = df[df[\"Coffee_sales\"] != 0]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# print the top k maximal values of Getränke_sales and the corresponding date\n",
    "k = 15\n",
    "var = 'Getränke'\n",
    "# first convert the sales to float\n",
    "df.loc[f'{var}_sales'] = df.loc[f'{var}_sales'].astype(float)\n",
    "df.loc[f'{var}_count'] = df.loc[f'{var}_count'].astype(int)\n",
    "print(df.nlargest(k, f'{var}_sales')[['date', f'{var}_sales']])\n",
    "print(df.nlargest(k, f'{var}_count')[['date', f'{var}_count']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Manually correct certain dates, where the sales data for two or more days is entered in one day\n",
    "\n",
    "vars = ['Getränke', 'Coffee']\n",
    "sal_cnt = ['sales', 'count']\n",
    "\n",
    "for var in vars:\n",
    "    # first convert the sales to float\n",
    "    df[f'{var}_sales'] = df[f'{var}_sales'].astype(float)\n",
    "    df[f'{var}_count'] = df[f'{var}_count'].astype(int)\n",
    "\n",
    "    for sal in sal_cnt:\n",
    "        # 2021-10-16 has all data for 2021-10-16 and 2021-10-17,\n",
    "        print(df.loc[df[\"date\"] == \"2021-10-16\", [\"date\", f'{var}_{sal}']])\n",
    "        print(df.loc[df[\"date\"] == \"2021-10-17\", [\"date\", f'{var}_{sal}']])\n",
    "        combined_sales = df.loc[df[\"date\"] == \"2021-10-16\", f'{var}_{sal}'].values[0]\n",
    "        getr_sales_on_2021_10_16 = combined_sales * 0.58\n",
    "        getr_sales_on_2021_10_17 = combined_sales - getr_sales_on_2021_10_16\n",
    "        df.loc[df['date'] == \"2021-10-16\", f'{var}_{sal}'] = getr_sales_on_2021_10_16\n",
    "        df.loc[df['date'] == \"2021-10-17\", f'{var}_{sal}'] = getr_sales_on_2021_10_17\n",
    "        print(df.loc[df[\"date\"] == \"2021-10-16\", [\"date\", f'{var}_{sal}']])\n",
    "        print(\"\\n ---------------------------- \\n\")\n",
    "\n",
    "        # 2022-02-13 has all data for 2022-02-13 and 2022-02-14\n",
    "        print(df.loc[df[\"date\"] == \"2022-02-13\", [\"date\", f'{var}_{sal}']])\n",
    "        print(df.loc[df[\"date\"] == \"2022-02-14\", [\"date\", f'{var}_{sal}']])\n",
    "        combined_sales = df.loc[df[\"date\"] == \"2022-02-13\", f'{var}_{sal}'].values[0]\n",
    "        getr_sales_on_2022_02_13 = combined_sales * 0.739\n",
    "        getr_sales_on_2022_02_14 = combined_sales - getr_sales_on_2022_02_13\n",
    "        df.loc[df['date'] == \"2022-02-13\", f'{var}_{sal}'] = getr_sales_on_2022_02_13\n",
    "        df.loc[df['date'] == \"2022-02-14\", f'{var}_{sal}'] = getr_sales_on_2022_02_14\n",
    "        print(df.loc[df[\"date\"] == \"2022-02-13\", [\"date\", f'{var}_{sal}']])\n",
    "        print(\"\\n ---------------------------- \\n\")\n",
    "\n",
    "        # 2021-07-31 has all data for 2021-07-31 and 2021-08-01\n",
    "        print(df.loc[df[\"date\"] == \"2021-07-31\", [\"date\", f'{var}_{sal}']])\n",
    "        print(df.loc[df[\"date\"] == \"2021-08-01\", [\"date\", f'{var}_{sal}']])\n",
    "        combined_sales = df.loc[df[\"date\"] == \"2021-07-31\", f'{var}_{sal}'].values[0]\n",
    "        getr_sales_on_2021_07_31 = combined_sales * 0.618\n",
    "        getr_sales_on_2021_08_01 = combined_sales - getr_sales_on_2021_07_31\n",
    "        df.loc[df['date'] == \"2021-07-31\", f'{var}_{sal}'] = getr_sales_on_2021_07_31\n",
    "        df.loc[df['date'] == \"2021-08-01\", f'{var}_{sal}'] = getr_sales_on_2021_08_01\n",
    "        print(df.loc[df[\"date\"] == \"2021-07-31\", [\"date\", f'{var}_{sal}']])\n",
    "        print(\"\\n ---------------------------- \\n\")\n",
    "\n",
    "        # 2022-01-08 has all data for 2022-01-08 and 2022-01-09\n",
    "        print(df.loc[df[\"date\"] == \"2022-01-08\", [\"date\", f'{var}_{sal}']])\n",
    "        print(df.loc[df[\"date\"] == \"2022-01-09\", [\"date\", f'{var}_{sal}']])\n",
    "        combined_sales = df.loc[df[\"date\"] == \"2022-01-08\", f'{var}_{sal}'].values[0]\n",
    "        getr_sales_on_2022_01_08 = combined_sales * 0.523\n",
    "        getr_sales_on_2022_01_09 = combined_sales - getr_sales_on_2022_01_08\n",
    "        df.loc[df['date'] == \"2022-01-08\", f'{var}_{sal}'] = getr_sales_on_2022_01_08\n",
    "        df.loc[df['date'] == \"2022-01-09\", f'{var}_{sal}'] = getr_sales_on_2022_01_09\n",
    "        print(df.loc[df[\"date\"] == \"2022-01-08\", [\"date\", f'{var}_{sal}']])\n",
    "        print(\"\\n ---------------------------- \\n\")\n",
    "\n",
    "        # 2022-07-25 has all data for 2022-07-25 and 2022-07-26\n",
    "        print(df.loc[df[\"date\"] == \"2022-07-25\", [\"date\", f'{var}_{sal}']])\n",
    "        print(df.loc[df[\"date\"] == \"2022-07-26\", [\"date\", f'{var}_{sal}']])\n",
    "        combined_sales = df.loc[df[\"date\"] == \"2022-07-25\", f'{var}_{sal}'].values[0]\n",
    "        getr_sales_on_2022_07_25 = combined_sales * 0.469\n",
    "        getr_sales_on_2022_07_26 = combined_sales - getr_sales_on_2022_07_25\n",
    "        df.loc[df['date'] == \"2022-07-25\", f'{var}_{sal}'] = getr_sales_on_2022_07_25\n",
    "        df.loc[df['date'] == \"2022-07-26\", f'{var}_{sal}'] = getr_sales_on_2022_07_26\n",
    "        print(df.loc[df[\"date\"] == \"2022-07-25\", [\"date\", f'{var}_{sal}']])\n",
    "        print(\"\\n ---------------------------- \\n\")\n",
    "\n",
    "        # 2022-08-11 has all data for 2022-08-11 and 2022-08-12\n",
    "        print(df.loc[df[\"date\"] == \"2022-08-11\", [\"date\", f'{var}_{sal}']])\n",
    "        print(df.loc[df[\"date\"] == \"2022-08-12\", [\"date\", f'{var}_{sal}']])\n",
    "        combined_sales = df.loc[df[\"date\"] == \"2022-08-11\", f'{var}_{sal}'].values[0]\n",
    "        getr_sales_on_2022_08_11 = combined_sales * 0.486\n",
    "        getr_sales_on_2022_08_12 = combined_sales - getr_sales_on_2022_08_11\n",
    "        df.loc[df['date'] == \"2022-08-11\", f'{var}_{sal}'] = getr_sales_on_2022_08_11\n",
    "        df.loc[df['date'] == \"2022-08-12\", f'{var}_{sal}'] = getr_sales_on_2022_08_12\n",
    "        print(df.loc[df[\"date\"] == \"2022-08-11\", [\"date\", f'{var}_{sal}']])\n",
    "        print(\"\\n ---------------------------- \\n\")\n",
    "\n",
    "        # 2021-12-13 has all data for 2021-12-13 and 2021-12-14 and 2021-12-15\n",
    "        print(df.loc[df[\"date\"] == \"2021-12-13\", [\"date\", f'{var}_{sal}']])\n",
    "        print(df.loc[df[\"date\"] == \"2021-12-14\", [\"date\", f'{var}_{sal}']])\n",
    "        print(df.loc[df[\"date\"] == \"2021-12-15\", [\"date\", f'{var}_{sal}']])\n",
    "        combined_sales = df.loc[df[\"date\"] == \"2021-12-13\", f'{var}_{sal}'].values[0]\n",
    "        getr_sales_on_2021_12_13 = combined_sales * 0.2895\n",
    "        getr_sales_on_2021_12_14 = combined_sales * 0.377\n",
    "        getr_sales_on_2021_12_15 = combined_sales - getr_sales_on_2021_12_13 - getr_sales_on_2021_12_14\n",
    "        df.loc[df['date'] == \"2021-12-13\", f'{var}_{sal}'] = getr_sales_on_2021_12_13\n",
    "        df.loc[df['date'] == \"2021-12-14\", f'{var}_{sal}'] = getr_sales_on_2021_12_14\n",
    "        df.loc[df['date'] == \"2021-12-15\", f'{var}_{sal}'] = getr_sales_on_2021_12_15\n",
    "        print(df.loc[df[\"date\"] == \"2021-12-13\", [\"date\", f'{var}_{sal}']])\n",
    "        print(\"\\n ---------------------------- \\n\")\n",
    "\n",
    "        # 2021-12-22 has all data for 2021-12-22 and 2021-12-23\n",
    "        print(df.loc[df[\"date\"] == \"2021-12-22\", [\"date\", f'{var}_{sal}']])\n",
    "        print(df.loc[df[\"date\"] == \"2021-12-23\", [\"date\", f'{var}_{sal}']])\n",
    "        combined_sales = df.loc[df[\"date\"] == \"2021-12-22\", f'{var}_{sal}'].values[0]\n",
    "        getr_sales_on_2021_12_22 = combined_sales * 0.516\n",
    "        getr_sales_on_2021_12_23 = combined_sales - getr_sales_on_2021_12_22\n",
    "        df.loc[df['date'] == \"2021-12-22\", f'{var}_{sal}'] = getr_sales_on_2021_12_22\n",
    "        df.loc[df['date'] == \"2021-12-23\", f'{var}_{sal}'] = getr_sales_on_2021_12_23\n",
    "        print(df.loc[df[\"date\"] == \"2021-12-22\", [\"date\", f'{var}_{sal}']])\n",
    "        print(\"\\n ---------------------------- \\n\")\n",
    "\n",
    "        # 2021-11-09 has all data for 2021-11-09 and 2021-11-10\n",
    "        print(df.loc[df[\"date\"] == \"2021-11-09\", [\"date\", f'{var}_{sal}']])\n",
    "        print(df.loc[df[\"date\"] == \"2021-11-10\", [\"date\", f'{var}_{sal}']])\n",
    "        combined_sales = df.loc[df[\"date\"] == \"2021-11-09\", f'{var}_{sal}'].values[0]\n",
    "        getr_sales_on_2021_11_09 = combined_sales * 0.546\n",
    "        getr_sales_on_2021_11_10 = combined_sales - getr_sales_on_2021_11_09\n",
    "        df.loc[df['date'] == \"2021-11-09\", f'{var}_{sal}'] = getr_sales_on_2021_11_09\n",
    "        df.loc[df['date'] == \"2021-11-10\", f'{var}_{sal}'] = getr_sales_on_2021_11_10\n",
    "        print(df.loc[df[\"date\"] == \"2021-11-09\", [\"date\", f'{var}_{sal}']])\n",
    "        print(\"\\n ---------------------------- \\n\")\n",
    "    \n",
    "    df[f'{var}_count'] = df[f'{var}_count'].astype(int)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop all rows where no sales were made/are not available\n",
    "#df = df[df[\"Getränke_sales\"] != 0]\n",
    "# save the df as a csv file\n",
    "df.to_csv('processed_data/sales_2021-2022.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.10 ('PytorchGeometric')",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.10"
  },
  "vscode": {
   "interpreter": {
    "hash": "b264b2ac6437c9bf059af297d6e83aaee783728e002e0b2ed5d328e2da20d4ac"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}