<jupyter_start><jupyter_text>Load<jupyter_code>columns = ["account_id", "url", "city_code", "state_code", "dma_code", 
           "country_code","user_id", "session_id", "referrer", "client", 
           "user_agent_platform", "user_agent_language", "user_agent_browser",
           "zeta_user_id","geo_data","ip", "remote_addr"]
#columns = ["ip"]

file_path = f"s3://drose-sandbox/sizmek_zync_64m"
%%time

#dd_raw = dd.read_parquet(file_path, columns=columns).compute()
#dd_raw.compute().shape
%%time

df_raw = pd.read_parquet(file_path, columns=columns)
print(f"Shape: {df_raw.shape[0]:,} Memory: {df_raw.memory_usage(deep=True).sum()/1e9:.2f}GB")
#df2 = df_raw.copy()<jupyter_output><empty_output><jupyter_text>IP<jupyter_code>%%time

def replace_ip(ips, thresh=1000):
    new_ips = ips.copy()
    value_counts = new_ips.value_counts().to_dict()
    mapping = new_ips.map(value_counts)
    new_ips[mapping < thresh] = "other"
    return new_ips

new_ips = replace_ip(df_raw["ip"])
%%time

new_addrs = replace_ip(df_raw["remote_addr"])
print(f"Sizmek ips: {new_ips.nunique()}")
print(f"Zync ips: {new_addrs.nunique()}")<jupyter_output>Sizmek ips: 3834
Zync ips: 3598<jupyter_text>Geo<jupyter_code>df_raw["geo_data"][0]
%%time

geo_split = pd.DataFrame([i for i in df_raw["geo_data"].values])
#geo_split = df_raw["geo_data"].apply(pd.Series)
geo_split<jupyter_output>CPU times: user 43 s, sys: 3.5 s, total: 46.5 s
Wall time: 46.4 s<jupyter_text>Coords<jupyter_code>%%time

coords = pd.DataFrame([i for i in geo_split["coordinates"].values])
coords[:3]<jupyter_output><empty_output><jupyter_text>Countries<jupyter_code>%%time

def replace_low_counts(data, thresh=1_000):
    new_data = data.copy()
    value_counts = new_data.value_counts().to_dict()
    mapping = new_data.map(value_counts)
    new_data[mapping < thresh] = "other"
    return new_data

new_countries = replace_low_counts(geo_split["country"])
new_countries.value_counts().shape<jupyter_output>CPU times: user 15.8 s, sys: 697 ms, total: 16.5 s
Wall time: 16.5 s<jupyter_text>Cities<jupyter_code>%%time

print(geo_split["city"].value_counts().shape)
new_cities = replace_low_counts(geo_split["city"], 1_000)
print(new_cities.value_counts().shape)<jupyter_output>(5384,)
(1840,)
CPU times: user 19.6 s, sys: 728 ms, total: 20.3 s
Wall time: 20.3 s<jupyter_text>Make new DF<jupyter_code>%%time

cols_to_keep = [
    "account_id", "url", "city_code", "state_code", "dma_code", 
    "country_code","user_id", "session_id", "referrer", "client", 
    "user_agent_platform", "user_agent_language", "user_agent_browser",
    "zeta_user_id",
]

df3 = df_raw[cols_to_keep]
%%time

df3["sizmek_ip"] = new_ips
df3["zync_ip"] = new_addrs
df3["zync_country"] = new_countries
df3["zync_state"] = geo_split["subdivision"]
df3["zync_city"] = new_cities
df3["zync_lat"] = coords["latitude"]
df3["zync_long"] = coords["longitude"]
#df3.head(1).T<jupyter_output><empty_output><jupyter_text>Filter Down<jupyter_code>%%time

print(df3.shape)
df4 = df3.fillna("missing")
print(df4.shape)
df4 = df4.drop_duplicates()
print(df4.shape)
df4 = df4.reset_index(drop=True)
print(df4.shape)
%%time

print(df4.shape)
group_list = [e for e in (df4.columns) if e not in ("url", "referrer")]
df5 = df4.groupby(group_list)[["url", "referrer"]].agg(lambda x: list(x))
df5 = df5.reset_index()
print(df5.shape)
df5 = df5[df5["user_id"].duplicated(keep=False)]
print(df5.shape)<jupyter_output>(793073, 21)
(44928, 21)
(30818, 21)
CPU times: user 3.38 s, sys: 0 ns, total: 3.38 s
Wall time: 3.38 s<jupyter_text>Export<jupyter_code>%%time
df4.to_csv("s3://drose-sandbox/sizmek_zync_cleaned_793k.csv")
%%time
df5.to_csv("s3://drose-sandbox/sizmek_zync_cleaned_grouped_30k.csv")<jupyter_output>CPU times: user 3.55 s, sys: 132 ms, total: 3.68 s
Wall time: 6.62 s
{'fraction_non_alphanumeric': 0.16820702402957485, 'fraction_numerical': 0.04119355690520201, 'mean_word_length': 6.810309278350515, 'pattern_counts': {'":': 0, '<': 33, '<?xml version=': 0, '>': 31, 'https://': 0, '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': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '513159', 'license': 'MIT', 'n_tokens_mistral': 1697, 'n_tokens_neox': 1603, 'n_words': 254, 'path': 'm64_ip.ipynb', 'repo_name': 'cipher982/GNN-playground', 'size': '3787'}