{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Extracting data from csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['id', 'age', 'gender', 'bmi', 'daily_steps', 'sleep_hours',\n",
      "       'water_intake_l', 'calories_consumed', 'smoker', 'alcohol',\n",
      "       'resting_hr', 'systolic_bp', 'diastolic_bp', 'cholesterol',\n",
      "       'family_history', 'disease_risk'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv('health_lifestyle_dataset.csv')\n",
    "print(df.columns)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We need firstly look at the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   id  age  gender   bmi  daily_steps  sleep_hours  water_intake_l  \\\n",
      "0   1   56    Male  20.5         4198          3.9             3.4   \n",
      "1   2   69  Female  33.3        14359          9.0             4.7   \n",
      "2   3   46    Male  31.6         1817          6.6             4.2   \n",
      "3   4   32  Female  38.2        15772          3.6             2.0   \n",
      "4   5   60  Female  33.6         6037          3.8             4.0   \n",
      "\n",
      "   calories_consumed  smoker  alcohol  resting_hr  systolic_bp  diastolic_bp  \\\n",
      "0               1602       0        0          97          161           111   \n",
      "1               2346       0        1          68          116            65   \n",
      "2               1643       0        1          90          123            99   \n",
      "3               2460       0        0          71          165            95   \n",
      "4               3756       0        1          98          139            61   \n",
      "\n",
      "   cholesterol  family_history  disease_risk  \n",
      "0          240               0             0  \n",
      "1          207               0             0  \n",
      "2          296               0             0  \n",
      "3          175               0             0  \n",
      "4          294               0             0  \n"
     ]
    }
   ],
   "source": [
    "print(df.head())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then we need to change the NULL values to the 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>id</th>\n",
       "      <th>age</th>\n",
       "      <th>gender</th>\n",
       "      <th>bmi</th>\n",
       "      <th>daily_steps</th>\n",
       "      <th>sleep_hours</th>\n",
       "      <th>water_intake_l</th>\n",
       "      <th>calories_consumed</th>\n",
       "      <th>smoker</th>\n",
       "      <th>alcohol</th>\n",
       "      <th>resting_hr</th>\n",
       "      <th>systolic_bp</th>\n",
       "      <th>diastolic_bp</th>\n",
       "      <th>cholesterol</th>\n",
       "      <th>family_history</th>\n",
       "      <th>disease_risk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>Male</td>\n",
       "      <td>20.5</td>\n",
       "      <td>4198</td>\n",
       "      <td>3.9</td>\n",
       "      <td>3.4</td>\n",
       "      <td>1602</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>97</td>\n",
       "      <td>161</td>\n",
       "      <td>111</td>\n",
       "      <td>240</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>69</td>\n",
       "      <td>Female</td>\n",
       "      <td>33.3</td>\n",
       "      <td>14359</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.7</td>\n",
       "      <td>2346</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>68</td>\n",
       "      <td>116</td>\n",
       "      <td>65</td>\n",
       "      <td>207</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>46</td>\n",
       "      <td>Male</td>\n",
       "      <td>31.6</td>\n",
       "      <td>1817</td>\n",
       "      <td>6.6</td>\n",
       "      <td>4.2</td>\n",
       "      <td>1643</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "      <td>123</td>\n",
       "      <td>99</td>\n",
       "      <td>296</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>32</td>\n",
       "      <td>Female</td>\n",
       "      <td>38.2</td>\n",
       "      <td>15772</td>\n",
       "      <td>3.6</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2460</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>71</td>\n",
       "      <td>165</td>\n",
       "      <td>95</td>\n",
       "      <td>175</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>60</td>\n",
       "      <td>Female</td>\n",
       "      <td>33.6</td>\n",
       "      <td>6037</td>\n",
       "      <td>3.8</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3756</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>98</td>\n",
       "      <td>139</td>\n",
       "      <td>61</td>\n",
       "      <td>294</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99995</th>\n",
       "      <td>99996</td>\n",
       "      <td>53</td>\n",
       "      <td>Male</td>\n",
       "      <td>33.1</td>\n",
       "      <td>4726</td>\n",
       "      <td>3.9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3118</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>105</td>\n",
       "      <td>76</td>\n",
       "      <td>282</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99996</th>\n",
       "      <td>99997</td>\n",
       "      <td>22</td>\n",
       "      <td>Male</td>\n",
       "      <td>35.1</td>\n",
       "      <td>11554</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1967</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>51</td>\n",
       "      <td>149</td>\n",
       "      <td>77</td>\n",
       "      <td>192</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99997</th>\n",
       "      <td>99998</td>\n",
       "      <td>37</td>\n",
       "      <td>Male</td>\n",
       "      <td>18.9</td>\n",
       "      <td>3924</td>\n",
       "      <td>3.8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2328</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>69</td>\n",
       "      <td>92</td>\n",
       "      <td>117</td>\n",
       "      <td>218</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99998</th>\n",
       "      <td>99999</td>\n",
       "      <td>72</td>\n",
       "      <td>Female</td>\n",
       "      <td>27.8</td>\n",
       "      <td>16110</td>\n",
       "      <td>5.6</td>\n",
       "      <td>0.8</td>\n",
       "      <td>3093</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>93</td>\n",
       "      <td>164</td>\n",
       "      <td>72</td>\n",
       "      <td>188</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99999</th>\n",
       "      <td>100000</td>\n",
       "      <td>37</td>\n",
       "      <td>Male</td>\n",
       "      <td>35.4</td>\n",
       "      <td>8222</td>\n",
       "      <td>9.1</td>\n",
       "      <td>1.8</td>\n",
       "      <td>3942</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>71</td>\n",
       "      <td>145</td>\n",
       "      <td>80</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100000 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           id  age  gender   bmi  daily_steps  sleep_hours  water_intake_l  \\\n",
       "0           1   56    Male  20.5         4198          3.9             3.4   \n",
       "1           2   69  Female  33.3        14359          9.0             4.7   \n",
       "2           3   46    Male  31.6         1817          6.6             4.2   \n",
       "3           4   32  Female  38.2        15772          3.6             2.0   \n",
       "4           5   60  Female  33.6         6037          3.8             4.0   \n",
       "...       ...  ...     ...   ...          ...          ...             ...   \n",
       "99995   99996   53    Male  33.1         4726          3.9             2.0   \n",
       "99996   99997   22    Male  35.1        11554          4.5             3.1   \n",
       "99997   99998   37    Male  18.9         3924          3.8             1.0   \n",
       "99998   99999   72  Female  27.8        16110          5.6             0.8   \n",
       "99999  100000   37    Male  35.4         8222          9.1             1.8   \n",
       "\n",
       "       calories_consumed  smoker  alcohol  resting_hr  systolic_bp  \\\n",
       "0                   1602       0        0          97          161   \n",
       "1                   2346       0        1          68          116   \n",
       "2                   1643       0        1          90          123   \n",
       "3                   2460       0        0          71          165   \n",
       "4                   3756       0        1          98          139   \n",
       "...                  ...     ...      ...         ...          ...   \n",
       "99995               3118       0        1          56          105   \n",
       "99996               1967       0        0          51          149   \n",
       "99997               2328       0        0          69           92   \n",
       "99998               3093       0        0          93          164   \n",
       "99999               3942       0        1          71          145   \n",
       "\n",
       "       diastolic_bp  cholesterol  family_history  disease_risk  \n",
       "0               111          240               0             0  \n",
       "1                65          207               0             0  \n",
       "2                99          296               0             0  \n",
       "3                95          175               0             0  \n",
       "4                61          294               0             0  \n",
       "...             ...          ...             ...           ...  \n",
       "99995            76          282               0             0  \n",
       "99996            77          192               0             0  \n",
       "99997           117          218               0             0  \n",
       "99998            72          188               0             0  \n",
       "99999            80          276               0             1  \n",
       "\n",
       "[100000 rows x 16 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.fillna(0)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then I remove the duplicate values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>id</th>\n",
       "      <th>age</th>\n",
       "      <th>gender</th>\n",
       "      <th>bmi</th>\n",
       "      <th>daily_steps</th>\n",
       "      <th>sleep_hours</th>\n",
       "      <th>water_intake_l</th>\n",
       "      <th>calories_consumed</th>\n",
       "      <th>smoker</th>\n",
       "      <th>alcohol</th>\n",
       "      <th>resting_hr</th>\n",
       "      <th>systolic_bp</th>\n",
       "      <th>diastolic_bp</th>\n",
       "      <th>cholesterol</th>\n",
       "      <th>family_history</th>\n",
       "      <th>disease_risk</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>Male</td>\n",
       "      <td>20.5</td>\n",
       "      <td>4198</td>\n",
       "      <td>3.9</td>\n",
       "      <td>3.4</td>\n",
       "      <td>1602</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>97</td>\n",
       "      <td>161</td>\n",
       "      <td>111</td>\n",
       "      <td>240</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>69</td>\n",
       "      <td>Female</td>\n",
       "      <td>33.3</td>\n",
       "      <td>14359</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.7</td>\n",
       "      <td>2346</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>68</td>\n",
       "      <td>116</td>\n",
       "      <td>65</td>\n",
       "      <td>207</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>46</td>\n",
       "      <td>Male</td>\n",
       "      <td>31.6</td>\n",
       "      <td>1817</td>\n",
       "      <td>6.6</td>\n",
       "      <td>4.2</td>\n",
       "      <td>1643</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>90</td>\n",
       "      <td>123</td>\n",
       "      <td>99</td>\n",
       "      <td>296</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>32</td>\n",
       "      <td>Female</td>\n",
       "      <td>38.2</td>\n",
       "      <td>15772</td>\n",
       "      <td>3.6</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2460</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>71</td>\n",
       "      <td>165</td>\n",
       "      <td>95</td>\n",
       "      <td>175</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>60</td>\n",
       "      <td>Female</td>\n",
       "      <td>33.6</td>\n",
       "      <td>6037</td>\n",
       "      <td>3.8</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3756</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>98</td>\n",
       "      <td>139</td>\n",
       "      <td>61</td>\n",
       "      <td>294</td>\n",
       "      <td>0</td>\n",
       "      <td>0</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99995</th>\n",
       "      <td>99996</td>\n",
       "      <td>53</td>\n",
       "      <td>Male</td>\n",
       "      <td>33.1</td>\n",
       "      <td>4726</td>\n",
       "      <td>3.9</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3118</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>105</td>\n",
       "      <td>76</td>\n",
       "      <td>282</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99996</th>\n",
       "      <td>99997</td>\n",
       "      <td>22</td>\n",
       "      <td>Male</td>\n",
       "      <td>35.1</td>\n",
       "      <td>11554</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1967</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>51</td>\n",
       "      <td>149</td>\n",
       "      <td>77</td>\n",
       "      <td>192</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99997</th>\n",
       "      <td>99998</td>\n",
       "      <td>37</td>\n",
       "      <td>Male</td>\n",
       "      <td>18.9</td>\n",
       "      <td>3924</td>\n",
       "      <td>3.8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2328</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>69</td>\n",
       "      <td>92</td>\n",
       "      <td>117</td>\n",
       "      <td>218</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99998</th>\n",
       "      <td>99999</td>\n",
       "      <td>72</td>\n",
       "      <td>Female</td>\n",
       "      <td>27.8</td>\n",
       "      <td>16110</td>\n",
       "      <td>5.6</td>\n",
       "      <td>0.8</td>\n",
       "      <td>3093</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>93</td>\n",
       "      <td>164</td>\n",
       "      <td>72</td>\n",
       "      <td>188</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99999</th>\n",
       "      <td>100000</td>\n",
       "      <td>37</td>\n",
       "      <td>Male</td>\n",
       "      <td>35.4</td>\n",
       "      <td>8222</td>\n",
       "      <td>9.1</td>\n",
       "      <td>1.8</td>\n",
       "      <td>3942</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>71</td>\n",
       "      <td>145</td>\n",
       "      <td>80</td>\n",
       "      <td>276</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100000 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           id  age  gender   bmi  daily_steps  sleep_hours  water_intake_l  \\\n",
       "0           1   56    Male  20.5         4198          3.9             3.4   \n",
       "1           2   69  Female  33.3        14359          9.0             4.7   \n",
       "2           3   46    Male  31.6         1817          6.6             4.2   \n",
       "3           4   32  Female  38.2        15772          3.6             2.0   \n",
       "4           5   60  Female  33.6         6037          3.8             4.0   \n",
       "...       ...  ...     ...   ...          ...          ...             ...   \n",
       "99995   99996   53    Male  33.1         4726          3.9             2.0   \n",
       "99996   99997   22    Male  35.1        11554          4.5             3.1   \n",
       "99997   99998   37    Male  18.9         3924          3.8             1.0   \n",
       "99998   99999   72  Female  27.8        16110          5.6             0.8   \n",
       "99999  100000   37    Male  35.4         8222          9.1             1.8   \n",
       "\n",
       "       calories_consumed  smoker  alcohol  resting_hr  systolic_bp  \\\n",
       "0                   1602       0        0          97          161   \n",
       "1                   2346       0        1          68          116   \n",
       "2                   1643       0        1          90          123   \n",
       "3                   2460       0        0          71          165   \n",
       "4                   3756       0        1          98          139   \n",
       "...                  ...     ...      ...         ...          ...   \n",
       "99995               3118       0        1          56          105   \n",
       "99996               1967       0        0          51          149   \n",
       "99997               2328       0        0          69           92   \n",
       "99998               3093       0        0          93          164   \n",
       "99999               3942       0        1          71          145   \n",
       "\n",
       "       diastolic_bp  cholesterol  family_history  disease_risk  \n",
       "0               111          240               0             0  \n",
       "1                65          207               0             0  \n",
       "2                99          296               0             0  \n",
       "3                95          175               0             0  \n",
       "4                61          294               0             0  \n",
       "...             ...          ...             ...           ...  \n",
       "99995            76          282               0             0  \n",
       "99996            77          192               0             0  \n",
       "99997           117          218               0             0  \n",
       "99998            72          188               0             0  \n",
       "99999            80          276               0             1  \n",
       "\n",
       "[100000 rows x 16 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.drop_duplicates()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Then just change the datatype of some non-numeric variables into the numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "num_cols = ['age','bmi','daily_steps','sleep_hours','water_intake_l',\n",
    "            'calories_consumed','resting_hr','systolic_bp','diastolic_bp','cholesterol']\n",
    "df[num_cols] = df[num_cols].apply(pd.to_numeric, errors='coerce')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After cleaning we need to create new values , so first is age_group where is 2: older and younger"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['age_group'] = df['age'].apply(lambda x: 'Older' if x > 45 else 'Younger')\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Walking activity level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['walking_stat'] = df['daily_steps'].apply(\n",
    "    lambda x: 'High' if x > 10000 else 'Medium' if 7500 < x <= 10000 else 'Low'\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Heart health indicator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['bp_category'] = df.apply(\n",
    "    lambda r: 'High_BP' if r['systolic_bp'] > 130 or r['diastolic_bp'] > 80 else 'Normal',\n",
    "    axis=1\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Remove the smokers and alcoholic people , we need only the ones who is not "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_df = df[(df['smoker'] == 0) & (df['alcohol'] == 0)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>id</th>\n",
       "      <th>age</th>\n",
       "      <th>gender</th>\n",
       "      <th>bmi</th>\n",
       "      <th>daily_steps</th>\n",
       "      <th>sleep_hours</th>\n",
       "      <th>water_intake_l</th>\n",
       "      <th>calories_consumed</th>\n",
       "      <th>smoker</th>\n",
       "      <th>alcohol</th>\n",
       "      <th>resting_hr</th>\n",
       "      <th>systolic_bp</th>\n",
       "      <th>diastolic_bp</th>\n",
       "      <th>cholesterol</th>\n",
       "      <th>family_history</th>\n",
       "      <th>disease_risk</th>\n",
       "      <th>age_group</th>\n",
       "      <th>walking_stat</th>\n",
       "      <th>bp_category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>Male</td>\n",
       "      <td>20.5</td>\n",
       "      <td>4198</td>\n",
       "      <td>3.9</td>\n",
       "      <td>3.4</td>\n",
       "      <td>1602</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>97</td>\n",
       "      <td>161</td>\n",
       "      <td>111</td>\n",
       "      <td>240</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Older</td>\n",
       "      <td>Low</td>\n",
       "      <td>High_BP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>32</td>\n",
       "      <td>Female</td>\n",
       "      <td>38.2</td>\n",
       "      <td>15772</td>\n",
       "      <td>3.6</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2460</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>71</td>\n",
       "      <td>165</td>\n",
       "      <td>95</td>\n",
       "      <td>175</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Younger</td>\n",
       "      <td>High</td>\n",
       "      <td>High_BP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>12</td>\n",
       "      <td>40</td>\n",
       "      <td>Female</td>\n",
       "      <td>23.9</td>\n",
       "      <td>10841</td>\n",
       "      <td>6.5</td>\n",
       "      <td>4.4</td>\n",
       "      <td>3858</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>99</td>\n",
       "      <td>158</td>\n",
       "      <td>79</td>\n",
       "      <td>234</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Younger</td>\n",
       "      <td>High</td>\n",
       "      <td>High_BP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>13</td>\n",
       "      <td>28</td>\n",
       "      <td>Male</td>\n",
       "      <td>24.7</td>\n",
       "      <td>10059</td>\n",
       "      <td>6.5</td>\n",
       "      <td>2.8</td>\n",
       "      <td>3281</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>87</td>\n",
       "      <td>129</td>\n",
       "      <td>88</td>\n",
       "      <td>224</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Younger</td>\n",
       "      <td>High</td>\n",
       "      <td>High_BP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>15</td>\n",
       "      <td>41</td>\n",
       "      <td>Male</td>\n",
       "      <td>31.4</td>\n",
       "      <td>15963</td>\n",
       "      <td>3.7</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3274</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>81</td>\n",
       "      <td>148</td>\n",
       "      <td>99</td>\n",
       "      <td>278</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Younger</td>\n",
       "      <td>High</td>\n",
       "      <td>High_BP</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99992</th>\n",
       "      <td>99993</td>\n",
       "      <td>44</td>\n",
       "      <td>Female</td>\n",
       "      <td>32.9</td>\n",
       "      <td>6644</td>\n",
       "      <td>7.2</td>\n",
       "      <td>3.9</td>\n",
       "      <td>3193</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>78</td>\n",
       "      <td>135</td>\n",
       "      <td>107</td>\n",
       "      <td>236</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Younger</td>\n",
       "      <td>Low</td>\n",
       "      <td>High_BP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99993</th>\n",
       "      <td>99994</td>\n",
       "      <td>23</td>\n",
       "      <td>Male</td>\n",
       "      <td>27.3</td>\n",
       "      <td>3586</td>\n",
       "      <td>6.3</td>\n",
       "      <td>3.8</td>\n",
       "      <td>2089</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>65</td>\n",
       "      <td>159</td>\n",
       "      <td>105</td>\n",
       "      <td>175</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Younger</td>\n",
       "      <td>Low</td>\n",
       "      <td>High_BP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99996</th>\n",
       "      <td>99997</td>\n",
       "      <td>22</td>\n",
       "      <td>Male</td>\n",
       "      <td>35.1</td>\n",
       "      <td>11554</td>\n",
       "      <td>4.5</td>\n",
       "      <td>3.1</td>\n",
       "      <td>1967</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>51</td>\n",
       "      <td>149</td>\n",
       "      <td>77</td>\n",
       "      <td>192</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Younger</td>\n",
       "      <td>High</td>\n",
       "      <td>High_BP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99997</th>\n",
       "      <td>99998</td>\n",
       "      <td>37</td>\n",
       "      <td>Male</td>\n",
       "      <td>18.9</td>\n",
       "      <td>3924</td>\n",
       "      <td>3.8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2328</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>69</td>\n",
       "      <td>92</td>\n",
       "      <td>117</td>\n",
       "      <td>218</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Younger</td>\n",
       "      <td>Low</td>\n",
       "      <td>High_BP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99998</th>\n",
       "      <td>99999</td>\n",
       "      <td>72</td>\n",
       "      <td>Female</td>\n",
       "      <td>27.8</td>\n",
       "      <td>16110</td>\n",
       "      <td>5.6</td>\n",
       "      <td>0.8</td>\n",
       "      <td>3093</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>93</td>\n",
       "      <td>164</td>\n",
       "      <td>72</td>\n",
       "      <td>188</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Older</td>\n",
       "      <td>High</td>\n",
       "      <td>High_BP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>55978 rows × 19 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id  age  gender   bmi  daily_steps  sleep_hours  water_intake_l  \\\n",
       "0          1   56    Male  20.5         4198          3.9             3.4   \n",
       "3          4   32  Female  38.2        15772          3.6             2.0   \n",
       "11        12   40  Female  23.9        10841          6.5             4.4   \n",
       "12        13   28    Male  24.7        10059          6.5             2.8   \n",
       "14        15   41    Male  31.4        15963          3.7             2.0   \n",
       "...      ...  ...     ...   ...          ...          ...             ...   \n",
       "99992  99993   44  Female  32.9         6644          7.2             3.9   \n",
       "99993  99994   23    Male  27.3         3586          6.3             3.8   \n",
       "99996  99997   22    Male  35.1        11554          4.5             3.1   \n",
       "99997  99998   37    Male  18.9         3924          3.8             1.0   \n",
       "99998  99999   72  Female  27.8        16110          5.6             0.8   \n",
       "\n",
       "       calories_consumed  smoker  alcohol  resting_hr  systolic_bp  \\\n",
       "0                   1602       0        0          97          161   \n",
       "3                   2460       0        0          71          165   \n",
       "11                  3858       0        0          99          158   \n",
       "12                  3281       0        0          87          129   \n",
       "14                  3274       0        0          81          148   \n",
       "...                  ...     ...      ...         ...          ...   \n",
       "99992               3193       0        0          78          135   \n",
       "99993               2089       0        0          65          159   \n",
       "99996               1967       0        0          51          149   \n",
       "99997               2328       0        0          69           92   \n",
       "99998               3093       0        0          93          164   \n",
       "\n",
       "       diastolic_bp  cholesterol  family_history  disease_risk age_group  \\\n",
       "0               111          240               0             0     Older   \n",
       "3                95          175               0             0   Younger   \n",
       "11               79          234               0             0   Younger   \n",
       "12               88          224               1             0   Younger   \n",
       "14               99          278               1             0   Younger   \n",
       "...             ...          ...             ...           ...       ...   \n",
       "99992           107          236               0             0   Younger   \n",
       "99993           105          175               0             0   Younger   \n",
       "99996            77          192               0             0   Younger   \n",
       "99997           117          218               0             0   Younger   \n",
       "99998            72          188               0             0     Older   \n",
       "\n",
       "      walking_stat bp_category  \n",
       "0              Low     High_BP  \n",
       "3             High     High_BP  \n",
       "11            High     High_BP  \n",
       "12            High     High_BP  \n",
       "14            High     High_BP  \n",
       "...            ...         ...  \n",
       "99992          Low     High_BP  \n",
       "99993          Low     High_BP  \n",
       "99996         High     High_BP  \n",
       "99997          Low     High_BP  \n",
       "99998         High     High_BP  \n",
       "\n",
       "[55978 rows x 19 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_df"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aggregation , "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "age_group\n",
      "Older      224.262089\n",
      "Younger    224.347202\n",
      "Name: cholesterol, dtype: float64\n",
      "walking_stat\n",
      "High      2.751903\n",
      "Low       2.744082\n",
      "Medium    2.769037\n",
      "Name: water_intake_l, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "avg_cholesterol = df.groupby('age_group')['cholesterol'].mean()\n",
    "print(avg_cholesterol)\n",
    "\n",
    "avg_water = df.groupby('walking_stat')['water_intake_l'].mean()\n",
    "print(avg_water)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Loading to database Postgresql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "978"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine(\"postgresql://postgres:Krasava10%26@localhost:5432/Life_style\")\n",
    "\n",
    "filtered_df.to_sql('clean_health_data', engine, index=False, if_exists='replace')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I download the sql in ipynb file to work with sql queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql postgresql://postgres:Krasava10%26@localhost:5432/Life_style\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So final 3 queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/Life_style\n",
      "10 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>id</th>\n",
       "            <th>age</th>\n",
       "            <th>gender</th>\n",
       "            <th>bmi</th>\n",
       "            <th>daily_steps</th>\n",
       "            <th>sleep_hours</th>\n",
       "            <th>water_intake_l</th>\n",
       "            <th>calories_consumed</th>\n",
       "            <th>resting_hr</th>\n",
       "            <th>systolic_bp</th>\n",
       "            <th>diastolic_bp</th>\n",
       "            <th>cholesterol</th>\n",
       "            <th>family_history</th>\n",
       "            <th>disease_risk</th>\n",
       "            <th>age_group</th>\n",
       "            <th>walking_stat</th>\n",
       "            <th>drinked_water</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>1</td>\n",
       "            <td>56</td>\n",
       "            <td>Male</td>\n",
       "            <td>20.5</td>\n",
       "            <td>4198</td>\n",
       "            <td>3.9</td>\n",
       "            <td>3.4</td>\n",
       "            <td>1602</td>\n",
       "            <td>97</td>\n",
       "            <td>161</td>\n",
       "            <td>111</td>\n",
       "            <td>240</td>\n",
       "            <td>0</td>\n",
       "            <td>0</td>\n",
       "            <td>Older</td>\n",
       "            <td>Low</td>\n",
       "            <td>94099.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>4</td>\n",
       "            <td>32</td>\n",
       "            <td>Female</td>\n",
       "            <td>38.2</td>\n",
       "            <td>15772</td>\n",
       "            <td>3.6</td>\n",
       "            <td>2.0</td>\n",
       "            <td>2460</td>\n",
       "            <td>71</td>\n",
       "            <td>165</td>\n",
       "            <td>95</td>\n",
       "            <td>175</td>\n",
       "            <td>0</td>\n",
       "            <td>0</td>\n",
       "            <td>Younger</td>\n",
       "            <td>High</td>\n",
       "            <td>144309.8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>12</td>\n",
       "            <td>40</td>\n",
       "            <td>Female</td>\n",
       "            <td>23.9</td>\n",
       "            <td>10841</td>\n",
       "            <td>6.5</td>\n",
       "            <td>4.4</td>\n",
       "            <td>3858</td>\n",
       "            <td>99</td>\n",
       "            <td>158</td>\n",
       "            <td>79</td>\n",
       "            <td>234</td>\n",
       "            <td>0</td>\n",
       "            <td>0</td>\n",
       "            <td>Younger</td>\n",
       "            <td>High</td>\n",
       "            <td>144309.8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>13</td>\n",
       "            <td>28</td>\n",
       "            <td>Male</td>\n",
       "            <td>24.7</td>\n",
       "            <td>10059</td>\n",
       "            <td>6.5</td>\n",
       "            <td>2.8</td>\n",
       "            <td>3281</td>\n",
       "            <td>87</td>\n",
       "            <td>129</td>\n",
       "            <td>88</td>\n",
       "            <td>224</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "            <td>Younger</td>\n",
       "            <td>High</td>\n",
       "            <td>144309.8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>15</td>\n",
       "            <td>41</td>\n",
       "            <td>Male</td>\n",
       "            <td>31.4</td>\n",
       "            <td>15963</td>\n",
       "            <td>3.7</td>\n",
       "            <td>2.0</td>\n",
       "            <td>3274</td>\n",
       "            <td>81</td>\n",
       "            <td>148</td>\n",
       "            <td>99</td>\n",
       "            <td>278</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "            <td>Younger</td>\n",
       "            <td>High</td>\n",
       "            <td>144309.8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>17</td>\n",
       "            <td>53</td>\n",
       "            <td>Male</td>\n",
       "            <td>39.3</td>\n",
       "            <td>14432</td>\n",
       "            <td>3.6</td>\n",
       "            <td>3.2</td>\n",
       "            <td>2349</td>\n",
       "            <td>67</td>\n",
       "            <td>130</td>\n",
       "            <td>63</td>\n",
       "            <td>233</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "            <td>Older</td>\n",
       "            <td>High</td>\n",
       "            <td>144309.8</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>18</td>\n",
       "            <td>57</td>\n",
       "            <td>Female</td>\n",
       "            <td>29.9</td>\n",
       "            <td>2046</td>\n",
       "            <td>9.7</td>\n",
       "            <td>4.4</td>\n",
       "            <td>1506</td>\n",
       "            <td>84</td>\n",
       "            <td>136</td>\n",
       "            <td>68</td>\n",
       "            <td>188</td>\n",
       "            <td>0</td>\n",
       "            <td>1</td>\n",
       "            <td>Older</td>\n",
       "            <td>Low</td>\n",
       "            <td>94099.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>19</td>\n",
       "            <td>41</td>\n",
       "            <td>Female</td>\n",
       "            <td>37.3</td>\n",
       "            <td>6529</td>\n",
       "            <td>8.7</td>\n",
       "            <td>3.1</td>\n",
       "            <td>1336</td>\n",
       "            <td>50</td>\n",
       "            <td>104</td>\n",
       "            <td>88</td>\n",
       "            <td>195</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "            <td>Younger</td>\n",
       "            <td>Low</td>\n",
       "            <td>94099.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>20</td>\n",
       "            <td>20</td>\n",
       "            <td>Male</td>\n",
       "            <td>35.7</td>\n",
       "            <td>1398</td>\n",
       "            <td>8.5</td>\n",
       "            <td>3.3</td>\n",
       "            <td>2161</td>\n",
       "            <td>57</td>\n",
       "            <td>167</td>\n",
       "            <td>100</td>\n",
       "            <td>272</td>\n",
       "            <td>1</td>\n",
       "            <td>0</td>\n",
       "            <td>Younger</td>\n",
       "            <td>Low</td>\n",
       "            <td>94099.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>23</td>\n",
       "            <td>19</td>\n",
       "            <td>Male</td>\n",
       "            <td>35.7</td>\n",
       "            <td>19840</td>\n",
       "            <td>9.3</td>\n",
       "            <td>3.6</td>\n",
       "            <td>3365</td>\n",
       "            <td>92</td>\n",
       "            <td>177</td>\n",
       "            <td>78</td>\n",
       "            <td>187</td>\n",
       "            <td>0</td>\n",
       "            <td>0</td>\n",
       "            <td>Younger</td>\n",
       "            <td>High</td>\n",
       "            <td>144309.8</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, 56, 'Male', 20.5, 4198, 3.9, 3.4, 1602, 97, 161, 111, 240, 0, 0, 'Older', 'Low', 94099.2),\n",
       " (4, 32, 'Female', 38.2, 15772, 3.6, 2.0, 2460, 71, 165, 95, 175, 0, 0, 'Younger', 'High', 144309.8),\n",
       " (12, 40, 'Female', 23.9, 10841, 6.5, 4.4, 3858, 99, 158, 79, 234, 0, 0, 'Younger', 'High', 144309.8),\n",
       " (13, 28, 'Male', 24.7, 10059, 6.5, 2.8, 3281, 87, 129, 88, 224, 1, 0, 'Younger', 'High', 144309.8),\n",
       " (15, 41, 'Male', 31.4, 15963, 3.7, 2.0, 3274, 81, 148, 99, 278, 1, 0, 'Younger', 'High', 144309.8),\n",
       " (17, 53, 'Male', 39.3, 14432, 3.6, 3.2, 2349, 67, 130, 63, 233, 1, 0, 'Older', 'High', 144309.8),\n",
       " (18, 57, 'Female', 29.9, 2046, 9.7, 4.4, 1506, 84, 136, 68, 188, 0, 1, 'Older', 'Low', 94099.2),\n",
       " (19, 41, 'Female', 37.3, 6529, 8.7, 3.1, 1336, 50, 104, 88, 195, 1, 0, 'Younger', 'Low', 94099.2),\n",
       " (20, 20, 'Male', 35.7, 1398, 8.5, 3.3, 2161, 57, 167, 100, 272, 1, 0, 'Younger', 'Low', 94099.2),\n",
       " (23, 19, 'Male', 35.7, 19840, 9.3, 3.6, 3365, 92, 177, 78, 187, 0, 0, 'Younger', 'High', 144309.8)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT * FROM cleaned_data\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/Life_style\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>count</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>4234</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(4234,)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql \n",
    "SELECT COUNT(disease_risk)\n",
    "FROM cleaned_data\n",
    "WHERE disease_risk = 1 and family_history = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/Life_style\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>sum</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>153739.70000000193</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(153739.70000000193,)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT SUM(water_intake_l)\n",
    "FROM cleaned_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/Life_style\n",
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>total_people</th>\n",
       "            <th>avg_age</th>\n",
       "            <th>avg_bmi</th>\n",
       "            <th>avg_cholesterol</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>55978</td>\n",
       "            <td>48.5</td>\n",
       "            <td>29.0</td>\n",
       "            <td>224.4</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(55978, Decimal('48.5'), Decimal('29.0'), Decimal('224.4'))]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    COUNT(*) AS total_people,\n",
    "    ROUND(AVG(age)::numeric, 1) AS avg_age,\n",
    "    ROUND(AVG(bmi)::numeric, 1) AS avg_bmi,\n",
    "    ROUND(AVG(cholesterol)::numeric, 1) AS avg_cholesterol\n",
    "FROM clean_health_data;\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/Life_style\n",
      "3 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>max</th>\n",
       "            <th>min</th>\n",
       "            <th>drinked_water</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>10000</td>\n",
       "            <td>1000</td>\n",
       "            <td>94099.2</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>9999</td>\n",
       "            <td>7501</td>\n",
       "            <td>36740.6</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>19999</td>\n",
       "            <td>10001</td>\n",
       "            <td>144309.8</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[(10000, 1000, 94099.2), (9999, 7501, 36740.6), (19999, 10001, 144309.8)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT MAX(daily_steps) , MIN(daily_steps) , drinked_water\n",
    "FROM cleaned_data\n",
    "GROUP BY drinked_water"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost:5432/Life_style\n",
      "2 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <thead>\n",
       "        <tr>\n",
       "            <th>age_group</th>\n",
       "            <th>avg_steps</th>\n",
       "            <th>avg_sleep</th>\n",
       "            <th>avg_water_intake</th>\n",
       "        </tr>\n",
       "    </thead>\n",
       "    <tbody>\n",
       "        <tr>\n",
       "            <td>Older</td>\n",
       "            <td>10494</td>\n",
       "            <td>6.5</td>\n",
       "            <td>2.7</td>\n",
       "        </tr>\n",
       "        <tr>\n",
       "            <td>Younger</td>\n",
       "            <td>10443</td>\n",
       "            <td>6.5</td>\n",
       "            <td>2.8</td>\n",
       "        </tr>\n",
       "    </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Older', Decimal('10494'), Decimal('6.5'), Decimal('2.7')),\n",
       " ('Younger', Decimal('10443'), Decimal('6.5'), Decimal('2.8'))]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "    age_group,\n",
    "    ROUND(AVG(daily_steps)::numeric, 0) AS avg_steps,\n",
    "    ROUND(AVG(sleep_hours)::numeric, 1) AS avg_sleep,\n",
    "    ROUND(AVG(water_intake_l)::numeric, 1) AS avg_water_intake\n",
    "FROM clean_health_data\n",
    "GROUP BY age_group\n",
    "ORDER BY avg_steps DESC;\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}