电商Python合并Excel生意参谋-访客数据

合并访客数据 ⽤于简单分析

import pandas as pdimport osimport re

path = \"./files/\"

files = os.listdir(path)

# ⽤于存放Excel，⾥⾯的每个元素类型为：pandas.core.frame.DataFramelist_excel = []

for filename in files:

fullname = path + filename# excel的相对路径

df = pd.read_excel(fullname)# 默认读取Excel的第⼀个表单

# 访问时间列 增加 ⽇期

m = re.search(\"(\\d{4}-\\d{1,2}-\\d{1,2})\ strdate = m.group(1)

df[\"访问时间\"] =['%s %s' % (strdate, s) for s in df[\"访问时间\"]]

col_name = df.columns.tolist() if '搜索关键字' not in col_name: # 插⼊列

index = col_name.index('⼊店来源') + 1 col_name.insert(index, '搜索关键字') df = df.reindex(columns = col_name)

# 修改值

df.loc[df['⼊店来源'].str.find('⼿淘搜索') > -1 , '搜索关键字'] = df['⼊店来源'].str.replace('⼿淘搜索','') df.loc[df['⼊店来源'].str.find('⼿淘搜索') > -1 , '⼊店来源'] = '⼿淘搜索' list_excel.append(df)# 把Excel追加到list中

writer = pd.ExcelWriter('test.xlsx')

# pd.concat：数据拼接# to_excel：写⼊到Excel

pd.concat(list_excel).to_excel(writer,'sheet1',index=False)writer.save()print('合并完成')