#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Fri Mar 10 10:01:34 2017

@author: apogee_tpaschke
"""

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

file = 'daily_ROA$_report_2017-04-13-1516.csv'


day = file[18:28]
mtd = int(file[26:28])-1
days = mtd

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 = [ 'Date'
            ,'Advertiser'
            ,'Insertion Order'
            ,'Line Item Status'
            ,'Line Item'
            ,'Creative'
            ,'Impressions'
            ,'Total Conversions'
            ,'Total Media Cost (Advertiser Currency)'
            , 'DCM Post-Click Revenue'
            , 'DCM Post-View Revenue']

df = df[keep_col]
#df = df.drop(df.index[[-1,-2,-3,-4,-5,-6,-7,-8,-9,-10,-11,-12,-13,-14,-15,-16,-17,-18,-19,-20,-21,-22,-23,24,-25,-26,-27,-28]])
df = df.rename(columns={'Total Media Cost (Advertiser Currency)': 'Media Cost'})
df['Total Revenue'] = df['DCM Post-Click Revenue' ]+df['DCM Post-View Revenue']
df = df.drop(df[df.Date == 'Group By:'].index)

adv = df.groupby(['Date','Advertiser'], as_index=False).sum()

adv['ROA$'] = adv['Total Revenue'] / adv['Media Cost']
adv['CPA'] = adv['Media Cost']/adv['Total Conversions']
adv['Ave Product Cost'] = adv['Total Revenue']/adv['Total Conversions']

adv = adv[['Date','Advertiser','Impressions','Total Conversions','Media Cost','Total Revenue','Ave Product Cost','CPA','ROA$']]

#Create summary for Line Item 
LI = df.groupby(['Advertiser','Line Item'], as_index=False).sum()
LI['ROA$'] = LI['Total Revenue'] / LI['Media Cost']
LI['CPA'] = LI['Media Cost']/LI['Total Conversions']
LI['Ave Product Cost'] = LI['Total Revenue']/LI['Total Conversions']

LI = LI[['Advertiser','Line Item','Impressions','Total Conversions','Media Cost','Total Revenue','Ave Product Cost','CPA','ROA$']]


#Create subsets for each advertiser 
butter = adv.loc[adv['Advertiser'] == 'Apogee Digital - ButterLondon']
webroot = adv.loc[adv['Advertiser'] == 'Apogee Digital - Webroot']
blank = pd.DataFrame({'Date':['-----'],'Advertiser':['------------']})

butter_LI = LI.loc[LI['Advertiser'] == 'Apogee Digital - ButterLondon']
webroot_LI = LI.loc[LI['Advertiser'] == 'Apogee Digital - Webroot']

li = webroot_LI.tail(days).append(blank).append(butter_LI.tail(days))
#li = li[['Advertiser','Line Item','Impressions','Total Conversions','Media Cost','Total Revenue','Ave Product Cost','CPA','ROA$']]

roas = webroot.tail(days).append(blank).append(butter.tail(days))
#roas = roas[['Date','Advertiser','Impressions','Total Conversions','Media Cost','Total Revenue','Ave Product Cost','CPA','ROA$']]

final = roas.append(blank).append(blank).append(li)
final = final[['Date','Advertiser','Line Item','Impressions','Total Conversions','Media Cost','Total Revenue','Ave Product Cost','CPA','ROA$']]
final.to_csv('/Users/apogee_tpaschke/Documents/daily_roa$/'+'Daily_ROA$-'+day+'.csv')
#print(webroot)





