Example explanation of python practical implementation of excel reading, statistics and writing-Python Tutorial-php.cn

这篇文章主要介绍了关于python实战之实现excel读取、统计、写入的示例讲解，有着一定的参考价值，现在分享给大家，有需要的朋友可以参考一下

背景

图像领域内的一个国内会议快要召开了，要发各种邀请邮件，之后要录入、统计邮件回复（参会还是不参会等）。如此重要的任务，老师就托付给我了。ps: 统计回复邮件的时候，能知道谁参会或谁不参会。

而我主要的任务，除了录入邮件回复，就是统计理事和普通会员的参会情况了（参会的、不参会的、没回复的）。录入邮件回复信息没办法只能人工操作，但如果统计也要人工的话，那工作量就太大了（比如在上百人的列表中搜索另外上百人在不在此列表中！！），于是就想到了用python来帮忙，花两天时间不断修改，写了6个版本。。。

摘要

version_1 基本实现了excel读取、统计、显示功能，但问题也有不少，像显示出来后还要自已复制、粘贴到excel表，而且set中还有nan这样的bug。

version_2 相比较version_1而言，此版本用set代替list，可以自动去重。

version_3 解决了set中出现nan的bug，而且还加入的excel写入的功能，但一次只能写入一张表，所以要运行两次才能写入两张表（sheet）。

version_4 的改进在于将version_3中写入两张表格的操作，集成在一个程序里，只需要运行一次便可写入两张表，但也总是会写入两张表，万一你只想写入一张表呢？？

version_5 相对之前版本的最大改进在于将程序模块化，更具可读性了; 对修复set中出现nan的方法也进行了改进和简化; 而且可以自由控制写入多少张表了。

version_final 相比较version_5，修复了一个bug，之前需要先验知识，现在更通用一点（prep函数取代了set2list函数）。

version_1

基本实现了excel读取、统计、显示功能，但问题也有不少，像显示出来后还要自已复制、粘贴到excel表，而且set中还有nan这样的值。

#version_1
import os
import numpy as np
import pandas as pd
os.chdir(&#39;C:\\Users\\dell\\Desktop\\0711任务&#39;)
print(os.getcwd())
data = pd.read_excel(&#39;for_python.xlsx&#39;,&#39;Sheet2&#39;)
return_set = set(data[&#39;回执名单&#39;])
demand_set = set(data[&#39;理事名单&#39;])
answer_list = []
unanswer_list = []
for each in demand_set:
 if each in return_set:
 answer_list.append(each)
 else:
 unanswer_list.append(each)
notattend_set = set(data[&#39;回执名单&#39;][-15:])
nt = []
for each in notattend_set:
 if each in answer_list:
 nt.append(each)
def disp(ll, cap, num = True):
 print(cap)
 if num:
 for i, each in enumerate(ll):
  print(i+1,each)
 else:
 for each in enumerate(ll):
  print(each)
disp(answer_list,&#39;\n理事回执名单&#39;)
disp(unanswer_list,&#39;\n理事未回执名单&#39;)
disp(nt,&#39;\n理事回执说不参加名单&#39;)

Copy after login

version_2

相比较上一个版本，此版本用set代替list，可以自动去重。

#version_2
import os
import numpy as np
import pandas as pd
os.chdir(&#39;C:\\Users\\dell\\Desktop\\0711任务&#39;)
print(os.getcwd())
data = pd.read_excel(&#39;for_python.xlsx&#39;,&#39;Sheet2&#39;)
return_set = set(data[&#39;回执名单&#39;])
demand_set = set(data[&#39;理事名单&#39;])
answer_set = set([]) #理事回执名单
unanswer_set = set([]) #理事未回执名单
for each in demand_set:
 if each in return_set:
 answer_set.add(each)
 else:
 unanswer_set.add(each)
notattend_set = set(data[&#39;回执名单&#39;][-17:])
nt = set([]) #理事回执说不参加名单
for each in notattend_set:
 if each in answer_set:
 nt.add(each)
ans_att_set = answer_set - nt #理事回执参加名单
def disp(ss, cap, num = False):
 print(cap)
 if num:
 for i, each in enumerate(ss):
  print(i+1,each)
 else:
 for each in ss:
  print(each)
#disp(answer_set,&#39;\n理事回执名单&#39;)
disp(ans_att_set,&#39;\n理事回执说参加名单&#39;)
disp(nt,&#39;\n理事回执说不参加名单&#39;)
disp(unanswer_set,&#39;\n理事未回执名单&#39;)
print(len(ans_att_set),len(nt),len(unanswer_set))

Copy after login

version_3

此版本解决了set中出现nan的bug，而且还加入的excel写入的功能，但一次只能写入一张表，所以要运行两次才能写入两张表（sheet）。

step_1

import os
import numpy as np
import pandas as pd
os.chdir(&#39;C:\\Users\\dell\\Desktop&#39;)
print(&#39;work_directory: &#39;, os.getcwd())
data = pd.read_excel(&#39;理事与会员名单.xlsx&#39;,&#39;理事与会员名单&#39;)
#1.载入excel，得到三个名单
ans_attend_set = set(data[&#39;回执参加&#39;]) #回执参会名单
N = len(ans_attend_set)
ans_notatt_idx = [i for i in range(N) if type(data[&#39;回执不参加&#39;][i]) == np.float][0]
ans_notatt_set = set(data[&#39;回执不参加&#39;][:ans_notatt_idx])#回执不参会名单
concil_idx = [i for i in range(N) if type(data[&#39;理事名单&#39;][i]) == np.float][0]
concil_set = set(data[&#39;理事名单&#39;][:concil_idx])  #理事名单
#2.统计理事参会情况
concil_attend_set = set([]) #理事回执参会名单
concil_notatt_set = set([]) #理事回执不参会名单
concil_notans_set = set([]) #理事未回执名单
for each in concil_set:
 if each in ans_attend_set:
 concil_attend_set.add(each)
 elif each in ans_notatt_set:
 concil_notatt_set.add(each)
 else:
 concil_notans_set.add(each)
#3. 显示结果
def disp(ss, cap, num = True):
 #ss: 名单集合
 #cap: 开头描述
 print(cap,&#39;({})&#39;.format(len(ss)))
 for i in range(np.ceil(len(ss)/5).astype(int)):
 pre = i * 5
 nex = (i+1) * 5
 #调整显示格式
 dd = &#39;&#39;
 for each in list(ss)[pre:nex]:
  if len(each) == 2:
  dd = dd + &#39; &#39; + each
  elif len(each) == 3:
  dd = dd + &#39; &#39; + each
  else:
  dd = dd + &#39;&#39; + each
 print(&#39;{:3.0f} -{:3.0f} {}&#39;.format(i*5+1,(i+1)*5,dd))
disp(concil_attend_set,&#39;\n参会理事&#39;)
disp(concil_notatt_set,&#39;\n不参会理事&#39;)
disp(concil_notans_set,&#39;\n未回执理事&#39;)
#4. 将理事参会情况，写入excel
df = pd.DataFrame(list(concil_attend_set),columns = [&#39;参会理事&#39;])
df[&#39;&#39;]=pd.DataFrame([&#39;&#39;])
df[&#39;序号1&#39;] = pd.DataFrame(np.arange(len(concil_notatt_set))+1)
df[&#39;不参会理事&#39;] = pd.DataFrame(list(concil_notatt_set))
df[&#39;_&#39;]=pd.DataFrame([&#39;&#39;])
df[&#39;序号2&#39;] = pd.DataFrame(np.arange(len(concil_notans_set))+1)
df[&#39;未回执理事&#39;] = pd.DataFrame(list(concil_notans_set))
df.index = df.index + 1
df.to_excel(&#39;理事和会员回执统计.xlsx&#39;, sheet_name=&#39;理事回执统计&#39;)
print(&#39;\n\n写入excel成功~~&#39;)

Copy after login

step_2

import os
import numpy as np
import pandas as pd
os.chdir(&#39;C:\\Users\\dell\\Desktop&#39;)
print(&#39;work_directory: &#39;, os.getcwd())
data = pd.read_excel(&#39;理事与会员名单.xlsx&#39;,&#39;理事与会员名单&#39;)
#1.载入excel，得到三个名单
ans_attend_set = set(data[&#39;回执参加&#39;]) #回执参会名单
N = len(ans_attend_set)
ans_notatt_idx = [i for i in range(N) if type(data[&#39;回执不参加&#39;][i]) == np.float][0]
ans_notatt_set = set(data[&#39;回执不参加&#39;][:ans_notatt_idx])#回执不参会名单
mem_idx = [i for i in range(N) if type(data[&#39;被推荐人&#39;][i]) == np.float][0]
mem_set = set(data[&#39;被推荐人&#39;][:mem_idx])  #被推荐为会员代表名单
#2.统计会员参会情况
mem_attend_set = set([]) #回执参会会员
mem_notatt_set = set([]) #回执不参会会员
mem_notans_set = set([]) #未回执会员
for each in mem_set:
 if each in ans_attend_set:
 mem_attend_set.add(each)
 elif each in ans_notatt_set:
 mem_notatt_set.add(each)
 else:
 mem_notans_set.add(each)
#3. 显示结果
def disp(ss, cap, num = True):
 #ss: 名单集合
 #cap: 开头描述
 print(cap,&#39;({})&#39;.format(len(ss)))
 for i in range(np.ceil(len(ss)/5).astype(int)):
 pre = i * 5
 nex = (i+1) * 5
 #调整显示格式
 dd = &#39;&#39;
 for each in list(ss)[pre:nex]:
  if len(each) == 2:
  dd = dd + &#39; &#39; + each
  elif len(each) == 3:
  dd = dd + &#39; &#39; + each
  else:
  dd = dd + &#39;&#39; + each
 print(&#39;{:3.0f} -{:3.0f} {}&#39;.format(i*5+1,(i+1)*5,dd))
disp(mem_attend_set,&#39;\n参会会员&#39;)
disp(mem_notatt_set,&#39;\n不参会会员&#39;)
disp(mem_notans_set,&#39;\n未回执会员&#39;)
#4. 将会员参会情况，写入excel
if len(mem_attend_set) > len(mem_notans_set):
 print(&#39;#1&#39;)
 L = len(mem_attend_set)
 mem_notans_list = list(mem_notans_set)
 mem_notans_list.extend([&#39;&#39;] * (L - len(mem_notans_set)))
 mem_attend_list = list(mem_attend_set)
else:
 print(&#39;#2&#39;)
 L = len(mem_notans_set)
 mem_attend_list = list(mem_attend_set)
 mem_attend_list.extend([&#39;&#39;] * (L - len(mem_attend_set)))
 mem_notans_list = list(mem_notans_set) 
df = pd.DataFrame(mem_attend_list,columns = [&#39;参会会员&#39;])
df[&#39;&#39;]=pd.DataFrame([&#39;&#39;])
if len(mem_notatt_set) == 0:
 df[&#39;序号1&#39;] = np.NaN
 df[&#39;不参会会员&#39;] = np.NaN
else:
 df[&#39;序号1&#39;] = pd.DataFrame(np.arange(len(mem_notatt_set))+1)
 df[&#39;不参会会员&#39;] = pd.DataFrame(list(mem_notatt_set))
df[&#39;_&#39;]=pd.DataFrame([&#39;&#39;])
df[&#39;序号2&#39;] = pd.DataFrame(np.arange(len(mem_notans_set))+1)
df[&#39;未回执会员&#39;] = pd.DataFrame(mem_notans_list)
df.index = df.index + 1
df0 = pd.read_excel(&#39;理事和会员回执统计.xlsx&#39;,sheet_name=&#39;理事回执统计&#39;)
writer = pd.ExcelWriter(&#39;理事和会员回执统计.xlsx&#39;)
df0.to_excel(writer, sheet_name=&#39;理事回执统计&#39;)
df.to_excel(writer, sheet_name=&#39;会员回执统计&#39;)
writer.save()
print(&#39;\n\n写入excel成功~~&#39;)

Copy after login

version_4

version_4的改进在于将version_3中写入两张表格的操作，集成在一个程序里，只需要运行一次便可写入两张表，也总是会写入两张表。问题是要是你只想写入一张表呢？？

import os
import numpy as np
import pandas as pd
os.chdir(&#39;C:\\Users\\dell\\Desktop&#39;)
print(&#39;work_directory: &#39;, os.getcwd())
loadfile_sheet = [&#39;理事与会员名单.xlsx&#39;,&#39;理事与会员名单&#39;]
columns = [&#39;回执参加&#39;,&#39;回执不参加&#39;,&#39;理事&#39;,&#39;会员&#39;]
savefile_sheet = [&#39;理事和会员回执统计.xlsx&#39;,&#39;理事回执统计&#39;,&#39;会员回执统计&#39;]
display = [1,1]
def main(loadfile_sheet,columns,savefile_sheet,display):
 #1. 载入excel，得到名单
 data = pd.read_excel(loadfile_sheet[0],loadfile_sheet[1])
 def first_nan_index(pd):
 for i, each in enumerate(pd):
  if type(each) == np.float:
  return i
 return i
 idx = first_nan_index(data[columns[0]])
 ans_attend_set = set(data[columns[0]][:idx])#回执参会名单
 idx = first_nan_index(data[columns[1]])
 ans_notatt_set = set(data[columns[1]][:idx])#回执不参会名单
 idx = first_nan_index(data[columns[2]])
 concil_set = set(data[columns[2]][:idx])#理事名单
 idx = first_nan_index(data[columns[3]])
 mem_set = set(data[columns[3]][:idx])#会员名单
 #2. 统计参会情况
 concil_attend_set = set([]) #回执参会理事
 concil_notatt_set = set([]) #回执不参会理事
 concil_notans_set = set([]) #未回执理事
 for each in concil_set:
 if each in ans_attend_set:
  concil_attend_set.add(each)
 elif each in ans_notatt_set:
  concil_notatt_set.add(each)
 else:
  concil_notans_set.add(each)
 mem_attend_set = set([]) #回执参会会员
 mem_notatt_set = set([]) #回执不参会会员
 mem_notans_set = set([]) #未回执会员
 for each in mem_set:
 if each in ans_attend_set:
  mem_attend_set.add(each)
 elif each in ans_notatt_set:
  mem_notatt_set.add(each)
 else:
  mem_notans_set.add(each)
 #3. 是否显示中间结果 
 def disp(ss, cap, num = True):
 #ss: 名单集合
 #cap: 开头描述
 print(cap,&#39;({})&#39;.format(len(ss)))
 for i in range(np.ceil(len(ss)/5).astype(int)):
  pre = i * 5
  nex = (i+1) * 5
  #调整显示格式
  dd = &#39;&#39;
  for each in list(ss)[pre:nex]:
  if len(each) == 2:
   dd = dd + &#39; &#39; + each
  elif len(each) == 3:
   dd = dd + &#39; &#39; + each
  else:
   dd = dd + &#39;&#39; + each
  print(&#39;{:3.0f} -{:3.0f} {}&#39;.format(i*5+1,(i+1)*5,dd))
 if display[0]:
 disp(concil_attend_set,&#39;\n参会理事&#39;)
 disp(concil_notatt_set,&#39;\n不参会理事&#39;)
 disp(concil_notans_set,&#39;\n未回执理事&#39;)
 if display[1]:
 disp(mem_attend_set,&#39;\n参会会员&#39;)
 disp(mem_notatt_set,&#39;\n不参会会员&#39;)
 disp(mem_notans_set,&#39;\n未回执会员&#39;)
 #4. 写入excel
 def trans_pd(df,ss,cap,i=1):
 if len(ss) == 0:
  df[&#39;序号{}&#39;.format(i)] = np.NaN
  df[cap] = np.NaN
 else:
  df[&#39;序号{}&#39;.format(i)] = pd.DataFrame(np.arange(len(ss))+1)
  df[cap] = pd.DataFrame(list(ss))
 df[&#39;_&#39;*i]=pd.DataFrame([&#39;&#39;])
 return df
 def set2list(mem_attend_set,mem_notans_set):
 if len(mem_attend_set) > len(mem_notans_set):
  L = len(mem_attend_set)
  mem_notans_list = list(mem_notans_set)
  mem_notans_list.extend([&#39;&#39;] * (L - len(mem_notans_set)))
  mem_attend_list = list(mem_attend_set)
 else:
  L = len(mem_notans_set)
  mem_attend_list = list(mem_attend_set)
  mem_attend_list.extend([&#39;&#39;] * (L - len(mem_attend_set)))
  mem_notans_list = list(mem_notans_set)
 return mem_attend_list,mem_notans_list
 mem_attend_list, mem_notans_list = set2list(mem_attend_set, mem_notans_set) 
 df1 = pd.DataFrame(mem_attend_list,columns = [&#39;参会会员&#39;])
 df1[&#39;&#39;]=pd.DataFrame([&#39;&#39;])
 df1 = trans_pd(df1,mem_notatt_set,&#39;不参会会员&#39;)
 df1 = trans_pd(df1,mem_notans_set,&#39;未回执会员&#39;,2)
 df1.index = df1.index + 1
 concil_attend_list, concil_notans_list = set2list(concil_attend_set, concil_notans_set)
 df2 = pd.DataFrame(concil_attend_list,columns = [&#39;参会理事&#39;])
 df2[&#39;&#39;]=pd.DataFrame([&#39;&#39;])
 df2 = trans_pd(df2,concil_notatt_set,&#39;不参会理事&#39;)
 df2 = trans_pd(df2,concil_notans_list,&#39;未回执理事&#39;,2)
 df2.index = df2.index + 1
 writer = pd.ExcelWriter(savefile_sheet[0])
 df2.to_excel(writer, sheet_name=savefile_sheet[1])
 df1.to_excel(writer, sheet_name=savefile_sheet[2])
 writer.save()
 print(&#39;\n\n写入excel成功~~&#39;)
if __name__ == &#39;__main__&#39;:
 main(loadfile_sheet,columns,savefile_sheet,display)

Copy after login

version_5

version_5对修复set中出现nan的方法进行了改进和简化; 而且将程序模块化，更具可读性; 可以自由控制写入多少张表了。

import os
import numpy as np
import pandas as pd
os.chdir(&#39;C:\\Users\\dell\\Desktop&#39;)
print(&#39;work_directory: &#39;, os.getcwd())
loadfile_sheet = [&#39;理事与会员名单.xlsx&#39;,&#39;理事与会员名单&#39;]
common_columns = [&#39;回执参加&#39;,&#39;回执不参加&#39;]
concerned_columns = [&#39;理事&#39;,&#39;会员&#39;]
disp_columns = [&#39;参会&#39;,&#39;不参会&#39;,&#39;未回执&#39;]
savefile_sheet = [&#39;理事和会员回执统计.xlsx&#39;,&#39;理事回执统计&#39;,&#39;会员回执统计&#39;]
def disp(ss, cap, num = True):
 #ss: 名单集合
 #cap: 开头描述
 print(cap,&#39;({})&#39;.format(len(ss)))
 for i in range(np.ceil(len(ss)/5).astype(int)):
 pre = i * 5
 nex = (i+1) * 5
 #调整显示格式
 dd = &#39;&#39;
 for each in list(ss)[pre:nex]:
  if len(each) == 2:
  dd = dd + &#39; &#39; + each
  elif len(each) == 3:
  dd = dd + &#39; &#39; + each
  else:
  dd = dd + &#39;&#39; + each
 print(&#39;{:3.0f} -{:3.0f} {}&#39;.format(i*5+1,(i+1)*5,dd))
def trans_pd(df,ss,cap,i=1):
 df[&#39;_&#39;*i]=pd.DataFrame([&#39;&#39;])
 if len(ss) == 0:
 df[&#39;序号{}&#39;.format(i)] = np.NaN
 df[cap] = np.NaN
 else:
 df[&#39;序号{}&#39;.format(i)] = pd.DataFrame(np.arange(len(ss))+1)
 df[cap] = pd.DataFrame(list(ss)) 
 return df
def set2list(ss1,ss2):
 if len(ss1) > len(ss2):
 L = len(ss1)
 ss2_list = list(ss2)
 ss2_list.extend([&#39;&#39;] * (L - len(ss2)))
 ss1_list = list(ss1)
 else:
 L = len(ss2)
 ss1_list = list(ss1)
 ss1_list.extend([&#39;&#39;] * (L - len(ss1)))
 ss2_list = list(ss2)
 return ss1_list,ss2_list 
def get_df(loadfile_sheet,common_columns,concerned_column,disp_columns, display = True):
 #1. 载入excel
 data = pd.read_excel(loadfile_sheet[0],loadfile_sheet[1])
 common_set1 = set(data[common_columns[0]])
 common_set1.discard(np.NaN)
 common_set2 = set(data[common_columns[1]])
 common_set2.discard(np.NaN)
 concerned_set = set(data[concerned_column])
 concerned_set.discard(np.NaN)
 #2. 统计
 concerned_in_set_1 = set([])
 concerned_in_set_2 = set([])
 concerned_in_no_set = set([])
 for each in concerned_set:
 if each in common_set1:
  concerned_in_set_1.add(each)
 elif each in common_set2:
  concerned_in_set_2.add(each)
 else:
  concerned_in_no_set.add(each)
 #3. 显示
 if display:
 disp(concerned_in_set_1,&#39;\n&#39;+disp_columns[0]+concerned_column)
 disp(concerned_in_set_2,&#39;\n&#39;+disp_columns[1]+concerned_column)
 disp(concerned_in_no_set,&#39;\n&#39;+disp_columns[2]+concerned_column)
 #4. 返回DataFrame
 concerned_in_set_1_list, concerned_in_set_2_list = set2list(concerned_in_set_1, concerned_in_no_set) 
 df = pd.DataFrame(concerned_in_set_1_list,columns = [disp_columns[0]])
 df = trans_pd(df,concerned_in_set_2,disp_columns[1])
 df = trans_pd(df,concerned_in_no_set,disp_columns[2],2)
 df.index = df.index + 1
 return df
def save2excel(df, concerned_column, savefile_sheet):
 L = len(savefile_sheet) - 1
 idx = 0
 for i in np.arange(L)+1:
 if concerned_column in savefile_sheet[i]:
  idx = i
  break
 if idx != 0:  
 names = locals()
 for i in np.arange(L)+1:
  if i != idx:
  names[&#39;df%s&#39; % i] = pd.read_excel(savefile_sheet[0], sheet_name=savefile_sheet[i])
 writer = pd.ExcelWriter(savefile_sheet[0])
 for i in np.arange(L)+1:
  if i != idx:
  names[&#39;df%s&#39; % i].to_excel(writer, sheet_name=savefile_sheet[i])
  else:
  df.to_excel(writer, sheet_name=savefile_sheet[i])
 writer.save()
 else:  
 names = locals()
 for i in np.arange(L)+1:
  names[&#39;df%s&#39; % i] = pd.read_excel(savefile_sheet[0], sheet_name=savefile_sheet[i])
 writer = pd.ExcelWriter(savefile_sheet[0])
 for i in np.arange(L)+1:
  names[&#39;df%s&#39; % i].to_excel(writer, sheet_name=savefile_sheet[i])
 df.to_excel(writer, sheet_name=concerned_column)
 writer.save()
 print(&#39;writing success&#39;)
if __name__ == &#39;__main__&#39;:
 for concerned_column in concerned_columns:
 df = get_df(loadfile_sheet,common_columns,
   concerned_column,disp_columns, display = True)
 save2excel(df, concerned_column, savefile_sheet)

Copy after login

version_final

相比较version_5，修复了一个bug，之前需要先验知识，现在更通用一点（prep函数取代了set2list函数）。

import os
import numpy as np
import pandas as pd
os.chdir(&#39;C:\\Users\\dell\\Desktop&#39;)
print(&#39;work_directory: &#39;, os.getcwd())
loadfile_sheet = [&#39;理事与会员名单.xlsx&#39;,&#39;理事与会员名单&#39;]
common_columns = [&#39;回执参加&#39;,&#39;回执不参加&#39;]
concerned_columns = [&#39;理事&#39;,&#39;会员&#39;]
disp_columns = [&#39;参会&#39;,&#39;不参会&#39;,&#39;未回执&#39;]
savefile_sheet = [&#39;理事和会员回执统计.xlsx&#39;,&#39;理事回执统计&#39;,&#39;会员回执统计&#39;]
def disp(ss, cap, num = True):
 #功能：显示名单
 #ss : 名单集合
 #cap ：开头描述
 print(cap,&#39;({})&#39;.format(len(ss)))
 for i in range(np.ceil(len(ss)/5).astype(int)):
 pre = i * 5
 nex = (i+1) * 5
 #调整显示格式
 dd = &#39;&#39;
 for each in list(ss)[pre:nex]:
  if len(each) == 2:
  dd = dd + &#39; &#39; + each
  elif len(each) == 3:
  dd = dd + &#39; &#39; + each
  else:
  dd = dd + &#39;&#39; + each
 print(&#39;{:3.0f} -{:3.0f} {}&#39;.format(i*5+1,(i+1)*5,dd))
def trans_pd(df,ll,cap,i=1):
 #功能：生成三列--空列、序号列、数据列
 #df : DataFrame结构
 #ll : 列表
 #cap : 显示的列名
 #i : 控制空列的名字
 df[&#39;_&#39;*i]=pd.DataFrame([&#39;&#39;])
 if len(set(ll)) == 1:
 df[&#39;序号{}&#39;.format(i)] = np.NaN
 df[cap] = np.NaN
 else:
 df[&#39;序号{}&#39;.format(i)] = pd.DataFrame(np.arange(len(set(ll))-1)+1)
 df[cap] = pd.DataFrame(ll) 
 return df
def prep(ss, N):
 #功能：预处理，生成列表，并补齐到长度N
 #ss : 集体
 #N ：长度
 ll = list(ss)
 L = len(ll)
 ll.extend([np.NaN] * (N-L))
 return ll
def get_df(loadfile_sheet,common_columns,concerned_column,disp_columns, display = True):
 #1. 载入excel
 data = pd.read_excel(loadfile_sheet[0],loadfile_sheet[1]) 
 common_set1 = set(data[common_columns[0]])
 common_set2 = set(data[common_columns[1]]) 
 concerned_set = set(data[concerned_column])
 common_set1.discard(np.NaN)
 common_set2.discard(np.NaN)
 concerned_set.discard(np.NaN)
 #2. 统计
 concerned_in_set_1 = set([])
 concerned_in_set_2 = set([])
 concerned_in_no_set = set([])
 for each in concerned_set:
 if each in common_set1:
  concerned_in_set_1.add(each)
 elif each in common_set2:
  concerned_in_set_2.add(each)
 else:
  concerned_in_no_set.add(each)
 #3. 显示
 if display:
 disp(concerned_in_set_1,&#39;\n&#39;+disp_columns[0]+concerned_column)
 disp(concerned_in_set_2,&#39;\n&#39;+disp_columns[1]+concerned_column)
 disp(concerned_in_no_set,&#39;\n&#39;+disp_columns[2]+concerned_column)
 #4. 返回DataFrame
 N = np.max([len(concerned_in_set_1),len(concerned_in_set_2),len(concerned_in_no_set)])
 concerned_in_set_1_list = prep(concerned_in_set_1,N)
 concerned_in_set_2_list = prep(concerned_in_set_2,N)
 concerned_in_no_list = prep(concerned_in_no_set,N)
 df = pd.DataFrame(concerned_in_set_1_list,columns = [disp_columns[0]])
 df = trans_pd(df,concerned_in_set_2_list,disp_columns[1])
 df = trans_pd(df,concerned_in_no_list,disp_columns[2],2)
 df.index = df.index + 1
 return df
def save2excel(df, concerned_column, savefile_sheet):
 L = len(savefile_sheet) - 1
 idx = 0
 for i in np.arange(L)+1:
 if concerned_column in savefile_sheet[i]:
  idx = i
  break
 if idx != 0: #如果有对应sheet  
 names = locals()
 for i in np.arange(L)+1:
  if i != idx:
  names[&#39;df%s&#39; % i] = pd.read_excel(savefile_sheet[0], sheet_name=savefile_sheet[i])
 writer = pd.ExcelWriter(savefile_sheet[0])
 for i in np.arange(L)+1:
  if i != idx:
  names[&#39;df%s&#39; % i].to_excel(writer, sheet_name=savefile_sheet[i])
  else:
  df.to_excel(writer, sheet_name=savefile_sheet[i])
 writer.save()
 else: #如果没有对应sheet，创建一个新sheet  
 names = locals()
 for i in np.arange(L)+1:
  names[&#39;df%s&#39; % i] = pd.read_excel(savefile_sheet[0], sheet_name=savefile_sheet[i])
 writer = pd.ExcelWriter(savefile_sheet[0])
 for i in np.arange(L)+1:
  names[&#39;df%s&#39; % i].to_excel(writer, sheet_name=savefile_sheet[i])
 df.to_excel(writer, sheet_name=concerned_column)
 writer.save()
 print(&#39;writing success&#39;)
if __name__ == &#39;__main__&#39;:
 for concerned_column in concerned_columns:
 df = get_df(loadfile_sheet,common_columns,
   concerned_column,disp_columns, display = True)
 save2excel(df, concerned_column, savefile_sheet)

Copy after login