#Assign XP groups with hash functions

# Set hash seed
%env PYTHONHASHSEED=0

# Import libraries
import pandas as pd

# Sample dataframe of UIDs
df = pd.DataFrame({'uid':[
    'd4212ed3-4399-4f5b-87ab-faec4f6558f8',
    'a9780c08-9714-4da2-a53c-6102d5f47cf7',
    '26c6853f-cae9-4a06-849d-7df46d744e5a',
    'efc1583b-946a-449f-a811-87d241e57bc4',
    '60d568e2-052b-4ca3-83da-34ee00ef5c6b'
]})
df

# Step 1a: compute hash value for each user UID
df['uid_hash'] = df['uid'].apply(hash)
df

# Step 1b: get the absolute value of the hash
df['abs_hash'] = df['uid_hash'].apply(abs)
df

# Step 3: apply a modulo, to reduce each hash
# In this example, we want 5 groups
groups = 5
df['user_group'] = df['abs_hash'].apply(lambda x: x % groups)
df

-- Redshift hash with FNV_HASH() function
SELECT
    uid,
    FNV_HASH(uid) AS uid_hash,
    ABS(FNV_HASH(uid)) AS abs_hash,
    MOD(ABS(FNV_HASH(uid)), 5) AS user_group
FROM users

-- BigQuery provides FARM_FINGERPRINT() for hash
SELECT
    uid,
    FARM_FINGERPRINT(uid) AS uid_hash,
    ABS(FARM_FINGERPRINT(uid)) AS abs_hash,
    MOD(ABS(FARM_FINGERPRINT(uid)), 5) AS user_group
FROM users