问题描述
2018、2019、2020三年的客户数据各有不同,如何寻找每一年相较前一年客户的增加、减少,以及确定他们是谁,有助于分析客户流失。如图所示:
思路分析
采用交并补思想,通过交集、差集计算,如18年客户与18、19年客户交集的差集即为18-19年客户流失量,以此类推,共计求出19年客户增加、客户减少,20年客户增加、客户减少的具体名单。
利用python读取excel中客户列表作为list,通过对list进行交集、差集计算得到结果list并写入excel。
列表读取
获得2018、2019、2020三年的客户
import pandas as pd
import xlrd #excel文件读取
worksheet = xlrd.open_workbook(r'C:\Users\63045\Desktop\customer\customerpy.xlsx')
sheet1 = worksheet.sheet_by_name("2018")
sheet2 = worksheet.sheet_by_name("2019")
sheet3 = worksheet.sheet_by_name("2020")
rows1 = sheet1.nrows # 获取行数
rows2 = sheet2.nrows
rows3 = sheet3.nrows
list1 = []
list2 = []
list3 = []
for i in range(1,rows1) :
data1 = sheet1.cell_value(i, 0) # 取第1列数据
list1.append(data1)
print(list1)
for i in range(1,rows2) :
data2 = sheet2.cell_value(i, 0) # 取第2列数据
list2.append(data2)
print(list2)
for i in range(1,rows3) :
data3 = sheet3.cell_value(i, 0) # 取第3列数据
list3.append(data3)
print(list3)
列表交集计算
jiaoji1819=list(set(list1).intersection(set(list2)))
jiaoji1920=list(set(list2).intersection(set(list3)))
列表差集计算
jianshao20=list(set(list2).difference(set(jiaoji1920)))
zengjia20=list(set(list3).difference(set(jiaoji1920)))
print(jianshao19,
jianshao20,
zengjia19,
zengjia20)
将数据写入excel
import xlwt as xw
from xlutils.copy import copy
import xlrd as xr
file=r'C:\Users\63045\Desktop\customer\1.xlsx'
style = xw.easyxf()#字体风格设置,此处为默认值
oldwb = xr.open_workbook(file)#打开工作簿
newwb = copy(oldwb)#复制出一份新工作簿
newws = newwb.get_sheet(0)#获取指定工作表,0表示实际第一张工作表
for i in range(len(zengjia19)):
newws.write(i+1, 0, zengjia19[i],style) #把列表中的元素逐个写入第一列,0表示实际第1列,i+1表示实际第i+2行
for j in range(len(jianshao19)):
newws.write(j+1, 1, jianshao19[j],style) #把列表中的元素逐个写入第一列,1表示实际第2列,i+1表示实际第i+2行
for k in range(len(zengjia20)):
newws.write(k+1, 2, zengjia20[k],style) #把列表中的元素逐个写入第一列,2表示实际第3列,i+1表示实际第i+2行
for m in range(len(jianshao20)):
newws.write(m+1, 3, jianshao20[m],style) #把列表中的元素逐个写入第一列,3表示实际第4列,i+1表示实际第i+2行
newwb.save(file)
print('sucessful')
完整代码
#$ sudo chmod -R 777 myResources
import pandas as pd
import xlrd #excel文件读取
worksheet = xlrd.open_workbook(r'C:\Users\63045\Desktop\customer\customerpy.xlsx')
sheet1 = worksheet.sheet_by_name("2018")
sheet2 = worksheet.sheet_by_name("2019")
sheet3 = worksheet.sheet_by_name("2020")
rows1 = sheet1.nrows # 获取行数
rows2 = sheet2.nrows
rows3 = sheet3.nrows
list1 = []
list2 = []
list3 = []
for i in range(1,rows1) :
data1 = sheet1.cell_value(i, 0) # 取第1列数据
list1.append(data1)
print(list1)
for i in range(1,rows2) :
data2 = sheet2.cell_value(i, 0) # 取第2列数据
list2.append(data2)
print(list2)
for i in range(1,rows3) :
data3 = sheet3.cell_value(i, 0) # 取第3列数据
list3.append(data3)
print(list3)
jiaoji1819=list(set(list1).intersection(set(list2)))
jiaoji1920=list(set(list2).intersection(set(list3)))
jianshao19=list(set(list1).difference(set(jiaoji1819)))
zengjia19=list(set(list2).difference(set(jiaoji1819)))
jianshao20=list(set(list2).difference(set(jiaoji1920)))
zengjia20=list(set(list3).difference(set(jiaoji1920)))
print(jianshao19,
jianshao20,
zengjia19,
zengjia20)
# 将数据写入新文件
import xlwt as xw
from xlutils.copy import copy
import xlrd as xr
file=r'C:\Users\63045\Desktop\customer\1.xlsx'
style = xw.easyxf()#字体风格设置,此处为默认值
oldwb = xr.open_workbook(file)#打开工作簿
newwb = copy(oldwb)#复制出一份新工作簿
newws = newwb.get_sheet(0)#获取指定工作表,0表示实际第一张工作表
for i in range(len(zengjia19)):
newws.write(i+1, 0, zengjia19[i],style) #把列表中的元素逐个写入第一列,0表示实际第1列,i+1表示实际第i+2行
for j in range(len(jianshao19)):
newws.write(j+1, 1, jianshao19[j],style) #把列表中的元素逐个写入第一列,1表示实际第2列,i+1表示实际第i+2行
for k in range(len(zengjia20)):
newws.write(k+1, 2, zengjia20[k],style) #把列表中的元素逐个写入第一列,2表示实际第3列,i+1表示实际第i+2行
for m in range(len(jianshao20)):
newws.write(m+1, 3, jianshao20[m],style) #把列表中的元素逐个写入第一列,3表示实际第4列,i+1表示实际第i+2行
newwb.save(file)
print('sucessful')