CGN_taiyuan_104/tmp/measurepoint2405.py

# -*- 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测点')