{ "cells": [ { "cell_type": "markdown", "metadata": { "id": "4ba6aba8" }, "source": [ "# 🤖 **Data Collection, Creation, Storage, and Processing**\n" ] }, { "cell_type": "markdown", "metadata": { "id": "jpASMyIQMaAq" }, "source": [ "## **1.** 📦 Install required packages" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "f48c8f8c", "outputId": "7e50dd72-2e2a-47b1-e59b-ed7c5511d9cd" }, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "Requirement already satisfied: beautifulsoup4 in /usr/local/lib/python3.12/dist-packages (4.13.5)\n", "Requirement already satisfied: pandas in /usr/local/lib/python3.12/dist-packages (2.2.2)\n", "Requirement already satisfied: matplotlib in /usr/local/lib/python3.12/dist-packages (3.10.0)\n", "Requirement already satisfied: seaborn in /usr/local/lib/python3.12/dist-packages (0.13.2)\n", "Requirement already satisfied: numpy in /usr/local/lib/python3.12/dist-packages (2.0.2)\n", "Requirement already satisfied: textblob in /usr/local/lib/python3.12/dist-packages (0.19.0)\n", "Requirement already satisfied: soupsieve>1.2 in /usr/local/lib/python3.12/dist-packages (from beautifulsoup4) (2.8.3)\n", "Requirement already satisfied: typing-extensions>=4.0.0 in /usr/local/lib/python3.12/dist-packages (from beautifulsoup4) (4.15.0)\n", "Requirement already satisfied: python-dateutil>=2.8.2 in /usr/local/lib/python3.12/dist-packages (from pandas) (2.9.0.post0)\n", "Requirement already satisfied: pytz>=2020.1 in /usr/local/lib/python3.12/dist-packages (from pandas) (2025.2)\n", "Requirement already satisfied: tzdata>=2022.7 in /usr/local/lib/python3.12/dist-packages (from pandas) (2025.3)\n", "Requirement already satisfied: contourpy>=1.0.1 in /usr/local/lib/python3.12/dist-packages (from matplotlib) (1.3.3)\n", "Requirement already satisfied: cycler>=0.10 in /usr/local/lib/python3.12/dist-packages (from matplotlib) (0.12.1)\n", "Requirement already satisfied: fonttools>=4.22.0 in /usr/local/lib/python3.12/dist-packages (from matplotlib) (4.62.1)\n", "Requirement already satisfied: kiwisolver>=1.3.1 in /usr/local/lib/python3.12/dist-packages (from matplotlib) (1.5.0)\n", "Requirement already satisfied: packaging>=20.0 in /usr/local/lib/python3.12/dist-packages (from matplotlib) (26.0)\n", "Requirement already satisfied: pillow>=8 in /usr/local/lib/python3.12/dist-packages (from matplotlib) (11.3.0)\n", "Requirement already satisfied: pyparsing>=2.3.1 in /usr/local/lib/python3.12/dist-packages (from matplotlib) (3.3.2)\n", "Requirement already satisfied: nltk>=3.9 in /usr/local/lib/python3.12/dist-packages (from textblob) (3.9.1)\n", "Requirement already satisfied: click in /usr/local/lib/python3.12/dist-packages (from nltk>=3.9->textblob) (8.3.1)\n", "Requirement already satisfied: joblib in /usr/local/lib/python3.12/dist-packages (from nltk>=3.9->textblob) (1.5.3)\n", "Requirement already satisfied: regex>=2021.8.3 in /usr/local/lib/python3.12/dist-packages (from nltk>=3.9->textblob) (2025.11.3)\n", "Requirement already satisfied: tqdm in /usr/local/lib/python3.12/dist-packages (from nltk>=3.9->textblob) (4.67.3)\n", "Requirement already satisfied: six>=1.5 in /usr/local/lib/python3.12/dist-packages (from python-dateutil>=2.8.2->pandas) (1.17.0)\n" ] } ], "source": [ "!pip install beautifulsoup4 pandas matplotlib seaborn numpy textblob" ] }, { "cell_type": "markdown", "metadata": { "id": "lquNYCbfL9IM" }, "source": [ "## **2.** ⛏ Load the Superstore dataset from Kaggle\n", "\n", "Dataset source: [Superstore Dataset – Kaggle](https://www.kaggle.com/datasets/vivek468/superstore-dataset-final?resource=download)" ] }, { "cell_type": "markdown", "metadata": { "id": "0IWuNpxxYDJF" }, "source": [ "### *a. Initial setup*\n", "Define the base url of the dataset source as well as how and what you will load" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "91d52125" }, "outputs": [], "source": [ "import requests\n", "from bs4 import BeautifulSoup\n", "import pandas as pd\n", "import time\n", "\n", "# Dataset source\n", "base_url = \"https://www.kaggle.com/datasets/vivek468/superstore-dataset-final?resource=download\"\n", "headers = {\"User-Agent\": \"Mozilla/5.0\"}\n", "\n", "df_raw = pd.read_csv(\"Sample - Superstore.csv\", encoding=\"latin-1\")\n", "df_raw[\"Order Date\"] = pd.to_datetime(df_raw[\"Order Date\"], format=\"%m/%d/%Y\")\n", "df_raw[\"Ship Date\"] = pd.to_datetime(df_raw[\"Ship Date\"], format=\"%m/%d/%Y\")\n", "\n", "sub_categories, avg_prices, avg_profits = [], [], []" ] }, { "cell_type": "markdown", "metadata": { "id": "oCdTsin2Yfp3" }, "source": [ "### *b. Fill sub_categories, avg_prices, and avg_profits from the dataset*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "xqO5Y3dnYhxt" }, "outputs": [], "source": [ "# Aggregate over all Sub-Categories\n", "for sub_cat, group in df_raw.groupby(\"Sub-Category\"):\n", " sub_categories.append(sub_cat)\n", " avg_prices.append(round(group[\"Sales\"].sum() / group[\"Quantity\"].sum(), 2))\n", " avg_profits.append(round(group[\"Profit\"].mean(), 2))\n", "\n", " time.sleep(0) # kept for structural parity" ] }, { "cell_type": "markdown", "metadata": { "id": "T0TOeRC4Yrnn" }, "source": [ "### *c. ✋🏻🛑⛔️ Create a dataframe df_products that contains the now complete \"sub_category\", \"avg_price\", and \"avg_profit\" objects*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "l5FkkNhUYTHh" }, "outputs": [], "source": [ "# 🗂️ Create DataFrame\n", "df_products = pd.DataFrame({\n", " \"sub_category\": sub_categories,\n", " \"avg_price\": avg_prices,\n", " \"avg_profit\": avg_profits\n", "})" ] }, { "cell_type": "markdown", "metadata": { "id": "duI5dv3CZYvF" }, "source": [ "### *d. Save dataframe either as a CSV or Excel file*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "lC1U_YHtZifh" }, "outputs": [], "source": [ "# 💾 Save to CSV\n", "df_products.to_csv(\"superstore_data.csv\", index=False)\n" ] }, { "cell_type": "markdown", "metadata": { "id": "qMjRKMBQZlJi" }, "source": [ "### *e. ✋🏻🛑⛔️ View first few lines*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "colab": { "base_uri": "https://localhost:8080/", "height": 206 }, "id": "O_wIvTxYZqCK", "outputId": "5c622472-47de-4352-daa9-c7da226d0c30" }, "outputs": [ { "output_type": "execute_result", "data": { "text/plain": [ " sub_category avg_price avg_profit\n", "0 Accessories 57.42 55.81\n", "1 Appliances 62.69 38.47\n", "2 Art 9.09 8.15\n", "3 Binders 33.07 20.72\n", "4 Bookcases 124.61 -19.17" ], "text/html": [ "\n", "
\n", "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
sub_categoryavg_priceavg_profit
0Accessories57.4255.81
1Appliances62.6938.47
2Art9.098.15
3Binders33.0720.72
4Bookcases124.61-19.17
\n", "
\n", "
\n", "\n", "
\n", " \n", "\n", " \n", "\n", " \n", "
\n", "\n", "\n", "
\n", "
\n" ], "application/vnd.google.colaboratory.intrinsic+json": { "type": "dataframe", "variable_name": "df_products", "summary": "{\n \"name\": \"df_products\",\n \"rows\": 17,\n \"fields\": [\n {\n \"column\": \"sub_category\",\n \"properties\": {\n \"dtype\": \"string\",\n \"num_unique_values\": 17,\n \"samples\": [\n \"Accessories\",\n \"Appliances\",\n \"Chairs\"\n ],\n \"semantic_type\": \"\",\n \"description\": \"\"\n }\n },\n {\n \"column\": \"avg_price\",\n \"properties\": {\n \"dtype\": \"number\",\n \"std\": 171.03515695739625,\n \"min\": 3.37,\n \"max\": 651.79,\n \"num_unique_values\": 17,\n \"samples\": [\n 57.42,\n 62.69,\n 138.76\n ],\n \"semantic_type\": \"\",\n \"description\": \"\"\n }\n },\n {\n \"column\": \"avg_profit\",\n \"properties\": {\n \"dtype\": \"number\",\n \"std\": 200.76924667278809,\n \"min\": -55.48,\n \"max\": 837.47,\n \"num_unique_values\": 17,\n \"samples\": [\n 55.81,\n 38.47,\n 43.23\n ],\n \"semantic_type\": \"\",\n \"description\": \"\"\n }\n }\n ]\n}" } }, "metadata": {}, "execution_count": 8 } ], "source": [ "df_products.head()" ] }, { "cell_type": "markdown", "metadata": { "id": "p-1Pr2szaqLk" }, "source": [ "## **3.** 🧩 Create a meaningful connection between real & synthetic datasets" ] }, { "cell_type": "markdown", "metadata": { "id": "SIaJUGIpaH4V" }, "source": [ "### *a. Initial setup*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "-gPXGcRPuV_9" }, "outputs": [], "source": [ "import numpy as np\n", "import random\n", "from datetime import datetime\n", "import warnings\n", "\n", "warnings.filterwarnings(\"ignore\")\n", "random.seed(2025)\n", "np.random.seed(2025)" ] }, { "cell_type": "markdown", "metadata": { "id": "pY4yCoIuaQqp" }, "source": [ "### *b. Generate popularity scores based on avg_profit (with some randomness) with a generate_popularity_score function*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "mnd5hdAbaNjz" }, "outputs": [], "source": [ "def generate_popularity_score(avg_profit):\n", " if avg_profit >= 50:\n", " base = 4\n", " elif avg_profit >= 10:\n", " base = 3\n", " elif avg_profit >= 0:\n", " base = 2\n", " else:\n", " base = 1\n", " trend_factor = random.choices([-1, 0, 1], weights=[1, 3, 2])[0]\n", " return int(np.clip(base + trend_factor, 1, 5))" ] }, { "cell_type": "markdown", "metadata": { "id": "n4-TaNTFgPak" }, "source": [ "### *c. ✋🏻🛑⛔️ Run the function to create a \"popularity_score\" column from \"avg_profit\"*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "V-G3OCUCgR07" }, "outputs": [], "source": [ "df_products[\"popularity_score\"] = df_products[\"avg_profit\"].apply(generate_popularity_score)" ] }, { "cell_type": "markdown", "metadata": { "id": "HnngRNTgacYt" }, "source": [ "### *d. Decide on the sentiment_label based on the popularity score with a get_sentiment function*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "kUtWmr8maZLZ" }, "outputs": [], "source": [ "def get_sentiment(popularity_score):\n", " if popularity_score <= 2:\n", " return \"negative\"\n", " elif popularity_score == 3:\n", " return \"neutral\"\n", " else:\n", " return \"positive\"" ] }, { "cell_type": "markdown", "metadata": { "id": "HF9F9HIzgT7Z" }, "source": [ "### *e. ✋🏻🛑⛔️ Run the function to create a \"sentiment_label\" column from \"popularity_score\"*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "tafQj8_7gYCG" }, "outputs": [], "source": [ "df_products[\"sentiment_label\"] = df_products[\"popularity_score\"].apply(get_sentiment)" ] }, { "cell_type": "markdown", "metadata": { "id": "T8AdKkmASq9a" }, "source": [ "## **4.** 📈 Generate synthetic sub-category sales data of 18 months" ] }, { "cell_type": "markdown", "metadata": { "id": "OhXbdGD5fH0c" }, "source": [ "### *a. Create a generate_sales_profit function that would generate sales patterns based on sentiment_label (with some randomness)*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "qkVhYPXGbgEn" }, "outputs": [], "source": [ "def generate_sales_profile(sentiment):\n", " months = pd.date_range(end=datetime.today(), periods=18, freq=\"M\")\n", "\n", " if sentiment == \"positive\":\n", " base = random.randint(200, 300)\n", " trend = np.linspace(base, base + random.randint(20, 60), len(months))\n", " elif sentiment == \"negative\":\n", " base = random.randint(20, 80)\n", " trend = np.linspace(base, base - random.randint(10, 30), len(months))\n", " else: # neutral\n", " base = random.randint(80, 160)\n", " trend = np.full(len(months), base + random.randint(-10, 10))\n", "\n", " seasonality = 10 * np.sin(np.linspace(0, 3 * np.pi, len(months)))\n", " noise = np.random.normal(0, 5, len(months))\n", " monthly_sales = np.clip(trend + seasonality + noise, a_min=0, a_max=None).astype(int)\n", "\n", " return list(zip(months.strftime(\"%Y-%m\"), monthly_sales))" ] }, { "cell_type": "markdown", "metadata": { "id": "L2ak1HlcgoTe" }, "source": [ "### *b. Run the function as part of building sales_data*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "SlJ24AUafoDB" }, "outputs": [], "source": [ "sales_data = []\n", "for _, row in df_products.iterrows():\n", " records = generate_sales_profile(row[\"sentiment_label\"])\n", " for month, units in records:\n", " sales_data.append({\n", " \"sub_category\": row[\"sub_category\"],\n", " \"month\": month,\n", " \"units_sold\": units,\n", " \"sentiment_label\": row[\"sentiment_label\"]\n", " })" ] }, { "cell_type": "markdown", "metadata": { "id": "4IXZKcCSgxnq" }, "source": [ "### *c. ✋🏻🛑⛔️ Create a df_sales DataFrame from sales_data*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "wcN6gtiZg-ws" }, "outputs": [], "source": [ "df_sales = pd.DataFrame(sales_data)" ] }, { "cell_type": "markdown", "metadata": { "id": "EhIjz9WohAmZ" }, "source": [ "### *d. Save df_sales as synthetic_sales_data.csv & view first few lines*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "MzbZvLcAhGaH", "outputId": "683d930c-27a4-4925-b01f-af8490fa8b9b" }, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ " sub_category month units_sold sentiment_label\n", "0 Accessories 2024-10 223 positive\n", "1 Accessories 2024-11 234 positive\n", "2 Accessories 2024-12 229 positive\n", "3 Accessories 2025-01 236 positive\n", "4 Accessories 2025-02 239 positive\n" ] } ], "source": [ "df_sales.to_csv(\"synthetic_sales_data.csv\", index=False)\n", "\n", "print(df_sales.head())" ] }, { "cell_type": "markdown", "metadata": { "id": "7g9gqBgQMtJn" }, "source": [ "## **5.** 🎯 Generate synthetic customer reviews" ] }, { "cell_type": "markdown", "metadata": { "id": "Gi4y9M9KuDWx" }, "source": [ "### *a. ✋🏻🛑⛔️ Ask ChatGPT to create a list of 50 distinct generic retail product review texts for the sentiment labels \"positive\", \"neutral\", and \"negative\" called synthetic_reviews_by_sentiment*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "b3cd2a50" }, "outputs": [], "source": [ "synthetic_reviews_by_sentiment = {\n", " \"positive\": [\n", " \"This product line is exactly what we needed — quality and value combined.\",\n", " \"Consistently impressed with the performance across every item in this range.\",\n", " \"Our team relies on these products daily — they never let us down.\",\n", " \"Top-tier quality that justifies every cent spent.\",\n", " \"Outstanding selection that meets every requirement our office has.\",\n", " \"Remarkable durability and design. Will definitely reorder.\",\n", " \"These products set the bar — nothing else comes close.\",\n", " \"Every purchase from this category has exceeded expectations.\",\n", " \"Huge uplift in team productivity since we started stocking these.\",\n", " \"Perfect balance of price and quality. Highly recommended.\",\n", " \"A consistent bestseller in our store — customers keep coming back.\",\n", " \"Rarely see products deliver at this level — truly impressive.\",\n", " \"Our highest-rated category by a wide margin.\",\n", " \"Revenue from this range keeps growing quarter over quarter.\",\n", " \"Customers praise reliability and style in equal measure.\",\n", " \"Flawless functionality and great aesthetics — perfect combo.\",\n", " \"Demand for this sub-category is always strong.\",\n", " \"These items turn first-time buyers into loyal repeat customers.\",\n", " \"Low return rates and high satisfaction scores tell the whole story.\",\n", " \"The profit margins from this category are the envy of the floor.\",\n", " \"Never overstocked, never understocked — a category manager's dream.\",\n", " \"Positive word of mouth keeps driving new customers our way.\",\n", " \"An anchor category that props up overall store performance.\",\n", " \"Premium feel at a competitive price — customers notice.\",\n", " \"Strong sell-through rate with minimal markdowns needed.\",\n", " \"Year-on-year growth in this sub-category continues to impress.\",\n", " \"Staff love recommending these — they practically sell themselves.\",\n", " \"Margin-accretive with broad appeal across all customer segments.\",\n", " \"Dependable supply chain and consistent quality from this range.\",\n", " \"A strategic priority that keeps delivering returns.\",\n", " \"These products make every planogram look good.\",\n", " \"Our highest NPS scores come from buyers of this category.\",\n", " \"Inventory turnover in this range is best in class.\",\n", " \"High basket attachment — customers rarely buy just one.\",\n", " \"Category leadership is clearly visible in the sales data.\",\n", " \"Priced right, built right, and always in demand.\",\n", " \"Zero complaints this quarter — quality control is excellent.\",\n", " \"Seasonal lifts are strong and predictable for this sub-category.\",\n", " \"The data backs what the floor team feels: this range is thriving.\",\n", " \"Promotional uplift is consistently strong when we feature this.\",\n", " \"Low shrinkage and high margins make this our star performer.\",\n", " \"Vendor relationship is excellent — orders arrive on time.\",\n", " \"Shelf velocity is outstanding — we rarely see excess stock.\",\n", " \"A go-to category for cross-sell opportunities.\",\n", " \"Customers actively seek this sub-category out.\",\n", " \"Strong online and in-store performance in tandem.\",\n", " \"This range punches above its weight in every metric.\",\n", " \"Customer lifetime value is highest in this category.\",\n", " \"Our data team flagged this as a priority to protect and grow.\",\n", " \"Consistently the top contributor to monthly profit targets.\",\n", " ],\n", " \"neutral\": [\n", " \"Decent products but nothing that really stands out.\",\n", " \"Sells steadily but rarely generates excitement on the floor.\",\n", " \"A serviceable category — not driving growth, not dragging it down.\",\n", " \"Average performance across the board; room for improvement.\",\n", " \"Customers buy when they need to, but won't seek it out.\",\n", " \"Margins are acceptable but could be healthier.\",\n", " \"We hold stock at standard levels — no need to over-invest.\",\n", " \"Reliable but uninspiring category for us.\",\n", " \"Turnover is consistent; just not a headline performer.\",\n", " \"Some lines do well, others just sit there.\",\n", " \"Not a category that drives footfall but serves a purpose.\",\n", " \"Could benefit from a range refresh to boost interest.\",\n", " \"Sales are predictable but plateau-ed for two quarters.\",\n", " \"Customers rate it fine — three stars is the norm.\",\n", " \"We reorder on schedule but rarely see spikes in demand.\",\n", " \"A middle-of-the-range performer in our assortment.\",\n", " \"Acceptable quality with a price point that matches.\",\n", " \"Would benefit from promotional support to lift velocity.\",\n", " \"Returns are low but so is enthusiasm.\",\n", " \"Not losing money here, but not winning either.\",\n", " \"Lacks the wow factor that drives impulse purchases.\",\n", " \"A category we maintain rather than invest in.\",\n", " \"Steady demand with flat growth trajectory.\",\n", " \"Fine as a filler category but not a focus area.\",\n", " \"Moderate satisfaction scores — room to grow.\",\n", " \"No complaints, but no praise either.\",\n", " \"Neither the best nor worst in our assortment.\",\n", " \"Metrics are in range — just not remarkable.\",\n", " \"Could be optimised further to push margins up.\",\n", " \"It does its job without causing problems.\",\n", " \"We've seen better quarters and worse quarters from this range.\",\n", " \"Shelf space allocation seems appropriate for the returns.\",\n", " \"No urgent action needed but worth monitoring.\",\n", " \"Customer feedback is uneventful — nothing to act on urgently.\",\n", " \"Average sell-through rate with seasonal fluctuation.\",\n", " \"An important category but not a strategic priority right now.\",\n", " \"Returns are in line with expectations — nothing alarming.\",\n", " \"Standard performance for a mature product category.\",\n", " \"Moderate margin contribution across the range.\",\n", " \"Sales are ticking along — no cause for alarm or celebration.\",\n", " \"Inventory sits around target levels most weeks.\",\n", " \"We manage this category on autopilot.\",\n", " \"Neither growing the basket nor shrinking it.\",\n", " \"Prices are competitive but not differentiated.\",\n", " \"A solid but unremarkable part of our assortment.\",\n", " \"Customer interest is stable, not building.\",\n", " \"We'll keep it in range until the data tells us otherwise.\",\n", " \"Balanced between fast movers and slow movers.\",\n", " \"A 'wait and see' category for next quarter.\",\n", " \"Performs as expected given market conditions.\",\n", " ],\n", " \"negative\": [\n", " \"Consistently underperforming — needs urgent review.\",\n", " \"Margins are being squeezed and sales volume isn't compensating.\",\n", " \"We're carrying too much stock in a category customers avoid.\",\n", " \"High return rate is eating into what little margin we have.\",\n", " \"Customer feedback is predominantly negative for this range.\",\n", " \"This sub-category drags down our overall category scorecard.\",\n", " \"Markdown frequency is too high — a sign of poor demand.\",\n", " \"Demand has been declining for three consecutive months.\",\n", " \"Slow sell-through is causing costly clearance cycles.\",\n", " \"The vendor quality issues are now showing up in reviews.\",\n", " \"We're losing customers to competitors in this category.\",\n", " \"Profitability is negative on several key lines.\",\n", " \"Poor basket attachment — rarely part of a multi-item purchase.\",\n", " \"Staff are reluctant to recommend this range to customers.\",\n", " \"Shrinkage and damages are above average in this sub-category.\",\n", " \"Supply chain delays have damaged customer trust in this range.\",\n", " \"We're overstocked with no clear path to clear inventory.\",\n", " \"Worst NPS scores in the store come from this category.\",\n", " \"Customers complain about value for money consistently.\",\n", " \"A legacy category that no longer meets modern customer needs.\",\n", " \"The data makes it clear: this sub-category needs rationalising.\",\n", " \"Low traffic and poor conversion make this a liability.\",\n", " \"Promotional support hasn't moved the needle at all.\",\n", " \"Price reductions have failed to stimulate demand.\",\n", " \"A drain on resources — time to consider ranging out.\",\n", " \"Multiple customers have cited quality issues in recent weeks.\",\n", " \"Inventory obsolescence risk is high in this sub-category.\",\n", " \"We need a full range review to fix this underperformance.\",\n", " \"Contribution margin is insufficient to justify space allocation.\",\n", " \"Consistent poor performance across all three KPIs: sales, margin, units.\",\n", " \"The category has no clear differentiation in our assortment.\",\n", " \"Weak competitive positioning with no obvious fix.\",\n", " \"Customer returns have doubled in the past quarter.\",\n", " \"Our worst performing lines sit in this sub-category.\",\n", " \"Brand perception damage is spilling over from this range.\",\n", " \"Order quantities have been cut as confidence has dropped.\",\n", " \"We've tried relaunching this — it hasn't worked.\",\n", " \"No seasonal uplift to speak of — flat and declining.\",\n", " \"Even discounting hasn't driven meaningful volume.\",\n", " \"The opportunity cost of this shelf space is significant.\",\n", " \"Vendor reliability has been poor — impacting availability.\",\n", " \"Category performance is a red flag in every monthly report.\",\n", " \"Stock provision risk is increasing as demand erodes.\",\n", " \"A persistent weak spot that needs a decisive decision.\",\n", " \"Write-downs in this category are becoming a regular occurrence.\",\n", " \"Customer satisfaction surveys single this out for criticism.\",\n", " \"No investment case can be made for this sub-category currently.\",\n", " \"Foot traffic drops off near this section of the store.\",\n", " \"Management escalations about this category are increasing.\",\n", " \"The exit scenario is being actively evaluated.\",\n", " ],\n", "}" ] }, { "cell_type": "markdown", "metadata": { "id": "fQhfVaDmuULT" }, "source": [ "### *b. Generate 10 reviews per sub-category using random sampling from the corresponding 50*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "l2SRc3PjuTGM" }, "outputs": [], "source": [ "review_rows = []\n", "for _, row in df_products.iterrows():\n", " sub_category = row['sub_category']\n", " sentiment_label = row['sentiment_label']\n", " review_pool = synthetic_reviews_by_sentiment[sentiment_label]\n", " sampled_reviews = random.sample(review_pool, 10)\n", " for review_text in sampled_reviews:\n", " review_rows.append({\n", " \"sub_category\": sub_category,\n", " \"sentiment_label\": sentiment_label,\n", " \"review_text\": review_text,\n", " \"avg_profit\": row['avg_profit'],\n", " \"popularity_score\": row['popularity_score']\n", " })" ] }, { "cell_type": "markdown", "metadata": { "id": "bmJMXF-Bukdm" }, "source": [ "### *c. Create the final dataframe df_reviews & save it as synthetic_superstore_reviews.csv*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "id": "ZUKUqZsuumsp" }, "outputs": [], "source": [ "df_reviews = pd.DataFrame(review_rows)\n", "df_reviews.to_csv(\"synthetic_superstore_reviews.csv\", index=False)" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "3946e521", "outputId": "bdb1ca17-6b82-46b9-e790-cd5b20312507" }, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ "✅ Wrote synthetic_title_level_features.csv\n", "✅ Wrote synthetic_monthly_revenue_series.csv\n" ] } ], "source": [ "\n", "# ============================================================\n", "# ✅ Create \"R-ready\" derived inputs (root-level files)\n", "# ============================================================\n", "# These two files make the R notebook robust and fast:\n", "# 1) synthetic_title_level_features.csv -> regression-ready, one row per sub-category\n", "# 2) synthetic_monthly_revenue_series.csv -> forecasting-ready, one row per month\n", "\n", "import numpy as np\n", "\n", "def _safe_num(s):\n", " return pd.to_numeric(\n", " pd.Series(s).astype(str).str.replace(r\"[^0-9.]\", \"\", regex=True),\n", " errors=\"coerce\"\n", " )\n", "\n", "# --- Clean product metadata (avg_price/avg_profit) ---\n", "df_books_r = df_products.copy()\n", "if \"avg_price\" in df_books_r.columns:\n", " df_books_r[\"avg_price\"] = _safe_num(df_books_r[\"avg_price\"])\n", "if \"avg_profit\" in df_books_r.columns:\n", " df_books_r[\"avg_profit\"] = _safe_num(df_books_r[\"avg_profit\"])\n", "\n", "df_books_r[\"sub_category\"] = df_books_r[\"sub_category\"].astype(str).str.strip()\n", "\n", "# --- Clean sales ---\n", "df_sales_r = df_sales.copy()\n", "df_sales_r[\"sub_category\"] = df_sales_r[\"sub_category\"].astype(str).str.strip()\n", "df_sales_r[\"month\"] = pd.to_datetime(df_sales_r[\"month\"], errors=\"coerce\")\n", "df_sales_r[\"units_sold\"] = _safe_num(df_sales_r[\"units_sold\"])\n", "\n", "# --- Clean reviews ---\n", "df_reviews_r = df_reviews.copy()\n", "df_reviews_r[\"sub_category\"] = df_reviews_r[\"sub_category\"].astype(str).str.strip()\n", "df_reviews_r[\"sentiment_label\"] = df_reviews_r[\"sentiment_label\"].astype(str).str.lower().str.strip()\n", "if \"avg_profit\" in df_reviews_r.columns:\n", " df_reviews_r[\"avg_profit\"] = _safe_num(df_reviews_r[\"avg_profit\"])\n", "if \"popularity_score\" in df_reviews_r.columns:\n", " df_reviews_r[\"popularity_score\"] = _safe_num(df_reviews_r[\"popularity_score\"])\n", "\n", "# --- Sentiment shares per sub-category (from reviews) ---\n", "sent_counts = (\n", " df_reviews_r.groupby([\"sub_category\", \"sentiment_label\"])\n", " .size()\n", " .unstack(fill_value=0)\n", ")\n", "for lab in [\"positive\", \"neutral\", \"negative\"]:\n", " if lab not in sent_counts.columns:\n", " sent_counts[lab] = 0\n", "\n", "sent_counts[\"total_reviews\"] = sent_counts[[\"positive\", \"neutral\", \"negative\"]].sum(axis=1)\n", "den = sent_counts[\"total_reviews\"].replace(0, np.nan)\n", "sent_counts[\"share_positive\"] = sent_counts[\"positive\"] / den\n", "sent_counts[\"share_neutral\"] = sent_counts[\"neutral\"] / den\n", "sent_counts[\"share_negative\"] = sent_counts[\"negative\"] / den\n", "sent_counts = sent_counts.reset_index()\n", "\n", "# --- Sales aggregation per sub-category ---\n", "sales_by_title = (\n", " df_sales_r.dropna(subset=[\"sub_category\"])\n", " .groupby(\"sub_category\", as_index=False)\n", " .agg(\n", " months_observed=(\"month\", \"nunique\"),\n", " avg_units_sold=(\"units_sold\", \"mean\"),\n", " total_units_sold=(\"units_sold\", \"sum\"),\n", " )\n", ")\n", "\n", "# --- Sub-category-level features (join sales + products + sentiment) ---\n", "df_title = (\n", " sales_by_title\n", " .merge(df_books_r[[\"sub_category\", \"avg_price\", \"avg_profit\"]], on=\"sub_category\", how=\"left\")\n", " .merge(sent_counts[[\"sub_category\", \"share_positive\", \"share_neutral\", \"share_negative\", \"total_reviews\"]],\n", " on=\"sub_category\", how=\"left\")\n", ")\n", "\n", "df_title[\"avg_revenue\"] = df_title[\"avg_units_sold\"] * df_title[\"avg_price\"]\n", "df_title[\"total_revenue\"] = df_title[\"total_units_sold\"] * df_title[\"avg_price\"]\n", "\n", "df_title.to_csv(\"synthetic_title_level_features.csv\", index=False)\n", "print(\"✅ Wrote synthetic_title_level_features.csv\")\n", "\n", "# --- Monthly revenue series (proxy: units_sold * avg_price) ---\n", "monthly_rev = (\n", " df_sales_r.merge(df_books_r[[\"sub_category\", \"avg_price\"]], on=\"sub_category\", how=\"left\")\n", ")\n", "monthly_rev[\"revenue\"] = monthly_rev[\"units_sold\"] * monthly_rev[\"avg_price\"]\n", "\n", "df_monthly = (\n", " monthly_rev.dropna(subset=[\"month\"])\n", " .groupby(\"month\", as_index=False)[\"revenue\"]\n", " .sum()\n", " .rename(columns={\"revenue\": \"total_revenue\"})\n", " .sort_values(\"month\")\n", ")\n", "# if revenue is all NA (e.g., missing price), fallback to units_sold as a teaching proxy\n", "if df_monthly[\"total_revenue\"].notna().sum() == 0:\n", " df_monthly = (\n", " df_sales_r.dropna(subset=[\"month\"])\n", " .groupby(\"month\", as_index=False)[\"units_sold\"]\n", " .sum()\n", " .rename(columns={\"units_sold\": \"total_revenue\"})\n", " .sort_values(\"month\")\n", " )\n", "\n", "df_monthly[\"month\"] = pd.to_datetime(df_monthly[\"month\"], errors=\"coerce\").dt.strftime(\"%Y-%m-%d\")\n", "df_monthly.to_csv(\"synthetic_monthly_revenue_series.csv\", index=False)\n", "print(\"✅ Wrote synthetic_monthly_revenue_series.csv\")\n" ] }, { "cell_type": "markdown", "metadata": { "id": "RYvGyVfXuo54" }, "source": [ "### *d. ✋🏻🛑⛔️ View the first few lines*" ] }, { "cell_type": "code", "execution_count": null, "metadata": { "colab": { "base_uri": "https://localhost:8080/" }, "id": "xfE8NMqOurKo", "outputId": "0155e713-b16c-43e0-bc90-46183e48ce67" }, "outputs": [ { "output_type": "stream", "name": "stdout", "text": [ " sub_category sentiment_label \\\n", "0 Accessories positive \n", "1 Accessories positive \n", "2 Accessories positive \n", "3 Accessories positive \n", "4 Accessories positive \n", "\n", " review_text avg_profit \\\n", "0 Category leadership is clearly visible in the ... 55.81 \n", "1 A strategic priority that keeps delivering ret... 55.81 \n", "2 Year-on-year growth in this sub-category conti... 55.81 \n", "3 Customers actively seek this sub-category out. 55.81 \n", "4 Outstanding selection that meets every require... 55.81 \n", "\n", " popularity_score \n", "0 4 \n", "1 4 \n", "2 4 \n", "3 4 \n", "4 4 \n" ] } ], "source": [ "print(df_reviews.head())" ] } ], "metadata": { "colab": { "provenance": [] }, "kernelspec": { "display_name": "Python 3", "name": "python3" }, "language_info": { "name": "python" } }, "nbformat": 4, "nbformat_minor": 0 }