<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 <a href="https
Step1:  Helper Functions
Step2:  Flattening
Sizes
Step3:  Labels
Step4:  Objects
Step5:  Words
Step6:  Colors
Step7:  Faces
Step8:  Logos
Step9:  Feature Extraction
Colors
Step10:  Faces
Step11:  Logos
Step12:  Labels (one-hot query)
Step13:  Words
Step14:  Objects
Step15:  Sizes
Step16:  Full Feature Table
Step17:  Exploration
Step18:  Merge with Performance Data
Steps taken in the Campaign Manager UI
Step19:  Performance Bucketing
Step20:  Analysis
Random Forest Classifier
Step21:  Hypothesis Testing
<ASSISTANT_TASK:>
Python Code:
import os
from google.cloud import bigquery
import pandas as pd
from google.colab import syntax
%%writefile credentials.json
{
  "type": "service_account",
  "project_id": "[project_id]",
  "private_key_id": "[private_key_id]",
  "private_key": "[private_key]",
  "client_email": "[client_email]",
  "client_id": "[client_id]",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "[client_x509_cert_url]"
  }
#@title Project Variables { run: "auto", display-mode: "form" }
project_id = 'oculi-v2-dev' #@param {type:"string"}
dataset_name = "demoverse" #@param {type:"string"}
#@markdown Enter a file path for the json key downloaded from GCP:
json_key_file = r'credentials.json' #@param {type:"string"}
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = r'credentials.json'
client = bigquery.Client(project=project_id)
Explanation: <a href="https://colab.research.google.com/github/google/oculi/blob/master/colabs/CM360/%5Bcolab_3%5D_Feature_Extraction_and_Modeling.ipynb" target="_parent"><img src="https://colab.research.google.com/assets/colab-badge.svg" alt="Open In Colab"/></a>
Setup
End of explanation
from IPython.display import Image, display, IFrame
from IPython.core.display import HTML
def display_creative(url):
  extension = url.split('.')[-1]
  if extension == 'html':
    display(IFrame(src=url, width=300, height=250))
  else:
    display(Image(url=url))
def display_sample(subset, sample_size=5):
  sample = subset.sample(sample_size)
  sample_urls = sample['url'].tolist()
  sample_cids = sample['creative_id'].tolist()
  for i in range(sample_size):
    print('Creative ID:', sample_cids[i])
    print('URL:', sample_urls[i])
    display_creative(sample_urls[i])
    print()  
def fill_sql(sql):
  return sql.format(project=project_id, dataset=dataset_name)
def sql_to_df(sql):
  sql = fill_sql(sql)
  return client.query(sql).to_dataframe()
def sql_to_view(sql, view_name):
  sql = fill_sql(sql)
  view_id = '{project}.{dataset}.{view_name}'.format(
      project=project_id, dataset=dataset_name,
      view_name=view_name
  )
  view = bigquery.Table(view_id)
  view.view_query = sql
  client.delete_table(view, not_found_ok=True)
  return client.create_table(view)
def sql_to_pivot_df(sql, col_name, view_name, prominence, limit='20'):
  sql = sql.format(
      project=project_id, dataset=dataset_name,
      col_name=col_name, view_name=view_name,
      prominence=prominence, limit=limit
  )
  df = sql_to_df(sql)
  features = pd.pivot_table(
      df, index='creative_id', values='prominence',
      columns=col_name
  )
  features.reset_index(inplace=True)
  features.fillna(0, inplace=True)
  return features
Explanation: Helper Functions
End of explanation
sql = syntax.sql('''
SELECT
  creative_id,
  full_url AS url,
  CAST(SPLIT(creative_pixel_size, 'x')[OFFSET(0)] AS INT64) AS width,
  CAST(SPLIT(creative_pixel_size, 'x')[OFFSET(1)] AS INT64) AS height
FROM `{project}.{dataset}.creative_urls`
''')
sql_to_df(sql).head(3)
sql_to_view(sql, 'creative_sizes')
Explanation: Flattening
Sizes
End of explanation
sql = syntax.sql('''
SELECT
  creative_id,
  frame_id,
  description AS label,
  score
FROM
  `{project}.{dataset}.label_annotations`
  JOIN UNNEST(frames)
  JOIN UNNEST(label_annotations)
''')
sql_to_df(sql).head(3)
sql_to_view(sql, 'flat_labels')
Explanation: Labels
End of explanation
sql = syntax.sql('''
SELECT
  creative_id,
  frame_id,
  name AS object,
  score,
  (
    (MAX(v.x) - MIN(v.x)) -- width of shape
    *
    (MAX(v.y) - MIN(v.y)) -- height of shape
  ) AS area_fraction
FROM
  `{project}.{dataset}.localized_object_annotations`
  JOIN UNNEST(frames)
  JOIN UNNEST(localized_object_annotations)
  JOIN UNNEST(boundingPoly.normalizedVertices) AS v
GROUP BY 1,2,3,4
''')
sql_to_df(sql).head(3)
sql_to_view(sql, 'flat_objects')
Explanation: Objects
End of explanation
sql = syntax.sql('''
SELECT
  creative_id,
  frame_id,
  LOWER(description) AS word,
  (
    (MAX(v.x) - MIN(v.x)) / ANY_VALUE(width)
    *
    (MAX(v.y) - MIN(v.y)) / ANY_VALUE(height)
  ) AS area_fraction
FROM
  `{project}.{dataset}.text_annotations`
  JOIN UNNEST(frames)
  JOIN UNNEST(text_annotations)
  JOIN UNNEST(boundingPoly.vertices) AS v
  JOIN `{project}.{dataset}.creative_sizes` USING (creative_id)
WHERE
  -- Exclude small and common words
  LENGTH(description) > 2
  AND LOWER(description) NOT IN ('for', 'the')
GROUP BY 1,2,3
''')
sql_to_view(sql, 'flat_words')
sql_to_df(sql).head(3)
Explanation: Words
End of explanation
sql = syntax.sql('''
SELECT
  creative_id,
  frame_id,
  ROW_NUMBER() OVER (PARTITION BY creative_id, frame_id) AS color,
  color.red,
  color.green,
  color.blue,
  score,
  pixelFraction AS area_fraction
FROM
  `{project}.{dataset}.image_properties_annotation`
  JOIN UNNEST(frames)
  JOIN UNNEST(image_properties_annotation.dominantColors.colors)
''')
sql_to_df(sql).head(3)
sql_to_view(sql, 'flat_colors')
Explanation: Colors
End of explanation
sql = syntax.sql('''
SELECT
  creative_id,
  frame_id,
  ROW_NUMBER() OVER (PARTITION BY creative_id, frame_id) AS face,
  headwearLikelihood,
  angerLikelihood,
  surpriseLikelihood,
  sorrowLikelihood,
  joyLikelihood,
  panAngle,
  rollAngle,
  tiltAngle,
  detectionConfidence AS score,
  (
    (MAX(v.x) - MIN(v.x)) / ANY_VALUE(width)
    *
    (MAX(v.y) - MIN(v.y)) / ANY_VALUE(height)
  ) AS area_fraction
FROM
  `{project}.{dataset}.face_annotations`
  JOIN UNNEST(frames)
  JOIN UNNEST(face_annotations)
  JOIN UNNEST(boundingPoly.vertices) AS v
  JOIN `{project}.{dataset}.creative_sizes` USING (creative_id)
GROUP BY 1,2,4,5,6,7,8,9,10,11,12
''')
sql_to_df(sql).head(3)
sql_to_view(sql, 'flat_faces')
Explanation: Faces
End of explanation
sql = syntax.sql('''
SELECT
  creative_id,
  frame_id,
  description AS logo,
  score,
  (
    (MAX(v.x) - MIN(v.x)) / ANY_VALUE(width)
    *
    (MAX(v.y) - MIN(v.y)) / ANY_VALUE(height)
  ) AS area_fraction,
  (MAX(v.x) + MIN(v.x)) / 2 / ANY_VALUE(width) AS x_fraction,
  (MAX(v.y) + MIN(v.y)) / 2 / ANY_VALUE(height) AS y_fraction,
FROM
  `{project}.{dataset}.logo_annotations`
  JOIN UNNEST(frames)
  JOIN UNNEST(logo_annotations)
  JOIN UNNEST(boundingPoly.vertices) AS v
  JOIN `{project}.{dataset}.creative_sizes` USING (creative_id)
GROUP BY 1,2,3,4
''')
sql_to_df(sql).head(3)
sql_to_view(sql, 'flat_logos')
Explanation: Logos
End of explanation
sql = syntax.sql('''
WITH scaled_colors AS (
  SELECT
    creative_id,
    frame_id,
    color,
    red / 255.0 AS red,
    green / 255.0 AS green,
    blue / 255.0 AS blue,
    -- definition of perceived brightness:
    -- https://en.wikipedia.org/wiki/Relative_luminance
    (0.2126*red + 0.7152*green + 0.0722*blue) / 255.0 AS brightness,
    area_fraction
  FROM `{project}.{dataset}.flat_colors`
)
SELECT
  creative_id,
  SUM(red * area_fraction) / COUNT(frame_id) AS avg_redness,
  SUM(green * area_fraction) / COUNT(frame_id) AS avg_greenness,
  SUM(blue * area_fraction) / COUNT(frame_id) AS avg_blueness,
  SUM(brightness * area_fraction) / COUNT(frame_id) AS avg_brightness,
  MAX(red * area_fraction) AS max_redness,
  MAX(green * area_fraction) AS max_greenness,
  MAX(blue * area_fraction) AS max_blueness,
  MAX(brightness * area_fraction) AS max_brightness
FROM scaled_colors
GROUP BY 1
''')
df_colors = sql_to_df(sql)
df_colors.head(3)
Explanation: Feature Extraction
Colors
End of explanation
sql = syntax.sql('''
CREATE OR REPLACE FUNCTION `{project}.{dataset}.likelihoodToNumber`(likelihood STRING) AS (
CASE
  WHEN likelihood = 'VERY_LIKELY' THEN 1.0
  WHEN likelihood = 'LIKELY' THEN 0.7
  WHEN likelihood = 'POSSIBLE' THEN 0.5
  ELSE 0
END
);
''')
sql_to_df(sql).head(3)
sql = syntax.sql('''
WITH scaled_faces AS (
  SELECT
    creative_id,
    frame_id,
    face,
    `{project}.{dataset}.likelihoodToNumber`(headwearLikelihood) AS headwear,
    `{project}.{dataset}.likelihoodToNumber`(angerLikelihood) AS anger,
    `{project}.{dataset}.likelihoodToNumber`(surpriseLikelihood) AS surprise,
    `{project}.{dataset}.likelihoodToNumber`(sorrowLikelihood) AS sorrow,
    `{project}.{dataset}.likelihoodToNumber`(joyLikelihood) AS joy,
    ABS(panAngle) / 180.0 + ABS(rollAngle) / 180.0  + ABS(tiltAngle) / 180.0 AS angular_deviation,
    score,
    area_fraction
  FROM `{project}.{dataset}.flat_faces`
)
SELECT
  creative_id,
  COUNT(face) AS num_faces,
  MAX(headwear) AS has_headwear_face,
  MAX(anger) AS has_angry_face,
  MAX(surprise) AS has_surprised_face,
  MAX(sorrow) AS has_sad_face,
  MAX(joy) AS has_happy_face,
  MAX(area_fraction) AS largest_face_area,
  1 - MIN(angular_deviation) AS has_front_facing_face
FROM scaled_faces
GROUP BY 1
''')
df_faces = sql_to_df(sql)
df_faces.head(3)
Explanation: Faces
End of explanation
sql = syntax.sql('''
WITH flat_logos_with_prominence AS (
  SELECT
    creative_id,
    frame_id,
    logo,
    (score * area_fraction) AS prominence,
    x_fraction,
    y_fraction
  FROM `{project}.{dataset}.flat_logos` LIMIT 1000
),
prominent_logos AS (
  SELECT
    creative_id,
    logo,
    ROW_NUMBER() OVER (PARTITION BY creative_id ORDER BY prominence DESC) AS rank,
    x_fraction,
    y_fraction
  FROM flat_logos_with_prominence
)
SELECT
  creative_id,
  IF(x_fraction < 0.5 AND y_fraction < 0.5, 1, 0) AS has_logo_top_left,
  IF(x_fraction > 0.5 AND y_fraction < 0.5, 1, 0) AS has_logo_top_right,
  IF(x_fraction < 0.5 AND y_fraction > 0.5, 1, 0) AS has_logo_bottom_left,
  IF(x_fraction > 0.5 AND y_fraction > 0.5, 1, 0) AS has_logo_bottom_right
FROM prominent_logos
WHERE rank = 1
''')
df_logos = sql_to_df(sql)
df_logos.head(3)
Explanation: Logos
End of explanation
one_hot_sql = syntax.sql('''
WITH
  source AS (
    SELECT
      creative_id,
      'has_{col_name}_' || LOWER(REGEXP_REPLACE(REPLACE({col_name},' ','_'), r'[;:*?#"<>|()&,\.]', ''))
        AS col,
      {prominence} AS prominence
    FROM `{project}.{dataset}.{view_name}`
    GROUP BY 1, 2
  )
SELECT
  creative_id,
  col AS {col_name},
  prominence
FROM
  source
INNER JOIN
  (
    SELECT col, MAX(prominence)
    FROM source
    GROUP BY 1
    ORDER BY MAX(prominence) DESC
    LIMIT {limit} 
  )
USING (col)
''')
df_labels = sql_to_pivot_df(
    one_hot_sql,
    col_name='label',
    view_name='flat_labels',
    prominence='MAX(score)',
    limit='20'
)
df_labels.head(3)
Explanation: Labels (one-hot query)
End of explanation
df_words = sql_to_pivot_df(
    one_hot_sql,
    col_name='word',
    view_name='flat_words',
    prominence='MAX(area_fraction)',
    limit='30'
)
df_words.head(3)
Explanation: Words
End of explanation
df_objects = sql_to_pivot_df(
    one_hot_sql,
    col_name='object',
    view_name='flat_objects',
    prominence='SUM(score * area_fraction) / COUNT(DISTINCT frame_id)',
    limit='30'
)
df_objects.head(3)
Explanation: Objects
End of explanation
sql = syntax.sql('''
  SELECT * FROM `{project}.{dataset}.creative_sizes`
''')
df_all_creatives = sql_to_df(sql)
df_all_creatives.head(3)
Explanation: Sizes
End of explanation
df_features = pd.merge(df_all_creatives,
                           df_colors, how='outer', on='creative_id')
df_features = pd.merge(df_features,
                           df_faces, how='outer', on='creative_id')
df_features = pd.merge(df_features,
                           df_logos, how='outer', on='creative_id')
df_features = pd.merge(df_features,
                           df_objects, how='outer', on='creative_id')
df_features = pd.merge(df_features,
                           df_words, how='outer', on='creative_id')
df_features = pd.merge(df_features,
                           df_labels, how='outer', on='creative_id')
df_features.fillna(0, inplace=True)
df_features.head(3)
Explanation: Full Feature Table
End of explanation
subset = df_features[df_features['num_faces'] > 0]
display_sample(subset, 1)
Explanation: Exploration
End of explanation
sql = syntax.sql('''
SELECT
  CAST(Creative_ID AS INT64) AS creative_id,
  -- Metric we're using: adjusted viewable CTR
  Clicks / (Impressions * (Active_View__Viewable_Impressions / Active_View__Measurable_Impressions)) AS av_ctr
FROM `{project}.{dataset}.performance`
WHERE
  -- Hard Requirements
  Impressions > 0
  AND Active_View__Measurable_Impressions > 0
  AND Active_View__Viewable_Impressions > 0
  AND Creative_ID NOT LIKE "%Grand Total%"
  -- Soft Requirements
  AND Impressions > 1000
''')
df_performance = sql_to_df(sql)
df_master = pd.merge(df_features, df_performance, how='inner', on='creative_id')
df_master.head(3)
Explanation: Merge with Performance Data
Steps taken in the Campaign Manager UI:
1. Go to Campaign Manager > Reporting and Attribution > enter your account ID
2. Create a new Standard report including, at minimum, Creative ID and performance metrics (Impressions, Clicks, Active View: Viewable Impressions, Active View: Measureable Impressions)
  - Don't include Date as a dimension
  - Set the Date Range to the maximum allowed (24 months)
3. Run the report, download as CSV, and upload to BigQuery as a table called performance in the same dataset as the Oculi data
  - Set Number of errors allowed to 2
  - Set Header rows to skip to the line number of the column headers in the CSV (usually 11, but depends on report parameters)
End of explanation
av_ctr_75 = df_master['av_ctr'].quantile(0.75)
av_ctr_75
av_ctr_bucket = lambda x: 1 if x > av_ctr_75 else 0
df_master['high_perf'] = df_master['av_ctr'].apply(av_ctr_bucket)
Explanation: Performance Bucketing
End of explanation
from sklearn.ensemble import RandomForestClassifier
from sklearn.model_selection import train_test_split
from sklearn import metrics
df_raw = df_master.drop(columns=['creative_id', 'url', 'width', 'height', 'av_ctr'])
df_raw.reset_index(drop=True, inplace=True)
x = df_raw.loc[:, df_raw.columns != 'high_perf']
y = df_raw['high_perf']
x_train, x_test, y_train, y_test = train_test_split(x, y, test_size = 0.2)
rf = RandomForestClassifier(
    n_estimators=100,
    max_depth=5,
    max_features=30,
    min_samples_split=20,
    bootstrap=True,
    criterion='gini'
)
rf.fit(x_train, y_train.values.ravel())
threshold = 0.5
y_train_pred = rf.predict_proba(x_train)
y_train_pred = (y_train_pred[:,1] > threshold).astype(int)
y_test_pred = rf.predict_proba(x_test)
y_test_pred = (y_test_pred[:,1] > threshold).astype(int)
print('Train Accuracy :', metrics.accuracy_score(y_train, y_train_pred))
print('Test Accuracy  :', metrics.accuracy_score(y_test, y_test_pred))
feat_importances = pd.DataFrame(
    rf.feature_importances_,
    index=x_train.columns
).reset_index().sort_values(0, ascending=False)
feat_importances.columns = ['Feature', 'Importance']
feat_importances.reset_index(drop=True, inplace=True)
feat_importances.head(40)
Explanation: Analysis
Random Forest Classifier
End of explanation
left = df_master[df_master['has_word_get'] == 0]
right = df_master[df_master['has_word_get'] > 0]
display_sample(right)
left['av_ctr'].describe()
right['av_ctr'].describe()
from scipy.stats import mannwhitneyu
mannwhitneyu(left['av_ctr'], right['av_ctr'], alternative='less')
Explanation: Hypothesis Testing
End of explanation
<END_TASK>