#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Fri Apr  7 14:26:53 2017

@author: apogee_tpaschke
"""

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

file = '46002_query_20170407_143519_546092149.csv'
days = 15
day = file[18:28]


df = pd.read_csv('/Users/apogee_tpaschke/Downloads/'+file, skiprows = 23,  error_bad_lines=False, low_memory=False )
df = pd.DataFrame(df)
df = df.drop(df.index[[-1]])

keep_col = ['Conversion ID',
'Activity',
'Activity Group',
'Impression Count',
'Path Length',
'Days Since First Interaction',
'Hours Since Attributed Interaction',
'UserReportedWealthCategory',	
'LTV',
'Interaction 1: Placement',
'Interaction 2: Placement',
'Interaction 3: Placement',
'Interaction 4: Placement',
'Interaction 5: Placement',
'Interaction 6: Placement',
'Interaction 7: Placement',
'Interaction 8: Placement',
'Interaction 9: Placement',
'Interaction 10: Placement',
'Interaction 11: Placement',
'Interaction 12: Placement',
'Interaction 13: Placement',
'Interaction 14: Placement',
'Interaction 15: Placement',
'Interaction 16: Placement',
'Interaction 17: Placement',
'Interaction 18: Placement',
'Interaction 19: Placement',
'Interaction 20: Placement',
'Interaction 21: Placement',
'Interaction 22: Placement',
'Interaction 23: Placement',
'Interaction 24: Placement',
'Interaction 25: Placement',
'Interaction 26: Placement',
'Interaction 27: Placement',
'Interaction 28: Placement',
'Interaction 29: Placement',
'Interaction 30: Placement',
'Interaction 31: Placement',
'Interaction 32: Placement',
'Interaction 33: Placement',
'Interaction 34: Placement',
'Interaction 35: Placement',
'Interaction 36: Placement',
'Interaction 37: Placement',
'Interaction 38: Placement',
'Interaction 39: Placement',
'Interaction 40: Placement',
'Interaction 41: Placement',
'Interaction 42: Placement',
'Interaction 43: Placement',
'Interaction 44: Placement',
'Interaction 45: Placement',
'Interaction 46: Placement',
'Interaction 47: Placement',
'Interaction 48: Placement',
'Interaction 49: Placement',
'Interaction 50: Placement',
'Interaction 51: Placement',
'Interaction 52: Placement',
'Interaction 53: Placement',
'Interaction 54: Placement',
'Interaction 55: Placement',
'Interaction 56: Placement',
'Interaction 57: Placement',
'Interaction 58: Placement',
'Interaction 59: Placement',
'Interaction 60: Placement',
'Interaction 61: Placement',
'Interaction 62: Placement',
'Interaction 63: Placement',
'Interaction 64: Placement',
'Interaction 65: Placement',
'Interaction 66: Placement',
'Interaction 67: Placement',
'Interaction 68: Placement',
'Interaction 69: Placement',
'Interaction 70: Placement',
'Interaction 71: Placement',
'Interaction 72: Placement',
'Interaction 73: Placement',
'Interaction 74: Placement',
'Interaction 75: Placement',
'Interaction 76: Placement',
'Interaction 77: Placement',
'Interaction 78: Placement',
'Interaction 79: Placement',
'Interaction 80: Placement',
'Interaction 81: Placement',
'Interaction 82: Placement',
'Interaction 83: Placement',
'Interaction 84: Placement',
'Interaction 85: Placement',
'Interaction 86: Placement',
'Interaction 87: Placement',
'Interaction 88: Placement',
'Interaction 89: Placement',
'Interaction 90: Placement',
'Interaction 91: Placement',
'Interaction 92: Placement',
'Interaction 93: Placement',
'Interaction 94: Placement',
'Interaction 95: Placement',
'Interaction 96: Placement',
'Interaction 97: Placement',
'Interaction 98: Placement',
'Interaction 99: Placement',
'Interaction 100: Placement',
'Interaction 101: Placement',
'Interaction 102: Placement',
'Interaction 103: Placement',
'Interaction 104: Placement',
'Interaction 105: Placement',
'Interaction 106: Placement',
'Interaction 107: Placement',
'Interaction 108: Placement',
'Interaction 109: Placement',
'Interaction 110: Placement',
'Interaction 111: Placement',
'Interaction 112: Placement',
'Interaction 113: Placement',
'Interaction 114: Placement',
'Interaction 115: Placement',
'Interaction 116: Placement',
'Interaction 117: Placement',
'Interaction 118: Placement',
'Interaction 119: Placement',
'Interaction 120: Placement',
'Interaction 121: Placement',
'Interaction 122: Placement',
'Interaction 123: Placement',
'Interaction 124: Placement',
'Interaction 125: Placement',
'Interaction 126: Placement',
'Interaction 127: Placement',
'Interaction 128: Placement',
'Interaction 129: Placement',
'Interaction 130: Placement',
'Interaction 131: Placement',
'Interaction 132: Placement',
'Interaction 133: Placement',
'Interaction 134: Placement',
'Interaction 135: Placement',
'Interaction 136: Placement',
'Interaction 137: Placement',
'Interaction 138: Placement',
'Interaction 139: Placement',
'Interaction 140: Placement',
'Interaction 141: Placement',
'Interaction 142: Placement',
'Interaction 143: Placement',
'Interaction 144: Placement',
'Interaction 145: Placement',
'Interaction 146: Placement',
'Interaction 147: Placement',
'Interaction 148: Placement',
'Interaction 149: Placement',
'Interaction 150: Placement',
'Interaction 151: Placement',
'Interaction 152: Placement',
'Interaction 153: Placement',
'Interaction 154: Placement',
'Interaction 155: Placement',
'Interaction 156: Placement',
'Interaction 157: Placement',
'Interaction 158: Placement',
'Interaction 159: Placement',
'Interaction 160: Placement',
'Interaction 161: Placement',
'Interaction 162: Placement',
'Interaction 163: Placement',
'Interaction 164: Placement',
'Interaction 165: Placement',
'Interaction 166: Placement',
'Interaction 167: Placement',
'Interaction 168: Placement',
'Interaction 169: Placement',
'Interaction 170: Placement',
'Interaction 171: Placement',
'Interaction 172: Placement',
'Interaction 173: Placement',
'Interaction 174: Placement',
'Interaction 175: Placement',
'Interaction 176: Placement',
'Interaction 177: Placement',
'Interaction 178: Placement',
'Interaction 179: Placement',
'Interaction 180: Placement',
'Interaction 181: Placement',
'Interaction 182: Placement',
'Interaction 183: Placement',
'Interaction 184: Placement',
'Interaction 185: Placement',
'Interaction 186: Placement',
'Interaction 187: Placement',
'Interaction 188: Placement',
'Interaction 189: Placement',
'Interaction 190: Placement',
'Interaction 191: Placement',
'Interaction 192: Placement',
'Interaction 193: Placement',
'Interaction 194: Placement',
'Interaction 195: Placement',
'Interaction 196: Placement',
'Interaction 197: Placement',
'Interaction 198: Placement',
'Interaction 199: Placement',
'Interaction 200: Placement']

df = df[keep_col]
df = df['Impression Count'].astype(float).fillna(0.0)
df.set_index('Conversion ID', inplace=True)


AppSign = df.loc[df['Activity'] == 'Application Signup Complete']
AppSign_MA = df.loc[df['Activity'] == 'Application Signup Complete - Only Mass Affluent or HNW']
AppStart = df.loc[df['Activity'] == 'Application Start']
Visitors = df.loc[df['Activity'] == 'Brochure Site Visitors']
Deposit = df.loc[df['Activity'] == 'Initial Deposit']
Deposit_High_LTV = df.loc[df['Activity'] == 'Initial Deposit - High LTV/Amount']
Deposit_MA = df.loc[df['Activity'] == 'Initial Deposit - MA or HNW (Any $ Amount)']
Rollover = df.loc[df['Activity'] == 'Rollover Initiated to Unfunded Account']
Rollover_MA = df.loc[df['Activity'] == 'Rollover Initiated to Unfunded Account - MA or HNW']


#Output each dataframe to a seperate sheet in excel 
writer = pd.ExcelWriter('/Users/apogee_tpaschke/Documents/python_outputs/Betterment_PathtoConversion.xlsx', engine='xlsxwriter') 

#write each dataframe to a different worksheet 

AppSign.to_excel(writer, sheet_name='App_Signup')
AppSign_MA.to_excel(writer, sheet_name='App_Signup_Affluent')
AppStart.to_excel(writer, sheet_name='App_Start')
Visitors.to_excel(writer, sheet_name='Visitors')
Deposit.to_excel(writer, sheet_name='Deposit') 
Deposit_High_LTV.to_excel(writer, sheet_name='Deposit_High_LTV') 
Deposit_MA.to_excel(writer, sheet_name='Deposit_Affluent')
Rollover.to_excel(writer, sheet_name='Rollover') 
Rollover_MA.to_excel(writer, sheet_name='Rollover_Affluent') 


writer.save()

print('Betterment Path To Conversion Report Complete')