Amazon python pandas project.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "64f3ada7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "45117f7b",
   "metadata": {
    "scrolled": true
   },
   "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>index</th>\n",
       "      <th>order_id</th>\n",
       "      <th>date</th>\n",
       "      <th>status</th>\n",
       "      <th>fulfilment</th>\n",
       "      <th>sales_channel</th>\n",
       "      <th>ship_service_level</th>\n",
       "      <th>style</th>\n",
       "      <th>sku</th>\n",
       "      <th>category</th>\n",
       "      <th>...</th>\n",
       "      <th>currency</th>\n",
       "      <th>amount</th>\n",
       "      <th>ship_city</th>\n",
       "      <th>ship_state</th>\n",
       "      <th>ship_postal_code</th>\n",
       "      <th>ship_country</th>\n",
       "      <th>promotion_ids</th>\n",
       "      <th>b2b</th>\n",
       "      <th>fulfilled_by</th>\n",
       "      <th>unnamed:_22</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>43725</td>\n",
       "      <td>402-7472994-8693158</td>\n",
       "      <td>2022-04-04</td>\n",
       "      <td>Shipped</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>Amazon.in</td>\n",
       "      <td>Expedited</td>\n",
       "      <td>SET271</td>\n",
       "      <td>SET271-KR-NP-XS</td>\n",
       "      <td>Set</td>\n",
       "      <td>...</td>\n",
       "      <td>INR</td>\n",
       "      <td>885.0</td>\n",
       "      <td>FERRARGUNJ</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR</td>\n",
       "      <td>744103.0</td>\n",
       "      <td>IN</td>\n",
       "      <td>IN Core Free Shipping 2015/04/08 23-48-5-108</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>37064</td>\n",
       "      <td>403-8616092-1924316</td>\n",
       "      <td>2022-04-08</td>\n",
       "      <td>Shipped - Delivered to Buyer</td>\n",
       "      <td>Merchant</td>\n",
       "      <td>Amazon.in</td>\n",
       "      <td>Standard</td>\n",
       "      <td>JNE3797</td>\n",
       "      <td>JNE3797-KR-L</td>\n",
       "      <td>Western Dress</td>\n",
       "      <td>...</td>\n",
       "      <td>INR</td>\n",
       "      <td>725.0</td>\n",
       "      <td>FERRARGUNJ</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR</td>\n",
       "      <td>744103.0</td>\n",
       "      <td>IN</td>\n",
       "      <td>Amazon PLCC Free-Financing Universal Merchant ...</td>\n",
       "      <td>False</td>\n",
       "      <td>Easy Ship</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   index             order_id       date                        status   \n",
       "0  43725  402-7472994-8693158 2022-04-04                       Shipped  \\\n",
       "1  37064  403-8616092-1924316 2022-04-08  Shipped - Delivered to Buyer   \n",
       "\n",
       "  fulfilment sales_channel ship_service_level    style              sku   \n",
       "0     Amazon     Amazon.in          Expedited   SET271  SET271-KR-NP-XS  \\\n",
       "1   Merchant     Amazon.in           Standard  JNE3797     JNE3797-KR-L   \n",
       "\n",
       "        category  ... currency amount   ship_city          ship_state   \n",
       "0            Set  ...      INR  885.0  FERRARGUNJ  ANDAMAN & NICOBAR   \\\n",
       "1  Western Dress  ...      INR  725.0  FERRARGUNJ  ANDAMAN & NICOBAR    \n",
       "\n",
       "  ship_postal_code  ship_country   \n",
       "0         744103.0            IN  \\\n",
       "1         744103.0            IN   \n",
       "\n",
       "                                       promotion_ids    b2b  fulfilled_by   \n",
       "0       IN Core Free Shipping 2015/04/08 23-48-5-108  False           NaN  \\\n",
       "1  Amazon PLCC Free-Financing Universal Merchant ...  False     Easy Ship   \n",
       "\n",
       "  unnamed:_22  \n",
       "0         NaN  \n",
       "1         NaN  \n",
       "\n",
       "[2 rows x 24 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#  HERE WE LOAD THE THE CSV FILE OF AMAZON IN  SALES DATA OF YEAR 20222   \n",
    "df=pd.read_csv('1aammazon.csv')\n",
    " \n",
    "# THIS COMMAND DROP ALL THE  ROWS WITH NULL VALES , \n",
    "df.dropna()\n",
    "\n",
    "# NOW I REMOVE THE UNECCESSARY SPACE AND CHARACTERS IN COLUMN NAME FOR EASY TO USE OR TYPE \n",
    "df.columns= df.columns.str.lower().str.strip().str.replace(' ','_').str.replace('-','_')\n",
    "\n",
    "# HERE WE  SET THE COLUMN DATE WITH DATE_TIME_INDEX IN PANDAS  FOR EASY TO ACCES DATE ,MONTH , YEAR ONLY  \n",
    "df.date=pd.to_datetime(df.date)\n",
    "\n",
    "# NOW WE CHECK DATA FERAME \n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5c8e97d",
   "metadata": {},
   "source": [
    "#  (Q1)=> Top ten state with max order count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "88896fda",
   "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>ship_state</th>\n",
       "      <th>no of oerder</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>MAHARASHTRA</td>\n",
       "      <td>10417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>KARNATAKA</td>\n",
       "      <td>8256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>TELANGANA</td>\n",
       "      <td>5449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>TAMIL NADU</td>\n",
       "      <td>5230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>UTTAR PRADESH</td>\n",
       "      <td>4864</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>DELHI</td>\n",
       "      <td>3322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>KERALA</td>\n",
       "      <td>2687</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>2634</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANDHRA PRADESH</td>\n",
       "      <td>2326</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>HARYANA</td>\n",
       "      <td>2069</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        ship_state  no of oerder\n",
       "26     MAHARASHTRA         10417\n",
       "21       KARNATAKA          8256\n",
       "44       TELANGANA          5449\n",
       "43      TAMIL NADU          5230\n",
       "46   UTTAR PRADESH          4864\n",
       "12           DELHI          3322\n",
       "22          KERALA          2687\n",
       "48     WEST BENGAL          2634\n",
       "1   ANDHRA PRADESH          2326\n",
       "17         HARYANA          2069"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('ship_state').order_id.count().reset_index(name='no of oerder')\\\n",
    ".sort_values(by='no of oerder',ascending=False).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d495c0ca",
   "metadata": {},
   "source": [
    "# (Q2)=> show total number of order count total amount with max . min amount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "b2daa2e6",
   "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>max_amount</th>\n",
       "      <th>min_amount</th>\n",
       "      <th>sum_amount</th>\n",
       "      <th>order_no</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>category</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Blouse</th>\n",
       "      <td>1266.66</td>\n",
       "      <td>0.0</td>\n",
       "      <td>173003.77</td>\n",
       "      <td>353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bottom</th>\n",
       "      <td>1028.58</td>\n",
       "      <td>0.0</td>\n",
       "      <td>48863.89</td>\n",
       "      <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dupatta</th>\n",
       "      <td>305.00</td>\n",
       "      <td>305.0</td>\n",
       "      <td>305.00</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ethnic Dress</th>\n",
       "      <td>1449.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>308224.80</td>\n",
       "      <td>443</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Saree</th>\n",
       "      <td>1248.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>35048.19</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Set</th>\n",
       "      <td>5495.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17975916.77</td>\n",
       "      <td>23197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Top</th>\n",
       "      <td>1797.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2361257.02</td>\n",
       "      <td>4712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Western Dress</th>\n",
       "      <td>2655.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4937370.08</td>\n",
       "      <td>6858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kurta</th>\n",
       "      <td>2796.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9759005.91</td>\n",
       "      <td>22920</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               max_amount  min_amount   sum_amount  order_no\n",
       "category                                                    \n",
       "Blouse            1266.66         0.0    173003.77       353\n",
       "Bottom            1028.58         0.0     48863.89       147\n",
       "Dupatta            305.00       305.0       305.00         1\n",
       "Ethnic Dress      1449.00         0.0    308224.80       443\n",
       "Saree             1248.00         0.0     35048.19        50\n",
       "Set               5495.00         0.0  17975916.77     23197\n",
       "Top               1797.00         0.0   2361257.02      4712\n",
       "Western Dress     2655.00         0.0   4937370.08      6858\n",
       "kurta             2796.00         0.0   9759005.91     22920"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('category').agg(max_amount=('amount','max'),\\\n",
    "min_amount=('amount','min'),sum_amount=('amount','sum'),order_no=('order_id','count'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d8a711d",
   "metadata": {},
   "source": [
    "# (Q3)=> FOR EVERY MONTH AND STATE SHOW TOP 2 MAX AMOUNT ORDER DETAIL WHERE  PRODUCT CATEGORY IS NOT \"SET\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "150cf538",
   "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>date</th>\n",
       "      <th>ship_state</th>\n",
       "      <th>datee</th>\n",
       "      <th>category</th>\n",
       "      <th>order_id</th>\n",
       "      <th>ship_city</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>April</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR</td>\n",
       "      <td>2022-04-03</td>\n",
       "      <td>Western Dress</td>\n",
       "      <td>171-0807078-4971518</td>\n",
       "      <td>PORT BLAIR</td>\n",
       "      <td>1033.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>April</td>\n",
       "      <td>ANDAMAN &amp; NICOBAR</td>\n",
       "      <td>2022-04-23</td>\n",
       "      <td>Western Dress</td>\n",
       "      <td>402-1396331-3893925</td>\n",
       "      <td>PORTBLAIR</td>\n",
       "      <td>856.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>April</td>\n",
       "      <td>ANDHRA PRADESH</td>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>Ethnic Dress</td>\n",
       "      <td>171-5793625-8052350</td>\n",
       "      <td>KAKINADA</td>\n",
       "      <td>1174.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>April</td>\n",
       "      <td>ANDHRA PRADESH</td>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>Western Dress</td>\n",
       "      <td>405-2258415-2444323</td>\n",
       "      <td>VIJAYAWADA</td>\n",
       "      <td>1099.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>April</td>\n",
       "      <td>ARUNACHAL PRADESH</td>\n",
       "      <td>2022-04-20</td>\n",
       "      <td>Western Dress</td>\n",
       "      <td>403-4321280-9677103</td>\n",
       "      <td>Itanagar</td>\n",
       "      <td>859.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>252</th>\n",
       "      <td>May</td>\n",
       "      <td>UTTARAKHAND</td>\n",
       "      <td>2022-05-30</td>\n",
       "      <td>Western Dress</td>\n",
       "      <td>171-1868964-1977150</td>\n",
       "      <td>DEHRADUN</td>\n",
       "      <td>885.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>May</td>\n",
       "      <td>UTTARAKHAND</td>\n",
       "      <td>2022-05-19</td>\n",
       "      <td>kurta</td>\n",
       "      <td>404-7580978-4249953</td>\n",
       "      <td>KASHIPUR</td>\n",
       "      <td>869.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>May</td>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>2022-05-20</td>\n",
       "      <td>kurta</td>\n",
       "      <td>406-3078152-5286722</td>\n",
       "      <td>HOWRAH</td>\n",
       "      <td>1099.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>May</td>\n",
       "      <td>WEST BENGAL</td>\n",
       "      <td>2022-05-25</td>\n",
       "      <td>kurta</td>\n",
       "      <td>406-6800199-7385123</td>\n",
       "      <td>KOLKATA</td>\n",
       "      <td>1099.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>May</td>\n",
       "      <td>punjab</td>\n",
       "      <td>2022-05-02</td>\n",
       "      <td>kurta</td>\n",
       "      <td>171-3535790-1113136</td>\n",
       "      <td>MOHALI</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>257 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      date          ship_state      datee       category             order_id   \n",
       "0    April  ANDAMAN & NICOBAR  2022-04-03  Western Dress  171-0807078-4971518  \\\n",
       "1    April  ANDAMAN & NICOBAR  2022-04-23  Western Dress  402-1396331-3893925   \n",
       "2    April      ANDHRA PRADESH 2022-04-01   Ethnic Dress  171-5793625-8052350   \n",
       "3    April      ANDHRA PRADESH 2022-04-01  Western Dress  405-2258415-2444323   \n",
       "4    April   ARUNACHAL PRADESH 2022-04-20  Western Dress  403-4321280-9677103   \n",
       "..     ...                 ...        ...            ...                  ...   \n",
       "252    May         UTTARAKHAND 2022-05-30  Western Dress  171-1868964-1977150   \n",
       "253    May         UTTARAKHAND 2022-05-19          kurta  404-7580978-4249953   \n",
       "254    May         WEST BENGAL 2022-05-20          kurta  406-3078152-5286722   \n",
       "255    May         WEST BENGAL 2022-05-25          kurta  406-6800199-7385123   \n",
       "256    May              punjab 2022-05-02          kurta  171-3535790-1113136   \n",
       "\n",
       "      ship_city   amount  \n",
       "0    PORT BLAIR  1033.00  \n",
       "1     PORTBLAIR   856.19  \n",
       "2      KAKINADA  1174.00  \n",
       "3    VIJAYAWADA  1099.00  \n",
       "4      Itanagar   859.00  \n",
       "..          ...      ...  \n",
       "252    DEHRADUN   885.00  \n",
       "253    KASHIPUR   869.00  \n",
       "254      HOWRAH  1099.00  \n",
       "255     KOLKATA  1099.00  \n",
       "256      MOHALI      NaN  \n",
       "\n",
       "[257 rows x 7 columns]"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfQ=df[df.category!='Set']\n",
    "dfQ.groupby([df.date.dt.month_name(),'ship_state'])\\\n",
    ".apply(lambda x: x.nlargest(2,columns=['amount']))\\\n",
    "[['date','category','order_id','ship_city','amount']].rename(columns={'date':'datee'},level=0)\\\n",
    ".reset_index(level=2,drop=True).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1f456ae",
   "metadata": {},
   "source": [
    "# (Q4)=> FOR EACH MONTH SHOW  TOP 2 MOST FAMOUS PRODUCT CATEGORY IN TERM {{AMOUNT}}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5b967d6d",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'df' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[2], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m \u001b[43mdf\u001b[49m\u001b[38;5;241m.\u001b[39mgroupby([df\u001b[38;5;241m.\u001b[39mdate\u001b[38;5;241m.\u001b[39mdt\u001b[38;5;241m.\u001b[39mto_period(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mM\u001b[39m\u001b[38;5;124m'\u001b[39m),\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mcategory\u001b[39m\u001b[38;5;124m'\u001b[39m])\u001b[38;5;241m.\u001b[39mapply(\u001b[38;5;28;01mlambda\u001b[39;00m x: x\u001b[38;5;241m.\u001b[39mamount\u001b[38;5;241m.\u001b[39msum())\\\n\u001b[0;32m      2\u001b[0m \u001b[38;5;241m.\u001b[39mreset_index(name\u001b[38;5;241m=\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mamount\u001b[39m\u001b[38;5;124m'\u001b[39m)\u001b[38;5;241m.\u001b[39mgroupby(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mdate\u001b[39m\u001b[38;5;124m'\u001b[39m)\u001b[38;5;241m.\u001b[39mapply(\u001b[38;5;28;01mlambda\u001b[39;00m x: x\u001b[38;5;241m.\u001b[39mnlargest(\u001b[38;5;241m3\u001b[39m,\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mamount\u001b[39m\u001b[38;5;124m'\u001b[39m))\\\n\u001b[0;32m      3\u001b[0m \u001b[38;5;241m.\u001b[39mreset_index(drop\u001b[38;5;241m=\u001b[39m\u001b[38;5;28;01mTrue\u001b[39;00m)\u001b[38;5;241m.\u001b[39mhead()\n",
      "\u001b[1;31mNameError\u001b[0m: name 'df' is not defined"
     ]
    }
   ],
   "source": [
    "df.groupby([df.date.dt.to_period('M'),'category']).apply(lambda x: x.amount.sum())\\\n",
    ".reset_index(name='amount').groupby('date').apply(lambda x: x.nlargest(3,'amount'))\\\n",
    ".reset_index(drop=True).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e31e681",
   "metadata": {},
   "source": [
    "# (Q5)=> FOR EACH STATE COUNT NUMBER OF ORDER "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "c10bc949",
   "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>ship_state</th>\n",
       "      <th>order_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>MAHARASHTRA</td>\n",
       "      <td>10417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>KARNATAKA</td>\n",
       "      <td>8256</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>TELANGANA</td>\n",
       "      <td>5449</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>TAMIL NADU</td>\n",
       "      <td>5230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>UTTAR PRADESH</td>\n",
       "      <td>4864</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ship_state  order_id\n",
       "26    MAHARASHTRA     10417\n",
       "21      KARNATAKA      8256\n",
       "44      TELANGANA      5449\n",
       "43     TAMIL NADU      5230\n",
       "46  UTTAR PRADESH      4864"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('ship_state').order_id.count().reset_index().sort_values('order_id',ascending=False).head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67d61852",
   "metadata": {},
   "source": [
    "# (Q6)=> FOR THIS  ['MAHARASHTRA','DELHI','ANDHRA PRADESH'] STATE  FIND GROWTH PERCENTAGE OF **ORDER COUNT**  FROM MONTH  APRIL TO MAY \n",
    "# USE OF PIVOT TABLE AND "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "28c62fbe",
   "metadata": {
    "scrolled": true
   },
   "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>ship_state</th>\n",
       "      <th>2022-April</th>\n",
       "      <th>2022-May</th>\n",
       "      <th>growth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANDHRA PRADESH</td>\n",
       "      <td>839</td>\n",
       "      <td>745</td>\n",
       "      <td>-11.2%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>DELHI</td>\n",
       "      <td>1310</td>\n",
       "      <td>1080</td>\n",
       "      <td>-17.56%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MAHARASHTRA</td>\n",
       "      <td>3982</td>\n",
       "      <td>3416</td>\n",
       "      <td>-14.21%</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ship_state  2022-April  2022-May   growth\n",
       "0  ANDHRA PRADESH         839       745   -11.2%\n",
       "1           DELHI        1310      1080  -17.56%\n",
       "2     MAHARASHTRA        3982      3416  -14.21%"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df.ship_state.isin(['MAHARASHTRA','DELHI','ANDHRA PRADESH']))&(df.date.dt.month.isin([4,5]))]\\\n",
    ".groupby([df.date.dt.strftime('%Y-%B'),'ship_state']).order_id.count().reset_index(name='order_in_maharash')\\\n",
    ".pivot_table(index='ship_state',values='order_in_maharash',columns='date').reset_index().rename_axis(columns=None)\\\n",
    ".assign(growth=lambda x: ((x['2022-May']-x['2022-April'])/x['2022-April']*100).round(2).astype(str)+'%')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c4481db",
   "metadata": {},
   "source": [
    "# (Q7)=> ANOMALY FIND\n",
    "# STATE WITH  MINIMUM ORDER BUT  MAX AMOUNT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "f925fc20",
   "metadata": {
    "scrolled": true
   },
   "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>order_count</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ship_state</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Punjab</th>\n",
       "      <td>3</td>\n",
       "      <td>2932.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LADAKH</th>\n",
       "      <td>20</td>\n",
       "      <td>18320.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chandigarh</th>\n",
       "      <td>4</td>\n",
       "      <td>3270.62</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            order_count  total_amount\n",
       "ship_state                           \n",
       "Punjab                3       2932.00\n",
       "LADAKH               20      18320.43\n",
       "Chandigarh            4       3270.62"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('ship_state').agg(order_count=('order_id','count'),total_amount=('amount','sum'))\\\n",
    ".assign(t= lambda x: x.total_amount/x.order_count).nlargest(3,'t').iloc[:,:-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87d1de82",
   "metadata": {},
   "source": [
    "# (Q8)=> ANOMALY FIND\n",
    "# STATE WITH  MINIMUM AMOUNT BUT  MAX ORDRER"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "12ad9f28",
   "metadata": {
    "scrolled": true
   },
   "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>order_count</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ship_state</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Arunachal pradesh</th>\n",
       "      <td>1</td>\n",
       "      <td>301.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Goa</th>\n",
       "      <td>6</td>\n",
       "      <td>1937.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manipur</th>\n",
       "      <td>1</td>\n",
       "      <td>339.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   order_count  total_amount\n",
       "ship_state                                  \n",
       "Arunachal pradesh            1         301.0\n",
       "Goa                          6        1937.0\n",
       "Manipur                      1         339.0"
      ]
     },
     "execution_count": 120,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('ship_state').agg(order_count=('order_id','count'),total_amount=('amount','sum'))\\\n",
    ".assign(t= lambda x: x.total_amount/x.order_count).nsmallest(3,'t').iloc[:,:-1]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "743ee7b8",
   "metadata": {},
   "source": [
    "# (Q9)=> CITY WITH MAX ORDRE COUNT WHERE FULFILMENT BY  AMAZON IN JULY MONTH"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "6cd48896",
   "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>ship_state</th>\n",
       "      <th>ship_city</th>\n",
       "      <th>order_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>434</th>\n",
       "      <td>KARNATAKA</td>\n",
       "      <td>BENGALURU</td>\n",
       "      <td>1269</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ship_state  ship_city  order_count\n",
       "434  KARNATAKA  BENGALURU         1269"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.fulfilment.isin(['Amazon'])&df.date.dt.month.isin([5])].groupby(['ship_state','ship_city'])\\\n",
    ".order_id.count().reset_index(name='order_count').nlargest(1,'order_count')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7d680ca",
   "metadata": {},
   "source": [
    "# (Q10)=> TOP 2 MONTH WITH HIGHEST AMOUNT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "46415858",
   "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>date</th>\n",
       "      <th>total_amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-Apr</td>\n",
       "      <td>13176537.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-May</td>\n",
       "      <td>11816880.93</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date  total_amount\n",
       "0  2022-Apr   13176537.61\n",
       "3  2022-May   11816880.93"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby(df.date.dt.strftime('%Y-%b')).amount.sum()\\\n",
    ".reset_index(name='total_amount').nlargest(2,'total_amount')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e9e353b5",
   "metadata": {},
   "source": [
    "# (Q11)=> USE OF   TABLE JOIN \n",
    "# STATE WHERE 50% OF PRODUCT IS BY MERCHANTE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "883f4e8f",
   "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>ship_state</th>\n",
       "      <th>total_order</th>\n",
       "      <th>total_order_from_amazon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ANDAMAN &amp; NICOBAR</td>\n",
       "      <td>116</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANDHRA PRADESH</td>\n",
       "      <td>2326</td>\n",
       "      <td>1597</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ARUNACHAL PRADESH</td>\n",
       "      <td>54</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ASSAM</td>\n",
       "      <td>709</td>\n",
       "      <td>462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arunachal Pradesh</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           ship_state  total_order  total_order_from_amazon\n",
       "0  ANDAMAN & NICOBAR           116                       73\n",
       "1      ANDHRA PRADESH         2326                     1597\n",
       "2   ARUNACHAL PRADESH           54                       33\n",
       "3               ASSAM          709                      462\n",
       "4   Arunachal Pradesh            1                        1"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 =df.groupby('ship_state').order_id.count().reset_index(name='total_order')\n",
    "df2=df[df.fulfilment=='Amazon'].groupby('ship_state').order_id.count().reset_index(name='total_order_from_amazon')\n",
    "df3=pd.merge(df1,df2,on='ship_state')\n",
    "df3[(df3.total_order/2)<df3.total_order_from_amazon].head()"
   ]
  }
 ],
 "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}