# Setup - Run only once per Kernel App
%conda install openjdk -y
# install PySpark
%pip install pyspark==3.3.0
# restart kernel
from IPython.core.display import HTML
HTML("<script>Jupyter.notebook.kernel.restart()</script>")
Collecting package metadata (current_repodata.json): done
Solving environment: done
==> WARNING: A newer version of conda exists. <==
current version: 23.3.1
latest version: 23.11.0
Please update conda by running
$ conda update -n base -c defaults conda
Or to minimize the number of packages updated during conda update use
conda install conda=23.11.0
## Package Plan ##
environment location: /opt/conda
added / updated specs:
- openjdk
The following packages will be downloaded:
package | build
---------------------------|-----------------
ca-certificates-2023.08.22 | h06a4308_0 123 KB
certifi-2023.11.17 | py310h06a4308_0 158 KB
openjdk-11.0.13 | h87a67e3_0 341.0 MB
------------------------------------------------------------
Total: 341.3 MB
The following NEW packages will be INSTALLED:
openjdk pkgs/main/linux-64::openjdk-11.0.13-h87a67e3_0
The following packages will be UPDATED:
ca-certificates conda-forge::ca-certificates-2023.7.2~ --> pkgs/main::ca-certificates-2023.08.22-h06a4308_0
certifi conda-forge/noarch::certifi-2023.7.22~ --> pkgs/main/linux-64::certifi-2023.11.17-py310h06a4308_0
Downloading and Extracting Packages
ca-certificates-2023 | 123 KB | | 0%
openjdk-11.0.13 | 341.0 MB | | 0%
ca-certificates-2023 | 123 KB | ##################################### | 100%
openjdk-11.0.13 | 341.0 MB | 3 | 1%
certifi-2023.11.17 | 158 KB | ##################################### | 100%
certifi-2023.11.17 | 158 KB | ##################################### | 100%
openjdk-11.0.13 | 341.0 MB | #6 | 4%
openjdk-11.0.13 | 341.0 MB | ###2 | 9%
openjdk-11.0.13 | 341.0 MB | ##### | 14%
openjdk-11.0.13 | 341.0 MB | ######7 | 18%
openjdk-11.0.13 | 341.0 MB | ########2 | 22%
openjdk-11.0.13 | 341.0 MB | #########5 | 26%
openjdk-11.0.13 | 341.0 MB | ##########8 | 29%
openjdk-11.0.13 | 341.0 MB | ############1 | 33%
openjdk-11.0.13 | 341.0 MB | #############4 | 36%
openjdk-11.0.13 | 341.0 MB | ##############6 | 40%
openjdk-11.0.13 | 341.0 MB | ###############8 | 43%
openjdk-11.0.13 | 341.0 MB | ################# | 46%
openjdk-11.0.13 | 341.0 MB | ##################2 | 49%
openjdk-11.0.13 | 341.0 MB | ###################4 | 52%
openjdk-11.0.13 | 341.0 MB | ####################5 | 56%
openjdk-11.0.13 | 341.0 MB | #####################5 | 58%
openjdk-11.0.13 | 341.0 MB | ######################5 | 61%
openjdk-11.0.13 | 341.0 MB | #######################4 | 63%
openjdk-11.0.13 | 341.0 MB | ########################5 | 66%
openjdk-11.0.13 | 341.0 MB | #########################5 | 69%
openjdk-11.0.13 | 341.0 MB | ##########################6 | 72%
openjdk-11.0.13 | 341.0 MB | ############################1 | 76%
openjdk-11.0.13 | 341.0 MB | #############################8 | 81%
openjdk-11.0.13 | 341.0 MB | ###############################4 | 85%
openjdk-11.0.13 | 341.0 MB | ################################7 | 89%
openjdk-11.0.13 | 341.0 MB | #################################9 | 92%
openjdk-11.0.13 | 341.0 MB | ##################################9 | 95%
openjdk-11.0.13 | 341.0 MB | ###################################9 | 97%
openjdk-11.0.13 | 341.0 MB | ##################################### | 100%
Preparing transaction: done
Verifying transaction: done
Executing transaction: done
Note: you may need to restart the kernel to use updated packages.
Collecting pyspark==3.3.0
Using cached pyspark-3.3.0-py2.py3-none-any.whl
Collecting py4j==0.10.9.5 (from pyspark==3.3.0)
Using cached py4j-0.10.9.5-py2.py3-none-any.whl (199 kB)
Installing collected packages: py4j, pyspark
Successfully installed py4j-0.10.9.5 pyspark-3.3.0
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
[notice] A new release of pip is available: 23.2.1 -> 23.3.1
[notice] To update, run: pip install --upgrade pip
Note: you may need to restart the kernel to use updated packages.
# Import pyspark and build Spark session
from pyspark.sql import SparkSession
spark = (
SparkSession.builder.appName("PySparkApp")
.config("spark.jars.packages", "org.apache.hadoop:hadoop-aws:3.2.2")
.config(
"fs.s3a.aws.credentials.provider",
"com.amazonaws.auth.ContainerCredentialsProvider",
)
.getOrCreate()
)
print(spark.version)
Warning: Ignoring non-Spark config property: fs.s3a.aws.credentials.provider
:: loading settings :: url = jar:file:/opt/conda/lib/python3.10/site-packages/pyspark/jars/ivy-2.5.0.jar!/org/apache/ivy/core/settings/ivysettings.xml
Ivy Default Cache set to: /root/.ivy2/cache The jars for the packages stored in: /root/.ivy2/jars org.apache.hadoop#hadoop-aws added as a dependency :: resolving dependencies :: org.apache.spark#spark-submit-parent-bb190541-59e1-48bc-9de1-0f405eb5702f;1.0 confs: [default] found org.apache.hadoop#hadoop-aws;3.2.2 in central found com.amazonaws#aws-java-sdk-bundle;1.11.563 in central :: resolution report :: resolve 368ms :: artifacts dl 22ms :: modules in use: com.amazonaws#aws-java-sdk-bundle;1.11.563 from central in [default] org.apache.hadoop#hadoop-aws;3.2.2 from central in [default] --------------------------------------------------------------------- | | modules || artifacts | | conf | number| search|dwnlded|evicted|| number|dwnlded| --------------------------------------------------------------------- | default | 2 | 0 | 0 | 0 || 2 | 0 | --------------------------------------------------------------------- :: retrieving :: org.apache.spark#spark-submit-parent-bb190541-59e1-48bc-9de1-0f405eb5702f confs: [default] 0 artifacts copied, 2 already retrieved (0kB/21ms)
23/12/08 21:17:35 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Setting default log level to "WARN". To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
3.3.0
%%time
import time
import sagemaker
bucket = "dsan-6000-group-35"
output_prefix_data_submissions = f"project/submissions/yyyy=*"
s3_path = f"s3a://{bucket}/{output_prefix_data_submissions}"
print(f"reading submissions from {s3_path}")
submissions = spark.read.parquet(s3_path, header=True)
sagemaker.config INFO - Not applying SDK defaults from location: /etc/xdg/sagemaker/config.yaml sagemaker.config INFO - Not applying SDK defaults from location: /root/.config/sagemaker/config.yaml reading submissions from s3a://dsan-6000-group-35/project/submissions/yyyy=* 23/11/04 00:12:51 WARN MetricsConfig: Cannot locate configuration: tried hadoop-metrics2-s3a-file-system.properties,hadoop-metrics2.properties
23/11/04 00:13:00 WARN package: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'. CPU times: user 3.25 s, sys: 267 ms, total: 3.51 s Wall time: 14.7 s
%%time
output_prefix_data_comments = "project/comments/yyyy=*"
s3_path = f"s3a://{bucket}/{output_prefix_data_comments}"
print(f"reading comments from {s3_path}")
comments = spark.read.parquet(s3_path, header=True)
reading comments from s3a://dsan-6000-group-35/project/comments/yyyy=* CPU times: user 5.85 ms, sys: 0 ns, total: 5.85 ms Wall time: 2.11 s
print(f"shape of the submissions dataframe is {submissions.count():,}x{len(submissions.columns)}")
[Stage 1:=======================================================>(99 + 1) / 100]
shape of the submissions dataframe is 666,498x68
submissions.printSchema()
root |-- adserver_click_url: string (nullable = true) |-- adserver_imp_pixel: string (nullable = true) |-- archived: boolean (nullable = true) |-- author: string (nullable = true) |-- author_cakeday: boolean (nullable = true) |-- author_flair_css_class: string (nullable = true) |-- author_flair_text: string (nullable = true) |-- author_id: string (nullable = true) |-- brand_safe: boolean (nullable = true) |-- contest_mode: boolean (nullable = true) |-- created_utc: timestamp (nullable = true) |-- crosspost_parent: string (nullable = true) |-- crosspost_parent_list: array (nullable = true) | |-- element: struct (containsNull = true) | | |-- approved_at_utc: string (nullable = true) | | |-- approved_by: string (nullable = true) | | |-- archived: boolean (nullable = true) | | |-- author: string (nullable = true) | | |-- author_flair_css_class: string (nullable = true) | | |-- author_flair_text: string (nullable = true) | | |-- banned_at_utc: string (nullable = true) | | |-- banned_by: string (nullable = true) | | |-- brand_safe: boolean (nullable = true) | | |-- can_gild: boolean (nullable = true) | | |-- can_mod_post: boolean (nullable = true) | | |-- clicked: boolean (nullable = true) | | |-- contest_mode: boolean (nullable = true) | | |-- created: double (nullable = true) | | |-- created_utc: double (nullable = true) | | |-- distinguished: string (nullable = true) | | |-- domain: string (nullable = true) | | |-- downs: long (nullable = true) | | |-- edited: boolean (nullable = true) | | |-- gilded: long (nullable = true) | | |-- hidden: boolean (nullable = true) | | |-- hide_score: boolean (nullable = true) | | |-- id: string (nullable = true) | | |-- is_crosspostable: boolean (nullable = true) | | |-- is_reddit_media_domain: boolean (nullable = true) | | |-- is_self: boolean (nullable = true) | | |-- is_video: boolean (nullable = true) | | |-- likes: string (nullable = true) | | |-- link_flair_css_class: string (nullable = true) | | |-- link_flair_text: string (nullable = true) | | |-- locked: boolean (nullable = true) | | |-- media: string (nullable = true) | | |-- mod_reports: array (nullable = true) | | | |-- element: string (containsNull = true) | | |-- name: string (nullable = true) | | |-- num_comments: long (nullable = true) | | |-- num_crossposts: long (nullable = true) | | |-- num_reports: string (nullable = true) | | |-- over_18: boolean (nullable = true) | | |-- parent_whitelist_status: string (nullable = true) | | |-- permalink: string (nullable = true) | | |-- pinned: boolean (nullable = true) | | |-- quarantine: boolean (nullable = true) | | |-- removal_reason: string (nullable = true) | | |-- report_reasons: string (nullable = true) | | |-- saved: boolean (nullable = true) | | |-- score: long (nullable = true) | | |-- secure_media: string (nullable = true) | | |-- selftext: string (nullable = true) | | |-- selftext_html: string (nullable = true) | | |-- spoiler: boolean (nullable = true) | | |-- stickied: boolean (nullable = true) | | |-- subreddit: string (nullable = true) | | |-- subreddit_id: string (nullable = true) | | |-- subreddit_name_prefixed: string (nullable = true) | | |-- subreddit_type: string (nullable = true) | | |-- suggested_sort: string (nullable = true) | | |-- thumbnail: string (nullable = true) | | |-- thumbnail_height: string (nullable = true) | | |-- thumbnail_width: string (nullable = true) | | |-- title: string (nullable = true) | | |-- ups: long (nullable = true) | | |-- url: string (nullable = true) | | |-- user_reports: array (nullable = true) | | | |-- element: string (containsNull = true) | | |-- view_count: string (nullable = true) | | |-- visited: boolean (nullable = true) | | |-- whitelist_status: string (nullable = true) |-- disable_comments: boolean (nullable = true) |-- distinguished: string (nullable = true) |-- domain: string (nullable = true) |-- domain_override: string (nullable = true) |-- edited: string (nullable = true) |-- embed_type: string (nullable = true) |-- embed_url: string (nullable = true) |-- gilded: long (nullable = true) |-- hidden: boolean (nullable = true) |-- hide_score: boolean (nullable = true) |-- href_url: string (nullable = true) |-- id: string (nullable = true) |-- imp_pixel: string (nullable = true) |-- is_crosspostable: boolean (nullable = true) |-- is_reddit_media_domain: boolean (nullable = true) |-- is_self: boolean (nullable = true) |-- is_video: boolean (nullable = true) |-- link_flair_css_class: string (nullable = true) |-- link_flair_text: string (nullable = true) |-- locked: boolean (nullable = true) |-- media: struct (nullable = true) | |-- event_id: string (nullable = true) | |-- oembed: struct (nullable = true) | | |-- author_name: string (nullable = true) | | |-- author_url: string (nullable = true) | | |-- cache_age: long (nullable = true) | | |-- description: string (nullable = true) | | |-- height: long (nullable = true) | | |-- html: string (nullable = true) | | |-- provider_name: string (nullable = true) | | |-- provider_url: string (nullable = true) | | |-- thumbnail_height: long (nullable = true) | | |-- thumbnail_url: string (nullable = true) | | |-- thumbnail_width: long (nullable = true) | | |-- title: string (nullable = true) | | |-- type: string (nullable = true) | | |-- url: string (nullable = true) | | |-- version: string (nullable = true) | | |-- width: long (nullable = true) | |-- reddit_video: struct (nullable = true) | | |-- dash_url: string (nullable = true) | | |-- duration: long (nullable = true) | | |-- fallback_url: string (nullable = true) | | |-- height: long (nullable = true) | | |-- hls_url: string (nullable = true) | | |-- is_gif: boolean (nullable = true) | | |-- scrubber_media_url: string (nullable = true) | | |-- transcoding_status: string (nullable = true) | | |-- width: long (nullable = true) | |-- type: string (nullable = true) |-- media_embed: struct (nullable = true) | |-- content: string (nullable = true) | |-- height: long (nullable = true) | |-- scrolling: boolean (nullable = true) | |-- width: long (nullable = true) |-- mobile_ad_url: string (nullable = true) |-- num_comments: long (nullable = true) |-- num_crossposts: long (nullable = true) |-- original_link: string (nullable = true) |-- over_18: boolean (nullable = true) |-- parent_whitelist_status: string (nullable = true) |-- permalink: string (nullable = true) |-- pinned: boolean (nullable = true) |-- post_hint: string (nullable = true) |-- preview: struct (nullable = true) | |-- enabled: boolean (nullable = true) | |-- images: array (nullable = true) | | |-- element: struct (containsNull = true) | | | |-- id: string (nullable = true) | | | |-- resolutions: array (nullable = true) | | | | |-- element: struct (containsNull = true) | | | | | |-- height: long (nullable = true) | | | | | |-- url: string (nullable = true) | | | | | |-- width: long (nullable = true) | | | |-- source: struct (nullable = true) | | | | |-- height: long (nullable = true) | | | | |-- url: string (nullable = true) | | | | |-- width: long (nullable = true) | | | |-- variants: struct (nullable = true) | | | | |-- gif: struct (nullable = true) | | | | | |-- resolutions: array (nullable = true) | | | | | | |-- element: struct (containsNull = true) | | | | | | | |-- height: long (nullable = true) | | | | | | | |-- url: string (nullable = true) | | | | | | | |-- width: long (nullable = true) | | | | | |-- source: struct (nullable = true) | | | | | | |-- height: long (nullable = true) | | | | | | |-- url: string (nullable = true) | | | | | | |-- width: long (nullable = true) | | | | |-- mp4: struct (nullable = true) | | | | | |-- resolutions: array (nullable = true) | | | | | | |-- element: struct (containsNull = true) | | | | | | | |-- height: long (nullable = true) | | | | | | | |-- url: string (nullable = true) | | | | | | | |-- width: long (nullable = true) | | | | | |-- source: struct (nullable = true) | | | | | | |-- height: long (nullable = true) | | | | | | |-- url: string (nullable = true) | | | | | | |-- width: long (nullable = true) | | | | |-- nsfw: struct (nullable = true) | | | | | |-- resolutions: array (nullable = true) | | | | | | |-- element: struct (containsNull = true) | | | | | | | |-- height: long (nullable = true) | | | | | | | |-- url: string (nullable = true) | | | | | | | |-- width: long (nullable = true) | | | | | |-- source: struct (nullable = true) | | | | | | |-- height: long (nullable = true) | | | | | | |-- url: string (nullable = true) | | | | | | |-- width: long (nullable = true) | | | | |-- obfuscated: struct (nullable = true) | | | | | |-- resolutions: array (nullable = true) | | | | | | |-- element: struct (containsNull = true) | | | | | | | |-- height: long (nullable = true) | | | | | | | |-- url: string (nullable = true) | | | | | | | |-- width: long (nullable = true) | | | | | |-- source: struct (nullable = true) | | | | | | |-- height: long (nullable = true) | | | | | | |-- url: string (nullable = true) | | | | | | |-- width: long (nullable = true) |-- promoted: boolean (nullable = true) |-- promoted_by: string (nullable = true) |-- promoted_display_name: string (nullable = true) |-- promoted_url: string (nullable = true) |-- retrieved_on: timestamp (nullable = true) |-- score: long (nullable = true) |-- secure_media: struct (nullable = true) | |-- event_id: string (nullable = true) | |-- oembed: struct (nullable = true) | | |-- author_name: string (nullable = true) | | |-- author_url: string (nullable = true) | | |-- cache_age: long (nullable = true) | | |-- description: string (nullable = true) | | |-- height: long (nullable = true) | | |-- html: string (nullable = true) | | |-- provider_name: string (nullable = true) | | |-- provider_url: string (nullable = true) | | |-- thumbnail_height: long (nullable = true) | | |-- thumbnail_url: string (nullable = true) | | |-- thumbnail_width: long (nullable = true) | | |-- title: string (nullable = true) | | |-- type: string (nullable = true) | | |-- url: string (nullable = true) | | |-- version: string (nullable = true) | | |-- width: long (nullable = true) | |-- type: string (nullable = true) |-- secure_media_embed: struct (nullable = true) | |-- content: string (nullable = true) | |-- height: long (nullable = true) | |-- media_domain_url: string (nullable = true) | |-- scrolling: boolean (nullable = true) | |-- width: long (nullable = true) |-- selftext: string (nullable = true) |-- spoiler: boolean (nullable = true) |-- stickied: boolean (nullable = true) |-- subreddit: string (nullable = true) |-- subreddit_id: string (nullable = true) |-- suggested_sort: string (nullable = true) |-- third_party_trackers: array (nullable = true) | |-- element: string (containsNull = true) |-- third_party_tracking: string (nullable = true) |-- third_party_tracking_2: string (nullable = true) |-- thumbnail: string (nullable = true) |-- thumbnail_height: long (nullable = true) |-- thumbnail_width: long (nullable = true) |-- title: string (nullable = true) |-- url: string (nullable = true) |-- whitelist_status: string (nullable = true)
print(f"shape of the comments dataframe is {comments.count():,}x{len(comments.columns)}")
[Stage 5:======================================================>(245 + 1) / 246]
shape of the comments dataframe is 6,260,219x21
comments.printSchema()
root |-- author: string (nullable = true) |-- author_cakeday: boolean (nullable = true) |-- author_flair_css_class: string (nullable = true) |-- author_flair_text: string (nullable = true) |-- body: string (nullable = true) |-- can_gild: boolean (nullable = true) |-- controversiality: long (nullable = true) |-- created_utc: timestamp (nullable = true) |-- distinguished: string (nullable = true) |-- edited: string (nullable = true) |-- gilded: long (nullable = true) |-- id: string (nullable = true) |-- is_submitter: boolean (nullable = true) |-- link_id: string (nullable = true) |-- parent_id: string (nullable = true) |-- permalink: string (nullable = true) |-- retrieved_on: timestamp (nullable = true) |-- score: long (nullable = true) |-- stickied: boolean (nullable = true) |-- subreddit: string (nullable = true) |-- subreddit_id: string (nullable = true)
import pyspark.sql.functions as f
submissions = submissions.select(f.year("created_utc").alias("year"),f.month("created_utc").alias("month"),"subreddit","id","author","created_utc","title","selftext","num_comments","num_crossposts","score","is_self","stickied")
comments = comments.select(f.year("created_utc").alias("year"),f.month("created_utc").alias("month"),"subreddit","id","author","link_id","parent_id","created_utc","body","score","gilded","controversiality")
submissions.printSchema()
comments.printSchema()
# no datatype conversion needed
root |-- year: integer (nullable = true) |-- month: integer (nullable = true) |-- subreddit: string (nullable = true) |-- id: string (nullable = true) |-- author: string (nullable = true) |-- created_utc: timestamp (nullable = true) |-- title: string (nullable = true) |-- selftext: string (nullable = true) |-- num_comments: long (nullable = true) |-- num_crossposts: long (nullable = true) |-- score: long (nullable = true) |-- is_self: boolean (nullable = true) |-- stickied: boolean (nullable = true) root |-- year: integer (nullable = true) |-- month: integer (nullable = true) |-- subreddit: string (nullable = true) |-- id: string (nullable = true) |-- author: string (nullable = true) |-- link_id: string (nullable = true) |-- parent_id: string (nullable = true) |-- created_utc: timestamp (nullable = true) |-- body: string (nullable = true) |-- score: long (nullable = true) |-- gilded: long (nullable = true) |-- controversiality: long (nullable = true)
First take a look at the dataframe, some columns included values like deleted and removed which can be considered as missing values, which should be removed from rows
submissions.show(10)
[Stage 2:> (0 + 1) / 1]
+----+-----+-----------+------+--------------------+-------------------+--------------------+---------+------------+--------------+-----+-------+--------+ |year|month| subreddit| id| author| created_utc| title| selftext|num_comments|num_crossposts|score|is_self|stickied| +----+-----+-----------+------+--------------------+-------------------+--------------------+---------+------------+--------------+-----+-------+--------+ |2021| 6| Music|o2omjb| the_dionysian_1|2021-06-18 13:03:18|Bo Burnham - Welc...| | 1656| 2|20968| false| false| |2021| 6|TaylorSwift|o2ongy| niamhronald|2021-06-18 13:04:37|taylor loves her ...|[removed]| 0| 0| 1| true| false| |2021| 6| Music|o2onm6| Seismic_Noise|2021-06-18 13:04:51|LIQUERUS Resident...| | 0| 0| 2| false| false| |2021| 6| Music|o2oop3|SteveNewmanGuitarist|2021-06-18 13:06:31|Steve Newman - Rd...| | 0| 0| 1| false| false| |2021| 6| Music|o2ootz| [deleted]|2021-06-18 13:06:42|OK so I've been m...|[deleted]| 1| 0| 1| false| false| |2021| 6| Music|o2op0p| Zoinksbeats|2021-06-18 13:07:01|(FREE) Playboi Ca...| | 1| 0| 1| false| false| |2021| 6| Music|o2oqzq| salem-1200|2021-06-18 13:10:02| Attention Required!| | 1| 0| 1| false| false| |2021| 6| Music|o2or35| [deleted]|2021-06-18 13:10:09|Zapo Maze - All T...|[deleted]| 1| 0| 1| false| false| |2021| 6| Music|o2org3| BiscuitsAndTeaCups|2021-06-18 13:10:43|Biscuit Sundown -...| | 0| 0| 1| false| false| |2021| 6| Music|o2orxb| [deleted]|2021-06-18 13:11:30|Zapo Maze - All T...|[deleted]| 1| 0| 1| false| false| +----+-----+-----------+------+--------------------+-------------------+--------------------+---------+------------+--------------+-----+-------+--------+ only showing top 10 rows
comments.show(10)
+----+-----+-----------+-------+--------------+---------+----------+-------------------+--------------------+-----+------+----------------+ |year|month| subreddit| id| author| link_id| parent_id| created_utc| body|score|gilded|controversiality| +----+-----+-----------+-------+--------------+---------+----------+-------------------+--------------------+-----+------+----------------+ |2022| 10|TaylorSwift|irf7cfk|realscoutfinch|t3_xwui3g|t1_iracq55|2022-10-07 16:46:13|This this this! I...| 1| 0| 0| |2022| 10| Music|irf7cqw| Alouitious|t3_xxz930|t1_irf6yfd|2022-10-07 16:46:17|4. Once you've bu...| 4| 0| 0| |2022| 10| Music|irf7dpm| ToastedSimian|t3_xy1kr4|t1_ireuzta|2022-10-07 16:46:29|Sadly, I'm a work...| 1| 0| 0| |2022| 10| Music|irf7drt| tommykiddo|t3_xxz930|t1_irf39w8|2022-10-07 16:46:30|Ultimate Guitar h...| 3| 0| 0| |2022| 10| Music|irf7ghz| monkee67|t3_xy0jgc| t3_xy0jgc|2022-10-07 16:47:03|MEH \n\nBTW this...| -2| 0| 1| |2022| 10|TaylorSwift|irf7gn3| dietrichs90|t3_xt3tob| t3_xt3tob|2022-10-07 16:47:05|Taylors caption t...| 21| 0| 0| |2022| 10| Music|irf7jai| dogsarefun|t3_xxrwic|t1_irdprwc|2022-10-07 16:47:38|Also, Aesop Rock ...| 0| 0| 0| |2022| 10|TaylorSwift|irf7jgy| evergreenkat|t3_xxy08f| t3_xxy08f|2022-10-07 16:47:40|The whole left co...| 2| 0| 0| |2022| 10|TaylorSwift|irf7qr0| sapphicsato|t3_xxr3aj| t3_xxr3aj|2022-10-07 16:49:08|IWAASPIWTWWGROMBF...| 1| 0| 0| |2022| 10|TaylorSwift|irf7rn4| [deleted]|t3_xt3tob| t3_xt3tob|2022-10-07 16:49:19| [deleted]| 1| 0| 0| +----+-----+-----------+-------+--------------+---------+----------+-------------------+--------------------+-----+------+----------------+ only showing top 10 rows
# drop any rows with na
submissions = submissions.dropna()
comments = comments.dropna()
# drop any records before 2021 or after 2023
submissions = submissions.filter((submissions.year >=2021) & (submissions.year <= 2023))
comments = comments.filter((comments.year>=2021) & (comments.year <= 2023))
# drop any records with invalid month values
months = [int(x) for x in range(1,13)]
submissions = submissions.filter(submissions.month.isin(months))
comments = comments.filter(comments.month.isin(months))
# drop rows containing [deleted] or [removed] values
removed_or_deleted = ["[removed]","[deleted]"]
submissions = submissions.filter(~submissions.author.isin(removed_or_deleted)& ~submissions.title.isin(removed_or_deleted) & ~submissions.selftext.isin(removed_or_deleted))
comments = comments.filter(~comments.author.isin(removed_or_deleted)& ~comments.body.isin(removed_or_deleted))
submissions.show(10)
[Stage 4:> (0 + 1) / 1]
+----+-----+---------+------+--------------------+-------------------+--------------------+--------+------------+--------------+-----+-------+--------+ |year|month|subreddit| id| author| created_utc| title|selftext|num_comments|num_crossposts|score|is_self|stickied| +----+-----+---------+------+--------------------+-------------------+--------------------+--------+------------+--------------+-----+-------+--------+ |2021| 6| Music|o2omjb| the_dionysian_1|2021-06-18 13:03:18|Bo Burnham - Welc...| | 1656| 2|20968| false| false| |2021| 6| Music|o2onm6| Seismic_Noise|2021-06-18 13:04:51|LIQUERUS Resident...| | 0| 0| 2| false| false| |2021| 6| Music|o2oop3|SteveNewmanGuitarist|2021-06-18 13:06:31|Steve Newman - Rd...| | 0| 0| 1| false| false| |2021| 6| Music|o2op0p| Zoinksbeats|2021-06-18 13:07:01|(FREE) Playboi Ca...| | 1| 0| 1| false| false| |2021| 6| Music|o2oqzq| salem-1200|2021-06-18 13:10:02| Attention Required!| | 1| 0| 1| false| false| |2021| 6| Music|o2org3| BiscuitsAndTeaCups|2021-06-18 13:10:43|Biscuit Sundown -...| | 0| 0| 1| false| false| |2021| 6| Music|o2otxk| Funkedalic|2021-06-18 13:14:32|XTC - That's Real...| | 0| 0| 2| false| false| |2021| 6| Music|o2ou3y| Taffia1977|2021-06-18 13:14:46|Armand the Chemis...| | 1| 0| 2| false| false| |2021| 6| Music|o2ouv2| TorontoRichard|2021-06-18 13:15:53|Skip Stompin’ Ric...| | 0| 0| 0| false| false| |2021| 6| Music|o2oww6| ShiftAxisRecords|2021-06-18 13:18:51|[Powerful Vocal H...| | 1| 0| 1| false| false| +----+-----+---------+------+--------------------+-------------------+--------------------+--------+------------+--------------+-----+-------+--------+ only showing top 10 rows
comments.show(10)
+----+-----+-----------+-------+--------------+---------+----------+-------------------+--------------------+-----+------+----------------+ |year|month| subreddit| id| author| link_id| parent_id| created_utc| body|score|gilded|controversiality| +----+-----+-----------+-------+--------------+---------+----------+-------------------+--------------------+-----+------+----------------+ |2022| 10|TaylorSwift|irf7cfk|realscoutfinch|t3_xwui3g|t1_iracq55|2022-10-07 16:46:13|This this this! I...| 1| 0| 0| |2022| 10| Music|irf7cqw| Alouitious|t3_xxz930|t1_irf6yfd|2022-10-07 16:46:17|4. Once you've bu...| 4| 0| 0| |2022| 10| Music|irf7dpm| ToastedSimian|t3_xy1kr4|t1_ireuzta|2022-10-07 16:46:29|Sadly, I'm a work...| 1| 0| 0| |2022| 10| Music|irf7drt| tommykiddo|t3_xxz930|t1_irf39w8|2022-10-07 16:46:30|Ultimate Guitar h...| 3| 0| 0| |2022| 10| Music|irf7ghz| monkee67|t3_xy0jgc| t3_xy0jgc|2022-10-07 16:47:03|MEH \n\nBTW this...| -2| 0| 1| |2022| 10|TaylorSwift|irf7gn3| dietrichs90|t3_xt3tob| t3_xt3tob|2022-10-07 16:47:05|Taylors caption t...| 21| 0| 0| |2022| 10| Music|irf7jai| dogsarefun|t3_xxrwic|t1_irdprwc|2022-10-07 16:47:38|Also, Aesop Rock ...| 0| 0| 0| |2022| 10|TaylorSwift|irf7jgy| evergreenkat|t3_xxy08f| t3_xxy08f|2022-10-07 16:47:40|The whole left co...| 2| 0| 0| |2022| 10|TaylorSwift|irf7qr0| sapphicsato|t3_xxr3aj| t3_xxr3aj|2022-10-07 16:49:08|IWAASPIWTWWGROMBF...| 1| 0| 0| |2022| 10| Music|irf7txv| Kidspud|t3_xy0jgc|t1_irepwh7|2022-10-07 16:49:47|I agree 100%. I f...| 17| 0| 0| +----+-----+-----------+-------+--------------+---------+----------+-------------------+--------------------+-----+------+----------------+ only showing top 10 rows
selftext column contained empty string, but the title, comments and scores are still informative. So we want to keep the title instead of removing the rows
submissions_concat_text = submissions.select("year","month","subreddit","id","author","created_utc",f.concat_ws(" ",submissions.title,submissions.selftext).alias("text"),"num_comments","num_crossposts","score","is_self","stickied")
submissions_concat_text.show(10)
+----+-----+---------+------+--------------------+-------------------+--------------------+------------+--------------+-----+-------+--------+ |year|month|subreddit| id| author| created_utc| text|num_comments|num_crossposts|score|is_self|stickied| +----+-----+---------+------+--------------------+-------------------+--------------------+------------+--------------+-----+-------+--------+ |2021| 6| Music|o2omjb| the_dionysian_1|2021-06-18 13:03:18|Bo Burnham - Welc...| 1656| 2|20968| false| false| |2021| 6| Music|o2onm6| Seismic_Noise|2021-06-18 13:04:51|LIQUERUS Resident...| 0| 0| 2| false| false| |2021| 6| Music|o2oop3|SteveNewmanGuitarist|2021-06-18 13:06:31|Steve Newman - Rd...| 0| 0| 1| false| false| |2021| 6| Music|o2op0p| Zoinksbeats|2021-06-18 13:07:01|(FREE) Playboi Ca...| 1| 0| 1| false| false| |2021| 6| Music|o2oqzq| salem-1200|2021-06-18 13:10:02|Attention Required! | 1| 0| 1| false| false| |2021| 6| Music|o2org3| BiscuitsAndTeaCups|2021-06-18 13:10:43|Biscuit Sundown -...| 0| 0| 1| false| false| |2021| 6| Music|o2otxk| Funkedalic|2021-06-18 13:14:32|XTC - That's Real...| 0| 0| 2| false| false| |2021| 6| Music|o2ou3y| Taffia1977|2021-06-18 13:14:46|Armand the Chemis...| 1| 0| 2| false| false| |2021| 6| Music|o2ouv2| TorontoRichard|2021-06-18 13:15:53|Skip Stompin’ Ric...| 0| 0| 0| false| false| |2021| 6| Music|o2oww6| ShiftAxisRecords|2021-06-18 13:18:51|[Powerful Vocal H...| 1| 0| 1| false| false| +----+-----+---------+------+--------------------+-------------------+--------------------+------------+--------------+-----+-------+--------+ only showing top 10 rows
Remove rows with empty strings in comments text
comments = comments.filter(f.col("body") != '')
remove rows with meaningless values occurred in numerical variables
submissions_concat_text = submissions_concat_text.filter((f.col("num_comments")>=0) & (f.col("num_crossposts")>=0))
comments = comments.filter((f.col("gilded")>=0) & (f.col("controversiality")>=0))
print(f"shape of the submissions dataframe is {submissions_concat_text.count():,}x{len(submissions_concat_text.columns)}")
[Stage 7:=======================================================>(99 + 1) / 100]
shape of the submissions dataframe is 391,059x12
print(f"shape of the comments dataframe is {comments.count():,}x{len(comments.columns)}")
[Stage 10:=====================================================>(245 + 1) / 246]
shape of the comments dataframe is 5,750,926x12
submissions_concat_text.printSchema()
root |-- year: integer (nullable = true) |-- month: integer (nullable = true) |-- subreddit: string (nullable = true) |-- id: string (nullable = true) |-- author: string (nullable = true) |-- created_utc: timestamp (nullable = true) |-- text: string (nullable = false) |-- num_comments: long (nullable = true) |-- num_crossposts: long (nullable = true) |-- score: long (nullable = true) |-- is_self: boolean (nullable = true) |-- stickied: boolean (nullable = true)
comments.printSchema()
root |-- year: integer (nullable = true) |-- month: integer (nullable = true) |-- subreddit: string (nullable = true) |-- id: string (nullable = true) |-- author: string (nullable = true) |-- link_id: string (nullable = true) |-- parent_id: string (nullable = true) |-- created_utc: timestamp (nullable = true) |-- body: string (nullable = true) |-- score: long (nullable = true) |-- gilded: long (nullable = true) |-- controversiality: long (nullable = true)
bucket = "dsan-6000-group-35"
comments.write.parquet(f"s3a://{bucket}/comments.parquet")
submissions_concat_text.write.parquet(f"s3a://{bucket}/submissions.parquet")
!pip install pycountry
!pip install langdetect
!pip install kaleido
Collecting pycountry Obtaining dependency information for pycountry from https://files.pythonhosted.org/packages/63/6a/30150d5aaa7c4a979e5360df0f95b595fbf6eb1897c4ddc6426b69cbaf66/pycountry-23.12.7-py3-none-any.whl.metadata Downloading pycountry-23.12.7-py3-none-any.whl.metadata (9.9 kB) Downloading pycountry-23.12.7-py3-none-any.whl (6.2 MB) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 6.2/6.2 MB 32.5 MB/s eta 0:00:00:00:0100:01 Installing collected packages: pycountry Successfully installed pycountry-23.12.7 WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv [notice] A new release of pip is available: 23.2.1 -> 23.3.1 [notice] To update, run: pip install --upgrade pip Collecting langdetect Using cached langdetect-1.0.9-py3-none-any.whl Requirement already satisfied: six in /opt/conda/lib/python3.10/site-packages (from langdetect) (1.16.0) Installing collected packages: langdetect Successfully installed langdetect-1.0.9 WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv [notice] A new release of pip is available: 23.2.1 -> 23.3.1 [notice] To update, run: pip install --upgrade pip Collecting kaleido Using cached kaleido-0.2.1-py2.py3-none-manylinux1_x86_64.whl (79.9 MB) Installing collected packages: kaleido Successfully installed kaleido-0.2.1 WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv [notice] A new release of pip is available: 23.2.1 -> 23.3.1 [notice] To update, run: pip install --upgrade pip
submissions = spark.read.parquet("s3a://dsan-6000-group-35/submissions.parquet", header=True)
comments = spark.read.parquet("s3a://dsan-6000-group-35/comments.parquet", header=True)
23/12/08 21:18:27 WARN MetricsConfig: Cannot locate configuration: tried hadoop-metrics2-s3a-file-system.properties,hadoop-metrics2.properties
taylor_submissions = submissions.filter(submissions.subreddit == "TaylorSwift")
taylor_comments = comments.filter(comments.subreddit == "TaylorSwift")
# get the country list
import pycountry
import re
country_info = list(pycountry.countries)
country_name = [country.name for country in country_info]
import pyspark.sql.functions as f
# create a new column as dummy variable to find if country has been mentioned inthe text
pattern = r'\b(?:' + '|'.join(re.escape(name) for name in country_name) + r')\b'
taylor_submissions = taylor_submissions.withColumn("country_mentioned",f.col("text").rlike(pattern))
taylor_comments = taylor_comments.withColumn("country_mentioned",f.col("body").rlike(pattern))
taylor_submissions.show(10)
[Stage 2:> (0 + 1) / 1]
+----+-----+-----------+------+--------------------+-------------------+--------------------+------------+--------------+-----+-------+--------+-----------------+ |year|month| subreddit| id| author| created_utc| text|num_comments|num_crossposts|score|is_self|stickied|country_mentioned| +----+-----+-----------+------+--------------------+-------------------+--------------------+------------+--------------+-----+-------+--------+-----------------+ |2021| 3|TaylorSwift|m2tbw3| storm131713|2021-03-11 15:52:54|The most connecti...| 30| 0| 43| true| false| false| |2021| 3|TaylorSwift|m2td8l| nostalgia-geek|2021-03-11 15:54:30|I just moved and ...| 9| 0| 165| false| false| false| |2021| 1|TaylorSwift|kua4oj| franklintbassett|2021-01-10 07:37:10|If you had to say...| 19| 0| 28| true| false| false| |2021| 1|TaylorSwift|kuaero|stillwantthekidsmenu|2021-01-10 08:00:14|Favorite line fro...| 25| 0| 51| true| false| false| |2021| 11|TaylorSwift|qxjbc8| ScheduleHuman326|2021-11-19 16:03:53|When you are at s...| 1| 0| 1| false| false| false| |2021| 11|TaylorSwift|qxjjqf| edthrowwaway|2021-11-19 16:15:00|the new backgroun...| 8| 0| 88| false| false| false| |2021| 11|TaylorSwift|qxjl9i| edthrowwaway|2021-11-19 16:17:02|the new backgroun...| 1| 0| 1| false| false| false| |2021| 11|TaylorSwift|qxjnav| billieexists|2021-11-19 16:20:01|Where would you p...| 12| 0| 16| true| false| false| |2021| 11|TaylorSwift|qxjp1u| 20escapades|2021-11-19 16:22:24|The train running...| 2| 0| 64| false| false| false| |2021| 11|TaylorSwift|qxju5h| wefoundtaylorland13|2021-11-19 16:29:34|"Carole, you and ...| 1| 0| 1| false| false| false| +----+-----+-----------+------+--------------------+-------------------+--------------------+------------+--------------+-----+-------+--------+-----------------+ only showing top 10 rows
# summary1 shows the number and the percentage that country has been mentioned in submission dataset
summary1 = taylor_submissions.groupBy("country_mentioned").count()
summary1 = summary1.withColumn("percentage",f.round(f.col("count")/taylor_submissions.count()*100,2))
summary1.show()
[Stage 6:============================================> (3 + 1) / 4]
+-----------------+-----+----------+ |country_mentioned|count|percentage| +-----------------+-----+----------+ | true| 343| 0.84| | false|40421| 99.16| +-----------------+-----+----------+
# summary2 shows the number and the percentage that country has been mentioned in comments dataset
summary2 = taylor_comments.groupBy("country_mentioned").count()
summary2 = summary2.withColumn("percentage",f.round(f.col("count")/taylor_comments.count()*100,2))
summary2.show()
[Stage 12:====================================================> (14 + 1) / 15]
+-----------------+-------+----------+ |country_mentioned| count|percentage| +-----------------+-------+----------+ | true| 8301| 0.46| | false|1810014| 99.54| +-----------------+-------+----------+
# save summary tables to repo
summary1 = summary1.withColumn("country_mentioned",f.when(summary1.country_mentioned,"Yes").otherwise("No"))
summary2 = summary2.withColumn("country_mentioned",f.when(summary2.country_mentioned,"Yes").otherwise("No"))
summary1.toPandas().to_csv('../data/csv/country_summary_table_submissions.csv')
summary2.toPandas().to_csv('../data/csv/country_summary_table_comments.csv')
# filter the records with country mentioned only
df_submissions = taylor_submissions.filter(f.col("country_mentioned")==True)
df_comments = taylor_comments.filter(f.col("country_mentioned")==True)
pattern = r'\b(?:' + '|'.join(re.escape(name) for name in country_name) + r')\b'
# extract the exact country from these text
df_submissions = df_submissions.withColumn("country", f.when(f.col("text").rlike(pattern),f.regexp_extract(f.col("text"),pattern,0)).otherwise(None))
df_comments = df_comments.withColumn("country", f.when(f.col("body").rlike(pattern),f.regexp_extract(f.col("body"),pattern,0)).otherwise(None))
# make country data to list
submissions_countries = df_submissions.select('country').rdd.flatMap(list).collect()
comments_countries = df_comments.select('country').rdd.flatMap(list).collect()
import pandas as pd
from collections import Counter
country_count = pd.DataFrame(Counter(submissions_countries+comments_countries).most_common(),columns = ['Country','Count']).to_csv("country_count.csv",index = False)
top_20 = Counter(submissions_countries+comments_countries).most_common(20)
country,count = zip(*top_20)
top_20_df = pd.DataFrame(data = {'Country':list(country),'Count':list(count)})
top_20_df.to_csv("top_20_country.csv",index = False)
#import seaborn as sns
#import matplotlib.pyplot as plt
import plotly.graph_objects as go
import plotly.express as px
import plotly.io as pio
import kaleido
fig = go.Figure()
fig.add_trace(go.Bar(x = top_20_df['Count'],y = top_20_df['Country'],orientation = 'h',
text = top_20_df['Count'],
textposition = 'outside',
marker = dict(color = px.colors.qualitative.Dark24)
))
fig.update_layout(
title = 'Top 20 Countries Mentioned in Taylor Swift Subreddit',
xaxis_title = 'Count',
yaxis_title = 'Country',
plot_bgcolor = 'white',
#width = 800,
height = 500)
#pio.write_image(fig,"country_analysis_bar_plot.png")
#fig.write_image("country_analysis_bar_plot.svg")
fig.show()
# reduce the row numbers for computing and visualization, keep about 10000 records for each dataset
taylor_submissions_sample = taylor_submissions.sample(withReplacement = False,fraction = 10000/taylor_submissions.count(),seed = 123)
taylor_comments_sample = taylor_comments.sample(withReplacement = False,fraction = 10000/taylor_comments.count(),seed = 456)
!pip install langdetect
!pip install python-iso639
Requirement already satisfied: langdetect in /opt/conda/lib/python3.10/site-packages (1.0.9) Requirement already satisfied: six in /opt/conda/lib/python3.10/site-packages (from langdetect) (1.16.0) WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv [notice] A new release of pip is available: 23.2.1 -> 23.3.1 [notice] To update, run: pip install --upgrade pip Collecting python-iso639 Obtaining dependency information for python-iso639 from https://files.pythonhosted.org/packages/b8/6d/5d1f7e5c1b0c58b700eb67dbb570f9381afc90bc0535686a89e90eac5dfb/python_iso639-2023.6.15-py3-none-any.whl.metadata Using cached python_iso639-2023.6.15-py3-none-any.whl.metadata (13 kB) Using cached python_iso639-2023.6.15-py3-none-any.whl (275 kB) Installing collected packages: python-iso639 Successfully installed python-iso639-2023.6.15 WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv [notice] A new release of pip is available: 23.2.1 -> 23.3.1 [notice] To update, run: pip install --upgrade pip
from langdetect import detect
from pyspark.sql.types import StringType
import pyspark.sql.functions as f
# make udf, take text as input, return the language detected, return None if unable to determine the language
def get_language(text):
try:
return detect(text)
except:
return None
get_language_udf = f.udf(get_language,StringType())
# apply the udf, get language, get text length and score for further analysis
taylor_submissions_sample = taylor_submissions_sample.withColumn('language',get_language_udf(taylor_submissions_sample['text']))\
.withColumn("text_length",f.length(taylor_submissions_sample.text))\
.select('language','text_length','score')
taylor_comments_sample = taylor_comments_sample.withColumn('language',get_language_udf(taylor_comments_sample['body']))\
.withColumn("text_length",f.length(taylor_comments_sample.body))\
.select('language','text_length','score')
taylor_submissions_sample.show(10)
[Stage 23:> (0 + 1) / 1]
+--------+-----------+-----+ |language|text_length|score| +--------+-----------+-----+ | en| 581| 43| | en| 76| 1| | en| 377| 100| | en| 81| 1233| | ca| 14| 1| | en| 357| 35| | en| 44| 1| | en| 417| 35| | en| 480| 23| | de| 72| 42| +--------+-----------+-----+ only showing top 10 rows
# compute language frequency, average text length, and average score
submissions_sample_agg = taylor_submissions_sample.groupBy('language').agg(f.round(f.avg('text_length'),2).alias('Average Text Length'),f.round(f.avg('score'),2).alias('Average Score'),f.count(f.col('language')).alias('Count'))\
.orderBy(f.col('Count').desc()).dropna()
submissions_sample_agg.show(10)
submissions_sample_agg.cache()
+--------+-------------------+-------------+-----+ |language|Average Text Length|Average Score|Count| +--------+-------------------+-------------+-----+ | en| 371.08| 177.25| 9708| | de| 33.38| 76.16| 63| | no| 31.76| 259.95| 38| | da| 32.0| 150.38| 37| | it| 27.83| 195.14| 29| | af| 29.57| 122.21| 28| | nl| 30.0| 108.38| 26| | fr| 25.74| 130.78| 23| | ca| 30.0| 114.4| 20| | sv| 27.37| 231.58| 19| +--------+-------------------+-------------+-----+ only showing top 10 rows
DataFrame[language: string, Average Text Length: double, Average Score: double, Count: bigint]
comments_sample_agg = taylor_comments_sample.groupBy('language').agg(f.round(f.avg('text_length'),2).alias('Average Text Length'),f.round(f.avg('score'),2).alias('Average Score'),f.count(f.col('language')).alias('Count'))\
.orderBy(f.col('Count').desc()).dropna()
comments_sample_agg.show(10)
comments_sample_agg.cache()
[Stage 27:====================================================> (14 + 1) / 15]
+--------+-------------------+-------------+-----+ |language|Average Text Length|Average Score|Count| +--------+-------------------+-------------+-----+ | en| 177.54| 13.77| 9235| | af| 25.56| 9.79| 77| | so| 15.36| 12.64| 75| | de| 17.79| 17.12| 66| | fr| 16.62| 4.2| 60| | no| 23.6| 12.5| 52| | tr| 7.6| 3.65| 40| | tl| 16.76| 5.45| 38| | pt| 11.8| 4.49| 35| | id| 13.91| 2.82| 34| +--------+-------------------+-------------+-----+ only showing top 10 rows
DataFrame[language: string, Average Text Length: double, Average Score: double, Count: bigint]
We can see that English language dominates the subreddit, remove english and analysis on the remaining languages
submissions_sample_agg = submissions_sample_agg.filter(f.col('language') !='en')
comments_sample_agg = comments_sample_agg.filter(f.col('language') != 'en')
# convert iso-639 language code to language names
import iso639
def code_to_name(lang_code):
try:
lang_info = iso639.Language.from_part1(lang_code)
return lang_info.name
except:
return None
code_to_name_udf = f.udf(code_to_name,StringType())
submissions_sample_agg = submissions_sample_agg.withColumn("lang_name",code_to_name_udf(f.col('language'))).drop('language')
comments_sample_agg = comments_sample_agg.withColumn("lang_name",code_to_name_udf(f.col('language'))).drop('language')
submissions_sample_agg.cache()
comments_sample_agg.cache()
submissions_sample_agg.show(10)
[Stage 40:================================================> (12 + 2) / 14]
+-------------------+-------------+-----+---------+ |Average Text Length|Average Score|Count|lang_name| +-------------------+-------------+-----+---------+ | 33.67| 99.33| 61| German| | 30.26| 262.97| 38|Norwegian| | 31.78| 148.75| 36| Danish| | 27.69| 170.31| 32| Italian| | 28.5| 201.0| 28|Afrikaans| | 28.92| 95.04| 26| Dutch| | 28.32| 135.5| 22| French| | 30.53| 260.84| 19| Swedish| | 30.5| 128.11| 18| Catalan| | 25.0| 154.06| 16| Spanish| +-------------------+-------------+-----+---------+ only showing top 10 rows
# pyspark df to pandas df for visualization
submissions_sample_agg_df = submissions_sample_agg.toPandas()
comments_sample_agg_df = comments_sample_agg.toPandas()
submissions_sample_agg_df.to_csv("text_and_score_bubble_plot_submissions.csv",index = False)
comments_sample_agg_df.to_csv("text_and_score_bubble_plot_comments.csv",index = False)
import plotly.express as px
fig = px.scatter(submissions_sample_agg_df,x = "Average Text Length",y = "Average Score",size = 'Count',text = "lang_name",template = "plotly_white")
fig.update_traces(textposition='top center')
fig.update_layout(
height = 450,
width = 1100,
title_text='Text Length and Score for Non-English Submissions'
)
#fig.write_image("language_analysis_submissions.svg")
fig.show()
fig = px.scatter(comments_sample_agg_df,x = "Average Text Length",y = "Average Score",size = 'Count',text = "lang_name",template = "plotly_white")
fig.update_traces(textposition='top center')
fig.update_layout(
height = 450,
width = 1100,
title_text='Text Length and Score for Non-English Comments'
)
#fig.write_image("language_analysis_comments.svg")
fig.show()
The scatter plots visually present the usage of languages other than English in the Taylor Swift subreddit. The scatter plot radius signifies the frequency of each language occurrence, while the x-axis reflects the comment length, and the y-axis captures the average score. Notably, German, Norwegian, French, and Afrikaans emerge as the most prevalent non-English languages, with comments typically around 20 words —significantly briefer than English comments. Nevertheless, within the realm of non-English comments, these expressions maintain a noteworthy length, reaching an average score of approximately 12. It's evident that the forum is predominantly used by English speakers, followed by users of European languages. This analysis indicates that in a forum primarily dominated by English, users of European languages, while fewer in number, engage in conversations of relatively high quality.