pandas 的excel 查找合并

import numpy as np
import pandas as pd
import datetime
import win32com.client


def getYesterday():  #获取昨天日期
    today=datetime.date.today() 
    oneday=datetime.timedelta(days=1) 
    yesterday=today-oneday  
    return yesterday

def open_xls(xls):#打开表格
    ShiJian = getYesterday() #昨天
    #path ='C:\\Users\\Administrator\\Desktop\\每日流量\\'+str(ShiJian)+'PC本店数据.xls'
    path ='C:\\Users\\Administrator\\Desktop\\每日流量\\'+str(ShiJian)+str(xls)
    df = pd.read_excel(path)#导入到pandas
    df =pd.DataFrame(df[5:50000])#切片获取
    df.columns = ['流量来源','来源明细','访客数','访客数变化','下单买家数','下单买家数变化','下单转化率','下单转化率变化'] #重命名列表名称
    df['流量来源明细'] = df['流量来源']+df['来源明细']
    return (df)

def rm_notes(s):
    return s.replace(',','')

def add_to(x):
    y = 'PC' + str(x)
    return  y

def add_to2(z):
    y = 'WX' + str(z)
    return  y


def new_regroup(Pin,You,Ben):
    df_empty = pd.DataFrame(Pin[['流量来源明细']])

    df_empty['我们的访客数'] = df_empty['流量来源明细'].replace(Ben.set_index('流量来源明细')['访客数']).astype('int')
    df_empty['行业平均访客数'] = df_empty['流量来源明细'].replace(Pin.set_index('流量来源明细')['访客数']).map(rm_notes).astype('int')
    df_empty['行业优秀访客数'] = df_empty['流量来源明细'].replace(You.set_index('流量来源明细')['访客数']).map(rm_notes).astype('int')

    df_empty['我们的支付买家数'] = df_empty['流量来源明细'].replace(Ben.set_index('流量来源明细')['下单买家数']).astype('int')
    df_empty['行业平均支付买家数'] = df_empty['流量来源明细'].replace(Pin.set_index('流量来源明细')['下单买家数']).astype('int')
    df_empty['行业优秀支付买家数'] = df_empty['流量来源明细'].replace(You.set_index('流量来源明细')['下单买家数']).astype('int')


    df_empty['支付买家数平均值'] = df_empty['行业平均支付买家数'] / df_empty['行业平均访客数'] * df_empty['我们的访客数']
    df_empty['支付买家数优秀值'] = df_empty['行业优秀支付买家数'] / df_empty['行业优秀访客数'] * df_empty['我们的访客数']

    return (df_empty)



def name():
    PCBen = open_xls('PC本店数据.xls')
    PCPin = open_xls('PC同行平均数据.xls')
    PCYou = open_xls('PC同行优秀数据.xls')

    WXBen = open_xls('无线本店数据.xls')
    WXPin = open_xls('无线同行平均数据.xls')
    WXYou = open_xls('无线同行优秀数据.xls')


    PC = new_regroup(PCPin,PCYou,PCBen)
    WX = new_regroup(WXPin,WXYou,WXBen)
    PC['流量来源明细']= PC['流量来源明细'] .map(add_to)
    WX['流量来源明细']= WX['流量来源明细'] .map(add_to2)

    report = pd.concat([PC,WX])

    ShiJian = getYesterday() #昨天
    report_name = str(ShiJian)+'单天流量对比.xlsx'

    writer = pd.ExcelWriter(report_name)#新建一个表格
    report.to_excel(writer,'page_1',float_format='%.5f') # float_format 控制精度 将数据写入表格
    writer.save()#保存表格



name()