User Purchase Platform.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "7f1def30-d48f-4409-918b-aa0799b9ea7d",
   "metadata": {},
   "source": [
    "# User Purchase Platform\n",
    "\n",
    "## Table Schema: Spending\n",
    "\n",
    "| Column Name | Type    |\n",
    "|-------------|---------|\n",
    "| user_id     | int     |\n",
    "| spend_date  | date    |\n",
    "| platform    | str     | \n",
    "| amount      | int     |\n",
    "\n",
    "- **Description**:  \n",
    "  The `Spending` table logs the history of user spending on an online shopping website that offers both desktop and mobile applications.\n",
    "\n",
    "- **Primary Key**:  \n",
    "  The combination of `(user_id, spend_date, platform)` ensures each record is unique.\n",
    "\n",
    "- **Platform Str**:  \n",
    "  The `platform` column is a str type with possible values: `'desktop'`, `'mobile'`.\n",
    "\n",
    "## Problem Statement\n",
    "\n",
    "Write a solution to find the **total number of users** and the **total amount spent** using:\n",
    "\n",
    "- **Mobile only**\n",
    "- **Desktop only**\n",
    "- **Both mobile and desktop together**\n",
    "\n",
    "for each date.\n",
    "\n",
    "**Return the result table in any order.**\n",
    "\n",
    "### Example\n",
    "\n",
    "#### Input\n",
    "\n",
    "**Spending Table:**\n",
    "\n",
    "| user_id | spend_date | platform | amount |\n",
    "|---------|------------|----------|--------|\n",
    "| 1       | 2019-07-01 | mobile   | 100    |\n",
    "| 1       | 2019-07-01 | desktop  | 100    |\n",
    "| 2       | 2019-07-01 | mobile   | 100    |\n",
    "| 2       | 2019-07-02 | mobile   | 100    |\n",
    "| 3       | 2019-07-01 | desktop  | 100    |\n",
    "| 3       | 2019-07-02 | desktop  | 100    |\n",
    "\n",
    "#### Output\n",
    "\n",
    "| spend_date | platform | total_amount | total_users |\n",
    "|------------|----------|--------------|-------------|\n",
    "| 2019-07-01 | desktop  | 100          | 1           |\n",
    "| 2019-07-01 | mobile   | 100          | 1           |\n",
    "| 2019-07-01 | both     | 200          | 1           |\n",
    "| 2019-07-02 | desktop  | 100          | 1           |\n",
    "| 2019-07-02 | mobile   | 100          | 1           |\n",
    "| 2019-07-02 | both     | 0            | 0           |\n",
    "\n",
    "#### Explanation\n",
    "\n",
    "- **On 2019-07-01**:\n",
    "  - **User 1** purchased using **both** desktop and mobile.\n",
    "  - **User 2** purchased using **mobile only**.\n",
    "  - **User 3** purchased using **desktop only**.\n",
    "\n",
    "- **On 2019-07-02**:\n",
    "  - **User 2** purchased using **mobile only**.\n",
    "  - **User 3** purchased using **desktop only**.\n",
    "  - No users purchased using **both** platforms.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6adade0d-295d-42df-ab9f-da463d04e2ac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>spend_date</th>\n",
       "      <th>platform</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id spend_date platform  amount\n",
       "0        1 2019-07-01   mobile     100\n",
       "1        1 2019-07-01  desktop     100\n",
       "2        2 2019-07-01   mobile     100\n",
       "3        2 2019-07-02   mobile     100\n",
       "4        3 2019-07-01  desktop     100\n",
       "5        3 2019-07-02  desktop     100"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Sample Data\n",
    "data = [\n",
    "    [1, '2019-07-01', 'mobile', 100],\n",
    "    [1, '2019-07-01', 'desktop', 100],\n",
    "    [2, '2019-07-01', 'mobile', 100],\n",
    "    [2, '2019-07-02', 'mobile', 100],\n",
    "    [3, '2019-07-01', 'desktop', 100],\n",
    "    [3, '2019-07-02', 'desktop', 100]\n",
    "]\n",
    "\n",
    "# Create DataFrame\n",
    "spending = pd.DataFrame(\n",
    "    data,\n",
    "    columns=['user_id', 'spend_date', 'platform', 'amount']\n",
    ").astype({\n",
    "    'user_id': 'Int64',\n",
    "    'spend_date': 'datetime64[ns]',\n",
    "    'platform': 'object',\n",
    "    'amount': 'Int64'\n",
    "})\n",
    "\n",
    "display(spending)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "605c5f3a-0cdc-47a1-bbd0-1feec01da043",
   "metadata": {},
   "source": [
    "## Solution 01\n",
    "\n",
    "We can solve this problem using **Pandas** by following these steps:\n",
    "\n",
    "**Step 1: Group by spend_date and user_id to aggregate platforms and amounts**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "37e4f454-e9e6-4e9c-b36f-ec370618b09b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>spend_date</th>\n",
       "      <th>user_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>1</td>\n",
       "      <td>{desktop, mobile}</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>2</td>\n",
       "      <td>{mobile}</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>3</td>\n",
       "      <td>{desktop}</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>2</td>\n",
       "      <td>{mobile}</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>3</td>\n",
       "      <td>{desktop}</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  spend_date  user_id           platform  amount\n",
       "0 2019-07-01        1  {desktop, mobile}     200\n",
       "1 2019-07-01        2           {mobile}     100\n",
       "2 2019-07-01        3          {desktop}     100\n",
       "3 2019-07-02        2           {mobile}     100\n",
       "4 2019-07-02        3          {desktop}     100"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = spending.groupby(['spend_date', 'user_id']).agg({\n",
    "    'platform': lambda x: set(x),\n",
    "    'amount': 'sum'\n",
    "}).reset_index()\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e7e37c8-1d6e-40b7-8279-13cfd36afaa4",
   "metadata": {},
   "source": [
    "**Step 2: Categorize each user based on the platforms used**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "350d2e83-cfc5-4fcb-b8f9-971e71ae4a00",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>spend_date</th>\n",
       "      <th>user_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>2</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>3</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>2</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>3</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  spend_date  user_id platform  amount\n",
       "0 2019-07-01        1     both     200\n",
       "1 2019-07-01        2   mobile     100\n",
       "2 2019-07-01        3  desktop     100\n",
       "3 2019-07-02        2   mobile     100\n",
       "4 2019-07-02        3  desktop     100"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def categorize_platform(platforms):\n",
    "    if platforms == {'mobile'}:\n",
    "        return 'mobile'\n",
    "    elif platforms == {'desktop'}:\n",
    "        return 'desktop'\n",
    "    elif platforms == {'mobile', 'desktop'}:\n",
    "        return 'both'\n",
    "    else:\n",
    "        return 'other'  # For any unexpected cases\n",
    "\n",
    "df['platform'] = df['platform'].apply(categorize_platform)\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24ba4ec6-8dc9-4e0d-95b7-f47d0c945f6b",
   "metadata": {},
   "source": [
    "**Step 3: Aggregate total_amount and total_users for each spend_date and platform_category**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "9d612e61-492e-4a8f-9db8-ec3c61fcbfbb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>spend_date</th>\n",
       "      <th>platform</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>total_users</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>both</td>\n",
       "      <td>200</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  spend_date platform  total_amount  total_users\n",
       "0 2019-07-01     both           200            1\n",
       "1 2019-07-01  desktop           100            1\n",
       "2 2019-07-01   mobile           100            1\n",
       "3 2019-07-02  desktop           100            1\n",
       "4 2019-07-02   mobile           100            1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = df.groupby(['spend_date', 'platform']).agg(\n",
    "    total_amount=('amount', 'sum'),\n",
    "    total_users=('user_id', 'nunique')\n",
    ").reset_index()\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b50a8841-8a18-4a6e-858e-25abab4f328f",
   "metadata": {},
   "source": [
    "**Step 4: Ensure all combinations of spend_date and platforms are present**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "21ffc03b-b708-4034-8782-e6690dba2c15",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>spend_date</th>\n",
       "      <th>platform</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>mobile</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>desktop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>mobile</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>desktop</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  spend_date platform\n",
       "0 2019-07-01   mobile\n",
       "1 2019-07-01  desktop\n",
       "2 2019-07-01     both\n",
       "3 2019-07-02   mobile\n",
       "4 2019-07-02  desktop\n",
       "5 2019-07-02     both"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Define all possible categories\n",
    "categories = ['mobile', 'desktop', 'both']\n",
    "\n",
    "# Create a data frame with all combinations of spend_date and platform\n",
    "all_combinations = pd.MultiIndex.from_product(\n",
    "    [spending['spend_date'].unique(), categories],\n",
    "    names=['spend_date', 'platform']\n",
    ").to_frame(index=False)\n",
    "\n",
    "display(all_combinations)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56c419a4-f7e7-4732-87f1-6d717faf1123",
   "metadata": {},
   "source": [
    "**Step 5: Merge with the df to include missing combinations with default values**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "42e1f0cd-13dd-4c97-816d-8e1aacf51d65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>spend_date</th>\n",
       "      <th>platform</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>total_users</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>both</td>\n",
       "      <td>200</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  spend_date platform  total_amount  total_users\n",
       "0 2019-07-01     both           200            1\n",
       "1 2019-07-01  desktop           100            1\n",
       "2 2019-07-01   mobile           100            1\n",
       "3 2019-07-02     both             0            0\n",
       "4 2019-07-02  desktop           100            1\n",
       "5 2019-07-02   mobile           100            1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = all_combinations.merge(\n",
    "    df,\n",
    "    on=['spend_date', 'platform'],\n",
    "    how='left'\n",
    ").fillna({'total_amount': 0, 'total_users': 0})\n",
    "\n",
    "# Convert total_amount and total_users to integer type\n",
    "df['total_amount'] = df['total_amount'].astype(int)\n",
    "df['total_users'] = df['total_users'].astype(int)\n",
    "\n",
    "# Optional: Sort for better readability\n",
    "df = df.sort_values(by=['spend_date', 'platform']).reset_index(drop=True)\n",
    "\n",
    "# Display the final output\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d82d9c58-8e3f-4776-a5be-e3af29e70397",
   "metadata": {},
   "source": [
    "## Solution 02\n",
    "\n",
    "We can solve this problem using **Pandas** by following these steps:\n",
    "\n",
    "**Step 1: Identify spending by platform.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c5788f76-70c1-4fef-8a37-801085fee8eb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>spend_date</th>\n",
       "      <th>user_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>1</td>\n",
       "      <td>mobiledesktop</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>2</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>3</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>2</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>3</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  spend_date  user_id       platform  amount\n",
       "0 2019-07-01        1  mobiledesktop     200\n",
       "1 2019-07-01        2         mobile     100\n",
       "2 2019-07-01        3        desktop     100\n",
       "3 2019-07-02        2         mobile     100\n",
       "4 2019-07-02        3        desktop     100"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = spending.groupby([\"spend_date\", \"user_id\"], as_index=False).sum()\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "261ef19d-cc24-44e7-bee9-7e80f9352469",
   "metadata": {},
   "source": [
    "**Step 2: Replace \"desktopmobile\" or \"mobiledesktop\" with \"both\".**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "83bb82b3-881e-4b3e-a089-c05b37787c9c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>spend_date</th>\n",
       "      <th>user_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>2</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>3</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>2</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>3</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  spend_date  user_id platform  amount\n",
       "0 2019-07-01        1     both     200\n",
       "1 2019-07-01        2   mobile     100\n",
       "2 2019-07-01        3  desktop     100\n",
       "3 2019-07-02        2   mobile     100\n",
       "4 2019-07-02        3  desktop     100"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df.loc[(df[\"platform\"] != \"mobile\") & (df[\"platform\"] != \"desktop\"), \"platform\"] = \"both\"\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06dbf44b-35eb-4668-80b2-dc99b8acc614",
   "metadata": {},
   "source": [
    "**Step 3: Set platform as category to show \"0\" results.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f018e154-285b-4912-9b93-7cd0427822bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>spend_date</th>\n",
       "      <th>user_id</th>\n",
       "      <th>platform</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>1</td>\n",
       "      <td>both</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>2</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>3</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>2</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>3</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  spend_date  user_id platform  amount\n",
       "0 2019-07-01        1     both     200\n",
       "1 2019-07-01        2   mobile     100\n",
       "2 2019-07-01        3  desktop     100\n",
       "3 2019-07-02        2   mobile     100\n",
       "4 2019-07-02        3  desktop     100"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df[\"platform\"] = df[\"platform\"].astype(\"category\").cat.set_categories([\"desktop\", \"mobile\", \"both\"])\n",
    "\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a6fede0-46f6-48cb-aa0d-cef1224dc0e4",
   "metadata": {},
   "source": [
    "**Step 4: Return dataframe using final groupby aggregations.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "607853e4-11a5-4add-8adb-89134db17189",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "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>spend_date</th>\n",
       "      <th>platform</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>total_users</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-07-01</td>\n",
       "      <td>both</td>\n",
       "      <td>200</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>desktop</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>mobile</td>\n",
       "      <td>100</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019-07-02</td>\n",
       "      <td>both</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  spend_date platform  total_amount  total_users\n",
       "0 2019-07-01  desktop           100            1\n",
       "1 2019-07-01   mobile           100            1\n",
       "2 2019-07-01     both           200            1\n",
       "3 2019-07-02  desktop           100            1\n",
       "4 2019-07-02   mobile           100            1\n",
       "5 2019-07-02     both             0            0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df = df.groupby([\"spend_date\", \"platform\"], \n",
    "                observed=False).agg(total_amount=(\"amount\", \"sum\"), \n",
    "                                    total_users=(\"user_id\", \"count\")).reset_index()\n",
    "\n",
    "display(df)"
   ]
  }
 ],
 "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.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}