Data_manipulation_3 (python vs SQL).ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "30b2c810",
   "metadata": {},
   "outputs": [],
   "source": [
    "# This is project will be about comparing queries with python and SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b258ccc0",
   "metadata": {
    "collapsed": 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>#</th>\n",
       "      <th>Name</th>\n",
       "      <th>Type 1</th>\n",
       "      <th>Type 2</th>\n",
       "      <th>Total</th>\n",
       "      <th>HP</th>\n",
       "      <th>Attack</th>\n",
       "      <th>Defense</th>\n",
       "      <th>Sp. Atk</th>\n",
       "      <th>Sp. Def</th>\n",
       "      <th>Speed</th>\n",
       "      <th>Generation</th>\n",
       "      <th>Legendary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bulbasaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>318</td>\n",
       "      <td>45</td>\n",
       "      <td>49</td>\n",
       "      <td>49</td>\n",
       "      <td>65</td>\n",
       "      <td>65</td>\n",
       "      <td>45</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Ivysaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>405</td>\n",
       "      <td>60</td>\n",
       "      <td>62</td>\n",
       "      <td>63</td>\n",
       "      <td>80</td>\n",
       "      <td>80</td>\n",
       "      <td>60</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Venusaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>525</td>\n",
       "      <td>80</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>VenusaurMega Venusaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>625</td>\n",
       "      <td>80</td>\n",
       "      <td>100</td>\n",
       "      <td>123</td>\n",
       "      <td>122</td>\n",
       "      <td>120</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Charmander</td>\n",
       "      <td>Fire</td>\n",
       "      <td>NaN</td>\n",
       "      <td>309</td>\n",
       "      <td>39</td>\n",
       "      <td>52</td>\n",
       "      <td>43</td>\n",
       "      <td>60</td>\n",
       "      <td>50</td>\n",
       "      <td>65</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   #                   Name Type 1  Type 2  Total  HP  Attack  Defense  \\\n",
       "0  1              Bulbasaur  Grass  Poison    318  45      49       49   \n",
       "1  2                Ivysaur  Grass  Poison    405  60      62       63   \n",
       "2  3               Venusaur  Grass  Poison    525  80      82       83   \n",
       "3  3  VenusaurMega Venusaur  Grass  Poison    625  80     100      123   \n",
       "4  4             Charmander   Fire     NaN    309  39      52       43   \n",
       "\n",
       "   Sp. Atk  Sp. Def  Speed  Generation  Legendary  \n",
       "0       65       65     45           1      False  \n",
       "1       80       80     60           1      False  \n",
       "2      100      100     80           1      False  \n",
       "3      122      120     80           1      False  \n",
       "4       60       50     65           1      False  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "df= pd.read_csv('Pokemon.csv')\n",
    "df.head(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e3b286d0",
   "metadata": {
    "collapsed": 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>Name</th>\n",
       "      <th>Attack</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>MewtwoMega Mewtwo X</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>HeracrossMega Heracross</td>\n",
       "      <td>185</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>424</th>\n",
       "      <td>GroudonPrimal Groudon</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>426</th>\n",
       "      <td>RayquazaMega Rayquaza</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>429</th>\n",
       "      <td>DeoxysAttack Forme</td>\n",
       "      <td>180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>139</th>\n",
       "      <td>Magikarp</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>261</th>\n",
       "      <td>Blissey</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>Shuckle</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Chansey</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488</th>\n",
       "      <td>Happiny</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>800 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                        Name  Attack\n",
       "163      MewtwoMega Mewtwo X     190\n",
       "232  HeracrossMega Heracross     185\n",
       "424    GroudonPrimal Groudon     180\n",
       "426    RayquazaMega Rayquaza     180\n",
       "429       DeoxysAttack Forme     180\n",
       "..                       ...     ...\n",
       "139                 Magikarp      10\n",
       "261                  Blissey      10\n",
       "230                  Shuckle      10\n",
       "121                  Chansey       5\n",
       "488                  Happiny       5\n",
       "\n",
       "[800 rows x 2 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#now we have a df which belongs to pokemons, its features are like \n",
    "#Type1, Type2, Hp, Attack, Defense ...\n",
    "\n",
    "#Question 1\n",
    "#bring the 'name' and 'attack' values then order by desc\n",
    "\n",
    "#SQL \n",
    "# Select name, attack from df order by desc\n",
    "\n",
    "#Python \n",
    "df[['Name','Attack']].sort_values('Attack', ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "0d03be71",
   "metadata": {
    "collapsed": 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>Type 1</th>\n",
       "      <th>Type 2</th>\n",
       "      <th>HP</th>\n",
       "      <th>Attack</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Bug</td>\n",
       "      <td>Electric</td>\n",
       "      <td>60.000000</td>\n",
       "      <td>62.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bug</td>\n",
       "      <td>Fighting</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>155.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bug</td>\n",
       "      <td>Fire</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>72.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bug</td>\n",
       "      <td>Flying</td>\n",
       "      <td>63.000000</td>\n",
       "      <td>70.142857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bug</td>\n",
       "      <td>Ghost</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>90.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>Water</td>\n",
       "      <td>Ice</td>\n",
       "      <td>90.000000</td>\n",
       "      <td>83.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>Water</td>\n",
       "      <td>Poison</td>\n",
       "      <td>61.666667</td>\n",
       "      <td>68.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Water</td>\n",
       "      <td>Psychic</td>\n",
       "      <td>87.000000</td>\n",
       "      <td>73.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Water</td>\n",
       "      <td>Rock</td>\n",
       "      <td>70.750000</td>\n",
       "      <td>82.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>Water</td>\n",
       "      <td>Steel</td>\n",
       "      <td>84.000000</td>\n",
       "      <td>86.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>136 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Type 1    Type 2         HP      Attack\n",
       "0      Bug  Electric  60.000000   62.000000\n",
       "1      Bug  Fighting  80.000000  155.000000\n",
       "2      Bug      Fire  70.000000   72.500000\n",
       "3      Bug    Flying  63.000000   70.142857\n",
       "4      Bug     Ghost   1.000000   90.000000\n",
       "..     ...       ...        ...         ...\n",
       "131  Water       Ice  90.000000   83.333333\n",
       "132  Water    Poison  61.666667   68.333333\n",
       "133  Water   Psychic  87.000000   73.000000\n",
       "134  Water      Rock  70.750000   82.750000\n",
       "135  Water     Steel  84.000000   86.000000\n",
       "\n",
       "[136 rows x 4 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#question 2 \n",
    "#get the average number of HP and Attack for each Type1 and Type2\n",
    "\n",
    "# Sql \n",
    "# select city, property, avg(bedrooms), avg(bathrooms) from property_datasets\n",
    "# group by city, property\n",
    "\n",
    "#Python\n",
    "import pandas as pd\n",
    "df= pd.read_csv('Pokemon.csv')\n",
    "df.head(5)\n",
    "\n",
    "#python code is like\n",
    "df.groupby(['Type 1','Type 2'])[['HP','Attack']].mean().reset_index()\n",
    "df.groupby(['Type 1','Type 2']).mean()[['HP','Attack']].reset_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "cacb67e2",
   "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>#</th>\n",
       "      <th>Name</th>\n",
       "      <th>Type 1</th>\n",
       "      <th>Type 2</th>\n",
       "      <th>Total</th>\n",
       "      <th>HP</th>\n",
       "      <th>Attack</th>\n",
       "      <th>Defense</th>\n",
       "      <th>Sp. Atk</th>\n",
       "      <th>Sp. Def</th>\n",
       "      <th>Speed</th>\n",
       "      <th>Generation</th>\n",
       "      <th>Legendary</th>\n",
       "      <th>Power_status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bulbasaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>318</td>\n",
       "      <td>45</td>\n",
       "      <td>49</td>\n",
       "      <td>49</td>\n",
       "      <td>65</td>\n",
       "      <td>65</td>\n",
       "      <td>45</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>weak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Ivysaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>405</td>\n",
       "      <td>60</td>\n",
       "      <td>62</td>\n",
       "      <td>63</td>\n",
       "      <td>80</td>\n",
       "      <td>80</td>\n",
       "      <td>60</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>weak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Venusaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>525</td>\n",
       "      <td>80</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>weak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>VenusaurMega Venusaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>625</td>\n",
       "      <td>80</td>\n",
       "      <td>100</td>\n",
       "      <td>123</td>\n",
       "      <td>122</td>\n",
       "      <td>120</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>Strong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Charmander</td>\n",
       "      <td>Fire</td>\n",
       "      <td>NaN</td>\n",
       "      <td>309</td>\n",
       "      <td>39</td>\n",
       "      <td>52</td>\n",
       "      <td>43</td>\n",
       "      <td>60</td>\n",
       "      <td>50</td>\n",
       "      <td>65</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>weak</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",
       "      <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>795</th>\n",
       "      <td>719</td>\n",
       "      <td>Diancie</td>\n",
       "      <td>Rock</td>\n",
       "      <td>Fairy</td>\n",
       "      <td>600</td>\n",
       "      <td>50</td>\n",
       "      <td>100</td>\n",
       "      <td>150</td>\n",
       "      <td>100</td>\n",
       "      <td>150</td>\n",
       "      <td>50</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "      <td>Strong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796</th>\n",
       "      <td>719</td>\n",
       "      <td>DiancieMega Diancie</td>\n",
       "      <td>Rock</td>\n",
       "      <td>Fairy</td>\n",
       "      <td>700</td>\n",
       "      <td>50</td>\n",
       "      <td>160</td>\n",
       "      <td>110</td>\n",
       "      <td>160</td>\n",
       "      <td>110</td>\n",
       "      <td>110</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "      <td>Strong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>797</th>\n",
       "      <td>720</td>\n",
       "      <td>HoopaHoopa Confined</td>\n",
       "      <td>Psychic</td>\n",
       "      <td>Ghost</td>\n",
       "      <td>600</td>\n",
       "      <td>80</td>\n",
       "      <td>110</td>\n",
       "      <td>60</td>\n",
       "      <td>150</td>\n",
       "      <td>130</td>\n",
       "      <td>70</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "      <td>Strong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>798</th>\n",
       "      <td>720</td>\n",
       "      <td>HoopaHoopa Unbound</td>\n",
       "      <td>Psychic</td>\n",
       "      <td>Dark</td>\n",
       "      <td>680</td>\n",
       "      <td>80</td>\n",
       "      <td>160</td>\n",
       "      <td>60</td>\n",
       "      <td>170</td>\n",
       "      <td>130</td>\n",
       "      <td>80</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "      <td>Strong</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>799</th>\n",
       "      <td>721</td>\n",
       "      <td>Volcanion</td>\n",
       "      <td>Fire</td>\n",
       "      <td>Water</td>\n",
       "      <td>600</td>\n",
       "      <td>80</td>\n",
       "      <td>110</td>\n",
       "      <td>120</td>\n",
       "      <td>130</td>\n",
       "      <td>90</td>\n",
       "      <td>70</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "      <td>Strong</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>800 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       #                   Name   Type 1  Type 2  Total  HP  Attack  Defense  \\\n",
       "0      1              Bulbasaur    Grass  Poison    318  45      49       49   \n",
       "1      2                Ivysaur    Grass  Poison    405  60      62       63   \n",
       "2      3               Venusaur    Grass  Poison    525  80      82       83   \n",
       "3      3  VenusaurMega Venusaur    Grass  Poison    625  80     100      123   \n",
       "4      4             Charmander     Fire     NaN    309  39      52       43   \n",
       "..   ...                    ...      ...     ...    ...  ..     ...      ...   \n",
       "795  719                Diancie     Rock   Fairy    600  50     100      150   \n",
       "796  719    DiancieMega Diancie     Rock   Fairy    700  50     160      110   \n",
       "797  720    HoopaHoopa Confined  Psychic   Ghost    600  80     110       60   \n",
       "798  720     HoopaHoopa Unbound  Psychic    Dark    680  80     160       60   \n",
       "799  721              Volcanion     Fire   Water    600  80     110      120   \n",
       "\n",
       "     Sp. Atk  Sp. Def  Speed  Generation  Legendary Power_status  \n",
       "0         65       65     45           1      False         weak  \n",
       "1         80       80     60           1      False         weak  \n",
       "2        100      100     80           1      False         weak  \n",
       "3        122      120     80           1      False       Strong  \n",
       "4         60       50     65           1      False         weak  \n",
       "..       ...      ...    ...         ...        ...          ...  \n",
       "795      100      150     50           6       True       Strong  \n",
       "796      160      110    110           6       True       Strong  \n",
       "797      150      130     70           6       True       Strong  \n",
       "798      170      130     80           6       True       Strong  \n",
       "799      130       90     70           6       True       Strong  \n",
       "\n",
       "[800 rows x 14 columns]"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#question 3\n",
    "#Create a column and specify 'stgong','weak' by looking 'Attack' (threshold 100)\n",
    "\n",
    "#SQL\n",
    "# Select name, Attack,  \n",
    "# Case\n",
    "#     When Attack > 80 Then 'Powerful'\n",
    "#     When Attack > 50 Then 'Strong'\n",
    "#     Else 'Weak'\n",
    "# end as Power_status\n",
    "# From Pokemon\n",
    "\n",
    "#Python\n",
    "# df.drop(columns = ['Power_status'],inplace = True)\n",
    "for i in df['Attack']:\n",
    "    if i > 100 :\n",
    "        df.loc [ df['Attack'] >= 100, ['Power_status']] = ['Strong']\n",
    "    else :\n",
    "        df.loc [ df['Attack'] < 100, ['Power_status']] = ['weak']\n",
    "df\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de8ebe49",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}