#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Wed Feb 15 11:22:32 2017

@author: apogee_tpaschke
"""

import pandas as pd 
import pickle 
import numpy as np 
import pprint

file = 'All_Advertisers_blacklist_weekly_report_IO_LI_2017-02-23-2212.csv'
output = 'Advertiser_Blacklist_TESTING'
cpa_count = 10
spend_count = 10



df = pd.read_csv('/Users/apogee_tpaschke/Downloads/'+file, error_bad_lines=False , low_memory = False)



#only keep the columns we care about 
keep_col = [ 'Insertion Order'
            ,'Advertiser'
            ,'Line Item'
            ,'App/URL'
            ,'Impressions'
            ,'Total Conversions'
            ,'Total Media Cost (Advertiser Currency)' ]


df = df[keep_col]
df = df[df['App/URL'] != 'Low volume inventory']
df = df[df['Impressions'] > 0.0]
df = df[df['Insertion Order'] != 'Retail Targeting | 2017 Q3']
df = df[df['Insertion Order'].str.contains('INV-NA')==False]
df = df[df['Line Item'].str.contains('PMP')==False]

#drop the bottom 16 rows that are extranious info about the report from DBM 
df = df.drop(df.index[[-1,-2,-3,-4,-5,-6,-7,-8,-9,-10,-11,-12,-13,-14,-15,-16]])


df.to_pickle('url_cpa')



#read in the data from pickle, remove 0's , "Low Volume Inventory"  and pmp line items
df = pd.read_pickle('url_cpa')
df.rename(columns ={'Total Media Cost (Advertiser Currency)':'Media Cost'}, inplace = True)


#Calculate CPA 
df['CPA'] = np.where(df['Total Conversions']== 0.0,0,(df['Media Cost']/df['Total Conversions']))

   
    
#Create dataframe for Betterment
bet = df.loc[(df.Advertiser == 'Apogee Digital - Betterment'), ['Advertiser','Line Item','App/URL','Impressions','Total Conversions','Media Cost','CPA']]
bet = bet.set_index('Advertiser')

#Create dataframe for betterment for advertisers 
ba = df.loc[(df.Advertiser == 'Apogee Digital - Betterment for Advisors'), ['Advertiser','Line Item','App/URL','Impressions','Total Conversions','Media Cost','CPA']]
ba = ba.set_index('Advertiser')

#Create dataframe for IB
ib = df.loc[(df['Advertiser'] == 'Apogee Digital - InteractiveBrokers'), ['Advertiser','Line Item','App/URL','Impressions','Total Conversions','Media Cost','CPA']]
ib = ib.set_index('Advertiser')

#Create dataframe for Webroot 
web = df.loc[(df['Advertiser'] == 'Apogee Digital - Webroot'), ['Advertiser','Line Item','App/URL','Impressions','Total Conversions','Media Cost','CPA']]
web = web.set_index('Advertiser')

#Create dataframe for VictorOps 
vo = df.loc[(df['Advertiser'] == 'Apogee Digital - VictorOps'), ['Advertiser','Line Item','App/URL','Impressions','Total Conversions','Media Cost','CPA']]
vo = vo.set_index('Advertiser')


#create dataframe for ButterLondon
bl = df.loc[(df['Advertiser'] == 'Apogee Digital - ButterLondon'), ['Advertiser','Line Item','App/URL','Impressions','Total Conversions','Media Cost','CPA']]
bl = bl.set_index('Advertiser')




def blacklist(df):
    cpa = df.nlargest(cpa_count, 'CPA')
    df = df[df['Total Conversions'] == 0]
    df = df.nlargest(spend_count, 'Media Cost')
    df = df.sort_values(by = 'Line Item')
    cpa = cpa.sort_values(by = 'Line Item')
    df = cpa.append(df)
    df = df[['Line Item','App/URL','Impressions','Media Cost','Total Conversions','CPA']]
    return df



# append all runs of each advertiser and output 
master = blacklist(bet).append(blacklist(ib).append(blacklist(web).append(blacklist(vo).append(blacklist(bl)))))

master.to_csv('Master.csv')







