{ "cells": [ { "cell_type": "code", "execution_count": 7, "id": "e8c00b70-9c91-4c38-bb25-4a3183b4457f", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: findspark in ./anaconda3/lib/python3.10/site-packages (2.0.1)\n" ] } ], "source": [ "!pip install findspark" ] }, { "cell_type": "code", "execution_count": 8, "id": "4dfcba8c-8d95-40ed-8ecb-7e34fe140289", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: graphframes in ./anaconda3/lib/python3.10/site-packages (0.6)\n", "Requirement already satisfied: numpy in ./anaconda3/lib/python3.10/site-packages (from graphframes) (1.26.4)\n", "Requirement already satisfied: nose in ./anaconda3/lib/python3.10/site-packages (from graphframes) (1.3.7)\n" ] } ], "source": [ "!pip install graphframes" ] }, { "cell_type": "code", "execution_count": 9, "id": "e33f8a74-6c24-408e-b593-1d0ffc27019d", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "'/shared/spark-3.4.4-bin-hadoop3'" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import findspark\n", "findspark.init()\n", "findspark.find()" ] }, { "cell_type": "code", "execution_count": 10, "id": "0b3c2d33-ab73-4882-8776-6362e4f023d7", "metadata": {}, "outputs": [], "source": [ "#spark.stop()" ] }, { "cell_type": "code", "execution_count": 11, "id": "d6e2cc7c-7cfb-4d5a-9c85-075d0e290321", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "26/02/21 16:06:19 WARN SparkConf: Note that spark.local.dir will be overridden by the value set by the cluster manager (via SPARK_LOCAL_DIRS in mesos/standalone/kubernetes and LOCAL_DIRS in YARN).\n" ] } ], "source": [ "from pyspark.sql import SparkSession\n", "\n", "# HALF of your cluster cores: 224 (7 workers * 64 cores = 448 total)\n", "cores_per_app = \"224\"\n", "\n", "spark = (\n", " SparkSession.builder\n", " .master(\"spark://master:7077\")\n", " .appName(\"Stackoverflow_Graphframes_app1\")\n", " .config(\"spark.cores.max\", cores_per_app) # cap total cores for this app\n", " .config(\"spark.executor.instances\", \"56\") # 56 executors\n", " .config(\"spark.executor.cores\", \"4\") # 4 cores per executor -> 56*4 = 224\n", " .config(\"spark.executor.memory\", \"6g\") # per-executor memory\n", " .config(\"spark.driver.memory\", \"6g\")\n", " .config(\"spark.sql.shuffle.partitions\", \"200\")\n", " .getOrCreate()\n", ")\n" ] }, { "cell_type": "code", "execution_count": 12, "id": "cfc612f1-b0d5-4d07-b414-411b05db4264", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 9:> (0 + 1) / 1]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "CPU times: user 41.4 ms, sys: 12.6 ms, total: 54 ms\n", "Wall time: 27.5 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time \n", "badge_df = spark.read.option(\"header\", True).csv(\"hdfs:///stackoverflow/badges.csv\")\n", "comment_df = spark.read.option(\"header\", True).csv(\"hdfs:///stackoverflow/comments_final.csv\")\n", "post_answer_df = spark.read.option(\"header\", True).csv(\"hdfs:///stackoverflow/posts_answers_fixed.csv\")\n", "post_question_df = spark.read.option(\"header\", True).csv(\"hdfs:///stackoverflow/posts_questions_cleaned1.csv\")\n", "post_tag_wiki_df = spark.read.option(\"header\", True).csv(\"hdfs:///stackoverflow/posts_tag_wiki_clean.csv\")\n", "post_tag_wiki_excerpt_df = spark.read.option(\"header\", True).csv(\"hdfs:///stackoverflow/posts_tag_wiki_excerpt_100PCT.csv\")\n", "user_df = spark.read.option(\"header\", True).csv(\"hdfs:///stackoverflow/users_clean.csv\")\n", "tag_df = spark.read.option(\"header\", True).csv(\"hdfs:///stackoverflow/tags.csv\")\n", "vote_df = spark.read.option(\"header\", True).csv(\"hdfs:///stackoverflow/votes.csv\")\n", "post_links_df = spark.read.option(\"header\", True).csv(\"hdfs:///stackoverflow/posts_links.csv\")" ] }, { "cell_type": "code", "execution_count": 13, "id": "86f8e3e2-9444-41f0-98eb-7082cf6f12ef", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 10:> (0 + 1) / 1]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-------+------------+--------------------+-------+-----+---------+\n", "| id| name| date|user_id|class|tag_based|\n", "+-------+------------+--------------------+-------+-----+---------+\n", "| 226296|Great Answer|2009-02-03 21:22:...| 24243| 1| false|\n", "| 601701| Electorate|2010-01-02 07:42:...| 1116| 1| false|\n", "| 685963| Electorate|2010-02-25 09:26:...| 33708| 1| false|\n", "| 774319| javascript|2010-04-18 22:17:...| 19068| 1| true|\n", "|1106944| Populist|2010-10-04 14:07:...| 227665| 1| false|\n", "+-------+------------+--------------------+-------+-----+---------+\n", "only showing top 5 rows\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "badge_df.show(5)" ] }, { "cell_type": "code", "execution_count": 14, "id": "40ee35a3-7145-4201-a163-83fe88c587dc", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 11:> (0 + 1) / 1]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+---+--------------------+--------------------+-------+-------+-----------------+-----+\n", "| id| text| creation_date|post_id|user_id|user_display_name|score|\n", "+---+--------------------+--------------------+-------+-------+-----------------+-----+\n", "| 10|It will help if y...|2008-09-06 13:38:...| 45651| 242| null| 6|\n", "| 25|infact it does. L...|2008-09-06 17:37:...| 47626| 4213| null| 10|\n", "| 27|Currently + is im...|2008-09-06 19:25:...| 47624| 4064| null| 7|\n", "| 41|I dont think that...|2008-09-06 22:32:...| 47885| 4213| null| 18|\n", "| 59|Its still very us...|2008-09-07 00:13:...| 47885| 2915| null| 12|\n", "+---+--------------------+--------------------+-------+-------+-----------------+-----+\n", "only showing top 5 rows\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "comment_df.show(5)" ] }, { "cell_type": "code", "execution_count": 15, "id": "cea7b97d-c9c3-4d45-ad05-86f095ed7f3b", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 12:> (0 + 1) / 1]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----+-----+--------------------+------------------+------------+-------------+--------------------+--------------------+--------------+--------------------+--------------------+------------------------+-------------------+------------------+-------------+---------+------------+-----+----+----------+\n", "| id|title| body|accepted_answer_id|answer_count|comment_count|community_owned_date| creation_date|favorite_count| last_activity_date| last_edit_date|last_editor_display_name|last_editor_user_id|owner_display_name|owner_user_id|parent_id|post_type_id|score|tags|view_count|\n", "+----+-----+--------------------+------------------+------------+-------------+--------------------+--------------------+--------------+--------------------+--------------------+------------------------+-------------------+------------------+-------------+---------+------------+-----+----+----------+\n", "| 18| null|

For a table li...| null| null| 2| null|2008-08-01 05:12:...| null|2016-06-02 05:56:...|2016-06-02 05:56:...| Jeff Atwood| 126039| phpguy| null| 17| 2| 59|null| null|\n", "| 165| null|

You can use a ...| null| null| 0| null|2008-08-01 18:04:...| null|2019-04-06 14:03:...|2019-04-06 14:03:...| null| 1721793| user2189331| null| 145| 2| 10|null| null|\n", "|1028| null|

The VB code lo...| null| null| 0| null|2008-08-04 04:58:...| null|2013-02-07 13:22:...|2013-02-07 13:22:...| null| 395659| user2189331| null| 947| 2| 8|null| null|\n", "|1073| null|

My first choic...| null| null| 0| null|2008-08-04 07:51:...| null|2015-09-01 17:32:...|2015-09-01 17:32:...| null| 45459| user2189331| null| 1069| 2| 29|null| null|\n", "|1260| null|

I found the an...| null| null| 0| null|2008-08-04 14:06:...| null|2016-12-20 08:38:...|2016-12-20 08:38:...| null| 1221571| Jin| null| 1229| 2| 1|null| null|\n", "+----+-----+--------------------+------------------+------------+-------------+--------------------+--------------------+--------------+--------------------+--------------------+------------------------+-------------------+------------------+-------------+---------+------------+-----+----+----------+\n", "only showing top 5 rows\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "post_answer_df.show(5)" ] }, { "cell_type": "code", "execution_count": 16, "id": "69c6dc19-c25a-4070-80dd-c094a56a1b21", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 13:> (0 + 1) / 1]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+------+------------------+------------+-------------+--------------------+--------------------+--------------+--------------------+--------------------+------------------------+-------------------+------------------+-------------+---------+------------+-----+--------------------+----------+\n", "| id|accepted_answer_id|answer_count|comment_count|community_owned_date| creation_date|favorite_count| last_activity_date| last_edit_date|last_editor_display_name|last_editor_user_id|owner_display_name|owner_user_id|parent_id|post_type_id|score| tags|view_count|\n", "+------+------------------+------------+-------------+--------------------+--------------------+--------------+--------------------+--------------------+------------------------+-------------------+------------------+-------------+---------+------------+-----+--------------------+----------+\n", "|320268| null| 0| 0| null|2008-11-26 10:42:...| 0|2009-02-06 20:13:...| null| null| null| Paulo| null| null| 1| 0| asp.net-mvc| 390|\n", "|324003| null| 0| 0| null|2008-11-27 15:12:...| 0|2012-09-25 19:54:...|2012-09-25 19:54:...| Marcin| 1288| null| 41000| null| 1| 0|haskell|lambda|fu...| 497|\n", "|390605| 390608| 0| 0| null|2008-12-24 01:49:...| 2|2008-12-24 03:08:...| null| null| null| Unkwntech| 115| null| 1| 0|language-agnostic...| 11262|\n", "|413246| null| 0| 0| null|2009-01-05 14:23:...| 0|2009-03-24 21:30:...|2009-01-05 14:42:...| Tom Anderson| 13502| Velnias| null| null| 1| 0|asp.net|deploymen...| 4823|\n", "|454921| 454928| 0| 0| null|2009-01-18 10:22:...| 0|2009-01-18 10:30:...|2017-05-23 11:49:...| null| -1| Evan Fosmark| 49701| null| 1| 0| java|arrays| 798|\n", "+------+------------------+------------+-------------+--------------------+--------------------+--------------+--------------------+--------------------+------------------------+-------------------+------------------+-------------+---------+------------+-----+--------------------+----------+\n", "only showing top 5 rows\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "post_question_df.show(5)" ] }, { "cell_type": "code", "execution_count": 17, "id": "5beef3df-aabb-4d02-9fcf-48d2d95e3f45", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 14:> (0 + 1) / 1]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-------+-----+--------------------+------------------+------------+-------------+--------------------+--------------------+--------------+--------------------+--------------------+------------------------+-------------------+------------------+-------------+---------+------------+-----+----+----------+\n", "| id|title| body|accepted_answer_id|answer_count|comment_count|community_owned_date| creation_date|favorite_count| last_activity_date| last_edit_date|last_editor_display_name|last_editor_user_id|owner_display_name|owner_user_id|parent_id|post_type_id|score|tags|view_count|\n", "+-------+-----+--------------------+------------------+------------+-------------+--------------------+--------------------+--------------+--------------------+--------------------+------------------------+-------------------+------------------+-------------+---------+------------+-----+----+----------+\n", "|5046395| null|

The Web (short...| null| null| 0| null|2011-02-18 20:47:...| null|2011-06-23 21:43:...|2011-06-23 21:43:...| user213154| null| user213154| null| null| 5| 0|null| null|\n", "|5062415| null|

Celerity is a ...| null| null| 0| null|2011-02-21 04:49:...| null|2011-02-21 04:54:...|2011-02-21 04:54:...| user479911| null| user479911| null| null| 5| 0|null| null|\n", "|5130474| null|

Ramaze is a we...| null| null| 0| null|2011-02-26 23:38:...| null|2011-06-04 16:05:...|2011-06-04 16:05:...| user290102| null| user290102| null| null| 5| 0|null| null|\n", "|5166749| null|

OpenWrap is a ...| null| null| 0| null|2011-03-02 11:20:...| null|2011-09-07 19:07:...|2011-09-07 19:07:...| user1228| null| user1228| null| null| 5| 0|null| null|\n", "|5362329| null|

SmartGWT is ba...| null| null| 0| null|2011-03-19 13:21:...| null|2012-12-19 20:48:...|2012-12-19 20:48:...| user195488| null| user614868| null| null| 5| 0|null| null|\n", "+-------+-----+--------------------+------------------+------------+-------------+--------------------+--------------------+--------------+--------------------+--------------------+------------------------+-------------------+------------------+-------------+---------+------------+-----+----+----------+\n", "only showing top 5 rows\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "post_tag_wiki_df.show(5)" ] }, { "cell_type": "code", "execution_count": 18, "id": "3a49e126-3e2c-403c-a6c0-7938857def6d", "metadata": { "scrolled": true }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 15:> (0 + 1) / 1]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-------+-----+--------------------+------------------+------------+-------------+--------------------+--------------------+--------------+--------------------+--------------------+------------------------+-------------------+------------------+-------------+---------+------------+-----+----+----------+\n", "| id|title| body|accepted_answer_id|answer_count|comment_count|community_owned_date| creation_date|favorite_count| last_activity_date| last_edit_date|last_editor_display_name|last_editor_user_id|owner_display_name|owner_user_id|parent_id|post_type_id|score|tags|view_count|\n", "+-------+-----+--------------------+------------------+------------+-------------+--------------------+--------------------+--------------+--------------------+--------------------+------------------------+-------------------+------------------+-------------+---------+------------+-----+----+----------+\n", "|4742863| null|SlimDX is an MIT-...| null| 0| 0| null|2011-01-20 02:14:...| 0|2011-01-20 02:14:...|2011-01-20 02:14:...| user197015| 0| user197015| 0| null| 4| 0|null| 0|\n", "|5062416| null|Celerity is a JRu...| null| 0| 0| null|2011-02-21 04:49:...| 0|2011-02-21 04:54:...|2011-02-21 04:54:...| user479911| 0| user479911| 0| null| 4| 0|null| 0|\n", "|5130475| null|Ramaze is a simpl...| null| 0| 0| null|2011-02-26 23:38:...| 0|2011-02-27 00:08:...|2011-02-27 00:08:...| user290102| 0| user290102| 0| null| 4| 0|null| 0|\n", "|5304008| null|WebMachine is an ...| null| 0| 0| null|2011-03-14 20:20:...| 0|2011-03-14 20:44:...|2011-03-14 20:44:...| user177800| 0| user177800| 0| null| 4| 0|null| 0|\n", "|5354120| null|TCP/IP Transmissi...| null| 0| 0| null|2011-03-18 15:24:...| 0|2011-03-18 15:27:...|2011-03-18 15:27:...| user614868| 0| user614868| 0| null| 4| 0|null| 0|\n", "+-------+-----+--------------------+------------------+------------+-------------+--------------------+--------------------+--------------+--------------------+--------------------+------------------------+-------------------+------------------+-------------+---------+------------+-----+----+----------+\n", "only showing top 5 rows\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "post_tag_wiki_excerpt_df.show(5)" ] }, { "cell_type": "code", "execution_count": 19, "id": "49597e0a-8366-4c97-928f-1462526c0d2b", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "+----+-------------------+--------------------+----+--------------------+--------------------+--------------------+----------+--------+----------+-----+--------------------+--------------------+\n", "| id| display_name| about_me| age| creation_date| last_access_date| location|reputation|up_votes|down_votes|views| profile_image_url| website_url|\n", "+----+-------------------+--------------------+----+--------------------+--------------------+--------------------+----------+--------+----------+-----+--------------------+--------------------+\n", "| 366| littlecharva| null|null|2008-08-05 06:49:...|2022-08-08 13:12:...| null| 4214| 90| 1| 252| null|http://www.twitte...|\n", "|1110| user1110| null|null|2008-08-12 12:33:...|2009-07-08 20:03:...| null| 59| 0| 0| 49| null| null|\n", "|1666| SCdF|

...!

As an entrepre...|null|2008-08-18 12:45:...|2022-09-17 03:59:...|Kansas City, MO, USA| 2767| 170| 2| 614|https://i.stack.i...|https://drlongnec...|\n", "|2169|Michał Niedźwiedzki| null|null|2008-08-20 17:54:...|2021-03-01 11:02:...| Katowice, Poland| 12739| 99| 28| 1094|https://i.stack.i...| null|\n", "+----+-------------------+--------------------+----+--------------------+--------------------+--------------------+----------+--------+----------+-----+--------------------+--------------------+\n", "only showing top 5 rows\n", "\n" ] } ], "source": [ "user_df.show(5)" ] }, { "cell_type": "code", "execution_count": 20, "id": "c2d3341e-7712-4988-867a-e9ec0b67a1af", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 17:> (0 + 1) / 1]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+------+----------------+-----+---------------+------------+\n", "| id| tag_name|count|excerpt_post_id|wiki_post_id|\n", "+------+----------------+-----+---------------+------------+\n", "|149358| announcement| 0| 69362460| 69362459|\n", "| 2136| regsvr32| 256| 10051236| 10051235|\n", "| 8287| rpg| 256| 7080550| 7080549|\n", "| 9136| dbexpress| 256| 7663373| 7663372|\n", "| 9213|multiple-domains| 256| 22931444| 22931443|\n", "+------+----------------+-----+---------------+------------+\n", "only showing top 5 rows\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "tag_df.show(5)" ] }, { "cell_type": "code", "execution_count": 21, "id": "fc3d13bb-c4b9-471d-9791-8812c848fc6b", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 18:> (0 + 1) / 1]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+---------+--------------------+--------+------------+\n", "| id| creation_date| post_id|vote_type_id|\n", "+---------+--------------------+--------+------------+\n", "|245810971|2022-01-27 00:00:...|70882985| 11|\n", "| 57415841|2013-11-16 00:00:...|19872892| 15|\n", "| 57703886|2013-11-21 00:00:...|20114527| 15|\n", "|238741747|2021-09-17 00:00:...|19945516| 15|\n", "|239330448|2021-09-28 00:00:...|69355148| 15|\n", "+---------+--------------------+--------+------------+\n", "only showing top 5 rows\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "vote_df.show(5)" ] }, { "cell_type": "code", "execution_count": 22, "id": "496f011d-c54d-4d2d-9dcd-ee04b10c7d16", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 19:> (0 + 1) / 1]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----+--------------------+------------+-------+---------------+\n", "| id| creation_date|link_type_id|post_id|related_post_id|\n", "+----+--------------------+------------+-------+---------------+\n", "| 939|2010-04-26 03:00:...| 1| 85770| 83953|\n", "|1394|2010-04-26 03:00:...| 1| 108399| 55273|\n", "|1431|2010-04-26 03:00:...| 1| 110030| 44601|\n", "|1529|2010-04-26 03:00:...| 1| 114504| 43249|\n", "|1949|2010-04-26 03:00:...| 1| 134778| 1669772|\n", "+----+--------------------+------------+-------+---------------+\n", "only showing top 5 rows\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "post_links_df.show(5)" ] }, { "cell_type": "markdown", "id": "e1acafb2-6850-49ea-911e-41c592270138", "metadata": {}, "source": [ "# Vertices" ] }, { "cell_type": "code", "execution_count": 23, "id": "1eb49d28-ae41-436c-ac66-a6bfa8fc2965", "metadata": {}, "outputs": [], "source": [ "# 1) Users\n", "user_vertices = user_df.selectExpr(\n", " \"concat('user_', cast(id as string)) as id\",\n", " \"'User' as type\",\n", " \"display_name\",\n", " \"about_me\",\n", " \"age\",\n", " \"creation_date\",\n", " \"last_access_date\",\n", " \"location\",\n", " \"cast(reputation as long) as reputation\",\n", " \"cast(up_votes as long) as up_votes\",\n", " \"cast(down_votes as long) as down_votes\",\n", " \"cast(views as long) as views\",\n", " \"profile_image_url\",\n", " \"website_url\"\n", ").dropDuplicates([\"id\"])" ] }, { "cell_type": "code", "execution_count": 24, "id": "2ec3f6a2-2817-4ac2-a8f3-e4f19cccecc7", "metadata": {}, "outputs": [], "source": [ "# 2) Badges\n", "badge_vertices = badge_df.selectExpr(\n", " \"concat('badge_', cast(id as string)) as id\",\n", " \"'Badge' as type\",\n", " \"name\",\n", " \"date\",\n", " \"cast(user_id as long) as user_id\",\n", " \"class\",\n", " \"tag_based\"\n", ").dropDuplicates([\"id\"])" ] }, { "cell_type": "code", "execution_count": 25, "id": "902468fa-5bba-4b09-a3d5-79a977279143", "metadata": {}, "outputs": [], "source": [ "# 3) Comments\n", "comment_vertices = comment_df.selectExpr(\n", " \"concat('comment_', cast(id as string)) as id\",\n", " \"'Comment' as type\",\n", " \"text\",\n", " \"creation_date\",\n", " \"cast(post_id as long) as post_id\",\n", " \"cast(user_id as long) as user_id\",\n", " \"user_display_name\",\n", " \"cast(score as long) as score\"\n", ").dropDuplicates([\"id\"])" ] }, { "cell_type": "code", "execution_count": 26, "id": "c02cfe56-9cd3-4c8e-b892-c7afc64f8178", "metadata": {}, "outputs": [], "source": [ "# 4) Post_answers (answers)\n", "post_answer_vertices = post_answer_df.selectExpr(\n", " \"concat('answer_', cast(id as string)) as id\",\n", " \"'Post_answer' as type\",\n", " \"title\",\n", " \"body\",\n", " \"cast(accepted_answer_id as long) as accepted_answer_id\",\n", " \"cast(answer_count as long) as answer_count\",\n", " \"cast(comment_count as long) as comment_count\",\n", " \"community_owned_date\",\n", " \"creation_date\",\n", " \"cast(favorite_count as long) as favorite_count\",\n", " \"last_activity_date\",\n", " \"last_edit_date\",\n", " \"last_editor_display_name\",\n", " \"cast(last_editor_user_id as long) as last_editor_user_id\",\n", " \"owner_display_name\",\n", " \"cast(owner_user_id as long) as owner_user_id\",\n", " \"cast(parent_id as long) as parent_id\",\n", " \"cast(post_type_id as long) as post_type_id\",\n", " \"cast(score as long) as score\",\n", " \"tags\",\n", " \"cast(view_count as long) as view_count\"\n", ").dropDuplicates([\"id\"])" ] }, { "cell_type": "code", "execution_count": 27, "id": "7a3540f5-ce47-4a58-8421-dc38ea64a88b", "metadata": {}, "outputs": [], "source": [ "# 5) Post_questions\n", "post_question_vertices = post_question_df.selectExpr(\n", " \"concat('question_', cast(id as string)) as id\",\n", " \"'Post_question' as type\",\n", " \"cast(accepted_answer_id as long) as accepted_answer_id\",\n", " \"cast(answer_count as long) as answer_count\",\n", " \"cast(comment_count as long) as comment_count\",\n", " \"community_owned_date\",\n", " \"creation_date\",\n", " \"cast(favorite_count as long) as favorite_count\",\n", " \"last_activity_date\",\n", " \"last_edit_date\",\n", " \"last_editor_display_name\",\n", " \"cast(last_editor_user_id as long) as last_editor_user_id\",\n", " \"owner_display_name\",\n", " \"cast(owner_user_id as long) as owner_user_id\",\n", " \"cast(parent_id as long) as parent_id\",\n", " \"cast(post_type_id as long) as post_type_id\",\n", " \"cast(score as long) as score\",\n", " \"tags\",\n", " \"cast(view_count as long) as view_count\"\n", ").dropDuplicates([\"id\"])" ] }, { "cell_type": "code", "execution_count": 28, "id": "a3c53bbc-3420-4dbe-8bb8-91a2492bd003", "metadata": {}, "outputs": [], "source": [ "# 6) Tags\n", "tag_vertices = tag_df.selectExpr(\n", " \"concat('tag_', cast(id as string)) as id\",\n", " \"'Tag' as type\",\n", " \"tag_name\",\n", " \"cast(count as long) as count\",\n", " \"cast(excerpt_post_id as long) as excerpt_post_id\",\n", " \"cast(wiki_post_id as long) as wiki_post_id\"\n", ").dropDuplicates([\"id\"])" ] }, { "cell_type": "code", "execution_count": 29, "id": "46cacca7-92e9-4216-88b9-ff04d7004747", "metadata": {}, "outputs": [], "source": [ "# 7) Votes\n", "vote_vertices = vote_df.selectExpr(\n", " \"concat('vote_', cast(id as string)) as id\",\n", " \"'Vote' as type\",\n", " \"creation_date\",\n", " \"cast(post_id as long) as post_id\",\n", " \"cast(vote_type_id as long) as vote_type_id\"\n", ").dropDuplicates([\"id\"])" ] }, { "cell_type": "code", "execution_count": 30, "id": "b0ce4508-0d82-419b-825c-804ac0c521a2", "metadata": {}, "outputs": [], "source": [ "# 8) Post_tag_wiki\n", "post_tag_wiki_vertices = post_tag_wiki_df.selectExpr(\n", " \"concat('wiki_', cast(id as string)) as id\",\n", " \"'Post_tag_wiki' as type\",\n", " \"title\",\n", " \"body\",\n", " \"cast(comment_count as long) as comment_count\",\n", " \"creation_date\",\n", " \"last_activity_date\",\n", " \"last_edit_date\",\n", " \"last_editor_display_name\",\n", " \"cast(last_editor_user_id as long) as last_editor_user_id\",\n", " \"owner_display_name\",\n", " \"cast(owner_user_id as long) as owner_user_id\",\n", " \"cast(post_type_id as long) as post_type_id\",\n", " \"cast(score as long) as score\"\n", ").dropDuplicates([\"id\"])" ] }, { "cell_type": "code", "execution_count": 31, "id": "e59822be-03b9-460b-963d-aa5da1db2a95", "metadata": {}, "outputs": [], "source": [ "# 9) Post_tag_wiki_excerpt\n", "post_tag_wiki_excerpt_vertices = post_tag_wiki_excerpt_df.selectExpr(\n", " \"concat('wiki_excerpt_', cast(id as string)) as id\",\n", " \"'Post_tag_wiki_excerpt' as type\",\n", " \"title\",\n", " \"body\",\n", " \"cast(comment_count as long) as comment_count\",\n", " \"creation_date\",\n", " \"last_activity_date\",\n", " \"last_edit_date\",\n", " \"last_editor_display_name\",\n", " \"cast(last_editor_user_id as long) as last_editor_user_id\",\n", " \"owner_display_name\",\n", " \"cast(owner_user_id as long) as owner_user_id\",\n", " \"cast(post_type_id as long) as post_type_id\",\n", " \"cast(score as long) as score\"\n", ").dropDuplicates([\"id\"])" ] }, { "cell_type": "code", "execution_count": 32, "id": "07a397ef-8d3e-4b5f-87ad-ccd9a778f2ab", "metadata": {}, "outputs": [], "source": [ "from functools import reduce\n", "from pyspark.sql import DataFrame\n", "\n", "vertex_frames = [\n", " user_vertices,\n", " badge_vertices,\n", " comment_vertices,\n", " post_question_vertices,\n", " post_answer_vertices,\n", " tag_vertices,\n", " vote_vertices,\n", " post_tag_wiki_vertices,\n", " post_tag_wiki_excerpt_vertices\n", "]" ] }, { "cell_type": "code", "execution_count": 33, "id": "4f86e9b3-6b2b-4bd8-bae1-031060f6fac0", "metadata": {}, "outputs": [], "source": [ "vertices = reduce(\n", " lambda a, b: a.unionByName(b, allowMissingColumns=True),\n", " vertex_frames\n", ").dropDuplicates([\"id\"]).filter(\"id IS NOT NULL\")" ] }, { "cell_type": "code", "execution_count": 34, "id": "f04587cf-b9bd-4ad0-8af8-03aa2cd63ca6", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Vertex counts by type:\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "[Stage 49:=====================================================>(199 + 1) / 200]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+---------------------+---------+\n", "|type |count |\n", "+---------------------+---------+\n", "|Post_tag_wiki |55113 |\n", "|Comment |86754111 |\n", "|Vote |236452885|\n", "|Post_tag_wiki_excerpt|55115 |\n", "|User |18712212 |\n", "|Post_answer |34024119 |\n", "|Badge |46135386 |\n", "|Tag |63653 |\n", "|Post_question |23020127 |\n", "+---------------------+---------+\n", "\n", "CPU times: user 67.3 ms, sys: 16.1 ms, total: 83.4 ms\n", "Wall time: 28.3 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "# Quick sanity checks\n", "print(\"Vertex counts by type:\")\n", "vertices.groupBy(\"type\").count().show(truncate=False)" ] }, { "cell_type": "markdown", "id": "a9c6f112-ee48-4421-a76e-bab109dd64ca", "metadata": {}, "source": [ "# edges" ] }, { "cell_type": "code", "execution_count": 35, "id": "d3858012-87c8-4606-adf9-a812fd030c5a", "metadata": {}, "outputs": [], "source": [ "from pyspark.sql.functions import col, lit, explode, split, regexp_replace, trim, lower, concat" ] }, { "cell_type": "code", "execution_count": 36, "id": "6c1f1bfc-8fb9-486c-b36a-626110b0aec0", "metadata": {}, "outputs": [], "source": [ "# Create Edge - User created Question\n", "e_question_created_by_user = post_question_df.selectExpr(\n", " \"concat('question_', cast(id as string)) as src\",\n", " \"concat('user_', cast(owner_user_id as string)) as dst\",\n", " \"'Owned_by' as etype\"\n", ").filter(\"owner_user_id IS NOT NULL\").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 37, "id": "018aabaf-c5a5-4153-8df7-6123417186a6", "metadata": {}, "outputs": [], "source": [ "# Create Edge - User last edited Question\n", "e_question_last_edited_by_user = post_question_df.selectExpr(\n", " \"concat('question_', cast(id as string)) as src\",\n", " \"concat('user_', cast(last_editor_user_id as string)) as dst\",\n", " \"'Last_edited_by' as etype\"\n", ").filter(\"last_editor_user_id IS NOT NULL\").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 38, "id": "857ef65d-b3c9-43ee-be79-05597f4240b6", "metadata": {}, "outputs": [], "source": [ "# Create Edge - User created Answer\n", "e_answer_created_by_user = post_answer_df.selectExpr(\n", " \"concat('answer_', cast(id as string)) as src\",\n", " \"concat('user_', cast(owner_user_id as string)) as dst\",\n", " \"'Answers_by' as etype\"\n", ").filter(\"owner_user_id IS NOT NULL\").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 39, "id": "4f92fbf4-e835-4dfb-940c-3c94dd5c8cc5", "metadata": {}, "outputs": [], "source": [ "# Create Edge - User last edited Answer\n", "e_answer_last_edited_by_user = post_answer_df.selectExpr(\n", " \"concat('answer_', cast(id as string)) as src\",\n", " \"concat('user_', cast(last_editor_user_id as string)) as dst\",\n", " \"'Last_edited_by' as etype\"\n", ").filter(\"last_editor_user_id IS NOT NULL\").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 40, "id": "351ef53a-4b89-4ba1-a140-4788085ea9e5", "metadata": {}, "outputs": [], "source": [ "# Create Edge - User created Tag Wiki\n", "e_wiki_created_by_user = post_tag_wiki_df.selectExpr(\n", " \"concat('wiki_', cast(id as string)) as src\",\n", " \"concat('user_', cast(owner_user_id as string)) as dst\",\n", " \"'Created_by' as etype\"\n", ").filter(\"owner_user_id IS NOT NULL\").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 41, "id": "a74143df-3675-43ac-8e3e-229e735b6ce2", "metadata": {}, "outputs": [], "source": [ "# Create Edge - User last edited Tag Wiki\n", "e_wiki_last_edited_by_user = post_tag_wiki_df.selectExpr(\n", " \"concat('wiki_', cast(id as string)) as src\",\n", " \"concat('user_', cast(last_editor_user_id as string)) as dst\",\n", " \"'Last_edited_by' as etype\"\n", ").filter(\"last_editor_user_id IS NOT NULL\").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 42, "id": "d98a2425-fbd3-4f9f-b0f1-33d55ca22647", "metadata": {}, "outputs": [], "source": [ "# Create Edge - User created Tag Wiki Excerpt\n", "e_wiki_excerpt_created_by_user = post_tag_wiki_excerpt_df.selectExpr(\n", " \"concat('wiki_excerpt_', cast(id as string)) as src\",\n", " \"concat('user_', cast(owner_user_id as string)) as dst\",\n", " \"'Created_by' as etype\"\n", ").filter(\"owner_user_id IS NOT NULL\").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 43, "id": "e4759b07-4979-4263-b762-e1fe56befa6b", "metadata": {}, "outputs": [], "source": [ "# Create Edge - User last edited Tag Wiki Excerpt\n", "e_wiki_excerpt_last_edited_by_user = post_tag_wiki_excerpt_df.selectExpr(\n", " \"concat('wiki_excerpt_', cast(id as string)) as src\",\n", " \"concat('user_', cast(last_editor_user_id as string)) as dst\",\n", " \"'Last_edited_by' as etype\"\n", ").filter(\"last_editor_user_id IS NOT NULL\").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 44, "id": "c047b728-0358-44e5-93ac-bd115af8416b", "metadata": {}, "outputs": [], "source": [ "# Create Edge - User earns Badge\n", "e_user_earns_badge = badge_df.selectExpr(\n", " \"concat('user_', cast(user_id as string)) as src\",\n", " \"concat('badge_', cast(id as string)) as dst\",\n", " \"'Earns' as etype\"\n", ").filter(\"user_id IS NOT NULL\").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 45, "id": "ed477ae7-b7dc-449b-a927-da790a94c8f1", "metadata": {}, "outputs": [], "source": [ "# Create Edge - Answer to Question\n", "e_answer_to_question = post_answer_df.selectExpr(\n", " \"concat('answer_', cast(id as string)) as src\",\n", " \"concat('question_', cast(parent_id as string)) as dst\",\n", " \"'Answers_to' as etype\"\n", ").filter(\"parent_id IS NOT NULL\").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 46, "id": "54b32f76-f887-440c-81b9-b81e12a65998", "metadata": {}, "outputs": [], "source": [ "# Create Edge - Question accepts Answer\n", "e_question_accepts_answer = post_question_df.filter(\n", " \"accepted_answer_id IS NOT NULL\"\n", ").selectExpr(\n", " \"concat('question_', cast(id as string)) as src\",\n", " \"concat('answer_', cast(accepted_answer_id as string)) as dst\",\n", " \"'Accepts' as etype\"\n", ").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 47, "id": "4c6eb1a2-3ae4-481a-b70a-f04a48fb1877", "metadata": {}, "outputs": [], "source": [ "# Create Edge - User commented\n", "e_comment_by_user = comment_df.selectExpr(\n", " \"concat('comment_', cast(id as string)) as src\",\n", " \"concat('user_', cast(user_id as string)) as dst\"\n", ").filter(\"user_id IS NOT NULL AND user_id != '' AND user_id != '0'\") \\\n", " .dropDuplicates(['src', 'dst']) \\\n", " .withColumn(\"etype\", lit(\"Commented_by\"))" ] }, { "cell_type": "code", "execution_count": 48, "id": "7de1b202-8e67-4c1a-8696-678889ce3126", "metadata": {}, "outputs": [], "source": [ "# Create Edge - Comment on Question\n", "c = comment_df.alias(\"c\")\n", "q = post_question_df.alias(\"q\")\n", "\n", "e_comment_on_question = c.join(\n", " q,\n", " col(\"c.post_id\") == col(\"q.id\"),\n", " \"inner\"\n", ").select(\n", " concat(lit(\"comment_\"), col(\"c.id\").cast(\"string\")).alias(\"src\"),\n", " concat(lit(\"question_\"), col(\"q.id\").cast(\"string\")).alias(\"dst\"),\n", " lit(\"On_question\").alias(\"etype\")\n", ").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 49, "id": "490bc30d-6f7e-4e26-90a2-368e5ae022e6", "metadata": {}, "outputs": [], "source": [ "# Create Edge - Comment on Answer\n", "c = comment_df.alias(\"c\")\n", "a = post_answer_df.alias(\"a\")\n", "\n", "e_comment_on_answer = c.join(\n", " a,\n", " col(\"c.post_id\") == col(\"a.id\"),\n", " \"inner\"\n", ").select(\n", " concat(lit(\"comment_\"), col(\"c.id\").cast(\"string\")).alias(\"src\"),\n", " concat(lit(\"answer_\"), col(\"a.id\").cast(\"string\")).alias(\"dst\"),\n", " lit(\"On_answer\").alias(\"etype\")\n", ").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 50, "id": "15723468-2204-4da0-a9f0-150c4a7dd91d", "metadata": {}, "outputs": [], "source": [ "# Create Edge - Vote on Question\n", "e_vote_on_question = vote_df.alias(\"v\") \\\n", " .join(post_question_df.alias(\"q\"), col(\"v.post_id\") == col(\"q.id\"), \"inner\") \\\n", " .select(\n", " concat(lit(\"vote_\"), col(\"v.id\").cast(\"string\")).alias(\"src\"),\n", " concat(lit(\"question_\"), col(\"q.id\").cast(\"string\")).alias(\"dst\"),\n", " lit(\"Vote_on\").alias(\"etype\")\n", " ).dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 51, "id": "857db470-3080-470b-a9a3-17c688c64612", "metadata": {}, "outputs": [], "source": [ "# Create Edge - Vote on Answer\n", "e_vote_on_answer = vote_df.alias(\"v\") \\\n", " .join(post_answer_df.alias(\"a\"), col(\"v.post_id\") == col(\"a.id\"), \"inner\") \\\n", " .select(\n", " concat(lit(\"vote_\"), col(\"v.id\").cast(\"string\")).alias(\"src\"),\n", " concat(lit(\"answer_\"), col(\"a.id\").cast(\"string\")).alias(\"dst\"),\n", " lit(\"Vote_on\").alias(\"etype\")\n", " ).dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 52, "id": "40a6c06e-e585-4dc8-ba61-2ac29e3a551f", "metadata": {}, "outputs": [], "source": [ "# Create Edge - Vote on Comment\n", "e_vote_on_comment = vote_df.alias(\"v\") \\\n", " .join(comment_df.alias(\"c\"), col(\"v.post_id\") == col(\"c.id\"), \"inner\") \\\n", " .select(\n", " concat(lit(\"vote_\"), col(\"v.id\").cast(\"string\")).alias(\"src\"),\n", " concat(lit(\"comment_\"), col(\"c.id\").cast(\"string\")).alias(\"dst\"),\n", " lit(\"Vote_on\").alias(\"etype\")\n", " ).dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 53, "id": "0d991380-01d2-4e61-8901-4510a9d9a3b6", "metadata": {}, "outputs": [], "source": [ "# Create Edge - Tag to Question (CORRECTED)\n", "question_tags = post_question_df \\\n", " .filter(col(\"tags\").isNotNull() & (col(\"tags\") != \"\")) \\\n", " .withColumn(\"tags_cleaned\", regexp_replace(col(\"tags\"), \"><\", \"|\")) \\\n", " .withColumn(\"tags_cleaned\", regexp_replace(col(\"tags_cleaned\"), \"[<>]\", \"\")) \\\n", " .withColumn(\"tag\", explode(split(col(\"tags_cleaned\"), \"\\\\|\"))) \\\n", " .withColumn(\"tag\", trim(lower(col(\"tag\")))) \\\n", " .filter(col(\"tag\") != \"\") \\\n", " .select(\n", " concat(lit(\"question_\"), col(\"id\").cast(\"string\")).alias(\"dst\"), \n", " col(\"tag\").alias(\"tag_name\")\n", " )\n", "\n", "tag_map = tag_df.select(\n", " concat(lit(\"tag_\"), col(\"id\").cast(\"string\")).alias(\"tag_id\"), \n", " lower(col(\"tag_name\")).alias(\"tag_name\")\n", ").distinct()\n", "\n", "e_tag_to_question = question_tags.join(tag_map, on=\"tag_name\", how=\"inner\") \\\n", " .select(\n", " col(\"tag_id\").alias(\"src\"), \n", " col(\"dst\"), \n", " lit(\"Tag_to\").alias(\"etype\")\n", " ).dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 54, "id": "020481c3-847c-470d-b4e0-70998d2a0acd", "metadata": {}, "outputs": [], "source": [ "# Create Edge - Tag has Wiki\n", "e_tag_has_wiki = tag_df.filter(\n", " \"wiki_post_id IS NOT NULL\"\n", ").selectExpr(\n", " \"concat('tag_', cast(id as string)) as src\",\n", " \"concat('wiki_', cast(wiki_post_id as string)) as dst\",\n", " \"'Has_wiki' as etype\"\n", ").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 55, "id": "7ff8dd66-0842-4965-acd1-8bf823b35651", "metadata": {}, "outputs": [], "source": [ "# Create Edge - Tag has Wiki Excerpt\n", "e_tag_has_wiki_excerpt = tag_df.filter(\n", " \"excerpt_post_id IS NOT NULL\"\n", ").selectExpr(\n", " \"concat('tag_', cast(id as string)) as src\",\n", " \"concat('wiki_excerpt_', cast(excerpt_post_id as string)) as dst\",\n", " \"'Has_wiki_excerpt' as etype\"\n", ").dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 58, "id": "715b4f78-ca5a-4d98-be52-bedcf8014f01", "metadata": {}, "outputs": [], "source": [ "\n", "# Create edges - Post_links to Question\n", "# Use post_question_df directly instead of g.vertices (avoids circular dependency)\n", "\n", "valid_question_ids = post_question_df.select(\n", " col(\"id\").cast(\"string\").alias(\"numeric_id\")\n", ").distinct()\n", "\n", "e_question_link_to_question = post_links_df \\\n", " .filter(\n", " \"post_id IS NOT NULL AND related_post_id IS NOT NULL \"\n", " \"AND post_id != '' AND related_post_id != '' \"\n", " \"AND post_id != '0' AND related_post_id != '0'\"\n", " ) \\\n", " .join(valid_question_ids.select(col(\"numeric_id\").alias(\"src_check\")),\n", " post_links_df.post_id == col(\"src_check\"), \"inner\") \\\n", " .join(valid_question_ids.select(col(\"numeric_id\").alias(\"dst_check\")),\n", " post_links_df.related_post_id == col(\"dst_check\"), \"inner\") \\\n", " .selectExpr(\n", " \"concat('question_', cast(post_id as string)) as src\",\n", " \"concat('question_', cast(related_post_id as string)) as dst\",\n", " \"'Link_to' as etype\"\n", " ).dropDuplicates()" ] }, { "cell_type": "code", "execution_count": 59, "id": "be307ade-f0fb-46a4-a90b-236a3970953f", "metadata": {}, "outputs": [], "source": [ "# Union all edges\n", "edges = e_question_created_by_user \\\n", " .unionByName(e_question_last_edited_by_user, allowMissingColumns=True) \\\n", " .unionByName(e_answer_created_by_user, allowMissingColumns=True) \\\n", " .unionByName(e_answer_last_edited_by_user, allowMissingColumns=True) \\\n", " .unionByName(e_wiki_created_by_user, allowMissingColumns=True) \\\n", " .unionByName(e_wiki_last_edited_by_user, allowMissingColumns=True) \\\n", " .unionByName(e_wiki_excerpt_created_by_user, allowMissingColumns=True) \\\n", " .unionByName(e_wiki_excerpt_last_edited_by_user, allowMissingColumns=True) \\\n", " .unionByName(e_user_earns_badge, allowMissingColumns=True) \\\n", " .unionByName(e_answer_to_question, allowMissingColumns=True) \\\n", " .unionByName(e_question_accepts_answer, allowMissingColumns=True) \\\n", " .unionByName(e_comment_by_user, allowMissingColumns=True) \\\n", " .unionByName(e_comment_on_question, allowMissingColumns=True) \\\n", " .unionByName(e_comment_on_answer, allowMissingColumns=True) \\\n", " .unionByName(e_vote_on_question, allowMissingColumns=True) \\\n", " .unionByName(e_vote_on_answer, allowMissingColumns=True) \\\n", " .unionByName(e_vote_on_comment, allowMissingColumns=True) \\\n", " .unionByName(e_tag_to_question, allowMissingColumns=True) \\\n", " .unionByName(e_tag_has_wiki, allowMissingColumns=True) \\\n", " .unionByName(e_tag_has_wiki_excerpt, allowMissingColumns=True) \\\n", " .unionByName(e_question_link_to_question, allowMissingColumns=True)" ] }, { "cell_type": "code", "execution_count": 60, "id": "549bb8f7-5b1f-42d1-ac96-c4a0ce404d8d", "metadata": {}, "outputs": [], "source": [ "# Create GraphFrames\n", "from graphframes import GraphFrames\n", "g = GraphFrames(vertices, edges)" ] }, { "cell_type": "code", "execution_count": 61, "id": "b2ef2f70-3545-4e72-bd72-4502e17ef303", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 133:====================================================>(289 + 2) / 291]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------------+---------+\n", "|etype |count |\n", "+----------------+---------+\n", "|Vote_on |365545435|\n", "|Commented_by |85362434 |\n", "|Tag_to |68338336 |\n", "|Earns |46135386 |\n", "|On_question |45700093 |\n", "|On_answer |41046017 |\n", "|Answers_to |34024119 |\n", "|Answers_by |33661703 |\n", "|Last_edited_by |22881481 |\n", "|Owned_by |22562265 |\n", "|Accepts |11755280 |\n", "|Link_to |5508987 |\n", "|Created_by |109836 |\n", "|Has_wiki_excerpt|44966 |\n", "|Has_wiki |44966 |\n", "+----------------+---------+\n", "\n", "CPU times: user 127 ms, sys: 38.8 ms, total: 166 ms\n", "Wall time: 53.7 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "# Verify edge counts\n", "edges.groupBy(\"etype\").count().orderBy(\"count\", ascending=False).show(100, truncate=False)" ] }, { "cell_type": "code", "execution_count": 62, "id": "68c9a35e-35bd-42ec-b799-938036b2b964", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "=== GRAPH STRUCTURE ===\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " ]]]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Comment --[Commented_by]--> User\n", "Comment --[On_answer]--> Post_answer\n", "Comment --[On_question]--> Post_question\n", "Post_answer --[Answers_by]--> User\n", "Post_answer --[Answers_to]--> Post_question\n", "Post_answer --[Last_edited_by]--> User\n", "Post_question --[Accepts]--> Post_answer\n", "Post_question --[Last_edited_by]--> User\n", "Post_question --[Link_to]--> Post_question\n", "Post_question --[Owned_by]--> User\n", "Post_tag_wiki --[Created_by]--> User\n", "Post_tag_wiki --[Last_edited_by]--> User\n", "Post_tag_wiki_excerpt --[Created_by]--> User\n", "Post_tag_wiki_excerpt --[Last_edited_by]--> User\n", "Tag --[Has_wiki]--> Post_tag_wiki\n", "Tag --[Has_wiki_excerpt]--> Post_tag_wiki_excerpt\n", "Tag --[Tag_to]--> Post_question\n", "User --[Earns]--> Badge\n", "Vote --[Vote_on]--> Post_question\n", "Vote --[Vote_on]--> Post_answer\n", "Vote --[Vote_on]--> Comment\n" ] } ], "source": [ "# Cell 45: Text-based graph schema\n", "print(\"\\n=== GRAPH STRUCTURE ===\\n\")\n", "\n", "edges_with_types = g.edges.alias(\"e\") \\\n", " .join(g.vertices.alias(\"v_src\"), col(\"e.src\") == col(\"v_src.id\")) \\\n", " .join(g.vertices.alias(\"v_dst\"), col(\"e.dst\") == col(\"v_dst.id\")) \\\n", " .select(\n", " col(\"v_src.type\").alias(\"src_type\"),\n", " col(\"e.etype\"),\n", " col(\"v_dst.type\").alias(\"dst_type\")\n", " ) \\\n", " .distinct() \\\n", " .orderBy(\"src_type\", \"etype\")\n", "\n", "for row in edges_with_types.collect():\n", " print(f\"{row['src_type']} --[{row['etype']}]--> {row['dst_type']}\")" ] }, { "cell_type": "markdown", "id": "637a9d8d-2e97-443a-b491-ff37e013b3e9", "metadata": {}, "source": [ "# Queries" ] }, { "cell_type": "code", "execution_count": 65, "id": "552c98dd-7136-4087-86fc-c6db1ac5b252", "metadata": {}, "outputs": [], "source": [ "from pyspark.sql.functions import (\n", " col, count, countDistinct, datediff, desc, asc,\n", " min as sql_min, sum as sql_sum, when, year,\n", " regexp_replace, lit\n", ")" ] }, { "cell_type": "markdown", "id": "1489c060-7c2c-4bdb-92bd-0cf4678cb6d9", "metadata": {}, "source": [ "# 1. User Pairs with Most Question-Answer Interactions" ] }, { "cell_type": "code", "execution_count": 66, "id": "2a4969d4-5bd1-4da9-986f-88d89651bd64", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "26/02/21 16:17:06 WARN DAGScheduler: Broadcasting large task binary with size 1104.3 KiB\n", "26/02/21 16:17:12 WARN DAGScheduler: Broadcasting large task binary with size 1391.6 KiB\n", "26/02/21 16:17:14 WARN DAGScheduler: Broadcasting large task binary with size 1385.0 KiB\n", "[Stage 677:====================================================>(199 + 1) / 200]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-------------+----------------+-----------------+\n", "|asker_user_id|answerer_user_id|interaction_count|\n", "+-------------+----------------+-----------------+\n", "|1277576 |756809 |241 |\n", "|742402 |315935 |163 |\n", "|8410477 |2901002 |140 |\n", "|7223434 |3732271 |138 |\n", "|2258442 |217408 |111 |\n", "|6447399 |3732271 |110 |\n", "|10829044 |2901002 |103 |\n", "|4930974 |756809 |102 |\n", "|9189799 |4549554 |99 |\n", "|13631281 |3732271 |96 |\n", "|1024586 |3732271 |95 |\n", "|5619171 |3732271 |93 |\n", "|46207 |505088 |87 |\n", "|8901845 |2901002 |86 |\n", "|7585973 |2901002 |85 |\n", "|4934150 |1144035 |84 |\n", "|399145 |6309 |84 |\n", "|1391249 |157882 |83 |\n", "|6803114 |2901002 |81 |\n", "|470184 |6309 |78 |\n", "+-------------+----------------+-----------------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 278 ms, sys: 84.1 ms, total: 362 ms\n", "Wall time: 2min 30s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "# (question)-[Owned_by]->(asker) AND (answer)-[Answers_to]->(question) AND (answer)-[Answers_by]->(answerer)\n", "motif_q1 = g.find(\"(q)-[e1]->(asker); (a)-[e2]->(q); (a)-[e3]->(answerer)\") \\\n", " .filter(\"e1.etype = 'Owned_by'\") \\\n", " .filter(\"e2.etype = 'Answers_to'\") \\\n", " .filter(\"e3.etype = 'Answers_by'\") \\\n", " .filter(\"q.type = 'Post_question'\") \\\n", " .filter(\"a.type = 'Post_answer'\") \\\n", " .filter(\"asker.type = 'User'\") \\\n", " .filter(\"answerer.type = 'User'\") \\\n", " .filter(\"asker.id != answerer.id\") \\\n", " .groupBy(\n", " regexp_replace(col(\"asker.id\"), \"user_\", \"\").alias(\"asker_user_id\"),\n", " regexp_replace(col(\"answerer.id\"), \"user_\", \"\").alias(\"answerer_user_id\")\n", " ) \\\n", " .agg(count(\"*\").alias(\"interaction_count\")) \\\n", " .filter(\"interaction_count >= 5\") \\\n", " .orderBy(desc(\"interaction_count\"))\n", "\n", "motif_q1.show(20, truncate=False)" ] }, { "cell_type": "markdown", "id": "7be952c1-95ec-4613-b710-3a42d34100f2", "metadata": {}, "source": [ "# 2. Tags with Highest Question-to-Answer Ratio" ] }, { "cell_type": "code", "execution_count": 67, "id": "1ed9a0a7-1b4b-4873-b4fc-e24141fe97f9", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "26/02/21 16:20:16 WARN DAGScheduler: Broadcasting large task binary with size 1381.6 KiB\n", "26/02/21 16:20:20 WARN DAGScheduler: Broadcasting large task binary with size 1381.2 KiB\n", "26/02/21 16:20:23 WARN DAGScheduler: Broadcasting large task binary with size 1385.0 KiB\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------+-----------------------------+--------------+------------+-----+\n", "|tag_id |tag_name |question_count|answer_count|ratio|\n", "+----------+-----------------------------+--------------+------------+-----+\n", "|tag_156009|autosummary |15 |1 |15.0 |\n", "|tag_149507|react-native-background-fetch|11 |1 |11.0 |\n", "|tag_154965|exoplayer-media-item |11 |1 |11.0 |\n", "|tag_151856|cgan |11 |1 |11.0 |\n", "|tag_155460|gnn |11 |1 |11.0 |\n", "|tag_147360|camera-intrinsics |9 |1 |9.0 |\n", "|tag_140788|oneplus7 |9 |1 |9.0 |\n", "|tag_115632|facebook-call-to-action |9 |1 |9.0 |\n", "|tag_153416|moveable |9 |1 |9.0 |\n", "|tag_124328|smack-stack |8 |1 |8.0 |\n", "|tag_153380|tilesets |15 |2 |7.5 |\n", "|tag_156012|nintendo-3ds |7 |1 |7.0 |\n", "|tag_154164|aws.s3 |7 |1 |7.0 |\n", "|tag_155634|windows-subsystem-for-android|7 |1 |7.0 |\n", "|tag_146122|d2l |7 |1 |7.0 |\n", "|tag_156111|react-mapbox-gl |7 |1 |7.0 |\n", "|tag_157023|shopify-hydrogen |7 |1 |7.0 |\n", "|tag_150447|pickup |7 |1 |7.0 |\n", "|tag_156845|slider-revolution |7 |1 |7.0 |\n", "|tag_152157|aiokafka |7 |1 |7.0 |\n", "+----------+-----------------------------+--------------+------------+-----+\n", "only showing top 20 rows\n", "\n", "CPU times: user 291 ms, sys: 84.3 ms, total: 376 ms\n", "Wall time: 2min 28s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "# Step 1: tag -> question (Tag_to)\n", "tag_question_motif = g.find(\"(t)-[e1]->(q)\") \\\n", " .filter(\"e1.etype = 'Tag_to'\") \\\n", " .filter(\"t.type = 'Tag'\") \\\n", " .filter(\"q.type = 'Post_question'\") \\\n", " .select(\n", " col(\"t.id\").alias(\"tag_id\"),\n", " col(\"t.tag_name\"),\n", " col(\"q.id\").alias(\"question_id\")\n", " )\n", "\n", "# Step 2: answer -> question (Answers_to)\n", "answer_question_motif = g.find(\"(a)-[e2]->(q)\") \\\n", " .filter(\"e2.etype = 'Answers_to'\") \\\n", " .filter(\"a.type = 'Post_answer'\") \\\n", " .filter(\"q.type = 'Post_question'\") \\\n", " .select(\n", " col(\"q.id\").alias(\"question_id\"),\n", " col(\"a.id\").alias(\"answer_id\")\n", " )\n", "\n", "tag_stats = tag_question_motif.alias(\"tq\") \\\n", " .join(answer_question_motif.alias(\"aq\"),\n", " col(\"tq.question_id\") == col(\"aq.question_id\"), \"left\") \\\n", " .groupBy(\"tq.tag_id\", \"tq.tag_name\") \\\n", " .agg(\n", " countDistinct(\"tq.question_id\").alias(\"question_count\"),\n", " countDistinct(\"aq.answer_id\").alias(\"answer_count\")\n", " ) \\\n", " .filter(\"answer_count > 0\") \\\n", " .withColumn(\"ratio\", col(\"question_count\") / col(\"answer_count\")) \\\n", " .orderBy(desc(\"ratio\"))\n", "\n", "tag_stats.show(20, truncate=False)" ] }, { "cell_type": "markdown", "id": "bcc6d38d-c3ec-4fd8-8791-2d3ce63be7a6", "metadata": {}, "source": [ "# 3. Questions with Most Views But No Accepted Answer " ] }, { "cell_type": "code", "execution_count": 68, "id": "79797a42-7c07-4653-8ccd-ad974b766780", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 826:====================================================>(197 + 3) / 200]0]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-----------+----------+------------+\n", "|question_id|view_count|answer_count|\n", "+-----------+----------+------------+\n", "|3207219 |7123135 |21 |\n", "|332289 |5186388 |25 |\n", "|82831 |4940426 |40 |\n", "|3010840 |4910699 |45 |\n", "|363681 |4849559 |71 |\n", "|455612 |4737341 |33 |\n", "|3277503 |4506666 |28 |\n", "|4181861 |3679263 |126 |\n", "|510348 |3549819 |13 |\n", "|402504 |3545040 |21 |\n", "|768431 |3464503 |33 |\n", "|1026069 |3350397 |122 |\n", "|5618878 |3348086 |3 |\n", "|4849652 |3266951 |35 |\n", "|452859 |3114457 |4 |\n", "|184618 |3113666 |518 |\n", "|470617 |2887822 |40 |\n", "|1841565 |2884572 |21 |\n", "|388242 |2879101 |1 |\n", "|17271319 |2751756 |21 |\n", "+-----------+----------+------------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 43.8 ms, sys: 16 ms, total: 59.8 ms\n", "Wall time: 23.7 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "result_q3 = g.vertices \\\n", " .filter(\"type = 'Post_question'\") \\\n", " .filter(\"accepted_answer_id = 0 OR accepted_answer_id IS NULL\") \\\n", " .filter(\"view_count IS NOT NULL\") \\\n", " .select(\n", " regexp_replace(col(\"id\"), \"question_\", \"\").alias(\"question_id\"),\n", " col(\"view_count\"),\n", " col(\"answer_count\")\n", " ) \\\n", " .orderBy(desc(\"view_count\")) \\\n", " .limit(200)\n", "\n", "result_q3.show(20, truncate=False)" ] }, { "cell_type": "markdown", "id": "355c0328-90e8-4f3f-a12d-de5be2635112", "metadata": {}, "source": [ "# 4. Users Who Earn Badges Fastest After Account Creation" ] }, { "cell_type": "code", "execution_count": 69, "id": "a173a263-2b50-48b8-8957-789862985170", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 893:===================================================> (195 + 5) / 200]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+--------+---------------+-----------------------+\n", "|id |display_name |min_time_to_first_badge|\n", "+--------+---------------+-----------------------+\n", "|10000000|Max Vasiuta |0 |\n", "|10000003|Corlet Dragos |0 |\n", "|10000005|Ahmad |0 |\n", "|10000010|Xid from Mış |0 |\n", "|10000015|AlexisVey |0 |\n", "|10000018|neha |0 |\n", "|10000019|projectworlds |0 |\n", "|10000028|Sunil Parihar |0 |\n", "|10000035|K.Nagare |0 |\n", "|10000036|Alicja D |0 |\n", "|10000038|Robyn Cullen |0 |\n", "|10000039|transcend |0 |\n", "|10000042|RomanK |0 |\n", "|10000045|Soniya |0 |\n", "|10000046|JMR |0 |\n", "|10000047|Gleb Moskalenko|0 |\n", "|10000053|Michael |0 |\n", "|10000059|Marc |0 |\n", "|10000078|Nilesh |0 |\n", "|10000079|Carlos Duarte |0 |\n", "+--------+---------------+-----------------------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 161 ms, sys: 36.1 ms, total: 197 ms\n", "Wall time: 1min 34s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "motif_q4 = g.find(\"(u)-[e]->(b)\") \\\n", " .filter(\"e.etype = 'Earns'\") \\\n", " .filter(\"u.type = 'User'\") \\\n", " .filter(\"b.type = 'Badge'\") \\\n", " .select(\n", " col(\"u.id\").alias(\"user_id\"),\n", " col(\"u.display_name\"),\n", " col(\"u.creation_date\").alias(\"user_creation_date\"),\n", " col(\"b.date\").alias(\"badge_date\")\n", " ) \\\n", " .withColumn(\"days_to_badge\", datediff(col(\"badge_date\"), col(\"user_creation_date\"))) \\\n", " .groupBy(\"user_id\", \"display_name\") \\\n", " .agg(sql_min(\"days_to_badge\").alias(\"min_time_to_first_badge\")) \\\n", " .filter(\"min_time_to_first_badge >= 0\") \\\n", " .select(\n", " regexp_replace(col(\"user_id\"), \"user_\", \"\").alias(\"id\"),\n", " col(\"display_name\"),\n", " col(\"min_time_to_first_badge\")\n", " ) \\\n", " .orderBy(asc(\"min_time_to_first_badge\"), asc(\"id\"))\n", "\n", "motif_q4.show(20, truncate=False)" ] }, { "cell_type": "markdown", "id": "cee2a055-aeec-49f2-98db-1ac5552d08ed", "metadata": {}, "source": [ "# 5. Percentage of Questions Answered Over the Years" ] }, { "cell_type": "code", "execution_count": 70, "id": "64d4e999-c238-4d6f-be57-0c1010d9ef7d", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 923:===================================================> (195 + 5) / 200]24]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----+---------------+------------------+-----------------+\n", "|year|total_questions|answered_questions|answer_rate |\n", "+----+---------------+------------------+-----------------+\n", "|2008|57569 |57495 |99.87145859750908|\n", "|2009|341651 |340436 |99.6443739371464 |\n", "|2010|690840 |684254 |99.0466678246772 |\n", "|2011|1189881 |1161031 |97.57538779087993|\n", "|2012|1629386 |1549529 |95.09895138414102|\n", "|2013|2033690 |1879399 |92.41324882356702|\n", "|2014|2137435 |1908588 |89.29338202097374|\n", "|2015|2196676 |1912123 |87.04620071416996|\n", "|2016|2200802 |1878653 |85.36219977989842|\n", "|2017|2116212 |1774813 |83.86744806285948|\n", "|2018|1888989 |1562874 |82.73600322712308|\n", "|2019|1766933 |1456706 |82.44262798872396|\n", "|2020|1871695 |1523233 |81.3825436302389 |\n", "|2021|1629580 |1235736 |75.83156396126608|\n", "|2022|1268788 |778449 |61.35374861679019|\n", "+----+---------------+------------------+-----------------+\n", "\n", "CPU times: user 56.7 ms, sys: 18.6 ms, total: 75.3 ms\n", "Wall time: 29.6 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "result_q5 = g.vertices \\\n", " .filter(\"type = 'Post_question'\") \\\n", " .filter(\"creation_date IS NOT NULL\") \\\n", " .withColumn(\"year\", year(col(\"creation_date\"))) \\\n", " .groupBy(\"year\") \\\n", " .agg(\n", " count(\"id\").alias(\"total_questions\"),\n", " sql_sum(when(col(\"answer_count\") > 0, 1).otherwise(0)).alias(\"answered_questions\")\n", " ) \\\n", " .withColumn(\"answer_rate\", col(\"answered_questions\") / col(\"total_questions\") * 100) \\\n", " .orderBy(asc(\"year\"))\n", "\n", "result_q5.show(50, truncate=False)" ] }, { "cell_type": "markdown", "id": "1995a279-d061-480b-846b-054599bf4696", "metadata": {}, "source": [ "# 6. Top 10 \"Easiest\" Gold Badges to Earn" ] }, { "cell_type": "code", "execution_count": 71, "id": "a4bab6dd-276a-43b6-a637-b237469abcac", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 984:===================================================> (195 + 5) / 200]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------------+-------------+----------------+\n", "|badge_name |total_earners|min_time_to_earn|\n", "+----------------+-------------+----------------+\n", "|Famous Question |448095 |0 |\n", "|Great Answer |56296 |0 |\n", "|Fanatic |42012 |98 |\n", "|Great Question |36230 |0 |\n", "|Electorate |27510 |14 |\n", "|Unsung Hero |26872 |4 |\n", "|Populist |22266 |0 |\n", "|Stellar Question|8119 |3 |\n", "|Steward |7981 |21 |\n", "|Publicist |7868 |1 |\n", "+----------------+-------------+----------------+\n", "\n", "CPU times: user 162 ms, sys: 40.7 ms, total: 202 ms\n", "Wall time: 1min 30s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "motif_q6 = g.find(\"(u)-[e]->(b)\") \\\n", " .filter(\"e.etype = 'Earns'\") \\\n", " .filter(\"u.type = 'User'\") \\\n", " .filter(\"b.type = 'Badge'\") \\\n", " .filter(\"b.class = '1'\") \\\n", " .select(\n", " col(\"b.name\").alias(\"badge_name\"),\n", " col(\"u.id\").alias(\"user_id\"),\n", " col(\"u.creation_date\").alias(\"user_creation_date\"),\n", " col(\"b.date\").alias(\"badge_date\")\n", " ) \\\n", " .withColumn(\"days_to_earn\", datediff(col(\"badge_date\"), col(\"user_creation_date\"))) \\\n", " .groupBy(\"badge_name\") \\\n", " .agg(\n", " countDistinct(\"user_id\").alias(\"total_earners\"),\n", " sql_min(\"days_to_earn\").alias(\"min_time_to_earn\")\n", " ) \\\n", " .filter(\"min_time_to_earn >= 0\") \\\n", " .orderBy(desc(\"total_earners\")) \\\n", " .limit(10)\n", "\n", "motif_q6.show(10, truncate=False)" ] }, { "cell_type": "markdown", "id": "c80252d8-0103-4e90-933d-595c9a412b5c", "metadata": {}, "source": [ "# 7. Vote Distribution" ] }, { "cell_type": "code", "execution_count": 76, "id": "7bfc37f5-8bcc-449f-b7a0-e52f32c95630", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "26/02/21 16:34:28 WARN DAGScheduler: Broadcasting large task binary with size 1998.8 KiB\n", "26/02/21 16:34:30 WARN DAGScheduler: Broadcasting large task binary with size 2.0 MiB\n", "[Stage 1403:===================================================>(199 + 1) / 200]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-------+--------------------+-----------+\n", "|id |display_name |total_votes|\n", "+-------+--------------------+-----------+\n", "|22656 |Jon Skeet |517780 |\n", "|6309 |VonC |231400 |\n", "|157882 |BalusC |201681 |\n", "|100297 |Martijn Pieters |192307 |\n", "|29407 |Darin Dimitrov |168172 |\n", "|1144035|Gordon Linoff |153385 |\n", "|23354 |Marc Gravell |150035 |\n", "|157247 |T.J. Crowder |146664 |\n", "|893 |Greg Hewgill |135440 |\n", "|115145 |CommonsWare |123794 |\n", "|34397 |SLaks |117111 |\n", "|17034 |Hans Passant |112406 |\n", "|19068 |Quentin |108923 |\n", "|2901002|jezrael |106039 |\n", "|218196 |Felix Kling |103256 |\n", "|190597 |unutbu |103251 |\n", "|95810 |Alex Martelli |99508 |\n", "|14860 |paxdiablo |98641 |\n", "|61974 |Mark Byers |96642 |\n", "|5445 |Christian C. Salvadó|95050 |\n", "+-------+--------------------+-----------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 643 ms, sys: 139 ms, total: 782 ms\n", "Wall time: 4min 5s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "# Votes on questions -> question owner\n", "votes_q = g.find(\"(v)-[e1]->(q); (q)-[e2]->(u)\") \\\n", " .filter(\"e1.etype = 'Vote_on'\") \\\n", " .filter(\"e2.etype = 'Owned_by'\") \\\n", " .filter(\"v.type = 'Vote'\") \\\n", " .filter(\"q.type = 'Post_question'\") \\\n", " .filter(\"u.type = 'User'\") \\\n", " .select(col(\"u.id\").alias(\"user_id\"))\n", "\n", "# Votes on answers -> answer author\n", "votes_a = g.find(\"(v)-[e1]->(a); (a)-[e2]->(u)\") \\\n", " .filter(\"e1.etype = 'Vote_on'\") \\\n", " .filter(\"e2.etype = 'Answers_by'\") \\\n", " .filter(\"v.type = 'Vote'\") \\\n", " .filter(\"a.type = 'Post_answer'\") \\\n", " .filter(\"u.type = 'User'\") \\\n", " .select(col(\"u.id\").alias(\"user_id\"))\n", "\n", "user_vote_counts = votes_q.union(votes_a) \\\n", " .groupBy(\"user_id\") \\\n", " .agg(count(\"*\").alias(\"total_votes\")) \\\n", " .withColumnRenamed(\"user_id\", \"uid\") # avoid ambiguity before join\n", "\n", "result_q7 = user_vote_counts \\\n", " .join(g.vertices.filter(\"type = 'User'\").alias(\"u\"),\n", " col(\"uid\") == col(\"u.id\"), \"inner\") \\\n", " .select(\n", " regexp_replace(col(\"u.id\"), \"user_\", \"\").alias(\"id\"),\n", " col(\"u.display_name\"),\n", " col(\"total_votes\")\n", " ) \\\n", " .orderBy(desc(\"total_votes\"))\n", "\n", "result_q7.show(20, truncate=False)" ] }, { "cell_type": "markdown", "id": "9c19127c-c270-47ec-a376-df5c7e26dfd3", "metadata": {}, "source": [ "# 8. Most Linked Questions" ] }, { "cell_type": "code", "execution_count": 73, "id": "a7780282-38fb-4a24-8deb-f9683bb0b2d4", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 1087:================================================> (189 + 11) / 200]]]]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+--------+----------+\n", "|id |link_count|\n", "+--------+----------+\n", "|5963269 |18394 |\n", "|14220321|10956 |\n", "|218384 |10286 |\n", "|60174 |9759 |\n", "|1732348 |9755 |\n", "|388242 |5994 |\n", "|4660142 |5229 |\n", "|588004 |4578 |\n", "|12859942|4532 |\n", "|605845 |4208 |\n", "|10714251|3981 |\n", "|513832 |3958 |\n", "|12573816|3430 |\n", "|4261133 |3312 |\n", "|1452721 |3106 |\n", "|750486 |2878 |\n", "|203198 |2834 |\n", "|20109391|2823 |\n", "|20279484|2682 |\n", "|18050071|2388 |\n", "+--------+----------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 156 ms, sys: 34.6 ms, total: 191 ms\n", "Wall time: 1min 31s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "motif_q8 = g.find(\"(q1)-[e]->(q2)\") \\\n", " .filter(\"e.etype = 'Link_to'\") \\\n", " .filter(\"q1.type = 'Post_question'\") \\\n", " .filter(\"q2.type = 'Post_question'\") \\\n", " .groupBy(col(\"q2.id\").alias(\"question_id\")) \\\n", " .agg(count(\"*\").alias(\"link_count\")) \\\n", " .select(\n", " regexp_replace(col(\"question_id\"), \"question_\", \"\").alias(\"id\"),\n", " col(\"link_count\")\n", " ) \\\n", " .orderBy(desc(\"link_count\"))\n", "\n", "motif_q8.show(20, truncate=False)" ] }, { "cell_type": "markdown", "id": "bf6f37b8-076c-48f1-a3d7-d3d93056c239", "metadata": {}, "source": [ "# 9. Users Who Comment Most" ] }, { "cell_type": "code", "execution_count": 74, "id": "1e5aa142-9020-402f-b588-c51692c08423", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 1136:========================================> (158 + 42) / 200] 200]]0]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-------+--------------------+--------------+\n", "|id |display_name |total_comments|\n", "+-------+--------------------+--------------+\n", "|1491895|Barmar |167475 |\n", "|22656 |Jon Skeet |87006 |\n", "|1048572|Bergi |79365 |\n", "|1144035|Gordon Linoff |74579 |\n", "|-1 |Community |74550 |\n", "|440558 |Some programmer dude|69749 |\n", "|17034 |Hans Passant |68212 |\n", "|505088 |David Heffernan |64719 |\n", "|3832970|Wiktor Stribiżew |63155 |\n", "|1415724|Funk Forty Niner |59857 |\n", "|15168 |Jonathan Leffler |59119 |\n", "|224132 |Peter Cordes |59011 |\n", "|157247 |T.J. Crowder |58088 |\n", "|207421 |user207421 |57206 |\n", "|2308683|OneCricketeer |56098 |\n", "|100297 |Martijn Pieters |54986 |\n", "|14122 |Charles Duffy |53971 |\n", "|115145 |CommonsWare |53256 |\n", "|330315 |a_horse_with_no_name|52954 |\n", "|1175966|charlietfl |52278 |\n", "+-------+--------------------+--------------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 182 ms, sys: 48.7 ms, total: 231 ms\n", "Wall time: 1min 34s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "motif_q9 = g.find(\"(c)-[e]->(u)\") \\\n", " .filter(\"e.etype = 'Commented_by'\") \\\n", " .filter(\"c.type = 'Comment'\") \\\n", " .filter(\"u.type = 'User'\") \\\n", " .groupBy(col(\"u.id\").alias(\"user_id\"), col(\"u.display_name\")) \\\n", " .agg(count(\"*\").alias(\"total_comments\")) \\\n", " .select(\n", " regexp_replace(col(\"user_id\"), \"user_\", \"\").alias(\"id\"),\n", " col(\"display_name\"),\n", " col(\"total_comments\")\n", " ) \\\n", " .orderBy(desc(\"total_comments\"))\n", "\n", "motif_q9.show(20, truncate=False)" ] }, { "cell_type": "markdown", "id": "90559137-0801-4bdf-bb20-204624110e8e", "metadata": {}, "source": [ "# 10.Most Accepted Answer" ] }, { "cell_type": "code", "execution_count": 75, "id": "bf61f50a-03f4-4c7e-81ff-2f3ee53dfd73", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "26/02/21 16:30:29 WARN DAGScheduler: Broadcasting large task binary with size 1090.7 KiB\n", "[Stage 1206:=================================================> (192 + 8) / 200]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-------+-------------------+---------------------+\n", "|id |display_name |accepted_answer_count|\n", "+-------+-------------------+---------------------+\n", "|1144035|Gordon Linoff |37191 |\n", "|22656 |Jon Skeet |22149 |\n", "|2901002|jezrael |21855 |\n", "|3732271|akrun |19647 |\n", "|1491895|Barmar |16684 |\n", "|548225 |anubhava |14608 |\n", "|100297 |Martijn Pieters |14559 |\n", "|3832970|Wiktor Stribiżew |14330 |\n", "|115145 |CommonsWare |13725 |\n", "|157247 |T.J. Crowder |13275 |\n", "|6309 |VonC |12743 |\n", "|29407 |Darin Dimitrov |12683 |\n", "|157882 |BalusC |11713 |\n", "|19068 |Quentin |10314 |\n", "|1863229|Tim Biegeleisen |9999 |\n", "|3962914|Ronak Shah |9746 |\n", "|17034 |Hans Passant |9717 |\n", "|209103 |Frank van Puffelen |9487 |\n", "|335858 |Sergey Kalinichenko|8841 |\n", "|23354 |Marc Gravell |8829 |\n", "+-------+-------------------+---------------------+\n", "only showing top 20 rows\n", "\n", "CPU times: user 273 ms, sys: 65.1 ms, total: 339 ms\n", "Wall time: 1min 56s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "motif_q10 = g.find(\"(q)-[e1]->(a); (a)-[e2]->(u)\") \\\n", " .filter(\"e1.etype = 'Accepts'\") \\\n", " .filter(\"e2.etype = 'Answers_by'\") \\\n", " .filter(\"q.type = 'Post_question'\") \\\n", " .filter(\"a.type = 'Post_answer'\") \\\n", " .filter(\"u.type = 'User'\") \\\n", " .groupBy(col(\"u.id\").alias(\"user_id\"), col(\"u.display_name\")) \\\n", " .agg(count(\"*\").alias(\"accepted_answer_count\")) \\\n", " .select(\n", " regexp_replace(col(\"user_id\"), \"user_\", \"\").alias(\"id\"),\n", " col(\"display_name\"),\n", " col(\"accepted_answer_count\")\n", " ) \\\n", " .orderBy(desc(\"accepted_answer_count\"))\n", "\n", "motif_q10.show(20, truncate=False)" ] }, { "cell_type": "code", "execution_count": null, "id": "c408d9ae-8a56-435f-9b69-8b383d9e42eb", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.10.19" } }, "nbformat": 4, "nbformat_minor": 5 }