批量读取文件夹下xlsx的数据

提前设置好filelocation（存放要读取文件的文件夹），读取到的文件数据保存在all_data里，打印文件夹下xlsx文件总数，显示读取的文件数。

import glob
import pandas as pd
#读取文件的地址
filelocation=filelocation
# filelocation="/Users/quhongchao/Desktop/未命名文件夹 2/"  
#当前文件夹下搜索的文件名后缀  
fileform="xlsx"  
#首先查找默认文件夹下有多少文档需要整合  
filearray=[]  
for filename in glob.glob(filelocation+"*."+fileform):  
    filearray.append(filename)  
#以上是从pythonscripts文件夹下读取所有excel表格，并将所有的名字存储到列表filearray
print("在默认文件夹下有%d个文档"%len(filearray)) 
ge=len(filearray)  
matrix = [None]*ge  
#定义空的DataFrame，接收数据
all_data = pd.DataFrame()
#读取数据
for i in range(ge):
    print(i)
    l_data = pd.read_excel(filearray[i])
    all_data = all_data.append(l_data)

去掉列表中部分数值前面的特殊字符

all_data['商户姓名']=all_data['商户姓名'].apply(lambda x:x.replace('\n', '').replace('\r', ''))

时间的显示方式

data['交易时间'] = pd.to_datetime(
    data.交易时间, 
    format='%Y/%m/%d')

做汇总，t1 是第一次交易的时间，t2为最后一笔交易的时间。

money=data.groupby('ming')['交易金额'].sum()
t1=data.groupby('ming')['交易时间'].min()
t2=data.groupby('ming')['交易时间'].max()
h3=data.groupby('ming')['sn编码']
aaaa=[]
for i in h3:
    a1=i[1].tolist()
    b=a1[0]
    c=str(b)
    aaaa.append(c)

df={'名字':money.index,'sn编码':aaaa,"第一笔交易的时间":t1,"最后一笔交易的时间":t2,"累计刷卡金额":money}
DF=pd.DataFrame(df)

# t3=t2-t1
DF['活跃天数']=t2-t1
DF['活跃月数']=round(DF['活跃天数']/pd.Timedelta(days=30),1)
DF['平均月刷卡金额']=round(DF['累计刷卡金额']/DF['活跃月数'],1)
# DF['沉默天数']=pd.datetime.now()-DF['最后一笔交易的时间']
# DF['沉默天数']=pd.to_datetime("2021-9-1")-DF['最后一笔交易的时间']
DF['沉默天数']=pd.to_datetime(suanchentime)-DF['最后一笔交易的时间']
#组合列还原原始信息
DF['商户姓名']=DF['名字'].map(lambda x:x.split("&")[0])
DF['合伙人姓名']=DF['名字'].map(lambda x:x.split("&")[1])
DF['商户编号']=DF['名字'].map(lambda x:x.split("&")[2])
DF['合伙人编号']=DF['名字'].map(lambda x:x.split("&")[3])

#按照某列条件对另一列赋值
DF.loc[DF['活跃月数']<1,['平均月刷卡金额']] = 0
DF.loc[DF['活跃月数']<1,['备注']] = "——"

M=DF[DF["沉默天数"]>pd.Timedelta(days=60)]
H=DF[DF["沉默天数"]<pd.Timedelta(days=60)]

按照客户总数降序排列

CCC=CC.sort_values(by= ['客户总数'],ascending=False)

对合伙人、商户按照对应关系做年、月交易额汇总

qe1=pd.pivot_table(data,index=['合伙人姓名和编号','商户姓名和编号',
data.交易时间.map(lambda x:x.year),
data.交易时间.map(lambda x:x.month)],
values='交易金额',aggfunc=np.sum)

将数据存入Excel

with pd.ExcelWriter(savepath+name+'team.xlsx') as writer:
    str1 = ['活跃','沉默',"客户沉默率","月交易金额汇总"]
    HH.to_excel(writer, sheet_name= str1[0],index=False)
    MM.to_excel(writer, sheet_name= str1[1],index=False)
    CCC.to_excel(writer, sheet_name= str1[2],index=False)
#     qe1.to_excel(writer, sheet_name= str1[3])
writer.save()
writer.close()

将数据按照固定格式存储到Excel 中

导入需要的库

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter 
from openpyxl.styles import Font,Border,Side,PatternFill,Alignment
from openpyxl.styles import numbers
import datetime

读取excel文件

wb = load_workbook(savepath)#使用openpyxl读取xlsx文件，创建workbook
ws = wb.active

原本时间为“2020/12/28 14:27:51”形式，只显示年月日，
金额保留一位小数且以万为单位，
月数那一列在原本数字的后面加“个月‘两个字，
天数那一列保留整数加‘天’字

#定位表单
ws1 = wb["活跃"]

#改时间  改百分数显示
rownum1=str(ws1.max_row)
print(rownum1)
for i in range(1,int(rownum1)+1):
    ws1.cell(row=i, column=7).number_format ='yyyy-mm-dd'

for i in range(1,int(rownum3)+1):
    ws3.cell(row=i, column=7).number_format ='0%'
    
    
for i in range(1,int(rownum2)+1):
    ws1.cell(row=i, column=9).number_format ="0!.0,万" 
for i in range(1,int(rownum2)+1):
    ws1.cell(row=i, column=11).number_format ='General,个月'
for i in range(1,int(rownum2)+1):
    ws1.cell(row=i, column=12).number_format ='0!天'


for i in range(1,int(rownum2)+1):
    ws2.cell(row=i, column=6).number_format ='@'

设置行高列宽

# 设置行高列宽
ws1.row_dimensions[1].height=55

for i in range(2,int(rownum1)+1):
    ws1.row_dimensions[i].height=36
        
ws1.column_dimensions['A'].width=7

加边框线

# 边框线
border_set = Border(left=Side(border_style='thin', color='000000'),
                    right=Side(border_style='thin', color='000000'),
                    top=Side(border_style='thin', color='000000'),
                    bottom=Side(border_style='thin', color='000000'))

# 给ws2第一列数据加边框
col = ws2['A']
for cell in col:
    cell.border = border_set

for row in ws1['A2:H'+str(int(rownum1)+1)]:
    for cell in row:
        cell.alignment = Alignment(horizontal='center',vertical='center',wrap_text=True)

for row in ws4["A1:F"+rownum4]:
    for cell in row:
        cell.border = border_set

设置字体

font1 = Font(u'宋体',size = 12,bold=True,italic=False,strike=False,color='000000')#12B
font2 = Font(u'宋体',size = 14,bold=True,italic=False,strike=False,color="ff0000")# 14 红 B


#改字体格式 表头居中 ws3处理完
ws1['A1'].font = font1

for i in range(2,int(rownum1)+1):
    ws1.cell(row=i, column=1).font = font1

设置水平居中，垂直居中，自动换行

# 水平居中，垂直居中，自动换行
ws1['A1'].alignment = Alignment(horizontal='center',vertical='center',wrap_text=True)

合并单元格

ws3.merge_cells('A1:G1')

架构

数据预处理

for i in name:
    data[i].fillna('WWWWWWW',inplace=True)

生成tag形式的套着字典的列表，dengji，tuijian，hehuoren都是列表

tag = [{'id': 1,  'code': '1000', 'parentCode': 'null'},
       {'id': 2, 'code': '1010', 'parentCode': '1000'},
       {'id': 3, 'code': '1011', 'parentCode': '1000'},
       {'id': 4,  'code': '1111', 'parentCode': '1011'},
       {'id': 5,  'code': '1112', 'parentCode': '1011'},
       {'id': 6,  'code': '2000', 'parentCode': 'null'},
       {'id': 7,  'code': '2010', 'parentCode': '2000'},
       {'id': 8, 'code': '2111', 'parentCode': '2010'}]
zong=[]
for i in range(len(dengji)):
    dict1={'dengji':dengji[i],'tuijian':tuijian[i],'hehuoren':hehuoren[i]}
    zong.append(dict1)

以tag形式的数据为基础
生成层级结构的json树

def one(tag):
    pl =[]
    for i in tag:
        if i['tuijian'] == '喔**部&C004':
            i['children'] = two(tag,i)
            pl.append(i)
    return pl
#     print(pl)
def two(tag,parent):
    cl=[]
    for i in tag :
        m={}
        if i['tuijian'] == parent['hehuoren']:
            cl.append(i)
            i['children'] = two(tag,i)
    return cl
one(zong)

json 数据的保存与打开

#cunchu 
import json 
#baocun为要存储的数据
with open ("baocun.json",'w')as file_obj:
    json.dump(baocun,file_obj)
    
 #dakai 
 with open('')as file_obj:
     name=json.load(file_obj)
 print(name)

Excel 字母到序号的转化，列数和序号数的变化

from openpyxl.utils import get_column_letter, column_index_from_string
 
def to_excel序号_字母(数字):
    if isinstance(数字,str): return 数字
    return get_column_letter(数字)
def to_excel序号_数字(字母):
    if isinstance(字母, int): return 字母
    return column_index_from_string(字母)
 
def get_excel序号_列表(开头序号_字母或数字__包括开头,结尾序号_字母或数字__包括结尾,生成字母列表=True):
    开头序号 = to_excel序号_数字(开头序号_字母或数字__包括开头)
    结尾序号 = to_excel序号_数字(结尾序号_字母或数字__包括结尾)
    返回列表 = []
    for i in range(开头序号,结尾序号+1):
        返回列表.append(i)
    if 生成字母列表:
        返回列表 = stream(返回列表).map(lambda i: to_excel序号_字母(i)).collect()
    return 返回列表

设置列宽

for i in range(84):
    a=to_excel序号_字母(i+1)
    ws.column_dimensions[a].width=33

合并单元格

#合并第a列相同的信息,a为列号
def hebing(a):
        # 需要处理的文件
#     file = r'86ji.xlsx'
#     wb = load_workbook(file)
#     # 数据存放的工作表'Sheet1'
#     ws = wb['Sheet1']

    # 获取第一列数据
    type_list = []
    i = 2
    while True:
        r = ws.cell(i, a).value
        if r:
            type_list.append(r)
        else:
            break
        i += 1
#     print(type_list)
    # 判断合并单元格的始末位置
    start = 0
    end = 0
    flag = type_list[0]
#     print(flag)
    for i in range(len(type_list)):
        if type_list[i] != flag:
            flag = type_list[i]
            end = i - 1
            if end >= start:
                ws.merge_cells(to_excel序号_字母(a) + str(start + 2) + ":" +to_excel序号_字母(a)+ str(end + 2))
                start = end + 1
        if i == len(type_list) - 1:
            end = i
            ws.merge_cells(to_excel序号_字母(a) + str(start + 2) + ":"+to_excel序号_字母(a) + str(end + 2))

  

for i in range(83):
    print(i)
    hebing(i+1)

生成映射字典，将某些值替换

hehuoren3=D.合伙人编号姓名手机号.tolist()
hehuoren1=D.合伙人姓名.tolist()
dictmap=dict(zip(hehuoren3,hehuoren1))
a=f1.applymap(lambda s: dictmap.get(s) if s in dictmap else s)

D['bianma']=D['推荐'].map(lambda x:x.split("&")[-1])

字典的键值反转

#键值反转

reverse_dict1 = dict([(value,key) for (key,value) in dict1.item()])

将一列的值按照对应关系转换

D['bianma'] = D['bianma'].map(temp)

列换名字

D.rename(columns={'bianma':'tuijian'})