Showcase ROUNDING across MSAccess, Python, Matlab & C#

WLTP_rounding.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Showcase ROUNDING across MSAccess, Python, Matlab & C#\n",
    "\n",
    "## Linux dependencies for this notebook:\n",
    "- *octave*: install it with:\n",
    "\n",
    "      sudo apt install octave\n",
    "\n",
    "\n",
    "- *dotnet-core*: \n",
    "  - Follow [these `apt`(*Debian, Ubutnu*) instructions](https://dotnet.microsoft.com/download/linux-package-manager/debian9/sdk-current).\n",
    "    to install .NET SDK from MS repository:\n",
    "  - Source `/etc/profile` (OR append `~/.dotnet/tools` in *PATH*, OR login & logout).\n",
    "  - Install REPL for *C#*: \n",
    "  \n",
    "        dotnet tool install -g dotnet-script\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## INPUT numbers to be rounded\n",
    "Interesting numbers taken from: https://stackoverflow.com/a/45424214/548792"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0.49999999999999994, 5000000000000001.0, -2.4, 2.4]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = [0.49999999999999994, 5000000000000001.0, -2.4, 2.4]\n",
    "#a = [0.499999999999994, 500000000000001.0, -2.4, 2.4]  Access input\n",
    "a"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## *MSAccess* behavior\n",
    "While *Access* internally stores floats as `doubles` (8-bytes) <br>\n",
    "**BUT...** the GUI only allows floats with 15 decimals, and not 17 needed to [represent IEEE 754 doubles](https://en.wikipedia.org/wiki/Double-precision_floating-point_format#IEEE_754_double-precision_binary_floating-point_format:_binary64)\n",
    "(15 decimals are enough to be preserved when passed through an IEEE 754 double).\n",
    "\n",
    "\n",
    "```\n",
    "                Input             Out1               Out2             Out3\n",
    "    0.499999999999994                0  0.999999999999994              0.5     ## Input had 15 decimals (instead of 17)!!\n",
    "    500000000000001    500000000000001    500000000000002  500000000000001     ## Input had 15 digits (instead of 16)!!\n",
    "    -2.4                            -2               -1.9             -2.4\n",
    "    2.4                              2                2.9             2.4\n",
    "\n",
    "\n",
    "WHERE:\n",
    "    - Out1: int(Input + 0.5)\n",
    "    - Out2: Input + 0.5\n",
    "    - Out3: int(10 * Input + 0.5) / 10\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# COPIED manually from `Out1`, above\n",
    "r_access = [0, 500000000000001, -2, 2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Python floats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'%.17f'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import math\n",
    "%precision 17"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1, 5000000000000002, -2, 2]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r_python = [math.floor(n + 0.5) for n in a]\n",
    "r_python"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Numpy float64"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "## Note that `%precision` magic also modifies numpy's precision.\n",
    "np.set_printoptions(precision=17, floatmode='fixed', suppress=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 1.00000000000000000e+00,  5.00000000000000200e+15,\n",
       "       -2.00000000000000000e+00,  2.00000000000000000e+00])"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aa = np.array(a)\n",
    "r_numpy = np.floor(aa + 0.5)\n",
    "r_numpy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Python DECIMALS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0.00000000000000000,\n",
       " 5000000000000001.00000000000000000,\n",
       " -2.00000000000000000,\n",
       " 2.00000000000000000]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from decimal import Decimal as D, ROUND_HALF_UP\n",
    "\n",
    "def round_dec(n):\n",
    "    decimals = D('1')\n",
    "    n = D(n).quantize(decimals, rounding=ROUND_HALF_UP)\n",
    "    return float(n)\n",
    "r_decimals = [round_dec(n) for n in a]\n",
    "r_decimals"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Matlab (Octave)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%script octave --out r_octave\n",
    "format long\n",
    "a = [0.49999999999999994, 5000000000000001.0, -2.4, 2.4];\n",
    "%a = [0.499999999999994, 500000000000001.0, -2.4, 2.4];  % Access input\n",
    "disp(sprintf(repmat('%0.17g ', 1, length(a)), floor(a + 0.5)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1.00000000000000000,\n",
       " 5000000000000002.00000000000000000,\n",
       " -2.00000000000000000,\n",
       " 2.00000000000000000]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r_matlab = [float(n) for n in r_octave.strip().split(' ')]\n",
    "r_matlab"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## .Net Core C&#35;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "tmpfile = f\"/tmp/rounding-{os.getpid()}.csx\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing /tmp/rounding-16121.csx\n"
     ]
    }
   ],
   "source": [
    "%%writefile $tmpfile\n",
    "double [] a = {0.49999999999999994, 5000000000000001.0, -2.4, 2.4};\n",
    "//double [] a = {0.499999999999994, 500000000000001.0, -2.4, 2.4};  // Access input\n",
    "for(int i = 0; i < a.GetLength(0); i++)\n",
    "    Console.WriteLine(Math.Floor(a[i] + 0.5).ToString(\"F\"));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[1.00000000000000000,\n",
       " 5000000000000000.00000000000000000,\n",
       " -2.00000000000000000,\n",
       " 2.00000000000000000]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "r_dotnet = !dotnet-script $tmpfile\n",
    "!rm $tmpfile\n",
    "r_dotnet = [float(n) for n in r_dotnet]\n",
    "r_dotnet"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COMPARE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>access</th>\n",
       "      <th>python</th>\n",
       "      <th>numpy</th>\n",
       "      <th>decimals</th>\n",
       "      <th>matlab</th>\n",
       "      <th>dotnet</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5.000000e-01</th>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5.000000e+15</th>\n",
       "      <td>5.000000e+14</td>\n",
       "      <td>5.000000e+15</td>\n",
       "      <td>5.000000e+15</td>\n",
       "      <td>5.000000e+15</td>\n",
       "      <td>5.000000e+15</td>\n",
       "      <td>5.000000e+15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-2.400000e+00</th>\n",
       "      <td>-2.000000e+00</td>\n",
       "      <td>-2.000000e+00</td>\n",
       "      <td>-2.000000e+00</td>\n",
       "      <td>-2.000000e+00</td>\n",
       "      <td>-2.000000e+00</td>\n",
       "      <td>-2.000000e+00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.400000e+00</th>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>2.000000e+00</td>\n",
       "      <td>2.000000e+00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     access        python         numpy      decimals  \\\n",
       " 5.000000e-01  0.000000e+00  1.000000e+00  1.000000e+00  0.000000e+00   \n",
       " 5.000000e+15  5.000000e+14  5.000000e+15  5.000000e+15  5.000000e+15   \n",
       "-2.400000e+00 -2.000000e+00 -2.000000e+00 -2.000000e+00 -2.000000e+00   \n",
       " 2.400000e+00  2.000000e+00  2.000000e+00  2.000000e+00  2.000000e+00   \n",
       "\n",
       "                     matlab        dotnet  \n",
       " 5.000000e-01  1.000000e+00  1.000000e+00  \n",
       " 5.000000e+15  5.000000e+15  5.000000e+15  \n",
       "-2.400000e+00 -2.000000e+00 -2.000000e+00  \n",
       " 2.400000e+00  2.000000e+00  2.000000e+00  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame(np.array([r_access, r_python, r_numpy, r_decimals, r_matlab, r_dotnet]).T,\n",
    "                  columns=\"access python numpy decimals matlab dotnet\".split(),\n",
    "                 index=a)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Comments:\n",
    "- All implementations but \"decimals\" fail to reproduce *access* on number 0.49999999999999994. <br>\n",
    "  - *Access* works this way because it accepts only \"decimals(15)\" as input (not IEEE 754 doubles that require 17 decimals).\n",
    "  - If same input as *access* is given to all impls (by uncommenting lines), then thay all agree.\n",
    "- For all the rest numbers, all implementations work fine with `floor(n + 0.5)` function."
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}