In [ ]:
import json
import calendar
import random
from datetime import date, timedelta
import faker
import numpy as np
from pandas import DataFrame
from delorean import parse
import pandas as pd
# make the graphs nicer
import matplotlib.pyplot as plt
plt.style.use('ggplot')
# see all available with: print(plt.style.available)
In [ ]:
# create the faker to populate the data
fake = faker.Faker()
In [ ]:
usernames = set()
usernames_no = 1000
# populate the set with 1000 unique usernames
while len(usernames) < usernames_no:
usernames.add(fake.user_name())
In [ ]:
def get_random_name_and_gender():
skew = .6 # 60% of users will be female
male = random.random() > skew
if male:
return fake.name_male(), 'M'
else:
return fake.name_female(), 'F'
# for each username, create a complete user profile
# simulate user data coming from an API. It is a list
# of JSON strings (users).
def get_users(usernames):
users = []
for username in usernames:
name, gender = get_random_name_and_gender()
user = {
'username': username,
'name': name,
'gender': gender,
'email': fake.email(),
'age': fake.random_int(min=18, max=90),
'address': fake.address(),
}
users.append(json.dumps(user))
return users
users = get_users(usernames)
users[:3]
In [ ]:
# campaign name format:
# InternalType_StartDate_EndDate_TargetAge_TargetGender_Currency
def get_type():
# just some gibberish internal codes
types = ['AKX', 'BYU', 'GRZ', 'KTR']
return random.choice(types)
def get_start_end_dates():
duration = random.randint(1, 2 * 365)
offset = random.randint(-365, 365)
start = date.today() - timedelta(days=offset)
end = start + timedelta(days=duration)
def _format_date(date_):
return date_.strftime("%Y%m%d")
return _format_date(start), _format_date(end)
def get_age():
age = random.randint(20, 45)
age -= age % 5
diff = random.randint(5, 25)
diff -= diff % 5
return '{}-{}'.format(age, age + diff)
def get_gender():
return random.choice(('M', 'F', 'B'))
def get_currency():
return random.choice(('GBP', 'EUR', 'USD'))
def get_campaign_name():
separator = '_'
type_ = get_type()
start_end = separator.join(get_start_end_dates())
age = get_age()
gender = get_gender()
currency = get_currency()
return separator.join(
(type_, start_end, age, gender, currency))
In [ ]:
# campaign data:
# name, budget, spent, clicks, impressions
def get_campaign_data():
name = get_campaign_name()
budget = random.randint(10**3, 10**6)
spent = random.randint(10**2, budget)
clicks = int(random.triangular(10**2, 10**5, 0.2 * 10**5))
impressions = int(random.gauss(0.5 * 10**6, 2))
return {
'cmp_name': name,
'cmp_bgt': budget,
'cmp_spent': spent,
'cmp_clicks': clicks,
'cmp_impr': impressions
}
In [ ]:
# assemble the logic to get the final version of the rough data
# data will be a list of dictionaries. Each dictionary will follow
# this structure:
# {'user': user_json, 'campaigns': [c1, c2, ...]}
# where user_json is the JSON string version of a user data dict
# and c1, c2, ... are campaign dicts as returned by
# get_campaign_data
def get_data(users):
data = []
for user in users:
campaigns = [get_campaign_data()
for _ in range(random.randint(2, 8))]
data.append({'user': user, 'campaigns': campaigns})
return data
In [ ]:
# fetch simulated rough data
rough_data = get_data(users)
rough_data[:2] # let's take a peek
In [ ]:
# Let's start from having a different version of the data
# I want a list whose items will be dicts. Each dict is
# the original campaign dict plus the user JSON
data = []
for datum in rough_data:
for campaign in datum['campaigns']:
campaign.update({'user': datum['user']})
data.append(campaign)
data[:2] # let's take another peek
In [ ]:
# data is good enough for creating an initial DataFrame
df = DataFrame(data)
# let's take a peek at the first 5 rows, to make sure
# nothing weird has happened
df.head()
In [ ]:
# OK! DataFrame is alive and well!
# let's get a sense of how many rows there are and what is
# their structure.
df.count()
In [ ]:
df.describe()
In [ ]:
# let's see which are the top and bottom 3 campaigns according
# to budget (regardless of the currency)
df.sort_values(by=['cmp_bgt'], ascending=False).head(3)
In [ ]:
df.sort_values(by=['cmp_bgt'], ascending=False).tail(3)
In [ ]:
# first, let's explode cmp_name into its components
# and get a separate DataFrame for those
def unpack_campaign_name(name):
# very optimistic method, assumes data in campaign name
# is always in good state
type_, start, end, age, gender, currency = name.split('_')
start = parse(start).date
end = parse(end).date
return type_, start, end, age, gender, currency
campaign_data = df['cmp_name'].apply(unpack_campaign_name)
campaign_cols = [
'Type', 'Start', 'End', 'Age', 'Gender', 'Currency']
campaign_df = DataFrame(
campaign_data.tolist(), columns=campaign_cols, index=df.index)
campaign_df.head(3)
In [ ]:
# let's join the two dataframes
df = df.join(campaign_df)
In [ ]:
# and take a peek: good! We didn't screw up!
df[['cmp_name'] + campaign_cols].head(3)
In [ ]:
# now let's do the same for the JSON user object
def unpack_user_json(user):
# very optimistic as well, expects user objects
# to have all attributes
user = json.loads(user.strip())
return [
user['username'],
user['email'],
user['name'],
user['gender'],
user['age'],
user['address'],
]
user_data = df['user'].apply(unpack_user_json)
user_cols = [
'username', 'email', 'name', 'gender', 'age', 'address']
user_df = DataFrame(
user_data.tolist(), columns=user_cols, index=df.index)
In [ ]:
# let's join the two dataframes
df = df.join(user_df)
In [ ]:
# and take a peek: good! Also this time we didn't screw up!
df[['user'] + user_cols].head(2)
In [ ]:
# now we have the DataFrame completely expanded, so it's
# time to play with it. First, let's fix those ugly column names
better_columns = [
'Budget', 'Clicks', 'Impressions',
'cmp_name', 'Spent', 'user',
'Type', 'Start', 'End',
'Target Age', 'Target Gender', 'Currency',
'Username', 'Email', 'Name',
'Gender', 'Age', 'Address',
]
df.columns = better_columns
In [ ]:
# let's add three other columns
def calculate_extra_columns(df):
# Click Through Rate
df['CTR'] = df['Clicks'] / df['Impressions']
# Cost Per Click
df['CPC'] = df['Spent'] / df['Clicks']
# Cost Per Impression
df['CPI'] = df['Spent'] / df['Impressions']
calculate_extra_columns(df)
In [ ]:
# let's take a peek
df[['Spent', 'Clicks', 'Impressions',
'CTR', 'CPC', 'CPI']].head(3)
In [ ]:
# let's take the values of the first row and verify
clicks = df['Clicks'][0]
impressions = df['Impressions'][0]
spent = df['Spent'][0]
CTR = df['CTR'][0]
CPC = df['CPC'][0]
CPI = df['CPI'][0]
print('CTR:', CTR, clicks / impressions)
print('CPC:', CPC, spent / clicks)
print('CPI:', CPI, spent / impressions)
In [ ]:
# let's also add the name of the Day when campaign starts
def get_day_of_the_week(day):
number_to_day = dict(enumerate(calendar.day_name, 1))
return number_to_day[day.isoweekday()]
def get_duration(row):
return (row['End'] - row['Start']).days
df['Day of Week'] = df['Start'].apply(get_day_of_the_week)
df['Duration'] = df.apply(get_duration, axis=1)
In [ ]:
# let's verify
df[['Start', 'End', 'Duration', 'Day of Week']].head(3)
In [ ]:
# now let's get rid of the cmp_name and user columns,
# which we don't need any more, and address too
final_columns = [
'Type', 'Start', 'End', 'Duration', 'Day of Week', 'Budget',
'Currency', 'Clicks', 'Impressions', 'Spent', 'CTR', 'CPC',
'CPI', 'Target Age', 'Target Gender', 'Username', 'Email',
'Name', 'Gender', 'Age'
]
df = df[final_columns]
In [ ]:
# CSV format
# df.to_csv('df.csv')
In [ ]:
# JSON format
# df.to_json('df.json')
In [ ]:
# Spreadsheet format
# df.to_excel('df.xls')
First let's take care of the graphics, we need to instruct the notebook to use matplotlib inline and the serif font family.
In [ ]:
%matplotlib inline
In [ ]:
import pylab
pylab.rcParams.update({'font.family' : 'serif'})
In [ ]:
df.describe()
In [ ]:
df[['Budget', 'Spent', 'Clicks', 'Impressions']].hist(
bins=16, figsize=(16, 6));
In [ ]:
df[['CTR', 'CPC', 'CPI']].hist(
bins=20, figsize=(16, 6));
In [ ]:
# let's see the campaigns whose spent is > than 75% of the budget
mask = (df.Spent > 0.75 * df.Budget)
df[mask][['Budget', 'Spent', 'Clicks', 'Impressions']].hist(
bins=15, figsize=(16, 6), color='g');
In [ ]:
# Let's aggregate per Day of the Week
df_weekday = df.groupby(['Day of Week']).sum()
df_weekday[['Impressions', 'Spent', 'Clicks']].plot(
figsize=(16, 6), subplots=True);
In [ ]:
# Let's aggregate per gender
agg_config = {
'Impressions': {
'Mean Impr': 'mean',
'Std Impr': 'std',
},
'Spent': ['mean', 'std'],
}
df.groupby(['Target Gender', 'Target Age']).agg(agg_config)
In [ ]:
# finally, let's make a pivot table
pivot = df.pivot_table(
values=['Impressions', 'Clicks', 'Spent'],
index=['Target Age'],
columns=['Target Gender'],
aggfunc=np.sum
)
pivot
In [ ]: