Python将EXCEL表格中的多个sheet中的一列提取合并，并去重后统计记录的个数

import numpy as np
import xlrd
import datetime
import time
import numpy as np
import pandas as pd

fp=‘D:/乔/工作簿5.xlsx’#原表的存储路径
worksheet = xlrd.open_workbook(fp)
sheet_names= worksheet.sheet_names()

#存储锁号
lock_code=[]
branch=[]
big_customer=[]
customer_type=[]

for sh in range(3,7):
sheet = worksheet.sheet_by_name(sheet_names[sh])
rows = sheet.nrows # 获取行数

for i in range(1,rows) :
#cell = sheet.cell_value(i,0) # 注意0表示第1列数据，1表示第二列数据
    branch.append(sheet.cell_value(i,0))  #获取表中第一列数据
    customer_type.append(sheet.cell_value(i,4))
    lock_code.append(sheet.cell_value(i,15))
    big_customer.append(sheet.cell_value(i,16))

zone=[]
lock=[]

for i in range(len(branch)):
if big_customer[i]==“否” and customer_type[i]!=“中介” and customer_type[i]!=“算量工作室” and customer_type[i]!=“财审” and customer_type[i]!=“监理”:
zone.append(branch[i])
lock.append(lock_code[i])

count={}
temp={}
h=list(temp.fromkeys(zone))
for i in zone:
count[i]=[]

zone_lock={}

for i in range(len(lock)):
count[zone[i]].append(lock[i])
print(count)

temp={}
for i in count.keys():
temp1=list(temp.fromkeys(count[i]))
count[i]=len(temp1)

print(count)
writer = pd.ExcelWriter(“新建.xlsx”)
data = pd.DataFrame(count,index=[0])

写入Excel文件，路径可以任意指定

data.to_excel(writer, ‘page_1’, float_format=’%.5f’)
writer.save()
writer.close()