import os
import pandas as pd
l = []
for root, dirs, files in os.walk('E:\shuju'):
for file in files:
if '店铺流量来源' in file:
day = file[-14: -4]
terminal = file[8: 10]
df = pd.read_excel(os.path.join(root, file),skiprows = 5)
df = df[['流量来源','来源明细','访客数','支付转化率']].loc[df['来源明细'] == '汇总'].drop(['来源明细'], axis = 1)
df = df.set_index('流量来源').stack().reset_index()
df.rename(columns = {'level_1': 'UV&CR', 0: 'num'}, inplace = True)
df['date'] = day
df['终端'] = terminal
l.append(df)
data = pd.concat(l,ignore_index=True)
data = data.set_index(['终端', '流量来源', 'UV&CR','date']).unstack()
data.columns = data.columns.droplevel(0)
data = data.reset_index()
list_sorted = ['淘内免费', '自主访问', '付费流量', '淘外流量', '其它', '淘外网站', '淘外APP', '其它来源']
data['流量来源'] = data['流量来源'].astype('category').cat.set_categories(list_sorted)
df_sortes = data.sort_values(by=['终端','流量来源','UV&CR'], ascending= (True, True, False))
empty2 = pd.DataFrame(columns=['终端', '流量来源', 'UV&CR',],index = range(2))
result = pd.concat([df_sortes[0:10], empty2, df_sortes[10:]], sort=False)
result.to_csv(r'E:\shuju\UV.csv',index = 0,encoding="utf_8_sig")
生意参谋流量来源数据处理
最新推荐文章于 2021-09-07 14:26:35 发布