<jupyter_start><jupyter_text>Filter and Merge Extracted Interactions===Builds on the three `Extraction` jupyter notebooks in this directory.<jupyter_code>%reload_ext autoreload
%autoreload 2
%matplotlib inline
import os
import re
import pandas as pd
import numpy as np

from collections import Counter, defaultdict
import sqlite3
from nltk import word_tokenize
from html.parser import HTMLParser
from tqdm import tqdm
import random
import pickle
import json

from datetime import datetime
from pprint import pprint

import matplotlib.pyplot as plt
import matplotlib.dates as md
import matplotlib
import pylab as pl
from IPython.core.display import display, HTML
from pathlib import Path
git_root_dir = !git rev-parse --show-toplevel
git_root_dir = Path(git_root_dir[0].strip())
git_root_dir
import sys
caringbridge_core_path = "/home/srivbane/levon003/repos/caringbridge_core"
sys.path.append(caringbridge_core_path)
import cbcore.data.paths as paths
import cbcore.data.dates as dates
working_dir = "/home/srivbane/shared/caringbridge/data/projects/sna-social-support/csv_data"
assert os.path.exists(working_dir)<jupyter_output><empty_output><jupyter_text>Load valid user listWe start by filtering only to include interactions contained in the list of valid users.<jupyter_code># load the list of valid users
data_selection_working_dir = "/home/srivbane/shared/caringbridge/data/projects/sna-social-support/data_selection"
valid_user_ids = set()
with open(os.path.join(data_selection_working_dir, "valid_user_ids.txt"), 'r') as infile:
    for line in infile:
        user_id = line.strip()
        if user_id == "":
            continue
        else:
            valid_user_ids.add(int(user_id))
len(valid_user_ids)<jupyter_output><empty_output><jupyter_text>Filter raw interactions filesFilters each interactions file to include only selected users.We filter first to greatly reduce the size of the data when we subsequently merge.<jupyter_code>gb_filepath = os.path.join(working_dir, "old_gb_metadata_raw.csv")
comments_filepath = os.path.join(working_dir, "new_comments_metadata_raw.csv")
amps_filepath = os.path.join(working_dir, "new_amps_metadata_raw.csv")

gb_filtered_filepath = os.path.join(working_dir, "old_gb_metadata_filtered.csv")
comments_filtered_filepath = os.path.join(working_dir, "new_comments_metadata_filtered.csv")
amps_filtered_filepath = os.path.join(working_dir, "new_amps_metadata_filtered.csv")
for input_filepath, output_filepath in [(gb_filepath, gb_filtered_filepath), 
                                        (comments_filepath, comments_filtered_filepath), 
                                        (amps_filepath, amps_filtered_filepath)]:
    with open(input_filepath, 'r') as infile:
        with open(output_filepath, 'w') as outfile:
            for line in tqdm(infile, desc=os.path.basename(input_filepath)):
                if line.strip() == "":
                    continue
                user_id = int(line.split(",")[0])
                if user_id in valid_user_ids:
                    outfile.write(line)
                
print("Finished filtering files.")<jupyter_output>old_gb_metadata_raw.csv: 82980359it [01:59, 695570.18it/s]
new_comments_metadata_raw.csv: 31052715it [00:48, 645155.79it/s]
new_amps_metadata_raw.csv: 63314738it [01:51, 567768.46it/s]<jupyter_text>Pre- and post-filtering word counts:```   63314738 new_amps_metadata_raw.csv   31052715 new_comments_metadata_raw.csv   82980359 old_gb_metadata_raw.csv       3536819 new_amps_metadata_filtered.csv    1094435 new_comments_metadata_filtered.csv    5864304 old_gb_metadata_filtered.csv```<jupyter_code># proportion of remaining lines for amps, comments, and the guestbook respectively
3536819 / 63314738, 1094435 / 31052715, 5864304 / 82980359<jupyter_output><empty_output><jupyter_text>Read interactions data<jupyter_code>gb_cols = ['user_id', 'site_id', 'int_type', 'created_at', 'updated_at']
comments_cols = ['user_id','site_id','int_type','journal_oid','created_at','updated_at']
amps_cols = ['user_id','site_id','journal_oid','journal_user_id','created_at','updated_at']
s = datetime.now()
gb_df = pd.read_csv(gb_filtered_filepath, header=None, names=gb_cols)
print(datetime.now() - s)
len(gb_df)
s = datetime.now()
comments_df = pd.read_csv(comments_filtered_filepath, header=None, names=comments_cols)
print(datetime.now() - s)
len(comments_df)
s = datetime.now()
amps_df = pd.read_csv(amps_filtered_filepath, header=None, names=amps_cols)
print(datetime.now() - s)
len(amps_df)<jupyter_output>/home/srivbane/shared/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3049: DtypeWarning: Columns (3) have mixed types. Specify dtype option on import or set low_memory=False.
  interactivity=interactivity, compiler=compiler, result=result)<jupyter_text>Plot interactions overlapsTakes a user view of interactions, producing visualizations to understand user types more broadly.<jupyter_code>gb_user_ids = set(gb_df.user_id)
comments_user_ids = set(comments_df.user_id)
amps_user_ids = set(amps_df.user_id)
len(gb_user_ids), len(comments_user_ids), len(amps_user_ids)
total_user_count = len(gb_user_ids | comments_user_ids | amps_user_ids)
total_user_count
from matplotlib_venn import venn3
fig, ax = plt.subplots(1, 1, figsize=(10,10))
venn3([gb_user_ids, comments_user_ids, amps_user_ids], set_labels=['Guestbook', 'Comments', 'Amps'], ax=ax,
     subset_label_formatter=lambda label: f"{label} ({int(label)/total_user_count*100:.1f}%)")
plt.title("Number of authors by interaction type", fontweight='bold')
plt.show()
gb_counts = defaultdict(int)
for user_id in tqdm(gb_df.user_id):
    gb_counts[user_id] += 1
comments_counts = defaultdict(int)
for user_id in tqdm(comments_df.user_id):
    comments_counts[user_id] += 1
amps_counts = defaultdict(int)
for user_id in tqdm(amps_df.user_id):
    amps_counts[user_id] += 1
users = []
for user_id in tqdm(gb_user_ids | comments_user_ids | amps_user_ids):
    u = {
        'user_id': user_id,
        'total_guestbook_count': gb_counts[user_id],
        'total_comments_count': comments_counts[user_id],
        'total_amps_count': amps_counts[user_id]
    }
    users.append(u)
len(users)
user_ints_df = pd.DataFrame(users)
len(user_ints_df)
user_ints_df.head()
fig, ax = plt.subplots(1, 1, figsize=(10,10))
ax.hist([user_ints_df.total_guestbook_count, user_ints_df.total_comments_count, user_ints_df.total_amps_count], 
        log=True, bins=50, range=(0,1000), label=['Guestbook', 'Comments', 'Amps'])
plt.title("Distribution")
plt.legend()
plt.show()
# 95th, 99th, and 99.9th percentile for guestbook activity
np.quantile(user_ints_df.total_guestbook_count, 0.95), np.quantile(user_ints_df.total_guestbook_count, 0.99), np.quantile(user_ints_df.total_guestbook_count, 0.999)
# 95th, 99th, and 99.9th percentile for comments activity
np.quantile(user_ints_df.total_comments_count, 0.95), np.quantile(user_ints_df.total_comments_count, 0.99), np.quantile(user_ints_df.total_comments_count, 0.999)
# 95th, 99th, and 99.9th percentile for amps activity
np.quantile(user_ints_df.total_amps_count, 0.95), np.quantile(user_ints_df.total_amps_count, 0.99), np.quantile(user_ints_df.total_amps_count, 0.999)
user_ints_df[user_ints_df.total_comments_count >= 500].head()<jupyter_output><empty_output><jupyter_text>We now cluster and plot the interaction totals to try to uncover additional structure<jupyter_code>import sklearn
X = user_ints_df[['total_guestbook_count', 'total_comments_count', 'total_amps_count']].values
X.shape
import umap
reducer = umap.UMAP()
embedding = reducer.fit_transform(X)
embedding.shape
# we don't visualize these embeddings until after we cluster, in order to compare the 3d-clustering to the 2d embedding
# we cluster the user interactions using DBSCAN
import sklearn.cluster
dbscan = sklearn.cluster.DBSCAN(n_jobs=-1)
clusters = dbscan.fit_predict(np.log(X + 1))
Counter(clusters).most_common()
cluster_counter = Counter(clusters)
num_clusters = len(set(clusters))
num_clusters
from mpl_toolkits.mplot3d import Axes3D
fig, ax = plt.subplots(1, 1, figsize=(10,10), subplot_kw={'projection': '3d'})
colors=[plt.cm.tab20((cluster + 1) / num_clusters) for cluster in clusters]
ax.scatter(np.log(X[:, 0] + 1), np.log(X[:, 1] + 1), zs=np.log(X[:, 2] + 1), alpha=0.01, c=colors)
plt.title("3d log plot of user interactions")
ax.set_xlabel("log(Guestbook count)")
ax.set_ylabel("log(Comment count)")
ax.set_zlabel("log(Amps count)")
plt.show()
# we create a 360 degree movie of this visualization by saving each frame
# SO source: https://stackoverflow.com/a/12905458/4146714
# this took 2:14:39 to execute the first time
visualization_working_dir = "/home/srivbane/shared/caringbridge/data/projects/sna-social-support/visualization"
user_interactions_movie_dir = os.path.join(visualization_working_dir, 'user_interactions_movie')
os.makedirs(user_interactions_movie_dir, exist_ok=True)

fig, ax = plt.subplots(1, 1, figsize=(10,10), subplot_kw={'projection': '3d'})
colors=[plt.cm.tab20((cluster + 1) / num_clusters) for cluster in clusters]
ax.scatter(np.log(X[:, 0] + 1), np.log(X[:, 1] + 1), zs=np.log(X[:, 2] + 1), alpha=0.01, c=colors)
ax.set_xlabel("log(Guestbook count)")
ax.set_ylabel("log(Comment count)")
ax.set_zlabel("log(Amps count)")

elev=10

for ii in tqdm(range(0,360,1), desc='Rendering frames.'):
    ax.view_init(elev=None, azim=ii)
    # default DPI is 100
    plt.savefig(os.path.join(user_interactions_movie_dir, f"f{ii}.png"))
from mpl_toolkits.mplot3d import Axes3D
fig, ax = plt.subplots(1, 1, figsize=(14,10))
ax.set_title("Distribution of cluster assignments")
ax.bar(range(num_clusters), [count for clust, count in Counter(clusters).most_common()],
      color=[plt.cm.tab20((clust + 1) / num_clusters) for clust, count in Counter(clusters).most_common()], 
       align='edge')
ax.set_xticks(np.array(range(num_clusters)) + 0.5)
ax.set_xticklabels([str(clust) for clust, count in Counter(clusters).most_common()])
for i, tup in enumerate(Counter(clusters).most_common()):
    clust, count = tup
    m = np.mean(X[clusters == clust, :], axis=0)
    med = np.median(X[clusters == clust, :], axis=0)
    ql = np.quantile(X[clusters == clust, :], 0.05, axis=0)
    qh = np.quantile(X[clusters == clust, :], 0.95, axis=0)
    s = np.std(X[clusters == clust, :], axis=0)
    text = f"{m[0]:.0f}\n{m[1]:.0f}\n{m[2]:.0f}"
    text_med = f"50%\n{med[0]:.0f}\n{med[1]:.0f}\n{med[2]:.0f}"
    text_ql = f"5%\n{ql[0]:.0f}\n{ql[1]:.0f}\n{ql[2]:.0f}"
    text_qh = f"95%\n{qh[0]:.0f}\n{qh[1]:.0f}\n{qh[2]:.0f}"
    ax.text(i, count, text, fontsize=12, verticalalignment='top')
    if i <= 7:
        ax.text(i, count / 10, text_qh, fontsize=12, verticalalignment='top')
        ax.text(i, count / 100, text_med, fontsize=12, verticalalignment='top')
        if i <= 6:
            ax.text(i, count / 1000, text_ql, fontsize=12, verticalalignment='top')
    ax.text(i, count, f"{count / len(clusters) * 100:.1f}%", fontsize=10, verticalalignment='bottom')
ax.set_yscale('log')
plt.show()<jupyter_output><empty_output><jupyter_text>Text labels within-column are mean guestbooks, comments, and amps respectively of entries in that cluster.The most common clusters also show values at 3 different quantiles to characterize the distribution.From the clustering results, a few observations: - 40% of authors make only 1-40 guestbook posts - 24% of authors make an approximately equal number of guestbooks and amps, with a third as many comments - 12% of authors make guestbook and amp posts (more GB than amps), but leave no comments - 8% of authors make guestbook posts and comments, but leave no amps - 7%  of authors leave 1-44 amps and 1-25 comments, but leave no guestbooks - 6% of authors leave only 1-15 amps - 5% of authors leave only 1-11 comments - The remainder (<1%) of authors leave huge numbers of guestbooks, amps, and comments, with varying ratios among the three interaction types.<jupyter_code>Y = embedding
fig, ax = plt.subplots(1, 1, figsize=(10,10))
ax.scatter(Y[:, 0], Y[:, 1], alpha=0.01, c=[plt.cm.tab20((cluster + 1) / num_clusters) for cluster in clusters])
plt.title("2d UMAP embedding of user interaction totals")
plt.show()<jupyter_output><empty_output><jupyter_text>Not clear that the UMAP embedding reveals any particularly interesting structure in the data.  Merge with journal data<jupyter_code>amps_df['int_type'] = 'amps'
ints_df = pd.concat([gb_df, comments_df, amps_df.drop(columns='journal_user_id')], sort=False)
ints_df.reset_index(drop=True, inplace=True)
len(ints_df)
assert len(ints_df) == len(gb_df) + len(amps_df) + len(comments_df)
ints_df.columns
ints_df.head()
# load the journal metadata
# load the dataframe with the index
s = datetime.now()
journal_metadata_dir = "/home/srivbane/shared/caringbridge/data/derived/journal_metadata"
journal_metadata_filepath = os.path.join(journal_metadata_dir, "journal_metadata.df")
journal_df = pd.read_feather(journal_metadata_filepath)
print(datetime.now() - s)
len(journal_df)
journal_df.head()
journal_df['int_type'] = 'journal'
journal_cols = ['user_id', 'site_id', 'int_type', 'created_at', 'updated_at', 'journal_oid', 'site_index', 'is_nontrivial']
merged_df = pd.concat([ints_df, journal_df[journal_cols]], sort=False)
assert len(merged_df) == len(ints_df) + len(journal_df)
len(merged_df)
merged_df.head()
# construct a map of (user_id) -> set(site_id)
# captures all sites on which a user_id has authored at least a single journal update
author_site_map = defaultdict(set)
for user_id, site_id in tqdm(zip(journal_df.user_id, journal_df.site_id), total=len(journal_df)):
    author_site_map[user_id].add(site_id)
s = datetime.now()
merged_df = merged_df.assign(is_self_interaction=merged_df.apply(lambda row: row.site_id in author_site_map[row.user_id], axis=1))
print(datetime.now() - s)
# quickly verify that journal OIDs are unique
assert len(journal_df) == len(set(journal_df.journal_oid))
journal_oid_index_map = {journal_oid: site_index
    for journal_oid, site_index in zip(journal_df.journal_oid, journal_df.site_index)
}
merged_df.head()
no_journal_count = len(merged_df[pd.isnull(merged_df.journal_oid)]) / len(merged_df)
guestbook_count = len(merged_df[merged_df.int_type == 'guestbook']) / len(merged_df)
assert no_journal_count == guestbook_count
# add site_index info to all interactions with a journal_oid
s = datetime.now()
merged_df.loc[~pd.isnull(merged_df.journal_oid), 'site_index'] = merged_df.loc[~pd.isnull(merged_df.journal_oid), 'journal_oid'].map(lambda journal_oid: journal_oid_index_map[journal_oid] if journal_oid in journal_oid_index_map else -1)
print(datetime.now() - s)
# only 229 journals are reference by a journal oid that we don't have in the journal data
ints_journal_oid_set = set(ints_df.journal_oid)
journals_journal_oid_set = set(journal_df.journal_oid)
len(ints_journal_oid_set - journals_journal_oid_set)
len(merged_df[merged_df.site_index == -1])
merged_df.reset_index(drop=True, inplace=True)
merged_df.head()
user_metadata_dir = "/home/srivbane/shared/caringbridge/data/projects/sna-social-support/user_metadata"
interaction_metadata_filepath = os.path.join(user_metadata_dir, "interaction_metadata.feather")
merged_df.to_feather(interaction_metadata_filepath)
print("Finished.")<jupyter_output>Finished.<jupyter_text>HDF5 storageNeed to convert mixed-type columns<jupyter_code>merged_df.loc[pd.isnull(merged_df.journal_oid), 'journal_oid'] = ""
merged_df.loc[pd.isnull(merged_df.site_index), 'site_index'] = -1
merged_df.loc[pd.isnull(merged_df.is_nontrivial), 'is_nontrivial'] = True
# also write to a HDF5 datastore 
s = datetime.now()
hdf5_store_filepath = os.path.join(user_metadata_dir, "interaction_metadata.h5")
store = pd.HDFStore(hdf5_store_filepath)
try:
    store.append('user_ints_df', merged_df, data_columns=True)
finally:
    store.close()
print(datetime.now() - s)
# test querying data for a specific user
s = datetime.now()
user_metadata_dir = "/home/srivbane/shared/caringbridge/data/projects/sna-social-support/user_metadata"
hdf5_store_filepath = os.path.join(user_metadata_dir, "interaction_metadata.h5")
store = pd.HDFStore(hdf5_store_filepath)
try:
    user_df = store.select('user_ints_df', where=['user_id == 173825'])
finally:
    store.close()
print(datetime.now() - s)
len(user_df)
user_df
# TODO Having identified self-interactions, produce percentages of each user that does self-interactions 
# (and percentage of each interaction that is a self-interaction)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12280914549093334, 'fraction_numerical': 0.028261265085814787, 'mean_word_length': 5.908253037285295, 'pattern_counts': {'":': 2, '<': 41, '<?xml version=': 0, '>': 40, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '378018', 'license': 'MIT', 'n_tokens_mistral': 6248, 'n_tokens_neox': 5799, 'n_words': 1247, 'path': 'data_pulling/FilterAndMergeExtractedInteractions.ipynb', 'repo_name': 'levon003/cscw-caringbridge-interaction-network', 'size': '16489'}