如何使用Pandas的explode、crosstab高效统计700万行大型数据集的元素出现次数

Problem Context #

I'm working with a massive dataset that has 7 million rows and 2 columns. The second column, listElements, stores lists of individual elements and element groups (like [apple, peer]). Testing the first 10k rows already revealed over 27k unique elements/groups, and I expect the full dataset will have over 100k unique entries.

Sample Data & Goal #

Here's a small sample of my DataFrame:

import pandas as pd

df = pd.DataFrame(columns=["id", "listElements"])
df = df.append([
    {"id": 1, "listElements": ["apple","peer", "[apple, peer]", "banana", "chocolate", "[chocolate, apple]"]},
    {"id": 2, "listElements": ["ginger","peer", "[ginger, sugar]", "tofu", "[tofu, veggie]", "chocolate", ]},
    {"id": 3, "listElements": ["steak","beef", "[beef, potatoes]", "banana", ]}
])

My end goal is to:

• Count how many times each element/group appears across the entire dataset
• Keep track of which ids each element/group is associated with for later analysis

Current Approach #

Right now, I'm using explode on the listElements column, then generating a cross-tab to associate elements with their ids:

df2 = df['listElements'].explode()
df = df[['id',]].join(pd.crosstab(df2.index, df2, colnames=['listElements']))

The output looks like this (truncated):

id  [apple, peer]  [beef, potatoes]  [chocolate, apple]  [ginger, sugar]  ...  chocolate  ginger  peer  steak  tofu
0   1              1                 0                   1                 0  ...          1       0     1      0     0
1   2              0                 0                   0                 1  ...          1       1     1      0     1
2   3              0                 1                   0                 0  ...          0       0     0      1     0

I plan to aggregate this to get counts and associated ids, but I'm worried about scaling.

Bottlenecks I'm Facing #

With 7 million rows and 100k+ unique elements/groups, this approach will almost certainly cause memory exhaustion or take an unreasonable amount of time. The cross-tab creates a huge sparse matrix, which is inefficient for this use case.

Questions #

1. Is there a more direct, efficient way to achieve my goal? Are there redundant steps in my current approach that I can optimize?
2. How can I avoid memory issues and improve performance? Would encoding elements into numerical values, processing in batches, or other strategies help here?

Solutions & Recommendations #

Let's break this down into practical, scalable steps that avoid the memory trap of your current cross-tab approach.

1. Ditch the Cross-Tab: Use a Counter + ID Mapping #

Your current method creates a wide DataFrame with 100k+ columns, which is the main memory hog. Instead, we can work with a long-form structure and use Python's built-in collections.defaultdict to track counts and associated IDs efficiently.

Here's a streamlined approach:

from collections import defaultdict
import pandas as pd

# Initialize two dictionaries: one for counts, one for id lists
element_counts = defaultdict(int)
element_to_ids = defaultdict(set)  # Using sets to avoid duplicate IDs per element

# Iterate through each row (memory-friendly if using chunking later)
for _, row in df.iterrows():
    elem_list = row['listElements']
    current_id = row['id']
    for elem in elem_list:
        element_counts[elem] += 1
        element_to_ids[elem].add(current_id)

# Convert to a final DataFrame for analysis
result_df = pd.DataFrame({
    'element': element_counts.keys(),
    'count': element_counts.values(),
    'associated_ids': [list(ids) for ids in element_to_ids.values()]
})

This is way more memory-efficient because we're only storing each unique element once, plus its count and ID set. No giant sparse matrix here.

2. Batch Processing for Extreme Scale #

If even iterating through 7 million rows at once is too much, split the dataset into chunks using pandas.read_csv (if your data is in a CSV) with the chunksize parameter:

# Example if reading from CSV
chunk_size = 100000  # Adjust based on your memory
element_counts = defaultdict(int)
element_to_ids = defaultdict(set)

for chunk in pd.read_csv('your_data.csv', chunksize=chunk_size):
    for _, row in chunk.iterrows():
        elem_list = row['listElements']
        # Note: If your listElements are stored as strings (not actual lists), parse them first with ast.literal_eval
        current_id = row['id']
        for elem in elem_list:
            element_counts[elem] += 1
            element_to_ids[elem].add(current_id)

# Build result_df as before

This way, you only load a portion of the dataset into memory at a time.

3. Encode Elements for Even More Efficiency #

If 100k unique elements are still pushing memory limits, you can map each element to a numerical ID first. This reduces the memory footprint of the dictionaries since integers take less space than strings:

from collections import defaultdict
import pandas as pd
from itertools import chain

# First, collect all unique elements (can do this in batches too)
all_elements = set(chain.from_iterable(df['listElements']))
elem_to_code = {elem: i for i, elem in enumerate(all_elements)}
code_to_elem = {i: elem for elem, i in elem_to_code.items()}

# Now use codes instead of strings in the dictionaries
code_counts = defaultdict(int)
code_to_ids = defaultdict(set)

for _, row in df.iterrows():
    elem_list = row['listElements']
    current_id = row['id']
    for elem in elem_list:
        code = elem_to_code[elem]
        code_counts[code] += 1
        code_to_ids[code].add(current_id)

# Map back to original elements for the final DataFrame
result_df = pd.DataFrame({
    'element': [code_to_elem[code] for code in code_counts.keys()],
    'count': code_counts.values(),
    'associated_ids': [list(ids) for ids in code_to_ids.values()]
})

This is especially useful if your element strings are long (like complex group names).

4. Alternative: Use Pandas' explode + groupby (Better Than Cross-Tab) #

If you prefer staying within pandas, using explode followed by groupby is way more efficient than cross-tab:

# Explode the list column
exploded_df = df.explode('listElements')

# Group by element to get counts and list of IDs
result_df = exploded_df.groupby('listElements').agg(
    count=('id', 'size'),
    associated_ids=('id', lambda x: list(set(x)))  # Use set to avoid duplicates, then convert to list
).reset_index().rename(columns={'listElements': 'element'})

This creates a long-form DataFrame instead of a wide one, which is far more memory-efficient. For 7 million rows, exploded_df will be ~7M * average list length rows, but pandas handles this better than a wide 100k-column DataFrame.

Key Takeaways #

• Avoid wide DataFrames at all costs: Cross-tab creates a sparse matrix that's terrible for memory with 100k+ columns.
• Use dictionaries or groupby: These approaches focus on storing only necessary data (unique elements + their metadata).
• Batch process if needed: Chunking your dataset prevents loading everything into memory at once.
• Encode elements: Numerical IDs reduce memory usage for string-heavy data.

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