import pandas as pd # 读取Excel文件 file_path 处理的数据表.xlsx df pd.read_excel(file_path, sheet_name自清洁逻辑-ABC) print(df.columns) # 去掉不需要的列假设你知道不需要的列名 columns_to_drop [模块1, 模块2, 模块3, 语音] df.drop(columnscolumns_to_drop, inplaceTrue) # 展开每一秒的数据 expanded_data [] for index, row in df.iterrows(): time_str str(row[时长]) # 确保time_str始终是字符串 if time_str.isdigit(): time int(row[时长]) # 将字符串转换为整数 for second in range(time): expanded_row row.copy() expanded_row[秒] second 1 expanded_data.append(expanded_row) else: print(fInvalid time value:{time_str} at index {index}) # 创建新的DataFrame expanded_df pd.DataFrame(expanded_data) # 重新排序列使“秒”列在前 cols [秒] [col for col in expanded_df.columns if col ! 秒] expanded_df expanded_df[cols] # 根据第一列的秒数调整“时长”列 expanded_df[时长] expanded_df[秒] # 使第一列的秒数递增 expanded_df[秒] range(1, len(expanded_df) 1) # 保存为新的Excel文件 expanded_df.to_excel(展开后的数据AB.xlsx, indexFalse) print(数据已成功展开并保存至 展开后的数据-AB.xlsx)表二import pandas as pd import os def parse_and_export_to_excel(input_filename): try: # 从文件中读取数据 with open(input_filename, r, encodingutf-8) as file: data file.read() # 解析数据 lines data.strip().split(\n) parsed_data [] for i in range(0, len(lines), 7): # 每组数据现在是6行 try: tick lines[i].split(: )[1].strip() bms_parts lines[i 1].split(, ) bms_p bms_parts[0].split(: )[2].strip() bms_v bms_parts[1].split(: )[1].strip() bms_c bms_parts[2].split(: )[1].strip() bms_s bms_parts[3].split(: )[1].strip() vacuum_parts lines[i 2].split(, ) vacuum_duty vacuum_parts[0].split(: )[2].strip() vacuum_sta vacuum_parts[1].split(: )[1].strip() pump_level lines[i 3].split(: )[1].strip() brush_parts lines[i 4].split(, ) brush_dir brush_parts[0].split(: )[2].strip() brush_speed brush_parts[1].split(: )[1].strip() # brush_cur brush_parts[2].split(: )[1].strip() # base_parts lines[i 5].split(, ) # fan_heat base_parts[0].split(: )[2].strip() # temperature base_parts[1].split(: )[1].strip() parsed_data.append({ Tick: tick, # BMS_P: bms_p, # BMS_V: bms_v, # BMS_C: bms_c, BMS_S: bms_s, Vacuum_Duty: vacuum_duty, Vacuum_Sta: vacuum_sta, Pump_Level: pump_level, Brush_Dir: brush_dir, Brush_Speed: brush_speed, # Fan_Heat: fan_heat, # Temperature: temperature }) except IndexError as e: print(f解析行 {i} 到 {i5} 时出错: {e}) continue # 创建 DataFrame df pd.DataFrame(parsed_data) # 获取文件名不带扩展名 base_filename os.path.splitext(input_filename)[0] # 生成 Excel 文件名 output_filename f{base_filename}.xlsx # 写入 Excel df.to_excel(output_filename, indexFalse) print(f数据已导出到 {output_filename}) except Exception as e: print(f处理文件 {input_filename} 失败: {e}) def main(): # 在这里指定输入文件名 input_filename AB.txt parse_and_export_to_excel(input_filename) if __name__ __main__: main()比对数据import pandas as pd # 读取两个表的数据 table1 pd.read_excel(展开后的数据AB.xlsx, sheet_nameSheet1) table2 pd.read_excel(AB.xlsx, sheet_nameSheet1) # 定义转换字典确保与Excel数据匹配 conversion_dict { 地刷: {18V正转450rpm: (1, 2), 5V正转150rpm: (1, 1), 关: (0, 0), 18V反转450rpm: (2, 2), 5V反转150rpm: (2, 1)}, 水泵: {开: 6mL/min, 关: 0mL/min}, 吸力电机: { 200W开: (46, 1), 120W开: (38, 1), 90W开: (30, 1), 30W开: (6, 1), 关: (0, 0) }, 电池包充电状态: {放电: 26, 充电: 10} } # 定义一个函数来检查表二的状态是否为“关” def is_closed_table2(vacuum_status): return vacuum_status 0 # 提取和转换数据 converted_table1 pd.DataFrame({ Brush_Dir: table1[地刷].map(lambda x: conversion_dict[地刷][x][0]), Brush_Speed: table1[地刷].map(lambda x: conversion_dict[地刷][x][1]), Pump_Level: table1[水泵].map(conversion_dict[水泵]), Vacuum_Duty: table1[吸力电机].map(lambda x: conversion_dict[吸力电机].get(x, (0, 0))[0]), Vacuum_Sta: table1[吸力电机].map(lambda x: conversion_dict[吸力电机].get(x, (0, 0))), BMS_S: table1[电池包充电状态].map(conversion_dict[电池包充电状态]) }) # 根据是否为“关”来调整Vacuum_Duty和Vacuum_Status converted_table1[Vacuum_Duty] converted_table1[Vacuum_Sta].map(lambda x: 0 if is_closed_table2(x[1]) else x[0]) converted_table1[Vacuum_Sta] converted_table1[Vacuum_Sta].map(lambda x: 0 if is_closed_table2(x[1]) else x[1]) # 提取表2数据 table2_data table2[[Brush_Dir, Brush_Speed, Pump_Level, Vacuum_Duty, Vacuum_Sta, BMS_S]] # 确保两表长度一致 min_length min(len(converted_table1), len(table2_data)) converted_table1 converted_table1[:min_length] table2_data table2_data[:min_length] # 对比数据 comparison_results pd.DataFrame(indexconverted_table1.index, columnsconverted_table1.columns) for col in converted_table1.columns: if col Vacuum_Sta: comparison_results[col] converted_table1.apply( lambda row: is_closed_table2(row[col]) is_closed_table2(table2_data.loc[row.name, col]), axis1 ) else: comparison_results[col] converted_table1[col] table2_data[col] # 打印差异 for index, row in comparison_results.iterrows(): if not row.all(): differences row[row False].index.tolist() print(fDifference at row {index 1}: {differences}) # 保存对比结果到Excel comparison_df pd.DataFrame({ Table1_Brush_Dir: converted_table1[Brush_Dir], Table2_Brush_Dir: table2_data[Brush_Dir], Table1_Brush_Speed: converted_table1[Brush_Speed], Table2_Brush_Speed: table2_data[Brush_Speed], Table1_Pump_Level: converted_table1[Pump_Level], Table2_Pump_Level: table2_data[Pump_Level], Table1_Vacuum_Duty: converted_table1[Vacuum_Duty], Table2_Vacuum_Duty: table2_data[Vacuum_Duty], Table1_Vacuum_Sta: converted_table1[Vacuum_Sta], Table2_Vacuum_Sta: table2_data[Vacuum_Sta], Table1_BMS_S: converted_table1[BMS_S], Table2_BMS_S: table2_data[BMS_S], Match: comparison_results.all(axis1) }) comparison_df.to_excel(comparison_resultsAB.xlsx, indexFalse)
