How to classify the contents of two excel tables through python fuzzy matching algorithm

1. Problem description

During the internship, it is necessary to match and classify the contents of the two tables, such as for two different engineering projects The objects are all A, then these two engineering projects need to be classified into A. There are quite a lot of engineering projects and construction objects among them, so I thought of writing a program to automatically classify them. This can reduce a large part of the workload.

2. Application method

Since the two tables have similar keywords, that is, the content format of one table is project A, and the content format of the other table is unit A, then I just need to match the keyword "A" to filter it out. In this problem, I used a fuzzy matching algorithm to achieve my goal, but this algorithm is not the only possible solution.

3. Code writing

Note: Here we have imported the difflib library for using the fuzzy matching algorithm; the xlwt library for exporting excel tables

3.1

First we import the two excel tables that need to be processed.

df1=pd.read_excel(r'D:\杂货\项目.xlsx',sheet_name='Sheet1')
df2=pd.read_excel(r'D:\杂货\项目2.xlsx',sheet_name='Sheet1')#导入两个需要处理的excel表格

The content and format of the two tables are roughly as above. My requirement is to match and classify the engineering projects related to these two tables.

3.2

Put the two columns of data we want to process into a list.

for i in df1['XXXXXX改造']:#将这两列的数据存入list1和list2两个列表中
    list1.append(i)
for j in df2['XXXXXX新改']:
    list2.append(j)

3.3

Use the fuzzy matching algorithm to match the data content in list2 with the data content in list1 one by one.

for n in range(len(list2)):#通过模糊匹配算法，将list2与list1中的数据一一匹配，设置近似度为42%，得到匹配结果res
    query_word=str(list2[n])
    res=difflib.get_close_matches(query_word,list1,1,cutoff=0.42)
    res = "".join(res)
    listx.append(res)

It should be noted that the get_close_matches(query_word,list1,n,cutoff) method in the difflib library is called here, where query_word is the matched string; list1 is the list of strings to be matched. ; n is the return of the top n best matches, I set it to 1; cutoff is the matching degree, which is a floating point number in [0,1], which can also be called the degree of similarity between the two. This depends on personal needs. According to the specific problem, I set the similarity level to 0.42, which can successfully match the contents of the two tables that I need to match.

Since every result matched by res is in the form of a list, and we want to write the results into a new table, we need the results in string form, so we use res="".join(res ) method converts the list into string form, and then puts the result in string form into the listx list to facilitate writing to a new excel table.

3.4

Because I was worried that there would be missing matching results, I matched the data content in list1 with the data content in list2 one by one.

for m in range(len(list1)):#同上，将list1与list2的数据一一匹配
    query_word=str(list1[m])
    res=difflib.get_close_matches(query_word,list2,1,cutoff=0.42)
    res="".join(res)
    listy.append(res)

At this time, I set the matched string to the string in list1, and the string list to be matched to list2. The other parameters are the same, which is equivalent to saying that I first use table 1 to match table 2. , and then use Table 2 to match Table 1, so that the missing problem can be better solved.

3.5

Finally set the parameters of the new excel table

workbook=xlwt.Workbook(encoding='utf-8')#设定好新的excel表格的参数
worksheet=workbook.add_sheet('test_sheet')
worksheet.write(0,0,label='XXX改造')#从第0行第0列开始输入标签为XXX改造的数据
worksheet.write(0,1,label='XX金额')#从第0行第1列开始输入标签为XX金额的数据
worksheet.write(0,2,label='XXX新改')
worksheet.write(0,3,label='XX金额')
worksheet.write(0,4,label='已XXX金额')
 
for i in range(len(listx)):#写入运算出来的数据
    worksheet.write(i+1,0,label=listx[i])
for j in range(len(listy)):
    worksheet.write(j+1,2,label=listy[j])
for k in range(len(list1)):
    worksheet.write(k+1,1,label=list3[k])
for l in range(len(list2)):
    worksheet.write(l+1,3,label=list4[l])
    worksheet.write(l+1,4,label=list5[l])
workbook.save(r'D:\杂货\新项目6.xls')#导出excel表格

The method used here to write data content into the excel table will not be introduced in detail. People who have experience working with excel can easily understand the meaning of the code.

The final output table format is as follows:

# After two passes of matching, those with a high degree of mutual matching will appear in the table accordingly. , and if there is only a single high degree of matching, there will be data on the left but no data on the right, or there will be data on the right but no data on the left.

4. Code collection

import pandas as pd
import difflib
import xlwt#导入库
 
df1=pd.read_excel(r'D:\杂货\项目.xlsx',sheet_name='Sheet1')
df2=pd.read_excel(r'D:\杂货\项目2.xlsx',sheet_name='Sheet1')#导入两个需要处理的excel表格
 
list1=[]#设置空列表，用于存储2017年一列的数据
list2=[]#用于存储2018年一列的数据
list3=list(df1['XX金额'])#将excel表格中的列数据列表化
list4=list(df2['XX金额'])
list5=list(df2['XXX金额'])
listx=[]#用于存储匹配结果的数据
listy=[]#同上
for i in df1['XXXXXXXXX改造']:#将这两列的数据存入list1和list2两个列表中
    list1.append(i)
for j in df2['XXXXXXXXXXXXX新改']:
    list2.append(j)
 
for n in range(len(list2)):#通过模糊匹配算法，将list2与list1中的数据一一匹配，设置近似度为42%，得到匹配结果res
    query_word=str(list2[n])
    res=difflib.get_close_matches(query_word,list1,1,cutoff=0.42)
    res = "".join(res)
    listx.append(res)
 
for m in range(len(list1)):#同上，将list1与list2的数据一一匹配
    query_word=str(list1[m])
    res=difflib.get_close_matches(query_word,list2,1,cutoff=0.42)
    res="".join(res)
    listy.append(res)
 
workbook=xlwt.Workbook(encoding='utf-8')#设定好新的excel表格的参数
worksheet=workbook.add_sheet('test_sheet')
worksheet.write(0,0,label='XXXXXXXXX改造')
worksheet.write(0,1,label='XX金额')
worksheet.write(0,2,label='XXXXXXXXXXX新改')
worksheet.write(0,3,label='XX金额')
worksheet.write(0,4,label='XXX金额')
 
for i in range(len(listx)):#写入运算出来的数据
    worksheet.write(i+1,0,label=listx[i])
for j in range(len(listy)):
    worksheet.write(j+1,2,label=listy[j])
for k in range(len(list1)):
    worksheet.write(k+1,1,label=list3[k])
for l in range(len(list2)):
    worksheet.write(l+1,3,label=list4[l])
    worksheet.write(l+1,4,label=list5[l])
workbook.save(r'D:\杂货\新项目6.xls')#导出excel表格

The above is the detailed content of How to classify the contents of two excel tables through python fuzzy matching algorithm. For more information, please follow other related articles on the PHP Chinese website!