{ "cells": [ { "cell_type": "markdown", "id": "4c065ad1-4785-48b5-abe3-9a4ebbb788dd", "metadata": {}, "source": [ "# Data Cafe Sales dataset - preprocessing notebook\n", "\n", "The objective is to preprocess the `dirty_cafe_sales.csv` dataset by cleaning **'ERROR'**, **'UNKNOWN'** but also missing (`None`) values.\n", "\n", "> **Notes:**\n", "> when you created your notebook, you normally already installed the `Numpy` and `Pandas` dependencies\n", ">" ] }, { "cell_type": "markdown", "id": "7143699b-5c3d-4011-8137-54d5a7c646ed", "metadata": {}, "source": [ "### Import dependencies" ] }, { "cell_type": "code", "execution_count": 1, "id": "70cf76ac-6501-48d2-a19a-f15a86735ef8", "metadata": { "tags": [] }, "outputs": [], "source": [ "# use DataPlatform Python SDK \n", "from forepaas.dwh import connect, update_metas\n", "from forepaas.core.settings import CONFIG\n", "from forepaas.dwh import bulk_insert\n", "\n", "# import Python Pandas and Numpy to manage Python dataframe\n", "import pandas as pd\n", "import numpy as np\n", "import os\n", "import requests\n", "import json" ] }, { "cell_type": "markdown", "id": "a25431bb-f5fb-45c7-b7b7-10ecb6acb236", "metadata": {}, "source": [ "### Load datatset from Connectors\n", "\n", "Choose your `dirty_cafe_sales` table from **Lakehouse Manager**:" ] }, { "cell_type": "code", "execution_count": null, "id": "07e4f47c-835d-462b-9cfd-d0cbb8c8404c", "metadata": { "tags": [] }, "outputs": [], "source": [ "connector = connect(\"dwh/default_dataset/\")\n", "connector.list()" ] }, { "cell_type": "code", "execution_count": null, "id": "e204eaf8-fa93-40a0-b808-10d10f2755e7", "metadata": { "tags": [] }, "outputs": [], "source": [ "df = connector.select(\"dirty_cafe_sales\")\n", "\n", "print(\"\\nDataset information:\\n\", df.info)\n", "print(\"\\nDataset details:\\n\", df.describe())" ] }, { "cell_type": "markdown", "id": "8cbc07f0-61f7-4c28-9cee-a1fb3c6661db", "metadata": {}, "source": [ "### Change datatype for the following attributes\n", "\n", "- `item` is a string\n", "- `quantity` is a float\n", "- `price_per_unit` is a float\n", "- `total_spent` is a float" ] }, { "cell_type": "code", "execution_count": null, "id": "7c380f6e-8ec7-4d44-9af2-981d75a6ea3e", "metadata": { "tags": [] }, "outputs": [], "source": [ "print(\"Attributes dtypes:\\n\", df.dtypes)" ] }, { "cell_type": "code", "execution_count": null, "id": "0002e8cd-16cd-4452-93f9-b24cb238c830", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Columns to clean\n", "columns_to_clean = ['item', 'quantity', 'price_per_unit', 'total_spent']\n", "\n", "# Replace 'ERROR' and 'UNKNOWN' with NaN\n", "for col in columns_to_clean:\n", " print(col)\n", " df[col] = df[col].replace(['ERROR', 'UNKNOWN', ''], np.nan)\n", " # Convert numerical columns to float\n", " if col != 'item': \n", " df[col] = df[col].astype(float)\n", "\n", "# Check datatypes\n", "print(\"Attributes dtypes:\", df.dtypes)" ] }, { "cell_type": "markdown", "id": "1e7ea2e3-0f39-4bbe-8f8d-56d6606ba070", "metadata": {}, "source": [ "### Fill in the empty values as much as possible thanks to the correlation between the data\n", "\n", "- `item`\n", "- `quantity`\n", "- `price_per_unit`\n", "- `total_spent`\n", "\n", "> **⚠️ Warning** - an `item` has a single `price_per_unit` BUT caution, the reverse is not true!" ] }, { "cell_type": "code", "execution_count": 6, "id": "f4ff78fe-b245-47de-bbea-e3c826d2f870", "metadata": { "tags": [] }, "outputs": [], "source": [ "# item / price dictionnary\n", "item_price = {\n", " 'Coffee': 2.0, 'Tea': 1.5, 'Sandwich': 4.0, 'Salad': 3.0,\n", " 'Cake': 3.0, 'Cookie': 1.0, 'Smoothie': 4.0, 'Juice': 3.0\n", "}\n", "\n", "# Reverse - Price / item dictionnary\n", "price_item = {price: item for item, price in item_price.items()}\n", "\n", "# Define a maximum number of iterations to avoid an infinite loop\n", "max_iterations = 3\n", "iteration = 0\n", "\n", "# Loop to fill NaNs as fully as possible thanks to correlation between 'item', 'price_per_unit', 'quantity' and 'total_spent'\n", "while df['item'].notna().sum() > 0 and iteration < max_iterations:\n", "\n", " # total_spent = price_per_unit * quantity\n", " df['price_per_unit'] = df['price_per_unit'].fillna(df['total_spent'] / df['quantity'])\n", " df['quantity'] = df['quantity'].fillna(df['total_spent'] / df['price_per_unit'])\n", " df['total_spent'] = df['total_spent'].fillna(df['price_per_unit'] * df['quantity'])\n", "\n", " # 'Coffee': 2.0, 'Tea': 1.5, 'Sandwich': 4.0, 'Salad': 3.0, 'Cake': 3.0, 'Cookie': 1.0, 'Smoothie': 4.0, 'Juice': 3.0\n", " df['price_per_unit'] = df['price_per_unit'].fillna(df['item'].map(item_price))\n", " df['item'] = df['item'].fillna(df['price_per_unit'].map(price_item))\n", "\n", " iteration += 1" ] }, { "cell_type": "code", "execution_count": null, "id": "590ca353-7eba-4aa4-beb4-8ecc8b554811", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Display dataset information after replacement\n", "print(\"Dataset information\", df.info())\n", "print(\"____________________________________________\")" ] }, { "cell_type": "markdown", "id": "8af85ffa-5c16-41a8-9cd6-0adfb5115f07", "metadata": {}, "source": [ "### Remove rows where values are still missing\n", "\n", "Apply the deletion by looking at the following columns:\n", "\n", "- `item`\n", "- `quantity`\n", "- `transaction_date`" ] }, { "cell_type": "code", "execution_count": null, "id": "670618ae-f51a-4118-9a91-edf0113df001", "metadata": { "tags": [] }, "outputs": [], "source": [ "# delete the remaining missing values\n", "df = df.dropna(subset=['item', 'quantity', 'transaction_date'])\n", "\n", "# display dataset information after deletion\n", "print(\"Dataset information\", df.info())\n", "print(\"____________________________________________\")" ] }, { "cell_type": "markdown", "id": "ff75aa1c-100e-4b19-b1b4-ccc764691a7e", "metadata": {}, "source": [ "### Save clean dataframe into csv file\n", "\n", "You can now save your processed dataframe into a new csv file." ] }, { "cell_type": "code", "execution_count": null, "id": "a1164f02-3d02-46ea-a954-286b1885311c", "metadata": { "tags": [] }, "outputs": [], "source": [ "df.to_csv('clean_cafe_sales.csv', index=False)\n", "df" ] }, { "cell_type": "markdown", "id": "5c3c8a5e-daf8-4b81-ae81-00682f90520a", "metadata": {}, "source": [ "### [Optional] - Update the clean_cafe_sales table \n", "\n", "You can now connect to the **Lakehouse Manager** and update the table created previously." ] }, { "cell_type": "code", "execution_count": null, "id": "94720977-0880-42e1-b00d-05254eaa979b", "metadata": { "tags": [] }, "outputs": [], "source": [ "destination = connect(\"dwh/default_dataset/\")\n", "destination.list()" ] }, { "cell_type": "code", "execution_count": 12, "id": "e27d7208-8cd0-45ac-9fdb-44b69bd0b678", "metadata": { "tags": [] }, "outputs": [], "source": [ "stats = bulk_insert(destination, \"clean_cafe_sales\", df)" ] }, { "cell_type": "code", "execution_count": null, "id": "61a4ac44", "metadata": {}, "outputs": [], "source": [ "init_dwh_config()\n", "update_metas()" ] }, { "cell_type": "markdown", "id": "02b07322-6507-4448-af6d-39d2b23ab201", "metadata": {}, "source": [ "### Benefit from AI Endpoints for smart data analysis\n", "\n", "- Access AI Endpoint access token from envirnoment variables" ] }, { "cell_type": "code", "execution_count": 13, "id": "27d3dd33-9726-4ab6-b9c1-9780900c99c3", "metadata": { "tags": [] }, "outputs": [], "source": [ "# Convert dataframe into readable format - JSON\n", "df = pd.read_csv('clean_cafe_sales.csv')\n", "df_analysis = df.drop(['transaction_id'], axis=1)" ] }, { "cell_type": "code", "execution_count": null, "id": "cf795676-066a-4200-8bc9-8c415b79d4e8", "metadata": { "tags": [] }, "outputs": [], "source": [ "df_analysis" ] }, { "cell_type": "markdown", "id": "35065e49-83ba-48d1-86c6-112144abf159", "metadata": { "tags": [] }, "source": [ "- Create **AI Endpoints** request function\n", "\n", "A single request is enough to generate all the Python code you need for the dataset analysis!\n", "\n", "> Here, you can use the **[Llama 3.3 70B Instruct model](https://llama-3-3-70b-instruct.endpoints.kepler.ai.cloud.ovh.net/doc)** to obtain a Python code that will allow you to analyze your dataset easily.\n", "\n", "> The advantage of asking LLM to generate code in Python is that you will be able to reuse it when you add new data to your coffee sales in subsequent months. You will then have the same analysis method!\n", "\n", "\n", "#### To generate an AI Endpoints API key, acces [https://endpoints.ai.cloud.ovh.net/](https://endpoints.ai.cloud.ovh.net/)" ] }, { "cell_type": "code", "execution_count": 15, "id": "3de58966-bf18-4067-9535-0e4d8cce7651", "metadata": { "tags": [] }, "outputs": [], "source": [ "AI_ENDPOINTS_API_KEY = \" Then you will be able to reuse it for future Data Analysis if you add new data" ] }, { "cell_type": "code", "execution_count": 17, "id": "b9ad5cd2-9a17-4b68-8a96-4aa40f875d09", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Data Analysis script in Python has been created: data_analysis.py\n" ] } ], "source": [ "# Send your dataset to get generate automatically the Python code for data analysis\n", "csv_data = df_analysis.to_csv(index=False)\n", "data_analysis_answer = ai_endpoints_llm_data_anaysis(csv_data)\n", "print(\"Data Analysis script in Python has been created: data_analysis.py\")" ] }, { "cell_type": "markdown", "id": "2ff2d3d3-6faa-45d7-a131-ec1d31c0b3a1", "metadata": {}, "source": [ "- Get **Data Analysis** result\n", "\n", "You can launch the generated Python code and enjoy the Data Analysis result!\n", "\n", "`!python data_analysis.py`" ] }, { "cell_type": "code", "execution_count": null, "id": "26f2da14-3119-4de7-9436-9c8c69d91f18", "metadata": { "tags": [] }, "outputs": [], "source": [ "import pandas as pd\n", "import matplotlib.pyplot as plt\n", "import seaborn as sns\n", "\n", "# Load data\n", "df = pd.read_csv('clean_cafe_sales.csv')\n", "\n", "# Convert transaction_date to datetime\n", "df['transaction_date'] = pd.to_datetime(df['transaction_date'])\n", "\n", "# Identify top 5 best-selling items\n", "top_selling_items = df.groupby('item')['quantity'].sum().sort_values(ascending=False).head(5)\n", "print(\"Top 5 Best-Selling Items:\")\n", "print(top_selling_items)\n", "\n", "# Calculate total revenue\n", "total_revenue = df['total_spent'].sum()\n", "print(f\"\\nTotal Revenue: ${total_revenue:.2f}\")\n", "\n", "# Find most common payment method\n", "most_common_payment_method = df['payment_method'].mode().values[0]\n", "print(f\"\\nMost Common Payment Method: {most_common_payment_method}\")\n", "\n", "# Identify seasonal trends in sales\n", "df['month'] = df['transaction_date'].dt.month\n", "seasonal_trends = df.groupby('month')['total_spent'].sum()\n", "print(\"\\nSeasonal Trends in Sales:\")\n", "print(seasonal_trends)\n", "\n", "# Visualize sales trend over time\n", "plt.figure(figsize=(10,6))\n", "sns.lineplot(data=df, x='transaction_date', y='total_spent')\n", "plt.title('Sales Trend Over Time')\n", "plt.xlabel('Date')\n", "plt.ylabel('Total Spent')\n", "plt.show()\n", "\n", "# Visualize top 5 best-selling items\n", "plt.figure(figsize=(10,6))\n", "sns.countplot(data=df, x='item', order=top_selling_items.index)\n", "plt.title('Top 5 Best-Selling Items')\n", "plt.xlabel('Item')\n", "plt.ylabel('Quantity')\n", "plt.show()\n", "\n", "# Visualize payment method distribution\n", "plt.figure(figsize=(10,6))\n", "sns.countplot(data=df, x='payment_method')\n", "plt.title('Payment Method Distribution')\n", "plt.xlabel('Payment Method')\n", "plt.ylabel('Count')\n", "plt.show()" ] }, { "cell_type": "markdown", "id": "b4da0a35-1444-492f-bb78-180270fe3822", "metadata": {}, "source": [ "Good job!" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "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.9.13" } }, "nbformat": 4, "nbformat_minor": 5 }