<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Mapping Reddit
Reddit is a discussion board that bills itself as the "Front Page of the Internet". It is divided into a large number of topic-specific "subreddits". In this demo, we'll take some data about which subreddits various active Reddit users post to a lot to make a visual map of subreddits. The data comes from the paper Navigating the massive world of reddit.
Initializing pymldb
In this demo, we will use pymldb to interact with the REST API
Step1:  Loading up the raw data
Step2:  And here is what our raw dataset looks like. The lineText column will need to be parsed
Step3:  Transforming the raw data into a sparse matrix
We will create and run a Procedure of type transform. The tokenize function will project out the subreddit names into columns.
Step4:  Here is the resulting dataset
Step6:  Dimensionality Reduction with Singular Value Decomposition (SVD)
We will create and run a Procedure of type svd.train.
Step7:  The result of this operation is a new dataset with a row per subreddit for the 4000 most-active subreddits and columns representing coordinates for that subreddit in a 100-dimensional space. 
Note
Step8:  Clustering with K-Means
We will create and run a Procedure of type kmeans.train.
Step9:  The result of this operation is a simple dataset which associates each row in the input (i.e. each subreddit) to one of 20 clusters.
Step10:  2-d Dimensionality Reduction with t-SNE
We will create and run a Procedure of type tsne.train.
Step11:  The result is similar to the SVD step above
Step12:  Counting the number of users per subreddit
We will create and run a Procedure of type transform on the transpose of the original input dataset.
Step13:  We appended "|1" to the row names in this dataset to allow the merge operation below to work well.
Step15:  Querying and Visualizating the output
We'll use the Query API to get the data into a Pandas DataFrame and then use Bokeh to visualize it.
In the query below we renamed the rows to get rid of the "|1" which the SVD appended to each subreddit name and we filter out rows where cluster is null because we only clustered the 4000 most-active subreddits.
<ASSISTANT_TASK:>
Python Code:
from pymldb import Connection
mldb = Connection("http://localhost")
Explanation: Mapping Reddit
Reddit is a discussion board that bills itself as the "Front Page of the Internet". It is divided into a large number of topic-specific "subreddits". In this demo, we'll take some data about which subreddits various active Reddit users post to a lot to make a visual map of subreddits. The data comes from the paper Navigating the massive world of reddit.
Initializing pymldb
In this demo, we will use pymldb to interact with the REST API: see the Using pymldb Tutorial for more details.
End of explanation
mldb.put('/v1/procedures/import_reddit', { 
    "type": "import.text",  
    "params": { 
        "dataFileUrl": "file://mldb/mldb_test_data/reddit.csv.zst",
        'delimiter':'', 
        'quoteChar':'',
        'outputDataset': 'reddit_raw',
        'runOnCreation': True
    } 
})
Explanation: Loading up the raw data
End of explanation
mldb.query("select * from reddit_raw limit 5")
Explanation: And here is what our raw dataset looks like. The lineText column will need to be parsed: it's comma-delimited, with the first token being a user ID and the remaining tokens being the set of subreddits that user contributed to.
End of explanation
mldb.put('/v1/procedures/reddit_import', {
    "type": "transform",
    "params": {
        "inputData": "select tokenize(lineText, {offset: 1, value: 1}) as * from reddit_raw",
        "outputDataset": "reddit_dataset",
        "runOnCreation": True
    }
})
Explanation: Transforming the raw data into a sparse matrix
We will create and run a Procedure of type transform. The tokenize function will project out the subreddit names into columns.
End of explanation
mldb.query("select * from reddit_dataset limit 5")
Explanation: Here is the resulting dataset: it's a sparse matrix with a row per user and a column per subreddit, where the cells are 1 if the row's user was a contributor to the column's subreddit, and null otherwise.
End of explanation
mldb.put('/v1/procedures/reddit_svd', {
    "type" : "svd.train",
    "params" : {
        "trainingData" : 
            SELECT 
                COLUMN EXPR (AS columnName() ORDER BY rowCount() DESC, columnName() LIMIT 4000) 
            FROM reddit_dataset
        ,
        "columnOutputDataset" : "reddit_svd_embedding",
        "runOnCreation": True
    }
})
Explanation: Dimensionality Reduction with Singular Value Decomposition (SVD)
We will create and run a Procedure of type svd.train.
End of explanation
mldb.query("select * from reddit_svd_embedding limit 5")
Explanation: The result of this operation is a new dataset with a row per subreddit for the 4000 most-active subreddits and columns representing coordinates for that subreddit in a 100-dimensional space. 
Note: the row names are the subreddit names followed by ".numberEquals.1" because the SVD training procedure interpreted the input matrix as categorical rather than numerical.
End of explanation
mldb.put('/v1/procedures/reddit_kmeans', {
    "type" : "kmeans.train",
    "params" : {
        "trainingData" : "select * from reddit_svd_embedding",
        "outputDataset" : "reddit_kmeans_clusters",
        "numClusters" : 20,
        "runOnCreation": True
    }
})
Explanation: Clustering with K-Means
We will create and run a Procedure of type kmeans.train.
End of explanation
mldb.query("select * from reddit_kmeans_clusters limit 5")
Explanation: The result of this operation is a simple dataset which associates each row in the input (i.e. each subreddit) to one of 20 clusters.
End of explanation
mldb.put('/v1/procedures/reddit_tsne', {
    "type" : "tsne.train",
    "params" : {
        "trainingData" : "select * from reddit_svd_embedding",
        "rowOutputDataset" : "reddit_tsne_embedding",
        "runOnCreation": True
    }
})
Explanation: 2-d Dimensionality Reduction with t-SNE
We will create and run a Procedure of type tsne.train.
End of explanation
mldb.query("select * from reddit_tsne_embedding limit 5")
Explanation: The result is similar to the SVD step above: we get a row per subreddit and the columns are coordinates, but this time in a 2-dimensional space appropriate for visualization.
End of explanation
mldb.put('/v1/procedures/reddit_count_users', {
    "type": "transform",
    "params": {
        "inputData": "select columnCount() as numUsers from transpose(reddit_dataset)",
        "outputDataset": "reddit_user_counts",
        "runOnCreation": True
    }
})
Explanation: Counting the number of users per subreddit
We will create and run a Procedure of type transform on the transpose of the original input dataset.
End of explanation
mldb.query("select * from reddit_user_counts limit 5")
Explanation: We appended "|1" to the row names in this dataset to allow the merge operation below to work well.
End of explanation
df = mldb.query(
    select c.* as *, m.* as *, quantize(m.x, 7) as grid_x, quantize(m.y, 7) as grid_y 
    named c.rowName() 
    from merge(reddit_tsne_embedding, reddit_kmeans_clusters) as m
        join reddit_user_counts as c on c.rowName() = m.rowPathElement(0)
    where m.cluster is not null 
    order by c.numUsers desc
)
df.head()
import numpy as np
colormap = np.array([
    "#1f77b4", "#aec7e8", "#ff7f0e", "#ffbb78", "#2ca02c", 
    "#98df8a", "#d62728", "#ff9896", "#9467bd", "#c5b0d5", 
    "#8c564b", "#c49c94", "#e377c2", "#f7b6d2", "#7f7f7f", 
    "#c7c7c7", "#bcbd22", "#dbdb8d", "#17becf", "#9edae5"
])
import bokeh.plotting as bp
from bokeh.models import HoverTool
#this line must be in its own cell 
bp.output_notebook()
x = bp.figure(plot_width=900, plot_height=700, title="Subreddit Map by t-SNE",
       tools=[HoverTool( tooltips=[ ("/r/", "@subreddit") ] )], toolbar_location=None,
       x_axis_type=None, y_axis_type=None, min_border=1)
x.scatter(
    x = df.x.values, 
    y=df.y.values, 
    color=colormap[df.cluster.astype(int).values],
    alpha=0.6,
    radius=(df.numUsers.values ** .3)/15,
    source=bp.ColumnDataSource({"subreddit": df.index.values})
)
labels = df.reset_index().groupby(['grid_x', 'grid_y'], as_index=False).first()
labels = labels[labels["numUsers"] > 10000]
x.text(
    x = labels.x.values, 
    y = labels.y.values,
    text = labels._rowName.values,
    text_align="center", text_baseline="middle",
    text_font_size="8pt", text_font_style="bold",
    text_color="#333333"
)
bp.show(x)
Explanation: Querying and Visualizating the output
We'll use the Query API to get the data into a Pandas DataFrame and then use Bokeh to visualize it.
In the query below we renamed the rows to get rid of the "|1" which the SVD appended to each subreddit name and we filter out rows where cluster is null because we only clustered the 4000 most-active subreddits.
End of explanation
<END_TASK>