import pandas as pd
import numpy as np

import matplotlib.pyplot as plt
import seaborn as sns
plt.style.use('seaborn-white')
%matplotlib inline

df = pd.read_csv('Data-Analyst-Assignment-tab-cleaned.csv')

df.head()

df.info()

# Check missing values - no NaN values
df[df.isnull().any(axis=1)]

df.columns

# rename columns with extra space
df.columns = ['click_status','AMP_timestamp','query_term','IP_address','cost_per_click','conversions','user','device']

df.click_status = df.click_status.map(lambda x: 1 if x == 1 else 0)

df['date'] = df.AMP_timestamp.map(lambda x: x[:7])
df['hour'] = df.AMP_timestamp.map(lambda x: int(x[8:10]))

print 'max hour: ', df['hour'].max()
print 'min hour: ', df['hour'].min()

df.query_term.value_counts().head(10)

# Create a new column that stores query terms as a list
df['query_term_list'] = df.query_term.map(lambda x: x.strip('[').strip(']').replace('"','').split(','))

# Create a new column that indicates the length of the query terms list
df['len_query_term_list'] = df['query_term_list'].map(lambda x: len(x))

df['len_query_term_list'].value_counts()

df.cost_per_click = df.cost_per_click.map(lambda x: float(x.strip('$ ')))

df.conversions.value_counts()

df_1 = df[df.click_status == 1]
df_0 = df[df.click_status == 0]

# Valid click rate
len(df_1) / float(len(df))

# Avg CPC for valid clicks
df_1.cost_per_click.mean()

# Total spending for valid clicks
df_1.cost_per_click.sum()

# Conversion rate for valid clicks
len(df[df.conversions!=0])/float(len(df_1))

# Identify the top spending query term and the average cost per click
qt_table = pd.pivot_table(df_1,index='query_term',values=['click_status','cost_per_click'],aggfunc=[np.sum]).sort_values(by=('sum','cost_per_click'),ascending=False)
qt_table[('avg','cost_per_click')] = qt_table[('sum','cost_per_click')] / qt_table[('sum','click_status')]
qt_table.head(10)

qt_table.columns = ['valid_clicks','total_spending','avg_cpc']

bins = [0, 50, 100, 150, 200,250,300]
group_names = ['<50 Clicks', '50-99 Clicks', '100-149 Clicks', '150-199 Clicks','200-249 Clicks','250+ Clicks']
qt_table['click_bucket'] = pd.cut(qt_table['valid_clicks'], bins, labels=group_names)

qt_table.valid_clicks.mean()

# Identify the top converting IP addresses
df_1[df.conversions != 0].IP_address.value_counts().to_frame().head(10)

# Query terms table
fig,ax = plt.subplots(figsize=(12, 7))
sns.barplot(x = qt_table.head(20).index, y = qt_table.head(20)['sum'])
plt.ylabel('Total occurences')
plt.title('Top 20 Query Terms Occurences', fontsize=14);

total_num = float(len(qt_table))
for i in [1,5,10, 50, 100, 200]:
    print '-- {:.2f}% of query terms had {} or more valid clicks'.format( (qt_table['sum'][qt_table['sum']>=i].count()) / total_num *100,i)

ip_list = df.IP_address.value_counts()[df.IP_address.value_counts() >1].to_frame().index

df_ip = df[df.IP_address.isin(ip_list)]

# Provide a breakdown on invalid and valid clicks. Include steps taken to conduct analysis
ip_status_table = df_ip.click_status.value_counts().to_frame()
ip_status_table['percentage'] = ip_status_table.click_status / ip_status_table.click_status.sum()
ip_status_table

# Provide a breakdown on whether these users send the same query term for each click
ip_query_table = pd.pivot_table(df_ip,index=['IP_address','query_term'],values='click_status',aggfunc=[len]).reset_index()
ip_query_table.head()

ip_query_table_2 = pd.pivot_table(ip_query_table,index=['IP_address'],values='query_term',aggfunc=[len]).reset_index()
ip_query_table_2.columns = ['IP_address','unique_query_terms']

ip_unique_query = ip_query_table_2.unique_query_terms.value_counts().to_frame()
ip_unique_query['percentage'] = ip_unique_query.unique_query_terms / ip_unique_query.unique_query_terms.sum()
ip_unique_query

# Identify any shared attributes for this pool of users (if any)
# Create ip_clicks_status: 1 = IP addresses with > 1 click, 0 = IP addresses with 1 click
df['ip_clicks_status'] = df.IP_address.map(lambda x: 1 if x in ip_list else 0)

df['ip_clicks_status'].sum() / float(len(df['ip_clicks_status']))

df.to_csv('cleaned_data.csv')