From Pandas to Tableau Server.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How to publish Pandas DataFrames to Tableau Server\n",
    "<p>By <a href=\"https://www.linkedin.com/in/jmperafan/\">Juan Manuel Perafan</a></p>\n",
    "\n",
    "Tableau Server has many data governance features to promote exploration, collaboration, and security. <br>\n",
    "Out of all of them, my favorite by far is the Data Server.\n",
    "\n",
    "The Data Server is a powerful, but often underutilized, feature that allows you to publish certified data sources directly to Tableau Server. <br>\n",
    "These data sources can be analyzed with Tableau Web Edit or directly in Tableau Desktop. There numerous benefits of using the Data Server:\n",
    "\n",
    "- **User friendly**: Empowering your business users to use analyze this data Tableau without having to understand how to connect to a database, join tables, or write difficult calculations. \n",
    "- **Single Version of the Truth**: It removes the risk of ambiguity for important data versions of your data sources and ensures people use the right calculations and logic. \n",
    "- **Security**: It allows you to implement row-level security and allows you to monitor who sees what data.  \n",
    "\n",
    "In my career, I have prepared a couple dozen datasources in Tableau Desktop for other analysts to explore in Tableau Server. This process might involve a lot of manual work and prone to errors. <br>\n",
    "Additionally, there are some use cases (not many, but some) that might not be possible or extremely tedious in Tableau. Some examples include:\n",
    "\n",
    "- The 'IN' operator from SQL\n",
    "- Proper case for strings\n",
    "- Regex lookup from right to left\n",
    "- Prediction output from a Machine Learning Model\n",
    "- Row_number or index without a Table Calculation\n",
    "- Tokenizing strings\n",
    "- Removing rows and columns if every value is NULL\n",
    "\n",
    "But what if I told you that you can wrangle your data in Python and publish the output as a hyper extract in your Tableau Server? <br>\n",
    "Follow the instructions if you want to learn how!\n",
    "\n",
    "<a href=\"https://www.linkedin.com/feed/hashtag/juanalytics/\">#juanalytics</a>\n",
    "\n",
    "<p><img src=\"https://www.blastam.com/wp-content/uploads/tableau-python-forecast.png\" alt=\"\";></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading the packages\n",
    "\n",
    "There are multiple packages for you to manipulate your data in Python. <br>\n",
    "They might different from use case to use case, but more often than not, it will involve one of these 4.\n",
    "\n",
    "- Pandas\n",
    "- Numpy\n",
    "- Scipy\n",
    "- Sklearn \n",
    "\n",
    "On top of any package you might use to preapare your data, there are 2 additionally packages you will need for this to work.\n",
    "\n",
    "### pantab\n",
    "This package uses the Tableau SDK to transform Pandas Dataframes into Hyper files. <br>\n",
    "This package uses the Extract API 2.0 and installing it can get tricky. <a href=\"https://help.tableau.com/current/api/extract_api/en-us/Extract/extract_api_installing.html\">Here is how to install it. </a>\n",
    "\n",
    "### tableauserverclient\n",
    "This package servers as a Python wrapper for the REST API. <br>\n",
    "It allows to do multiple tasks, including publishing datasources to a server."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# The usual suspects: Pandas\n",
    "import pandas as pd\n",
    "\n",
    "# Pandleau is used transform a Pandas DataFrame into a Hyper file\n",
    "# To use pantab you need to install the Extract API 2.0\n",
    "\n",
    "import pantab #https://github.com/WillAyd/pantab\n",
    "\n",
    "# With the tableauserverclient library you can do almost everything that you can do with the REST API, including: publish workbooks, create users, query sites, etc.\n",
    "# For this specific tutorial, we will use TSC to publish our hyper file to a Tableau Server\n",
    "\n",
    "import tableauserverclient as TSC #https://github.com/tableau/server-client-python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a data source\n",
    "\n",
    "For the sake of simplicity, I just created a very simple dataset for this tutorial. <br>\n",
    "But it is 100% up to you! You can call data from many datasources and implement any logic. <br>\n",
    "Machine Learning? Advanced Regex? APIs? Go crazy with that data!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Name</th>\n",
       "      <th>Age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>Juan</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>Carlos</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>Andres</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>Felipe</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Name  Age\n",
       "0    Juan   20\n",
       "1  Carlos   21\n",
       "2  Andres   19\n",
       "3  Felipe   18"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create Pandas DataFrame or effectively load data and create it\n",
    "df = pd.DataFrame(\n",
    "    {'Name':['Juan', 'Carlos', 'Andres', 'Felipe']\n",
    "     , 'Age':[20, 21, 19, 18]}\n",
    ") \n",
    "  \n",
    "# This is how the datasources we created looks like\n",
    "df "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transform your DataFrame into a Tableau Hyper file\n",
    "\n",
    "Once you are done going crazy on your data, you will have to transform it into a Hyper file.<br>\n",
    "Luckily for you, this is as simple and straight-forward as typing one line of code.  <br>\n",
    "I saved a copy of this extract in a folder called datasets, but you can choose any place to store it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "os.remove('datasets/extract.hyper') # Remove the file if it already exists\n",
    "\n",
    "# Transform your data from a Pandas DataFrame to a Tableau Hyper Extract!\n",
    "df_tableau = pantab.frame_to_hyper(df, 'datasets/extract.hyper')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Sign in to your Tableau Server \n",
    "\n",
    "This part is trick to demo since most of these fields will depend on your own Tableau Server. <br>\n",
    "In this first cell, we are signing into our own Tableau Server.<br>\n",
    "I shouldn't have to point out that you should never hardcore your credentials. <br>\n",
    "Consider using a solution like **os.environ['SECRET']** from the os library. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Since this is not a real server, it will throw an error.\n",
    "# Just change the parameters with your own information. \n",
    "\n",
    "# There are safer ways to pass your credentials... \n",
    "# But I will keep it simple for the sake of this tutorial\n",
    "tableau_auth = TSC.TableauAuth('USERNAME', 'PASSWORD')\n",
    "\n",
    "# create an instance for your server\n",
    "server = TSC.Server('https://SERVER_URL')\n",
    "\n",
    "# Select to which site. Leave empty if there is only one site\n",
    "site_id=''\n",
    "\n",
    "# call the sign-in method with the auth object\n",
    "server.auth.sign_in(tableau_auth)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uploading the Extract to Tableau Server\n",
    "\n",
    "This is the moment you have been waiting for! <b>\n",
    "You should indicate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a new project id or select an existing one.\n",
    "# 3a8b... is just an example. You should change it.\n",
    "project_id = '3a8b6148-493c-11e6-a621-6f3499394a39'\n",
    "\n",
    "# The path and name of the data source to publish\n",
    "file_path = r'datasets/extract.hyper'\n",
    "  \n",
    "# Create a datasource with project id 3a8b...\n",
    "new_datasource = TSC.DatasourceItem(project_id)\n",
    "\n",
    "# You can publish the data source using this command\n",
    "new_datasource = server.datasources.publish(\n",
    "    \n",
    "    # This is the object we created earlier with pandtab\n",
    "     df_tableau\n",
    "    \n",
    "    # The path and name of the data source to publish\n",
    "     , file_path\n",
    "    \n",
    "    # Mode, you can choose between 'CreateNew', 'OverWrite', or 'Append'\n",
    "    , 'CreateNew'\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}