What Percent of the Top-Voted Comments in Reddit Threads Were Also 1st Comment?

This R Notebook is the complement to my blog post What Percent of the Top-Voted Comments in Reddit Threads Were Also 1st Comment?.

This notebook is licensed under the MIT License. If you use the code or data visualization designs contained within this notebook, it would be greatly appreciated if proper attribution is given back to this notebook and/or myself. Thanks! :)

1 Setup

Setup the R packages.

source("Rstart.R")

Attaching package: ‘dplyr’

The following objects are masked from ‘package:stats’:

    filter, lag

The following objects are masked from ‘package:base’:

    intersect, setdiff, setequal, union

Registering fonts with R

Attaching package: ‘scales’

The following objects are masked from ‘package:readr’:

    col_factor, col_numeric

library(viridis)
library(plotly)

Attaching package: ‘plotly’

The following object is masked _by_ ‘.GlobalEnv’:

    subplot

The following object is masked from ‘package:ggplot2’:

    last_plot

The following object is masked from ‘package:stats’:

    filter

The following object is masked from ‘package:graphics’:

    layout

library(bigrquery)
library(htmlwidgets)
sessionInfo()

R version 3.3.0 (2016-05-03)
Platform: x86_64-apple-darwin13.4.0 (64-bit)
Running under: OS X 10.12.1 (unknown)

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] grid      stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] htmlwidgets_0.7    bigrquery_0.3.0    plotly_4.5.2       viridis_0.3.4     
 [5] stringr_1.1.0      digest_0.6.10      RColorBrewer_1.1-2 scales_0.4.0      
 [9] extrafont_0.17     ggplot2_2.1.0      dplyr_0.5.0        readr_1.0.0       

loaded via a namespace (and not attached):
 [1] Rcpp_0.12.7       formatR_1.4       plyr_1.8.4        base64enc_0.1-3  
 [5] tools_3.3.0       jsonlite_1.1      evaluate_0.10     tibble_1.2       
 [9] gtable_0.2.0      viridisLite_0.1.3 DBI_0.5-1         yaml_2.1.13      
[13] gridExtra_2.2.1   Rttf2pt1_1.3.4    httr_1.2.1        knitr_1.14       
[17] R6_2.2.0          rmarkdown_1.1     purrr_0.2.2       tidyr_0.6.0      
[21] extrafontdb_1.0   magrittr_1.5      htmltools_0.3.5   assertthat_0.1   
[25] colorspace_1.2-7  stringi_1.1.2     lazyeval_0.2.0    munsell_0.4.3    

This project uses data from BigQuery. To get the data for the first charts, run this query:

SELECT created_rank, score_rank,
COUNT(*) as num_comments
FROM
(
SELECT 
subreddit,
ROW_NUMBER() OVER (PARTITION BY link_id ORDER BY score DESC) AS score_rank, 
ROW_NUMBER() OVER (PARTITION BY link_id ORDER BY created_utc ASC) AS created_rank,
COUNT(*) OVER (PARTITION BY link_id) AS num_toplevelcomments_in_thread
FROM [fh-bigquery:reddit_comments.all_starting_201501]
WHERE link_id = parent_id
)
WHERE score_rank <= 100 AND created_rank <= 100 AND num_toplevelcomments_in_thread >= 30
GROUP BY created_rank, score_rank
ORDER BY created_rank, score_rank

• ROW_NUMBER() must be used instead of RANK() to avoid biasing ties in score.
• COUNT() OVER (PARTITION BY link_id) returns the number of toplevel comments in the thread where that comment is located; the outerlevel filter then filters the output on that.
• WHERE link_id = parent_id corresponds to top-level comments

This outputs a 10,000 row file.

2 Exploratory Analysis

df <- read_csv("reddit_012015_all.csv")

Parsed with column specification:
cols(
  created_rank = col_integer(),
  score_rank = col_integer(),
  num_comments = col_integer()
)

df %>% head()

The total number of comments analyzed is n = 86,561,476.

Plot a basic 2x2 heat map. A log10() filter is likely necessary to compress the values.

plot <- ggplot(df, aes(x=created_rank, y=score_rank, fill=log10(num_comments))) +
            geom_raster(interpolate = TRUE) +
            fte_theme() +
            scale_fill_viridis()
max_save(plot, "reddit-first", "Reddit")

Optimize axes/parameters and add a contour to visualize groupings. (use non-log filter for posterity)

plot <- ggplot(df, aes(x=created_rank, y=score_rank, fill=num_comments, z=num_comments)) +
            geom_raster(interpolate = TRUE) +
            geom_contour(color = "white", alpha = 0.5, bins = 5) +
            scale_x_continuous(breaks = c(1,seq(10,100,by=10))) +
            scale_y_continuous(breaks = c(1,seq(10,100,by=10))) +
            fte_theme() +
            theme(legend.title = element_text(size=7, family="Open Sans Condensed Bold"), legend.position="top", legend.direction="horizontal", legend.key.width=unit(1.25, "cm"), legend.key.height=unit(0.25, "cm"), legend.margin=unit(0,"cm"), panel.margin=element_blank()) +
            scale_fill_viridis(name="# of\nComments", labels=comma, breaks=pretty_breaks(6)) +
            labs(title = "Heatmap between Time Comment Made on Reddit and Score Rank",
                 x = "# Top-Level Comment (Lower Number is Posted Earlier)",
                 y = "Comment Score Ranking (Lower Number is Higher Score)")
max_save(plot, "reddit-first-2", "Reddit (Jan 2015 - Sep 2016)")

Remake the plot using a log10 scale.

plot <- ggplot(df, aes(x=created_rank, y=score_rank, fill=num_comments, z=log10(num_comments))) +
            geom_raster(interpolate = TRUE) +
            geom_contour(color = "white", alpha = 0.5, bins = 5) +
            scale_x_continuous(breaks = c(1,seq(10,100,by=10))) +
            scale_y_continuous(breaks = c(1,seq(10,100,by=10))) +
            fte_theme() +
            theme(legend.title = element_text(size=7, family="Open Sans Condensed Bold"), legend.position="top", legend.direction="horizontal", legend.key.width=unit(1.25, "cm"), legend.key.height=unit(0.25, "cm"), legend.margin=unit(0,"cm"), panel.margin=element_blank()) +
            scale_fill_viridis(name="# of\nComments", labels=comma, breaks=10^(1:5), trans="log10") +
            labs(title = "Heatmap between Time Comment Made on Reddit and Score Rank",
                 x = "# Top-Level Comment (Lower Number is Posted Earlier)",
                 y = "Comment Score Ranking (Lower Number is Higher Score)")
max_save(plot, "reddit-first-2a", "Reddit (Jan 2015 - Sep 2016)")

Filter on only the first comments to create 1D slice of the proportions.

df_first_comment <- df %>% filter(created_rank == 1) %>%
                        mutate(norm = num_comments/sum(num_comments))
df_first_comment %>% select(score_rank, norm) %>% head()

The aggregate accounts for n = 1,636,298 first comments.

The total proportion of the top 5 ranks is 0.46, and the top 10 ranks is 0.62.

plot <- ggplot(df_first_comment, aes(x=score_rank, y=norm)) +
            geom_bar(stat = "identity", fill = "#2980b9") +
            scale_x_continuous(breaks = c(1,seq(10,100,by=10))) +
            scale_y_continuous(labels = percent, breaks=pretty_breaks(6)) +
            fte_theme() +
            theme(plot.title = element_text(size=6)) +
            labs(title = "What Percentage of the 1st Comments in Reddit Threads Were Also the Top-Voted Comment?",
                 x = "Score Rank of First Comment in Thread (Lower Number is Higher Score)",
                 y = "Proportion of all First Comments at Score Rank")
max_save(plot, "reddit-first-3", "Reddit (Jan 2015 - Sep 2016)")

Invert the dataframe to arrange by Top Comment (this should be symmetric, per the 2D map).

df_top_comment <- df %>% filter(score_rank == 1) %>%
                        mutate(norm = num_comments/sum(num_comments))
df_top_comment %>% head()

The aggregate accounts for n = 1,679,007 first comments.

The total proportion of the top 5 ranks is 0.56, and the top 10 ranks is 0.77.

plot <- ggplot(df_top_comment, aes(x=created_rank, y=norm)) +
            geom_bar(stat = "identity", fill = "#c0392b") +
            scale_x_continuous(breaks = c(1,seq(10,100,by=10))) +
            scale_y_continuous(labels = percent, breaks=pretty_breaks(6)) +
            fte_theme() +
            theme(plot.title = element_text(size=6)) +
            labs(title = "What Percentage of the Top-Voted Comments in Reddit Threads Were Also the 1st Comment?",
                 x = "# Comment Which Resulted in Top-Voted Comment (Lower Number is Posted Earlier)",
                 y = "Proportion of all Top-Voted Comments at Comment Posting #")
max_save(plot, "reddit-first-4", "Reddit (Jan 2015 - Sep 2016)")

3 Analyze by Subreddits

3.1 Get Subreddit Data from BigQuery

Create same charts as above for Top 100 Subreddits by unique active commenters. Must use R to get data since too many rows returned.

project_id <- <FILL IN>   # DO NOT SHARE!

This requires a subquery tweak on the first query to filter on top 100 subreddits, and include in output:

SELECT subreddit, created_rank, score_rank,
COUNT(*) as num_comments
FROM
(
SELECT 
subreddit,
ROW_NUMBER() OVER (PARTITION BY link_id ORDER BY score DESC) AS score_rank, 
ROW_NUMBER() OVER (PARTITION BY link_id ORDER BY created_utc ASC) AS created_rank,
COUNT(*) OVER (PARTITION BY link_id) AS num_toplevelcomments_in_thread
FROM [fh-bigquery:reddit_comments.all_starting_201501]
WHERE link_id = parent_id AND subreddit IN
    (SELECT subreddit FROM (SELECT subreddit, COUNT(DISTINCT author) as unique_commenters,
        FROM [fh-bigquery:reddit_comments.all_starting_201501]
        GROUP BY subreddit
        ORDER BY unique_commenters DESC
        LIMIT 100)
    )
)
WHERE score_rank <= 100 AND created_rank <= 100 AND num_toplevelcomments_in_thread >= 30
GROUP BY subreddit, created_rank, score_rank
ORDER BY subreddit, created_rank, score_rank

Query with R and save output for later.

query <- "SELECT subreddit, created_rank, score_rank,
COUNT(*) as num_comments
FROM
(
SELECT 
subreddit,
ROW_NUMBER() OVER (PARTITION BY link_id ORDER BY score DESC) AS score_rank, 
ROW_NUMBER() OVER (PARTITION BY link_id ORDER BY created_utc ASC) AS created_rank,
COUNT(*) OVER (PARTITION BY link_id) AS num_toplevelcomments_in_thread
FROM [fh-bigquery:reddit_comments.all_starting_201501]
WHERE link_id = parent_id AND subreddit IN
    (SELECT subreddit FROM (SELECT subreddit, COUNT(DISTINCT author) as unique_commenters,
        FROM [fh-bigquery:reddit_comments.all_starting_201501]
        GROUP BY subreddit
        ORDER BY unique_commenters DESC
        LIMIT 100)
    )
)
WHERE score_rank <= 100 AND created_rank <= 100 AND num_toplevelcomments_in_thread >= 30
GROUP BY subreddit, created_rank, score_rank
ORDER BY subreddit, created_rank, score_rank"

df_subreddits <- tbl_df(query_exec(query, project=project_id, max_pages=Inf))
df_subreddits %>% head()

write.csv(df_subreddits, "reddit_012015_by_subreddit.csv", row.names=F)

Read from cached output.

df_subreddits <- read_csv("reddit_012015_by_subreddit.csv")

Missing column names filled in: 'X1' [1]Parsed with column specification:
cols(
  X1 = col_integer(),
  subreddit = col_character(),
  created_rank = col_integer(),
  score_rank = col_integer(),
  num_comments = col_integer()
)

The returned file has 933,517 rows and takes 21.4 Mb of memory in R.

Remake the df_first_comment data frame with the subreddit data as well:

df_first_comment_subreddit <- df_subreddits %>% filter(created_rank == 1) %>%
                        group_by(subreddit) %>%
                        mutate(norm = num_comments/sum(num_comments))
df_first_comment_subreddit %>% head()

3.2 Visualize Subreddit Data

Create the 1D Map and the 2D Map for each subreddit using variants code above. First, extract a list of the top 100 subreddits used above.

subreddits <- df_subreddits %>% select(subreddit) %>% unique() %>% unlist()
subreddits %>% head()

     subreddit1      subreddit2      subreddit3      subreddit4      subreddit5 
        "4chan" "AdviceAnimals"       "Android"           "Art"        "AskMen" 
     subreddit6 
    "AskReddit" 

Create a wrapper function, which given a subreddit, produces the chart for that subreddit. (assumes img-1d and img-2d directories exist)

3.2.1 1D Charts for Top 100 Subreddits

subreddit_1d <- function(p_subreddit) {
plot <- ggplot(df_first_comment_subreddit %>% filter(subreddit == p_subreddit), aes(x=score_rank, y=norm)) +
            geom_bar(stat = "identity") +
            scale_x_continuous(breaks = c(1,seq(10,100,by=10))) +
            scale_y_continuous(labels = percent, breaks=pretty_breaks(6)) +
            fte_theme() +
            labs(title = sprintf("Distribution of First Comment Rankings for /r/%s", p_subreddit),
                 x = "Score Rank of First Comment in Thread (Lower Number is Higher Score)",
                 y = "Proportion of all First Comments at Score Rank")
max_save(plot, sprintf("img-1d/%s-1d", p_subreddit), "Reddit (Jan 2015 - Sep 2016)")
}

Run for each of the 100 subreddits.

temp <- lapply(subreddits, subreddit_1d)

3.2.2 2D Charts for Top 100 Subreddits

subreddit_2d <- function(p_subreddit) {
plot <- ggplot(df_subreddits %>% filter(subreddit == p_subreddit), aes(x=created_rank, y=score_rank, fill=num_comments, z=log10(num_comments))) +
            geom_raster(interpolate = FALSE) +
            geom_contour(color = "white", alpha = 0.5, bins = 3, size = 0.25) +
            scale_x_continuous(breaks = c(1,seq(10,100,by=10))) +
            scale_y_continuous(breaks = c(1,seq(10,100,by=10))) +
            fte_theme() +
            theme(plot.title = element_text(size=7)) +
            theme(legend.title = element_text(size=7, family="Open Sans Condensed Bold"), legend.position="top", legend.direction="horizontal", legend.key.width=unit(1.25, "cm"), legend.key.height=unit(0.25, "cm"), legend.margin=unit(0,"cm"), panel.margin=element_blank()) +
            scale_fill_viridis(name="# of\nComments", labels=comma, breaks=10^(0:4), trans="log10") +
            labs(title = sprintf("Heatmap between Time Comment Made and Score Rank for /r/%s", p_subreddit),
                 x = "# Top-Level Comment (Lower Number is Posted Earlier)",
                 y = "Comment Score Ranking (Lower Number is Higher Score)")
max_save(plot, sprintf("img-2d/%s-2d", p_subreddit), "Reddit (Jan 2015 - Sep 2016)")
}

temp <- lapply(subreddits, subreddit_2d)

4 Plotly

Do Plotly separately since it requires certain parameters for it to be implemented at minimaxir.com. (The final interactive plot is not used in the post since it doesn’t add much)

theme_color <- "#f7f8fa"
plotly_theme <- function() {
                theme(plot.background=element_rect(fill=theme_color), 
                  panel.background=element_rect(fill=theme_color),
                  panel.border=element_rect(color=theme_color),
                  strip.background=element_rect(fill=theme_color))
}

plot <- ggplot(df_first_comment, aes(x=score_rank, y=norm)) +
            geom_bar(stat = "identity") +
            scale_x_continuous(breaks = c(1,seq(10,100,by=10))) +
            scale_y_continuous(labels = percent, breaks=pretty_breaks(6)) +
            fte_theme() +
            plotly_theme() +
            theme(plot.title = element_text(size=7)) +
            labs(title = "What Percentage of the Top Comments in Reddit Threads Were Also 1st Comment?",
                 subtitle = "Proportion of Score Rankings for First Comment",
                 x = "Score Rank of First Comment in Thread (Lower Number is Higher Score)",
                 y = "Proportion of all First Comments at Score Rank")
plot <- plot %>%
        ggplotly() %>% 
        saveWidget("reddit-first-1d.html", selfcontained=F, libdir="plotly")

5 LICENSE

The MIT License (MIT)

Copyright (c) 2016 Max Woolf

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.