# -*- coding: utf-8 -*- """ Spyder 编辑器 这是一个临时脚本文件。 """ import os import pandas as pd pd.set_option('chained_assignment', None) select_cols = ['遥测ID号', '风场', '风场几期', '风机号', '测点', '标准化中文', '转发顺序号', 'en_name'] origin_col_map = { 'wind_turbine_number': '风机编号', 'wind_turbine_name': '风机原始名称', 'time_stamp': '时间戳', 'active_power': '有功功率', 'rotor_speed': '风轮转速', 'generator_speed': '发电机转速', 'wind_velocity': '风速', 'pitch_angle_blade_1': '桨距角1', 'pitch_angle_blade_2': '桨距角2', 'pitch_angle_blade_3': '桨距角3', 'cabin_position': '机舱位置', 'true_wind_direction': '绝对风向', 'yaw_error1': '对风角度', 'set_value_of_active_power': '有功功率设定值', 'gearbox_oil_temperature': '齿轮箱油温', 'generatordrive_end_bearing_temperature': '发电机驱动端轴承温度', 'generatornon_drive_end_bearing_temperature': '发电机非驱动端轴承温度', 'cabin_temperature': '机舱内温度', 'twisted_cable_angle': '扭缆角度', 'front_back_vibration_of_the_cabin': '机舱前后振动', 'side_to_side_vibration_of_the_cabin': '机舱左右振动', 'actual_torque': '实际力矩', 'given_torque': '给定力矩', 'clockwise_yaw_count': '顺时针偏航次数', 'counterclockwise_yaw_count': '逆时针偏航次数', 'unusable': '不可利用', 'power_curve_available': '功率曲线可用', 'required_gearbox_speed': '齿轮箱转速', 'inverter_speed_master_control': '变频器转速(主控)', 'outside_cabin_temperature': '环境温度', 'main_bearing_temperature': '主轴承轴承温度', 'gearbox_high_speed_shaft_bearing_temperature': '齿轮箱高速轴轴承温度', 'gearboxmedium_speed_shaftbearing_temperature': '齿轮箱中速轴轴承温度', 'gearbox_low_speed_shaft_bearing_temperature': '齿轮箱低速轴轴承温度', 'generator_winding1_temperature': '发电机绕组1温度', 'generator_winding2_temperature': '发电机绕组2温度', 'generator_winding3_temperature': '发电机绕组3温度', 'wind_turbine_status': '风机状态1', 'wind_turbine_status2': '风机状态2', 'turbulence_intensity': '湍流强度' } table_exists_cols = ['wind_turbine_number', 'wind_turbine_name', 'time_stamp', 'active_power', 'rotor_speed', 'generator_speed', 'wind_velocity', 'pitch_angle_blade_1', 'pitch_angle_blade_2', 'pitch_angle_blade_3', 'cabin_position', 'true_wind_direction', 'yaw_error1', 'set_value_of_active_power', 'gearbox_oil_temperature', 'generatordrive_end_bearing_temperature', 'generatornon_drive_end_bearing_temperature', 'cabin_temperature', 'twisted_cable_angle', 'front_back_vibration_of_the_cabin', 'side_to_side_vibration_of_the_cabin', 'actual_torque', 'given_torque', 'clockwise_yaw_count', 'counterclockwise_yaw_count', 'unusable', 'power_curve_available', 'required_gearbox_speed', 'inverter_speed_master_control', 'outside_cabin_temperature', 'main_bearing_temperature', 'gearbox_high_speed_shaft_bearing_temperature', 'gearboxmedium_speed_shaftbearing_temperature', 'gearbox_low_speed_shaft_bearing_temperature', 'generator_winding1_temperature', 'generator_winding2_temperature', 'generator_winding3_temperature', 'wind_turbine_status', 'wind_turbine_status2', 'turbulence_intensi'] def yangqu_measurepoint(df): df['风场几期'] = df['遥测ID号'].apply(lambda x: x.split(' ')[1].split('_')[1].split('-')[0][0:4]) df['风场'] = df['风场几期'].apply(lambda x: x[0:2]) df['风机号'] = df['遥测ID号'].apply(lambda x: x.split(' ')[1].split('_')[1].split('-')[1][0:3]) df['测点'] = df['遥测ID号'].apply( lambda x: ''.join(x.replace('遥测值', '').strip().split(' ')[1:]).split('_')[1].split('-')[1][3:]) show_measurepoint(df, '阳曲测点') col_mapping = { "机舱中轴线与风向夹角": "yaw_error1", "风向": "true_wind_direction", "舱外温度": "outside_cabin_temperature", "扭揽角度": "twisted_cable_angle", "1#桨叶片角度": "pitch_angle_blade_1", "2#桨叶片角度": "pitch_angle_blade_2", "3#桨叶片角度": "pitch_angle_blade_3", "发电机转速": "generator_speed", "发电机有功功率": "active_power", "机舱侧向振动(已滤波)": "side_to_side_vibration_of_the_cabin", "机舱轴向振动(已滤波)": "front_back_vibration_of_the_cabin", "舱内温度": "cabin_temperature", "风速": "wind_velocity", "发电机前轴承温度1": "generatordrive_end_bearing_temperature", # 根据规则，发电机前轴承对应驱动端轴承 "发电机后轴承温度1": "generatornon_drive_end_bearing_temperature", # 根据规则，发电机后轴承对应非驱动端轴承 } df['en_name'] = df['测点'].map(col_mapping) print(df.groupby('en_name').count()) df.sort_values(by='转发顺序号', inplace=True) save_df(df, '阳曲测点') return df def xipan_measurepoint(df): df['风场几期'] = df['遥测ID号'].apply(lambda x: x.split(' ')[1].split('_')[1][0:4]) df['风场'] = df['风场几期'].apply(lambda x: x[0:2]) df['风机号'] = df['遥测ID号'].apply(lambda x: x.split(' ')[1].split('_')[1][6:]) df['测点'] = df['遥测ID号'].apply( lambda x: ''.join(x.replace('遥测值', '').strip().split(' ')[2])) show_measurepoint(df, '西潘测点') col_mapping = { "风速": "wind_velocity", "机舱与风向夹角": "yaw_error1", "风向": "true_wind_direction", "舱外温度": "outside_cabin_temperature", "偏航角度(扭缆角度）": "twisted_cable_angle", "1#桨叶片角度(桨距角)": "pitch_angle_blade_1", "2#桨叶片角度(桨距角)": "pitch_angle_blade_2", "3#桨叶片角度(桨距角)": "pitch_angle_blade_3", "发电机有功功率": "active_power", "舱内温度": "cabin_temperature", "发电机转速": "generator_speed", "桨叶1位置给定": "pitch_angle_blade_1", # 假设为桨叶1的位置给定值 "桨叶2位置给定": "pitch_angle_blade_2", # 假设为桨叶2的位置给定值 "桨叶3位置给定": "pitch_angle_blade_3", # 假设为桨叶3的位置给定值 "机舱位置": "cabin_position", "齿轮箱油温": "gearbox_oil_temperature", "发电机定子L1绕组温度": "generator_winding1_temperature", # 假设L1对应U相 "发电机定子L2绕组温度": "generator_winding2_temperature", # 假设L2对应V相 "发电机定子L3绕组温度": "generator_winding3_temperature", # 假设L3对应W相 "发电机驱动端轴承温度": "generatordrive_end_bearing_temperature", "发电机非驱动端轴承温度": "generatornon_drive_end_bearing_temperature", "电网有功功率": "active_power", # 假设为电网有功功率 "实时风速": "wind_velocity", # 假设为实时风速 "3s风向(0-360度)": "true_wind_direction", # 假设为3秒平均风向 "主轴温度": "main_bearing_temperature", # 假设为主轴温度 } df['en_name'] = df['测点'].map(col_mapping) print(df.groupby('en_name').count()) df.sort_values(by='转发顺序号', inplace=True) save_df(df, '西潘测点') return df def majialiang_measurepoint(df, name): # 根据马家梁数据格式调整字段提取逻辑 df['风场几期'] = df['遥测ID号'].apply(lambda x: x.split(' ')[1].split('_')[1][0:5]) df['风场'] = df['风场几期'].apply(lambda x: x[0:len(name)]) # 假设风机号在第三个分割段中 df['风机号'] = df['遥测ID号'].apply(lambda x: x.split(' ')[1].split('-')[1][0:3]) # 测点提取可能需要调整切片位置 df['测点'] = df['遥测ID号'].apply( lambda x: ''.join(x.replace('遥测值', '').strip().split(' ')[1:]).split('_')[1].split('-')[1][3:]) show_measurepoint(df, name + '测点') col_mapping = { "风速": "wind_velocity", "机舱与风向夹角": "yaw_error1", "风向": "true_wind_direction", "偏航角度(扭缆角度）": "twisted_cable_angle", "1#桨叶片角度(桨距角)": "pitch_angle_blade_1", "2#桨叶片角度(桨距角)": "pitch_angle_blade_2", "3#桨叶片角度(桨距角)": "pitch_angle_blade_3", "发电机有功功率": "active_power", "发电机前轴承温度1": "generatordrive_end_bearing_temperature", # 前轴承对应驱动端 "发电机后轴承温度1": "generatornon_drive_end_bearing_temperature", # 后轴承对应非驱动端 "有功功率": "active_power", # 与“发电机有功功率”相同 "瞬时风速": "wind_velocity", # 假设为瞬时风速 "风向角": "yaw_error1", # 与“风向”相同 "桨距角1": "pitch_angle_blade_1", # 与“1#桨叶片角度(桨距角)”相同 "桨距角2": "pitch_angle_blade_2", # 与“2#桨叶片角度(桨距角)”相同 "桨距角3": "pitch_angle_blade_3", # 与“3#桨叶片角度(桨距角)”相同 "发电机转速": "generator_speed", "发电机定子U温度": "generator_winding1_temperature", # U相温度 "发电机定子V温度": "generator_winding2_temperature", # V相温度 "发电机定子W温度": "generator_winding3_temperature", # W相温度 "驱动端轴承温度": "generatordrive_end_bearing_temperature", # 与“发电机前轴承温度1”相同 "高速轴承温度": "gearbox_high_speed_shaft_bearing_temperature", # 假设为齿轮箱高速轴轴承温度 "舱外温度": "outside_cabin_temperature", "舱内温度": "cabin_temperature", } df['en_name'] = df['测点'].map(col_mapping) print(df.groupby('en_name').count()) df.sort_values(by='转发顺序号', inplace=True) save_df(df, name + '测点') return df def fufeng_measurepoint(df): # 根据孟县数据格式调整字段提取逻辑 df['风场几期'] = df['遥测ID号'].apply(lambda x: x.split(' ')[1].split('_')[0]) df['风场'] = df['风场几期'].apply(lambda x: x[0:2]) # 假设风机号在第三个分割段中 df['风机号'] = df['遥测ID号'].apply(lambda x: x.split(' ')[1].split('-')[1]) # 测点提取可能需要调整切片位置 df['测点'] = df['遥测ID号'].apply( lambda x: ''.join(x.replace('遥测值', '').strip().split(' ')[2:])) show_measurepoint(df, '富风测点') col_mapping = { "机组有功功率": "active_power", "机组1#叶片变桨角度": "pitch_angle_blade_1", "机组2#叶片变桨角度": "pitch_angle_blade_2", "机组3#叶片变桨角度": "pitch_angle_blade_3", "机组主轴前轴承温度": "main_bearing_temperature", # 假设为主轴前轴承温度 "机组齿轮箱油池温度": "gearbox_oil_temperature", "机组发电机转速": "generator_speed", "机组发电机绕组u1温度": "generator_winding1_temperature", # 假设u1对应U相 "机组发电机绕组v1温度": "generator_winding2_temperature", # 假设v1对应V相 "机组发电机绕组w1温度": "generator_winding3_temperature", # 假设w1对应W相 "机组发电机前轴承温度": "generatordrive_end_bearing_temperature", # 前轴承对应驱动端 "机组发电机后轴承温度": "generatornon_drive_end_bearing_temperature", # 后轴承对应非驱动端 "机组瞬时风速": "wind_velocity", # 假设为瞬时风速 "机组瞬时风向": "true_wind_direction", # 假设为瞬时风向 "机组环境温度": "outside_cabin_temperature", # 假设环境温度为舱外温度 "机组机舱温度": "cabin_temperature", "机组机舱X方向振动": "side_to_side_vibration_of_the_cabin", # 假设X方向为侧向振动 "机组机舱Y方向振动": "front_back_vibration_of_the_cabin", # 假设Y方向为轴向振动 } df['en_name'] = df['测点'].map(col_mapping) print(df.groupby('en_name').count()) df.sort_values(by='转发顺序号', inplace=True) save_df(df, '富风测点') return df def podi_measurepoint(df): # 坡底数据格式处理 df['风场几期'] = df['遥测ID号'].apply(lambda x: x.split(' ')[1][0:4]) df['风场'] = df['风场几期'].apply(lambda x: x[0:2]) # 假设风机号在第三个分割段中 df['风机号'] = df['遥测ID号'].apply(lambda x: x.split(' ')[1].split('-')[1][0:2]) # 测点提取可能需要调整切片位置 df['测点'] = df['遥测ID号'].apply( lambda x: ''.join(x.replace('遥测值', '').strip().split(' ')[2:])) show_measurepoint(df, '坡底测点') col_mapping = { "主轴转速": "rotor_speed", "发电机转速": "generator_speed", "风向": "true_wind_direction", "风速": "wind_velocity", "机舱温度": "cabin_temperature", "室外温度": "outside_cabin_temperature", "主轴轴承温度": "main_bearing_temperature", # 假设主轴轴承温度对应主轴承温度 "齿轮箱高速轴承端温度": "gearbox_high_speed_shaft_bearing_temperature", # 假设为齿轮箱高速轴轴承温度 "发电机驱动端温度": "generatordrive_end_bearing_temperature", # 驱动端轴承温度 "发电机非驱动端温度": "generatornon_drive_end_bearing_temperature", # 非驱动端轴承温度 "扭缆角度": "twisted_cable_angle", "轴1桨叶实际角度": "pitch_angle_blade_1", # 假设轴1对应1#桨叶 "轴2桨叶实际角度": "pitch_angle_blade_2", # 假设轴2对应2#桨叶 "轴3桨叶实际角度": "pitch_angle_blade_3", # 假设轴3对应3#桨叶 "机舱Y方向振动值": "front_back_vibration_of_the_cabin", # 假设Y方向为轴向振动 "机舱X方向振动值": "side_to_side_vibration_of_the_cabin", # 假设X方向为侧向振动 "有功功率": "active_power", } df['en_name'] = df['测点'].map(col_mapping) print(df.groupby('en_name').count()) df.sort_values(by='转发顺序号', inplace=True) save_df(df, '坡底测点') return df def hejiagou_measurepoint(df): # 贺家沟数据格式处理 df['风场几期'] = df['遥测ID号'].apply(lambda x: x.split(' ')[1].split('_')[1][0:5]) df['风场'] = df['风场几期'].apply(lambda x: x[0:3]) # 假设风机号在第三个分割段中 df['风机号'] = df['遥测ID号'].apply(lambda x: x.split(' ')[1].split('-')[1]) # 测点提取可能需要调整切片位置 df['测点'] = df['遥测ID号'].apply( lambda x: ''.join(x.replace('遥测值', '').strip().split(' ')[2])) show_measurepoint(df, '贺家沟测点') col_mapping = { "环境温度": "outside_cabin_temperature", # 假设环境温度为舱外温度 "风速1s": "wind_velocity", # 假设为1秒平均风速 "绝对风向": "true_wind_direction", # 假设为绝对风向 "桨角1": "pitch_angle_blade_1", # 1#桨叶角度 "桨角2": "pitch_angle_blade_2", # 2#桨叶角度 "桨角3": "pitch_angle_blade_3", # 3#桨叶角度 "发电机转速": "generator_speed", # "齿轮箱转速": "generator_speed", # 假设为齿轮箱转速 "发电机U相温度": "generator_winding1_temperature", # U相温度 "发电机V相温度": "generator_winding2_temperature", # V相温度 "发电机W相温度": "generator_winding3_temperature", # W相温度 "发电机轴承温度": "generatordrive_end_bearing_temperature", # 假设为发电机轴承温度（未区分驱动端和非驱动端） "齿轮箱高速轴前轴承温度": "gearbox_high_speed_shaft_bearing_temperature", # 假设为齿轮箱高速轴前轴承温度 "机舱内温度": "cabin_temperature", "变流器有功功率": "active_power", # 假设为变流器有功功率 "塔筒左右振动": "side_to_side_vibration_of_the_cabin", # 假设为塔筒左右振动 "塔筒前后振动": "front_back_vibration_of_the_cabin", # 假设为塔筒前后振动 } df['en_name'] = df['测点'].map(col_mapping) print(df.groupby('en_name').count()) df.sort_values(by='转发顺序号', inplace=True) save_df(df, '贺家沟测点') return df def not_in_exists_col(df): print("jinrule") tuple_datas = set() for feichang, fengji, cedian, col in zip(df['风场'], df['风机号'], df['测点'], df['en_name']): if col not in table_exists_cols: tuple_datas.add((feichang, cedian, col)) # print(feichang, cedian, col) for col in tuple_datas: print(col) print('-----------------') def show_measurepoint(df, name): print(f'--------{name}-----------') for cn_name in df['测点'].unique(): print(f"{cn_name}:'',") print('-------------------') def save_df(df, name): df['遥测ID号'] = df['遥测ID号'].apply(lambda x: x.replace('遥测定义表 ', '').replace('遥测值', '').strip()) df['标准化中文'] = df['en_name'].map(origin_col_map) df.to_csv(r'C:\Users\wzl\Desktop\中广核104测点\2405' + os.sep + str(name) + '.csv', columns=select_cols, index=False, encoding='utf8') if __name__ == '__main__': # df = pd.read_csv(r"D:\data\tmp\2405ZF.csv", encoding='gbk') df = pd.read_csv(r"D:\data\tmp\2405ZF.csv", encoding='utf8') # 添加阳曲处理 yangqu_df = yangqu_measurepoint(df[df['遥测ID号'].str.contains('阳曲')]) # 添加西潘处理 xipan_df = xipan_measurepoint(df[df['遥测ID号'].str.contains('西潘')]) # 添加马家梁处理 majialiang_df = majialiang_measurepoint(df[df['遥测ID号'].str.contains('马家梁')], '马家梁') # 添加富风处理 fufeng_df = fufeng_measurepoint(df[df['遥测ID号'].str.contains('富风')]) # 添加坡底处理 podi_df = podi_measurepoint(df[df['遥测ID号'].str.contains('坡底')]) # 添加贺家沟处理 hejiagou_df = hejiagou_measurepoint(df[df['遥测ID号'].str.contains('贺家沟')]) result_df = pd.concat([yangqu_df, xipan_df, majialiang_df, fufeng_df, podi_df, hejiagou_df]) not_in_exists_col(result_df) save_df(result_df, '2405测点')