Untitled.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "819ce7bf",
   "metadata": {},
   "source": [
    "# Brain Stroke Analysis with SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ae5e7e9",
   "metadata": {},
   "source": [
    "# About Dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0b9f6a2",
   "metadata": {},
   "source": [
    "A stroke is a medical condition in which poor blood flow to the brain causes cell death. There are two main types of stroke: ischemic, due to lack of blood flow, and hemorrhagic, due to bleeding. Both cause parts of the brain to stop functioning properly. Signs and symptoms of a stroke may include an inability to move or feel on one side of the body, problems understanding or speaking, dizziness, or loss of vision to one side. Signs and symptoms often appear soon after the stroke has occurred. If symptoms last less than one or two hours, the stroke is a transient ischemic attack (TIA), also called a mini-stroke. A hemorrhagic stroke may also be associated with a severe headache. The symptoms of a stroke can be permanent. Long-term complications may include pneumonia and loss of bladder control.\n",
    "\n",
    "The main risk factor for stroke is high blood pressure. Other risk factors include high blood cholesterol, tobacco smoking, obesity, diabetes mellitus, a previous TIA, end-stage kidney disease, and atrial fibrillation. An ischemic stroke is typically caused by blockage of a blood vessel, though there are also less common causes. A hemorrhagic stroke is caused by either bleeding directly into the brain or into the space between the brain's membranes. Bleeding may occur due to a ruptured brain aneurysm. Diagnosis is typically based on a physical exam and is supported by medical imaging such as a CT scan or MRI scan. A CT scan can rule out bleeding, but may not necessarily rule out ischemia, which early on typically does not show up on a CT scan. Other tests such as an electrocardiogram (ECG) and blood tests are done to determine risk factors and rule out other possible causes. Low blood sugar may cause similar symptoms.\n",
    "\n",
    "Prevention includes decreasing risk factors, surgery to open up the arteries to the brain in those with problematic carotid narrowing, and warfarin in people with atrial fibrillation. Aspirin or statins may be recommended by physicians for prevention. A stroke or TIA often requires emergency care. An ischemic stroke, if detected within three to four and half hours, may be treatable with a medication that can break down the clot. Some hemorrhagic strokes benefit from surgery. Treatment to attempt recovery of lost function is called stroke rehabilitation, and ideally takes place in a stroke unit; however, these are not available in much of the world.\n",
    "\n",
    "Attribute Information\n",
    "1) gender: \"Male\", \"Female\" or \"Other\"\n",
    "2) age: age of the patient\n",
    "3) hypertension: 0 if the patient doesn't have hypertension, 1 if the patient has hypertension\n",
    "4) heart disease: 0 if the patient doesn't have any heart diseases, 1 if the patient has a heart disease \n",
    "5) ever-married: \"No\" or \"Yes\"\n",
    "6) worktype: \"children\", \"Govtjov\", \"Neverworked\", \"Private\" or \"Self-employed\" 7) Residencetype: \"Rural\" or \"Urban\"\n",
    "8) avgglucoselevel: average glucose level in blood\n",
    "9) bmi: body mass index\n",
    "10) smoking_status: \"formerly smoked\", \"never smoked\", \"smokes\" or \"Unknown\"*\n",
    "11) stroke: 1 if the patient had a stroke or 0 if not"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac87fb5c",
   "metadata": {},
   "source": [
    "IMPORTING LIBRARIES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "35b4d5aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3 as sq"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce04738f",
   "metadata": {},
   "source": [
    "CREATING THE DATABASE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "3c803fb8",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sq.connect('brain_stroke.db')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d1eaa21",
   "metadata": {},
   "source": [
    "CREATING A TABLE CALLED BRAIN STROKE INSIDE THE DATABASE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "5f8ef022",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"\"\"\n",
    "CREATE TABLE BRAIN_STROKE(\n",
    "    gender VARCHAR(10),\n",
    "    age DECIMAL (2,2),\n",
    "    hypertension SMALLINT,\n",
    "    heart_disease SMALLINT,\n",
    "    ever_married VARCHAR (5),\n",
    "    work_type VARCHAR (20),\n",
    "    Residence_type VARCHAR (10),\n",
    "    avg_glucose_level DECIMAL (5,3),\n",
    "    bmi DECIMAL (4,3),\n",
    "    smoking_status VARCHAR (20),\n",
    "    stroke SMALLINT\n",
    "    )\n",
    "\"\"\"\n",
    "with conn:\n",
    "    cur = conn.cursor()\n",
    "    cur.execute('DROP TABLE IF EXISTS BRAIN_STROKE')\n",
    "    cur.execute(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dea8c078",
   "metadata": {},
   "source": [
    "CHECKING OUR SQLITE MASTER FOR CONFIRMATION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "8b2a7ce6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('BRAIN_STROKE',)]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT name FROM sqlite_master WHERE type = 'table'\"\n",
    "cur.execute(query)\n",
    "cur.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aff11cff",
   "metadata": {},
   "source": [
    "LOADING OUR CSV FILE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "38381a9b",
   "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>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>hypertension</th>\n",
       "      <th>heart_disease</th>\n",
       "      <th>ever_married</th>\n",
       "      <th>work_type</th>\n",
       "      <th>Residence_type</th>\n",
       "      <th>avg_glucose_level</th>\n",
       "      <th>bmi</th>\n",
       "      <th>smoking_status</th>\n",
       "      <th>stroke</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Male</td>\n",
       "      <td>67.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Private</td>\n",
       "      <td>Urban</td>\n",
       "      <td>228.69</td>\n",
       "      <td>36.6</td>\n",
       "      <td>formerly smoked</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Male</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Private</td>\n",
       "      <td>Rural</td>\n",
       "      <td>105.92</td>\n",
       "      <td>32.5</td>\n",
       "      <td>never smoked</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Female</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Private</td>\n",
       "      <td>Urban</td>\n",
       "      <td>171.23</td>\n",
       "      <td>34.4</td>\n",
       "      <td>smokes</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Female</td>\n",
       "      <td>79.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Self-employed</td>\n",
       "      <td>Rural</td>\n",
       "      <td>174.12</td>\n",
       "      <td>24.0</td>\n",
       "      <td>never smoked</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Male</td>\n",
       "      <td>81.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Private</td>\n",
       "      <td>Urban</td>\n",
       "      <td>186.21</td>\n",
       "      <td>29.0</td>\n",
       "      <td>formerly smoked</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender   age  hypertension  heart_disease ever_married      work_type  \\\n",
       "0    Male  67.0             0              1          Yes        Private   \n",
       "1    Male  80.0             0              1          Yes        Private   \n",
       "2  Female  49.0             0              0          Yes        Private   \n",
       "3  Female  79.0             1              0          Yes  Self-employed   \n",
       "4    Male  81.0             0              0          Yes        Private   \n",
       "\n",
       "  Residence_type  avg_glucose_level   bmi   smoking_status  stroke  \n",
       "0          Urban             228.69  36.6  formerly smoked       1  \n",
       "1          Rural             105.92  32.5     never smoked       1  \n",
       "2          Urban             171.23  34.4           smokes       1  \n",
       "3          Rural             174.12  24.0     never smoked       1  \n",
       "4          Urban             186.21  29.0  formerly smoked       1  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brain_stroke_df = pd.read_csv('C:/Users/CHINELO/Downloads/brain_stroke.csv')\n",
    "brain_stroke_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebb26d58",
   "metadata": {},
   "source": [
    "CONVERTING CSV FILE INTO TUPLE BEFORE LOADING INTO DATABASE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "2e820d52",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('Male',\n",
       " 67.0,\n",
       " 0,\n",
       " 1,\n",
       " 'Yes',\n",
       " 'Private',\n",
       " 'Urban',\n",
       " 228.69,\n",
       " 36.6,\n",
       " 'formerly smoked',\n",
       " 1)"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "csv_tuple = tuple(brain_stroke_df.itertuples(index=False, name=None))\n",
    "csv_tuple[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bb0fb33",
   "metadata": {},
   "source": [
    "INSERTING VALUES INTO DATABASE TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "c81a9941",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x1ea464ff420>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"INSERT INTO BRAIN_STROKE VALUES (?,?,?,?,?,?,?,?,?,?,?)\"\n",
    "cur.executemany(query, csv_tuple)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "29c48960",
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"SELECT * FROM BRAIN_STROKE LIMIT 5\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "f7147e97",
   "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>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>hypertension</th>\n",
       "      <th>heart_disease</th>\n",
       "      <th>ever_married</th>\n",
       "      <th>work_type</th>\n",
       "      <th>Residence_type</th>\n",
       "      <th>avg_glucose_level</th>\n",
       "      <th>bmi</th>\n",
       "      <th>smoking_status</th>\n",
       "      <th>stroke</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Male</td>\n",
       "      <td>67</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Private</td>\n",
       "      <td>Urban</td>\n",
       "      <td>228.69</td>\n",
       "      <td>36.6</td>\n",
       "      <td>formerly smoked</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Male</td>\n",
       "      <td>80</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Private</td>\n",
       "      <td>Rural</td>\n",
       "      <td>105.92</td>\n",
       "      <td>32.5</td>\n",
       "      <td>never smoked</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Female</td>\n",
       "      <td>49</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Private</td>\n",
       "      <td>Urban</td>\n",
       "      <td>171.23</td>\n",
       "      <td>34.4</td>\n",
       "      <td>smokes</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Female</td>\n",
       "      <td>79</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Self-employed</td>\n",
       "      <td>Rural</td>\n",
       "      <td>174.12</td>\n",
       "      <td>24.0</td>\n",
       "      <td>never smoked</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Male</td>\n",
       "      <td>81</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Private</td>\n",
       "      <td>Urban</td>\n",
       "      <td>186.21</td>\n",
       "      <td>29.0</td>\n",
       "      <td>formerly smoked</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender  age  hypertension  heart_disease ever_married      work_type  \\\n",
       "0    Male   67             0              1          Yes        Private   \n",
       "1    Male   80             0              1          Yes        Private   \n",
       "2  Female   49             0              0          Yes        Private   \n",
       "3  Female   79             1              0          Yes  Self-employed   \n",
       "4    Male   81             0              0          Yes        Private   \n",
       "\n",
       "  Residence_type  avg_glucose_level   bmi   smoking_status  stroke  \n",
       "0          Urban             228.69  36.6  formerly smoked       1  \n",
       "1          Rural             105.92  32.5     never smoked       1  \n",
       "2          Urban             171.23  34.4           smokes       1  \n",
       "3          Rural             174.12  24.0     never smoked       1  \n",
       "4          Urban             186.21  29.0  formerly smoked       1  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88728997",
   "metadata": {},
   "source": [
    "Questions\n",
    "1.  What is the distribution of gender in the dataset?\n",
    "2.  What is the distribution of people with stroke in the dataset?\n",
    "3.  What is the distribution of brain stroke by gender?\n",
    "4.  For women, how many percent has stroke?\n",
    "5.  For men, how many percent has no stroke?\n",
    "6.  Details of the oldest people with stroke\n",
    "7.  How many people are married and has stroke?\n",
    "8.  How many people are not married and has stroke?\n",
    "9.  What is the minimum, average and maximum age in the data?\n",
    "10. Details of people in the dataset below 10 years\n",
    "11. Number of children with either hypertension or stroke\n",
    "12. What's the distibution of hypertension in the dataset?\n",
    "13. How many male, and female has hypertension?\n",
    "14. For each smoking status show how many people have hypertension and how many do not?\n",
    "15. How many male, and female has both hypertension and heart disease?\n",
    "16. Which residence has the lowest number of people with no heart disease?\n",
    "17. How many percent of people with private work type has no hypertension?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fcc03151",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "3fe612df",
   "metadata": {},
   "source": [
    "1. What is the distribution of gender in the dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "1de87319",
   "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>gender</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Female</td>\n",
       "      <td>2907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Male</td>\n",
       "      <td>2074</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender  count\n",
       "0  Female   2907\n",
       "1    Male   2074"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT    gender, COUNT(*) count\n",
    "FROM      BRAIN_STROKE\n",
    "GROUP BY  gender\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc0f65dd",
   "metadata": {},
   "source": [
    "2. What is the distribution of people with stroke in the dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "2d4cf8e0",
   "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>stroke</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>4733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>248</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   stroke  count\n",
       "0       0   4733\n",
       "1       1    248"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT    stroke, COUNT(*) count\n",
    "FROM      BRAIN_STROKE\n",
    "GROUP BY  stroke\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "461d6379",
   "metadata": {},
   "source": [
    "3. What is the distribution of brain stroke by gender?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "7040352f",
   "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>gender</th>\n",
       "      <th>stroke</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Female</td>\n",
       "      <td>0</td>\n",
       "      <td>2767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Female</td>\n",
       "      <td>1</td>\n",
       "      <td>140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Male</td>\n",
       "      <td>0</td>\n",
       "      <td>1966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Male</td>\n",
       "      <td>1</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender  stroke  count\n",
       "0  Female       0   2767\n",
       "1  Female       1    140\n",
       "2    Male       0   1966\n",
       "3    Male       1    108"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT    gender, stroke, COUNT(*) count\n",
    "FROM      BRAIN_STROKE\n",
    "GROUP BY  gender, stroke\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "214ee341",
   "metadata": {},
   "source": [
    "4. For women, how many percent has stroke?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "5e704991",
   "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>gender</th>\n",
       "      <th>stroke</th>\n",
       "      <th>Percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Female</td>\n",
       "      <td>0</td>\n",
       "      <td>95.184039</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Female</td>\n",
       "      <td>1</td>\n",
       "      <td>4.815961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Male</td>\n",
       "      <td>0</td>\n",
       "      <td>94.792671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Male</td>\n",
       "      <td>1</td>\n",
       "      <td>5.207329</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender  stroke    Percent\n",
       "0  Female       0  95.184039\n",
       "1  Female       1   4.815961\n",
       "2    Male       0  94.792671\n",
       "3    Male       1   5.207329"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT      gender, stroke, CAST(COUNT(*) AS FLOAT)/total.count * 100 AS Percent\n",
    "FROM        BRAIN_STROKE, (SELECT COUNT(*) count FROM BRAIN_STROKE WHERE gender = 'Female') total\n",
    "WHERE       gender = 'Female'\n",
    "GROUP BY    stroke\n",
    "\n",
    "UNION ALL\n",
    "\n",
    "SELECT      gender, stroke, CAST(COUNT(*) AS FLOAT)/total.count * 100 AS Percent\n",
    "FROM        BRAIN_STROKE, (SELECT COUNT(*) count FROM BRAIN_STROKE WHERE gender = 'Male') total\n",
    "WHERE       gender = 'Male'\n",
    "GROUP BY    stroke\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5dec9e5",
   "metadata": {},
   "source": [
    "6. Details of the oldest people with stroke based on work type"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "30f74272",
   "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>gender</th>\n",
       "      <th>work_type</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Female</td>\n",
       "      <td>Govt_job</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Female</td>\n",
       "      <td>Private</td>\n",
       "      <td>193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Female</td>\n",
       "      <td>Self-employed</td>\n",
       "      <td>175</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Male</td>\n",
       "      <td>Govt_job</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Male</td>\n",
       "      <td>Private</td>\n",
       "      <td>143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Male</td>\n",
       "      <td>Self-employed</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender      work_type  count\n",
       "0  Female       Govt_job     52\n",
       "1  Female        Private    193\n",
       "2  Female  Self-employed    175\n",
       "3    Male       Govt_job     31\n",
       "4    Male        Private    143\n",
       "5    Male  Self-employed    113"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "WITH older_people AS (\n",
    "    SELECT  *\n",
    "    FROM    BRAIN_STROKE\n",
    "    WHERE   age > 70\n",
    ")\n",
    "\n",
    "SELECT  gender, work_type, COUNT(*) count \n",
    "FROM    older_people\n",
    "GROUP BY    gender, work_type\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d98e095",
   "metadata": {},
   "source": [
    "7. How many people are married and has stroke?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "1c74b14b",
   "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>Married_and_has_stroke</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Married_and_has_stroke\n",
       "0                     219"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT  COUNT(*) Married_and_has_stroke\n",
    "FROM    BRAIN_STROKE\n",
    "WHERE   ever_married = 'Yes' AND stroke = 1\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f605de4c",
   "metadata": {},
   "source": [
    "8. How many people are not married and has stroke?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "d86debb6",
   "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>Married_and_has_stroke</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Married_and_has_stroke\n",
       "0                      29"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT  COUNT(*) Married_and_has_stroke\n",
    "FROM    BRAIN_STROKE\n",
    "WHERE   ever_married = 'No' AND stroke = 1\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92afbaaf",
   "metadata": {},
   "source": [
    "9. What is the minimum, average and maximum age in the data?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "99708f33",
   "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_age</th>\n",
       "      <th>min_age</th>\n",
       "      <th>avg_age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>82</td>\n",
       "      <td>0.08</td>\n",
       "      <td>43.419859</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   max_age  min_age    avg_age\n",
       "0       82     0.08  43.419859"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT MAX(age) max_age, MIN(age) min_age, AVG(age) avg_age\n",
    "  FROM BRAIN_STROKE\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c45cb865",
   "metadata": {},
   "source": [
    "10. Details of people in the dataset below 10 years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "202a9f90",
   "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>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>hypertension</th>\n",
       "      <th>heart_disease</th>\n",
       "      <th>ever_married</th>\n",
       "      <th>work_type</th>\n",
       "      <th>Residence_type</th>\n",
       "      <th>avg_glucose_level</th>\n",
       "      <th>bmi</th>\n",
       "      <th>smoking_status</th>\n",
       "      <th>stroke</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Male</td>\n",
       "      <td>3.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>children</td>\n",
       "      <td>Rural</td>\n",
       "      <td>95.12</td>\n",
       "      <td>18.0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Female</td>\n",
       "      <td>8.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>Private</td>\n",
       "      <td>Urban</td>\n",
       "      <td>110.89</td>\n",
       "      <td>17.6</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Female</td>\n",
       "      <td>3.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>children</td>\n",
       "      <td>Urban</td>\n",
       "      <td>73.74</td>\n",
       "      <td>16.0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Male</td>\n",
       "      <td>4.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>children</td>\n",
       "      <td>Rural</td>\n",
       "      <td>79.17</td>\n",
       "      <td>20.0</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Male</td>\n",
       "      <td>8.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>children</td>\n",
       "      <td>Rural</td>\n",
       "      <td>78.05</td>\n",
       "      <td>25.7</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Female</td>\n",
       "      <td>0.64</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>children</td>\n",
       "      <td>Urban</td>\n",
       "      <td>83.82</td>\n",
       "      <td>24.9</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Female</td>\n",
       "      <td>4.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>children</td>\n",
       "      <td>Rural</td>\n",
       "      <td>93.25</td>\n",
       "      <td>16.6</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Female</td>\n",
       "      <td>0.88</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>children</td>\n",
       "      <td>Rural</td>\n",
       "      <td>88.11</td>\n",
       "      <td>15.5</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Male</td>\n",
       "      <td>4.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>children</td>\n",
       "      <td>Rural</td>\n",
       "      <td>106.22</td>\n",
       "      <td>16.7</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Male</td>\n",
       "      <td>5.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>children</td>\n",
       "      <td>Rural</td>\n",
       "      <td>85.84</td>\n",
       "      <td>16.4</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender   age  hypertension  heart_disease ever_married work_type  \\\n",
       "0    Male  3.00             0              0           No  children   \n",
       "1  Female  8.00             0              0           No   Private   \n",
       "2  Female  3.00             0              0           No  children   \n",
       "3    Male  4.00             0              0           No  children   \n",
       "4    Male  8.00             0              0           No  children   \n",
       "5  Female  0.64             0              0           No  children   \n",
       "6  Female  4.00             0              0           No  children   \n",
       "7  Female  0.88             0              0           No  children   \n",
       "8    Male  4.00             0              0           No  children   \n",
       "9    Male  5.00             0              0           No  children   \n",
       "\n",
       "  Residence_type  avg_glucose_level   bmi smoking_status  stroke  \n",
       "0          Rural              95.12  18.0        Unknown       0  \n",
       "1          Urban             110.89  17.6        Unknown       0  \n",
       "2          Urban              73.74  16.0        Unknown       0  \n",
       "3          Rural              79.17  20.0        Unknown       0  \n",
       "4          Rural              78.05  25.7        Unknown       0  \n",
       "5          Urban              83.82  24.9        Unknown       0  \n",
       "6          Rural              93.25  16.6        Unknown       0  \n",
       "7          Rural              88.11  15.5        Unknown       0  \n",
       "8          Rural             106.22  16.7        Unknown       0  \n",
       "9          Rural              85.84  16.4        Unknown       0  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT *\n",
    "  FROM BRAIN_STROKE\n",
    " WHERE age < 10\n",
    " LIMIT 10\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60396808",
   "metadata": {},
   "source": [
    "11. Number of children with either hypertension or stroke"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "9a96ba40",
   "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>gender</th>\n",
       "      <th>age</th>\n",
       "      <th>hypertension</th>\n",
       "      <th>heart_disease</th>\n",
       "      <th>ever_married</th>\n",
       "      <th>work_type</th>\n",
       "      <th>Residence_type</th>\n",
       "      <th>avg_glucose_level</th>\n",
       "      <th>bmi</th>\n",
       "      <th>smoking_status</th>\n",
       "      <th>stroke</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Female</td>\n",
       "      <td>14.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>children</td>\n",
       "      <td>Rural</td>\n",
       "      <td>57.93</td>\n",
       "      <td>30.9</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Female</td>\n",
       "      <td>1.32</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>No</td>\n",
       "      <td>children</td>\n",
       "      <td>Urban</td>\n",
       "      <td>70.37</td>\n",
       "      <td>18.7</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender    age  hypertension  heart_disease ever_married work_type  \\\n",
       "0  Female  14.00             0              0           No  children   \n",
       "1  Female   1.32             0              0           No  children   \n",
       "\n",
       "  Residence_type  avg_glucose_level   bmi smoking_status  stroke  \n",
       "0          Rural              57.93  30.9        Unknown       1  \n",
       "1          Urban              70.37  18.7        Unknown       1  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT  *\n",
    "FROM    BRAIN_STROKE\n",
    "WHERE   work_type = 'children' AND (hypertension = 1 OR stroke =1)\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20faf72b",
   "metadata": {},
   "source": [
    "12. What's the distibution of hypertension in the dataset?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "680c1b49",
   "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>hypertension</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>4502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>479</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   hypertension  count\n",
       "0             0   4502\n",
       "1             1    479"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT    hypertension, COUNT(*) count\n",
    "FROM      BRAIN_STROKE\n",
    "GROUP BY  hypertension\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "261c2dcb",
   "metadata": {},
   "source": [
    "13. How many male, and female has hypertension?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "ab8d4b76",
   "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>gender</th>\n",
       "      <th>hypertension</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Female</td>\n",
       "      <td>0</td>\n",
       "      <td>2643</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Female</td>\n",
       "      <td>1</td>\n",
       "      <td>264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Male</td>\n",
       "      <td>0</td>\n",
       "      <td>1859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Male</td>\n",
       "      <td>1</td>\n",
       "      <td>215</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender  hypertension  count\n",
       "0  Female             0   2643\n",
       "1  Female             1    264\n",
       "2    Male             0   1859\n",
       "3    Male             1    215"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT    gender, hypertension, COUNT(*) count\n",
    "FROM      BRAIN_STROKE\n",
    "GROUP BY  gender, hypertension\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b8746ad",
   "metadata": {},
   "source": [
    "14. For each smoking status show how many people have hypertension and how many do not? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "0484ac6e",
   "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>smoking_status</th>\n",
       "      <th>hypertension</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>1450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Unknown</td>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>formerly smoked</td>\n",
       "      <td>0</td>\n",
       "      <td>752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>formerly smoked</td>\n",
       "      <td>1</td>\n",
       "      <td>115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>never smoked</td>\n",
       "      <td>0</td>\n",
       "      <td>1615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>never smoked</td>\n",
       "      <td>1</td>\n",
       "      <td>223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>smokes</td>\n",
       "      <td>0</td>\n",
       "      <td>685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>smokes</td>\n",
       "      <td>1</td>\n",
       "      <td>91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    smoking_status  hypertension  count\n",
       "0          Unknown             0   1450\n",
       "1          Unknown             1     50\n",
       "2  formerly smoked             0    752\n",
       "3  formerly smoked             1    115\n",
       "4     never smoked             0   1615\n",
       "5     never smoked             1    223\n",
       "6           smokes             0    685\n",
       "7           smokes             1     91"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT    smoking_status, hypertension, COUNT(*) count\n",
    "FROM      BRAIN_STROKE\n",
    "GROUP BY  smoking_status, hypertension\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b3cd301c",
   "metadata": {},
   "source": [
    "15. How many male, and female has both hypertension and heart disease?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "8ec26f94",
   "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>gender</th>\n",
       "      <th>gender_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Female</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Male</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gender  gender_count\n",
       "0  Female            29\n",
       "1    Male            35"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT  gender,COUNT(gender) as gender_count\n",
    "FROM    BRAIN_STROKE\n",
    "WHERE   hypertension = 1 AND heart_disease = 1\n",
    "GROUP BY gender \n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f08311f5",
   "metadata": {},
   "source": [
    "16. Which residence has the lowest number of people with no heart disease?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "8e5963f1",
   "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>Residence_type</th>\n",
       "      <th>heart_disease</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rural</td>\n",
       "      <td>0</td>\n",
       "      <td>2315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Urban</td>\n",
       "      <td>0</td>\n",
       "      <td>2391</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Residence_type  heart_disease  count\n",
       "0          Rural              0   2315\n",
       "1          Urban              0   2391"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT    Residence_type, heart_disease, COUNT(*) count\n",
    "FROM      BRAIN_STROKE\n",
    "WHERE     heart_disease = 0\n",
    "GROUP BY  Residence_type\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98a6ffaf",
   "metadata": {},
   "source": [
    "17. How many percent of people with private work type has no hypertension?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "402ffca8",
   "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>work_type</th>\n",
       "      <th>hypertension</th>\n",
       "      <th>Percent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Private</td>\n",
       "      <td>0</td>\n",
       "      <td>90.48951</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  work_type  hypertension   Percent\n",
       "0   Private             0  90.48951"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "SELECT    work_type, hypertension, CAST(COUNT(*) AS FLOAT)/total.count * 100 AS Percent\n",
    "FROM      BRAIN_STROKE, (SELECT COUNT(*) count FROM BRAIN_STROKE WHERE work_type = 'Private') total\n",
    "WHERE     work_type = 'Private' AND hypertension = 0\n",
    "GROUP BY  hypertension\n",
    "\"\"\"\n",
    "pd.read_sql_query(query, conn)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2102621a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "e50bc28b",
   "metadata": {},
   "source": [
    "Summary\n",
    "\n",
    "![](download.jfif)\n",
    "\n",
    "* The brain stroke dataset was gotten from Kaggle\n",
    "* Pandas was used to import the dataset and converted into a dataframe\n",
    "* The dataset was queried using sqlite3\n",
    "\n",
    "Findings\n",
    "\n",
    "* There are 2907 females in the dataset, 140 of them have brain stroke while 2767 have no stroke.\n",
    "* There are 2074 males in the dataset, 108 of them have brain stroke while 1966 have no stroke. \n",
    "* 219 people that are married have stroke and 29 people that are not married have stroke\n",
    "* There are 2 children with stroke\n",
    "* 264 females have hypertension and 215 males have hypertension\n",
    "* 29 females and 35 males have both hypertension and heart disease\n",
    "* The urban area has the highest number of people with no heart disease\n",
    "* More than 90% of the people with private work type has no hypertension.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9088485",
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}