如何修改Pandas代码实现字符串列拆分及动态命名（含重复属性）

原始数据集 #

import pandas as pd

df = pd.DataFrame.from_dict({'study_id': {0: 'study1',
  1: 'study2',
  2: 'study3',
  3: 'study4',
  4: 'study5'}, 'fuzzy_market': {0: '[Age: 18-67], [Country of Birth: Austria], [Country of Birth: Germany], [Country: Austria], [Country: Germany], [Language: German]', 1: '[Country: Germany], [Management experience: Yes]', 2: '[Country: United Kingdom], [Language: English]', 3: '[Age: 18-67], [Country of Birth: Austria], [Country of Birth: Germany], [Country: Austria], [Country: Germany], [Language: German]', 4: '[Age: 48-99]'}})

期望输出 #

study_id    Age     Country of Birth    Country         Language      Management experience
study1     18-67    Austria             Austria         German        None
study1     18-67    Germany             Germany         German        None
study2     None     None                Germany         None          Yes
study3     None     None                United Kingdom  English       None
study4     18-67    Austria             Austria         German        None
study4     18-67    Germany             Germany         German        None
study5     48-99    None                None            None          None

现有代码的问题 #

原代码将每行的键值对转为字典时，重复键会被覆盖，无法生成多行记录来对应重复键的不同取值组合。

修改后的解决方案 #

import pandas as pd

# 原始数据集
df = pd.DataFrame.from_dict({'study_id': {0: 'study1',
  1: 'study2',
  2: 'study3',
  3: 'study4',
  4: 'study5'}, 'fuzzy_market': {0: '[Age: 18-67], [Country of Birth: Austria], [Country of Birth: Germany], [Country: Austria], [Country: Germany], [Language: German]', 1: '[Country: Germany], [Management experience: Yes]', 2: '[Country: United Kingdom], [Language: English]', 3: '[Age: 18-67], [Country of Birth: Austria], [Country of Birth: Germany], [Country: Austria], [Country: Germany], [Language: German]', 4: '[Age: 48-99]'}})

# 提取所有键值对，保留重复项
df['kv_pairs'] = df['fuzzy_market'].str.findall(r'([^:\[]+): ([^\]]+)')

# 展开为多行记录
all_keys = set()
expanded_records = []

for _, row in df.iterrows():
    study_id = row['study_id']
    kv_list = row['kv_pairs']
    
    # 构建键到值列表的映射，保留重复值
    kv_map = {}
    for key, val in kv_list:
        if key not in kv_map:
            kv_map[key] = []
        kv_map[key].append(val)
        all_keys.add(key)
    
    # 确定需要生成的行数（取值列表最长的长度）
    row_count = max(len(vals) for vals in kv_map.values()) if kv_map else 1
    
    # 生成每行记录
    for i in range(row_count):
        record = {'study_id': study_id}
        for key in all_keys:
            # 按索引取对应值，超出长度则填充None
            record[key] = kv_map[key][i] if i < len(kv_map.get(key, [])) else None
        expanded_records.append(record)

# 转换为DataFrame并调整列顺序
result_df = pd.DataFrame(expanded_records)
desired_columns = ['study_id', 'Age', 'Country of Birth', 'Country', 'Language', 'Management experience']
# 补充缺失列并调整顺序
for col in desired_columns:
    if col not in result_df.columns:
        result_df[col] = None
result_df = result_df[desired_columns]

print(result_df)

代码说明 #

1. 提取键值对：用正则表达式提取每个fuzzy_market字符串中的所有键值对，保留重复键的多个取值。
2. 构建映射表：将每个study_id对应的键值对整理为「键-值列表」的映射，避免重复键被覆盖。
3. 生成多行记录：根据值列表的最大长度生成对应行数，每行按索引匹配重复键的取值，缺失值填充为None。
4. 调整列顺序：对齐期望输出的列顺序，确保格式一致。

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