Pandas透视表内存错误咨询（系统内存充足）

Let's break down why you're hitting this memory limit even with 120GB of RAM, then walk through practical, actionable fixes to resolve the issue.

Why the Memory Error Occurs #

Your original DataFrame is only ~337MB, but pivoting it into 10800 columns causes memory usage to skyrocket for two critical reasons:

1. Sparse Data Expansion: Most combinations of id, idf, and ch are likely missing from your raw data. Pandas' default pivot_table fills these gaps with NaN (or 0 if you specify fill_value), creating a dense table where every possible cell occupies memory—even empty ones. For example, if you have 4 million unique ids, that's 4M × 10800 = 43.2 billion cells. Even storing each as a 4-byte integer would take ~172GB, which already exceeds your RAM (and that's before accounting for temporary objects created during the pivot process).
2. Data Type Inflation: Your original hr column uses a compact uint8 type, but pivot table count values default to int64 (8 bytes per cell). This alone multiplies each cell's memory footprint by 8x compared to your original data.

Actionable Solutions #

1. Use Sparse Data Structures #

Pandas supports sparse data types that only store non-zero/non-null values, which can drastically cut memory usage for pivot tables with lots of empty cells. Try this:

# First compute grouped counts, then unstack with sparse support
counts = df.groupby(['id', 'idf', 'ch']).size()
pivot_sparse = counts.unstack(level=['idf', 'ch'], fill_value=0).astype(pd.SparseDtype("uint32", fill_value=0))

# Check reduced memory usage
print(pivot_sparse.memory_usage(deep=True).sum() / 1024**3)  # Output in GB

This will only store cells where the count is non-zero, potentially reducing memory usage by orders of magnitude if your data is sparse.

2. Downcast to Smaller Numeric Types #

If your count values don't need 64-bit integers, downcast to a smaller type like uint32 (max value: 4,294,967,295) or uint16 (max: 65,535) depending on your data's range:

# Create pivot table first, then downcast
pivot_df = df.pivot_table(index='id', columns=['idf','ch'], aggfunc='count', fill_value=0)
pivot_df = pivot_df.apply(pd.to_numeric, downcast='unsigned')

This cuts each cell's memory footprint from 8 bytes to 2-4 bytes, reducing total usage by 50-75%.

3. Process in Chunks #

Split your data into smaller chunks by id, pivot each chunk, then combine the results. This avoids loading the entire pivot table into memory at once:

# Split unique ids into manageable chunks
unique_ids = df['id'].unique()
chunk_size = 100000  # Adjust based on your available RAM
chunks = [unique_ids[i:i+chunk_size] for i in range(0, len(unique_ids), chunk_size)]

# Process each chunk individually
pivot_chunks = []
for chunk in chunks:
    df_chunk = df[df['id'].isin(chunk)]
    pivot_chunk = df_chunk.pivot_table(index='id', columns=['idf','ch'], aggfunc='count', fill_value=0)
    pivot_chunks.append(pivot_chunk)

# Combine all chunks into the final pivot table
final_pivot = pd.concat(pivot_chunks)

4. Use Dask for Out-of-Core Processing #

If even chunking isn't enough, Dask handles datasets larger than RAM by parallelizing operations across chunks automatically:

import dask.dataframe as dd

# Convert pandas DataFrame to Dask DataFrame
ddf = dd.from_pandas(df, npartitions=8)  # Adjust partitions based on your CPU cores

# Compute pivot table without loading everything into memory
pivot_dask = ddf.crosstab(index='id', columns=['idf', 'ch'], dropna=False).compute()

Dask manages memory by processing each partition separately, so you won't hit out-of-memory errors even for massive pivot tables.

5. Filter Low-Frequency Columns #

If you don't need every idf/ch combination, filter out columns with very few occurrences first:

# Calculate global counts for each (idf, ch) pair
col_counts = df.groupby(['idf', 'ch']).size()
# Keep only pairs with at least N occurrences (adjust N to fit your needs)
keep_cols = col_counts[col_counts >= 10].index

# Filter the original DataFrame before pivoting
df_filtered = df[df.set_index(['idf', 'ch']).index.isin(keep_cols)]
# Pivot the filtered data to reduce total columns
pivot_filtered = df_filtered.pivot_table(index='id', columns=['idf','ch'], aggfunc='count')

This reduces the total number of columns, directly lowering the pivot table's memory footprint.

内容的提问来源于stack exchange，提问作者Nikhil Verma