如何将Pandas中的列表列转换为基于唯一值的稀疏DataFrame

Hey there! Let's break this down step by step—since you're dealing with 60k IDs and 100k unique values, memory efficiency is absolutely critical here. Using a value-to-short-ID mapping is a smart call, and we'll pair that with sparse matrix tools to avoid blowing up your RAM.

Step 1: Create a Value-to-Index Mapping #

First, we'll map each unique value to a small integer (instead of keeping string column names, which eat up memory). We'll also keep a reverse mapping for later lookup if needed:

import pandas as pd
import numpy as np
from scipy.sparse import coo_matrix

# Your existing code to get unique values
l = df.values.tolist()
flat_set = {item for sublist in l for item in sublist}

# Map unique values to integer indices (sorted for consistency)
value_to_idx = {val: np.int32(idx) for idx, val in enumerate(sorted(flat_set))}
# Reverse mapping to get original values from indices (store this for later use)
idx_to_value = {idx: val for val, idx in value_to_idx.items()}

Using np.int32 instead of default integers cuts down memory usage since we don't need the full range of int64 for 100k values.

Step 2: Build a Sparse Matrix #

Instead of creating a dense DataFrame (which would require 60k × 100k = 6 billion entries—totally unfeasible), we'll use a COO (Coordinate) sparse matrix from SciPy. This only stores the positions of non-zero values (which are the entries where an ID has a specific value):

# Map row IDs to integer indices (since sparse matrices use integer row positions)
row_ids = df.index.tolist()
row_to_idx = {row_id: np.int32(idx) for idx, row_id in enumerate(row_ids)}

# Collect coordinates for non-zero entries
rows = []
cols = []

# Iterate through each row in your DataFrame
for row_id, value_list in df.itertuples(name=None):
    row_idx = row_to_idx[row_id]
    # Your DataFrame has lists nested inside each cell, so we access value_list[0]
    for val in value_list[0]:
        col_idx = value_to_idx[val]
        rows.append(row_idx)
        cols.append(col_idx)

# Create the sparse matrix (use int8 dtype since we only need 0/1 values)
data = np.ones(len(rows), dtype=np.int8)
sparse_mat = coo_matrix((data, (rows, cols)), 
                        shape=(len(row_ids), len(value_to_idx)),
                        dtype=np.int8)

COO matrices are perfect for building sparse structures efficiently—they only store the row, column, and value of each non-zero entry, which is way lighter than a dense array.

Step 3: Convert to Pandas Sparse DataFrame #

Now we'll turn the SciPy sparse matrix into a Pandas Sparse DataFrame, keeping your original row IDs and using the integer column indices (to save memory):

# Convert sparse matrix to Pandas Sparse DataFrame
sparse_df = pd.DataFrame.sparse.from_spmatrix(
    sparse_mat,
    index=row_ids,  # Keep your original row IDs
    columns=list(value_to_idx.values())  # Use integer column indices
)

Why This Saves Memory: #

• Integer column indices take far less space than string column names (each string is an object, while integers are compact numeric types).
• Pandas' sparse storage only keeps track of non-zero values, so empty entries don't take up space.

Step 4: Verify and Use the Sparse DataFrame #

To check if everything works, you can look up the original values for a row using the reverse mapping:

# Example: Get all original values for 'id1'
id1_non_zero_cols = sparse_df.loc['id1'][sparse_df.loc['id1'] != 0].index.tolist()
original_values = [idx_to_value[col] for col in id1_non_zero_cols]
print(original_values)  # Should output ['a', 'b', 'c', 'd']

Bonus: Additional Memory Tweaks #

• If you don't need to preserve the order of unique values, skip sorting flat_set in Step 1 (saves a tiny bit of time).
• Use dtype=np.bool_ for the sparse matrix if you only care about presence/absence (even smaller than int8).

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