--- name: fabric-pandas-perf-remediate-364b9144afa2 description: Troubleshoot and optimize pandas performance in Microsoft Fabric Spark notebooks. Use when diagnosing slow pandas operations, toPandas() out-of-memory errors, pandas API on Spark (pyspark.pandas) bottlenecks, DataFrame conversion failures, collect() memory issues, driver memory exhaustion, notebook cell timeouts, or when optimizing pandas workloads for Fabric capacity. Covers pandas vs Spark DataFrame conversion, memory profiling, broadcast joins, shuffle tuning, resource profiles, and Native Execution Engine integration. license: Complete terms in LICENSE.txt --- # Fabric Pandas Performance Troubleshooting Diagnose and resolve pandas-related performance issues in Microsoft Fabric Spark notebooks, including memory exhaustion, slow conversions, and suboptimal pandas API on Spark usage. ## When to Use This Skill - Notebook cells hang or timeout during pandas operations - `toPandas()` fails with OutOfMemoryError or Java heap space errors - `collect()` crashes the driver node - Pandas API on Spark (`pyspark.pandas` / `ps`) runs slower than expected - DataFrame conversion between Spark and pandas causes memory spikes - Notebook kernel restarts unexpectedly during data processing - Large dataset operations exhaust driver memory on Fabric capacity - Need to choose between pandas, Spark DataFrame, or pandas API on Spark ## Prerequisites - Microsoft Fabric workspace with Data Engineering experience - Fabric capacity F2 or higher (F64+ recommended for large datasets) - PySpark notebook with Spark session active - Basic familiarity with pandas and PySpark DataFrames ## Quick Diagnosis ### Symptom-to-Solution Map | Symptom | Likely Cause | Jump To | |---------|-------------|---------| | `toPandas()` OOM error | Dataset too large for driver | [toPandas Optimization](#topandas-optimization) | | Kernel restart during pandas op | Driver memory exhausted | [Driver Memory Tuning](#driver-memory-tuning) | | `pyspark.pandas` slower than native pandas | Spark overhead on small data | [Right-Size Your Approach](#right-size-your-approach) | | Slow groupby/merge in pandas API on Spark | Excessive shuffling | [Shuffle Optimization](#shuffle-optimization) | | Cell timeout on DataFrame conversion | Large collect to driver | [Incremental Processing](#incremental-processing) | | `ArrowInvalid` or conversion errors | Schema mismatch / nulls | [Arrow Conversion Fixes](#arrow-conversion-fixes) | | High memory but slow pandas operations | GC pressure / fragmentation | [Memory Profiling](#memory-profiling) | ## Right-Size Your Approach **Critical Decision**: Choose the right DataFrame API for your data size and workload. ``` Dataset Size Decision Tree: ───────────────────────────────────────────────────────── < 100 MB → Native pandas (pd.DataFrame) 100 MB - 1 GB → pandas API on Spark (ps.DataFrame) > 1 GB → PySpark DataFrame (spark.DataFrame) > 10 GB → PySpark + partitioning + Delta optimization Mixed workload? → Process in Spark, convert final aggregation to pandas Visualization? → Aggregate in Spark first, toPandas() on summary only ML feature eng? → Spark for transforms, pandas for final model input ``` ### API Comparison | Operation | Native pandas | pandas API on Spark | PySpark DataFrame | |-----------|--------------|--------------------|--------------------| | Memory model | Single-node (driver) | Distributed | Distributed | | Max practical size | ~2-4 GB | 10s-100s GB | TB+ | | Startup overhead | None | Spark session | Spark session | | groupby speed (small) | Fast | Slower (shuffle) | Slower (shuffle) | | groupby speed (large) | OOM risk | Fast | Fast | | Interop with Spark | `.toPandas()` | `.to_spark()` | Native | ## toPandas Optimization ### Problem `toPandas()` collects the **entire distributed DataFrame** to the single driver node. This is the #1 cause of OOM in Fabric notebooks. ### Solutions (Progressive) **1. Reduce data BEFORE conversion** ```python # BAD - converts entire table pdf = spark_df.toPandas() # GOOD - filter and select first pdf = (spark_df .filter("date >= '2024-01-01'") .select("customer_id", "revenue", "region") .toPandas()) ``` **2. Aggregate in Spark, convert summary** ```python # BAD - convert raw data then aggregate in pandas pdf = spark_df.toPandas() result = pdf.groupby('region')['revenue'].sum() # GOOD - aggregate in Spark first summary = spark_df.groupBy("region").agg(F.sum("revenue").alias("total_revenue")) pdf = summary.toPandas() # Only converting small aggregated result ``` **3. Enable Apache Arrow for faster conversion** ```python # Enable Arrow-based columnar transfer (3-100x faster) spark.conf.set("spark.sql.execution.arrow.pyspark.enabled", "true") spark.conf.set("spark.sql.execution.arrow.pyspark.fallback.enabled", "true") # Now toPandas() uses Arrow columnar format pdf = spark_df.toPandas() ``` **4. Use sampling for exploration** ```python # Sample before converting (for EDA/visualization) pdf = spark_df.sample(fraction=0.01, seed=42).toPandas() # Or limit rows pdf = spark_df.limit(100000).toPandas() ``` **5. Chunk large conversions** ```python # Process in chunks using partitioning def process_in_chunks(spark_df, chunk_col="date", process_fn=None): """Convert Spark DF to pandas in manageable chunks.""" chunks = [row[chunk_col] for row in spark_df.select(chunk_col).distinct().collect()] results = [] for chunk_val in chunks: chunk_pdf = spark_df.filter(F.col(chunk_col) == chunk_val).toPandas() if process_fn: chunk_pdf = process_fn(chunk_pdf) results.append(chunk_pdf) return pd.concat(results, ignore_index=True) ``` ## Driver Memory Tuning ### Fabric Driver Memory by Node Size | Node Size | vCores | Memory | Recommended Max toPandas() | |-----------|--------|--------|---------------------------| | Small | 4 | 32 GB | ~4-6 GB | | Medium | 8 | 64 GB | ~10-12 GB | | Large | 16 | 128 GB | ~20-25 GB | | X-Large | 32 | 256 GB | ~40-50 GB | **Rule of thumb**: `toPandas()` safe limit ≈ 15-20% of total driver memory (pandas creates copies during operations). ### Configure Driver Memory ```python # Check current driver memory print(f"Driver memory: {spark.conf.get('spark.driver.memory', 'default')}") # Set via environment Spark properties (before session starts) # In Fabric Environment > Spark properties: # spark.driver.memory = 28g (for Medium nodes) # Or override in notebook (must be first cell) %%configure { "driverMemory": "28g", "driverCores": 8 } ``` ### Resource Profile Selection for Pandas Workloads ```python # For notebooks heavy on pandas operations (read-heavy pattern) spark.conf.set("spark.fabric.resourceProfile", "readHeavyForSpark") # Key settings this enables: # - spark.databricks.delta.optimizeWrite.enabled = true # - Optimized read paths for Delta tables ``` ## Shuffle Optimization ### Tune for pandas API on Spark Operations ```python # Reduce shuffle partitions for smaller datasets # Default 200 is too high for datasets < 10 GB spark.conf.set("spark.sql.shuffle.partitions", "auto") # Adaptive (AQE) # Or set explicitly based on data size # Rule: ~128 MB per partition data_size_gb = 5 optimal_partitions = max(1, int(data_size_gb * 1024 / 128)) spark.conf.set("spark.sql.shuffle.partitions", str(optimal_partitions)) # Enable Adaptive Query Execution (on by default in Fabric) spark.conf.set("spark.sql.adaptive.enabled", "true") spark.conf.set("spark.sql.adaptive.coalescePartitions.enabled", "true") ``` ### Broadcast Join Optimization ```python # Increase broadcast threshold for pandas API on Spark joins # Default: 10 MB - increase for medium lookup tables spark.conf.set("spark.sql.autoBroadcastJoinThreshold", "100m") # 100 MB # Force broadcast for known small DataFrames from pyspark.sql.functions import broadcast result = large_df.join(broadcast(small_lookup_df), "key_col") ``` ### Enable Autotune ```python # Let Fabric auto-optimize shuffle, broadcast, and partition settings spark.conf.set("spark.ms.autotune.enabled", "true") # Autotune requires: # - Runtime 1.1 or 1.2 # - Queries > 15 seconds # - Not in high concurrency mode # - ~20-25 iterations to learn optimal settings ``` ## Arrow Conversion Fixes ### Common Errors and Solutions | Error | Cause | Fix | |-------|-------|-----| | `ArrowInvalid: Could not convert X` | Unsupported type | Cast column before conversion | | `ArrowNotImplementedError` | Nested types | Flatten struct/array columns | | `pyarrow.lib.ArrowMemoryError` | OOM during Arrow transfer | Reduce data size or increase memory | | Null handling mismatch | Pandas NaN vs Spark null | Use `spark.sql.execution.arrow.pyspark.fallback.enabled` | ```python # Fix mixed types before conversion from pyspark.sql.types import StringType, DoubleType spark_df = spark_df.withColumn("mixed_col", F.col("mixed_col").cast(StringType())) # Flatten nested structs spark_df = spark_df.select( "simple_col", F.col("struct_col.field1").alias("field1"), F.col("struct_col.field2").alias("field2") ) # Handle null-heavy columns spark_df = spark_df.fillna({"numeric_col": 0, "string_col": ""}) ``` ## Incremental Processing ### Pattern: Spark Processing with Pandas Finish ```python import pyspark.sql.functions as F import pandas as pd # Step 1: Heavy lifting in Spark (distributed) aggregated = (spark_df .filter(F.col("status") == "active") .groupBy("category", "month") .agg( F.sum("amount").alias("total"), F.count("*").alias("cnt"), F.avg("score").alias("avg_score") )) # Step 2: Verify size before conversion row_count = aggregated.count() print(f"Rows to convert: {row_count:,}") assert row_count < 1_000_000, f"Too many rows ({row_count:,}) for toPandas()" # Step 3: Convert small result to pandas for visualization/export pdf = aggregated.toPandas() # Step 4: Pandas-specific operations (plotting, styling, etc.) pivot = pdf.pivot_table(index='category', columns='month', values='total') ``` ## Memory Profiling ### Monitor Driver Memory in Notebook ```python import os, psutil def check_memory(): """Report current driver memory usage.""" process = psutil.Process(os.getpid()) mem_info = process.memory_info() print(f"RSS Memory: {mem_info.rss / 1024**3:.2f} GB") print(f"VMS Memory: {mem_info.vms / 1024**3:.2f} GB") # System-wide sys_mem = psutil.virtual_memory() print(f"System Used: {sys_mem.used / 1024**3:.2f} / {sys_mem.total / 1024**3:.2f} GB ({sys_mem.percent}%)") # Call before/after pandas operations check_memory() pdf = spark_df.toPandas() check_memory() ``` ### Reduce pandas Memory Footprint ```python def optimize_pandas_dtypes(df): """Downcast pandas DataFrame dtypes to reduce memory.""" for col in df.select_dtypes(include=['int64']).columns: df[col] = pd.to_numeric(df[col], downcast='integer') for col in df.select_dtypes(include=['float64']).columns: df[col] = pd.to_numeric(df[col], downcast='float') for col in df.select_dtypes(include=['object']).columns: if df[col].nunique() / len(df) < 0.5: # Categorical threshold df[col] = df[col].astype('category') return df pdf = optimize_pandas_dtypes(pdf) print(f"Memory after optimization: {pdf.memory_usage(deep=True).sum() / 1024**2:.1f} MB") ``` ## pandas API on Spark Best Practices ### Use `pyspark.pandas` Instead of Conversion ```python import pyspark.pandas as ps # Read directly as pandas-on-Spark DataFrame psdf = ps.read_delta("Tables/my_table") # Or convert from Spark DataFrame (lazy, no collect) psdf = spark_df.pandas_api() # Operations run distributed (no driver memory pressure) result = psdf.groupby("region")["revenue"].sum() # Convert to pandas only for final small result pdf = result.to_pandas() ``` ### Common Pitfalls ```python # BAD: iterrows/itertuples on pandas-on-Spark (extremely slow) for idx, row in psdf.iterrows(): # Anti-pattern! process(row) # GOOD: Use vectorized operations psdf["new_col"] = psdf["col_a"] * psdf["col_b"] # BAD: apply with Python UDF (serialization overhead) psdf["result"] = psdf["col"].apply(lambda x: complex_fn(x)) # GOOD: Use Spark-native functions or pandas_udf from pyspark.sql.functions import pandas_udf @pandas_udf("double") def optimized_fn(series: pd.Series) -> pd.Series: return series * 2 + 1 ``` ## Troubleshooting Checklist 1. **Check data size** before any `toPandas()` / `collect()` call 2. **Enable Arrow** transfer: `spark.sql.execution.arrow.pyspark.enabled = true` 3. **Filter/aggregate** in Spark before converting to pandas 4. **Match node size** to workload (Medium 64 GB minimum for pandas-heavy notebooks) 5. **Use pandas API on Spark** for distributed pandas-like operations 6. **Monitor memory** with `psutil` before/after conversions 7. **Set resource profile** to `readHeavyForSpark` for notebook-heavy workloads 8. **Enable autotune** for automatic shuffle and partition optimization 9. **Downcast dtypes** after conversion to reduce pandas memory footprint 10. **Chunk processing** for datasets that exceed single-node memory ## Automation & Diagnostics Run the [diagnostic script](./scripts/Invoke-PandasDiagnostics.ps1) to collect Spark session configuration, memory settings, and environment details for troubleshooting. See the [detailed reference guide](./references/pandas-performance-deep-dive.md) for advanced patterns including pandas UDFs, Koalas migration, Native Execution Engine integration, and capacity planning formulas. Use the [notebook template](./templates/pandas-performance-template.py) as a starting point for memory-safe pandas workflows in Fabric notebooks. --- Source attribution: PatrickGallucci Original project: https://github.com/PatrickGallucci/fabric-skills License: Apache-2.0 License information: https://www.apache.org/licenses/LICENSE-2.0 Original upstream instructions; downloaded from Mega AI Hub. ## Upstream license notice Apache License Version 2.0, January 2004 http://www.apache.org/licenses/ TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION 1. Definitions. "License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document. "Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License. "Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity. "You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License. "Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files. "Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types. "Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work. "Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof. "Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to the Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution." "Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by the Licensor and subsequently incorporated within the Work. 2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form. 3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed. 4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions: (a) You must give any other recipients of the Work or Derivative Works a copy of this License; and (b) You must cause any modified files to carry prominent notices stating that You changed the files; and (c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and (d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding any notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License. You may add Your own copyright statement to Your modifications and may provide additional or different license terms and conditions for use, reproduction, or distribution of Your modifications, or for any such Derivative Works as a whole, provided Your use, reproduction, and distribution of the Work otherwise complies with the conditions stated in this License. 5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions. 6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file. 7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License. 8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages. 9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability. END OF TERMS AND CONDITIONS APPENDIX: How to apply the Apache License to your work. To apply the Apache License to your work, attach the following boilerplate notice, with the fields enclosed by brackets "[]" replaced with your own identifying information. (Don't include the brackets!) The text should be enclosed in the appropriate comment syntax for the file format. Please also get the specific language for your license from: http://www.apache.org/licenses/LICENSE-2.0 Copyright 2025 Patrick - Fabric Performance Skills Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. # Mega AI Hub resource metadata Creator / publisher: PatrickGallucci Original project: https://github.com/PatrickGallucci/fabric-skills Attribution basis: Upstream repository explicitly recorded in package mirror path Fields of work: Data & Analytics, Development & Engineering, Product Management Roles: Backend Developer, Data Analyst, Data Engineer, ML / AI Engineer, Product Manager Specialties: API, Backend Tools: Pyspark, Microsoft Fabric Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: 38b77c8d689b09150436ab283f4394da7816625f55a6e5217a899ee7388163bc