python implements an instance of excel's data cull operation

preface

In the process of learning Python, we will encounter various problems of Excel. The following article mainly introduces the relevant content of python's data culling operation on excel, and shares it for your reference and study. The following words are not enough, let's have a look at the detailed introduction.

When Python parses Excel, two packages need to be installed, namely xlrd (read excel) and xlwt (write excel). The installation method is as follows:

pip install xlrd
pip install xlwt

Demand analysis:

Determine whether a single field in excel2 table satisfies the condition, if so, query in excel1, and delete the data if it exists in excel.

Implementation of python script:

from __future__ import division
import pandas as pd
# Specifies the path to the file 
imputfile= 'C:\\Users\\Administrator\\Desktop\\excel1.xlsx' # The original table excel1
imputfile1= 'C:\\Users\\Administrator\\Desktop\\excel2.xls' #excel2
outputfile = 'C:\\Users\\Administrator\\Desktop\\result.xlsx' # The results of 
# read excel1 The data to the data
data = pd.read_excel(imputfile,encoding='utf-8') 
ex_list = list(data.iloc[:,1]) # Converts the fields that need to be compared to list In the form of 
# read excel2 The data to the remove_data
remove_data = pd.read_excel(imputfile1,encoding='utf-8')
# To find out excel2 Fields to be filtered meet the criteria. For example, I need to meet the following conditions: remove_data.iloc[i,7] ==' successful '
remove_phone=[] 
for i in range(0,len(remove_data)): 
 if remove_data.iloc[i,7] ==' successful ':
  phone = remove_data.iloc[i,3]
  remove_phone.append(phone)
# Deletes data that meets the criteria  
for i in range(0,len(remove_phone)): 
 ex_list.remove(remove_phone[i])
# Assigns the culled data to new_data
new_data=data[data.iloc[:,1].isin(ex_list)]
# export excel 
new_data.to_excel(outputfile)

Of course, such culling of excel data can also be directly implemented in excel. For example, we first sort excel2 and excel1 according to a certain 1-only field, and then copy the filtered results in excel2 into Excel1 and sort them directly according to this field in excel1.

Note: One drawback of this approach is that if the data in Excel2 is not complete, the ranking will not match that of excel1.

conclusion