assets/notebook/fetch_mha.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fetch MHA data collected from 11 clinically validated screens"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Depression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tableauscraper import TableauScraper as TS\n",
    "import time\n",
    "def refresh():\n",
    "    url = \"https://public.tableau.com/views/DepressionDashboard/StateDepressionDashboard\"\n",
    "    ts = TS()\n",
    "    ts.loads(url)\n",
    "    workbook = ts.getWorkbook()\n",
    "    return workbook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all adult or young Age -> 2020-2023 Year -> Map (# and %)\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for age_item in ['All Ages', 'Over 18', 'Under 18']:\n",
    "        workbook = refresh()\n",
    "        print(year_item,'-',age_item)\n",
    "        t = workbook.getWorksheet('Depression_State Bar')\n",
    "        wb = t.setFilter('Year',year_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('Depression_State Bar')\n",
    "        wb = t.setFilter('Age1',age_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('Depression_State Map')\n",
    "        t.data.to_csv(f'./depression/{year_item}_{age_item}_Depression_State_Map.csv')\n",
    "        time.sleep(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> Race -> # -> Table\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for race_item in ['All Races/Ethnicities', \n",
    "                      'American Indian or Alaska Native', \n",
    "                      'Asian or Pacific Islander', \n",
    "                      'Black or African American (non-Hispanic)', \n",
    "                      'Hispanic or Latino', \n",
    "                      'More than one of the above', \n",
    "                      'White (non-Hispanic)']:\n",
    "        workbook = refresh()\n",
    "        workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "        print(year_item,'-',race_item)\n",
    "        t = workbook.getWorksheet('Depression_State Bar')\n",
    "        wb = t.setFilter('Year',year_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('RACE #per100K Table')\n",
    "        wb = t.setFilter('Race/Ethnicity',race_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('RACE #per100K Table')\n",
    "        if race_item == 'All Races/Ethnicities':\n",
    "            t.data.to_csv(f'./depression/number_{year_item}_all races_RACE #per100K Table.csv')\n",
    "        else:\n",
    "            t.data.to_csv(f'./depression/number_{year_item}_{race_item}_RACE #per100K Table.csv')\n",
    "        time.sleep(5)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> Race -> % -> Table\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for race_item in ['All Races/Ethnicities', \n",
    "                      'American Indian or Alaska Native', \n",
    "                      'Asian or Pacific Islander', \n",
    "                      'Black or African American (non-Hispanic)', \n",
    "                      'Hispanic or Latino', \n",
    "                      'More than one of the above', \n",
    "                      'White (non-Hispanic)']:\n",
    "        workbook = refresh()\n",
    "        workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "        workbook = workbook.setParameter(\"# per or Percent Positive\",\"Percent at Risk Among Screeners\")\n",
    "        print(year_item,'-',race_item)\n",
    "        t = workbook.getWorksheet('Depression_State Bar')\n",
    "        wb = t.setFilter('Year',year_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('RACE #per100K Table')\n",
    "        wb = t.setFilter('Race/Ethnicity',race_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('RACE #per100K Table')\n",
    "        if race_item == 'All Races/Ethnicities':\n",
    "            t.data.to_csv(f'./depression/percent_{year_item}_all races_RACE #per100K Table.csv')\n",
    "        else:\n",
    "            t.data.to_csv(f'./depression/percent_{year_item}_{race_item}_RACE #per100K Table.csv')\n",
    "        time.sleep(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> all Race -> % -> Table\n",
    "workbook = refresh()\n",
    "workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "workbook = workbook.setParameter(\"# per or Percent Positive\",\"Percent at Risk Among Screeners\")\n",
    "print(year_item,'-',race_item)\n",
    "t = workbook.getWorksheet('RACE #per100K Table')\n",
    "t.data.to_csv('./depression/percent_All Years_all races_RACE #per100K Table.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Suicide"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tableauscraper import TableauScraper as TS\n",
    "import time\n",
    "def refresh():\n",
    "    url = \"https://public.tableau.com/views/SuicideDashboard_16427751075670/StateSuicideDashboard?%3Adisplay_static_image=y&amp;%3AbootstrapWhenNotified=true&amp;%3Aembed=true&amp;%3Alanguage=en-US&amp;:embed=y&amp;:showVizHome=n&amp;:apiID=host0#navType=0&amp;navSrc=Parse\"\n",
    "    ts = TS()\n",
    "    ts.loads(url)\n",
    "    workbook = ts.getWorkbook()\n",
    "    return workbook\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook = refresh()\n",
    "print(workbook.getParameters())\n",
    "# workbook = workbook.setParameter('# per or Percent Positive','# of People At Risk per 100K of State Population')\n",
    "# for t in workbook.worksheets:\n",
    "#     print(f\"worksheet name : {t.name}\") \n",
    "#     print(t.getFilters())\n",
    "t = workbook.getWorksheet('KPI_Total Positive_Suicide_State')\n",
    "wb = t.setFilter('Year','2021')\n",
    "t = wb.getWorksheet('KPI_Total Positive_Suicide_State')\n",
    "wb = t.setFilter('Age1','Under 18',filterDelta=True)\n",
    "t = wb.getWorksheet('Suicide_State Map')\n",
    "t.data\n",
    "\n",
    "# Table - KPI_Total Positive_Suicide_State  : Age1: ['All Ages', 'Over 18', 'Under 18']\n",
    "#'column': '# per or Percent Positive', 'values': ['# of People At Risk per 100K of State Population', 'Percent at Risk Among Screeners']\n",
    "# Table - RACE # per 100K Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook = refresh()\n",
    "workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "for t in workbook.worksheets:\n",
    "    print(f\"worksheet name : {t.name}\") \n",
    "    print(t.getFilters())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all adult or young Age -> 2020-2023 Year -> Map (# and %)\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for age_item in ['All Ages', 'Over 18', 'Under 18']:\n",
    "        workbook = refresh()\n",
    "        print(year_item,'-',age_item)\n",
    "        t = workbook.getWorksheet('KPI_Total Positive_Suicide_State')\n",
    "        wb = t.setFilter('Year',year_item)\n",
    "        t = wb.getWorksheet('KPI_Total Positive_Suicide_State')\n",
    "        wb = t.setFilter('Age1',age_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('Suicide_State Map')\n",
    "        t.data.to_csv(f'./suicide/{year_item}_{age_item}_Suicide_State_Map.csv')\n",
    "        time.sleep(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> Race -> # -> Table\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for race_item in ['All Races/Ethnicities', \n",
    "                      'American Indian or Alaska Native', \n",
    "                      'Asian or Pacific Islander', \n",
    "                      'Black or African American (non-Hispanic)', \n",
    "                      'Hispanic or Latino', \n",
    "                      'More than one of the above', \n",
    "                      'White (non-Hispanic)']:\n",
    "        workbook = refresh()\n",
    "        workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "        print(year_item,'-',race_item)\n",
    "        t = workbook.getWorksheet('KPI_Total Positive_Suicide_State')\n",
    "        wb = t.setFilter('Year',year_item)\n",
    "        t = wb.getWorksheet('RACE # per 100K Table')\n",
    "        wb = t.setFilter('Race/Ethnicity',race_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('RACE # per 100K Table')\n",
    "        if race_item == 'All Races/Ethnicities':\n",
    "            t.data.to_csv(f'./suicide/number_{year_item}_all races_RACE #per100K Table.csv')\n",
    "        else:\n",
    "            t.data.to_csv(f'./suicide/number_{year_item}_{race_item}_RACE #per100K Table.csv')\n",
    "        time.sleep(5)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> Race -> % -> Table\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for race_item in ['All Races/Ethnicities', \n",
    "                      'American Indian or Alaska Native', \n",
    "                      'Asian or Pacific Islander', \n",
    "                      'Black or African American (non-Hispanic)', \n",
    "                      'Hispanic or Latino', \n",
    "                      'More than one of the above', \n",
    "                      'White (non-Hispanic)']:\n",
    "        workbook = refresh()\n",
    "        workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "        workbook = workbook.setParameter(\"# per or Percent Positive\",\"Percent at Risk Among Screeners\")\n",
    "        print(year_item,'-',race_item)\n",
    "        t = workbook.getWorksheet('KPI_Total Positive_Suicide_State')\n",
    "        wb = t.setFilter('Year',year_item)\n",
    "        t = wb.getWorksheet('RACE # per 100K Table')\n",
    "        wb = t.setFilter('Race/Ethnicity',race_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('RACE # per 100K Table')\n",
    "        if race_item == 'All Races/Ethnicities':\n",
    "            t.data.to_csv(f'./suicide/percent_{year_item}_all races_RACE #per100K Table.csv')\n",
    "        else:\n",
    "            t.data.to_csv(f'./suicide/percent_{year_item}_{race_item}_RACE #per100K Table.csv')\n",
    "        time.sleep(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> all Race -> % -> Table\n",
    "workbook = refresh()\n",
    "workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "workbook = workbook.setParameter(\"# per or Percent Positive\",\"Percent at Risk Among Screeners\")\n",
    "print(year_item,'-',race_item)\n",
    "t = workbook.getWorksheet('RACE # per 100K Table')\n",
    "t.data.to_csv('./suicide/percent_All Years_all races_RACE #per100K Table.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "PTSD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tableauscraper import TableauScraper as TS\n",
    "import time\n",
    "def refresh():\n",
    "    url = \"https://public.tableau.com/views/PTSDDashboard/PTSDDashboard_State?%3Adisplay_static_image=y&amp;%3AbootstrapWhenNotified=true&amp;%3Aembed=true&amp;%3Alanguage=en-US&amp;:embed=y&amp;:showVizHome=n&amp;:apiID=host0#navType=0&amp;navSrc=Parse\"\n",
    "    ts = TS()\n",
    "    ts.loads(url)\n",
    "    workbook = ts.getWorkbook()\n",
    "    return workbook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook = refresh()\n",
    "for t in workbook.worksheets:\n",
    "    print(f\"worksheet name : {t.name}\") #show worksheet name\n",
    "    t.data.to_csv(f'./ptsd/{t.name}.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook = refresh()\n",
    "# print(workbook.getParameters())\n",
    "# workbook = workbook.setParameter('# per or Percent Positive','Percent at Risk Among Screeners')\n",
    "# for t in workbook.worksheets:\n",
    "#     print(f\"worksheet name : {t.name}\") \n",
    "#     print(t.getFilters())\n",
    "t = workbook.getWorksheet('# per 100K Race Bar.DZV')\n",
    "wb = t.setFilter('Year','2020')\n",
    "t = wb.getWorksheet('KPI_Total Population_PTSD_State')\n",
    "wb = t.setFilter('Age','Under 18',filterDelta=True)\n",
    "t = wb.getWorksheet('PTSD_State Map')\n",
    "t.data\n",
    "\n",
    "# Table - KPI_Total Population_PTSD_State  : Age: ['All Ages', 'Over 18', 'Under 18']\n",
    "# Table - # per 100K Race Bar.DZV : Year: ['2020', '2021', '2022', '2023', 'All Years']; Race\n",
    "#'column': '# per or Percent Positive', 'values': ['# of People At Risk per 100K of State Population', 'Percent at Risk Among Screeners']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all adult or young Age -> 2020-2023 Year -> Map (# and %)\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for age_item in ['All Ages', 'Over 18', 'Under 18']:\n",
    "        workbook = refresh()\n",
    "        print(year_item,'-',age_item)\n",
    "        t = workbook.getWorksheet('# per 100K Race Bar.DZV')\n",
    "        wb = t.setFilter('Year',year_item)\n",
    "        t = wb.getWorksheet('KPI_Total Population_PTSD_State')\n",
    "        wb = t.setFilter('Age',age_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('PTSD_State Map')\n",
    "        t.data.to_csv(f'./ptsd/{year_item}_{age_item}_PTSD_State_Map.csv')\n",
    "        time.sleep(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> Race -> # -> Table\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for race_item in ['All Races/Ethnicities', \n",
    "                      'American Indian or Alaska Native', \n",
    "                      'Asian or Pacific Islander', \n",
    "                      'Black or African American (non-Hispanic)', \n",
    "                      'Hispanic or Latino', \n",
    "                      'More than one of the above', \n",
    "                      'White (non-Hispanic)']:\n",
    "        workbook = refresh()\n",
    "        workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "        print(year_item,'-',race_item)\n",
    "        t = workbook.getWorksheet('# per 100K Race Bar.DZV')\n",
    "        wb = t.setFilter('Year',year_item)\n",
    "        t = wb.getWorksheet('# per 100K Race Bar.DZV')\n",
    "        wb = t.setFilter('Race/Ethnicity',race_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('# per 100K Race Bar.DZV')\n",
    "        if race_item == 'All Races/Ethnicities':\n",
    "            t.data.to_csv(f'./ptsd/number_{year_item}_all races_RACE #per100K Table.csv')\n",
    "        else:\n",
    "            t.data.to_csv(f'./ptsd/number_{year_item}_{race_item}_RACE #per100K Table.csv')\n",
    "        time.sleep(5)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> Race -> % -> Table\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for race_item in ['All Races/Ethnicities', \n",
    "                      'American Indian or Alaska Native', \n",
    "                      'Asian or Pacific Islander', \n",
    "                      'Black or African American (non-Hispanic)', \n",
    "                      'Hispanic or Latino', \n",
    "                      'More than one of the above', \n",
    "                      'White (non-Hispanic)']:\n",
    "        workbook = refresh()\n",
    "        workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "        workbook = workbook.setParameter(\"# per or Percent Positive\",\"Percent at Risk Among Screeners\")\n",
    "        print(year_item,'-',race_item)\n",
    "        t = workbook.getWorksheet('# per 100K Race Bar.DZV')\n",
    "        wb = t.setFilter('Year',year_item)\n",
    "        t = wb.getWorksheet('# per 100K Race Bar.DZV')\n",
    "        wb = t.setFilter('Race/Ethnicity',race_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('# per 100K Race Bar.DZV')\n",
    "        if race_item == 'All Races/Ethnicities':\n",
    "            t.data.to_csv(f'./ptsd/percent_{year_item}_all races_RACE #per100K Table.csv')\n",
    "        else:\n",
    "            t.data.to_csv(f'./ptsd/percent_{year_item}_{race_item}_RACE #per100K Table.csv')\n",
    "        time.sleep(5)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> all Race -> % -> Table\n",
    "workbook = refresh()\n",
    "workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "workbook = workbook.setParameter(\"# per or Percent Positive\",\"Percent at Risk Among Screeners\")\n",
    "print(year_item,'-',race_item)\n",
    "t = workbook.getWorksheet('# per 100K Race Bar.DZV')\n",
    "t.data.to_csv('./ptsd/percent_All Years_all races_RACE #per100K Table.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Psychosis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tableauscraper import TableauScraper as TS\n",
    "import time\n",
    "def refresh():\n",
    "    url = \"https://public.tableau.com/views/PsychosisDashboard/StatePsychosisDashboard?%3Adisplay_static_image=y&amp;%3AbootstrapWhenNotified=true&amp;%3Aembed=true&amp;%3Alanguage=en-US&amp;:embed=y&amp;:showVizHome=n&amp;:apiID=host0#navType=0&amp;navSrc=Parse\"\n",
    "    ts = TS()\n",
    "    ts.loads(url)\n",
    "    workbook = ts.getWorkbook()\n",
    "    return workbook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook = refresh()\n",
    "for t in workbook.worksheets:\n",
    "    print(f\"worksheet name : {t.name}\") #show worksheet name\n",
    "    t.data.to_csv(f'./psychosis/{t.name}.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook = refresh()\n",
    "print(workbook.getParameters())\n",
    "# workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "# workbook = workbook.setParameter('# per or Percent Positive','Percent at Risk Among Screeners')\n",
    "# for t in workbook.worksheets:\n",
    "#     print(f\"worksheet name : {t.name}\") \n",
    "#     print(t.getFilters())\n",
    "t = workbook.getWorksheet('KPI_Total Population_Dep_State')\n",
    "wb = t.setFilter('Year','2020')\n",
    "t = wb.getWorksheet('KPI_Total Population_Dep_State')\n",
    "wb = t.setFilter('Age','Under 18',filterDelta=True)\n",
    "t = wb.getWorksheet('Psychosis_State Map')\n",
    "t.data\n",
    "\n",
    "# Table - KPI_Total Population_Dep_State  : Age: ['All Ages', 'Over 18', 'Under 18'];  Year: ['2020', '2021', '2022', '2023', 'All Years']\n",
    "# Table -  RACE # per 100K Table : Race\n",
    "#'column': '# per or Percent Positive', 'values': ['# of People At Risk per 100K of State Population', 'Percent at Risk Among Screeners']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all adult or young Age -> 2020-2023 Year -> Map (# and %)\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for age_item in ['All Ages', 'Over 18', 'Under 18']:\n",
    "        workbook = refresh()\n",
    "        print(year_item,'-',age_item)\n",
    "        t = workbook.getWorksheet('KPI_Total Population_Dep_State')\n",
    "        wb = t.setFilter('Year',year_item)\n",
    "        t = wb.getWorksheet('KPI_Total Population_Dep_State')\n",
    "        wb = t.setFilter('Age',age_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('Psychosis_State Map')\n",
    "        t.data.to_csv(f'./psychosis/{year_item}_{age_item}_Psychosis_State_Map.csv')\n",
    "        time.sleep(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> Race -> # -> Table\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for race_item in ['All Races/Ethnicities', \n",
    "                      'American Indian or Alaska Native', \n",
    "                      'Asian or Pacific Islander', \n",
    "                      'Black or African American (non-Hispanic)', \n",
    "                      'Hispanic or Latino', \n",
    "                      'More than one of the above', \n",
    "                      'White (non-Hispanic)']:\n",
    "        workbook = refresh()\n",
    "        workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "        print(year_item,'-',race_item)\n",
    "        t = workbook.getWorksheet('KPI_Total Population_Dep_State')\n",
    "        wb = t.setFilter('Year',year_item)\n",
    "        t = wb.getWorksheet('RACE # per 100K Table')\n",
    "        wb = t.setFilter('Race/Ethnicity',race_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('RACE # per 100K Table')\n",
    "        if race_item == 'All Races/Ethnicities':\n",
    "            t.data.to_csv(f'./psychosis/number_{year_item}_all races_RACE #per100K Table.csv')\n",
    "        else:\n",
    "            t.data.to_csv(f'./psychosis/number_{year_item}_{race_item}_RACE #per100K Table.csv')\n",
    "        time.sleep(5)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> Race -> % -> Table\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for race_item in ['All Races/Ethnicities', \n",
    "                      'American Indian or Alaska Native', \n",
    "                      'Asian or Pacific Islander', \n",
    "                      'Black or African American (non-Hispanic)', \n",
    "                      'Hispanic or Latino', \n",
    "                      'More than one of the above', \n",
    "                      'White (non-Hispanic)']:\n",
    "        workbook = refresh()\n",
    "        workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "        workbook = workbook.setParameter(\"# per or Percent Positive\",\"Percent at Risk Among Screeners\")\n",
    "        print(year_item,'-',race_item)\n",
    "        t = workbook.getWorksheet('KPI_Total Population_Dep_State')\n",
    "        wb = t.setFilter('Year',year_item)\n",
    "        t = wb.getWorksheet('RACE # per 100K Table')\n",
    "        wb = t.setFilter('Race/Ethnicity',race_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('RACE # per 100K Table')\n",
    "        if race_item == 'All Races/Ethnicities':\n",
    "            t.data.to_csv(f'./psychosis/percent_{year_item}_all races_RACE #per100K Table.csv')\n",
    "        else:\n",
    "            t.data.to_csv(f'./psychosis/percent_{year_item}_{race_item}_RACE #per100K Table.csv')\n",
    "        time.sleep(5)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> all Race -> % -> Table\n",
    "workbook = refresh()\n",
    "workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "workbook = workbook.setParameter(\"# per or Percent Positive\",\"Percent at Risk Among Screeners\")\n",
    "print(year_item,'-',race_item)\n",
    "t = workbook.getWorksheet('RACE # per 100K Table')\n",
    "t.data.to_csv('./psychosis/percent_All Years_all races_RACE #per100K Table.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Trauma"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from tableauscraper import TableauScraper as TS\n",
    "import time\n",
    "def refresh():\n",
    "    url = \"https://public.tableau.com/views/TraumaDashboard/StateTraumaDashboard?%3Adisplay_static_image=y&%3AbootstrapWhenNotified=true&%3Aembed=true&%3Alanguage=en-US&:embed=y&:showVizHome=n&:apiID=host0#navType=0&navSrc=Parse\"\n",
    "    ts = TS()\n",
    "    ts.loads(url)\n",
    "    workbook = ts.getWorkbook()\n",
    "    return workbook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook = refresh()\n",
    "for t in workbook.worksheets:\n",
    "    print(f\"worksheet name : {t.name}\") #show worksheet name\n",
    "    t.data.to_csv(f'./trauma/{t.name}.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook = refresh()\n",
    "# print(workbook.getParameters())\n",
    "# workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "# workbook = workbook.setParameter('# per or Percent Positive','Percent of Trauma Survivors Among Screeners')\n",
    "# for t in workbook.worksheets:\n",
    "#     print(f\"worksheet name : {t.name}\") \n",
    "#     print(t.getFilters())\n",
    "t = workbook.getWorksheet('KPI_Total Reporting Special Pop_State')\n",
    "wb = t.setFilter('Year','2020',filterDelta=True)\n",
    "t = wb.getWorksheet('Trauma_State Map')\n",
    "wb = t.setFilter('Age','Under 18',filterDelta=True)\n",
    "t = wb.getWorksheet('Trauma_State Bar')\n",
    "t.data\n",
    "\n",
    "# Table - Trauma_State Map  : Age: ['All Ages', 'Over 18', 'Under 18']\n",
    "# Table -  KPI_Total Reporting Special Pop_State: Year: ['All Years', '2020', '2021', '2022', '2023']\n",
    "# Table - RACE # per 100K Table  : 'Race/Ethnicity'\n",
    "#'column': '# per or Percent Positive', 'values': ['# of People Identifying as Trauma Survivors per 100K of State Population', 'Percent of Trauma Survivors Among Screeners']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all adult or young Age -> 2020-2023 Year -> Map (# and %)\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for age_item in ['All Ages', 'Over 18', 'Under 18']:\n",
    "        workbook = refresh()\n",
    "        print(year_item,'-',age_item)\n",
    "        t = workbook.getWorksheet('KPI_Total Reporting Special Pop_State')\n",
    "        wb = t.setFilter('Year',year_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('Trauma_State Map')\n",
    "        wb = t.setFilter('Age',age_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('Trauma_State Map')\n",
    "        t.data.to_csv(f'./trauma/{year_item}_{age_item}_Trauma_State_Map.csv')\n",
    "        time.sleep(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all adult or young Age -> 2020-2023 Year -> Map (# and %)\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for age_item in ['All Ages', 'Over 18', 'Under 18']:\n",
    "        workbook = refresh()\n",
    "        workbook = workbook.setParameter('# per or Percent Positive','Percent of Trauma Survivors Among Screeners')\n",
    "        print(year_item,'-',age_item)\n",
    "        t = workbook.getWorksheet('KPI_Total Reporting Special Pop_State')\n",
    "        wb = t.setFilter('Year',year_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('Trauma_State Map')\n",
    "        wb = t.setFilter('Age',age_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('Trauma_State Map')\n",
    "        t.data.to_csv(f'./trauma/percent_{year_item}_{age_item}_Trauma_State_Map.csv')\n",
    "        time.sleep(5)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "workbook = refresh()\n",
    "workbook = workbook.setParameter('# per or Percent Positive','Percent of Trauma Survivors Among Screeners')\n",
    "t = workbook.getWorksheet('Trauma_State Map')\n",
    "t.data.to_csv('./trauma/percent_All Years_All Ages_Trauma_State_Map.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> Race -> # -> Table\n",
    "for year_item in [ '2020', '2021', '2022', '2023']: #'All Years'\n",
    "    for race_item in ['All Races/Ethnicities', \n",
    "                      'American Indian or Alaska Native', \n",
    "                      'Asian or Pacific Islander', \n",
    "                      'Black or African American (non-Hispanic)', \n",
    "                      'Hispanic or Latino', \n",
    "                      'More than one of the above', \n",
    "                      'White (non-Hispanic)']:\n",
    "        workbook = refresh()\n",
    "        workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "        print(year_item,'-',race_item)\n",
    "        t = workbook.getWorksheet('KPI_Total Reporting Special Pop_State')\n",
    "        wb = t.setFilter('Year',year_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('RACE # per 100K Table')\n",
    "        wb = t.setFilter('Race/Ethnicity',race_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('RACE # per 100K Table')\n",
    "        if race_item == 'All Races/Ethnicities':\n",
    "            t.data.to_csv(f'./trauma/number_{year_item}_all races_RACE #per100K Table.csv')\n",
    "        else:\n",
    "            t.data.to_csv(f'./trauma/number_{year_item}_{race_item}_RACE #per100K Table.csv')\n",
    "        time.sleep(5)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> Race -> % -> Table\n",
    "for year_item in ['2020', '2021', '2022', '2023', 'All Years']:\n",
    "    for race_item in ['All Races/Ethnicities', \n",
    "                      'American Indian or Alaska Native', \n",
    "                      'Asian or Pacific Islander', \n",
    "                      'Black or African American (non-Hispanic)', \n",
    "                      'Hispanic or Latino', \n",
    "                      'More than one of the above', \n",
    "                      'White (non-Hispanic)']:\n",
    "        workbook = refresh()\n",
    "        workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "        workbook = workbook.setParameter(\"# per or Percent Positive\",\"Percent of Trauma Survivors Among Screeners\")\n",
    "        print(year_item,'-',race_item)\n",
    "        t = workbook.getWorksheet('KPI_Total Reporting Special Pop_State')\n",
    "        wb = t.setFilter('Year',year_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('RACE # per 100K Table')\n",
    "        wb = t.setFilter('Race/Ethnicity',race_item,filterDelta=True)\n",
    "        t = wb.getWorksheet('RACE # per 100K Table')\n",
    "        if race_item == 'All Races/Ethnicities':\n",
    "            t.data.to_csv(f'./trauma/percent_{year_item}_all races_RACE #per100K Table.csv')\n",
    "        else:\n",
    "            t.data.to_csv(f'./trauma/percent_{year_item}_{race_item}_RACE #per100K Table.csv')\n",
    "        time.sleep(5)\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# all Year -> all Race -> % -> Table\n",
    "workbook = refresh()\n",
    "workbook = workbook.setParameter(\"Charts to Show\",\"Race\")\n",
    "workbook = workbook.setParameter(\"# per or Percent Positive\",\"Percent of Trauma Survivors Among Screeners\")\n",
    "# print(year_item,'-',race_item)\n",
    "t = workbook.getWorksheet('RACE # per 100K Table')\n",
    "t.data.to_csv('./psychosis/percent_All Years_all races_RACE #per100K Table.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}