<jupyter_start><jupyter_text>Identifying Units and Matching Proc for Experiment With the promise of a social graph at Nextdoor, we'd like to understand any behavioral changes (potentially) elicited by incremental social capabilities.In order to do this, we can't randomly assign neighborhoods to Variant (social capabilities) and Control (no social).We can abstract to the DMA level to preserve the functionality of social capabilities.<jupyter_code>import re, os, sys
import numpy as np
import pandas as pd
import seaborn as sns
from sklearn import preprocessing

# Import custom qubole workflow
reference_path = os.getcwd()
sys.path.append('../../..') # go up to `data-science/ level`
from utils import qubole<jupyter_output><empty_output><jupyter_text>Data Pull<jupyter_code># Read first query
units_query_file = './queries/hoods_rolled_up_to_dmas.sql'
with open(units_query_file, 'r') as f:
    units_query_str = f.read()
    print(f"Selected query:\n {units_query_str}")

# Execute Query against Qubole
units_query_results = qubole.read_presto(units_query_str)
print(f"Query returns table of shape: {units_query_results.shape}")
print(f"Head of table: {units_query_results.head(5)}")

# Dump table to local csv
units_query_results.to_csv('./dma_agg_20210715.csv', index=False)<jupyter_output>Selected query:
 WITH
     temp_parameters AS (
        SELECT
            CAST('2020-01-01' AS DATE) AS ref_date
            , 'US' AS ref_country
    )
    , hood_demographic_features AS (
        SELECT
            nk.neighborhood_id
	        , nd.dma_name AS dma_name
            , nd.city
            , nd.state
            , nd.launch_datetime
            , CAST(to_unixtime(nd.launch_datetime) AS BIGINT) as epoch_launch_datetime
            , nk.race_pcnt_black
            , nk.race_pcnt_white
            , nk.race_pcnt_asian
            , nk.race_pcnt_other
            , nk.homes_per_square_mile
            , nk.median_family_income
            , nk.home_owners
            , nk.age_pcnt_34_and_younger
            , nk.age_pcnt_65_and_older
        FROM p_dwh.neighborhood_key_demographics nk
        INNER JOIN p_dwh.neighborhood_detail nd
            ON
                nk.neighborhood_id = nd.neighborhood_id
                AND nd.country = (SELECT ref_country FROM temp_parameters)[...]<jupyter_text>Data Formatting If we want to match pairs of similar DMAs along a set of features, then we need to make sure that all of the features are scaled.Let $K$ denote an $n \times m$ matrix consisting of $n$ DMAs and $m$ features. We can calculate a pairwaise distance for each pair of rows, and thus get an $n \times n$ matrix, $X$, where $x_{i,j}$ is the entry in the ith row and jth column.Let $D:\mathbb{R}^m \times \mathbb{R}^m \mapsto \mathbb{R}^+$ be a distance metric. Then: $$x_{i,j} = D(K_{i,\cdot}, K_{j, \cdot})$$ There are multiple distance/similarity metrics $D$ available for this calculation, and each has certain considerations:* [Mahalanobis](https://en.wikipedia.org/wiki/Mahalanobis_distance)* [Cosine](https://en.wikipedia.org/wiki/Cosine_similarity)* [Euclidian](https://en.wikipedia.org/wiki/Euclidean_distance)* ...<jupyter_code>X =  units_query_results.set_index('dma_name').drop(columns=['state_'])
scaler = preprocessing.StandardScaler()
X_scaled = scaler.fit_transform(X)
print(X_scaled[:5,:5])<jupyter_output>[[-0.56526009  0.53965405  1.92328263 -1.55109749  0.30005477]
 [ 1.08528687 -0.65661536  1.70939359 -0.86760083 -0.45217311]
 [ 1.15646898 -0.66638348 -0.6410845   0.51351331 -0.51236694]
 [-0.54039071 -0.53158339 -0.27321523  0.31599471 -0.3768301 ]
 [-0.61346318 -0.20923534 -0.80005542  0.98449278 -0.50960378]]
{'fraction_non_alphanumeric': 0.10212998361551065, 'fraction_numerical': 0.06717640633533588, 'mean_word_length': 3.5334158415841586, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 11, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '647774', 'license': 'MIT', 'n_tokens_mistral': 1402, 'n_tokens_neox': 1215, 'n_words': 323, 'path': 'files/sample.ipynb', 'repo_name': 'sergiosonline/sergiosonline.github.io', 'size': '3662'}