#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Thu Feb 16 16:25:39 2017

@author: apogee_tpaschke
"""

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

file = 'All_Advertisers_blacklist_weekly_report_2017-04-10-1225.csv'
output = '/Users/apogee_tpaschke/Documents/python_outputs/Master_Blacklist.csv'
cpa_count = 10
spend_count = 20 


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]

# remove line items/IO's of PMP's and non discriptive apps 
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 and "Low Volume Inventory" 
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']))

#extract the number of unique advertisers from file passed in 
li = df.Advertiser.unique()


#This function creates a dataframe for each advertiser 
def dataframe(x):
    df = pd.read_pickle('url_cpa')
    df.rename(columns ={'Total Media Cost (Advertiser Currency)':'Media Cost'}, inplace = True)
    df = df[df['Impressions']> 1000]
    df = df[df['Advertiser'] == x]
    return df

#This function idetifies the blacklist domains by advertiser by line item 
def blacklist(df):
    df['CPA'] = np.where(df['Total Conversions']== 0.0,0,(df['Media Cost']/df['Total Conversions']))
    cpa = df[df['Total Conversions'] > 0.0]
    cpa = cpa.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[['Advertiser','Line Item','App/URL','Impressions','Media Cost','Total Conversions','CPA']]
    return df
 
    
bl = pd.DataFrame()

for i in li:
    bl = bl.append(blacklist(dataframe(i)))

bl.to_csv(output)



