Market Analysis II.ipynb

{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "# Market Analysis using Pandas\n",
    "\n",
    "Online marketplaces thrive on data, and understanding user behavior is key to driving growth and enhancing the user experience. One interesting challenge is analyzing the preferences of users who sell items on the platform. For instance, do sellers tend to offer items from their favorite brands? How can we determine patterns in their selling behavior based on transactional data?\n",
    "\n",
    "In this post, we’ll tackle a unique data problem where we analyze user activity to identify whether the second item sold by a user aligns with their favorite brand. This involves integrating data from multiple tables—Users, Orders, and Items—and applying a systematic approach to solve the problem.\n",
    "\n",
    "By the end of this blog, you'll gain insights into handling multi-table queries, dealing with chronological data, and deriving actionable insights from marketplace activity. Let’s dive in!\n",
    "\n",
    "## Problem Statement\n",
    "\n",
    "### Tables\n",
    "\n",
    "#### Users\n",
    "| Column Name    | Type    |\n",
    "|----------------|---------|\n",
    "| user_id        | int     |\n",
    "| join_date      | date    |\n",
    "| favorite_brand | varchar |\n",
    "\n",
    "- `user_id` is the primary key of this table.\n",
    "- This table contains information about users of an online shopping website where users can buy and sell items.\n",
    "\n",
    "#### Orders\n",
    "| Column Name   | Type    |\n",
    "|---------------|---------|\n",
    "| order_id      | int     |\n",
    "| order_date    | date    |\n",
    "| item_id       | int     |\n",
    "| buyer_id      | int     |\n",
    "| seller_id     | int     |\n",
    "\n",
    "- `order_id` is the primary key of this table.\n",
    "- `item_id` is a foreign key referencing the `Items` table.\n",
    "- `buyer_id` and `seller_id` are foreign keys referencing the `Users` table.\n",
    "\n",
    "#### Items\n",
    "| Column Name   | Type    |\n",
    "|---------------|---------|\n",
    "| item_id       | int     |\n",
    "| item_brand    | varchar |\n",
    "\n",
    "- `item_id` is the primary key of this table.\n",
    "\n",
    "---\n",
    "\n",
    "### Task\n",
    "\n",
    "For each user, determine whether the brand of the second item (by date) they sold is their favorite brand. If a user sold less than two items, report the answer for that user as \"no.\" \n",
    "\n",
    "It is guaranteed that no seller sells more than one item on the same day.\n",
    "\n",
    "---\n",
    "\n",
    "### Expected Output\n",
    "\n",
    "| seller_id | 2nd_item_fav_brand |\n",
    "|-----------|--------------------|\n",
    "| 1         | no                 |\n",
    "| 2         | yes                |\n",
    "| 3         | yes                |\n",
    "| 4         | no                 |\n",
    "\n",
    "---\n",
    "\n",
    "### Example Input\n",
    "\n",
    "#### Users Table:\n",
    "| user_id | join_date  | favorite_brand |\n",
    "|---------|------------|----------------|\n",
    "| 1       | 2019-01-01 | Lenovo         |\n",
    "| 2       | 2019-02-09 | Samsung        |\n",
    "| 3       | 2019-01-19 | LG             |\n",
    "| 4       | 2019-05-21 | HP             |\n",
    "\n",
    "#### Orders Table:\n",
    "| order_id | order_date | item_id | buyer_id | seller_id |\n",
    "|----------|------------|---------|----------|-----------|\n",
    "| 1        | 2019-08-01 | 4       | 1        | 2         |\n",
    "| 2        | 2019-08-02 | 2       | 1        | 3         |\n",
    "| 3        | 2019-08-03 | 3       | 2        | 3         |\n",
    "| 4        | 2019-08-04 | 1       | 4        | 2         |\n",
    "| 5        | 2019-08-04 | 1       | 3        | 4         |\n",
    "| 6        | 2019-08-05 | 2       | 2        | 4         |\n",
    "\n",
    "#### Items Table:\n",
    "| item_id | item_brand |\n",
    "|---------|------------|\n",
    "| 1       | Samsung    |\n",
    "| 2       | Lenovo     |\n",
    "| 3       | LG         |\n",
    "| 4       | HP         |\n",
    "\n",
    "---\n",
    "\n",
    "### Explanation\n",
    "\n",
    "1. **User with `user_id = 1`:**\n",
    "   - Sold no items.\n",
    "   - Result: `no`.\n",
    "\n",
    "2. **User with `user_id = 2`:**\n",
    "   - Second sold item: `item_id = 1` (Brand: `Samsung`).\n",
    "   - Favorite brand: `Samsung`.\n",
    "   - Result: `yes`.\n",
    "\n",
    "3. **User with `user_id = 3`:**\n",
    "   - Second sold item: `item_id = 3` (Brand: `LG`).\n",
    "   - Favorite brand: `LG`.\n",
    "   - Result: `yes`.\n",
    "\n",
    "4. **User with `user_id = 4`:**\n",
    "   - Second sold item: `item_id = 2` (Brand: `Lenovo`).\n",
    "   - Favorite brand: `HP`.\n",
    "   - Result: `no`.\n"
   ],
   "id": "86fcea25a8b6b907"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T06:21:27.593701Z",
     "start_time": "2025-01-23T06:21:27.588356Z"
    }
   },
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "data = [[1, '2019-01-01', 'Lenovo'], \n",
    "        [2, '2019-02-09', 'Samsung'], \n",
    "        [3, '2019-01-19', 'LG'], \n",
    "        [4, '2019-05-21', 'HP']]\n",
    "users = pd.DataFrame(data, \n",
    "         columns=['user_id', \n",
    "                  'join_date', \n",
    "                  'favorite_brand']).astype({'user_id':'Int64', \n",
    "                  'join_date':'datetime64[ns]', \n",
    "                  'favorite_brand':'object'})\n",
    "display(users)"
   ],
   "id": "7783d285-319a-45ff-bb83-a55340a00d2a",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   user_id  join_date favorite_brand\n",
       "0        1 2019-01-01         Lenovo\n",
       "1        2 2019-02-09        Samsung\n",
       "2        3 2019-01-19             LG\n",
       "3        4 2019-05-21             HP"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>join_date</th>\n",
       "      <th>favorite_brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-01-01</td>\n",
       "      <td>Lenovo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-02-09</td>\n",
       "      <td>Samsung</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2019-01-19</td>\n",
       "      <td>LG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2019-05-21</td>\n",
       "      <td>HP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 53
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T06:21:27.617071Z",
     "start_time": "2025-01-23T06:21:27.611444Z"
    }
   },
   "cell_type": "code",
   "source": [
    "data = [[1, '2019-08-01', 4, 1, 2], \n",
    "        [2, '2019-08-02', 2, 1, 3], \n",
    "        [3, '2019-08-03', 3, 2, 3], \n",
    "        [4, '2019-08-04', 1, 4, 2], \n",
    "        [5, '2019-08-04', 1, 3, 4], \n",
    "        [6, '2019-08-05', 2, 2, 4]]\n",
    "orders = pd.DataFrame(data, \n",
    "         columns=['order_id', \n",
    "                  'order_date', \n",
    "                  'item_id', \n",
    "                  'buyer_id', \n",
    "                  'seller_id']).astype({'order_id':'Int64', \n",
    "                  'order_date':'datetime64[ns]', \n",
    "                  'item_id':'Int64', \n",
    "                  'buyer_id':'Int64', \n",
    "                  'seller_id':'Int64'}) \n",
    "display(orders)"
   ],
   "id": "841fa8137120ca0",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   order_id order_date  item_id  buyer_id  seller_id\n",
       "0         1 2019-08-01        4         1          2\n",
       "1         2 2019-08-02        2         1          3\n",
       "2         3 2019-08-03        3         2          3\n",
       "3         4 2019-08-04        1         4          2\n",
       "4         5 2019-08-04        1         3          4\n",
       "5         6 2019-08-05        2         2          4"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>item_id</th>\n",
       "      <th>buyer_id</th>\n",
       "      <th>seller_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-08-01</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-08-02</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2019-08-03</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2019-08-04</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2019-08-04</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>2019-08-05</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 54
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T06:21:27.621844Z",
     "start_time": "2025-01-23T06:21:27.617929Z"
    }
   },
   "cell_type": "code",
   "source": [
    "data = [[1, 'Samsung'], \n",
    "        [2, 'Lenovo'], \n",
    "        [3, 'LG'], \n",
    "        [4, 'HP']]\n",
    "items = pd.DataFrame(data, \n",
    "         columns=['item_id', \n",
    "                  'item_brand']).astype({'item_id':'Int64', \n",
    "                  'item_brand':'object'})\n",
    "display(items)"
   ],
   "id": "15636bfc2cc68ea4",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   item_id item_brand\n",
       "0        1    Samsung\n",
       "1        2     Lenovo\n",
       "2        3         LG\n",
       "3        4         HP"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>item_id</th>\n",
       "      <th>item_brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Samsung</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Lenovo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>LG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>HP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 55
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 1: Rank Orders for Each Seller**\n",
    "- Adds a new column rank to the orders DataFrame.\n",
    "- Assigns a rank to each order for a seller based on the order date. The method=\"first\" ensures that if two orders have the same date, they are ranked in the order they appear in the dataset."
   ],
   "id": "20d7cfed06dec68a"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T06:21:27.627206Z",
     "start_time": "2025-01-23T06:21:27.622658Z"
    }
   },
   "cell_type": "code",
   "source": [
    "orders[\"rank\"] = orders.groupby([\"seller_id\"])['order_date'].rank(method=\"first\")\n",
    "display(orders)"
   ],
   "id": "b258a98229130255",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   order_id order_date  item_id  buyer_id  seller_id  rank\n",
       "0         1 2019-08-01        4         1          2   1.0\n",
       "1         2 2019-08-02        2         1          3   1.0\n",
       "2         3 2019-08-03        3         2          3   2.0\n",
       "3         4 2019-08-04        1         4          2   2.0\n",
       "4         5 2019-08-04        1         3          4   1.0\n",
       "5         6 2019-08-05        2         2          4   2.0"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>item_id</th>\n",
       "      <th>buyer_id</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-08-01</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-08-02</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2019-08-03</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2019-08-04</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2019-08-04</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>2019-08-05</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 56
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 2: Filter for Second Sold Items**\n",
    "- Filters the orders DataFrame to include only rows where the rank is 2.\n",
    "- Selects the second item sold by each seller.\n"
   ],
   "id": "a93b49462a8ce8c1"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T06:21:27.632236Z",
     "start_time": "2025-01-23T06:21:27.628398Z"
    }
   },
   "cell_type": "code",
   "source": [
    "orders = orders[orders[\"rank\"] == 2]\n",
    "display(orders)"
   ],
   "id": "55ab94fd5619476c",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   order_id order_date  item_id  buyer_id  seller_id  rank\n",
       "2         3 2019-08-03        3         2          3   2.0\n",
       "3         4 2019-08-04        1         4          2   2.0\n",
       "5         6 2019-08-05        2         2          4   2.0"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>item_id</th>\n",
       "      <th>buyer_id</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2019-08-03</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2019-08-04</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>2019-08-05</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 57
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 3: Merge with Items Data**\n",
    "- Joins the orders DataFrame with the items DataFrame on item_id using a left join.\n",
    "- Adds the item_brand information to the orders DataFrame, which will be used to compare with the seller's favorite brand.\n"
   ],
   "id": "2bd820b3371a3682"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T06:21:27.637383Z",
     "start_time": "2025-01-23T06:21:27.632735Z"
    }
   },
   "cell_type": "code",
   "source": [
    "orders = orders.merge(items, how=\"left\", on=\"item_id\")\n",
    "display(orders)"
   ],
   "id": "5eeae6eaa0d6a6c9",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   order_id order_date  item_id  buyer_id  seller_id  rank item_brand\n",
       "0         3 2019-08-03        3         2          3   2.0         LG\n",
       "1         4 2019-08-04        1         4          2   2.0    Samsung\n",
       "2         6 2019-08-05        2         2          4   2.0     Lenovo"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>order_id</th>\n",
       "      <th>order_date</th>\n",
       "      <th>item_id</th>\n",
       "      <th>buyer_id</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>rank</th>\n",
       "      <th>item_brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>2019-08-03</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2.0</td>\n",
       "      <td>LG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>2019-08-04</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Samsung</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>2019-08-05</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Lenovo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 58
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 4: Merge with Users Data**\n",
    "- Joins the users DataFrame with the orders DataFrame on user_id and seller_id using a left join.\n",
    "- Combines user information (like favorite_brand) with the orders containing the second sold item."
   ],
   "id": "bd31a9295ae56e83"
  },
  {
   "cell_type": "code",
   "id": "e4a09d20-01b3-4f31-beb5-542a0b38a08d",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T06:21:27.642023Z",
     "start_time": "2025-01-23T06:21:27.637926Z"
    }
   },
   "source": [
    "df = users.merge(orders, \n",
    "                 how=\"left\", \n",
    "                 right_on=\"seller_id\", \n",
    "                 left_on=\"user_id\")\n",
    "\n",
    "df = df.drop(columns=['join_date', 'order_id', 'order_date',\n",
    "       'item_id', 'buyer_id', 'rank'])\n",
    "display(df)"
   ],
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   user_id favorite_brand  seller_id item_brand\n",
       "0        1         Lenovo       <NA>        NaN\n",
       "1        2        Samsung          2    Samsung\n",
       "2        3             LG          3         LG\n",
       "3        4             HP          4     Lenovo"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>favorite_brand</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>item_brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Lenovo</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>2</td>\n",
       "      <td>Samsung</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>LG</td>\n",
       "      <td>3</td>\n",
       "      <td>LG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>HP</td>\n",
       "      <td>4</td>\n",
       "      <td>Lenovo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 59
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 5: Check if Second Item Matches Favorite Brand**\n",
    "- Creates a new column 2nd_item_fav_brand to indicate whether the item_brand of the second sold item matches the user's favorite_brand.\n",
    "- Evaluates the condition and assigns \"yes\" if they match, otherwise \"no.\"\n"
   ],
   "id": "8fe8b87cc779a027"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T06:21:27.645976Z",
     "start_time": "2025-01-23T06:21:27.642618Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df[\"2nd_item_fav_brand\"] = np.where(df[\"favorite_brand\"] == df[\"item_brand\"], \"yes\", \"no\")\n",
    "display(df)"
   ],
   "id": "916a0da903b7ea8d",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   user_id favorite_brand  seller_id item_brand 2nd_item_fav_brand\n",
       "0        1         Lenovo       <NA>        NaN                 no\n",
       "1        2        Samsung          2    Samsung                yes\n",
       "2        3             LG          3         LG                yes\n",
       "3        4             HP          4     Lenovo                 no"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>favorite_brand</th>\n",
       "      <th>seller_id</th>\n",
       "      <th>item_brand</th>\n",
       "      <th>2nd_item_fav_brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Lenovo</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>2</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>LG</td>\n",
       "      <td>3</td>\n",
       "      <td>LG</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>HP</td>\n",
       "      <td>4</td>\n",
       "      <td>Lenovo</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 60
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 6: Clean Up and Rename Columns**\n",
    "- Drops the duplicate seller_id column (since it exists in both users and orders) and renames user_id to seller_id for consistency.\n",
    "- Ensures a clean and consistent structure for the final result."
   ],
   "id": "60f15f828a62020d"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T06:21:27.650269Z",
     "start_time": "2025-01-23T06:21:27.647124Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df = df.drop([\"seller_id\"], axis=1).rename(columns={\"user_id\": \"seller_id\"})\n",
    "display(df)"
   ],
   "id": "a13c355765651ea2",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   seller_id favorite_brand item_brand 2nd_item_fav_brand\n",
       "0          1         Lenovo        NaN                 no\n",
       "1          2        Samsung    Samsung                yes\n",
       "2          3             LG         LG                yes\n",
       "3          4             HP     Lenovo                 no"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seller_id</th>\n",
       "      <th>favorite_brand</th>\n",
       "      <th>item_brand</th>\n",
       "      <th>2nd_item_fav_brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Lenovo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>LG</td>\n",
       "      <td>LG</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>HP</td>\n",
       "      <td>Lenovo</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 61
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "**Step 7: Select Relevant Columns for Output**\n",
    "- Selects only the seller_id and 2nd_item_fav_brand columns from the DataFrame for the final output.\n",
    "- Outputs the result in the desired format, showing each seller and whether the brand of their second sold item matches their favorite brand."
   ],
   "id": "af28b18598a08248"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-01-23T06:21:27.653479Z",
     "start_time": "2025-01-23T06:21:27.650747Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df = df[[\"seller_id\", \"2nd_item_fav_brand\"]]\n",
    "display(df)"
   ],
   "id": "1c4c7ac259ed3e3c",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "   seller_id 2nd_item_fav_brand\n",
       "0          1                 no\n",
       "1          2                yes\n",
       "2          3                yes\n",
       "3          4                 no"
      ],
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>seller_id</th>\n",
       "      <th>2nd_item_fav_brand</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "execution_count": 62
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": [
    "References: \n",
    "[1] https://leetcode.com/problems/market-analysis-ii/?lang=pythondata"
   ],
   "id": "e1178a37b78ffbb7"
  }
 ],
 "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.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}