Skip to content

Instantly share code, notes, and snippets.

@agriyakhetarpal

agriyakhetarpal/benchmark.ipynb

Last active November 18, 2022 09:18
Show Gist options
  • Save agriyakhetarpal/b390d26bdb1038faf830ce58f49946e6 to your computer and use it in GitHub Desktop.
Save agriyakhetarpal/b390d26bdb1038faf830ce58f49946e6 to your computer and use it in GitHub Desktop.
Download ZIP
Benchmark.ipynb
Display the source blob
Display the rendered blob
Raw
benchmark.ipynb
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"name": "Benchmark.ipynb",
"provenance": [],
"collapsed_sections": [
"q8Sfw2LLcYVX",
"5M_J2538cDxW",
"wKUxLZObdelf",
"qQBJpiuB-Mo-",
"oFCJ7rJ--ej9"
],
"include_colab_link": true
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
},
"language_info": {
"name": "python"
}
},
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/agriyakhetarpal/b390d26bdb1038faf830ce58f49946e6/benchmark.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"cell_type": "markdown",
"source": [
"## Benchmarks for the existing implementation"
],
"metadata": {
"id": "q8Sfw2LLcYVX"
}
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "z6afT_iZRFhP",
"outputId": "0de02082-5985-40f2-894c-7ded5894a171"
},
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/\n",
"Collecting xlsxwriter\n",
" Downloading XlsxWriter-3.0.3-py3-none-any.whl (149 kB)\n",
"\u001b[K |████████████████████████████████| 149 kB 7.5 MB/s \n",
"\u001b[?25hInstalling collected packages: xlsxwriter\n",
"Successfully installed xlsxwriter-3.0.3\n"
]
}
],
"source": [
"!pip install xlsxwriter"
]
},
{
"cell_type": "code",
"source": [
"import timeit\n",
"from xlsxwriter.utility import xl_col_to_name"
],
"metadata": {
"id": "Q39gLw9QSICt"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"print(timeit.timeit('''xl_col_to_name(0)''', globals = globals(), number = 100000))\n",
"print(timeit.timeit('''xl_col_to_name(701)''', globals = globals(), number = 100000))\n",
"print(timeit.timeit('''xl_col_to_name(16383)''', globals = globals(), number = 100000))"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "VGLlFkx-a_Jn",
"outputId": "ddb0e209-58d5-49e5-fc12-d58f3d10f5bb"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"0.08029076700000815\n",
"0.1202532089999977\n",
"0.16898207299999513\n"
]
}
]
},
{
"cell_type": "markdown",
"source": [
"## Benchmarks for my implementation **with** the 'alphabets' lookup"
],
"metadata": {
"id": "5M_J2538cDxW"
}
},
{
"cell_type": "code",
"source": [
"# My implementation with the alphabets string lookup\n",
"\n",
"def xl_col_to_name(col, col_abs=False):\n",
" \"\"\"\n",
" Convert a zero indexed column cell reference to a string.\n",
"\n",
" Args:\n",
" col: The cell column. Int.\n",
" col_abs: Optional flag to make the column absolute. Bool.\n",
"\n",
" Returns:\n",
" Column style string.\n",
"\n",
" \"\"\"\n",
" alphabets = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'\n",
" col_num = col\n",
" if col_num < 0:\n",
" warn(\"Col number %d must be >= 0\" % col_num)\n",
" return None\n",
"\n",
" col_abs = '$' if col_abs else ''\n",
"\n",
" # Return the alphabet if the index is less than 26\n",
" if col_num < 26:\n",
" return col_abs + alphabets[col_num]\n",
"\n",
" # If index is greater than or equal to 26\n",
" else:\n",
" # Store the quotient, remainder in a tuple via the divmod() method\n",
" index = divmod(col_num, 26)\n",
"\n",
" # If remainder is 0\n",
" if index[1] == 0:\n",
" # If quotient equals 1, return the alphabet\n",
" if index[0] == 1:\n",
" return col_abs + alphabets[index[0] - 1] + alphabets[index[1]]\n",
" else:\n",
" # If the quotient > 1 then use recursive relation\n",
" return col_abs + xl_col_to_name(index[0] - 1) + alphabets[index[1]]\n",
"\n",
" # If remainder is not 0\n",
" else:\n",
" return col_abs + xl_col_to_name(index[0] - 1) + alphabets[index[1]]"
],
"metadata": {
"id": "u5TlDFcQWnpj"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"print(timeit.timeit('''xl_col_to_name(0)''', globals = globals(), number = 100000))\n",
"print(timeit.timeit('''xl_col_to_name(701)''', globals = globals(), number = 100000))\n",
"print(timeit.timeit('''xl_col_to_name(16383)''', globals = globals(), number = 100000))"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "sdI7b1DaWtSt",
"outputId": "17893364-5fc7-4c21-b19b-b89132864801"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"0.026238966999983404\n",
"0.07187504399996669\n",
"0.12205562200000486\n"
]
}
]
},
{
"cell_type": "markdown",
"source": [
"So that means there's virtually a better time recorded for all the three column numbers, a very slight betterment and unnoticeable for practical implications; but an increase nonetheless"
],
"metadata": {
"id": "rbAf_opfdVkH"
}
},
{
"cell_type": "markdown",
"source": [
"## Benchmarks for my implementation **without** the 'alphabets' lookup"
],
"metadata": {
"id": "wKUxLZObdelf"
}
},
{
"cell_type": "code",
"source": [
"# My implementation without the alphabets string lookup\n",
"\n",
"def xl_col_to_name(col, col_abs=False):\n",
" \"\"\"\n",
" Convert a zero indexed column cell reference to a string.\n",
"\n",
" Args:\n",
" col: The cell column. Int.\n",
" col_abs: Optional flag to make the column absolute. Bool.\n",
"\n",
" Returns:\n",
" Column style string.\n",
"\n",
" \"\"\"\n",
" col_num = col\n",
" if col_num < 0:\n",
" warn(\"Col number %d must be >= 0\" % col_num)\n",
" return None\n",
"\n",
" col_abs = '$' if col_abs else ''\n",
"\n",
" # Return the alphabet if the index is less than 26\n",
" if col_num < 26:\n",
" return col_abs + chr(ord('A') + col_num)\n",
"\n",
" # If index is greater than or equal to 26\n",
" else:\n",
" # Store the quotient, remainder in a tuple via the divmod() method\n",
" index = divmod(col_num, 26)\n",
"\n",
" # If remainder is 0\n",
" if index[1] == 0:\n",
" # If quotient equals 1, return the alphabet\n",
" if index[0] == 1:\n",
" return col_abs + chr(ord('A') + index[0] - 1) + chr(ord('A') + index[1])\n",
" else:\n",
" # If the quotient > 1 then use recursive relation\n",
" return col_abs + xl_col_to_name(index[0] - 1) + chr(ord('A') + index[1])\n",
"\n",
" # If remainder is not 0\n",
" else:\n",
" return col_abs + xl_col_to_name(index[0] - 1) + chr(ord('A') + index[1])"
],
"metadata": {
"id": "U0q70s0Jb2-g"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"print(timeit.timeit('''xl_col_to_name(0)''', globals = globals(), number = 100000))\n",
"print(timeit.timeit('''xl_col_to_name(701)''', globals = globals(), number = 100000))\n",
"print(timeit.timeit('''xl_col_to_name(16383)''', globals = globals(), number = 100000))"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "mrKh1CEri-b7",
"outputId": "cefc24bc-63ba-4a05-a3f7-52fcc48c96e2"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"0.04032889799998429\n",
"0.0908235379999951\n",
"0.16862619299996595\n"
]
}
]
},
{
"cell_type": "markdown",
"source": [
"I think the `chr(ord('A'))` implementation is only marginally faster for column name `XFD` – and results are going quite dynamic and varying every time I run them. However, in most of the results, I see that even though it is slower than the one with the alphabets lookup above, it is faster than the existing implementation."
],
"metadata": {
"id": "Afq2IevAj7QY"
}
},
{
"cell_type": "markdown",
"source": [
"## Benchmarks for my implementation **with** the 'alphabets' lookup and added memoization with functools"
],
"metadata": {
"id": "qQBJpiuB-Mo-"
}
},
{
"cell_type": "code",
"source": [
"# My implementation with the alphabets string lookup\n",
"# updated with memoization with @cache decorators (Python 3.8+ only)\n",
"\n",
"from functools import cache\n",
"from functools import cached_property\n",
"\n",
"@cache\n",
"def xl_col_to_name(col, col_abs=False):\n",
" \"\"\"\n",
" Convert a zero indexed column cell reference to a string.\n",
"\n",
" Args:\n",
" col: The cell column. Int.\n",
" col_abs: Optional flag to make the column absolute. Bool.\n",
"\n",
" Returns:\n",
" Column style string.\n",
"\n",
" \"\"\"\n",
" alphabets = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'\n",
" col_num = col\n",
" if col_num < 0:\n",
" warn(\"Col number %d must be >= 0\" % col_num)\n",
" return None\n",
"\n",
" col_abs = '$' if col_abs else ''\n",
"\n",
" # Return the alphabet if the index is less than 26\n",
" if col_num < 26:\n",
" return col_abs + alphabets[col_num]\n",
"\n",
" # If index is greater than or equal to 26\n",
" else:\n",
" # Store the quotient, remainder in a tuple via the divmod() method\n",
" index = divmod(col_num, 26)\n",
"\n",
" # If remainder is 0\n",
" if index[1] == 0:\n",
" # If quotient equals 1, return the alphabet\n",
" if index[0] == 1:\n",
" return col_abs + alphabets[index[0] - 1] + alphabets[index[1]]\n",
" else:\n",
" # If the quotient > 1 then use recursive relation\n",
" return col_abs + xl_col_to_name(index[0] - 1) + alphabets[index[1]]\n",
"\n",
" # If remainder is not 0\n",
" else:\n",
" return col_abs + xl_col_to_name(index[0] - 1) + alphabets[index[1]]"
],
"metadata": {
"id": "rNJk38o29zlj"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"print(timeit.timeit('''xl_col_to_name(0)''', globals = globals(), number = 100000))\n",
"print(timeit.timeit('''xl_col_to_name(701)''', globals = globals(), number = 100000))\n",
"print(timeit.timeit('''xl_col_to_name(16383)''', globals = globals(), number = 100000))"
],
"metadata": {
"id": "FqhT1rSP-Qru"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"## Benchmarks for my implementation **without** the 'alphabets' lookup and added memoization with functools"
],
"metadata": {
"id": "oFCJ7rJ--ej9"
}
},
{
"cell_type": "code",
"source": [
"# My implementation without the alphabets string lookup\n",
"# updated with memoization with @cache decorators (Python 3.8+ only)\n",
"\n",
"def xl_col_to_name(col, col_abs=False):\n",
" \"\"\"\n",
" Convert a zero indexed column cell reference to a string.\n",
"\n",
" Args:\n",
" col: The cell column. Int.\n",
" col_abs: Optional flag to make the column absolute. Bool.\n",
"\n",
" Returns:\n",
" Column style string.\n",
"\n",
" \"\"\"\n",
" col_num = col\n",
" if col_num < 0:\n",
" warn(\"Col number %d must be >= 0\" % col_num)\n",
" return None\n",
"\n",
" col_abs = '$' if col_abs else ''\n",
"\n",
" # Return the alphabet if the index is less than 26\n",
" if col_num < 26:\n",
" return col_abs + chr(ord('A') + col_num)\n",
"\n",
" # If index is greater than or equal to 26\n",
" else:\n",
" # Store the quotient, remainder in a tuple via the divmod() method\n",
" index = divmod(col_num, 26)\n",
"\n",
" # If remainder is 0\n",
" if index[1] == 0:\n",
" # If quotient equals 1, return the alphabet\n",
" if index[0] == 1:\n",
" return col_abs + chr(ord('A') + index[0] - 1) + chr(ord('A') + index[1])\n",
" else:\n",
" # If the quotient > 1 then use recursive relation\n",
" return col_abs + xl_col_to_name(index[0] - 1) + chr(ord('A') + index[1])\n",
"\n",
" # If remainder is not 0\n",
" else:\n",
" return col_abs + xl_col_to_name(index[0] - 1) + chr(ord('A') + index[1])"
],
"metadata": {
"id": "0JT8Busa-dnh"
},
"execution_count": null,
"outputs": []
},
{
"cell_type": "code",
"source": [
"print(timeit.timeit('''xl_col_to_name(0)''', globals = globals(), number = 100000))\n",
"print(timeit.timeit('''xl_col_to_name(701)''', globals = globals(), number = 100000))\n",
"print(timeit.timeit('''xl_col_to_name(16383)''', globals = globals(), number = 100000))"
],
"metadata": {
"id": "0dv8FGW_-k7N"
},
"execution_count": null,
"outputs": []
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment