Pandas实现特定列条件分组合并ID及报错解决

输入输出样例 #

输入数据 #

ID      Band    
|--------|-------|
    A11     1800  
    A18     1800  
    B11     1800  
    B18     1800  
    B21     2400
    C11     1800 
    C18     1800  
    2       2100    
    3       2100        
    A1      2100        
    A2      2100
    A16     2300
    A11     1800        
    A18     1800        
    A26     2600        
    A27     2600 
    9       800

期望输出 #

ID        Band
|--------|--------| 
 A11,A18    1800  
 B11,B18    1800 
 B21        2400
 C11,C18    1800  
 2          2100        
 3          2100    
 A1,A2      2100
 A16        2300 
 A11,A18    1800  
 A26,A27    2600 
 9          800

处理规则 #

• 当Band列值相同时，若ID为纯数字，保留该行不改动（每个数字ID单独一行）
• 若ID以字母开头，按Band + ID首字母分组，合并对应ID值为逗号分隔的字符串

用户报错代码 #

import pandas as pd
import numpy as np
import re
a = pd.read_csv('MasterCellData.csv', encoding= 'unicode_escape', usecols=['antenna types per SDOS', 'Site_ID', 'ID'])
b = pd.read_excel('mapping.xlsx', sheet_name='Sheet1')
c = pd.read_excel('mapping.xlsx', sheet_name='Sheet2')

a = a.dropna(subset=['antenna types per SDOS'])
a['Mapped'] = a['ID'].map(b.set_index('SECTOR CODE')['Operator'])
a['Tech'] = a['ID'].map(b.set_index('SECTOR CODE')['Supported Tech'])
a['Band'] = a['ID'].map(b.set_index('SECTOR CODE')['BAND DESCRIPTION'])
a['Mapped1'] = a['ID'].map(c.set_index('SECTOR CODE')['Operator'])
a['Tech1'] = a['ID'].map(c.set_index('SECTOR CODE')['Supported Tech'])

# Removing duplicate columns
a['Mapped'] = a['Mapped'].fillna(a.pop('Mapped1'))
a['Tech'] = a['Tech'].fillna(a.pop('Tech1'))

# Assigning value to 3G
a['Band'] = a.apply(lambda row: '1800' if str(row['ID']).isdigit() and pd.isnull(row['Band']) else row['Band'], axis=1)
a['Band'] = a.apply(lambda row: '2100' if row['Tech'] == '3G' and pd.isna(row['Band']) else row['Band'], axis=1)


a['Band'] = a['Band'].apply(lambda x: re.findall(r'\d+', str(x)))
a['Band'] = a['Band'].apply(lambda x: ''.join(x) if len(x) > 0 else '')

from string import ascii_uppercase
mapper = {l: i for i,l in enumerate(ascii_uppercase)}
grp1 = a["ID"].str.isnumeric().cumsum() # sequence of alphanum/numbers
grp2 = a["ID"].str[0].map(mapper)       # sequence of letters A, B, ..
out = (
    a.groupby(["Band", grp1, grp2],
               dropna=False, as_index=False, sort=False)["ID"]
        .agg(",".join)[a.columns]
)
out

报错信息 #

C:\Users\guest\AppData\Local\Temp\ipykernel_9660\1921483541.py:4: DtypeWarning: Columns (16) have mixed types. Specify dtype option on import or set low_memory=False.
a = pd.read_csv('MasterCellData.csv', encoding= 'unicode_escape', usecols=['antenna types per SDOS', 'Site_ID', 'ID'])
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
Cell In[12], line 33
     30 grp1 = a["ID"].str.isnumeric().cumsum() # sequence of alphanum/numbers
     31 grp2 = a["ID"].str[0].map(mapper)       # sequence of letters A, B, ..
     32 out = (
---> 33     a.groupby(["Band", grp1, grp2],
     34                dropna=False, as_index=False, sort=False)["ID"]
     35         .agg(",".join)[a.columns]
     36 )
     37 out

File ~\PycharmProjects\pythonProject\venv\lib\site-packages\pandas\core\frame.py:3767, in DataFrame.__getitem__(self, key)
   3765     if is_iterator(key):
   3766         key = list(key)
-> 3767     indexer = self.columns._get_indexer_strict(key, "columns")[1]
   3769 # take() does not accept boolean indexers
   3770 if getattr(indexer, "dtype", None) == bool:

File ~\PycharmProjects\pythonProject\venv\lib\site-packages\pandas\core\indexes\base.py:5876, in Index._get_indexer_strict(self, key, axis_name)
   5873 else:
   5874     keyarr, indexer, new_indexer = self._reindex_non_unique(keyarr)
-> 5876 self._raise_if_missing(keyarr, indexer, axis_name)
   5878 keyarr = self.take(indexer)
   5879 if isinstance(key, Index):
   5880     # GH 42790 - Preserve name from an Index

File ~\PycharmProjects\pythonProject\venv\lib\site-packages\pandas\core\indexes\base.py:5938, in Index._raise_if_missing(self, key, indexer, axis_name)
   5935     raise KeyError(f"None of [{key}] are in the [{axis_name}]")
   5937 not_found = list(ensure_index(key)[missing_mask.nonzero()[0]].unique())
-> 5938 raise KeyError(f"{not_found} not in index")

KeyError: "['antenna types per SDOS', 'Site_ID', 'Mapped', 'Tech'] not in index"
out.to_csv('DDout')

问题分析 #

1. KeyError根源：groupby后仅对ID列执行agg(",".join)，最终结果只包含分组列和合并后的ID列，而你用[a.columns]去索引原DataFrame的所有列（比如antenna types per SDOS、Site_ID等），这些列在聚合结果中不存在，直接触发KeyError。
2. 分组逻辑错误：原代码用grp1 = a["ID"].str.isnumeric().cumsum()会把连续的数字ID归为同一组，不符合“每个数字ID单独保留一行”的要求；同时grp2的映射逻辑没有处理非字母开头的ID，会产生NaN值，导致分组异常。

修正后的代码 #

import pandas as pd
import numpy as np
import re

# 读取数据（消除DtypeWarning）
a = pd.read_csv('MasterCellData.csv', encoding= 'unicode_escape', usecols=['antenna types per SDOS', 'Site_ID', 'ID'], low_memory=False)
b = pd.read_excel('mapping.xlsx', sheet_name='Sheet1')
c = pd.read_excel('mapping.xlsx', sheet_name='Sheet2')

a = a.dropna(subset=['antenna types per SDOS'])
a['Mapped'] = a['ID'].map(b.set_index('SECTOR CODE')['Operator'])
a['Tech'] = a['ID'].map(b.set_index('SECTOR CODE')['Supported Tech'])
a['Band'] = a['ID'].map(b.set_index('SECTOR CODE')['BAND DESCRIPTION'])
a['Mapped1'] = a['ID'].map(c.set_index('SECTOR CODE')['Operator'])
a['Tech1'] = a['ID'].map(c.set_index('SECTOR CODE')['Supported Tech'])

# 合并重复列
a['Mapped'] = a['Mapped'].fillna(a.pop('Mapped1'))
a['Tech'] = a['Tech'].fillna(a.pop('Tech1'))

# Band列补全逻辑
a['Band'] = a.apply(lambda row: '1800' if str(row['ID']).isdigit() and pd.isnull(row['Band']) else row['Band'], axis=1)
a['Band'] = a.apply(lambda row: '2100' if row['Tech'] == '3G' and pd.isna(row['Band']) else row['Band'], axis=1)

# 提取Band中的数字，处理空值
a['Band'] = a['Band'].apply(lambda x: ''.join(re.findall(r'\d+', str(x))) if pd.notna(x) else '')

# 构建分组键：数字ID用索引做唯一键，字母ID用Band+首字母分组
is_numeric = a['ID'].astype(str).str.isnumeric()
a['group_key'] = np.where(is_numeric, a.index, 
                          a['Band'] + '_' + a['ID'].astype(str).str[0].str.upper())

# 分组合并ID，仅保留需要的列
out = a.groupby(['group_key', 'Band'], as_index=False, sort=False).agg(
    ID=('ID', ','.join)
)[['ID', 'Band']]

# 若需保留原DataFrame其他列，可取消下方注释（取每组第一行的对应值）
# out = a.groupby(['group_key', 'Band'], as_index=False, sort=False).agg(
#     ID=('ID', ','.join),
#     **{col: (col, 'first') for col in a.columns if col not in ['ID', 'Band', 'group_key']}
# )

out.to_csv('DDout.csv', index=False)

修正说明 #

1. 解决KeyError：聚合时明确指定需要保留的列，避免索引不存在的列；如需保留原DataFrame其他列，可通过agg的first逻辑保留每组对应值。
2. 修正分组逻辑：
   • 纯数字ID用a.index作为唯一分组键，确保每个数字ID单独成组
   • 字母开头ID用Band + 首字母大写作为分组键，实现同Band同首字母的ID合并
3. 优化数据处理：简化正则提取数字的逻辑，同时处理NaN值；读取CSV时添加low_memory=False消除类型警告

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