新手求助：Pandas合并时出现无法分配208GiB内存的错误

问题场景 #

作为编程新手，运行以下Pandas代码时触发内存错误：

play_count_with_title = pd.merge(df_count, df_small[['song_id', 'title', 'release']], on = 'song_id' )

final_ratings  = pd.merge(play_count_with_title, df_small[['song_id', 'artist_name']], on = 'song_id' )

final_ratings

报错信息 #

Unable to allocate 208. GiB for an array with shape (27939587241,) and data type int64

调用栈 #

File ~\anaconda3\lib\site-packages\pandas\core\reshape\merge.py:124, in merge(left, right, how, on, left_on, right_on, left_index, right_index, sort, suffixes, copy, indicator, validate)
     93 @Substitution("\nleft : DataFrame or named Series")
     94 @Appender(_merge_doc, indents=0)
     95 def merge(
   (...)
    108     validate: str | None = None,
    109 ) -> DataFrame:
    110     op = _MergeOperation(
    111         left,
    112         right,
   (...)
    122         validate=validate,
    123     )
--> 124     return op.get_result(copy=copy)

File ~\anaconda3\lib\site-packages\pandas\core\reshape\merge.py:773, in _MergeOperation.get_result(self, copy)
    770 if self.indicator:
    771     self.left, self.right = self._indicator_pre_merge(self.left, self.right)
--> 773 join_index, left_indexer, right_indexer = self._get_join_info()
    775 result = self._reindex_and_concat(
    776     join_index, left_indexer, right_indexer, copy=copy
    777 )
    778 result = result.__finalize__(self, method=self._merge_type)

File ~\anaconda3\lib\site-packages\pandas\core\reshape\merge.py:1026, in _MergeOperation._get_join_info(self)
   1022     join_index, right_indexer, left_indexer = _left_join_on_index(
   1023         right_ax, left_ax, self.right_join_keys, sort=self.sort
   1024     )
   1025 else:
--> 1026     (left_indexer, right_indexer) = self._get_join_indexers()
   1028     if self.right_index:
   1029         if len(self.left) > 0:

File ~\anaconda3\lib\site-packages\pandas\core\reshape\merge.py:1000, in _MergeOperation._get_join_indexers(self)
    998 def _get_join_indexers(self) -> tuple[npt.NDArray[np.intp], npt.NDArray[np.intp]]:
    999     """return the join indexers"""
--> 1000     return get_join_indexers(
   1001         self.left_join_keys, self.right_join_keys, sort=self.sort, how=self.how
   1002     )

File ~\anaconda3\lib\site-packages\pandas\core\reshape\merge.py:1610, in get_join_indexers(left_keys, right_keys, sort, how, **kwargs)
   1600 join_func = {
   1601     "inner": libjoin.inner_join,
   1602     "left": libjoin.left_outer_join,
   (...)
   1606     "outer": libjoin.full_outer_join,
   1607 }[how]
   1609 # error: Cannot call function of unknown type
--> 1610 return join_func(lkey, rkey, count, **kwargs)

File ~\anaconda3\lib\site-packages\pandas\_libs\join.pyx:48, in pandas._libs.join.inner_join()

原因分析 #

这个错误说明合并操作试图创建一个208GB大小的数组，远超系统可用内存。问题根源是：

• df_count或df_small中song_id存在大量重复值，导致合并时产生了笛卡尔积（每一条左表记录和所有匹配的右表记录配对，最终数据量爆炸）。
• 两次合并的中间DataFrame额外占用了内存，加剧了内存压力。

解决方法 #

1. 先检查数据重复情况 #

先确认song_id的重复程度，避免不必要的笛卡尔积：

# 查看df_count中song_id的重复数
print(df_count['song_id'].value_counts().head())
# 查看df_small中song_id的重复数
print(df_small['song_id'].value_counts().head())

如果song_id在某张表中重复，先做去重处理：

# 保留df_small中song_id的唯一值
df_small_unique = df_small.drop_duplicates(subset=['song_id'])

2. 合并为单次操作 #

把两次merge合并成一次，减少中间DataFrame的内存占用：

# 一次性合并所有需要的字段
final_ratings = pd.merge(
    df_count,
    df_small[['song_id', 'title', 'release', 'artist_name']],
    on='song_id'
)

3. 优化内存使用 #

• 把song_id设为category类型，减少内存占用：

df_count['song_id'] = df_count['song_id'].astype('category')
df_small['song_id'] = df_small['song_id'].astype('category')

• 使用validate参数提前检查合并类型，避免多对多导致的笛卡尔积：

# 验证合并为一对多或一对一（根据实际情况选择）
final_ratings = pd.merge(
    df_count,
    df_small[['song_id', 'title', 'release', 'artist_name']],
    on='song_id',
    validate='m:1'  # 左表多记录对应右表单记录
)

4. 分块处理（超大数据量场景） #

如果数据实在太大，无法一次性加载，用chunksize分块读取并合并：

final_ratings = []
# 分块读取df_count
for chunk in pd.read_csv('df_count文件路径.csv', chunksize=100000):
    # 合并当前块和df_small
    merged_chunk = pd.merge(chunk, df_small[['song_id', 'title', 'release', 'artist_name']], on='song_id')
    final_ratings.append(merged_chunk)
# 合并所有块
final_ratings = pd.concat(final_ratings)

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