# -*- coding: utf-8 -*-
"""
Created on Tue May 11 10:12:15 2021

@author: Administrator
"""

import os
from difflib import get_close_matches
import pandas as pd

path  = 'D:\_python小程序\OLT地址匹配'
os.chdir(path)

# 匹配列表中的字符串 （字符串， 列表， 最大匹配返回数， 相似度）
df_olt = pd.read_excel('大于200户OLT设备安装在基站上的清单表.xlsx')
df_ipran = pd.read_excel('IPRAN关键传输节点_输出.xlsx')


olt_address = list(df_olt['机房名称'][~df_olt['机房名称'].isnull()])
ipran_address = list(df_ipran['安装地址'][~df_ipran['安装地址'].isnull()])

matching_address = []
for add in olt_address:
    x = get_close_matches(add, ipran_address, n=1, cutoff=0.7)
    if x:
        matching_address.append(x)
    else:
        matching_address.append('无')

matching_address = list(map(lambda x:x[0]if isinstance(x,list) else x,matching_address))

dict_matching_address = {
    x:y for x,y in zip(olt_address, matching_address)
    }

df_olt['匹配IPRAN的地址'] = df_olt['机房名称'].map(dict_matching_address)

with pd.ExcelWriter('OLT与IPRAN安装地址匹配.xlsx') as f :
    df_olt.to_excel(f,index =False)