生意参谋流量来源数据处理

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")