DataNalysis/DataBase/turbine-cluster/inputData.py

import pandas as pd
import datetime, time
import re
import os
import argparse
from sqlalchemy import create_engine
import pytz
from data_cleaning import cleaning, rm_duplicated, key_field_row_cleaning

# current_path = os.path.dirname(__file__)
# dataloc = current_path + '/data/'
station_id = '260'

def readData(name):
    """
    读取数据
    :param name: 名字
    :return:
    """
    path =  rf"../../cluster/{station_id}/" + name
    return pd.read_csv(path)


def saveData(name, data):
    """
    存放数据
    :param name: 名字
    :param data: 数据
    :return:
    """
    path =  rf"../../cluster/{station_id}/" + name
    os.makedirs(os.path.dirname(path), exist_ok=True)
    data.to_csv(path, index=False)


def timestamp_to_datetime(ts):
    local_timezone = pytz.timezone('Asia/Shanghai')
    if type(ts) is not int:
        raise ValueError("timestamp-时间格式必须是整型")
    if len(str(ts)) == 13:
        dt = datetime.datetime.fromtimestamp(ts/1000, tz=pytz.utc).astimezone(local_timezone)
        return dt
    elif len(str(ts)) == 10:
        dt = datetime.datetime.fromtimestamp(ts, tz=pytz.utc).astimezone(local_timezone)
        return dt
    else:
        raise ValueError("timestamp-时间格式错误")

def dt_tag(dt):
    date = dt.replace(hour=0, minute=0, second=0)
    delta = (dt - date) / pd.Timedelta(minutes=15)
    return delta + 1

def timestr_to_timestamp(time_str):
    """
    将时间戳或时间字符串转换为datetime.datetime类型
    :param time_data: int or str
    :return:datetime.datetime
    """
    if isinstance(time_str, str):
        if len(time_str) == 10:
            dt = datetime.datetime.strptime(time_str, '%Y-%m-%d')
            return int(round(time.mktime(dt.timetuple())) * 1000)
        elif len(time_str) in {17, 18, 19}:
            dt = datetime.datetime.strptime(time_str, '%Y-%m-%d %H:%M:%S')   # strptime字符串解析必须严格按照字符串中的格式
            return int(round(time.mktime(dt.timetuple())) * 1000)   # 转换成毫秒级的时间戳
        else:
            raise ValueError("时间字符串长度不满足要求！")
    else:
        return time_str


class DataBase(object):
    def __init__(self, begin, end, opt):
        self.begin = begin
        self.end = end
        self.opt = opt
        self.begin_stamp = timestr_to_timestamp(str(begin))
        self.end_stamp = timestr_to_timestamp(str(end))
        self.database = opt.database
        self.dataloc = opt.dataloc

    def clear_data(self):
        """
        删除所有csv
        :return:
        """
        # 设置文件夹路径
        import glob
        import os
        folder_path = self.dataloc

        # 使用 glob 获取所有的 .csv 文件路径
        csv_files = glob.glob(os.path.join(folder_path, '**/*.csv'), recursive=True)

        # 遍历所有 .csv 文件并删除
        for file_path in csv_files:
            os.remove(file_path)
        print("清除所有csv文件")

    def create_database(self):
        """
        创建数据库连接
        :param database: 数据库地址
        :return:
        """
        engine = create_engine(self.database)
        return engine

    def exec_sql(self, sql, engine):
        """
        从数据库获取数据
        :param sql: sql语句
        :param engine: 数据库对象
        :return:
        """
        df = pd.read_sql_query(sql, engine)
        return df

    def get_process_power(self):
        """
        获取整体功率数据
        :param database:
        :return:
        """
        engine = self.create_database()
        sql_cap = "select C_CAPACITY from t_electric_field"
        cap = self.exec_sql(sql_cap, engine)['C_CAPACITY']
        sql_power = "select C_TIME,C_REAL_VALUE, C_ABLE_VALUE, C_IS_RATIONING_BY_MANUAL_CONTROL, C_IS_RATIONING_BY_AUTO_CONTROL" \
                    " from t_power_station_status_data where C_TIME between '{}' and '{}'".format(self.begin, self.end)
        powers = self.exec_sql(sql_power, engine)
        powers['C_TIME'] = pd.to_datetime(powers['C_TIME'])
        mask1 = powers['C_REAL_VALUE'].astype(float) > float(cap)
        mask = powers['C_REAL_VALUE'] == -99

        mask = mask | mask1
        print("实际功率共{}条，要剔除功率有{}条".format(len(powers), mask.sum()))
        powers = powers[~mask]
        print("剔除完后还剩{}条".format(len(powers)))
        binary_map = {b'\x00': 0, b'\x01': 1}
        powers['C_IS_RATIONING_BY_AUTO_CONTROL'] = powers['C_IS_RATIONING_BY_AUTO_CONTROL'].map(binary_map)
        powers = rm_duplicated(powers)
        saveData("power.csv", powers)

    def get_process_turbine(self, output_dir):
        """
        从数据库中获取风头数据，并进行简单处理
        :param database:
        :return:
        """
        for number in self.opt.turbineloc:
            # 机头数据
            engine = self.create_database()

            print("导出风机{}的数据".format(number))
            sql_turbine = "select C_TIME, C_WS, C_WD, C_ACTIVE_POWER from t_wind_turbine_status_data " \
                          "WHERE C_EQUIPMENT_NO=" + str(number) + " and C_TIME between '{}' and '{}'".format(self.begin, self.end)  # + " and C_WS>0 and C_ACTIVE_POWER>0"
            turbine = self.exec_sql(sql_turbine, engine)
            turbine = cleaning(turbine, 'turbine', cols=['C_WS', 'C_ACTIVE_POWER'], dup=False)
            turbine['C_TIME'] = pd.to_datetime(turbine['C_TIME'])
            turbine = turbine[turbine['C_TIME'].dt.strftime('%M').isin(['00', '15', '30', '45'])]
            # 直接导出所有数据
            output_file = os.path.join(output_dir, f"turbine-{number}.csv")
            saveData(output_file, turbine)

    def process_csv_files(self, input_dir, output_dir, M, N):  # MBD:没有考虑时间重复
        if not os.path.exists(output_dir):
            os.makedirs(output_dir)

        for i in self.opt.turbineloc:
            input_file = os.path.join(input_dir, f"turbine-{i}.csv")
            output_file = os.path.join(output_dir, f"turbine-{i}.csv")

            # 读取csv文件
            df = pd.read_csv(input_file)

            # 剔除异常值，并获取异常值统计信息
            df_clean, count_abnormal1, count_abnormal2, total_removed, removed_continuous_values = self.remove_abnormal_values(df, N)

            # 输出异常值统计信息
            print(f"处理文件：{input_file}")
            print(f"剔除 -99 点异常值数量：{count_abnormal1}")
            print(f"剔除连续异常值数量：{count_abnormal2}")
            print(f"总共剔除数据量：{total_removed}")
            print(f"剔除的连续异常值具体数值：{removed_continuous_values}\n")

            # 保存处理过的CSV文件
            df_clean.to_csv(output_file, index=False)

    def remove_abnormal_values(self,df, N):
        # 标记C_ACTIVE_POWER为-99的行为异常值
        abnormal_mask1 = df['C_ACTIVE_POWER'] == -99
        count_abnormal1 = abnormal_mask1.sum()

        # 标记C_WS, A, B连续5行不变的行为异常值
        columns = ['C_WS', 'C_WD', 'C_ACTIVE_POWER']
        abnormal_mask2 = self.mark_abnormal_streaks(df, columns, N)
        count_abnormal2 = abnormal_mask2.sum()
        # 获得所有异常值的布尔掩码
        abnormal_mask = abnormal_mask1 | abnormal_mask2
        # 获取连续异常值具体数值
        removed_continuous_values = {column: df.loc[abnormal_mask2, column].unique() for column in columns}
        # 剔除异常值
        df_clean = df[~abnormal_mask]
        total_removed = abnormal_mask.sum()
        return df_clean, count_abnormal1, count_abnormal2, total_removed, removed_continuous_values

    # ——————————————————————————机头风速-99和连续异常值清洗代码——————————————————————————————
    def mark_abnormal_streaks(self, df, columns, min_streak):
        abnormal_mask = pd.Series(False, index=df.index)
        streak_start = None
        for i in range(len(df)):
            if i == 0 or any(df.at[i - 1, col] != df.at[i, col] for col in columns):
                streak_start = i

            if i - streak_start >= min_streak - 1:
                abnormal_mask[i - min_streak + 1:i + 1] = True

        return abnormal_mask

    # ——————————————————————————风机单机时间对齐——————————————————————————————
    def TimeMerge(self, input_dir, output_dir, M):
        # 读取所有CSV文件
        files = [os.path.join(input_dir, f"turbine-{i}.csv") for i in self.opt.turbineloc]
        dataframes = [pd.read_csv(f) for f in files]

        # 获取C_TIME列的交集
        c_time_intersection = set(dataframes[0]["C_TIME"])
        for df in dataframes[1:]:
            c_time_intersection.intersection_update(df["C_TIME"])

        # 只保留C_TIME交集中的数据
        filtered_dataframes = [df[df["C_TIME"].isin(c_time_intersection)] for df in dataframes]

        # 将每个过滤后的DataFrame写入新的CSV文件
        os.makedirs(output_dir, exist_ok=True)
        for (filtered_df, i) in zip(filtered_dataframes, self.opt.turbineloc):
            if i == 144:
                filtered_df['C_ACTIVE_POWER'] /= 1000
            filtered_df.to_csv(os.path.join(output_dir, f"turbine-{i}.csv"), index=False)

    def data_process(self):
        """
        数据导出+初步处理的总操控代码
        :param database:
        :return:
        """
        self.clear_data()
        self.get_process_power()
        self.get_process_turbine(f'../../cluster/{station_id}')
        self.process_csv_files(f'../../cluster/{station_id}', f'../../cluster/{station_id}', 50, 5)
        self.TimeMerge(f'../../cluster/{station_id}', f'../../cluster/{station_id}', 50)
        # self.zone_powers('../cluster/data')

if __name__ == '__main__':
    import pandas as pd
    turbineloc = [x for x in range(76, 151, 1)]
    c_names = ['G01', 'G02', 'G03', 'G04', 'G05', 'G06', 'G07', 'G08', 'G09', 'G10'] + ['G' + str(x) for x in range(11, 76)]
    id_names = {id: c_names[x] for x, id in enumerate(turbineloc)}
    args = {'database': 'mysql+pymysql://root:mysql_T7yN3E@192.168.12.10:19306/ipfcst_j00260_20250507161106',
            'cap': 225,
            'id_name': id_names,
            'turbineloc': turbineloc,
            'dataloc': '../cluster/data'}
    opt = argparse.Namespace(**args)
    db = DataBase(begin='2025-01-01', end='2025-05-01', opt=opt)
    db.data_process()