1.概述

目前公司台区以下的户表与出线之间的对应关系，户表之间的逻辑顺序数据不够准确，设想通过机器学习算法迅雷用电信息采集量测数据及关联用户档案数据用来进一步自动提取低压台区拓扑关系。

1.1场景需求

人工识别方法结果精准，能够识别到3级拓扑，但效率低，成本高，出现拓扑变化需要重新识别，无法满足实时性要求。

1.2数据需求

1.鹰潭公司HPLC电表每天提取的96点电压数据；

2.配变与户表的映射关系

1.3假设前提

1.同一台区，不同单相电压表，同一日期，表现的电压趋势不同。

以下数据探索（假设条件）也可参照以下代码进行绘图

a, b = random.sample(range(len(self.raw_96_data_single)),2)
a_id = self.raw_96_data_single.loc[a, 'ID'] 
a_data = self.raw_96_data_single.loc[a, 'U1':'U96']
b_id = self.raw_96_data_single.loc[b, 'ID']
b_data = self.raw_96_data_single.loc[b, 'U1':'U96']plt.plot(range(96), a_data, label=a_id)
plt.plot(range(96), b_data, label=b_id)
plt.xticks(list(range(0,98,2)), rotation=45)
plt.ylim(230,250)
plt.xlim(0, 96)
plt.title("同一日期不同表的电压变化（单相表）")
plt.legend()
plt.savefig("1.png", dpi=300)
plt.show()

2.同一台区，同一单相电压表，不同日期，表现的电压趋势不同。

3.同一台区，同一三相电压表，同一日期，不同相（A/B/C）之间表现的电压趋势不同。

4.同一台区，同一（或不同）出线，不同三相电压表，同一日期，相同相（A/B/C）之间表现的电压趋势类似。

5.同一台区，同一（或不同）出线，单相表和三相表对应相，表现的电压趋势类似。

6.同一台区，同一出线，同一日期，同一相位的单相表（或不涉及相位的三相表），逻辑位置越远离变压器则电压越低（压降）。

2.实现

2.1数据预处理

2.1.1获取量测数据

只保留户表编号、采集时间、A相电压、B相电压、C相电压，其余字段全部裁剪。

def get_e_mp_curve(self, path_empcurve):"""ID           DATA_TIME     UA     UB     UC0       187517923 2020-04-01 00:15:00  239.6  240.4  239.21       187517923 2020-04-01 00:30:00  239.6  240.4  239.42       187517923 2020-04-01 01:00:00  237.3  241.0  238.33       187517923 2020-04-01 01:30:00  237.5  240.9  239.64       187517923 2020-04-01 01:45:00  238.5  240.9  239.9...           ...                 ...    ...    ...    ...550688  182468645 2020-04-13 15:15:00  231.1    NaN    NaN550689  182468645 2020-04-13 15:30:00  229.3    NaN    NaN550690  182468645 2020-04-13 15:45:00  230.4    NaN    NaN550691  182468645 2020-04-13 16:00:00  230.8    NaN    NaN550692  182468645 2020-04-13 16:15:00  230.6    NaN    NaN:param path_empcurve::return:"""df = pd.read_csv(path_empcurve, encoding='GBK')[['ID', 'DATA_TIME', 'UA', 'UB', 'UC']]df['DATA_TIME'] = pd.to_datetime(df['DATA_TIME'])return df

2.1.2获取配变户表映射关系

只保留台区编号、户表编号，其余字段全部裁剪。

def get_relation(self, path_relation):"""TG_ID         ID0    701026  1825733911    701026  1825734032    701026  1825734193    701026  1825732904    701026  182573240..      ...        ...139  813945  182430577140  813945  182430578141  813945  182430579142  813945  182430580143  813945  185643860:param path_relation::return:"""df = pd.read_csv(path_relation, encoding='GBK')[['TG_ID', 'ID']]return df

2.1.3获取具有完整96点数据的电表ID及日期的集合

去掉数据存在缺失的记录。

def get_completedata_id_datatime(self):"""{(182430570, datetime.date(2020, 4, 8)),(182573360, datetime.date(2020, 4, 11)),(182430510, datetime.date(2020, 4, 4))......}:return:"""df = self.e_mp_curve.groupby(by=['ID', pd.Grouper(key='DATA_TIME', freq='D')]).count()df = df.reset_index()df = df[df['UA'] == 96]df = df.reset_index(drop=True).drop(labels=['UA', 'UB', 'UC'], axis=1)return {(row[0], row[1].date()) for _, row in df.iterrows()}

2.1.4获取带有电表类型的数据

拼接数据

def get_raw_96_data(self):"""ID   DATA_TIME    TYPE     U1  ...    U93    U94    U95    U960    182430492  2020-04-02  SINGLE  243.3  ...  242.5  243.2  243.4  243.01    182430492  2020-04-04  SINGLE  241.4  ...  241.7  242.1  241.2  241.52    182430492  2020-04-05  SINGLE  241.8  ...  243.4  239.8  243.9  243.83    182430492  2020-04-06  SINGLE  243.1  ...  243.9  244.0  243.2  243.74    182430492  2020-04-07  SINGLE  243.6  ...  241.8  240.7  241.4  241.6..         ...         ...     ...    ...  ...    ...    ...    ...    ...488  186494185  2020-04-02  SINGLE  242.1  ...  241.6  242.1  242.4  242.4489  186494185  2020-04-04  SINGLE  241.1  ...  240.9  241.3  240.6  240.9490  186494185  2020-04-05  SINGLE  241.5  ...  242.9  239.4  242.7  243.2491  186494185  2020-04-06  SINGLE  241.8  ...  242.4  243.5  242.5  242.7492  186494185  2020-04-12  SINGLE  241.7  ...  240.8  241.3  241.2  241.0ID  DATA_TIME  ...                    U95                    U960   182430493 2020-04-02  ...  (242.2, 242.0, 242.8)  (242.4, 243.1, 242.9)1   182430493 2020-04-04  ...  (240.5, 240.3, 241.2)  (240.7, 240.6, 240.7)2   182430493 2020-04-05  ...  (242.8, 242.2, 242.3)  (242.9, 242.6, 242.3)3   182430493 2020-04-06  ...  (242.8, 243.2, 241.9)  (242.8, 240.8, 243.7)4   182430493 2020-04-12  ...  (240.9, 240.1, 240.9)  (240.9, 241.2, 240.4)..        ...        ...  ...                    ...                    ...21  184943920 2020-04-06  ...  (242.5, 243.1, 242.1)  (243.0, 242.4, 243.0)22  184943920 2020-04-07  ...  (240.7, 240.1, 240.7)  (241.1, 241.0, 241.0)23  184943920 2020-04-08  ...  (243.7, 242.6, 243.1)  (243.4, 241.9, 243.3)24  184943920 2020-04-09  ...  (243.7, 243.7, 243.4)  (243.4, 241.9, 243.5)25  184943920 2020-04-12  ...  (240.9, 240.8, 240.8)  (241.1, 240.5, 240.8):return:"""df = self.e_mp_curve.groupby(by=['ID', pd.Grouper(key='DATA_TIME', freq='D')])list_single = []list_triple = []for df_section in df:ID = df_section[0][0]DATE = df_section[0][1].date()if (ID, DATE) in self.completedata_id_datatime:if ID in self.single:listforsingle = [ID, DATE, 'SINGLE']for index, row in df_section[1].iterrows():listforsingle.append(row['UA'])list_single.append(listforsingle)else: listfortriple = list(df_section[0]) + ['TRIPLE']for index, row in df_section[1].iterrows():listfortriple.append((row['UA'], row['UB'], row['UC']))list_triple.append(listfortriple)else:continuecolumns = ['ID', 'DATA_TIME', 'TYPE'] + ['U' + str(i) for i in range(1, 97)]df_single = pd.DataFrame(data=list_single, columns=columns)df_triple = pd.DataFrame(data=list_triple, columns=columns)return df_single, df_triple

2.1.5加入所属台区ID信息

def get_pre_data(self):"""TG_ID         ID  ...                    U95                    U960    701026  182573391  ...                  239.1                    2401    701026  182573403  ...                  241.2                  240.92    701026  182573419  ...                  241.6                  241.53    701026  182573290  ...                  242.6                  243.14    701026  182573290  ...                  243.4                  243.4..      ...        ...  ...                    ...                    ...514  813945  184943920  ...  (242.5, 243.1, 242.1)  (243.0, 242.4, 243.0)515  813945  184943920  ...  (240.7, 240.1, 240.7)  (241.1, 241.0, 241.0)516  813945  184943920  ...  (243.7, 242.6, 243.1)  (243.4, 241.9, 243.3)517  813945  184943920  ...  (243.7, 243.7, 243.4)  (243.4, 241.9, 243.5)518  813945  184943920  ...  (240.9, 240.8, 240.8)  (241.1, 240.5, 240.8)"""df_single_final = pd.merge(self.relation, self.raw_96_data_single, on='ID')df_single_final['DATA_TIME'] = pd.to_datetime(df_single_final['DATA_TIME'])df_triple_final = pd.merge(self.relation, self.raw_96_data_triple, on='ID')df_single_final['DATA_TIME'] = pd.to_datetime(df_single_final['DATA_TIME'])df = pd.concat([df_single_final, df_triple_final], axis=0).reset_index(drop=True)df['phase'] = Nonereturn df

2.2单相表相位识别

通过第一张描述的数据探索结果（假设），对所有单相表所属相位进行区分。

def get_electricity_meter_type(self):"""{182573380, 185643860, 182573139, ......, 182573141}{182926605, 184943920, 182430516, 182430517, 182430552, 182430493}:return:"""	df = self.e_mp_curve.groupby(by=['ID', pd.Grouper(key='DATA_TIME', freq='D')]).count()df = df.reset_index()df = df[df['UA'] == 96]single = set()triple = set()for index, row in df.iterrows():if row['UB'] == 0 and row['UC'] == 0:single.add(row['ID'])else:triple.add(row['ID'])return single, triple

2.3通过聚类算法识别出线个数

使用轮廓系数训练数据，得出系数最大只所对应的n_clusters为出线个数。

def cluster_algorithm(self, algorithm, phase, data):""":param algorithm::param data: 95维度的电压变化值数据:return: 返回值为（最适回路个数，对应的簇标）"""if algorithm == 'kmeans':score = []y_pred = []for i in range(3, 4):cluster = KMeans(n_clusters=i,init='k-means++',n_init=10,max_iter=300,tol=0.0001,precompute_distances='auto',verbose=0,random_state=None,copy_x=True,n_jobs=None,algorithm='auto').fit(data)score.append(silhouette_score(data, cluster.labels_, metric='euclidean'))y_pred.append(cluster.fit_predict(data))max_index = score.index(max(score))return_labels = list(map(lambda x: phase + str(x), y_pred[max_index]))return return_labelselse:passreturn None

2.4相位与回路整合

通过聚类找到每个相位上的三个回路，但这三个回路是逻辑上的，需要将这9个回复合并成三个回路，如：确定A相第一个回路 与 B相上的哪个回路在物理上是同一回路。

def mainfunc(self):df_data = self.reg_datadf_data['DATA_TIME'] = df_data['DATA_TIME'].astype(str)df_data = df_data.groupby(by=['TG_ID', 'DATA_TIME'])for data in df_data:"""TG_ID         ID  DATA_TIME    TYPE  ...    U95    U96 cluseter phase0  701026  182573177 2020-04-03  SINGLE  ...  237.6  239.4     None  None1  701026  182573278 2020-04-03  SINGLE  ...  239.1  240.4     None  None2  701026  182573297 2020-04-03  SINGLE  ...    239  240.2     None  None"""if data[0][0] != 813945 or data[0][1] != '2020-04-04':continuedata_single, data_triple = self.step1_spilt_singleandtriple(data)random_digit = random.randint(0, len(data_triple) - 1) one_triple_data = data_triple.loc[random_digit, 'U1':'U96'].tolist()       A = [] B = []  C = [] for i in one_triple_data:A.append(i[0])B.append(i[1])C.append(i[2])for index, row in data_single.iterrows():one_single_data = row['U1':'U96']corrcoef_value = [corrcoef(one_single_data, A), corrcoef(one_single_data, B),corrcoef(one_single_data, C)]id = corrcoef_value.index(max(corrcoef_value))           if id == 0: data_single.at[index, 'phase'] = 'A'elif id == 1:  data_single.at[index, 'phase'] = 'B'               else: data_single.at[index, 'phase'] = 'C'data_single_A = data_single[data_single['phase'] == 'A']arr_top_A = data_single_A.loc[:, 'U2':'U96'].valuesarr_bot_A = data_single_A.loc[:, 'U1':'U95'].valuesdata_arr_A = get_95_dim(arr_top_A, arr_bot_A)data_single_A['cluster'] = self.cluster_algorithm('kmeans', 'A', data_arr_A)data_single_B = data_single[data_single['phase'] == 'B']arr_top_B = data_single_B.loc[:, 'U2':'U96'].valuesarr_bot_B = data_single_B.loc[:, 'U1':'U95'].valuesdata_arr_B = get_95_dim(arr_top_B, arr_bot_B)data_single_B['cluster'] = self.cluster_algorithm('kmeans', 'B', data_arr_B)data_single_C = data_single[data_single['phase'] == 'C']arr_top_C = data_single_C.loc[:, 'U2':'U96'].valuesarr_bot_C = data_single_C.loc[:, 'U1':'U95'].valuesdata_arr_C = get_95_dim(arr_top_C, arr_bot_C)data_single_C['cluster'] = self.cluster_algorithm('kmeans', 'C', data_arr_C)data_single = pd.concat([data_single_A, data_single_B, data_single_C], axis=0).reset_index(drop=True)  data_cluster = data_single.groupby(by=['cluster', 'phase'])candidate = pd.DataFrame(columns=data_single.columns)for cluster_dataset in data_cluster:  #cluster_dataset = cluster_dataset[1].reset_index(drop=True)rand_int = random.randint(0, len(cluster_dataset) - 1)candidate = candidate.append(cluster_dataset.iloc[rand_int, :], ignore_index=True)candidate['cof'] = 0.0data_triple['cluster'] = Nonefor index, row in data_triple.iterrows():temp_data = row['U1':'U96'].tolist()A = [] B = [] C = []  for i in temp_data:A.append(i[0])B.append(i[1])C.append(i[2])for index_candidate, row_candidate in candidate.iterrows():candidate_data = row_candidate['U1':'U96'].valuesif row_candidate['phase'] == 'A':candidate.at[index_candidate, 'cof'] = corrcoef(candidate_data, A)elif row_candidate['phase'] == 'B':candidate.at[index_candidate, 'cof'] = corrcoef(candidate_data, B)else:candidate.at[index_candidate, 'cof'] = corrcoef(candidate_data, C)print(candidate.sort_values(by=['cof'], ascending=False, axis=0))

2.5电表在回路上位置顺序

按电表在回路上的电压进行倒排。

2.5.1初始排序

按回路分别处理，先取回路一上电表按4号A相电压进行倒序（数据库中所有单相表只有A相电压字段有值）。

2.5.2修正排序

2.6辅助函数：皮尔逊相关系数( Pearson correlation coefficient)

使用Pearson correlation coefficient计算曲线之间的相关性。

def multipl(a, b):sumofab = 0.0for i in range(len(a)):temp = a[i] * b[i]sumofab += tempreturn sumofabdef corrcoef(x, y):n = len(x)sum1, sum2 = sum(x), sum(y)sumofxy = multipl(x, y)sumofx2, sumofy2 = sum([pow(i, 2) for i in x]), sum([pow(j, 2) for j in y])num = sumofxy - (float(sum1) * float(sum2) / n)den = sqrt((sumofx2 - float(sum1 ** 2) / n) * (sumofy2 - float(sum2 ** 2) / n))return num / den

2.7通过添加接口代码实现自动绘图

3.待改进的部分

1.训练选取更合适的聚类及相似度算法，kmeans受初始值和离群点的影响，每次结果都不稳定，导致结果不够收敛。
2.目前使用python单线程进行计算，由于不同低压台区的拓扑关系完全独立，第一步可以改用多进程进行并发计算，第二步使用pyspark改写程序。
3.调用可视化绘图包，对产生的拓扑数据进行图形绘制。