如何转换含字符串列表的Pandas Series并实现数值求和？

需要计算Pandas Series的求和，但在转换为数值型时多次触发ValueError。目标是计算df.scorePlayer1的求和，预期输出为20。数据源是从MongoDB导出的devicescores.csv，原处理代码如下：

with open("./devicescores.csv", 'r') as file:
  csvreader = pd.read_csv(file)
  for row in csvreader:
    csvreader.to_excel (r'File name.xlsx', sheet_name="Testing",index = False, header=True)
  df = pd.read_excel('File name.xlsx', dtype={'scoreTable':str})
  df['score1'] = df['scoreTable'].str.split('{', expand=True)[1]
  df['score1'] = df['score1'].str.split('scores', expand=True)[1]
  df['score1'] = df['score1'].str.split('\":', expand=True)[1]
  df['score1'] = df['score1'].str.split(',\"_id', expand=True)[0]

  df['score3'] = df['scoreTable'].str.split('{', expand=True)[3]
  df['score3'] = df['score3'].str.split('scores', expand=True)[1]
  df['score3'] = df['score3'].str.split('\":', expand=True)[1]
  df['score3'] = df['score3'].str.split(',\"_id', expand=True)[0]
  
  df['score5'] = df['scoreTable'].str.split('{', expand=True)[5]
  df['score5'] = df['score5'].str.split('scores', expand=True)[1]
  df['score5'] = df['score5'].str.split('\":', expand=True)[1]
  df['score5'] = df['score5'].str.split(',\"_id', expand=True)[0]
  df = df.drop('scoreTable', axis=1)

  df.rename(columns = {'score1':'scorePlayer1'}, inplace = True)
  df.rename(columns = {'score3':'scorePlayer2'}, inplace = True)
  df.rename(columns = {'score5':'scorePlayer3'}, inplace = True)
  
  df = df[['house', 'unit', 'deviceName', 'scorePlayer1', 'scorePlayer2','scorePlayer3']]

核心问题 #

原代码通过多次字符串分割提取分数，但结果仍是带非数字字符的字符串，直接转数值会触发ValueError；此外，冗余的Excel读写完全没必要，且硬编码分割位置的方式极度脆弱，数据格式稍有变化就会失效。

优化实现 #

1. 移除冗余Excel操作
   直接读取CSV，跳过转存Excel步骤：

   import pandas as pd
   import json
   
   df = pd.read_csv("./devicescores.csv", dtype={'scoreTable': str})
   

2. 用JSON解析提取分数（更稳健）
   scoreTable是嵌套JSON结构，用json.loads解析比字符串分割可靠：

   def get_player_score(score_str, player_pos):
       try:
           # 解析JSON字符串为Python对象
           score_list = json.loads(score_str)
           # 取对应位置的玩家分数（原代码取第1、3、5个{，对应列表索引0、2、4）
           return score_list[player_pos]['scores']
       except (json.JSONDecodeError, IndexError, KeyError):
           return 0  # 解析失败时返回0，避免后续求和出错
   
   # 提取三个玩家的分数
   df['scorePlayer1'] = df['scoreTable'].apply(lambda x: get_player_score(x, 0))
   df['scorePlayer2'] = df['scoreTable'].apply(lambda x: get_player_score(x, 2))
   df['scorePlayer3'] = df['scoreTable'].apply(lambda x: get_player_score(x, 4))
   

3. 转换数值并求和
   确保分数列是数值类型后直接求和：

   # 强制转换为数值型，处理可能的非数字值
   df['scorePlayer1'] = pd.to_numeric(df['scorePlayer1'], errors='coerce').fillna(0)
   # 计算求和
   total_score = df['scorePlayer1'].sum()
   print(total_score)  # 预期输出20
   

4. 整理最终数据

   df = df.drop('scoreTable', axis=1)
   df = df[['house', 'unit', 'deviceName', 'scorePlayer1', 'scorePlayer2','scorePlayer3']]
   

关键提示 #

• 结构化数据（如MongoDB导出的JSON嵌套字段）优先用JSON解析，避免硬编码字符串分割
• 转换数值时使用pd.to_numeric(errors='coerce')处理异常值，避免ValueError
• 冗余的文件读写会降低效率并引入格式风险，直接读取原始CSV即可

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