| { |
| "cells": [ |
| { |
| "cell_type": "markdown", |
| "id": "4fa81d13", |
| "metadata": {}, |
| "source": [ |
| "# ANSI Migration Guide - Pandas API on Spark\n", |
| "ANSI mode is now on by default for Pandas API on Spark. This guide helps you understand the key behavior differences you’ll see.\n", |
| "In short, with ANSI mode on, Pandas API on Spark behavior matches native pandas in cases where Pandas API on Spark with ANSI off did not." |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "id": "6e1c7952", |
| "metadata": {}, |
| "source": [ |
| "## Behavior Change\n", |
| "### String Number Comparison\n", |
| "**ANSI off:** Spark implicitly casts numbers and strings, so `1` and `'1'` are considered equal.\n", |
| "\n", |
| "**ANSI on:** behaves like pandas, `1 == '1'` is False." |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "id": "69474e28-c1cd-40fe-8ec6-7373b56c4dee", |
| "metadata": {}, |
| "source": [ |
| "Examples are as shown below:\n", |
| "\n", |
| "```python\n", |
| ">>> pdf = pd.DataFrame({\"int\": [1, 2], \"str\": [\"1\", \"2\"]})\n", |
| ">>> psdf = ps.from_pandas(pdf)\n", |
| "\n", |
| "# ANSI on\n", |
| ">>> spark.conf.set(\"spark.sql.ansi.enabled\", True)\n", |
| ">>> psdf[\"int\"] == psdf[\"str\"]\n", |
| "0 False\n", |
| "1 False\n", |
| "dtype: bool\n", |
| "\n", |
| "# ANSI off\n", |
| ">>> spark.conf.set(\"spark.sql.ansi.enabled\", False)\n", |
| ">>> psdf[\"int\"] == psdf[\"str\"]\n", |
| "0 True\n", |
| "1 True\n", |
| "dtype: bool\n", |
| "\n", |
| "# Pandas\n", |
| ">>> pdf[\"int\"] == pdf[\"str\"]\n", |
| "0 False\n", |
| "1 False\n", |
| "dtype: bool\n", |
| "```" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "id": "90a4ea8d", |
| "metadata": {}, |
| "source": [ |
| "### Strict Casting\n", |
| "**ANSI off:** invalid casts (e.g., `'a' → int`) quietly became NULL.\n", |
| "\n", |
| "**ANSI on:** the same casts raise errors." |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "id": "b361febc-4435-4bd1-9ee1-4874413d770c", |
| "metadata": {}, |
| "source": [ |
| "Examples are as shown below:\n", |
| "\n", |
| "```python\n", |
| ">>> pdf = pd.DataFrame({\"str\": [\"a\"]})\n", |
| ">>> psdf = ps.from_pandas(pdf)\n", |
| "\n", |
| "# ANSI on\n", |
| ">>> spark.conf.set(\"spark.sql.ansi.enabled\", True)\n", |
| ">>> psdf[\"str\"].astype(int)\n", |
| "Traceback (most recent call last):\n", |
| "...\n", |
| "pyspark.errors.exceptions.captured.NumberFormatException: [CAST_INVALID_INPUT] ...\n", |
| "\n", |
| "# ANSI off\n", |
| ">>> spark.conf.set(\"spark.sql.ansi.enabled\", False)\n", |
| ">>> psdf[\"str\"].astype(int)\n", |
| "0 NaN\n", |
| "Name: str, dtype: float64\n", |
| "\n", |
| "# Pandas\n", |
| ">>> pdf[\"str\"].astype(int)\n", |
| "Traceback (most recent call last):\n", |
| "...\n", |
| "ValueError: invalid literal for int() with base 10: 'a'\n", |
| "```" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "id": "e11583e2", |
| "metadata": {}, |
| "source": [ |
| "### MultiIndex.to_series Return\n", |
| "**ANSI off:** Each row is returned as an `ArrayType` value, e.g. `[1, red]`.\n", |
| "\n", |
| "**ANSI on:** Each row is returned as a `StructType` value, which appears as a tuple (e.g., `(1, red)`) if the Runtime SQL Configuration `spark.sql.execution.pandas.structHandlingMode` is set to `'row'`. Otherwise, the result may vary depending on whether Arrow is used. See more in the [Spark Runtime SQL Configuration docs](https://spark.apache.org/docs/latest/configuration.html#runtime-sql-configuration)." |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "id": "4671a895-ed40-4bc4-b1bc-fa9fbb86cc18", |
| "metadata": {}, |
| "source": [ |
| "Examples are as shown below:\n", |
| "\n", |
| "```python\n", |
| ">>> arrays = [[1, 2], [\"red\", \"blue\"]]\n", |
| ">>> pidx = pd.MultiIndex.from_arrays(arrays, names=(\"number\", \"color\"))\n", |
| ">>> psidx = ps.from_pandas(pidx)\n", |
| "\n", |
| "# ANSI on\n", |
| ">>> spark.conf.set(\"spark.sql.ansi.enabled\", True)\n", |
| ">>> spark.conf.set(\"spark.sql.execution.pandas.structHandlingMode\", \"row\")\n", |
| ">>> psidx.to_series()\n", |
| "number color\n", |
| "1 red (1, red)\n", |
| "2 blue (2, blue)\n", |
| "dtype: object\n", |
| "\n", |
| "# ANSI off\n", |
| ">>> spark.conf.set(\"spark.sql.ansi.enabled\", False)\n", |
| ">>> psidx.to_series()\n", |
| "number color\n", |
| "1 red [1, red]\n", |
| "2 blue [2, blue]\n", |
| "dtype: object\n", |
| "\n", |
| "# Pandas\n", |
| ">>> pidx.to_series()\n", |
| "number color\n", |
| "1 red (1, red)\n", |
| "2 blue (2, blue)\n", |
| "dtype: object\n", |
| "```" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "id": "a9ceb6cb-3bc4-4c23-b74b-84e60fd64e11", |
| "metadata": {}, |
| "source": [ |
| "### Invalid Mixed-Type Operations\n", |
| "**ANSI off:** Spark implicitly coerces so these operations succeed.\n", |
| "\n", |
| "**ANSI on:** Behaves like pandas, such operations are disallowed and raise errors.\n", |
| "\n", |
| "Operation types that show behavior changes under ANSI mode:\n", |
| "\n", |
| "- **Decimal–Float Arithmetic**: `/`, `//`, `*`, `%` \n", |
| "- **Boolean vs. None**: `|`, `&`, `^`" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "id": "2a8d5705-11ea-458c-8528-c7b1b7c88472", |
| "metadata": {}, |
| "source": [ |
| "Example: Decimal–Float Arithmetic\n", |
| "```python\n", |
| ">>> import decimal\n", |
| ">>> pser = pd.Series([decimal.Decimal(1), decimal.Decimal(2)])\n", |
| ">>> psser = ps.from_pandas(pser)\n", |
| "\n", |
| "# ANSI on\n", |
| ">>> spark.conf.set(\"spark.sql.ansi.enabled\", True)\n", |
| ">>> psser * 0.1\n", |
| "Traceback (most recent call last):\n", |
| "...\n", |
| "TypeError: Multiplication can not be applied to given types.\n", |
| "\n", |
| "# ANSI off\n", |
| ">>> spark.conf.set(\"spark.sql.ansi.enabled\", False)\n", |
| ">>> psser * 0.1\n", |
| "0 0.1\n", |
| "1 0.2\n", |
| "dtype: float64\n", |
| "\n", |
| "# Pandas\n", |
| ">>> pser * 0.1\n", |
| "...\n", |
| "TypeError: unsupported operand type(s) for *: 'decimal.Decimal' and 'float'\n", |
| "```" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "id": "0d2b8268-4b98-4239-95db-5269f9c658d2", |
| "metadata": {}, |
| "source": [ |
| "Example: Boolean vs. None\n", |
| "```python\n", |
| "# ANSI on\n", |
| ">>> spark.conf.set(\"spark.sql.ansi.enabled\", True)\n", |
| ">>> ps.Series([True, False]) | None\n", |
| "Traceback (most recent call last):\n", |
| "...\n", |
| "TypeError: OR can not be applied to given types.\n", |
| "\n", |
| "# ANSI off\n", |
| ">>> spark.conf.set(\"spark.sql.ansi.enabled\", False)\n", |
| ">>> ps.Series([True, False]) | None\n", |
| "0 False \n", |
| "1 False\n", |
| "dtype: bool\n", |
| "\n", |
| "# Pandas\n", |
| ">>> pd.Series([True, False]) | None\n", |
| "...\n", |
| "TypeError: unsupported operand type(s) for |: 'bool' and 'NoneType'\n", |
| "```" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "id": "fe146afd", |
| "metadata": {}, |
| "source": [ |
| "## Related Configurations\n", |
| "\n", |
| "### `spark.sql.ansi.enabled` (Spark config)\n", |
| "- Native Spark setting that controls ANSI mode. \n", |
| "- The most powerful config to control both SQL and pandas API behavior. \n", |
| "- If set to **False**, Spark reverts to the old behavior, and the other configs are not effective.\n", |
| "\n", |
| "### `compute.ansi_mode_support` (Pandas API on Spark option)\n", |
| "- Indicates whether ANSI mode is fully supported. \n", |
| "- Effective only when ANSI is enabled. \n", |
| "- If set to **False**, pandas API on Spark may hit unexpected results or errors. \n", |
| "- Default is **True**.\n", |
| "\n", |
| "### `compute.fail_on_ansi_mode` (Pandas API on Spark option)\n", |
| "- Controls whether pandas API on Spark fails immediately when ANSI mode is enabled. \n", |
| "- Effective only when ANSI is enabled and `compute.ansi_mode_support` is **False**. \n", |
| "- If set to **False**, forces pandas API on Spark to work with the old behavior even when ANSI is enabled." |
| ] |
| } |
| ], |
| "metadata": { |
| "kernelspec": { |
| "display_name": "Python 3 (ipykernel)", |
| "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.11.13" |
| } |
| }, |
| "nbformat": 4, |
| "nbformat_minor": 5 |
| } |
