tabulardataset_parquet_categoricals_issue_di.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1> Example of TabularDataset Categoricals Issue </h1>\n",
    "\n",
    "Native pandas preserves categorical data types when writing and reading to Parquet format.  Unfortunately, Azure's TabularDataset implementation does not preserve this nice behavior.   This notebook demonstrates the difference.\n",
    "\n",
    "This is more of a nice to have than a requirement, but I could imagine cases with (say) GBM models which accept categoricals not being able to leverage TabularDatasets due to this behavior.\n",
    "\n",
    "Core developed using Python 3.6 via AzureML."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2> Pure Pandas Behavior </h2>\n",
    "This section demonstrates that Pandas is able to write a dataframe that contains categoricals to a Parquet file, and then read in the data with categorical information intact.  I was using Pandas 0.25.3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'0.25.3'"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "pd.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1.18.5'"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "np.__version__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Directory into which you want to save the parquet files\n",
    "base_dir = os.getenv('HOME')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "x       int64\n",
       "y    category\n",
       "dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create an example dataframe with a categorical feature\n",
    "# Note I scramble the category order to illustrate that category order is preserved during\n",
    "# parquet read/write\n",
    "cat_in_df = pd.DataFrame.from_dict({'x': list(range(100)), \n",
    "                                   'y': pd.Categorical(['a', 'b', 'c', 'd', 'e']*20, \n",
    "                                                       categories = ['e', 'a', 'b', 'd', 'c'])})\n",
    "cat_in_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['e', 'a', 'b', 'd', 'c'], dtype='object')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# List the categories - note random order\n",
    "cat_in_df['y'].cat.categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save the data to parquet\n",
    "cat_in_df.to_parquet(os.path.join(base_dir, 'cat_in_df.parquet'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "x       int64\n",
       "y    category\n",
       "dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Retrieve from parquet.  Verify the categorical feature remains unchanged\n",
    "cat_out_df = pd.read_parquet(os.path.join(base_dir, 'cat_in_df.parquet'))\n",
    "cat_out_df.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['e', 'a', 'b', 'd', 'c'], dtype='object')"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# List the categories - note random order is preserved!\n",
    "cat_out_df['y'].cat.categories"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Everything is fine.  I am able to write categoricals to Parquet, then read the information back."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2> Azure Setup </h2>\n",
    "I want to show the same series of operations via Azure TabularDataset, but first I need to do some setup for the Azure Machine Learning environment.  You will need to use your own workspace, blob storage, etc. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to workspace - use your own info here\n",
    "from azureml.core.workspace import Workspace\n",
    "\n",
    "ws = Workspace.get(name='YOUR-INFO-HERE', # Put in your own info\n",
    "               subscription_id=\"YOUR-INFO-HERE\",\n",
    "               resource_group=\"YOUR-INFO-HERE\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to a datastore - use your own info here\n",
    "from azureml.core import Datastore\n",
    "output_datastore = Datastore.get(ws, 'YOUR-INFO-HERE')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Set a target path on your datastore where you want to save files\n",
    "output_path = 'YOUR-INFO-HERE'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h2> TabularDataSet Behavior </h2>\n",
    "In this section I show a failure when the same set of operations is performed using the Azure TabularDataset wrapper"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Uploading an estimated of 1 files\n",
      "Uploading /home/azureuser/cat_in_df.parquet\n",
      "Uploaded /home/azureuser/cat_in_df.parquet, 1 files out of an estimated total of 1\n",
      "Uploaded 1 files\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "$AZUREML_DATAREFERENCE_46278f9661044f5aadd8b7e3f2508339"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Move the parquet file to the datastore\n",
    "output_datastore.upload_files(files=[os.path.join(base_dir, 'cat_in_df.parquet')],\n",
    "                              target_path=output_path,\n",
    "                              overwrite=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make the initial file into a dataset\n",
    "from azureml.core import Dataset\n",
    "\n",
    "cat_in_dset =  Dataset.Tabular.from_parquet_files(path=[(output_datastore,  \n",
    "                                                           '/'.join([output_path, 'cat_in_df.parquet']))])   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read the dataset into Pandas\n",
    "cat_in_df_2 = cat_in_dset.to_pandas_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h3> Here is the issue - Lost categorical information </h3>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "x     int64\n",
       "y    object\n",
       "dtype: object"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Note the categorical information has been lost!\n",
    "cat_in_df_2.dtypes"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.6 - AzureML",
   "language": "python",
   "name": "python3-azureml"
  },
  "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.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}