解决Pandas中TypeError: float对象无法被解释为整数报错

问题描述 #

运行以下代码处理CSV数据时出现错误：

import pandas as pd
import numpy as np
import json


Data1 = 'Data/lab_202210181540.csv'


############### For Data 1 ####################
data_frame = pd.read_csv(Data1)
data_frame['data'] = data_frame['data'].apply(json.loads)
data_frame = data_frame.rename(columns={'id': 'Sr#'})
data_frame = data_frame.join(pd.json_normalize(data_frame['data']))
select_data = data_frame[['serializer']]
select_data['serializer'] = select_data['serializer'].apply(lambda x: json.loads(x) if pd.notnull(x) else np.nan)
final_select_data = select_data.join(pd.json_normalize(select_data.pop('serializer')))
result = final_select_data['Analyte_line']
df = pd.DataFrame(result)
for i in range(len(df['Analyte_line'])):
    for arr in range(df['Analyte_line'][i]):
        if arr[0] in ['Urea', 'Rapid Malaria']:
            df[arr[0]][i] = "".join(map(lambda x: str(x), arr[1:]))

报错信息：

TypeError: 'float' object cannot be interpreted as an integer

期望得到的表格格式：

原始数据示例：

Patient_ID,Analyte_line
KYN059AQP,"[['Urea', 3.0, '3', ''], ['Creatinine', 3.0, '3', ''], ['Uric Acid', 3.0, '3', '']]"
KQT767JLU,"[['Total Protein', '', '6', ''], ['Albumin', '', '6', ''], ['Globulin', '', '4', ''], ['Total Bilirubin', '', '6', ''], ['Direct Bilirubin', '', '4', ''], ['Indirect Bilirubin', '', '4', ''], ['Alkaline Phosphatase', '', '4', ''], ['SGPT', '', '5', ''], ['SGOT', '', '5', ''], ['Gamma GT', '', '5', ''], ['AG Ratio', '', '4', '']]"
PWV009AGQ,"[['HGB', '', '18', ''], ['RBC', '', '1', ''], ['HCT', '', '2', ''], ['MCV', '', '3', ''], ['MCH', '', '3', ''], ['MCHC', '', '3', ''], ['RDWcv', '', '2', ''], ['RDWsd', '', '3', ''], ['WBC', '', '4', ''], ['NEU', '', '5', ''], ['LYM', '', '6', ''], ['MON', '', '', ''], ['BAS', '', '', ''], ['EO', '', '', ''], ['NEU%', '', '', ''], ['LYM%', '', '', ''], ['MON%', '', '', ''], ['EO%', '', '', ''], ['BAS%', '', '', ''], ['PLT', '', '170', ''], ['PCT', '', '3', ''], ['MPV', '', '', ''], ['PDWsd', '', '', ''], ['PDWcv', '', '', ''], ['ESR', '', '5', ''], ['GRA#', '', '', '']]"
PWV009AGQ,"[['Total Protein', '', '23', ''], ['Albumin', '', '2', ''], ['Globulin', '', '2', ''], ['Total Bilirubin', '', '2', ''], ['Direct Bilirubin', 2.0, '', ''], ['Indirect Bilirubin', 2.0, '', ''], ['Alkaline Phosphatase', '', '3', ''], ['SGPT', 1.0, '', ''], ['SGOT', '', '4', ''], ['Gamma GT', 33.0, '31', ''], ['AG Ratio', '', '2', '']]"

错误原因 #

1. 循环逻辑错误：for arr in range(df['Analyte_line'][i])逻辑完全错误，df['Analyte_line'][i]是列表类型，而range()需要整数参数，直接包裹会触发类型错误。
2. 数据解析问题：原始数据中Analyte_line是单引号包裹的列表字符串，json.loads无法兼容解析，可能导致部分值变为NaN（float类型），进一步触发报错。
3. 赋值方式低效且危险：直接用df[arr[0]][i]赋值会触发SettingWithCopyWarning，同时遍历效率极低。

解决方案 #

1. 使用ast.literal_eval解析Analyte_line列的字符串列表，解决单引号列表的解析兼容性问题。
2. 定义目标提取指标列表，遍历每行数据生成对应指标的键值对。
3. 用pd.DataFrame将处理后的字典转换为表格，缺失值统一填充为'-'。

修正后的完整代码 #

import pandas as pd
import ast

# 读取数据
Data1 = 'Data/lab_202210181540.csv'
df = pd.read_csv(Data1)

# 解析Analyte_line列的字符串列表
df['Analyte_line'] = df['Analyte_line'].apply(ast.literal_eval)

# 定义需要提取的目标指标
target_analytes = ['Urea', 'Creatinine', 'Uric Acid', 'SGOT']

# 处理每行数据，生成指标字典
def process_analytes(row):
    analyte_dict = {analyte: '-' for analyte in target_analytes}
    for item in row['Analyte_line']:
        analyte_name = item[0]
        if analyte_name in target_analytes:
            # 拼接非空的后续值
            values = [str(x) for x in item[1:] if x != '']
            analyte_dict[analyte_name] = ','.join(values) if values else '-'
    return pd.Series(analyte_dict)

# 合并处理后的指标列
result_df = df[['Patient_ID']].join(df.apply(process_analytes, axis=1))

print(result_df)

运行结果 #

输出结果会完全匹配期望的表格格式，未检测到的指标自动填充为'-'。

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