def raw_pacing_and_dr(dr, pacing_data):
    sites = '|'.join(list(['ASG', 'Amazon', 'Magnetic', 'eBay']))
    cd = '|'.join(list(['C Pages', 'D Pages']))
    t2t = '|'.join(list(['Tablet to Tablet']))
    fbx = '|'.join(list(['FBX']))
    search = '|'.join(list(['Search']))
    pros = '|'.join(list(['Prospecting']))
    #Add-a-Line

    ddr = drop_columns(dr)

    pacing_dr = ddr.rename(columns={'Placement Messaging Type': 'Type', 'NTC Media Cost': 'Spend', 'Total GAs': 'GAs'})

    pacing_dr['Type.Agg'] = np.where(
        (pacing_dr['Type'].str.contains(cd) == True) & (pacing_dr['Type'].str.contains(fbx) == False), 'CD.Remessaging',
        np.where(pacing_dr['Type'].str.contains(t2t) == True, 'Tablet-to-Tablet',
                 np.where(pacing_dr['Type'].str.contains(fbx) == True, 'FBX.Remessaging',
                          np.where(pacing_dr['Type'].str.contains(search) == True, 'Search.Remessaging',
                                   np.where(pacing_dr['Type'].str.contains(pros) == True, 'Prospecting',
                                            'NA')))))

    pacing_dr = pacing_dr[(pacing_dr['Date'] >= main.quarter_start()) & (pacing_dr['Campaign'] == 'DR') & (
    pacing_dr['Site'].str.contains(sites) == True)]

    pacing_dr = pacing_dr.groupby(['Site', 'Type.Agg', 'Type', 'Date'])

    pacing_dr = pd.DataFrame(pacing_dr.sum()).reset_index()

    pacing_dr['Site_Type'] = pacing_dr['Site'] + '.' + pacing_dr['Type.Agg']

    pacing_pivoted = pd.pivot_table(pacing_dr, values=['Spend', 'GAs'], index=['Date'], columns=['Site_Type'],
                               aggfunc=np.sum)
    pacing_pivoted.fillna(0, inplace=True)

    spend = pacing_pivoted['Spend']
    gas = pacing_pivoted['GAs']

    spend.columns = pd.Series(spend.columns).astype(str) + '.Spend'
    gas.columns = pd.Series(gas.columns).astype(str) + '.GAs'

    merged = pd.merge(spend, gas, how= 'left', right_index= True, left_index= True)

    data_merged = pd.merge(merged, pacing_data, how='right', left_index=True, right_index=True)
    data_merged.reset_index(inplace=True)
    data_merged['Weekday'] = data_merged['Date'].apply(lambda x: arrow.get(x).format('dddd'))

    cols = ['ASG.Prospecting.Spend', 'ASG.Search.Remessaging.Spend', 'ASG.Prospecting.GAs',
            'ASG.Search.Remessaging.GAs']

    data_merged.drop(cols, axis= 1, inplace= True)

    return data_merged
def transform_dr(dr):
    dr = dr[dr['Campaign'] == 'DR']
    ddr = drop_columns(dr)

    cd, t2t, fbx, search, pros, aal = dr_placement_types()

    pacing_dr = ddr.rename(columns={'Placement Messaging Type': 'Type', 'NTC Media Cost': 'Spend', 'Total GAs': 'GAs'})

    pacing_dr['Type.Agg'] = np.where(
        pacing_dr['Type'].str.contains(cd) == True, 'CD.Remessaging',
        np.where(pacing_dr['Type'].str.contains(t2t) == True, 'Tablet-to-Tablet',
                 np.where(pacing_dr['Type'].str.contains(fbx) == True, 'FBX.Remessaging',
                          np.where(pacing_dr['Type'].str.contains(search) == True, 'Search.Remessaging',
                                   np.where(pacing_dr['Type'].str.contains(pros) == True, 'Prospecting',
                                            'Add-A-Line')))))

    pacing_dr = pacing_dr[(pacing_dr['Date'] >= main.quarter_start()) &
                          (pacing_dr['Site'].str.contains(dr_sites()) == True)]

    pacing_dr = pacing_dr.groupby(['Site', 'Type.Agg', 'Type', 'Date'])

    pacing_dr = pd.DataFrame(pacing_dr.sum()).reset_index()

    pacing_dr['Site_Type'] = pacing_dr['Site'] + '.' + pacing_dr['Type.Agg']

    pacing_pivoted = pd.pivot_table(pacing_dr, values=['Spend', 'GAs'], index=['Date'], columns=['Site_Type'],
                               aggfunc=np.sum)
    pacing_pivoted.fillna(0, inplace=True)

    spend = pacing_pivoted['Spend']
    gas = pacing_pivoted['GAs']

    spend.columns = pd.Series(spend.columns).astype(str) + '.Spend'
    gas.columns = pd.Series(gas.columns).astype(str) + '.GAs'

    merged = pd.merge(spend, gas, how= 'left', right_index= True, left_index= True)
    merged.reset_index(inplace=True)

    merged['Weekday'] = merged['Date'].apply(lambda x: arrow.get(x).format('dddd'))

    return merged
def pacing():
    a = '2015-09-28'
    b = '2015-10-04'
    c = '2015-10-05'
    d = '2015-12-27'
    e = '2015-12-28'
    f = '2015-12-31'

    date_rng = pd.date_range(main.quarter_start(), periods=92, freq='D')
    date_df = pd.DataFrame(date_rng, columns=['Date'])

    pacing_wb = Workbook(main.dr_pacing_path())
    pacing_wb.set_current()

    Application(wkb=pacing_wb).xl_app.Run('Clean_Pacing_Data')

    pacing_data = pd.DataFrame(Range('Q4 DDR Pacing by sub-tactic', 'Z1').table.value,
                               columns=Range('Q4 DDR Pacing by sub-tactic', 'Z1').horizontal.value)

    pacing_data.drop(0, inplace=True)

    Range('Q4 DDR Pacing by sub-tactic', 'Z1').table.clear_contents()
    pacing_wb.close()

    pacing_data.set_index('Week', inplace=True)
    pacing_data = pacing_data.resample('1D', fill_method='pad')

    pacing_data.fillna(method='ffill', inplace= True)
    pacing_data.reset_index(inplace=True)
    pacing_data.rename(columns={'Week': 'Date'}, inplace=True)

    pacing_data = pd.merge(pacing_data, date_df, how='right', left_on='Date', right_on='Date')
    pacing_data.fillna(method='ffill', inplace=True)

    pacing_data.ix[a:b] = pacing_data.ix[a:b] / 4
    pacing_data.ix[c:d] = pacing_data.ix[c:d] / 7
    pacing_data.ix[e:f] = pacing_data.ix[e:f] / 4

    pacing_data.set_index('Date', inplace= True)

    return pacing_data
def publishers(dr):
    cd, t2t, fbx, search, pros, aal = data_transform.dr_placement_types()
    week = week_of(dr)

    # Publisher Performance
    pub_dr = dr[(dr['Campaign'] == 'DR') & (dr['Date'] >= main.quarter_start())]
    pub_dr = pub_dr.groupby(['Site', 'Placement Messaging Type', 'Week', 'Date'])
    pub_dr = pd.DataFrame(pub_dr.sum()).reset_index()
    #pub_dr = pub_dr[(pub_dr['NTC Media Cost'] != 0)]

    pub_dr['Tactic'] = np.where((pub_dr['Placement Messaging Type'].str.contains(cd) == True) &
                                (pub_dr['Placement Messaging Type'].str.contains(fbx) != True), 'C/D Remessaging',
                                np.where(pub_dr['Placement Messaging Type'].str.contains(fbx) == True,
                                         'FBX Remessaging', pub_dr['Placement Messaging Type']))

    # Quarter
    q_dr = pub_dr[pub_dr['Date'] >= main.quarter_start()]
    q_dr = q_dr.groupby(['Site', 'Tactic'])
    q_dr = pd.DataFrame(q_dr.sum()).reset_index()

    q_dr = goals(q_dr)

    # Last Week
    last_week = pub_dr[pub_dr['Week'] == pub_dr['Week'].max()]
    last_week = last_week.groupby(['Site', 'Tactic'])
    last_week = pd.DataFrame(last_week.sum()).reset_index()

    last_week.rename(columns={'NET Media Cost': week}, inplace= True)

    # Publishers Overall
    sites = q_dr.groupby('Site')
    sites = pd.DataFrame(sites.sum()).reset_index()

    sites['CPGA'] = sites['NET Media Cost'] / sites['Total GAs']

    # Brand Remessaging

    br = dr[dr['Campaign'] == 'Brand Remessaging']
    br_quarter = br[br['Date'] >= main.quarter_start()]
    br_lw = br[br['Week'] == br['Week'].max()]

    br_quarter = br_quarter.groupby('Site')
    br_quarter = pd.DataFrame(br_quarter.sum().reset_index())
    br_quarter['Traffic Yield'] = br_quarter['Total Traffic Actions'].astype(float) / \
                                  br_quarter['Impressions'].astype(float)

    br_lw = br_lw.groupby('Site')
    br_lw = pd.DataFrame(br_lw.sum().reset_index())

    pacing_wb = Workbook(main.dr_pacing_path())
    pacing_wb.set_current()

    Range('Publisher Performance', 'A13', index=False, header=False).value = q_dr[
        ['Site', 'Tactic', 'Total GAs']]
    Range('Publisher Performance', 'E13', index= False, header= False).value = q_dr['NET Media Cost']

    Range('Publisher Performance', 'D13', index= False, header= False).value = last_week[week]
    Range('Publisher Performance', 'D12').value = week

    Range('Publisher Performance', 'A32', index= False, header= False).value = sites[['Site', 'Total GAs', 'CPGA']]

    Range('Publisher Performance', 'C7').value = pub_dr['Week'].max().strftime('%m/%d/%Y')

    Range('Publisher Performance', 'B45', index= False, header= False).value = \
        br_quarter[['Traffic Yield', 'Impressions', 'Total Traffic Actions']]

    Range('Publisher Performance', 'G45', index= False, header= False).value = br_quarter['NET Media Cost']

    Range('Publisher Performance', 'F45', index= False, header= False).value = br_lw['NET Media Cost']
    Range('Publisher Performance', 'F44').value = week

    pacing_wb.save()
    pacing_wb.close()