{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "99e8d2e1-ed53-4408-93c1-ff085e5fd8aa", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ ":: loading settings :: url = jar:file:/shared/spark-3.4.4-bin-hadoop3/jars/ivy-2.5.1.jar!/org/apache/ivy/core/settings/ivysettings.xml\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "Ivy Default Cache set to: /home/exouser/.ivy2/cache\n", "The jars for the packages stored in: /home/exouser/.ivy2/jars\n", "graphframes#graphframes added as a dependency\n", ":: resolving dependencies :: org.apache.spark#spark-submit-parent-2c6a9db3-d9d5-4193-b7d1-d1fc44ce4c46;1.0\n", "\tconfs: [default]\n", "\tfound graphframes#graphframes;0.8.3-spark3.4-s_2.12 in spark-packages\n", "\tfound org.slf4j#slf4j-api;1.7.16 in central\n", ":: resolution report :: resolve 117ms :: artifacts dl 5ms\n", "\t:: modules in use:\n", "\tgraphframes#graphframes;0.8.3-spark3.4-s_2.12 from spark-packages in [default]\n", "\torg.slf4j#slf4j-api;1.7.16 from central in [default]\n", "\t---------------------------------------------------------------------\n", "\t| | modules || artifacts |\n", "\t| conf | number| search|dwnlded|evicted|| number|dwnlded|\n", "\t---------------------------------------------------------------------\n", "\t| default | 2 | 0 | 0 | 0 || 2 | 0 |\n", "\t---------------------------------------------------------------------\n", ":: retrieving :: org.apache.spark#spark-submit-parent-2c6a9db3-d9d5-4193-b7d1-d1fc44ce4c46\n", "\tconfs: [default]\n", "\t0 artifacts copied, 2 already retrieved (0kB/4ms)\n", "Setting default log level to \"WARN\".\n", "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n", "26/02/17 20:17:36 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(\"amzon_graphframes\")\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", ")" ] }, { "cell_type": "code", "execution_count": 2, "id": "57a3c844-af71-417f-bbc8-046d9315c4bb", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Paths configured successfully!\n" ] } ], "source": [ "# Define base HDFS path\n", "base_hdfs = \"hdfs:///tg/amazon\"\n", "\n", "# Define paths for all vertex data (nodes)\n", "product_path = f\"{base_hdfs}/product_json\"\n", "reviewer_path = f\"{base_hdfs}/reviewer_json\"\n", "review_path = f\"{base_hdfs}/review_json\"\n", "category_path = f\"{base_hdfs}/category_json\"\n", "image_path = f\"{base_hdfs}/image_json\"\n", "reviewimage_path = f\"{base_hdfs}/reviewimage_json\"\n", "\n", "# Define paths for all edge data (relationships)\n", "also_buy_path = f\"{base_hdfs}/also_buy_edge\"\n", "also_view_path = f\"{base_hdfs}/also_view_edge\"\n", "reviews_edge_path = f\"{base_hdfs}/reviews_edge\"\n", "wrote_edge_path = f\"{base_hdfs}/wrote_edge\"\n", "in_category_path = f\"{base_hdfs}/in_category_edge\"\n", "has_product_image_path = f\"{base_hdfs}/has_product_image_edge\"\n", "has_review_image_path = f\"{base_hdfs}/has_review_image_edge\"\n", "\n", "print(\"Paths configured successfully!\")" ] }, { "cell_type": "code", "execution_count": 3, "id": "67df2623-3429-4b08-9118-b82cf77915f9", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "26/02/17 20:17:48 WARN SharedInMemoryCache: Evicting cached table partition metadata from memory due to size constraints (spark.sql.hive.filesourcePartitionFileCacheSize = 262144000 bytes). This may impact query planning performance.\n", " " ] }, { "name": "stdout", "output_type": "stream", "text": [ "All data loaded successfully!\n" ] } ], "source": [ "# Load all JSON data from HDFS\n", "df_products = spark.read.json(product_path)\n", "df_reviewers = spark.read.json(reviewer_path)\n", "df_reviews = spark.read.json(review_path)\n", "df_categories = spark.read.json(category_path)\n", "df_images = spark.read.json(image_path)\n", "df_reviewimages = spark.read.json(reviewimage_path)\n", "\n", "print(\"All data loaded successfully!\")" ] }, { "cell_type": "code", "execution_count": 4, "id": "69d75544-636b-47b3-9d50-698b489c651e", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Product vertices created!\n" ] } ], "source": [ "from pyspark.sql import Window\n", "from pyspark.sql import functions as F\n", "\n", "# Create a window partitioned by asin\n", "window = Window.partitionBy(\"asin\").orderBy(\n", " F.when(F.col(\"title\").isNotNull(), 0).otherwise(1), # Prioritize non-null titles\n", " F.when(F.col(\"brand\").isNotNull(), 0).otherwise(1), # Then non-null brands\n", " F.desc(\"price_min\") # Then highest price\n", ")\n", "\n", "# Add row number and keep only the first (best) row per asin\n", "product_vertices = df_products.withColumn(\"row_num\", F.row_number().over(window)) \\\n", " .filter(F.col(\"row_num\") == 1) \\\n", " .drop(\"row_num\") \\\n", " .selectExpr(\n", " \"asin as id\",\n", " \"'product' as type\",\n", " \"title\",\n", " \"brand\",\n", " \"description_str as description\",\n", " \"price_min\",\n", " \"price_max\"\n", " )\n", "\n", "print(\"Product vertices created!\")" ] }, { "cell_type": "code", "execution_count": 5, "id": "fbd75e7b-17cb-4838-b61e-7cbc96ff3483", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Reviewer vertices created!\n" ] } ], "source": [ "# Reviewer vertices\n", "reviewer_vertices = df_reviewers.selectExpr(\n", " \"reviewerID as id\",\n", " \"'reviewer' as type\",\n", " \"name\"\n", ").distinct()\n", "\n", "print(\"Reviewer vertices created!\")" ] }, { "cell_type": "code", "execution_count": 6, "id": "3b6a0b8b-b215-42b1-a6ed-99071dfc9f4d", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Review vertices created!\n" ] } ], "source": [ "# Review vertices\n", "review_vertices = df_reviews.selectExpr(\n", " \"review_id as id\",\n", " \"'review' as type\",\n", " \"reviewerID\",\n", " \"asin\",\n", " \"reviewText\",\n", " \"overall as rating\",\n", " \"summary\",\n", " \"unixReviewTime\",\n", " \"vote\"\n", ").distinct()\n", "\n", "print(\"Review vertices created!\")" ] }, { "cell_type": "code", "execution_count": 7, "id": "5e07bf3e-0b7a-4ee5-bd79-24ca7f3ee5f4", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Category vertices created!\n" ] } ], "source": [ "# Category vertices\n", "category_vertices = df_categories.selectExpr(\n", " \"name as id\",\n", " \"'category' as type\",\n", " \"name\"\n", ").distinct()\n", "\n", "print(\"Category vertices created!\")" ] }, { "cell_type": "code", "execution_count": 8, "id": "7d49d095-4ffa-4326-b1bf-fc402e815b80", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Image vertices created!\n" ] } ], "source": [ "# Image vertices (product images)\n", "image_vertices = df_images.selectExpr(\n", " \"url as id\",\n", " \"'image' as type\",\n", " \"url\"\n", ").distinct()\n", "\n", "print(\"Image vertices created!\")" ] }, { "cell_type": "code", "execution_count": 9, "id": "dede78fd-df36-4359-96b7-fc1680791e05", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "ReviewImage vertices created!\n" ] } ], "source": [ "# ReviewImage vertices (images attached to reviews)\n", "reviewimage_vertices = df_reviewimages.selectExpr(\n", " \"url as id\",\n", " \"'reviewimage' as type\",\n", " \"url\"\n", ").distinct()\n", "\n", "print(\"ReviewImage vertices created!\")" ] }, { "cell_type": "code", "execution_count": 10, "id": "8bc7b887-7bbe-44fa-bade-6b5ad7de733c", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ " " ] }, { "name": "stdout", "output_type": "stream", "text": [ "All edge data loaded successfully!\n" ] } ], "source": [ "# Load all edge data from HDFS\n", "df_also_buy = spark.read.json(also_buy_path)\n", "df_also_view = spark.read.json(also_view_path)\n", "df_reviews_edge = spark.read.json(reviews_edge_path)\n", "df_wrote_edge = spark.read.json(wrote_edge_path)\n", "df_in_category = spark.read.json(in_category_path)\n", "df_has_product_image = spark.read.json(has_product_image_path)\n", "df_has_review_image = spark.read.json(has_review_image_path)\n", "\n", "print(\"All edge data loaded successfully!\")" ] }, { "cell_type": "code", "execution_count": 11, "id": "70a2a1cb-b363-4bdd-b1d8-ccdce3c29e24", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "ALSO_BUY edges created!\n" ] } ], "source": [ "# ALSO_BUY edge: Product -> Product\n", "also_buy_edges = df_also_buy.selectExpr(\n", " \"from_asin as src\",\n", " \"to_asin as dst\",\n", " \"'also_buy' as relationship\"\n", ").distinct()\n", "\n", "print(\"ALSO_BUY edges created!\")" ] }, { "cell_type": "code", "execution_count": 12, "id": "f1b22f39-3447-45b9-a159-25d2b28caf60", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "ALSO_VIEW edges created!\n" ] } ], "source": [ "# ALSO_VIEW edge: Product -> Product\n", "also_view_edges = df_also_view.selectExpr(\n", " \"from_asin as src\",\n", " \"to_asin as dst\",\n", " \"'also_view' as relationship\"\n", ").distinct()\n", "\n", "print(\"ALSO_VIEW edges created!\")" ] }, { "cell_type": "code", "execution_count": 13, "id": "80dc2309-e8a6-4736-bf45-1a9cabb8b05a", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "REVIEWS edges created!\n" ] } ], "source": [ "# REVIEWS edge: Review -> Product\n", "reviews_edges = df_reviews_edge.selectExpr(\n", " \"from_review_id as src\",\n", " \"to_asin as dst\",\n", " \"'reviews' as relationship\"\n", ").distinct()\n", "\n", "print(\"REVIEWS edges created!\")" ] }, { "cell_type": "code", "execution_count": 14, "id": "409ec3a7-fe84-42c0-a504-d279c5a627ee", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "WROTE edges created!\n" ] } ], "source": [ "# WROTE edge: Reviewer -> Review\n", "wrote_edges = df_wrote_edge.selectExpr(\n", " \"from_reviewerID as src\",\n", " \"to_review_id as dst\",\n", " \"'wrote' as relationship\"\n", ").distinct()\n", "\n", "print(\"WROTE edges created!\")" ] }, { "cell_type": "code", "execution_count": 15, "id": "b3565bc2-ee3e-42b6-875d-c82eaa336065", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "IN_CATEGORY edges created!\n" ] } ], "source": [ "# IN_CATEGORY edge: Product -> Category\n", "in_category_edges = df_in_category.selectExpr(\n", " \"from_asin as src\",\n", " \"to_category as dst\",\n", " \"'in_category' as relationship\"\n", ").distinct()\n", "\n", "print(\"IN_CATEGORY edges created!\")" ] }, { "cell_type": "code", "execution_count": 16, "id": "bdd0ef0a-4488-439b-bc71-4de2d51e3bef", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "HAS_PRODUCT_IMAGE edges created!\n" ] } ], "source": [ "# HAS_PRODUCT_IMAGE edge: Product -> Image\n", "has_product_image_edges = df_has_product_image.selectExpr(\n", " \"from_asin as src\",\n", " \"to_url as dst\",\n", " \"'has_product_image' as relationship\"\n", ").distinct()\n", "\n", "print(\"HAS_PRODUCT_IMAGE edges created!\")" ] }, { "cell_type": "code", "execution_count": 17, "id": "1cf957e4-7b91-4ebc-8b11-36fc2438f4cc", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "HAS_REVIEW_IMAGE edges created!\n" ] } ], "source": [ "# HAS_REVIEW_IMAGE edge: Review -> ReviewImage\n", "has_review_image_edges = df_has_review_image.selectExpr(\n", " \"from_review_id as src\",\n", " \"to_url as dst\",\n", " \"'has_review_image' as relationship\"\n", ").distinct()\n", "\n", "print(\"HAS_REVIEW_IMAGE edges created!\")" ] }, { "cell_type": "code", "execution_count": 18, "id": "0d432066-0afa-493c-93dd-f81e90dad3e5", "metadata": {}, "outputs": [], "source": [ "# Union all vertex types into a single DataFrame\n", "\n", "vertices = product_vertices \\\n", " .unionByName(reviewer_vertices, allowMissingColumns=True) \\\n", " .unionByName(review_vertices, allowMissingColumns=True) \\\n", " .unionByName(category_vertices, allowMissingColumns=True) \\\n", " .unionByName(image_vertices, allowMissingColumns=True) \\\n", " .unionByName(reviewimage_vertices, allowMissingColumns=True)\n", "\n", "# Union all edge types into a single DataFrame\n", "edges = also_buy_edges \\\n", " .unionByName(also_view_edges, allowMissingColumns=True) \\\n", " .unionByName(reviews_edges, allowMissingColumns=True) \\\n", " .unionByName(wrote_edges, allowMissingColumns=True) \\\n", " .unionByName(in_category_edges, allowMissingColumns=True) \\\n", " .unionByName(has_product_image_edges, allowMissingColumns=True) \\\n", " .unionByName(has_review_image_edges, allowMissingColumns=True)\n", "\n", "# Create the GraphFrames\n", "#g = GraphFrames(vertices, edges)\n" ] }, { "cell_type": "code", "execution_count": 19, "id": "6009c6b1-8f6a-4e55-aeb8-66031b2fe50b", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Requirement already satisfied: numpy in ./anaconda3/lib/python3.10/site-packages (1.26.4)\n" ] } ], "source": [ "!pip install numpy\n", "\n", "from graphframes import GraphFrames\n", "\n", "g = GraphFrames(vertices, edges)" ] }, { "cell_type": "code", "execution_count": 20, "id": "501c9aea-8898-406c-9f12-18e403aa00bf", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "/shared/anaconda3/bin/python\n" ] } ], "source": [ "import sys\n", "print(sys.executable)\n" ] }, { "cell_type": "code", "execution_count": 21, "id": "1ba1d47f-b101-4fff-99af-f1eccfd86b0e", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 38:====================================================> (340 + 8) / 348]1]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-----------+---------+\n", "| type| count|\n", "+-----------+---------+\n", "| product| 22217153|\n", "| reviewer| 42369658|\n", "| review|213424686|\n", "| category| 2403388|\n", "|reviewimage| 6027346|\n", "| image| 15839958|\n", "+-----------+---------+\n", "\n", "CPU times: user 82.3 ms, sys: 20.1 ms, total: 102 ms\n", "Wall time: 46.9 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "vertices.groupBy(\"type\").count().show()" ] }, { "cell_type": "code", "execution_count": 22, "id": "d4935553-14d6-4337-93e0-0832955c86ff", "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "[Stage 61:=====================================================>(499 + 3) / 502]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-----------------+---------+\n", "| relationship| count|\n", "+-----------------+---------+\n", "| also_buy| 90037255|\n", "| reviews|213190633|\n", "| wrote|213190633|\n", "| also_view| 82581187|\n", "| in_category| 57932047|\n", "|has_product_image| 20873843|\n", "| has_review_image| 6223409|\n", "+-----------------+---------+\n", "\n", "CPU times: user 34.4 ms, sys: 8.7 ms, total: 43.1 ms\n", "Wall time: 17.4 s\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "%%time\n", "edges.groupBy(\"relationship\").count().show()" ] }, { "cell_type": "markdown", "id": "bd64dba6-f530-43ac-9da8-9a21080f5f72", "metadata": {}, "source": [ "## QUERIES" ] }, { "cell_type": "code", "execution_count": 23, "id": "d899a195-42de-4c35-9e1b-3ba451d58382", "metadata": {}, "outputs": [], "source": [ "import time\n", "\n", "def measure(df):\n", " start = time.time()\n", " df.count() # triggers the Spark job\n", " end = time.time()\n", " print(f\"Time taken: {end - start:.3f} seconds\")\n", " return df # return the DataFrame itself\n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "0c74ac57-0c2e-4347-be77-f46b11f5ea41", "metadata": {}, "source": [ "## ===Review===" ] }, { "attachments": {}, "cell_type": "markdown", "id": "ea5bf8bc-f3cc-4ced-9ac3-304671f34723", "metadata": {}, "source": [ "#### Products with the Most Reviews\n", "#### Identify the top 200 products with the most reviews (customer engagement)." ] }, { "cell_type": "code", "execution_count": 25, "id": "08caf54f-87f9-4faf-bab3-ce11c6c81a67", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Top 200 Products with Most Reviews\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " ]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Time taken: 62.075 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "[Stage 217:====================================================>(199 + 1) / 200]]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------+------------------------------------------------------------+-----------+\n", "| ASIN| Product|ReviewCount|\n", "+----------+------------------------------------------------------------+-----------+\n", "|038568231X| The Girl on the Train| 58150|\n", "|0297859382| Gone Girl| 44955|\n", "|0007420412| Divergent| 44196|\n", "|0141353678| The Fault in Our Stars| 37778|\n", "|0312577222| The Nightingale: A Novel| 36620|\n", "|0099911701| Cross Stitch| 33483|\n", "|0553418025| The Martian| 30297|\n", "|B010OYASRG|OontZ Angle 3 Enhanced Stereo Edition IPX5 Splashproof Po...| 28530|\n", "|0007548672| All the Light We Cannot See| 27954|\n", "|B00FLYWNYQ|Instant Pot DUO60 6 Qt 7-in-1 Multi-Use Programmable Pres...| 27595|\n", "|0316055433| The Goldfinch: A Novel (Pulitzer Prize for Fiction)| 25713|\n", "|B00YSG2ZPA| Band of Brothers(Elite SC/BD+DCExp12-21)| 24556|\n", "|B00006CXSS| | 24487|\n", "|0439023521| The Hunger Games (Book 1)| 22053|\n", "|8184776217| Mockingjay: The Hunger Games| 22027|\n", "|0545582881| Harry Potter and the Sorcerer's Stone (Book 1)| 21601|\n", "|B017WJ5PR4| | 21531|\n", "|B017V4IPPO| | 21509|\n", "|0385537859| Inferno| 21017|\n", "|B00AQVMZKQ| Downton Abbey - Series 4| 21015|\n", "|B01BHTSIOC| Doctor Thorne - Season 1 2015| 20889|\n", "|B00L0YLRUW| NETGEAR N300 WiFi Range Extender (EX2700)| 20873|\n", "|0143125478|The Boys in the Boat: Nine Americans and Their Epic Quest...| 20649|\n", "|006195070X| Orphan Train: A Novel| 20405|\n", "|1683247353| Beneath a Scarlet Sky (Center Point Large Print)| 19208|\n", "|B000OX89XI|MidWest Homes for Pets Dog Crate | iCrate Single Door &am...| 19206|\n", "|0345543246| Sycamore Row (The Jake Brigance)| 19077|\n", "|0399159347| The Husband's Secret| 18777|\n", "|B001C4VLZQ| Hobbit and Lord of the Rings Trilogy - Boxed Set of 4 Books| 18619|\n", "|B000K5THYU|THE LORD OF THE RINGS Trilogy - (The Fellowship of the Ri...| 18598|\n", "|B000FK9M18| Hobbit| 18597|\n", "|000711835X| The Hobbit| 18497|\n", "|B003156C4E| | 18443|\n", "|0345543254| Gray Mountain: A Novel| 18244|\n", "|1503943372| Beneath a Scarlet Sky: A Novel| 18060|\n", "|0007444117| Allegiant| 18039|\n", "|037032921X| The BOOK THIEF| 17663|\n", "|030788743X| Ready Player One| 17129|\n", "|B00DIF2BO2| | 17038|\n", "|B00NAQ3EOK|The Captive Ryan Reynolds, Scott Speedman, Rosario Dawson...| 16857|\n", "|6305837325| | 16671|\n", "|0529120887| Jesus Calling: Enjoying Peace in His Presence| 16658|\n", "|0340979496| The Shack| 16394|\n", "|B000X1MX7E| | 16248|\n", "|0143142372| The Pillars of the Earth| 16234|\n", "|B006GWO5WK| | 16129|\n", "|B003L1ZYYW| | 16046|\n", "|B009HVH4XO| | 15798|\n", "|1410472922| A Man Called Ove (Thorndike Press Large Print Core Series)| 15786|\n", "|B00Q7EV29G| Zinus Memory Foam 8 Inch Green Tea Mattress, Queen| 15696|\n", "|0399167064| Big Little Lies| 15694|\n", "|0062082361|American Sniper: The Autobiography of the Most Lethal Sni...| 15553|\n", "|B00P7EVST6|Arlo - Wireless Home Security Camera System | Indoor/Outd...| 15451|\n", "|B00EINBSJ2|Coop Home Goods - PREMIUM Adjustable Loft - Shredded Hypo...| 15443|\n", "|B00C2WDD5I| | 15345|\n", "|B00WNBABVC| Walking Dead, The Ssn 5| 15263|\n", "|B0019HL8Q8|Mediabridge HDMI Cable (10 Feet) Supports 4K@60Hz, High S...| 15247|\n", "|0849946158|Heaven is for Real: A Little Boy's Astounding Story of Hi...| 15144|\n", "|0439249546| Harry Potter (4 Volumes set)| 14993|\n", "|B017S3OP7A| The Martian| 14795|\n", "|B00BWF5U0M|Sabrent 4-Port USB 2.0 Hub with Individual Power Switches...| 14671|\n", "|1101946342|Grey: Fifty Shades of Grey as Told by Christian (Fifty Sh...| 14600|\n", "|B00YN6XHMU| | 14568|\n", "|B009934S5M| Star Trek Into Darkness| 14485|\n", "|B00ITIQI92|Fintie Slimshell Case for Kindle Paperwhite - Fits All Pa...| 14413|\n", "|B00DRALJ28|AcuRite 00613 Humidity Monitor with Indoor Thermometer, D...| 14331|\n", "|B00006IEEV|Prismacolor 3598T Premier Colored Pencils, Soft Core, 48 ...| 14315|\n", "|B00IVPU7AO|Samsung EVO 64GB 48/MB/s Micro SDXC Memory Card with Ada...| 14244|\n", "|B00FL31UF0| The Hunger Games: Catching Fire 2013| 14174|\n", "|B014HDTT84| Bone Tomahawk| 14158|\n", "|B00OGL6S64|The white queen Stagione 01The wh...| 14143|\n", "|0316228532| The Casual Vacancy| 14130|\n", "|B00V7T1YRQ|J5 Tactical V1-PRO Flashlight - The Original 300 Lumen Ul...| 14073|\n", "|B00LV4W8BI|Hotel Luxury Bed Sheets Set TODAY! On Amazon Softest Bedd...| 14032|\n", "|B0002ERXC2| The Wire: Season 1| 14007|\n", "|B0019EHU8G|Mediabridge HDMI Cable (6 Feet) Supports 4K@60Hz, High Sp...| 13999|\n", "|0552778478|The Light Between Oceans: The heartbreaking Richard and J...| 13949|\n", "|B000BQ7GW8|SanDisk 2GB Class 4 SD Flash Memory Card- SDSDB-002G-B35 ...| 13776|\n", "|1469984202| Wool - Omnibus Edition| 13675|\n", "|B00R8GUXPG| | 13649|\n", "|B00PY4Q9OS| Guardians of the Galaxy| 13648|\n", "|B00Q0G2VXM| Guardians Of The Galaxy Region Free| 13627|\n", "|1503934713| The Butterfly Garden (The Collector)| 13602|\n", "|B00NX47YP4|Ozeri ZK14-R Pronto Digital Multifunction Kitchen and Foo...| 13582|\n", "|B00MQSMDYU|Portable Chargers 16750 RAVPower 16750mAh External Batter...| 13560|\n", "|0307943232| The Racketeer| 13510|\n", "|0425266060| Bared to You / Reflected in You / Entwined with You| 13438|\n", "|B000W5QSYA| | 13380|\n", "|B005NF5NTK|Anker PowerCore+ Mini, 3350mAh Lipstick-Sized Portable Ch...| 13246|\n", "|1477825576| My Sister's Grave (Tracy Crosswhite)| 13245|\n", "|0307476073| Wild: From Lost to Found on the Pacific Crest Trail| 13232|\n", "|B00M4NF9H0| TOMS Seasonal Classics Women's Slip on Shoes| 13164|\n", "|0007378033|Unbroken: An Extraordinary True Story of Courage and Surv...| 13098|\n", "|B00X5RV14Y|Anker PowerCore 20100 - Ultra High Capacity Power Bank wi...| 13054|\n", "|B0043T7FXE|Logitech M570 Wireless Trackball Mouse – Ergonomic ...| 13034|\n", "|0984502203| Yellow Crocus: A Novel| 12996|\n", "|0091955106|The Life-Changing Magic of Tidying: A simple, effective w...| 12975|\n", "|0375969020| Wonder| 12712|\n", "|B00AS1Q8FW| Jack Reacher Combo| 12703|\n", "|B00M55C0NS|SanDisk Ultra 32GB UHS-I/Class 10 Micro SDHC Memory Card ...| 12651|\n", "|0141378247| Wonder| 12645|\n", "|0802407692|The 5 Love Languages Military Edition: The Secret to Love...| 12586|\n", "|0099664313| Drums of Autumn| 12567|\n", "|B019D9HESO| RTIC 30 oz Stainless Steel Tumbler Cup w/ Splash Proof Lid| 12457|\n", "|B00AZBIZTW| Bissell 9595A CleanView Bagless Vacuum with OnePass| 12180|\n", "|0143121707| The Invention of Wings| 12064|\n", "|B00DMCV7K0| | 12061|\n", "|B0063FQREO| The Hunger Games (2 Disc) [Region 2] [UK Import]| 12011|\n", "|1503935310| Everything We Keep: A Novel| 12001|\n", "|B00E055H5O|(Old Model) WD My Passport Ultra 2 TB Portable External U...| 12001|\n", "|B00AE9V3WQ|Aylio Comfort Seat Cushion - Tailbone, Coccyx, Sciatica, ...| 11942|\n", "|B00SB123IM|By Marie Kondo The Life-Changing Magic of Tidying Up: The...| 11825|\n", "|0751565350|Harry Potter and the Cursed Child, parts one and two. [Ba...| 11817|\n", "|0316044695|Lone Survivor: The Eyewitness Account of Operation Redwin...| 11813|\n", "|B000BQN9LA| The Original Elevated Pet Bed by Coolaroo| 11802|\n", "|B000WGWQG8| John Adams| 11797|\n", "|0062409883| Go Set a Watchman: A Novel| 11765|\n", "|2253195103| 22/11/63| 11695|\n", "|1410440478| | 11675|\n", "|0385344430| Written in My Own Heart's Blood (Outlander)| 11662|\n", "|0007155662| The Alchemist| 11591|\n", "|0141192496| Oliver Twist (Penguin Clothbound Classics)| 11505|\n", "|0007350899| | 11503|\n", "|B00BUKL666|KIND Bars, Dark Chocolate Nuts & Sea Salt, Gluten Fre...| 11400|\n", "|B00AVWKUJS| | 11387|\n", "|B00BP5KOPA|Logitech MK270 Wireless Keyboard and Mouse Combo - Keyboa...| 11349|\n", "|B00F3EDEOC| Levi's Men's 505 Regular Fit Jean| 11282|\n", "|B00GAPACC0| Levi's Men's 505 Regular Fit Jean| 11255|\n", "|0307582884|The Maze Runner (Maze Runner, Book One) (The Maze Runner ...| 11251|\n", "|B00543R3WG| Harry Potter - Complete 8-Film Collection| 11249|\n", "|6077547328|Maze Runner. Correr o Morir / Maze Runner, Run or die (Sp...| 11235|\n", "|B00QN1T6NM|Maxboost iPhone 6 Screen Protector, [3 Pack ] iPhone 6 Te...| 11227|\n", "|0141027126| War Brides| 11160|\n", "|B000HTICY0| Levi's Men's 505 Regular Fit-Jeans| 11133|\n", "|B00BJLS55G|Stainless Steel Pet Id Tags, Personalized Dog Tags & ...| 11111|\n", "|B009ZJ2M7G| Hoover Power Scrub Deluxe Carpet Washer FH50150| 11094|\n", "|0451412656| AN Innocent Client (Joe Dillard)| 11087|\n", "|B0046ZT40W| Downton Abbey| 11055|\n", "|B00HUCF7AO| Orphan Black: Season 2| 11008|\n", "|B00OV3VGP0| John Wick| 10954|\n", "|0749958790|Proof of Heaven: A Neurosurgeon's Journey into the Afterlife| 10942|\n", "|0143109464|Me Before You: A Novel (Movie Tie-In) (Me Before You Tril...| 10874|\n", "|B00O4ZC57I| Big Hero 6| 10846|\n", "|0141331976| Dog Days (Diary of a Wimpy Kid)| 10793|\n", "|0099740915| The Handmaid's Tale (Contemporary Classics)| 10774|\n", "|B014EB532U|iPhone 8 Plus, 7 Plus, 6S Plus, 6 Plus Screen Protector, ...| 10730|\n", "|0805098542| Killing Jesus (Bill O'Reilly's Killing Series)| 10699|\n", "|B0043WJRRS| MagicFiber Microfiber Cleaning Cloths, 30 PACK| 10694|\n", "|B00902X68W|Clara Clark 1800 Premier Series 4pc Bed Sheet Set - King,...| 10665|\n", "|080509668X|Killing Patton: The Strange Death of World War II's Most ...| 10538|\n", "|B00CD8AFFW| AmazonBasics 14-Inch Laptop Sleeve - Black| 10500|\n", "|B00J2T18XS|Teenage Mutant Ninja Turtles: The Good, The Bad, and Case...| 10490|\n", "|B00SU52460| Converse Chuck Taylor All Star Core Ox| 10467|\n", "|B00SU5244M|Converse Unisex Chuck Taylor All Star Low Top Red Sneaker...| 10467|\n", "|B006W8U2MU| Kingston Digital DataTraveler SE9 8GB USB 2.0 DTSE9H/8GBZ| 10386|\n", "|0718178130| The Rosie Project| 10359|\n", "|B00WRGPHY4|Converse Unisex Chuck Taylor All Star Hi Top Sneaker Shoe...| 10359|\n", "|B00X8AMIDG| Converse Chuck Taylor All Star Canvas Low Top Sneaker| 10359|\n", "|B0001VL0KC| The Lord Of The Rings: Trilogy| 10308|\n", "|B00WBHSMOQ|Converse Chuck Taylor All Star Low Top (International Ver...| 10303|\n", "|0316206857| The Cuckoo's Calling (Cormoran Strike)| 10283|\n", "|B00VJOT0CK|Converse Men's Chuck Taylor All Star Leather Low Top Sneaker| 10272|\n", "|B00LCIYHSK|Grace Alley American Flag: American Made 3x5 FT US Flag M...| 10264|\n", "|B00GV8I1BA| Converse Women's Chuck Taylor All Star Core Hi| 10236|\n", "|B00COK3FD8|Rubbermaid Easy Find Lids Food Storage Containers, Racer ...| 10221|\n", "|B003MTTJOY|Edimax EW-7811Un 150Mbps 11n Wi-Fi USB Adapter, Nano Size...| 10194|\n", "|B00S1ITA2W| Kingsman: The Secret Service| 10166|\n", "|B00SB9Y6KW| American Sniper 2014| 10150|\n", "|B00V6K7P0G| Converse C/T All Star Hi Little Kids Fashion Sneakers Black| 10144|\n", "|B00AANQLRI|Jackery External Battery Charger Giant+ 12000mAh Dual USB...| 10142|\n", "|B00UI91214|Converse Chuck Taylor All Star Low Top (International Ver...| 10141|\n", "|B00UHAC5L0|Converse Chuck Taylor All Star Low Top Black MonochromeSn...| 10140|\n", "|B00UFJS0I0|Converse Unisex Chuck Taylor All Star Ox Low Top Navy Sne...| 10132|\n", "|B00MXWFUQC|TaoTronics Phone Holder for Cars, Windshield Dashboard Un...| 10121|\n", "|B0002DHXX2| MidWest Deluxe Bolster Pet Bed for Dogs & Cats| 10111|\n", "|B00IVM1TKO|NoCry Cut Resistant Gloves - High Performance Level 5 Pro...| 10080|\n", "|B00005JLF2| 24: Season 1| 10061|\n", "|0141039280| The Help| 10000|\n", "|B009A5204K| LG Tone HBS-730 Wireless Stereo Headset - Black| 9990|\n", "|B00HFJWKWK|AmazonBasics 6-Sheet Cross-Cut Paper and Credit Card Shre...| 9987|\n", "|B00NYC65M8| Jurassic World| 9976|\n", "|1447287967| Memory Man (Amos Decker series)| 9951|\n", "|1477820876| The Dead Key| 9947|\n", "|B00GAO5KE6| Levi's Men's 501 Original-Fit Jean| 9939|\n", "|B0002I0O5G|Outward Hound Hide-A-Squirrel Puzzle Plush Squeaking Toys...| 9932|\n", "|0002247399| A Dance with Dragons| 9921|\n", "|B0018OLONE| Levi's Men's 501 Original-Fit Jean| 9911|\n", "|0147520762|The Girl in the Spider's Web: A Lisbeth Salander novel, c...| 9908|\n", "|B001BIL3MY| Levi's Men's 501 Original-Fit Jean| 9891|\n", "|B000YXC2LI| Levi's Men's 501 Original-Fit Jean| 9851|\n", "|B0018OHL5E| Levi's Men's Big and Tall 501 Original Fit Jean| 9841|\n", "|B00M0GMC26| Outlander - Season 1| 9836|\n", "|B00STBRT9M|Converse Unisex Chuck Taylor All-Star High-Top Casual Sne...| 9781|\n", "|B000E372ZY| HERCULES| 9747|\n", "|006017322X| To Kill a Mockingbird| 9657|\n", "|B000CITK8S|Battery Tender 021-0123 Battery Tender Junior 12V, 0.75A ...| 9653|\n", "|B007WQ9YNE|Aroma Housewares 8-Cup (Cooked) (4-Cup UNCOOKED) Digital...| 9646|\n", "|0385539436| Rogue Lawyer: A Novel| 9551|\n", "|0307341577| Dark Places| 9494|\n", "|B0091YYUAM|BISSELL PowerFresh Steam Mop, Floor Steamer, Tile Cleaner...| 9472|\n", "+----------+------------------------------------------------------------+-----------+\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "from pyspark.sql import functions as F\n", "\n", "# --------------------------------------------------\n", "# 1️⃣ Graph Motif: (review)-[:reviews]->(product)\n", "# --------------------------------------------------\n", "\n", "reviews_motif = g.find(\"\"\"\n", " (review)-[e]->(product)\n", "\"\"\").filter(\"\"\"\n", " e.relationship = 'reviews' AND\n", " review.type = 'review' AND\n", " product.type = 'product'\n", "\"\"\")\n", "\n", "# --------------------------------------------------\n", "# 2️⃣ Count reviews per product\n", "# --------------------------------------------------\n", "\n", "review_counts = reviews_motif.groupBy(\n", " F.col(\"product.id\").alias(\"asin\"),\n", " F.col(\"product.title\").alias(\"title\")\n", ").agg(\n", " F.count(\"review.id\").alias(\"ReviewCount\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 3️⃣ Order and limit\n", "# --------------------------------------------------\n", "\n", "result_df = review_counts.select(\n", " F.col(\"asin\").alias(\"ASIN\"),\n", " F.coalesce(F.col(\"title\"), F.col(\"asin\")).alias(\"Product\"),\n", " F.col(\"ReviewCount\")\n", ").orderBy(\n", " F.desc(\"ReviewCount\")\n", ").limit(200)\n", "\n", "print(\"\\nTop 200 Products with Most Reviews\\n\")\n", "measure(result_df).show(200, truncate=60)\n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "ef848d5a-f057-4a39-a5c1-d4913a6f331e", "metadata": {}, "source": [ "#### Highly Rated Products with Many Reviews\n", "#### Find reliable “quality” products with ≥ 50 reviews and avg rating ≥ 4.0." ] }, { "cell_type": "code", "execution_count": 27, "id": "c8fb0da1-e291-4f70-9562-86e40dcf93d1", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Top 200 Highly Rated Products (Rating ≥ 4.0, Reviews ≥ 50)\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " ]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Time taken: 63.518 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "[Stage 256:====================================================>(198 + 2) / 200]200]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------+------------------------------------------------------------+---------+-----------+\n", "| ASIN| Product|AvgRating|ReviewCount|\n", "+----------+------------------------------------------------------------+---------+-----------+\n", "|038568231X| The Girl on the Train| 4.04| 58150|\n", "|0007420412| Divergent| 4.49| 44196|\n", "|0141353678| The Fault in Our Stars| 4.68| 37778|\n", "|0312577222| The Nightingale: A Novel| 4.78| 36620|\n", "|0099911701| Cross Stitch| 4.59| 33483|\n", "|0553418025| The Martian| 4.63| 30297|\n", "|B010OYASRG|OontZ Angle 3 Enhanced Stereo Edition IPX5 Splashproof Po...| 4.4| 28530|\n", "|0007548672| All the Light We Cannot See| 4.55| 27954|\n", "|B00FLYWNYQ|Instant Pot DUO60 6 Qt 7-in-1 Multi-Use Programmable Pres...| 4.59| 27595|\n", "|B00YSG2ZPA| Band of Brothers(Elite SC/BD+DCExp12-21)| 4.91| 24556|\n", "|B00006CXSS| | 4.91| 24487|\n", "|0439023521| The Hunger Games (Book 1)| 4.64| 22053|\n", "|8184776217| Mockingjay: The Hunger Games| 4.64| 22027|\n", "|0545582881| Harry Potter and the Sorcerer's Stone (Book 1)| 4.34| 21601|\n", "|B017WJ5PR4| Harry Potter and the Deathly Hallows, Book 7| 4.72| 21531|\n", "|B017V4IPPO| Harry Potter and the Chamber of Secrets, Book 2| 4.72| 21509|\n", "|B00AQVMZKQ| Downton Abbey - Series 4| 4.83| 21015|\n", "|B01BHTSIOC| Doctor Thorne - Season 1 2015| 4.42| 20889|\n", "|0143125478|The Boys in the Boat: Nine Americans and Their Epic Quest...| 4.75| 20649|\n", "|006195070X| Orphan Train: A Novel| 4.58| 20405|\n", "|1683247353| Beneath a Scarlet Sky (Center Point Large Print)| 4.77| 19208|\n", "|B000OX89XI|MidWest Homes for Pets Dog Crate | iCrate Single Door &am...| 4.58| 19206|\n", "|0345543246| Sycamore Row (The Jake Brigance)| 4.48| 19077|\n", "|0399159347| The Husband's Secret| 4.26| 18777|\n", "|B001C4VLZQ| Hobbit and Lord of the Rings Trilogy - Boxed Set of 4 Books| 4.68| 18619|\n", "|B000K5THYU|THE LORD OF THE RINGS Trilogy - (The Fellowship of the Ri...| 4.68| 18598|\n", "|B000FK9M18| Hobbit| 4.68| 18597|\n", "|000711835X| The Hobbit| 4.68| 18497|\n", "|B003156C4E| | 4.68| 18443|\n", "|1503943372| Beneath a Scarlet Sky: A Novel| 4.77| 18060|\n", "|037032921X| The BOOK THIEF| 4.62| 17663|\n", "|030788743X| Ready Player One| 4.56| 17129|\n", "|B00DIF2BO2| | 4.42| 17038|\n", "|6305837325| | 4.62| 16671|\n", "|0529120887| Jesus Calling: Enjoying Peace in His Presence| 4.85| 16658|\n", "|0340979496| The Shack| 4.41| 16394|\n", "|B000X1MX7E| | 4.05| 16248|\n", "|0143142372| The Pillars of the Earth| 4.05| 16234|\n", "|B006GWO5WK| | 4.31| 16129|\n", "|B003L1ZYYW| | 4.72| 16046|\n", "|B009HVH4XO| | 4.28| 15798|\n", "|1410472922| A Man Called Ove (Thorndike Press Large Print Core Series)| 4.62| 15786|\n", "|B00Q7EV29G| Zinus Memory Foam 8 Inch Green Tea Mattress, Queen| 4.16| 15696|\n", "|0399167064| Big Little Lies| 4.53| 15694|\n", "|0062082361|American Sniper: The Autobiography of the Most Lethal Sni...| 4.56| 15553|\n", "|B00P7EVST6|Arlo - Wireless Home Security Camera System | Indoor/Outd...| 4.27| 15451|\n", "|B00EINBSJ2|Coop Home Goods - PREMIUM Adjustable Loft - Shredded Hypo...| 4.34| 15443|\n", "|B00C2WDD5I| | 4.1| 15345|\n", "|B00WNBABVC| Walking Dead, The Ssn 5| 4.7| 15263|\n", "|B0019HL8Q8|Mediabridge HDMI Cable (10 Feet) Supports 4K@60Hz, High S...| 4.73| 15247|\n", "|0849946158|Heaven is for Real: A Little Boy's Astounding Story of Hi...| 4.53| 15144|\n", "|0439249546| Harry Potter (4 Volumes set)| 4.69| 14993|\n", "|B017S3OP7A| The Martian| 4.49| 14795|\n", "|B00BWF5U0M|Sabrent 4-Port USB 2.0 Hub with Individual Power Switches...| 4.37| 14671|\n", "|1101946342|Grey: Fifty Shades of Grey as Told by Christian (Fifty Sh...| 4.24| 14600|\n", "|B00YN6XHMU| | 4.24| 14568|\n", "|B009934S5M| Star Trek Into Darkness| 4.38| 14485|\n", "|B00ITIQI92|Fintie Slimshell Case for Kindle Paperwhite - Fits All Pa...| 4.61| 14413|\n", "|B00DRALJ28|AcuRite 00613 Humidity Monitor with Indoor Thermometer, D...| 4.2| 14331|\n", "|B00006IEEV|Prismacolor 3598T Premier Colored Pencils, Soft Core, 48 ...| 4.7| 14315|\n", "|B00IVPU7AO|Samsung EVO 64GB 48/MB/s Micro SDXC Memory Card with Ada...| 4.16| 14244|\n", "|B00FL31UF0| The Hunger Games: Catching Fire 2013| 4.39| 14174|\n", "|B00OGL6S64|The white queen Stagione 01The wh...| 4.56| 14143|\n", "|0316228532| The Casual Vacancy| 4.57| 14130|\n", "|B00V7T1YRQ|J5 Tactical V1-PRO Flashlight - The Original 300 Lumen Ul...| 4.46| 14073|\n", "|B00LV4W8BI|Hotel Luxury Bed Sheets Set TODAY! On Amazon Softest Bedd...| 4.27| 14032|\n", "|B0002ERXC2| The Wire: Season 1| 4.5| 14007|\n", "|B0019EHU8G|Mediabridge HDMI Cable (6 Feet) Supports 4K@60Hz, High Sp...| 4.72| 13999|\n", "|0552778478|The Light Between Oceans: The heartbreaking Richard and J...| 4.44| 13949|\n", "|B000BQ7GW8|SanDisk 2GB Class 4 SD Flash Memory Card- SDSDB-002G-B35 ...| 4.56| 13776|\n", "|1469984202| Wool - Omnibus Edition| 4.62| 13675|\n", "|B00R8GUXPG| | 4.62| 13649|\n", "|B00PY4Q9OS| Guardians of the Galaxy| 4.62| 13648|\n", "|B00Q0G2VXM| Guardians Of The Galaxy Region Free| 4.62| 13627|\n", "|1503934713| The Butterfly Garden (The Collector)| 4.38| 13602|\n", "|B00NX47YP4|Ozeri ZK14-R Pronto Digital Multifunction Kitchen and Foo...| 4.56| 13582|\n", "|B00MQSMDYU|Portable Chargers 16750 RAVPower 16750mAh External Batter...| 4.57| 13560|\n", "|0307943232| The Racketeer| 4.18| 13510|\n", "|0425266060| Bared to You / Reflected in You / Entwined with You| 4.25| 13438|\n", "|B000W5QSYA| | 4.46| 13380|\n", "|B005NF5NTK|Anker PowerCore+ Mini, 3350mAh Lipstick-Sized Portable Ch...| 4.37| 13246|\n", "|1477825576| My Sister's Grave (Tracy Crosswhite)| 4.47| 13245|\n", "|0307476073| Wild: From Lost to Found on the Pacific Crest Trail| 4.32| 13232|\n", "|B00M4NF9H0| TOMS Seasonal Classics Women's Slip on Shoes| 4.28| 13164|\n", "|0007378033|Unbroken: An Extraordinary True Story of Courage and Surv...| 4.79| 13098|\n", "|B00X5RV14Y|Anker PowerCore 20100 - Ultra High Capacity Power Bank wi...| 4.62| 13054|\n", "|B0043T7FXE|Logitech M570 Wireless Trackball Mouse – Ergonomic ...| 4.44| 13034|\n", "|0984502203| Yellow Crocus: A Novel| 4.64| 12996|\n", "|0091955106|The Life-Changing Magic of Tidying: A simple, effective w...| 4.38| 12975|\n", "|0375969020| Wonder| 4.85| 12712|\n", "|B00M55C0NS|SanDisk Ultra 32GB UHS-I/Class 10 Micro SDHC Memory Card ...| 4.62| 12651|\n", "|0141378247| Wonder| 4.85| 12645|\n", "|0802407692|The 5 Love Languages Military Edition: The Secret to Love...| 4.75| 12586|\n", "|0099664313| Drums of Autumn| 4.72| 12567|\n", "|B019D9HESO| RTIC 30 oz Stainless Steel Tumbler Cup w/ Splash Proof Lid| 4.5| 12457|\n", "|B00AZBIZTW| Bissell 9595A CleanView Bagless Vacuum with OnePass| 4.2| 12180|\n", "|0143121707| The Invention of Wings| 4.64| 12064|\n", "|B00DMCV7K0| | 4.64| 12061|\n", "|B0063FQREO| The Hunger Games (2 Disc) [Region 2] [UK Import]| 4.29| 12011|\n", "|B00E055H5O|(Old Model) WD My Passport Ultra 2 TB Portable External U...| 4.47| 12001|\n", "|1503935310| Everything We Keep: A Novel| 4.24| 12001|\n", "|B00AE9V3WQ|Aylio Comfort Seat Cushion - Tailbone, Coccyx, Sciatica, ...| 4.32| 11942|\n", "|B00SB123IM|By Marie Kondo The Life-Changing Magic of Tidying Up: The...| 4.37| 11825|\n", "|0316044695|Lone Survivor: The Eyewitness Account of Operation Redwin...| 4.69| 11813|\n", "|B000BQN9LA| The Original Elevated Pet Bed by Coolaroo| 4.35| 11802|\n", "|B000WGWQG8| John Adams| 4.69| 11797|\n", "|2253195103| 22/11/63| 4.53| 11695|\n", "|1410440478| | 4.53| 11675|\n", "|0385344430| Written in My Own Heart's Blood (Outlander)| 4.71| 11662|\n", "|0007155662| The Alchemist| 4.44| 11591|\n", "|0141192496| Oliver Twist (Penguin Clothbound Classics)| 4.44| 11505|\n", "|0007350899| | 4.44| 11503|\n", "|B00BUKL666|KIND Bars, Dark Chocolate Nuts & Sea Salt, Gluten Fre...| 4.51| 11400|\n", "|B00BP5KOPA|Logitech MK270 Wireless Keyboard and Mouse Combo - Keyboa...| 4.1| 11349|\n", "|B00F3EDEOC| Levi's Men's 505 Regular Fit Jean| 4.16| 11282|\n", "|B00GAPACC0| Levi's Men's 505 Regular Fit Jean| 4.16| 11255|\n", "|0307582884|The Maze Runner (Maze Runner, Book One) (The Maze Runner ...| 4.33| 11251|\n", "|B00543R3WG| Harry Potter - Complete 8-Film Collection| 4.36| 11249|\n", "|6077547328|Maze Runner. Correr o Morir / Maze Runner, Run or die (Sp...| 4.33| 11235|\n", "|B00QN1T6NM|Maxboost iPhone 6 Screen Protector, [3 Pack ] iPhone 6 Te...| 4.1| 11227|\n", "|0141027126| War Brides| 4.26| 11160|\n", "|B000HTICY0| Levi's Men's 505 Regular Fit-Jeans| 4.16| 11133|\n", "|B00BJLS55G|Stainless Steel Pet Id Tags, Personalized Dog Tags & ...| 4.78| 11111|\n", "|B009ZJ2M7G| Hoover Power Scrub Deluxe Carpet Washer FH50150| 4.31| 11094|\n", "|0451412656| AN Innocent Client (Joe Dillard)| 4.52| 11087|\n", "|B0046ZT40W| Downton Abbey| 4.86| 11055|\n", "|B00HUCF7AO| Orphan Black: Season 2| 4.78| 11008|\n", "|B00OV3VGP0| John Wick| 4.36| 10954|\n", "|0749958790|Proof of Heaven: A Neurosurgeon's Journey into the Afterlife| 4.2| 10942|\n", "|0143109464|Me Before You: A Novel (Movie Tie-In) (Me Before You Tril...| 4.62| 10874|\n", "|B00O4ZC57I| Big Hero 6| 4.79| 10846|\n", "|0141331976| Dog Days (Diary of a Wimpy Kid)| 4.63| 10793|\n", "|B014EB532U|iPhone 8 Plus, 7 Plus, 6S Plus, 6 Plus Screen Protector, ...| 4.2| 10730|\n", "|0805098542| Killing Jesus (Bill O'Reilly's Killing Series)| 4.4| 10699|\n", "|B0043WJRRS| MagicFiber Microfiber Cleaning Cloths, 30 PACK| 4.81| 10694|\n", "|B00902X68W|Clara Clark 1800 Premier Series 4pc Bed Sheet Set - King,...| 4.23| 10665|\n", "|080509668X|Killing Patton: The Strange Death of World War II's Most ...| 4.58| 10538|\n", "|B00CD8AFFW| AmazonBasics 14-Inch Laptop Sleeve - Black| 4.5| 10500|\n", "|B00SU5244M|Converse Unisex Chuck Taylor All Star Low Top Red Sneaker...| 4.38| 10467|\n", "|B00SU52460| Converse Chuck Taylor All Star Core Ox| 4.38| 10467|\n", "|B006W8U2MU| Kingston Digital DataTraveler SE9 8GB USB 2.0 DTSE9H/8GBZ| 4.55| 10386|\n", "|0718178130| The Rosie Project| 4.44| 10359|\n", "|B00X8AMIDG| Converse Chuck Taylor All Star Canvas Low Top Sneaker| 4.38| 10359|\n", "|B00WRGPHY4|Converse Unisex Chuck Taylor All Star Hi Top Sneaker Shoe...| 4.38| 10359|\n", "|B00WBHSMOQ|Converse Chuck Taylor All Star Low Top (International Ver...| 4.38| 10303|\n", "|0316206857| The Cuckoo's Calling (Cormoran Strike)| 4.03| 10283|\n", "|B00VJOT0CK|Converse Men's Chuck Taylor All Star Leather Low Top Sneaker| 4.38| 10272|\n", "|B00LCIYHSK|Grace Alley American Flag: American Made 3x5 FT US Flag M...| 4.89| 10264|\n", "|B00GV8I1BA| Converse Women's Chuck Taylor All Star Core Hi| 4.39| 10236|\n", "|B00COK3FD8|Rubbermaid Easy Find Lids Food Storage Containers, Racer ...| 4.55| 10221|\n", "|B00SB9Y6KW| American Sniper 2014| 4.58| 10150|\n", "|B00V6K7P0G| Converse C/T All Star Hi Little Kids Fashion Sneakers Black| 4.38| 10144|\n", "|B00AANQLRI|Jackery External Battery Charger Giant+ 12000mAh Dual USB...| 4.58| 10142|\n", "|B00UI91214|Converse Chuck Taylor All Star Low Top (International Ver...| 4.38| 10141|\n", "|B00UHAC5L0|Converse Chuck Taylor All Star Low Top Black MonochromeSn...| 4.38| 10140|\n", "|B00UFJS0I0|Converse Unisex Chuck Taylor All Star Ox Low Top Navy Sne...| 4.38| 10132|\n", "|B0002DHXX2| MidWest Deluxe Bolster Pet Bed for Dogs & Cats| 4.14| 10111|\n", "|B00IVM1TKO|NoCry Cut Resistant Gloves - High Performance Level 5 Pro...| 4.71| 10080|\n", "|B00005JLF2| 24: Season 1| 4.63| 10061|\n", "|0141039280| The Help| 4.68| 10000|\n", "|B00HFJWKWK|AmazonBasics 6-Sheet Cross-Cut Paper and Credit Card Shre...| 4.26| 9987|\n", "|B00NYC65M8| Jurassic World| 4.21| 9976|\n", "|1447287967| Memory Man (Amos Decker series)| 4.41| 9951|\n", "|1477820876| The Dead Key| 4.25| 9947|\n", "|B00GAO5KE6| Levi's Men's 501 Original-Fit Jean| 4.19| 9939|\n", "|B0002I0O5G|Outward Hound Hide-A-Squirrel Puzzle Plush Squeaking Toys...| 4.38| 9932|\n", "|0002247399| A Dance with Dragons| 4.03| 9921|\n", "|B0018OLONE| Levi's Men's 501 Original-Fit Jean| 4.2| 9911|\n", "|B001BIL3MY| Levi's Men's 501 Original-Fit Jean| 4.2| 9891|\n", "|B000YXC2LI| Levi's Men's 501 Original-Fit Jean| 4.2| 9851|\n", "|B0018OHL5E| Levi's Men's Big and Tall 501 Original Fit Jean| 4.2| 9841|\n", "|B00M0GMC26| Outlander - Season 1| 4.73| 9836|\n", "|B00STBRT9M|Converse Unisex Chuck Taylor All-Star High-Top Casual Sne...| 4.38| 9781|\n", "|006017322X| | 4.72| 9657|\n", "|B000CITK8S|Battery Tender 021-0123 Battery Tender Junior 12V, 0.75A ...| 4.66| 9653|\n", "|B007WQ9YNE|Aroma Housewares 8-Cup (Cooked) (4-Cup UNCOOKED) Digital...| 4.07| 9646|\n", "|0307341577| Dark Places| 4.11| 9494|\n", "|B0091YYUAM|BISSELL PowerFresh Steam Mop, Floor Steamer, Tile Cleaner...| 4.22| 9472|\n", "|0751549959| The Longest Ride| 4.6| 9466|\n", "|1442362383| Doctor Sleep: A Novel| 4.56| 9435|\n", "|B00JX1ZS5O|Sabrent 4-Port USB 3.0 Hub with Individual LED Power Swit...| 4.32| 9435|\n", "|B0142IHZPI| | 4.43| 9428|\n", "|B000HPV3RW|Belkin 8-Outlet Power Strip Surge Protector with 6-Foot P...| 4.57| 9416|\n", "|B00OD9JH6W| New Balance Men's Mx608v4| 4.42| 9410|\n", "|B006JH8T3S|Logitech HD Pro Webcam C920, Widescreen Video Calling and...| 4.42| 9396|\n", "|B000HCLLMM|Classic Accessories 73942 Veranda Grill Cover, X-Large, P...| 4.29| 9354|\n", "|B00VANO9OE|Nestl Bedding 4 Piece Sheet Set - 1800 Deep Pocket Bed Sh...| 4.37| 9350|\n", "|1433524724| ESV New Classic Reference Bible (Black)| 4.49| 9336|\n", "|B005DKZTMG|Logitech Wireless Touch Keyboard K400 with Built-In Multi...| 4.28| 9308|\n", "|B00A9WRH3M|Great Expectations By Charles Dickens - The Franklin Libr...| 4.45| 9296|\n", "|1503953386| When I'm Gone: A Novel| 4.43| 9284|\n", "|B00IYAG7DW| | 4.4| 9233|\n", "|B001892AX2|Himalayan Glow 1002 Pink Crystal Salt Lamp, (8-11 lbs), W...| 4.1| 9214|\n", "|B00B18FA50| Mr Selfridge: Season 1| 4.55| 9204|\n", "|B006X040NY| | 4.21| 9145|\n", "|B009SYZ8OC|AmazonBasics USB A to Lightning Compatible Cable (3 Feet/...| 4.22| 9144|\n", "|B00UMDF01Y| Orphan Black: Season 3| 4.76| 9085|\n", "|1611735270| | 4.56| 9078|\n", "|B00542YXFW| Davidson's Tea Bulk, Anise Seed, 16-Ounce Bag| 4.22| 9055|\n", "|B00VH88CJ0|Anker 24W Dual USB Car Charger, PowerDrive 2 for iPhone X...| 4.62| 9043|\n", "+----------+------------------------------------------------------------+---------+-----------+\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "from pyspark.sql import functions as F\n", "\n", "# --------------------------------------------------\n", "# 1️⃣ Graph Motif: (review)-[:reviews]->(product)\n", "# --------------------------------------------------\n", "\n", "reviews_motif = g.find(\"\"\"\n", " (review)-[e]->(product)\n", "\"\"\").filter(\n", " (F.col(\"e.relationship\") == \"reviews\") &\n", " (F.col(\"review.type\") == \"review\") &\n", " (F.col(\"product.type\") == \"product\") &\n", " (F.col(\"review.rating\").isNotNull())\n", ")\n", "\n", "# --------------------------------------------------\n", "# 2️⃣ Aggregate rating + review count per product\n", "# --------------------------------------------------\n", "\n", "product_stats = reviews_motif.groupBy(\n", " F.col(\"product.id\").alias(\"asin\"),\n", " F.col(\"product.title\").alias(\"title\")\n", ").agg(\n", " F.avg(\"review.rating\").alias(\"AvgRating\"),\n", " F.count(\"review.id\").alias(\"ReviewCount\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 3️⃣ Apply quality filters\n", "# --------------------------------------------------\n", "\n", "filtered_products = product_stats.filter(\n", " (F.col(\"ReviewCount\") >= 50) &\n", " (F.col(\"AvgRating\") >= 4.0)\n", ")\n", "\n", "# --------------------------------------------------\n", "# 4️⃣ Final formatting\n", "# --------------------------------------------------\n", "\n", "result_df = filtered_products.select(\n", " F.col(\"asin\").alias(\"ASIN\"),\n", " F.coalesce(F.col(\"title\"), F.col(\"asin\")).alias(\"Product\"),\n", " F.round(F.col(\"AvgRating\"), 2).alias(\"AvgRating\"),\n", " F.col(\"ReviewCount\")\n", ").orderBy(\n", " F.desc(\"ReviewCount\"),\n", " F.desc(\"AvgRating\")\n", ").limit(200)\n", "\n", "print(\"\\nTop 200 Highly Rated Products (Rating ≥ 4.0, Reviews ≥ 50)\\n\")\n", "measure(result_df).show(200, truncate=60)\n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "85625598-b591-4cf6-85fc-99cfe009fd57", "metadata": {}, "source": [ "## ===Reviewer===" ] }, { "attachments": {}, "cell_type": "markdown", "id": "4e31a0e2-cad8-4d06-a018-6ff51214953b", "metadata": {}, "source": [ "#### Most Active Reviewers\n", "#### Top 200 users by review count (power users, potential spam or influencers)." ] }, { "cell_type": "code", "execution_count": 28, "id": "2a57b82f-e860-4e79-a260-47aeaa11a0f2", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Top 200 Most Active Reviewers\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " 00]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Time taken: 58.817 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "[Stage 319:=================> (68 + 132) / 200]200]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+--------------+----------------------------------------------+-----------+\n", "| ReviewerID| ReviewerName|ReviewCount|\n", "+--------------+----------------------------------------------+-----------+\n", "|A328S9RN3U5M68| Grady Harp| 13314|\n", "|A2F6N60Z96CAJI| Bookreporter| 9158|\n", "|A2OJW07GQRNJUT| Steven H Propp| 8586|\n", "| AHUT55E980RDR| Neal Reynolds| 7410|\n", "|A1D2C0WDCSHUWZ| E. A. Solinas| 6980|\n", "| A9Q28YTLYREO7| MAXIMILLIAN MUHAMMAD| 6804|\n", "|A3MV1KKHX51FYT| Acute Observer| 5702|\n", "|A13QTZ8CIMHHG4| LAS Reviewer| 5315|\n", "|A1JLU5H1CCENWX| Heather- A Crazy Vermonter's Book Reviews| 5245|\n", "|A1JLU5H1CCENWX|Heather- A Crazy Vermonter's Book Reviews| 5245|\n", "|A1JLU5H1CCENWX| andrewsheath| 5245|\n", "|A2EDZH51XHFA9B| Robert Beveridge| 5053|\n", "| A320TMDV6KCFU| mnix| 4812|\n", "|A22RY8N8CNDF3A| Loyd Eskildson| 4725|\n", "|A2NJO6YE954DBH| Lawrance Bernabo| 4725|\n", "|A281NPSIMI1C2R| Rebecca of Amazon| 4555|\n", "|A1X8VZWTOG8IS6| average| 4478|\n", "|A16CZRQL23NOIW| Hiram Gmez Pardo Venezuela| 4451|\n", "|A2TX179XAT5GRP| TeensReadToo| 4345|\n", "| AV6QDP8Q0ONK4| The Movie Guy| 4309|\n", "|A1K1JW1C5CUSUZ| Donald Mitchell| 4199|\n", "|A2VKWLCNZF4ZVB| The RAWSISTAZ Reviewers| 4177|\n", "|A1GGOC9PVDXW7Z| Annie Van Auken| 4041|\n", "|A13G1TKIKHGV3F| M. Heiss| 3959|\n", "|A1M8PP7MLHNBQB| John Matlock| 3958|\n", "| AWG2O9C42XW5G| Eli Santana 2018| 3944|\n", "| AWG2O9C42XW5G| Oreo Cookie Lemon 2018| 3944|\n", "| AWG2O9C42XW5G| Oreo Cookie Kettle Corn 2018| 3944|\n", "| AWG2O9C42XW5G| Greg Imhoff 2018| 3944|\n", "| AWG2O9C42XW5G| Carl Wierzbicky 2018| 3944|\n", "| AWG2O9C42XW5G| Oreo Cookie Lemon Twist 2018| 3944|\n", "| AWG2O9C42XW5G| Oreo Cookie Hot And Spicy Cinnamon 2018| 3944|\n", "| AWG2O9C42XW5G| Blake Meahl From Huntress2018| 3944|\n", "| AWG2O9C42XW5G| Anthony Crocamo 2018| 3944|\n", "| AWG2O9C42XW5G| Blake Meahl 2018| 3944|\n", "| AWG2O9C42XW5G| Oreo Cookie Cherry Cola 2018| 3944|\n", "| AWG2O9C42XW5G| Oreo Cookie Mystery 2018| 3944|\n", "| AWG2O9C42XW5G| Oreo Cookie Chocolate Peanut Butter Pie 2018| 3944|\n", "| AWG2O9C42XW5G| Oreo Cookie Limeade 2018| 3944|\n", "| AWG2O9C42XW5G| Eric Harris 2018| 3944|\n", "| AWG2O9C42XW5G| Happy4thofjuly 2018| 3944|\n", "| AWG2O9C42XW5G| Oreocokiemarshmallowkrispy 2018| 3944|\n", "| AWG2O9C42XW5G| Oreo Mint Creme 2018| 3944|\n", "| AWG2O9C42XW5G| Oreo Cookie Mocha 2018| 3944|\n", "|A2AIMXT9PLAM12| Santa Fe Listener| 3900|\n", "|A37BRR2L8PX3R2| Jbarr5| 3870|\n", "|A1MC6BFHWY6WC3| D. Blankenship| 3860|\n", "|A2WQY1B8ZS7QRZ| Mike Reed| 3853|\n", "|A2NYK9KWFMJV4Y| Mike Tarrani| 3762|\n", "|A3W4D8XOGLWUN5| Michael Kerner| 3757|\n", "| ALYZJ7W14YS26| iiiireader| 3737|\n", "| ACJT8MUC0LRF0| Deb| 3701|\n", "| ACJT8MUC0LRF0| D. Fowler| 3701|\n", "|A1POFVVXUZR3IQ| Z Hayes| 3658|\n", "| AHD101501WCN1| Shalom Freedman| 3626|\n", "|A21NVBFIEQWDSG| Armchair Interviews| 3620|\n", "|A2KWQ64TRHB3YH| Israel Drazin| 3568|\n", "|A1NATT3PN24QWY| Rolf Dobelli| 3566|\n", "|A2582KMXLK2P06| Bryan| 3514|\n", "|A2OCDK0BOW6UCY| Antigone Walsh| 3469|\n", "|A20EEWWSFMZ1PN| Bernie| 3389|\n", "|A20EEWWSFMZ1PN| Bernie (xyzzy)| 3389|\n", "|A20EEWWSFMZ1PN| bernie (xyzzy)| 3389|\n", "| A1LACH6MLQWZ| Doug Erlandson| 3309|\n", "|A25KKXUQQ0OLWB| Lynn Ellingwood| 3300|\n", "|A26JGAM6GZMM4V| Robert Morris| 3284|\n", "|A1GQAKL9CGQLP1| L. M. Keefer| 3226|\n", "|A3GWE80SUGORJD| Bette Hansen| 3223|\n", "|A2U49LUUY4IKQQ| Peter Durward Harris| 3219|\n", "|A3OXHLG6DIBRW8| C. Hill| 3205|\n", "|A2AIE9DO5QSVBD| Pop Bop| 3202|\n", "| AIO247KHD2O40| James N Simpson| 3186|\n", "| AJKWF4W7QD4NS| N. Durham| 3177|\n", "| A680RUE1FDO8B| Jerry Saperstein| 3133|\n", "|A2Z3DQ7P9JYRLG| BernieKindle Customer| 3117|\n", "|A2Z3DQ7P9JYRLG| Bernie| 3117|\n", "| AQ4B80F0JSBNI| George O'Leary| 3106|\n", "| A3LXRIY0HE71K| D - Guilty Pleasures BR| 3076|\n", "|A14GK0E64J0WAS| Paul Allaer| 3027|\n", "| A96K1ZGW56S2I| tvtv3| 3012|\n", "|A1L43KWWR05PCS| lawyeraau| 2977|\n", "|A2V92F5R7MLCVI| wogan| 2963|\n", "|A2TXR85WQLE32N| HMS Warspite| 2952|\n", "|A1TPW86OHXTXFC| prisrob| 2933|\n", "|A2R6RA8FRBS608| Matthew G. Sherwin| 2901|\n", "|A1I6U5AQAT3ZG9| E. B. MULLIGAN| 2898|\n", "|A3LZGLA88K0LA0| Michael Butts| 2895|\n", "| AUTBHG6070SL4| wiredweird| 2887|\n", "|A11OTLEDSW8ZXD| Connie (She who hikes with dogs)| 2842|\n", "|A13OFOB1394G31| ealovitt| 2839|\n", "|A2503LT8PZIHAD| Trouble| 2830|\n", "|A27R2V3WLS43LP| Cheryl| 2823|\n", "|A22B13EJTZKCTM| Karissa Eckert| 2804|\n", "|A3TI1WDAEOTD0I| Cyrus Webb| 2798|\n", "|A2VXSQHJWZAQGY| Sandy Milan| 2798|\n", "|A2I6MHMAZZDCRX| Mark Baker - Carstairs Considers| 2790|\n", "|A2HVL790PBWYTU| H. Bala| 2725|\n", "|A17BTP1QHK2I3I| Clare O'Beara| 2700|\n", "|A17BTP1QHK2I3I| Clare O'Beara| 2700|\n", "| AVU1ILDDYW301| G. Hearn| 2665|\n", "|A1JKGTL51HHTU1| Under The Covers Book Blog| 2661|\n", "|A3L1Z0R3ZB2OFU| Janet| 2660|\n", "| AW4NFCPN81CS3| Sandy S.| 2640|\n", "|A3HU0B9XUEVHIM| Andre S. Grindle| 2635|\n", "|A3JJ21YCMGSKGH| Amazon Customer| 2629|\n", "|A3CN9CCJUNIPKT| DJ Joe Sixpack| 2628|\n", "|A1IU7S4HCK1XK0| Joanna D.| 2617|\n", "|A1R0798K5DDQZP| Richard and Liz| 2606|\n", "|A22CW0ZHY3NJH8| ()| 2601|\n", "|A14I311IIUMBWC| Gaele| 2591|\n", "|A1J6UA1MD96SKE| kathleen g| 2586|\n", "|A3NHUQ33CFH3VM| Greeny| 2551|\n", "|A2H44WVZS59KKT| Cheryl Stout| 2547|\n", "|A244C0ORREA3N5| Isha Coleman| 2546|\n", "| AKMEY1BSHSDG7| J. Arena| 2545|\n", "|A1E481YU3ZPK6S| Elisa| 2542|\n", "| ACEOOCYZRPWF3| Nicola Mansfield| 2536|\n", "|A26J2TRDPZH7RZ| Romancing the Book| 2529|\n", "|A303U1MOXG5R1F| Sylviastel| 2512|\n", "|A2WW57XX2UVLM6| Talvi| 2511|\n", "|A16QODENBJVUI1| Robert Moore| 2506|\n", "|A1I2O9Y3X3HXLS| Arthur W Jordin| 2488|\n", "| AIEEK7AHXKZCC| BeatleBangs1964| 2468|\n", "|A2D1LPEUCTNT8X| Ali Julia| 2460|\n", "|A3MT311233OHMQ| Sophia Rose| 2432|\n", "| AQQLWCMRNDFGI| Steven Peterson| 2431|\n", "|A2GBJQ9THOYDAJ| Michael K. Smith| 2420|\n", "| AQ6AGWKWMTY8H| Pam| 2419|\n", "| AG35NEEFCMQVR| W Boudville| 2398|\n", "| AC1K4OQOZ90RS| booklover10| 2391|\n", "|A2ZY49IDE6TY5I| R PRIUS| 2366|\n", "|A2SY92ZXU2FOEU| sanoe.net| 2364|\n", "| ANCOMAI0I7LVG| Andrew Ellington| 2364|\n", "|A2PR6NXG0PA3KY| Gio| 2360|\n", "| AN3MHNH3C8FJE| Mike O'Connor| 2355|\n", "| AKPKPMWM6IIU5| Stephen T. Hopkins| 2342|\n", "|A1WVA7V02PQOY6| Dad of Divas| 2342|\n", "| AQP1VPK16SVWM| Wayne Klein| 2327|\n", "|A3A44JIMIWYFIE| Poppy| 2326|\n", "|A1JZFGZEZVWQPY| Professor H| 2322|\n", "| ABO2ZI2Y5DQ9T| T.NAKAJIMA| 2320|\n", "|A20DZX38KRBIT8| Deimos| 2313|\n", "| ATFRPYKXCCTJX| Lollipops| 2306|\n", "|A3R19YKNL641X3| Thomas Duff| 2302|\n", "| A6FIAB28IS79| Samuel C.| 2295|\n", "|A3R7MXVQRGGIQ9| Joseph J. Truncale| 2280|\n", "|A1CNQTCRQ35IMM| Jane| 2280|\n", "|A3KF4IP2MUS8QQ| Daniel Jolley| 2271|\n", "| ANKDQ12BPX6Z3| GH| 2266|\n", "|A1JMSX54DO3LOP| Bookzilla| 2259|\n", "|A1X1CEGHTHMBL1| JJCEO| 2255|\n", "|A28D20IM3BNAJ1| P.S Winn| 2252|\n", "|A3U3PA6J2XOVFK| Donald E. Gilliland| 2252|\n", "|A28D20IM3BNAJ1| P.S. Winn| 2252|\n", "| AQZH7YTWQPOBE| Arnold| 2241|\n", "|A2A7NHE5HTK79N| J. Lovins| 2227|\n", "|A1PGC1GEBY9CZU| By CJs Pirate| 2225|\n", "| A7Y6AVS576M03| gobirds2| 2224|\n", "|A1F89OE7BAM7EL| Ralph Moore| 2219|\n", "| AXIO51VZN815F| Don Kidwell| 2219|\n", "|A1D5RCOILPC9LX| Yolanda S. Bean| 2218|\n", "|A2QDOJFFLFGF18| michael alexander| 2215|\n", "|A231LBC8EGPO5L| OutlawPoet| 2213|\n", "|A2W4Z0J9DFZFSR| Angela| 2213|\n", "|A2W4Z0J9DFZFSR| Sunny| 2213|\n", "|A21VR7M8O55EF6| Robin Friedman| 2200|\n", "|A20IIR0422G3A5| B. Marold| 2195|\n", "|A319KYEIAZ3SON| Mary Whipple| 2188|\n", "|A23GRXCXQU3SM8| Smitten with Reading| 2178|\n", "|A3JVE1D10L1CFA| Joan N.| 2172|\n", "|A18OBUSMXVE8R0| Jerry Guild| 2170|\n", "|A39N24TTLP6I48| Jane Jones | 2166|\n", "|A2H3JURQZOHVMB| Stella Carrier| 2162|\n", "|A2RYWPOL4NN2KG| Raed Haed| 2137|\n", "| AMKWIQ2MT2V5R| Angel Hatfield| 2130|\n", "| ASEBX8TBYWQWA| Steve Ramm| 2114|\n", "|A2S3CQ90I4DQVX| Lupine Smile| 2110|\n", "|A3KJ6JAZPH382D| Tim Brough| 2108|\n", "|A1D7CDK7HS68NP| OpenBookSociety dot com| 2105|\n", "|A3U7ELIED4WP4R| Dark Faerie Tales| 2095|\n", "|A2PD27UKAD3Q00| Wilhelmina Zeitgeist| 2094|\n", "|A2R1HUYHXV7H18| Bugs| 2094|\n", "|A3QS1EPDZTLPWS| scoundrel| 2090|\n", "|A2TSTJP0B3A3H5| J.Prather| 2085|\n", "|A1RAUVCWYHTQI4| A. Ross| 2077|\n", "|A2UVDPBRZTWN6S| PattyLouise| 2071|\n", "|A2YKWYC3WQJX5J| ShannonOnTheLakes| 2070|\n", "|A2WY52YD5GAEIB| LAURI CRUMLEY COATES| 2066|\n", "|A2UBDYN21RI97X| Kindle Customer| 2065|\n", "| AUEY946M1L939| John L Murphy| 2064|\n", "| AMX0PJKV4PPNJ| E. R. Bird| 2060|\n", "| AFVZXHIUSXINA| Teen Reads| 2055|\n", "| A2C27IQUH9N1Z| audrey frances| 2049|\n", "|A1K4S4MWXI9E9M| Tina| 2047|\n", "|A3NIQK6ZLYEP1L| Michael Valdivielso| 2047|\n", "|A1EVV74UQYVKRY| K. Groh| 2047|\n", "|A26GE9ZG68SKIQ| Amazon Customer| 2046|\n", "| AYUTCGVSM1H7T| DEHSKindle Customer| 2038|\n", "| AYUTCGVSM1H7T| DEHS - EddyRapcon| 2038|\n", "| AYUTCGVSM1H7T| Don & Sheila| 2038|\n", "+--------------+----------------------------------------------+-----------+\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "from pyspark.sql import functions as F\n", "\n", "# --------------------------------------------------\n", "# 1️⃣ Graph Motif: (reviewer)-[:wrote]->(review)\n", "# --------------------------------------------------\n", "\n", "wrote_motif = g.find(\"\"\"\n", " (reviewer)-[e]->(review)\n", "\"\"\").filter(\n", " (F.col(\"e.relationship\") == \"wrote\") &\n", " (F.col(\"reviewer.type\") == \"reviewer\") &\n", " (F.col(\"review.type\") == \"review\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 2️⃣ Count reviews per reviewer\n", "# --------------------------------------------------\n", "\n", "review_counts = wrote_motif.groupBy(\n", " F.col(\"reviewer.id\").alias(\"reviewerID\"),\n", " F.col(\"reviewer.name\").alias(\"name\")\n", ").agg(\n", " F.count(\"review.id\").alias(\"ReviewCount\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 3️⃣ Order and limit\n", "# --------------------------------------------------\n", "\n", "result_df = review_counts.select(\n", " F.col(\"reviewerID\").alias(\"ReviewerID\"),\n", " F.coalesce(F.col(\"name\"), F.col(\"reviewerID\")).alias(\"ReviewerName\"),\n", " F.col(\"ReviewCount\")\n", ").orderBy(\n", " F.desc(\"ReviewCount\")\n", ").limit(200)\n", "\n", "print(\"\\nTop 200 Most Active Reviewers\\n\")\n", "measure(result_df).show(200, truncate=60)\n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "91feb646-8aea-40cb-9472-553a5396a531", "metadata": {}, "source": [ "#### Reviewers Who Write the Longest Reviews\n", "Order the 200 reviewers from the previous query by their average review length in descending order, returning each reviewer's ID, name, total number of reviews, and their average review length." ] }, { "cell_type": "code", "execution_count": 29, "id": "a87522e7-6a80-40e9-895f-11c70a6c86bf", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Reviewers Who Write the Longest Reviews\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " ]00]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Time taken: 106.748 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " ]]]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+--------------+----------------------------------------------+-----------+---------------+\n", "| ReviewerID| ReviewerName|ReviewCount|AvgReviewLength|\n", "+--------------+----------------------------------------------+-----------+---------------+\n", "| AMX0PJKV4PPNJ| E. R. Bird| 2060| 5106.74|\n", "|A2OJW07GQRNJUT| Steven H Propp| 8586| 4957.37|\n", "|A21VR7M8O55EF6| Robin Friedman| 2200| 4502.68|\n", "|A2HVL790PBWYTU| H. Bala| 2725| 4362.56|\n", "|A1I2O9Y3X3HXLS| Arthur W Jordin| 2488| 4358.31|\n", "|A3U7ELIED4WP4R| Dark Faerie Tales| 2095| 4079.7|\n", "|A26JGAM6GZMM4V| Robert Morris| 3284| 3682.58|\n", "| AUEY946M1L939| John L Murphy| 2064| 3681.6|\n", "|A2F6N60Z96CAJI| Bookreporter| 9158| 3539.32|\n", "|A3MT311233OHMQ| Sophia Rose| 2432| 3329.09|\n", "|A2A7NHE5HTK79N| J. Lovins| 2227| 3265.91|\n", "|A3KF4IP2MUS8QQ| Daniel Jolley| 2271| 3226.91|\n", "|A16QODENBJVUI1| Robert Moore| 2506| 3198.87|\n", "|A20IIR0422G3A5| B. Marold| 2195| 3168.41|\n", "|A2NJO6YE954DBH| Lawrance Bernabo| 4725| 3051.48|\n", "|A1D2C0WDCSHUWZ| E. A. Solinas| 6980| 3007.58|\n", "|A3MV1KKHX51FYT| Acute Observer| 5702| 2935.23|\n", "|A1D7CDK7HS68NP| OpenBookSociety dot com| 2105| 2914.18|\n", "| AQ4B80F0JSBNI| George O'Leary| 3106| 2884.77|\n", "| ANCOMAI0I7LVG| Andrew Ellington| 2364| 2872.53|\n", "|A2582KMXLK2P06| Bryan| 3514| 2823.82|\n", "|A1K1JW1C5CUSUZ| Donald Mitchell| 4199| 2800.01|\n", "| AFVZXHIUSXINA| Teen Reads| 2055| 2793.3|\n", "|A3HU0B9XUEVHIM| Andre S. Grindle| 2635| 2749.35|\n", "|A319KYEIAZ3SON| Mary Whipple| 2188| 2708.87|\n", "|A2PR6NXG0PA3KY| Gio| 2360| 2671.91|\n", "|A22RY8N8CNDF3A| Loyd Eskildson| 4725| 2662.34|\n", "| AW4NFCPN81CS3| Sandy S.| 2640| 2623.83|\n", "| A6FIAB28IS79| Samuel C.| 2295| 2582.17|\n", "|A1F89OE7BAM7EL| Ralph Moore| 2219| 2552.7|\n", "| ACJT8MUC0LRF0| D. Fowler| 14804| 2543.3|\n", "| ACJT8MUC0LRF0| Deb| 14804| 2543.3|\n", "| AQP1VPK16SVWM| Wayne Klein| 2327| 2459.74|\n", "|A2R6RA8FRBS608| Matthew G. Sherwin| 2901| 2401.1|\n", "|A22B13EJTZKCTM| Karissa Eckert| 2804| 2399.93|\n", "| AC1K4OQOZ90RS| booklover10| 2391| 2313.41|\n", "|A328S9RN3U5M68| Grady Harp| 13314| 2275.95|\n", "|A14I311IIUMBWC| Gaele| 2591| 2263.22|\n", "|A3R19YKNL641X3| Thomas Duff| 2302| 2234.64|\n", "|A23GRXCXQU3SM8| Smitten with Reading| 2178| 2229.47|\n", "|A13QTZ8CIMHHG4| LAS Reviewer| 5315| 2225.03|\n", "| A3LXRIY0HE71K| D - Guilty Pleasures BR| 3076| 2157.28|\n", "|A1RAUVCWYHTQI4| A. Ross| 2077| 2152.47|\n", "|A2I6MHMAZZDCRX| Mark Baker - Carstairs Considers| 2790| 2144.22|\n", "|A1JKGTL51HHTU1| Under The Covers Book Blog| 2661| 2120.16|\n", "|A2EDZH51XHFA9B| Robert Beveridge| 5053| 2100.08|\n", "|A1MC6BFHWY6WC3| D. Blankenship| 3860| 2091.84|\n", "|A1CNQTCRQ35IMM| Jane| 2280| 2083.33|\n", "|A1L43KWWR05PCS| lawyeraau| 2977| 2068.04|\n", "|A2GBJQ9THOYDAJ| Michael K. Smith| 2420| 2026.05|\n", "|A3JVE1D10L1CFA| Joan N.| 2172| 2014.2|\n", "|A1GGOC9PVDXW7Z| Annie Van Auken| 4041| 2002.68|\n", "| A320TMDV6KCFU| mnix| 4812| 1993.69|\n", "| AIO247KHD2O40| James N Simpson| 3186| 1988.58|\n", "|A14GK0E64J0WAS| Paul Allaer| 3027| 1976.28|\n", "|A13OFOB1394G31| ealovitt| 2839| 1974.11|\n", "| ABO2ZI2Y5DQ9T| T.NAKAJIMA| 2320| 1931.06|\n", "|A2AIMXT9PLAM12| Santa Fe Listener| 3900| 1921.75|\n", "|A26J2TRDPZH7RZ| Romancing the Book| 2529| 1910.88|\n", "| ASEBX8TBYWQWA| Steve Ramm| 2114| 1903.85|\n", "|A2U49LUUY4IKQQ| Peter Durward Harris| 3219| 1880.1|\n", "|A1X1CEGHTHMBL1| JJCEO| 2255| 1876.29|\n", "|A2NYK9KWFMJV4Y| Mike Tarrani| 3762| 1864.85|\n", "|A18OBUSMXVE8R0| Jerry Guild| 2170| 1842.91|\n", "|A3KJ6JAZPH382D| Tim Brough| 2108| 1833.4|\n", "|A2AIE9DO5QSVBD| Pop Bop| 3202| 1786.96|\n", "| A680RUE1FDO8B| Jerry Saperstein| 3133| 1757.26|\n", "| AIEEK7AHXKZCC| BeatleBangs1964| 2468| 1742.76|\n", "|A2WW57XX2UVLM6| Talvi| 2511| 1734.83|\n", "|A21NVBFIEQWDSG| Armchair Interviews| 3620| 1714.16|\n", "|A2TX179XAT5GRP| TeensReadToo| 4345| 1708.36|\n", "|A2VKWLCNZF4ZVB| The RAWSISTAZ Reviewers| 4177| 1668.23|\n", "| AQQLWCMRNDFGI| Steven Peterson| 2431| 1662.35|\n", "|A281NPSIMI1C2R| Rebecca of Amazon| 4555| 1657.46|\n", "|A2D1LPEUCTNT8X| Ali Julia| 2460| 1622.11|\n", "|A2KWQ64TRHB3YH| Israel Drazin| 3568| 1589.54|\n", "| ALYZJ7W14YS26| iiiireader| 3737| 1564.07|\n", "| A96K1ZGW56S2I| tvtv3| 3012| 1562.89|\n", "|A1POFVVXUZR3IQ| Z Hayes| 3658| 1548.47|\n", "| AN3MHNH3C8FJE| Mike O'Connor| 2355| 1546.42|\n", "|A1TPW86OHXTXFC| prisrob| 2933| 1518.63|\n", "|A2UBDYN21RI97X| Kindle Customer| 2065| 1512.71|\n", "|A17BTP1QHK2I3I| Clare O'Beara| 10800| 1502.31|\n", "|A17BTP1QHK2I3I| Clare O'Beara| 10800| 1502.31|\n", "| ACEOOCYZRPWF3| Nicola Mansfield| 2536| 1448.78|\n", "| AMKWIQ2MT2V5R| Angel Hatfield| 2130| 1445.82|\n", "| AUTBHG6070SL4| wiredweird| 2887| 1430.51|\n", "|A1E481YU3ZPK6S| Elisa| 2542| 1429.24|\n", "|A2TXR85WQLE32N| HMS Warspite| 2952| 1402.74|\n", "|A3R7MXVQRGGIQ9| Joseph J. Truncale| 2280| 1368.98|\n", "|A1IU7S4HCK1XK0| Joanna D.| 2617| 1366.2|\n", "| AHD101501WCN1| Shalom Freedman| 3626| 1362.56|\n", "|A303U1MOXG5R1F| Sylviastel| 2512| 1354.06|\n", "|A2TSTJP0B3A3H5| J.Prather| 2085| 1323.83|\n", "| AG35NEEFCMQVR| W Boudville| 2398| 1313.03|\n", "| AQZH7YTWQPOBE| Arnold| 2241| 1309.51|\n", "|A2ZY49IDE6TY5I| R PRIUS| 2366| 1301.59|\n", "|A11OTLEDSW8ZXD| Connie (She who hikes with dogs)| 2842| 1295.17|\n", "|A1I6U5AQAT3ZG9| E. B. MULLIGAN| 2898| 1275.17|\n", "|A2R1HUYHXV7H18| Bugs| 2094| 1260.3|\n", "| AQ6AGWKWMTY8H| Pam| 2419| 1256.57|\n", "|A3W4D8XOGLWUN5| Michael Kerner| 3757| 1237.11|\n", "| ATFRPYKXCCTJX| Lollipops| 2306| 1220.37|\n", "| AJKWF4W7QD4NS| N. Durham| 3177| 1215.6|\n", "|A2UVDPBRZTWN6S| PattyLouise| 2071| 1207.17|\n", "|A27R2V3WLS43LP| Cheryl| 2823| 1173.86|\n", "|A1R0798K5DDQZP| Richard and Liz| 2606| 1167.84|\n", "| A2C27IQUH9N1Z| audrey frances| 2049| 1140.07|\n", "|A2H44WVZS59KKT| Cheryl Stout| 2547| 1133.14|\n", "| A7Y6AVS576M03| gobirds2| 2224| 1121.42|\n", "|A3U3PA6J2XOVFK| Donald E. Gilliland| 2252| 1116.24|\n", "|A3CN9CCJUNIPKT| DJ Joe Sixpack| 2628| 1115.09|\n", "|A1D5RCOILPC9LX| Yolanda S. Bean| 2218| 1110.68|\n", "|A20EEWWSFMZ1PN| Bernie| 30501| 1101.63|\n", "|A20EEWWSFMZ1PN| bernie (xyzzy)| 30501| 1101.63|\n", "|A20EEWWSFMZ1PN| Bernie (xyzzy)| 30501| 1101.63|\n", "|A1GQAKL9CGQLP1| L. M. Keefer| 3226| 1098.51|\n", "|A1M8PP7MLHNBQB| John Matlock| 3958| 1098.39|\n", "|A2S3CQ90I4DQVX| Lupine Smile| 2110| 1097.11|\n", "|A3TI1WDAEOTD0I| Cyrus Webb| 2798| 1079.95|\n", "|A2V92F5R7MLCVI| wogan| 2963| 1049.81|\n", "| AV6QDP8Q0ONK4| The Movie Guy| 4309| 1003.97|\n", "|A3LZGLA88K0LA0| Michael Butts| 2895| 997.31|\n", "|A22CW0ZHY3NJH8| ()| 2601| 984.46|\n", "| A1LACH6MLQWZ| Doug Erlandson| 3309| 972.87|\n", "|A2H3JURQZOHVMB| Stella Carrier| 2162| 962.94|\n", "|A2VXSQHJWZAQGY| Sandy Milan| 2798| 961.44|\n", "|A2SY92ZXU2FOEU| sanoe.net| 2364| 960.95|\n", "|A16CZRQL23NOIW| Hiram Gmez Pardo Venezuela| 4451| 955.84|\n", "|A1NATT3PN24QWY| Rolf Dobelli| 3566| 918.32|\n", "|A231LBC8EGPO5L| OutlawPoet| 2213| 914.69|\n", "|A2OCDK0BOW6UCY| Antigone Walsh| 3469| 886.48|\n", "|A3NHUQ33CFH3VM| Greeny| 2551| 876.46|\n", "|A3NIQK6ZLYEP1L| Michael Valdivielso| 2047| 872.83|\n", "|A1EVV74UQYVKRY| K. Groh| 2047| 863.48|\n", "|A37BRR2L8PX3R2| Jbarr5| 3870| 856.08|\n", "|A2PD27UKAD3Q00| Wilhelmina Zeitgeist| 2094| 833.92|\n", "|A3OXHLG6DIBRW8| C. Hill| 3205| 826.43|\n", "|A3QS1EPDZTLPWS| scoundrel| 2090| 794.73|\n", "|A2503LT8PZIHAD| Trouble| 2830| 792.36|\n", "|A1WVA7V02PQOY6| Dad of Divas| 2342| 763.73|\n", "| AKPKPMWM6IIU5| Stephen T. Hopkins| 2342| 752.88|\n", "|A2WY52YD5GAEIB| LAURI CRUMLEY COATES| 2066| 742.92|\n", "|A2WQY1B8ZS7QRZ| Mike Reed| 3853| 732.73|\n", "|A1JLU5H1CCENWX| Heather- A Crazy Vermonter's Book Reviews| 47205| 731.6|\n", "|A1JLU5H1CCENWX| andrewsheath| 47205| 731.6|\n", "|A1JLU5H1CCENWX|Heather- A Crazy Vermonter's Book Reviews| 47205| 731.6|\n", "|A2YKWYC3WQJX5J| ShannonOnTheLakes| 2070| 731.5|\n", "|A1X8VZWTOG8IS6| average| 4478| 720.85|\n", "| AVU1ILDDYW301| G. Hearn| 2665| 717.59|\n", "|A39N24TTLP6I48| Jane Jones | 2166| 712.28|\n", "|A2RYWPOL4NN2KG| Raed Haed| 2137| 686.1|\n", "|A3L1Z0R3ZB2OFU| Janet| 2660| 656.82|\n", "|A13G1TKIKHGV3F| M. Heiss| 3959| 636.41|\n", "|A1PGC1GEBY9CZU| By CJs Pirate| 2225| 635.94|\n", "|A1JZFGZEZVWQPY| Professor H| 2322| 607.21|\n", "|A244C0ORREA3N5| Isha Coleman| 2546| 596.36|\n", "| AXIO51VZN815F| Don Kidwell| 2219| 592.57|\n", "|A25KKXUQQ0OLWB| Lynn Ellingwood| 3300| 570.55|\n", "| AKMEY1BSHSDG7| J. Arena| 2545| 556.78|\n", "|A3GWE80SUGORJD| Bette Hansen| 3223| 538.92|\n", "|A1K4S4MWXI9E9M| Tina| 2047| 504.7|\n", "|A2W4Z0J9DFZFSR| Sunny| 8852| 504.23|\n", "|A2W4Z0J9DFZFSR| Angela| 8852| 504.23|\n", "| AHUT55E980RDR| Neal Reynolds| 7410| 496.68|\n", "|A1JMSX54DO3LOP| Bookzilla| 2259| 495.52|\n", "|A1J6UA1MD96SKE| kathleen g| 2586| 479.17|\n", "|A28D20IM3BNAJ1| P.S. Winn| 9008| 459.94|\n", "|A28D20IM3BNAJ1| P.S Winn| 9008| 459.94|\n", "| A9Q28YTLYREO7| MAXIMILLIAN MUHAMMAD| 6804| 390.78|\n", "| AYUTCGVSM1H7T| DEHSKindle Customer| 18342| 309.03|\n", "| AYUTCGVSM1H7T| Don & Sheila| 18342| 309.03|\n", "| AYUTCGVSM1H7T| DEHS - EddyRapcon| 18342| 309.03|\n", "|A2QDOJFFLFGF18| michael alexander| 2215| 300.18|\n", "|A20DZX38KRBIT8| Deimos| 2313| 299.49|\n", "| AWG2O9C42XW5G| Carl Wierzbicky 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Eric Harris 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Oreo Cookie Lemon 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Happy4thofjuly 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Oreo Cookie Limeade 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Oreo Cookie Mystery 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Blake Meahl 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Oreo Cookie Chocolate Peanut Butter Pie 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Blake Meahl From Huntress2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Oreo Cookie Kettle Corn 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Oreo Cookie Lemon Twist 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Greg Imhoff 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Oreo Cookie Mocha 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Oreo Cookie Hot And Spicy Cinnamon 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Eli Santana 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Oreo Cookie Cherry Cola 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Oreo Mint Creme 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Oreocokiemarshmallowkrispy 2018| 1423784| 291.97|\n", "| AWG2O9C42XW5G| Anthony Crocamo 2018| 1423784| 291.97|\n", "|A3JJ21YCMGSKGH| Amazon Customer| 2629| 278.21|\n", "|A3A44JIMIWYFIE| Poppy| 2326| 210.66|\n", "|A2Z3DQ7P9JYRLG| BernieKindle Customer| 12468| 144.65|\n", "|A2Z3DQ7P9JYRLG| Bernie| 12468| 144.65|\n", "|A26GE9ZG68SKIQ| Amazon Customer| 2046| 123.82|\n", "| ANKDQ12BPX6Z3| GH| 2266| 112.7|\n", "+--------------+----------------------------------------------+-----------+---------------+\n", "\n" ] } ], "source": [ "from pyspark.sql import functions as F\n", "\n", "# --------------------------------------------------\n", "# 1️⃣ Graph Motif: (reviewer)-[:wrote]->(review)\n", "# --------------------------------------------------\n", "\n", "wrote_motif = g.find(\"\"\"\n", " (reviewer)-[e]->(review)\n", "\"\"\").filter(\n", " (F.col(\"e.relationship\") == \"wrote\") &\n", " (F.col(\"reviewer.type\") == \"reviewer\") &\n", " (F.col(\"review.type\") == \"review\") &\n", " (F.col(\"review.reviewText\").isNotNull())\n", ")\n", "\n", "# --------------------------------------------------\n", "# 2️⃣ Compute review count per reviewer (Top 200)\n", "# --------------------------------------------------\n", "\n", "review_counts = wrote_motif.groupBy(\n", " F.col(\"reviewer.id\").alias(\"reviewerID\"),\n", " F.col(\"reviewer.name\").alias(\"name\")\n", ").agg(\n", " F.count(\"review.id\").alias(\"ReviewCount\")\n", ")\n", "\n", "top_200_reviewers = review_counts.orderBy(\n", " F.desc(\"ReviewCount\")\n", ").limit(200)\n", "\n", "# --------------------------------------------------\n", "# 3️⃣ Compute average review length\n", "# --------------------------------------------------\n", "\n", "review_lengths = wrote_motif.select(\n", " F.col(\"reviewer.id\").alias(\"reviewerID\"),\n", " F.length(F.col(\"review.reviewText\")).alias(\"review_length\")\n", ")\n", "\n", "# Restrict to top 200 reviewers\n", "review_lengths_top200 = review_lengths.join(\n", " top_200_reviewers.select(\"reviewerID\"),\n", " \"reviewerID\",\n", " \"inner\"\n", ")\n", "\n", "# --------------------------------------------------\n", "# 4️⃣ Aggregate final stats\n", "# --------------------------------------------------\n", "\n", "final_stats = review_lengths_top200.groupBy(\"reviewerID\").agg(\n", " F.count(\"*\").alias(\"ReviewCount\"),\n", " F.avg(\"review_length\").alias(\"AvgReviewLength\")\n", ")\n", "\n", "# Join back reviewer names\n", "result_df = final_stats.join(\n", " top_200_reviewers.select(\"reviewerID\", \"name\"),\n", " \"reviewerID\",\n", " \"inner\"\n", ").select(\n", " F.col(\"reviewerID\").alias(\"ReviewerID\"),\n", " F.coalesce(F.col(\"name\"), F.col(\"reviewerID\")).alias(\"ReviewerName\"),\n", " F.col(\"ReviewCount\"),\n", " F.round(F.col(\"AvgReviewLength\"), 2).alias(\"AvgReviewLength\")\n", ").orderBy(\n", " F.desc(\"AvgReviewLength\")\n", ").limit(200)\n", "\n", "print(\"\\nReviewers Who Write the Longest Reviews\\n\")\n", "measure(result_df).show(200, truncate=60)\n" ] }, { "cell_type": "markdown", "id": "81947a85-dc62-483f-ae0a-24a26476ce16", "metadata": {}, "source": [ "### ===Product Associations===\n", "\n", "#### People Who Bought This Also Bought (Seed Expansion)" ] }, { "cell_type": "code", "execution_count": 31, "id": "d13e7145-2fc7-406d-b87a-e0b54a6c1192", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "People Who Bought This Also Bought\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " 200]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Time taken: 80.859 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "[Stage 494:==================================================> (191 + 9) / 200]200]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------+------------------------------------------------------------+-----------+\n", "| ASIN| CoBuyProduct|LinkedSeeds|\n", "+----------+------------------------------------------------------------+-----------+\n", "|0735219095| Where the Crawdads Sing| 32|\n", "|0062678418| The Woman in the Window: A Novel| 24|\n", "|0399590501| Educated: A Memoir| 23|\n", "|0425284689| Before We Were Yours: A Novel| 21|\n", "|1524763136| | 21|\n", "|0375842209| | 20|\n", "|0399563091| | 20|\n", "|1250080401| | 20|\n", "|0735220697| | 20|\n", "|0735224293| | 19|\n", "|1501173219| | 17|\n", "|0385544154| | 17|\n", "|0312577230| The Great Alone: A Novel| 15|\n", "|1503951812| | 15|\n", "|0062797158| | 14|\n", "|0307887448| | 14|\n", "|0060935464| | 14|\n", "|1542048273| | 13|\n", "|1476746583| | 13|\n", "|0812974492| | 13|\n", "|1101934182| | 12|\n", "|1476738025| | 12|\n", "|0670026190| | 12|\n", "|B0018OLTAC| | 11|\n", "|0316412694| The President Is Missing: A Novel| 11|\n", "|0718084225| The Hideaway| 11|\n", "|B07BZC5KHW| | 11|\n", "|1101883081| | 11|\n", "|0307341550| | 11|\n", "|B07FDNRPR5| | 10|\n", "|1524714682| One of Us Is Lying| 10|\n", "|074324754X| | 10|\n", "|0062654195| The Alice Network: A Novel| 10|\n", "|1250069823| | 10|\n", "|0425247449| | 10|\n", "|1338263897| | 10|\n", "|1616149981| | 10|\n", "|0062498533| The Hate U Give| 10|\n", "|1683993039| The Girl Who Lived: A Thrilling Suspense Novel| 10|\n", "|031631613X| | 10|\n", "|1328879941| | 9|\n", "|1503902374| | 9|\n", "|1451673310| | 9|\n", "|B078J5W5Z2| | 9|\n", "|0316055441| | 9|\n", "|1542046599| I Am Watching You| 9|\n", "|014242417X| | 9|\n", "|0525574689| | 9|\n", "|1451645856| | 9|\n", "|150116077X| | 9|\n", "|B0797K7NV7| | 9|\n", "|0312663153| | 9|\n", "|B0714QRG4Z| | 9|\n", "|1328869334| | 9|\n", "|1484707230| | 9|\n", "|0316556343| CIRCE (#1 New York Times bestseller)| 9|\n", "|1492663999| Sold on a Monday: A Novel| 9|\n", "|B074CTQWYS| | 9|\n", "|038549081X| | 8|\n", "|059035342X| | 8|\n", "|1400032717| | 8|\n", "|1250153964| | 8|\n", "|B073TT71KM| | 8|\n", "|B079F9D91N| | 8|\n", "|B07DQ3ZWYS| | 8|\n", "|1984825488| | 8|\n", "|0451490045| | 8|\n", "|B07CF6X6XF| | 8|\n", "|B07DDZP9RS| | 8|\n", "|0345544978| Small Great Things: A Novel| 8|\n", "|1455563927| Pachinko (National Book Award Finalist)| 8|\n", "|B076BVWKSF| | 8|\n", "|1419727435| | 8|\n", "|B0018OPBBK| | 8|\n", "|0345803787| Crazy Rich Asians (Crazy Rich Asians Trilogy)| 8|\n", "|0307742482| | 8|\n", "|145556902X|Girl Last Seen: A gripping psychological thriller with a ...| 8|\n", "|B07KS8SDBF| | 8|\n", "|B00DOWHZQ8| | 8|\n", "|B077Y4ZLL2| | 8|\n", "|B078H9GNKW| | 8|\n", "|0425274861| | 8|\n", "|B079FLYB41| | 8|\n", "|0385341008| The Guernsey Literary and Potato Peel Pie Society| 8|\n", "|B079P9BWR8| | 8|\n", "|B0018OY5X0| | 8|\n", "|1416936475| | 8|\n", "|1501156217| The Death of Mrs. Westaway| 8|\n", "|B06WVHLGRB| | 7|\n", "|B003LVYFUG| | 7|\n", "|B0713RV9HP| | 7|\n", "|0735221103| | 7|\n", "|0060850523| | 7|\n", "|1501132954| | 7|\n", "|B07KH61YJ2| | 7|\n", "|B07HDC7129| | 7|\n", "|0062315005| | 7|\n", "|0735220689| | 7|\n", "|0525563709| | 7|\n", "|1982102314| | 7|\n", "|0399501487| | 7|\n", "|B01G9AXWH2| | 7|\n", "|B07HSKPDBV| | 7|\n", "|B076BRK7Z4| | 7|\n", "|B07CWQ9D54| | 7|\n", "|B010276H5W| | 7|\n", "|1538728524| | 7|\n", "|0062457713|The Subtle Art of Not Giving a F*ck: A Counterintuitive A...| 7|\n", "|B078J6XVCD| | 7|\n", "|0743273567| | 7|\n", "|B07G2HKL1B| | 7|\n", "|B01N5FVM36| | 7|\n", "|B07B12HNN1| | 7|\n", "|B07B5FVT5K| | 7|\n", "|B07KMXNN3D| | 7|\n", "|014240733X| | 7|\n", "|B076TQ7R15| | 7|\n", "|0544336267| | 7|\n", "|B00CMQ911E| | 7|\n", "|0385541171| The Rooster Bar| 7|\n", "|B078HCG2FG| | 7|\n", "|B07HVFM4CG| | 6|\n", "|0061992887| | 6|\n", "|0316204269| | 6|\n", "|B0714C2W9C| | 6|\n", "|1451681739| | 6|\n", "|B07L1H86DG| | 6|\n", "|1101883073| | 6|\n", "|0375826696| | 6|\n", "|B07F5CPBPQ| | 6|\n", "|1503954056| | 6|\n", "|B06WW3H1F2| | 6|\n", "|0062319787|I'll Be Gone in the Dark: One Woman's Obsessive Search fo...| 6|\n", "|B01M9C1PD1| | 6|\n", "|B07B6KGQBZ| | 6|\n", "|B07G7QTXC2| | 6|\n", "|B07F4251BP| | 6|\n", "|038549565X| | 6|\n", "|B079NBWT45| | 6|\n", "|1477848665| Stillhouse Lake| 6|\n", "|B07KRQLX8D| | 6|\n", "|0316484806| | 6|\n", "|0061950726| | 6|\n", "|B074MGKHHD| | 6|\n", "|0440212561| | 6|\n", "|B01FH1TMRS| | 6|\n", "|1401308589| | 6|\n", "|B07BMWSVDX| | 6|\n", "|1786811278| | 6|\n", "|1501180983| The Outsider: A Novel| 6|\n", "|B01638AXXG| | 6|\n", "|0375869026| | 6|\n", "|0143127551| Everything I Never Told You| 6|\n", "|0439785960| | 6|\n", "|B073V4SPY6| | 6|\n", "|B00B58FQ5K| Lee Men's Total Freedom Relaxed Classic Fit Flat Front Pant| 6|\n", "|B073XNXXDZ| | 6|\n", "|1480030473| | 6|\n", "|0385751532| | 6|\n", "|0544340620| | 6|\n", "|1400201659|Girl, Wash Your Face: Stop Believing the Lies About Who Y...| 6|\n", "|1501154656| | 6|\n", "|B01BLS9E2Y| | 6|\n", "|0385534248| | 6|\n", "|152479628X| | 6|\n", "|B07886H8TF| | 6|\n", "|1503902129| | 6|\n", "|B01LTHZVSS| | 6|\n", "|B07C8V9T7T| | 6|\n", "|B06ZXWR8C5| | 6|\n", "|B01IXFS0L4| | 6|\n", "|0451224957| | 6|\n", "|B074Q1QKY9| | 5|\n", "|1328613046| The Fall of Gondolin| 5|\n", "|0997341521| | 5|\n", "|1477824758| | 5|\n", "|0525555366| | 5|\n", "|0060001941| A Tree Grows in Brooklyn| 5|\n", "|1501132938| The Woman in Cabin 10| 5|\n", "|0142423629| | 5|\n", "|1524708690| | 5|\n", "|1472229959| The Letter| 5|\n", "|0064404994| | 5|\n", "|B06XWWZ955| | 5|\n", "|B01I5SLS9A| | 5|\n", "|1250132363| Behind Closed Doors: A Novel| 5|\n", "|0062387243| | 5|\n", "|B07G1R63M9| | 5|\n", "|1538761386| | 5|\n", "|1501154648| Then She Was Gone: A Novel| 5|\n", "|0307409600| | 5|\n", "|B077RL8T87| | 5|\n", "|B07G1YKPPM| | 5|\n", "|B07BFB3F8M| | 5|\n", "|B01JPYUZF6| | 5|\n", "|B0741XXSRZ| | 5|\n", "|0735213186| The Immortalists| 5|\n", "|0385302312| | 5|\n", "|B074JS9QJB| | 5|\n", "|0439784549| | 5|\n", "+----------+------------------------------------------------------------+-----------+\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "from pyspark.sql import functions as F\n", "\n", "# --------------------------------------------------\n", "# 1️⃣ Top 200 seed products\n", "# --------------------------------------------------\n", "\n", "reviews_motif = g.find(\"\"\"\n", " (review)-[e]->(product)\n", "\"\"\").filter(\n", " (F.col(\"e.relationship\") == \"reviews\") &\n", " (F.col(\"review.type\") == \"review\") &\n", " (F.col(\"product.type\") == \"product\")\n", ")\n", "\n", "seed_products = reviews_motif.groupBy(\n", " F.col(\"product.id\").alias(\"seed_asin\")\n", ").agg(\n", " F.count(\"review.id\").alias(\"ReviewCount\")\n", ").orderBy(\n", " F.desc(\"ReviewCount\")\n", ").limit(200)\n", "\n", "# Alias for later joins\n", "seed_df_1 = seed_products.alias(\"seeds1\")\n", "seed_df_2 = seed_products.alias(\"seeds2\")\n", "\n", "# --------------------------------------------------\n", "# 2️⃣ ALSO_BUY motif\n", "# --------------------------------------------------\n", "\n", "also_buy_motif = g.find(\"\"\"\n", " (seed)-[e]->(other)\n", "\"\"\").filter(\n", " (F.col(\"e.relationship\") == \"also_buy\") &\n", " (F.col(\"seed.type\") == \"product\") &\n", " (F.col(\"other.type\") == \"product\")\n", ").alias(\"ab\")\n", "\n", "# --------------------------------------------------\n", "# 3️⃣ Keep only ALSO_BUY edges FROM seed products\n", "# --------------------------------------------------\n", "\n", "seed_also_buy = also_buy_motif.join(\n", " seed_df_1,\n", " F.col(\"ab.seed.id\") == F.col(\"seeds1.seed_asin\"),\n", " \"inner\"\n", ")\n", "\n", "# --------------------------------------------------\n", "# 4️⃣ Exclude products that are themselves seeds\n", "# --------------------------------------------------\n", "\n", "other_products = seed_also_buy.join(\n", " seed_df_2,\n", " F.col(\"ab.other.id\") == F.col(\"seeds2.seed_asin\"),\n", " \"left_anti\"\n", ")\n", "\n", "# --------------------------------------------------\n", "# 5️⃣ Count DISTINCT seed products per other product\n", "# --------------------------------------------------\n", "\n", "co_buy_stats = other_products.groupBy(\n", " F.col(\"ab.other.id\").alias(\"asin\"),\n", " F.col(\"ab.other.title\").alias(\"title\")\n", ").agg(\n", " F.countDistinct(\"ab.seed.id\").alias(\"LinkedSeeds\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 6️⃣ Final output\n", "# --------------------------------------------------\n", "\n", "result_df = co_buy_stats.select(\n", " F.col(\"asin\").alias(\"ASIN\"),\n", " F.coalesce(F.col(\"title\"), F.col(\"asin\")).alias(\"CoBuyProduct\"),\n", " F.col(\"LinkedSeeds\")\n", ").orderBy(\n", " F.desc(\"LinkedSeeds\")\n", ").limit(200)\n", "\n", "print(\"\\nPeople Who Bought This Also Bought\\n\")\n", "measure(result_df).show(200, truncate=60)\n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "a483602f-bfdc-4b46-ade4-450d71d5358f", "metadata": {}, "source": [ "### Second-Hop Co-Buy Influence (Ripple Effect)" ] }, { "cell_type": "code", "execution_count": 32, "id": "44df1f1c-2e82-45f8-93e2-85f1abf7b8e7", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Second-Hop Co-Buy Influence (Ripple Effect)\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " 00]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Time taken: 113.616 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " 00]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------+------------------------------------------------------------+---------------+\n", "| ASIN| SecondHopProduct|SharedNeighbors|\n", "+----------+------------------------------------------------------------+---------------+\n", "|0735219095| Where the Crawdads Sing| 18|\n", "|0735220697| | 16|\n", "|0735224293| | 13|\n", "|0062678418| The Woman in the Window: A Novel| 13|\n", "|0399590501| Educated: A Memoir| 13|\n", "|145556902X|Girl Last Seen: A gripping psychological thriller with a ...| 11|\n", "|1501154656| | 11|\n", "|1503943372| Beneath a Scarlet Sky: A Novel| 11|\n", "|1250069823| | 11|\n", "|0735221103| | 11|\n", "|1683993039| The Girl Who Lived: A Thrilling Suspense Novel| 11|\n", "|0735211221| | 10|\n", "|1524763136| | 10|\n", "|1250080401| | 10|\n", "|0062797158| | 10|\n", "|0425284689| Before We Were Yours: A Novel| 10|\n", "|1501156217| The Death of Mrs. Westaway| 10|\n", "|1501132954| | 10|\n", "|1501173219| | 9|\n", "|1542048273| | 9|\n", "|1250153964| | 9|\n", "|1542046599| I Am Watching You| 9|\n", "|0399563091| | 9|\n", "|0062319787|I'll Be Gone in the Dark: One Woman's Obsessive Search fo...| 9|\n", "|1616208775| | 9|\n", "|0735224625| | 8|\n", "|1250132363| Behind Closed Doors: A Novel| 8|\n", "|1492663999| Sold on a Monday: A Novel| 8|\n", "|0525520376| There There: A novel| 7|\n", "|0316412694| The President Is Missing: A Novel| 7|\n", "|0312577230| The Great Alone: A Novel| 7|\n", "|0385544154| | 7|\n", "|0525574689| | 7|\n", "|0307742482| | 7|\n", "|1503902374| | 7|\n", "|0062498533| The Hate U Give| 7|\n", "|0345803787| Crazy Rich Asians (Crazy Rich Asians Trilogy)| 7|\n", "|1786811278| | 7|\n", "|1616149981| | 7|\n", "|1501180983| The Outsider: A Novel| 7|\n", "|0425274861| | 7|\n", "|0307341550| | 7|\n", "|0316484806| | 6|\n", "|0062654195| The Alice Network: A Novel| 6|\n", "|031631613X| | 6|\n", "|0062645234| Magpie Murders: A Novel| 6|\n", "|1250130948| | 6|\n", "|1503951812| | 6|\n", "|0553535676| | 6|\n", "|1594634025| | 6|\n", "|1524714682| One of Us Is Lying| 6|\n", "|1447286006| | 6|\n", "|1250170974| Children of Blood and Bone (Legacy of Orisha)| 6|\n", "|1476740186| | 6|\n", "|0316556343| CIRCE (#1 New York Times bestseller)| 6|\n", "|0345544986| | 6|\n", "|0670026190| | 6|\n", "|0525521429| | 5|\n", "|0451490045| | 5|\n", "|1480030473| | 5|\n", "|1250066204| | 5|\n", "|1501107976| | 5|\n", "|0425247449| | 5|\n", "|0316422738| | 5|\n", "|1501156209| The Lying Game: A Novel| 5|\n", "|1484715705| | 5|\n", "|0142402516| | 5|\n", "|038549565X| | 5|\n", "|1101934743| | 5|\n", "|1644450003| | 5|\n", "|1250151333| | 5|\n", "|1538728524| | 5|\n", "|1503900649| | 5|\n", "|0718084225| The Hideaway| 5|\n", "|0375866280| | 5|\n", "|152473165X| Bad Blood: Secrets and Lies in a Silicon Valley Startup| 5|\n", "|178681143X|Guilty: A gripping psychological thriller that will have ...| 5|\n", "|0553496689| | 5|\n", "|1503901335| | 5|\n", "|0143132172| The Perfect Nanny: A Novel| 5|\n", "|1524743070| The Last Time I Lied: A Novel| 5|\n", "|1501112333| | 5|\n", "|1250105625| The Dry: A Novel| 5|\n", "|0316055441| | 5|\n", "|0525521194| Warlight: A novel| 4|\n", "|0008248869| | 4|\n", "|0008234167|The Woman in the Window: The Hottest New Release Thriller...| 4|\n", "|0778317765| | 4|\n", "|144947425X| Milk and Honey| 4|\n", "|1250088569| This Is How It Always Is| 4|\n", "|149262246X| This Is Where It Ends| 4|\n", "|052549877X| | 4|\n", "|1477848665| Stillhouse Lake| 4|\n", "|1534427031|The To All the Boys I've Loved Before Paperback Collectio...| 4|\n", "|0062795252| | 4|\n", "|1501154605| | 4|\n", "|0525555366| | 4|\n", "|1449486797| The Sun and Her Flowers| 4|\n", "|0991301870| | 4|\n", "|1455563927| Pachinko (National Book Award Finalist)| 4|\n", "|1505436354| Cold Lake (David Wolf) (Volume 5)| 4|\n", "|150116077X| | 4|\n", "|0385755910| | 4|\n", "|0553496670| | 4|\n", "|1101883081| | 4|\n", "|1786812150|Buried Secrets: A gripping thriller you wont be able to p...| 4|\n", "|0735221138| | 4|\n", "|1786817500| | 4|\n", "|1786812118| | 4|\n", "|1481449362| | 4|\n", "|1786815257| | 4|\n", "|0062217143| | 4|\n", "|159514188X| | 4|\n", "|0345544978| Small Great Things: A Novel| 4|\n", "|0312674392| | 4|\n", "|0307455920| | 4|\n", "|1400201659|Girl, Wash Your Face: Stop Believing the Lies About Who Y...| 4|\n", "|0143127551| Everything I Never Told You| 4|\n", "|031617663X| | 4|\n", "|1503948706| Say You're Sorry (Morgan Dane)| 4|\n", "|125019198X| The Other Woman: A Novel| 4|\n", "|0525557628| | 4|\n", "|006269460X| If You Knew Her: A Novel| 4|\n", "|0735223521| The Great Believers| 4|\n", "|1250130921| The Wife Between Us: A Novel| 4|\n", "|1786811065|Safe With Me: A psychological thriller so tense it will t...| 4|\n", "|0142410705| | 4|\n", "|0316420239| | 4|\n", "|1250069807| | 4|\n", "|014242417X| | 4|\n", "|1524797189| Something in the Water: A Novel| 4|\n", "|1479369446| Sara's Game| 4|\n", "|1683990021| | 4|\n", "|1484009290| Noble Beginnings: A Jack Noble Novel| 3|\n", "|0062684566| | 3|\n", "|0062327194| | 3|\n", "|0545169186| | 3|\n", "|006234868X| | 3|\n", "|0062667580| | 3|\n", "|0735212767| | 3|\n", "|0812985400| | 3|\n", "|1538761572| | 3|\n", "|1609450787| | 3|\n", "|0307887448| | 3|\n", "|1786816695| | 3|\n", "|0385542410| The Flight Attendant: A Novel| 3|\n", "|1324002646| | 3|\n", "|1501154834| The Tea Girl of Hummingbird Lane: A Novel| 3|\n", "|1683993063| | 3|\n", "|0062473557| The Keeper of Lost Things: A Novel| 3|\n", "|152476079X| | 3|\n", "|0312577222| The Nightingale: A Novel| 3|\n", "|1501135945|Indianapolis: The True Story of the Worst Sea Disaster in...| 3|\n", "|0552574244| The Sun is also a Star| 3|\n", "|153871342X| | 3|\n", "|1503900827| | 3|\n", "|1476795924| | 3|\n", "|0385741278| | 3|\n", "|1250012570| | 3|\n", "|014241493X| Paper Towns| 3|\n", "|1451649398| | 3|\n", "|1455524204| | 3|\n", "|0385534248| | 3|\n", "|0399586687| Next Year in Havana| 3|\n", "|1607749165| | 3|\n", "|1982102314| | 3|\n", "|0307341577| Dark Places| 3|\n", "|1984847392| | 3|\n", "|0312541538| | 3|\n", "|1501172093| The Glass Forest: A Novel| 3|\n", "|0735213186| The Immortalists| 3|\n", "|0375842209| | 3|\n", "|0743298071| | 3|\n", "|006287067X| | 3|\n", "|0316551244| | 3|\n", "|1481438255| Long Way Down| 3|\n", "|0385751532| | 3|\n", "|0062430254| | 3|\n", "|0374303231|The 57 Bus: A True Story of Two Teenagers and the Crime T...| 3|\n", "|1481430491| | 3|\n", "|1451696191| | 3|\n", "|0451224957| | 3|\n", "|1476738025| | 3|\n", "|1250074134| All the Ugly and Wonderful Things: A Novel| 3|\n", "|1442426713| | 3|\n", "|0062316117| | 3|\n", "|0307476073| Wild: From Lost to Found on the Pacific Crest Trail| 3|\n", "|0996781501| | 3|\n", "|0525589988| | 3|\n", "|1250179831| | 3|\n", "|1406372153| | 3|\n", "|0307931897| | 2|\n", "|0765382032| | 2|\n", "|0991499727| And Then You Loved Me| 2|\n", "|0143113496| In the Woods: A Novel| 2|\n", "|1524797146| | 2|\n", "|0062413864| What She Knew: A Novel| 2|\n", "|1484723643| | 2|\n", "|0399185046| The Book of Joy: Lasting Happiness in a Changing World| 2|\n", "|0451490800| The Kiss Quotient| 2|\n", "+----------+------------------------------------------------------------+---------------+\n", "\n" ] } ], "source": [ "from pyspark.sql import functions as F\n", "\n", "# (review)-[:reviews]->(product)\n", "reviews_motif = g.find(\"\"\"\n", " (review)-[e]->(product)\n", "\"\"\").filter(\n", " (F.col(\"e.relationship\") == \"reviews\") &\n", " (F.col(\"review.type\") == \"review\") &\n", " (F.col(\"product.type\") == \"product\")\n", ")\n", "\n", "top_seed = reviews_motif.groupBy(\n", " F.col(\"product.id\").alias(\"seed_asin\")\n", ").agg(\n", " F.count(\"review.id\").alias(\"ReviewCount\")\n", ").orderBy(\n", " F.desc(\"ReviewCount\")\n", ").limit(1)\n", "two_hop_motif = g.find(\"\"\"\n", " (seed)-[e1]->(firstHop);\n", " (firstHop)-[e2]->(secondHop)\n", "\"\"\").filter(\n", " (F.col(\"e1.relationship\") == \"also_buy\") &\n", " (F.col(\"e2.relationship\") == \"also_buy\") &\n", " (F.col(\"seed.type\") == \"product\") &\n", " (F.col(\"firstHop.type\") == \"product\") &\n", " (F.col(\"secondHop.type\") == \"product\")\n", ")\n", "two_hop_from_seed = two_hop_motif.join(\n", " top_seed.alias(\"ts\"),\n", " F.col(\"seed.id\") == F.col(\"ts.seed_asin\"),\n", " \"inner\"\n", ")\n", "filtered_two_hop = two_hop_from_seed.filter(\n", " F.col(\"secondHop.id\") != F.col(\"seed.id\")\n", ")\n", "second_hop_stats = filtered_two_hop.groupBy(\n", " F.col(\"secondHop.id\").alias(\"asin\"),\n", " F.col(\"secondHop.title\").alias(\"title\")\n", ").agg(\n", " F.countDistinct(\"firstHop.id\").alias(\"SharedNeighbors\")\n", ")\n", "result_df = second_hop_stats.select(\n", " F.col(\"asin\").alias(\"ASIN\"),\n", " F.coalesce(F.col(\"title\"), F.col(\"asin\")).alias(\"SecondHopProduct\"),\n", " F.col(\"SharedNeighbors\")\n", ").orderBy(\n", " F.desc(\"SharedNeighbors\")\n", ").limit(200)\n", "\n", "print(\"\\nSecond-Hop Co-Buy Influence (Ripple Effect)\\n\")\n", "measure(result_df).show(200, truncate=60)\n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "e96668fd-9975-4470-afd7-fbcd6f53f5f9", "metadata": {}, "source": [ "### ===Category/ Brand===\n", "\n", "Most Popular Categories by Reviews\n", "Rank categories by review volume.\n" ] }, { "cell_type": "code", "execution_count": 33, "id": "99a1ae3a-4a79-4945-b4cb-6f2ec682269c", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Most Popular Categories by Reviews\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " 200]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Time taken: 79.041 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "[Stage 667:====================================================>(250 + 1) / 251]00]]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------------------------------+------------+\n", "| Category|TotalReviews|\n", "+----------------------------------+------------+\n", "| Books| 48600233|\n", "| Clothing, Shoes & Jewelry| 31719207|\n", "| Home & Kitchen| 21417567|\n", "| Electronics| 20620845|\n", "| Women| 16560922|\n", "| Literature & Fiction| 15421525|\n", "| Clothing| 12597859|\n", "| Men| 10102232|\n", "| Accessories| 9965748|\n", "| Kitchen & Dining| 9961814|\n", "| Cell Phones & Accessories| 9403959|\n", "| Computers & Accessories| 9207028|\n", "| Shoes| 8749873|\n", "| Movies & TV| 8519234|\n", "| Tools & Home Improvement| 8428297|\n", "| Automotive| 7640415|\n", "| Toys & Games| 7465480|\n", "| Pet Supplies| 6180131|\n", "| Kindle Store| 5667503|\n", "| Kindle eBooks| 5649207|\n", "| Office Products| 5248017|\n", "| Patio, Lawn & Garden| 5056248|\n", "| Mystery, Thriller & Suspense| 5049550|\n", "| Imported| 5034287|\n", "| Grocery & Gourmet Food| 4922142|\n", "| Genre Fiction| 4838145|\n", "| Cases, Holsters & Sleeves| 4600745|\n", "| Romance| 4117772|\n", "| CDs & Vinyl| 4094569|\n", "| Office & School Supplies| 4038870|\n", "| Dogs| 3788075|\n", "| Science Fiction & Fantasy| 3633587|\n", "| Children's Books| 3342221|\n", "| Home Dcor| 3323148|\n", "| Christian Books & Bibles| 3229609|\n", "| Accessories & Supplies| 3021725|\n", "| Thrillers & Suspense| 2981132|\n", "| Replacement Parts| 2863526|\n", "| Jewelry| 2790301|\n", "|Computer Accessories & Peripherals| 2705254|\n", "| Camera & Photo| 2655045|\n", "| Audio & Video Accessories| 2610069|\n", "| Arts, Crafts & Sewing| 2597956|\n", "| Video Games| 2472436|\n", "| Novelty & More| 2355211|\n", "| Basic Cases| 2333905|\n", "| Studio Specials| 2266028|\n", "| Kitchen Utensils & Gadgets| 2193932|\n", "| Games| 2183400|\n", "| Tablet Accessories| 2176552|\n", "| Bedding| 2131328|\n", "| Storage & Organization| 2126129|\n", "| Contemporary| 2115840|\n", "| Biographies & Memoirs| 2107289|\n", "| Novelty| 2098434|\n", "| Power & Hand Tools| 1946477|\n", "| Bags, Cases & Sleeves| 1931345|\n", "| | 1918727|\n", "| Genre for Featured Categories| 1872707|\n", "| Athletic| 1847382|\n", "| Mystery| 1739999|\n", "| Rubber sole| 1730212|\n", "| Teen & Young Adult| 1668216|\n", "| Industrial & Scientific| 1645020|\n", "| History| 1623171|\n", "| Dining & Entertaining| 1596160|\n", "| Action & Adventure| 1547647|\n", "| Business & Money| 1547600|\n", "| Furniture| 1509961|\n", "| Headphones| 1501542|\n", "| Portable Audio & Video| 1476200|\n", "| Beverages| 1470075|\n", "| Musical Instruments| 1469464|\n", "| Arts & Photography| 1450137|\n", "| Machine Wash| 1440468|\n", "| Synthetic sole| 1425742|\n", "| Boots| 1425370|\n", "| Cats| 1420097|\n", "| Crafts, Hobbies & Home| 1411610|\n", "| Cables & Interconnects| 1410594|\n", "| United States| 1395896|\n", "| Science Fiction| 1392021|\n", "| Shirts| 1379846|\n", "| Health, Fitness & Dieting| 1370926|\n", "| Lingerie, Sleep & Lounge| 1351349|\n", "| Small Appliances| 1350564|\n", "| Sandals| 1343885|\n", "| Gardening & Lawn Care| 1327190|\n", "| Religion & Spirituality| 1315225|\n", "| T-Shirts| 1308642|\n", "| Lighting & Ceiling Fans| 1303132|\n", "| Cases| 1299914|\n", "| Girls| 1271543|\n", "| Fantasy| 1270047|\n", "| Movies| 1232294|\n", "| Screen Protectors| 1230089|\n", "| Watches| 1197387|\n", "| Dresses| 1187907|\n", "| New, Used & Rental Textbooks| 1184485|\n", "| Cookbooks, Food & Wine| 1176941|\n", "| Exterior Accessories| 1143736|\n", "| Coffee, Tea & Cocoa| 1136408|\n", "| Christian Living| 1127131|\n", "| Luggage & Travel Gear| 1119672|\n", "| Politics & Social Sciences| 1115079|\n", "| Growing Up & Facts of Life| 1097738|\n", "| Hand Tools| 1095552|\n", "| Laptop Accessories| 1095407|\n", "| Electrical| 1079189|\n", "| Wrist Watches| 1061972|\n", "| Lingerie| 1052793|\n", "| 100% Leather| 1044137|\n", "| Safety & Security| 1026705|\n", "| Self-Help| 1005643|\n", "| Costumes & Accessories| 977510|\n", "| Bath| 974558|\n", "| Outdoor Dcor| 968014|\n", "| Home Dcor Accents| 951262|\n", "| Humor & Entertainment| 939909|\n", "| Office Electronics| 918377|\n", "| Cooking & Baking| 893549|\n", "| Interior Accessories| 865704|\n", "| Uniforms, Work & Safety| 857696|\n", "| Pop| 834728|\n", "| Baby| 830889|\n", "| Computer Components| 830278|\n", "| Writing & Correction Supplies| 826600|\n", "| Fashion Sneakers| 820362|\n", "| 100% Cotton| 814062|\n", "| Hardware| 812164|\n", "| Running| 811024|\n", "| Boys| 806600|\n", "| Casual| 785738|\n", "| Motorcycle & Powersports| 781777|\n", "| Networking Products| 779502|\n", "| Women's Fiction| 770477|\n", "| Blu-ray| 758517|\n", "| Americas| 744510|\n", "| Necklaces| 739042|\n", "| Bathroom Accessories| 736365|\n", "| Hats & Caps| 721859|\n", "| Heating, Cooling & Air Quality| 718542|\n", "| Parts & Accessories| 717614|\n", "| Tops, Tees & Blouses| 711191|\n", "| Reference| 708493|\n", "| Cookware| 707586|\n", "| Action Figures & Statues| 707333|\n", "| Toys| 701075|\n", "| Vacuums & Floor Care| 699521|\n", "| Active| 697225|\n", "| Data Storage| 694923|\n", "| Car & Vehicle Electronics| 693537|\n", "| Printer Ink & Toner| 682892|\n", "| Flats| 674071|\n", "| Bakeware| 664450|\n", "| Computers & Technology| 661938|\n", "| Drama| 660152|\n", "| Politics & Government| 655042|\n", "| Earbud Headphones| 651297|\n", "| Kitchen & Bath Fixtures| 650264|\n", "| Crafts & Hobbies| 649052|\n", "| Tools & Equipment| 648827|\n", "| Computers & Tablets| 644387|\n", "| Collars, Harnesses & Leashes| 643634|\n", "| Science & Math| 632767|\n", "| Sewing| 630172|\n", "| Keyboards, Mice & Accessories| 629048|\n", "| Jeans| 625847|\n", "| Kids & Family| 621418|\n", "| Television & Video| 618162|\n", "| Outdoor Power Tools| 614644|\n", "| Historical| 614179|\n", "| PC| 611484|\n", "| Internal Components| 608957|\n", "| Candy & Chocolate| 607573|\n", "| Instrument Accessories| 600816|\n", "| Tops & Tees| 595170|\n", "| Coffee| 588768|\n", "| Appliances| 587607|\n", "| Comedy| 585858|\n", "| Stuffed Animals & Plush Toys| 583800|\n", "| Building Supplies| 580109|\n", "| Crafting| 579559|\n", "| Costumes| 576838|\n", "| Handbags & Wallets| 570767|\n", "| Sunglasses & Eyewear Accessories| 569590|\n", "| Pants| 568809|\n", "| Humor| 564844|\n", "| Coffee, Tea & Espresso| 562753|\n", "| Lights & Lighting Accessories| 558859|\n", "| All Titles| 557681|\n", "| Chargers & Power Adapters| 553220|\n", "| Sports & Outdoor Play| 552508|\n", "| Earrings| 547794|\n", "| Bras| 542428|\n", "| 100% Polyester| 538828|\n", "| Power Tool Parts & Accessories| 534692|\n", "| Portable Speakers & Docks| 529091|\n", "| Grills & Outdoor Cooking| 528143|\n", "| Leaders & Notable People| 527498|\n", "+----------------------------------+------------+\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "from pyspark.sql import functions as F\n", "\n", "# --------------------------------------------------\n", "# 1️⃣ 3-hop motif:\n", "# (review)-[:reviews]->(product)-[:in_category]->(category)\n", "# --------------------------------------------------\n", "\n", "category_motif = g.find(\"\"\"\n", " (review)-[e1]->(product);\n", " (product)-[e2]->(category)\n", "\"\"\").filter(\n", " (F.col(\"e1.relationship\") == \"reviews\") &\n", " (F.col(\"e2.relationship\") == \"in_category\") &\n", " (F.col(\"review.type\") == \"review\") &\n", " (F.col(\"product.type\") == \"product\") &\n", " (F.col(\"category.type\") == \"category\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 2️⃣ Count reviews per category\n", "# --------------------------------------------------\n", "\n", "category_stats = category_motif.groupBy(\n", " F.col(\"category.name\").alias(\"Category\")\n", ").agg(\n", " F.count(\"review.id\").alias(\"TotalReviews\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 3️⃣ Order and limit\n", "# --------------------------------------------------\n", "\n", "result_df = category_stats.orderBy(\n", " F.desc(\"TotalReviews\")\n", ").limit(200)\n", "\n", "print(\"\\nMost Popular Categories by Reviews\\n\")\n", "measure(result_df).show(200, truncate=60)\n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "60a44db5-50a9-40b6-a899-373fc7a4fd4a", "metadata": {}, "source": [ "### Top Brands by Average Rating\n", "Brands with ≥100 reviews across their catalog and high avg ratings." ] }, { "cell_type": "code", "execution_count": 34, "id": "f175bc4d-2b48-43ba-8be1-7142c82aa215", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Top Brands by Average Rating (≥100 Reviews)\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " ]]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Time taken: 56.145 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "[Stage 727:====================================================>(199 + 1) / 200]]]]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+-------------------------------------------+---------+-----------+\n", "| Brand|AvgRating|ReviewCount|\n", "+-------------------------------------------+---------+-----------+\n", "| Kimberly Ichter Eldredge| 5.0| 121|\n", "| Professor Nader Angha| 5.0| 117|\n", "| Dasia \"DasiaVu\" Edmond| 5.0| 106|\n", "| FILLBOSS| 5.0| 106|\n", "| Visit Amazon's Bryan C. Taylor Page| 5.0| 230|\n", "| OptiCover| 5.0| 153|\n", "| Visit Amazon's Russ McNeil Page| 5.0| 102|\n", "| Visit Amazon's Kasey Bell Page| 5.0| 151|\n", "| Dr. Bindu Mayi| 5.0| 141|\n", "| Kathrine Lee| 5.0| 116|\n", "| Visit Amazon's Chapo Trap House Page| 5.0| 100|\n", "| SUNATORIA| 5.0| 142|\n", "| Moon Knives| 5.0| 137|\n", "| Visit Amazon's Jennie Lynn Page| 5.0| 121|\n", "| Linon.| 5.0| 138|\n", "| Sunatoria| 5.0| 127|\n", "| Nicexxxxx| 5.0| 197|\n", "| Visit Amazon's Thomas R. Pryor Page| 5.0| 128|\n", "| Visit Amazon's Chanie Apfelbaum Page| 5.0| 115|\n", "| Visit Amazon's Caroline Harries Page| 5.0| 113|\n", "| KEKS| 5.0| 100|\n", "| YANKEL BEAUTY| 5.0| 114|\n", "| Visit Amazon's Megha Barot Page| 5.0| 116|\n", "| Good Luncheba| 5.0| 109|\n", "| Kitoo| 5.0| 722|\n", "| Roo Yori| 5.0| 102|\n", "| Campfire Stuff| 5.0| 107|\n", "| Visit Amazon's Nivedita Lakhera Page| 5.0| 245|\n", "| Dr. Pet| 5.0| 300|\n", "| XROOL| 5.0| 135|\n", "| Visit Amazon's Ronen Gafni Page| 5.0| 114|\n", "| VR WEAR| 5.0| 246|\n", "| Visit Amazon's Lynn M. Hayden Page| 5.0| 135|\n", "| Magdalena Yesil| 5.0| 125|\n", "| Visit Amazon's Lester Aradi Page| 5.0| 110|\n", "| Jane Sobel Klonsky| 5.0| 101|\n", "| NBCT| 5.0| 410|\n", "| Purizzy| 5.0| 108|\n", "| PHILLOVE| 5.0| 175|\n", "| Visit Amazon's Simon Calcavecchia Page| 5.0| 114|\n", "| Cold Brew Coffee Maker Home Story| 5.0| 153|\n", "| BKA Sound Master| 5.0| 120|\n", "| Leann Rhodes| 4.99| 106|\n", "| Litero| 4.99| 180|\n", "| Visit Amazon's Bubba's Dad Page| 4.99| 136|\n", "| Visit Amazon's Orlando Soria Page| 4.99| 170|\n", "| TETHER| 4.99| 107|\n", "| Visit Amazon's Vincent Pugliese Page| 4.99| 165|\n", "| Visit Amazon's John Chisholm Page| 4.99| 113|\n", "| Dr. Rich Melheim| 4.99| 107|\n", "| MothPro| 4.99| 163|\n", "| Visit Amazon's Garrett J. White Page| 4.99| 256|\n", "| LBKR Tech| 4.99| 137|\n", "| Visit Amazon's Andrew Marttila Page| 4.99| 116|\n", "| Carla Link| 4.99| 126|\n", "| Visit Amazon's James Barber Page| 4.99| 104|\n", "| Visit Amazon's Phil Robbins Page| 4.99| 110|\n", "| Visit Amazon's Paul Sohn Page| 4.99| 119|\n", "| Visit Amazon's Derrick Levasseur Page| 4.99| 101|\n", "| Marshawn Evans Daniels| 4.99| 172|\n", "|Visit Amazon's JWDownloads JWDownloads Page| 4.99| 139|\n", "| Visit Amazon's Will Hines Page| 4.99| 108|\n", "| Visit Amazon's Andy Frisella Page| 4.99| 178|\n", "| Visit Amazon's Daron K. Roberts Page| 4.99| 118|\n", "| Montico| 4.99| 153|\n", "| Paco Collars| 4.99| 156|\n", "| Lazzari Collections| 4.99| 350|\n", "| Dr John A Weber| 4.99| 154|\n", "| Brandon DiCamillo| 4.99| 118|\n", "| Visit Amazon's Ed Mylett Page| 4.99| 151|\n", "| Visit Amazon's Fran Hauser Page| 4.99| 102|\n", "| Visit Amazon's Eric Eaton Page| 4.99| 117|\n", "| SeeZoom| 4.99| 599|\n", "| Visit Amazon's Shawn Carney Page| 4.99| 131|\n", "| Suna| 4.99| 201|\n", "| The HOLO| 4.98| 124|\n", "| Nicexx| 4.98| 878|\n", "| Visit Amazon's Kari Samuels Page| 4.98| 281|\n", "| Sother Teague| 4.98| 151|\n", "| Visit Amazon's Jen Schmidt Page| 4.98| 119|\n", "| Visit Amazon's David Mike Page| 4.98| 123|\n", "| Visit Amazon's Jeff McManus Page| 4.98| 126|\n", "| Jeff Voth| 4.98| 138|\n", "| Visit Amazon's Lisa Mason Ziegler Page| 4.98| 185|\n", "| EHOUSE| 4.98| 166|\n", "| WoodenGifts| 4.98| 157|\n", "| cruel| 4.98| 558|\n", "| Bobby Susser| 4.98| 143|\n", "| Visit Amazon's Brian Bird Page| 4.98| 118|\n", "| MisterWand| 4.98| 261|\n", "| Urban Tribe| 4.98| 178|\n", "| Visit Amazon's Tracy Stopler Page| 4.98| 147|\n", "| Onuki| 4.98| 149|\n", "| Tiffany Jenkins| 4.98| 1127|\n", "| Karen Ann Wiant| 4.98| 603|\n", "| Fitquipment| 4.98| 1522|\n", "| Visit Amazon's Kris Wehrmeister Page| 4.98| 108|\n", "| Buddy&Max| 4.98| 1572|\n", "| Jesse L Gros| 4.98| 107|\n", "| Visit Amazon's Natasha Hazlett Page| 4.98| 127|\n", "| V!RS| 4.98| 234|\n", "| PantShop| 4.98| 335|\n", "| Visit Amazon's Daniel Eisenman Page| 4.98| 128|\n", "| Visit Amazon's Hank Smith Page| 4.98| 418|\n", "| Visit Amazon's Belinda Davidson Page| 4.98| 132|\n", "| Kape| 4.98| 141|\n", "| Visit Amazon's Amy Eldridge Page| 4.98| 102|\n", "| Mark Lewis Art| 4.98| 439|\n", "| Visit Amazon's Dr Teresa Davis Page| 4.98| 108|\n", "| StopWoofer| 4.97| 178|\n", "| Visit Amazon's Jos Luis Navajo Page| 4.97| 115|\n", "| Visit Amazon's Kelley Gunter Page| 4.97| 114|\n", "| Visit Amazon's Dan Kerrigan Page| 4.97| 108|\n", "| Visit Amazon's Gregory Q. Cheek Page| 4.97| 103|\n", "| SuperBEARPower| 4.97| 118|\n", "| Visit Amazon's Jon Vroman Page| 4.97| 183|\n", "| Paul Brown Hawaii| 4.97| 268|\n", "| Visit Amazon's Erin Stanton Page| 4.97| 148|\n", "| Visit Amazon's Heather Avis Page| 4.97| 201|\n", "| Daniel Lissing| 4.97| 112|\n", "| Poorboy Woodworks| 4.97| 101|\n", "| Visit Amazon's Evan Carmichael Page| 4.97| 144|\n", "| Visit Amazon's Pastor Surprise Page| 4.97| 197|\n", "| Christy Rutherford| 4.97| 228|\n", "| Rickie G. Rush| 4.97| 246|\n", "| Visit Amazon's David R. Dixon Page| 4.97| 705|\n", "| Dabest| 4.97| 153|\n", "| Visit Amazon's Greg Murray Page| 4.97| 105|\n", "| Visit Amazon's Kristy Dickerson Page| 4.97| 239|\n", "| Visit Amazon's Hoss Pratt Page| 4.97| 111|\n", "| Visit Amazon's Marilyn Holzmann Page| 4.97| 142|\n", "| Jen Oloo| 4.97| 239|\n", "| Visit Amazon's Ryan Montague PhD Page| 4.97| 106|\n", "| Gary S. Comer| 4.97| 134|\n", "| Visit Amazon's Brooke Sailer Page| 4.97| 132|\n", "| Hank Smith| 4.97| 187|\n", "| Vet My Pet| 4.97| 233|\n", "| Visit Amazon's Carolyn Ketchum Page| 4.97| 745|\n", "| SmartDelux| 4.97| 103|\n", "| Visit Amazon's Brandt Tobler Page| 4.97| 118|\n", "| Visit Amazon's Artie Lynnworth Page| 4.97| 103|\n", "| Express Tags| 4.97| 103|\n", "| Maureen Anderman| 4.97| 117|\n", "| Visit Amazon's Cody Bobay Page| 4.97| 205|\n", "| Malaha| 4.97| 151|\n", "| Visit Amazon's Only1MILLION Page| 4.97| 149|\n", "| RocksOnly| 4.97| 220|\n", "| Visit Amazon's Pamela Salzman Page| 4.97| 241|\n", "|Visit Amazon's The Okee Dokee Brothers Page| 4.96| 158|\n", "| Richard Phibbs| 4.96| 106|\n", "| Ray Bard| 4.96| 108|\n", "| Visit Amazon's Edwin Lee Page| 4.96| 159|\n", "|Visit Amazon's Jorge Alexander Sanchez Page| 4.96| 110|\n", "| Visit Amazon's Ryan Beauchesne Page| 4.96| 730|\n", "| Sharon Jaynes| 4.96| 135|\n", "| Visit Amazon's Mary Shannon Moore Page| 4.96| 237|\n", "| Visit Amazon's Dianne Bischoff James Page| 4.96| 107|\n", "| Visit Amazon's Jim H. Ainsworth Page| 4.96| 135|\n", "| Cafetec| 4.96| 3150|\n", "| Visit Amazon's John Brennan Page| 4.96| 172|\n", "| Visit Amazon's Lindsey Bell Page| 4.96| 100|\n", "|Visit Amazon's Susan Schaefer Bernardo Page| 4.96| 136|\n", "| Visit Amazon's Kirsten Samuel Page| 4.96| 100|\n", "| Visit Amazon's Camille Eide Page| 4.96| 191|\n", "| Mrs. Mariel Ruggieri| 4.96| 156|\n", "| Visit Amazon's Deanie Humphrys-Dunne Page| 4.96| 121|\n", "| Visit Amazon's Crystal Chatterton Page| 4.96| 113|\n", "| Visit Amazon's Tonya Calvert Page| 4.96| 104|\n", "| Visit Amazon's EJ Brock Page| 4.96| 171|\n", "| Visit Amazon's David Bereit Page| 4.96| 225|\n", "| Visit Amazon's Doug Moe Page| 4.96| 134|\n", "| Visit Amazon's Lori Degman Page| 4.96| 113|\n", "| Dorothea Warren Fox| 4.96| 165|\n", "| Visit Amazon's Andrea S. Kramer Page| 4.96| 112|\n", "| Visit Amazon's Ray Higdon Page| 4.96| 311|\n", "| Visit Amazon's Alan Tenenbaum Page| 4.96| 101|\n", "| Visit Amazon's Mrs Sondra A Torres Page| 4.96| 139|\n", "| JB Glossinger| 4.96| 111|\n", "| Visit Amazon's Julia Finley Mosca Page| 4.96| 168|\n", "| Visit Amazon's Jen Atkinson Page| 4.96| 127|\n", "| Christian Jacobs| 4.96| 116|\n", "| Visit Amazon's Brian Carroll Page| 4.96| 125|\n", "| Moosh Walks| 4.96| 123|\n", "| The Chainlink| 4.96| 268|\n", "| Visit Amazon's Laura Lea Goldberg Page| 4.96| 464|\n", "| Sara J. Weis| 4.96| 100|\n", "| Visit Amazon's Amy L. Sullivan Page| 4.96| 120|\n", "| Visit Amazon's Magdalena Wszelaki Page| 4.96| 110|\n", "| Byrne & Kelly| 4.96| 124|\n", "| Visit Amazon's Anita Carman Page| 4.96| 128|\n", "| Visit Amazon's Georgia Dunn Page| 4.96| 204|\n", "| BlankieGram.com| 4.96| 651|\n", "| Visit Amazon's Kris Morris Page| 4.96| 104|\n", "| Grace Gong| 4.96| 100|\n", "| Visit Amazon's Ramona Bridges Page| 4.96| 132|\n", "| Visit Amazon's Matt Sweetwood Page| 4.96| 114|\n", "| Visit Amazon's Hector Jesus Arencibia Page| 4.95| 121|\n", "| Visit Amazon's Stephen Rea Page| 4.95| 110|\n", "| Visit Amazon's Terry Lancaster Page| 4.95| 112|\n", "| Curtis Wiklund| 4.95| 116|\n", "+-------------------------------------------+---------+-----------+\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "from pyspark.sql import functions as F\n", "\n", "# --------------------------------------------------\n", "# 1️⃣ Motif: (review)-[:reviews]->(product)\n", "# --------------------------------------------------\n", "\n", "brand_motif = g.find(\"\"\"\n", " (review)-[e]->(product)\n", "\"\"\").filter(\n", " (F.col(\"e.relationship\") == \"reviews\") &\n", " (F.col(\"review.type\") == \"review\") &\n", " (F.col(\"product.type\") == \"product\") &\n", " (F.col(\"product.brand\").isNotNull()) &\n", " (F.col(\"review.rating\").isNotNull())\n", ")\n", "\n", "# --------------------------------------------------\n", "# 2️⃣ Aggregate by brand\n", "# --------------------------------------------------\n", "\n", "brand_stats = brand_motif.groupBy(\n", " F.col(\"product.brand\").alias(\"Brand\")\n", ").agg(\n", " F.avg(\"review.rating\").alias(\"AvgRating\"),\n", " F.count(\"review.id\").alias(\"ReviewCount\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 3️⃣ Apply minimum review threshold\n", "# --------------------------------------------------\n", "\n", "filtered_brands = brand_stats.filter(\n", " F.col(\"ReviewCount\") >= 100\n", ")\n", "\n", "# --------------------------------------------------\n", "# 4️⃣ Final formatting\n", "# --------------------------------------------------\n", "\n", "result_df = filtered_brands.select(\n", " F.col(\"Brand\"),\n", " F.round(F.col(\"AvgRating\"), 2).alias(\"AvgRating\"),\n", " F.col(\"ReviewCount\")\n", ").orderBy(\n", " F.desc(\"AvgRating\")\n", ").limit(200)\n", "\n", "print(\"\\nTop Brands by Average Rating (≥100 Reviews)\\n\")\n", "measure(result_df).show(200, truncate=60)\n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "ca9b4c38-4256-4ee7-ad11-db241418b6fe", "metadata": {}, "source": [ "### ===Images / Multimedia===\n", "\n", "Products with the Most Images\n", "Find products with the most marketing assets (images)." ] }, { "cell_type": "code", "execution_count": 36, "id": "2bc71ea0-2231-470d-84fa-809ae3ce96c9", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Products with the Most Images\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " ]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Time taken: 14.662 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "[Stage 790:====================================================>(199 + 1) / 200]]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+----------+------------------------------------------------------------+----------+\n", "| ASIN| Product|ImageCount|\n", "+----------+------------------------------------------------------------+----------+\n", "|B00H0LITFK|LSS Laptop 17 17.3 Skin Cover with Colorful Black Grey Fl...| 50|\n", "|B00H0LL7AO|LSS Laptop 17 17.3 Skin Cover with Colorful Earth Moon Pa...| 50|\n", "|B00H0LKW1E|LSS Laptop 17 17.3 Skin Cover with Colorful Pink Rose Flo...| 50|\n", "|B00H0LL3MQ|LSS Laptop 17 17.3 Skin Cover with Colorful Floral Vines ...| 50|\n", "|B00OBT5VAU|LSS 17 17.3 inch Laptop Padded Compartment Shoulder Messe...| 50|\n", "|B00H0LKXU4|LSS Laptop 17 17.3 Skin Cover with Colorful Blue Eyed Cat...| 50|\n", "|B00PDDO6P4|Galaxy S5 Case Hello Kitty Cute Diary Wallet Flip Synthet...| 50|\n", "|B01DDCV65Y|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00H0LL3WQ|LSS Laptop 17 17.3 Skin Cover with Colorful Cartoon Patte...| 50|\n", "|B00H0LKX4K|LSS Laptop 17 17.3 Skin Cover with Colorful Red Audi Car ...| 50|\n", "|B00J9M8RHI|LSS Laptop 17 17.3 Skin Cover with Colorful Birds and Ani...| 50|\n", "|B00H0LIT0U|LSS Laptop 17 17.3 Skin Cover with Colorful Planets Patte...| 50|\n", "|B00H0LLHXG|LSS Laptop 17 17.3 Skin Cover with Colorful Ying Yang Fan...| 50|\n", "|B00O7XZH4K|iPhone 6S / iPhone 6 Case HELLO KITTY Cute Diary Wallet F...| 50|\n", "|B00H0LII06|LSS Laptop 17 17.3 Skin Cover with Colorful Black Waves P...| 50|\n", "|B00J9M8UDE|LSS Laptop 17 17.3 Skin Cover with Colorful Blue Floral P...| 50|\n", "|B00MR44E2C| LL Womens Long Sleeve Handkerchief Hem Tunic - Made in USA| 50|\n", "|B00H0LL40C|LSS Laptop 17 17.3 Skin Cover with Colorful Abstract Purp...| 50|\n", "|B00H0LIUEA|LSS Laptop 17 17.3 Skin Cover with Colorful Pink Heart En...| 50|\n", "|B00H0LKVDI|LSS Laptop 17 17.3 Skin Cover with Colorful Black Floral ...| 50|\n", "|B01DRQ2I7G|Galaxy S7 Edge Case,Galaxy S7 Edge Wallet Case,S7 Edge Fl...| 50|\n", "|B01DRQ2844|Galaxy S7 Edge Case,Aiyze [Wrist Strap] Flip Folio [Kicks...| 50|\n", "|B00H0LIZV8|LSS Laptop 17 17.3 Skin Cover with Colorful Dolphins Patt...| 50|\n", "|B00IMHVBSI|THE MESH KING Coqueta Swimwear Women Sexy G-String Teeny ...| 50|\n", "|B00OBT7MG6|LSS 17 17.3 inch Laptop Padded Compartment Shoulder Messe...| 50|\n", "|B00H0LKTSU|LSS Laptop 17 17.3 Skin Cover with Colorful Heart Floral ...| 50|\n", "|B018M0MXRC|iPad 2/3/4 Case, Dteck(TM) Cartoon Cute Pattern PU Leathe...| 50|\n", "|B00YGU3AH0|LittleMax(TM), PU Leather Case Flip Stand Protective Auto...| 50|\n", "|B010VF1ZXO|Strap Compatible for Apple Watch Series 4/3/2/1 44mm/42mm...| 50|\n", "|B00J9M8VTC|LSS Laptop 17 17.3 Skin Cover with Colorful Black and Whi...| 50|\n", "|B010WQ7MK2|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00H0LJFY4|LSS Laptop 17 17.3 Skin Cover with Colorful Bamboo in For...| 50|\n", "|B00H0LL4AC|LSS Laptop 17 17.3 Skin Cover with Colorful Earth and Moo...| 50|\n", "|B010R0OGWK|Bands for Iwatchs 38mm/40mm Series 1/2/3/4 / Topgraph Com...| 50|\n", "|B00JUCLVBQ|LB 5x7ft Poly Fabric Rustic Barn Wood Floor Photography B...| 50|\n", "|B00H0LJ9SQ|LSS Laptop 17 17.3 Skin Cover with Colorful Daisy Flower ...| 50|\n", "|B00H0LKP9S|LSS Laptop 17 17.3 Skin Cover with Colorful Flame Racer C...| 50|\n", "|B00H0LKQZ6|LSS Laptop 17 17.3 Skin Cover with Colorful Brown Chaos F...| 50|\n", "|B011QL5CXQ|Band for Iwatch 38mm/40mm Series 1/2/3/4 / Topgraph Compa...| 50|\n", "|B00JRUC7LO|HRH Spanish Silicone Keyboard Cover Skin for MacBook Air ...| 50|\n", "|B0114NL41A|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00H0LJ9GI|LSS Laptop 17 17.3 Skin Cover with Colorful Star Fish Pat...| 50|\n", "|B013XZ33XE|iPad Case, iPad 2/3/4 Case, Dteck Multiple Viewing Angles...| 50|\n", "|B00Y397JIK|ZriEy Girl Women Pointed Toe High Heel Pumps Patent Leath...| 50|\n", "|B014BJ1LGW|Strap Compatible for Apple Watch Series 4/3/2/1 44mm/42mm...| 50|\n", "|B00H0LIXRY|LSS Laptop 17 17.3 Skin Cover with Colorful Pink Flower P...| 50|\n", "|B01GR132SM|Dteck iPad 2 Case, iPad 3 Case, iPad 4 Case, (TM) Elegant...| 50|\n", "|B01DDCVIE8|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B01DRQ2BSW|Galaxy S7 Edge Case,Aiyze Samsung Galaxy S7 Edge Case Wal...| 50|\n", "|B01CWJI5C6|Galaxy S7 Case Hello Kitty Cute Diary Wallet Flip/Synthet...| 50|\n", "|B00H0LJKOE|LSS Laptop 17 17.3 Skin Cover with Colorful Night Dragon ...| 50|\n", "|B0114NKWY0|Strap Compatible For Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B013I3RPT4|LittleMax(TM),PU Leather Case Flip Stand Protective Auto ...| 50|\n", "|B00BG4WFR0| | 50|\n", "|B00H0LL32Q|LSS Laptop 17 17.3 Skin Cover with Colorful Happy Face Pa...| 50|\n", "|B010WQ7DH4|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00YXX2YTA|HRH Thai Silicone Keyboard Cover Skin for MacBook Air 13,...| 50|\n", "|B00J9M91I2|LSS Laptop 17 17.3 Skin Cover with Colorful Paris Design ...| 50|\n", "|B00H0LJFHG|LSS Laptop 17 17.3 Skin Cover with Colorful Lady Bug Patt...| 50|\n", "|B00RGKR7OE|Dteck iPad Case for iPad 9.7" 2018/2017, Ultra Slim ...| 50|\n", "|B010NAE5WA|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00J9M8NCC|LSS Laptop 17 17.3 Skin Cover with Colorful Pink Gray Pat...| 50|\n", "|B00H0LKSTA|LSS Laptop 17 17.3 Skin Cover with Colorful Butterfly Flo...| 50|\n", "|B014BJ12BQ|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00H0LKY2Q|LSS Laptop 17 17.3 Skin Cover with Colorful Vines Floral ...| 50|\n", "|B00FVTJWB8|H2H Mens Slim Fit Turtleneck Pullover Sweaters Basic Desi...| 50|\n", "|B00H0LIL08|LSS Laptop 17 17.3 Skin Cover with Colorful Zebra Print P...| 50|\n", "|B00KB3PZBA|LSS Laptop 17 17.3 Skin Cover with Colorful White Butterf...| 50|\n", "|B00H0LJEYK|LSS Laptop 17 17.3 Skin Cover with Colorful Pink Plumeria...| 50|\n", "|B01837KI0S|Fire HD 8 Case,PU Synthetic Leather Case Flip Cover with ...| 50|\n", "|B01DIUQ444|HRH Chinese Flag Laptop Body Shell Protective Hard Case f...| 50|\n", "|B01DZMNRPE|Fire HD 8 Case,PU Synthetic Leather Case Flip Cover with ...| 50|\n", "|B01DRQ2B5K|Galaxy S7 Case,Aiyze Samsung Galaxy S7 Case Wallet PU Lea...| 50|\n", "|B00H0LKYFS|LSS Laptop 17 17.3 Skin Cover with Colorful Blue Floral P...| 50|\n", "|B00H0LIVTY|LSS Laptop 17 17.3 Skin Cover with Colorful Music Notes P...| 50|\n", "|B01DCDJH6Y| | 50|\n", "|B010NVV7T8|Band Replacement For Iwatch 44mm/42mm & Cisland Leath...| 50|\n", "|B00H0LL6C8|LSS Laptop 17 17.3 Skin Cover with Colorful Pink Heart Pa...| 50|\n", "|B00OBT46NI|LSS 17 17.3 inch Laptop Padded Compartment Shoulder Messe...| 50|\n", "|B0117SY6XK|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B01CWMPCL0|Galaxy S7 Edge Case Hello Kitty Cute Diary Wallet Flip Sy...| 50|\n", "|B00H0LLHJK|LSS Laptop 17 17.3 Skin Cover with Colorful Girl Pattern ...| 50|\n", "|B00H0LKM32|LSS Laptop 17 17.3 Skin Cover with Colorful Purple Flower...| 50|\n", "|B00YSV92MO| The North Face Recon Backpack| 50|\n", "|B00H0LLIKI|LSS Laptop 17 17.3 Skin Cover with Colorful Skeloton Hand...| 50|\n", "|B00SB6EVLY| | 50|\n", "|B00H4WO18E| | 50|\n", "|B00KB3PVQE|LSS Laptop 17 17.3 Skin Cover with Colorful New Hedgehog ...| 50|\n", "|B010NVVMLG|Band Replacement For Iwatch 42mm/44mm & Cisland Leath...| 50|\n", "|B00139U3G8|Cashmere Pashmina Group: Solid Pashmina Shawl, Scarf, Wra...| 50|\n", "|B018DHQ988|LittleMax(TM),PU Leather Case Flip Stand Protective Auto ...| 50|\n", "|B00H0LJ4PO|LSS Laptop 17 17.3 Skin Cover with Colorful Cool Car in T...| 50|\n", "|B00H0LJ85K|LSS Laptop 17 17.3 Skin Cover with Colorful Forest Butter...| 50|\n", "|B000BB4NHE|Happy Feet Baby Slippers - up to 9 Months - Officially Li...| 50|\n", "|B00VHOAWX8|Dteck iPad Case for iPad 9.7" 2018/2017, Ultra Slim ...| 50|\n", "|B00H0LKUIO|LSS Laptop 17 17.3 Skin Cover with Colorful Penguins Patt...| 50|\n", "|B00H0LKS86|LSS Laptop 17 17.3 Skin Cover with Colorful Dolphins Patt...| 50|\n", "|B010NAEPO8|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00H0LJ6W0|LSS Laptop 17 17.3 Skin Cover with Colorful Blue Monster ...| 50|\n", "|B00J9M8DVI|LSS Laptop 17 17.3 Skin Cover with Colorful Hedgehog Patt...| 50|\n", "|B00H0LJ79M|LSS Laptop 17 17.3 Skin Cover with Colorful Flames Patter...| 50|\n", "|B00KB3PWT0|LSS Laptop 17 17.3 Skin Cover with Colorful Tornado Swirl...| 50|\n", "|B00H0LIH4I|LSS Laptop 17 17.3 Skin Cover with Colorful Black Cross P...| 50|\n", "|B00H0LKW8M|LSS Laptop 17 17.3 Skin Cover with Colorful Eagle Pattern...| 50|\n", "|B00OBT87LK|LSS 17 17.3 inch Laptop Padded Compartment Shoulder Messe...| 50|\n", "|B017IIKUJW|Wedding Planning Women's A Line Short Knee Length Tutu Tu...| 50|\n", "|B0117SY8AG|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00OJZ4Y40|Rotibox Bluetooth Beanie Hat, Winter Outdoor Sport Knit C...| 50|\n", "|B0117SY1LC|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00UGH997M|LSS Laptop 17 17.3 Skin Cover with Colorful World Landmar...| 50|\n", "|B010NVU2SU|Band Replacement For Iwatch 44mm/42mm & Cisland Leath...| 50|\n", "|B00H0LIZIG|LSS Laptop 17 17.3 Skin Cover with Colorful Red Roses Pat...| 50|\n", "|B01D4TLW28|LEXUS SC430 / WHITE PEARL CRYSTAL SHINE - 065 / COLOR N D...| 50|\n", "|B00H0LLKV0|LSS Laptop 17 17.3 Skin Cover with Colorful Nuclear Sign ...| 50|\n", "|B0104520QC|Band Replacement for Iwatch 42mm/44mm & Cisland Leath...| 50|\n", "|B00NNNJBRE|LB 10x10ft Brick Wall lavender Thin Vinyl Customized Back...| 50|\n", "|B00H0LJJX6|LSS Laptop 17 17.3 Skin Cover with Colorful Futuristic Tr...| 50|\n", "|B00WLIH82C| JanSport Right Pack Laptop Backpack| 50|\n", "|B00GS1DL6K|#20 Solid Gold - Needloft Craft Cord 2 Pack 40 Yards (2x2...| 50|\n", "|B00H0LLGKK|LSS Laptop 17 17.3 Skin Cover with Colorful Tank and Heli...| 50|\n", "|B01837MERS|Fire HD 8 Case,PU Synthetic Leather Case Flip Cover with ...| 50|\n", "|B00J9M8Z8Y|LSS Laptop 17 17.3 Skin Cover with Colorful Cassette Play...| 50|\n", "|B014NBWSA6|Galaxy S6 Edge Plus Case Hello Kitty Cute Diary Wallet Fl...| 50|\n", "|B00H0LIZ54|LSS Laptop 17 17.3 Skin Cover with Colorful Skull Pattern...| 50|\n", "|B00H0LJC0G|LSS Laptop 17 17.3 Skin Cover with Colorful Purple Butter...| 50|\n", "|B01FRW179S| Mryok Replacement Lenses for Oakley Radar Path - Options| 50|\n", "|B00H0LKNA4|LSS Laptop 17 17.3 Skin Cover with Colorful Dragon Patter...| 50|\n", "|B01D56HPXK|FORD AMERICA FLEX / TUXEDO BLACK MET - UH / COLOR N DRIVE...| 50|\n", "|B00PB2J6C0|Galaxy Note4 Case HELLO KITTY Cute Diary Wallet Flip Synt...| 50|\n", "|B00H0LKZU2|LSS Laptop 17 17.3 Skin Cover with Colorful Red Sign Patt...| 50|\n", "|B00H0LIW6Q|LSS Laptop 17 17.3 Skin Cover with Colorful Colorful Flor...| 50|\n", "|B010NAE8CM|Strap Compatible for Apple Watch Series 4/3/2/1 44mm/42mm...| 50|\n", "|B0114NLAX2|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00H0LL0N8|LSS Laptop 17 17.3 Skin Cover with Colorful Police Gun Pa...| 50|\n", "|B00H0LIY3M|LSS Laptop 17 17.3 Skin Cover with Colorful Sparkaling Bl...| 50|\n", "|B010MC161M|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B01GR134SK|iPad 2 Case, iPad 3 Case, iPad 4 Case, Dteck(TM) Elegant ...| 50|\n", "|B0117SY5B8|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00H0LIXJW|LSS Laptop 17 17.3 Skin Cover with Colorful Baby Cat Patt...| 50|\n", "|B01837MDAG|Fire HD 8 Case,PU Synthetic Leather Case Flip Cover with ...| 50|\n", "|B017V6HDN2| LL Womens Long Sleeve Handkerchief Hem Tunic - Made in USA| 50|\n", "|B00VHTXKKK|Galaxy S6 Edge Case Hello Kitty Cute Diary Wallet Flip Sy...| 50|\n", "|B01DRQ27BI|Galaxy S7 Edge Case,[Wallet Case]Aiyze Samsung Galaxy S7 ...| 50|\n", "|B01BKOFQ08|Casual T Shirt Dress for Women Flowy Tunic Dress with Poc...| 50|\n", "|B018WUPVVC|DRESSTELLS Vintage 1950s Rockabilly Polka Dots Audrey Dre...| 50|\n", "|B00H0LJ68O|LSS Laptop 17 17.3 Skin Cover with Colorful Night Stars P...| 50|\n", "|B014BJ1O2S|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00H0LHQ4A|LSS Laptop 17 17.3 Skin Cover with Colorful Floral Patter...| 50|\n", "|B019HYC89U| | 50|\n", "|B010NVUE4C|Band Replacement for Iwatch 44mm/42mm & Cisland Leath...| 50|\n", "|B00H0LL6ZK| | 50|\n", "|B00H0LLNOE| Laptop Skins (Reaper Skull.)| 50|\n", "|B00H0LL44S|LSS Laptop 17 17.3 Skin Cover with Colorful Purple Butter...| 50|\n", "|B00H0LJ9ZO|LSS Laptop 17 17.3 Skin Cover with Colorful Earth Pattern...| 50|\n", "|B00H0LL916|LSS Laptop 17 17.3 Skin Cover with Colorful Abstract Flor...| 50|\n", "|B00J9M8QEM|LSS Laptop 17 17.3 Skin Cover with Colorful Purple Butter...| 50|\n", "|B00H0LLGD2|LSS Laptop 17 17.3 Skin Cover with Colorful Shooting Army...| 50|\n", "|B00H0LJGCK|LSS Laptop 17 17.3 Skin Cover with Colorful Giraffe Print...| 50|\n", "|B00H0LLF92|LSS Laptop 17 17.3 Skin Cover with Colorful Black and Whi...| 50|\n", "|B00H0LJCJ2|LSS Laptop 17 17.3 Skin Cover with Colorful Red Secretive...| 50|\n", "|B010NAEFZC|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B00H0LKMK0|LSS Laptop 17 17.3 Skin Cover with Colorful Pink Flower F...| 50|\n", "|B00H0LKRDM|LSS Laptop 17 17.3 Skin Cover with Colorful USA Eagle Pat...| 50|\n", "|B0108LME9A|Strap for Iwatch 38mm/40mm Series 1/2/3/4 / Topgraph Comp...| 50|\n", "|B00H0LLLQE|LSS Laptop 17 17.3 Skin Cover with Colorful Blue Moon Sce...| 50|\n", "|B00H0LIXWY|LSS Laptop 17 17.3 Skin Cover with Colorful Flower Tunnel...| 50|\n", "|B00H0LJD9G|LSS Laptop 17 17.3 Skin Cover with Colorful Egyptian Sign...| 50|\n", "|B00ZBZACIY|Galaxy Tab A 10.1 Case, T580 Case, Dteck Slim Fit Folio S...| 50|\n", "|B00H0LIVG2|LSS Laptop 17 17.3 Skin Cover with Colorful Red Dragon Pa...| 50|\n", "|B00H0LL056|LSS Laptop 17 17.3 Skin Cover with Colorful Ying Yang Pat...| 50|\n", "|B011IV6AQC|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B01AUNLMIA| LL Womens Long Sleeve Handkerchief Hem Tunic - Made in USA| 50|\n", "|B00H0LJ53U|LSS Laptop 17 17.3 Skin Cover with Colorful Lotus Pond Fl...| 50|\n", "|B00GFC0BWE|Art to Frames Double-Multimat-692-41/89-FRBW26079 Collage...| 50|\n", "|B00H0LL754|LSS Laptop 17 17.3 Skin Cover with Colorful Dark Ghost Zh...| 50|\n", "|B00H0LJBDO|LSS Laptop 17 17.3 Skin Cover with Colorful Clock Butterf...| 50|\n", "|B01D3C2888|Suzuki SAMURAI/WORCESTER BLUE MET - 24U/COLOR N DRIVE TOU...| 50|\n", "|B010MC1TF0|Strap Compatible for Apple Watch Series 4/3/2/1 44mm/42mm...| 50|\n", "|B00H0LJI6O|LSS Laptop 17 17.3 Skin Cover with Colorful Hawaiian Para...| 50|\n", "|B00139U3HW|Cashmere Pashmina Group: Solid Pashmina Shawl, Scarf, Wra...| 50|\n", "|B01126SZSO|FOR U DESIGNS 18/20/24/28 Inch Creative Color Printed Lug...| 50|\n", "|B01D19A9Y8|KAUKKO Laptop Outdoor Backpack, Travel Hiking& Campin...| 50|\n", "|B00H0LL16E|LSS Laptop 17 17.3 Skin Cover with Colorful Moon Pattern ...| 50|\n", "|B00H0LJ6HA|LSS Laptop 17 17.3 Skin Cover with Colorful Almond Trees ...| 50|\n", "|B00H0LKPQQ|LSS Laptop 17 17.3 Skin Cover with Colorful Butterfly Yin...| 50|\n", "|B00H0LJIQY|LSS Laptop 17 17.3 Skin Cover with Colorful Peace Signs P...| 50|\n", "|B00H0LIZBI|LSS Laptop 17 17.3 Skin Cover with Colorful Blue Leopard ...| 50|\n", "|B0117SYCHA|Strap Compatible for Apple Watch Series 4/3/2/1 38mm/40mm...| 50|\n", "|B01GBCJXD0|YMIX Plastic Cover Snap on Hard Protective Case for MacB...| 49|\n", "|B00RMWEMA8|KAUKKO Laptop Outdoor Backpack, Travel Hiking& Campin...| 49|\n", "|B01CJ2XOY0|LG G3 Case,EMAXELER Stylish Wallet Case [Kickstand Flip C...| 49|\n", "|B00YIAF1TS|TheMogan Women's Spaghetti Strap Tank Top Crop Cami Bra N...| 49|\n", "|B00QGRHGUS|Dteck iPad Mini 1/2/3/4 Case - Lightweight Slim Fit PU Le...| 49|\n", "|B019GHULVU|Laeacco 5x7ft Vinyl Photography Background Wood Floor and...| 49|\n", "|B0188YQV1G|Sea Island Ocean Beach Scenery Laeacco 3x5ft Vinyl Photog...| 49|\n", "|B014QA8ZD8| | 49|\n", "|B014BJ1O00|Strap Compatible for Apple Watch Series 4/3/2/1 44mm/42mm...| 49|\n", "|B01DFK6M5I|Xpand No Tie Shoelaces System with Elastic Laces - One Si...| 49|\n", "|B010NV45RE|Bands Replacement for Iwatch 38mm/40mm & Cisland Leat...| 49|\n", "|B018HL40OU|Dteck iPad Mini 4 Case with Stand Function [Card Holder] ...| 49|\n", "+----------+------------------------------------------------------------+----------+\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "from pyspark.sql import functions as F\n", "\n", "# --------------------------------------------------\n", "# 1️⃣ Motif: (product)-[:has_product_image]->(image)\n", "# --------------------------------------------------\n", "\n", "image_motif = g.find(\"\"\"\n", " (product)-[e]->(image)\n", "\"\"\").filter(\n", " (F.col(\"e.relationship\") == \"has_product_image\") &\n", " (F.col(\"product.type\") == \"product\") &\n", " (F.col(\"image.type\") == \"image\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 2️⃣ Count images per product\n", "# --------------------------------------------------\n", "\n", "image_stats = image_motif.groupBy(\n", " F.col(\"product.id\").alias(\"ASIN\"),\n", " F.col(\"product.title\").alias(\"title\")\n", ").agg(\n", " F.count(\"image.id\").alias(\"ImageCount\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 3️⃣ Final formatting\n", "# --------------------------------------------------\n", "\n", "result_df = image_stats.select(\n", " F.col(\"ASIN\"),\n", " F.coalesce(F.col(\"title\"), F.col(\"ASIN\")).alias(\"Product\"),\n", " F.col(\"ImageCount\")\n", ").orderBy(\n", " F.desc(\"ImageCount\")\n", ").limit(200)\n", "\n", "print(\"\\nProducts with the Most Images\\n\")\n", "measure(result_df).show(200, truncate=60)\n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "a85de931-6951-43a7-9815-5c0e5a21f79c", "metadata": {}, "source": [ "### Images reused by multiple products (Image-Centric View)\n", "For each Image, count how many distinct products are using it (i.e., shared images across products)." ] }, { "cell_type": "code", "execution_count": 37, "id": "f0be193a-aa23-456f-8ec3-a2810352fe41", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Images Reused by Multiple Products\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " 00]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Time taken: 12.424 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "[Stage 852:================================================> (187 + 13) / 200]]0]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+------------------------------------------------------------+------------+\n", "| ImageURL|ProductCount|\n", "+------------------------------------------------------------+------------+\n", "|https://images-na.ssl-images-amazon.com/images/I/31MpUA8m...| 48065|\n", "|https://images-na.ssl-images-amazon.com/images/I/31CoVdns...| 41399|\n", "|https://images-na.ssl-images-amazon.com/images/I/31BDm2Vq...| 15985|\n", "|https://m.media-amazon.com/images/G/01/Merch/SizeChart/AT...| 6526|\n", "|https://images-na.ssl-images-amazon.com/images/I/61uA4icL...| 4704|\n", "|https://images-na.ssl-images-amazon.com/images/I/51iG-3pg...| 4607|\n", "|https://images-na.ssl-images-amazon.com/images/I/41dp1H%2...| 4091|\n", "|https://images-na.ssl-images-amazon.com/images/I/51YJ9F64...| 3174|\n", "|https://images-na.ssl-images-amazon.com/images/I/41coxNoc...| 2676|\n", "|https://images-na.ssl-images-amazon.com/images/I/51cPvDJw...| 2595|\n", "|https://images-na.ssl-images-amazon.com/images/I/51ZLkpKo...| 2593|\n", "|https://images-na.ssl-images-amazon.com/images/I/31PRWjyM...| 2437|\n", "|https://images-na.ssl-images-amazon.com/images/I/31BDm2Vq...| 2206|\n", "|https://images-na.ssl-images-amazon.com/images/I/51RBia8J...| 2144|\n", "|https://images-na.ssl-images-amazon.com/images/I/51UV2RNb...| 2088|\n", "|https://images-na.ssl-images-amazon.com/images/I/41nGTxtA...| 2084|\n", "|https://images-na.ssl-images-amazon.com/images/I/41OaSYCP...| 2084|\n", "|https://images-na.ssl-images-amazon.com/images/I/41jiyY7b...| 2013|\n", "|https://images-na.ssl-images-amazon.com/images/I/41rw8uaM...| 1793|\n", "|https://images-na.ssl-images-amazon.com/images/I/51lUBVZH...| 1792|\n", "|https://images-na.ssl-images-amazon.com/images/I/414Lu8V1...| 1789|\n", "|https://images-na.ssl-images-amazon.com/images/I/41WuZ0yg...| 1786|\n", "|https://images-na.ssl-images-amazon.com/images/I/41fFGI9B...| 1784|\n", "|https://images-na.ssl-images-amazon.com/images/I/51EAUe4O...| 1737|\n", "|https://images-na.ssl-images-amazon.com/images/I/31vQNznz...| 1737|\n", "|https://images-na.ssl-images-amazon.com/images/I/31qBkPIy...| 1703|\n", "|https://images-na.ssl-images-amazon.com/images/I/417R1op2...| 1622|\n", "|https://images-na.ssl-images-amazon.com/images/I/41Na3gbZ...| 1543|\n", "|https://images-na.ssl-images-amazon.com/images/I/21WZFAFJ...| 1520|\n", "|https://images-na.ssl-images-amazon.com/images/I/51TkpEBh...| 1467|\n", "|https://images-na.ssl-images-amazon.com/images/I/31s5ApiM...| 1456|\n", "|https://images-na.ssl-images-amazon.com/images/I/312cH9ND...| 1449|\n", "|https://images-na.ssl-images-amazon.com/images/I/411E36Sp...| 1446|\n", "|https://images-na.ssl-images-amazon.com/images/I/51-2FxLS...| 1443|\n", "|https://images-na.ssl-images-amazon.com/images/I/515jI9W3...| 1430|\n", "|https://images-na.ssl-images-amazon.com/images/I/51tiTDuQ...| 1412|\n", "|https://images-na.ssl-images-amazon.com/images/I/51M6BI5J...| 1412|\n", "|https://images-na.ssl-images-amazon.com/images/I/51hem7jJ...| 1412|\n", "|https://images-na.ssl-images-amazon.com/images/I/51iI0Fxj...| 1412|\n", "|https://images-na.ssl-images-amazon.com/images/I/51-DDqAz...| 1412|\n", "|https://images-na.ssl-images-amazon.com/images/I/51nyV1rX...| 1405|\n", "|https://images-na.ssl-images-amazon.com/images/I/31qIwONt...| 1397|\n", "|https://images-na.ssl-images-amazon.com/images/I/41W2G9Bf...| 1356|\n", "|https://images-na.ssl-images-amazon.com/images/I/419GzkUt...| 1335|\n", "|https://images-na.ssl-images-amazon.com/images/I/51WZDACm...| 1333|\n", "|https://images-na.ssl-images-amazon.com/images/I/41c9WeI0...| 1333|\n", "|https://images-na.ssl-images-amazon.com/images/I/413N%2Be...| 1330|\n", "|https://images-na.ssl-images-amazon.com/images/I/31xbySGz...| 1306|\n", "|https://images-na.ssl-images-amazon.com/images/I/515uf%2B...| 1278|\n", "|https://images-na.ssl-images-amazon.com/images/I/41G0sEzg...| 1269|\n", "|https://images-na.ssl-images-amazon.com/images/I/41bhy4N8...| 1238|\n", "|https://images-na.ssl-images-amazon.com/images/I/21WZFAFJ...| 1235|\n", "|https://images-na.ssl-images-amazon.com/images/I/51yPB4j-...| 1221|\n", "|https://images-na.ssl-images-amazon.com/images/I/51SzfFDa...| 1214|\n", "|https://images-na.ssl-images-amazon.com/images/I/41P3HF62...| 1178|\n", "|https://images-na.ssl-images-amazon.com/images/I/51D1-fc6...| 1175|\n", "|https://images-na.ssl-images-amazon.com/images/I/315y-u8V...| 1172|\n", "|https://images-na.ssl-images-amazon.com/images/I/51Q9j%2B...| 1171|\n", "|https://images-na.ssl-images-amazon.com/images/I/61drkiul...| 1169|\n", "|https://images-na.ssl-images-amazon.com/images/I/51wSfVHC...| 1164|\n", "|https://images-na.ssl-images-amazon.com/images/I/31abMEKZ...| 1162|\n", "|https://images-na.ssl-images-amazon.com/images/I/51Eo5XLC...| 1156|\n", "|https://images-na.ssl-images-amazon.com/images/I/41QU%2Bl...| 1150|\n", "|https://images-na.ssl-images-amazon.com/images/I/41llNFdt...| 1149|\n", "|https://images-na.ssl-images-amazon.com/images/I/51ZN-3Uv...| 1148|\n", "|https://images-na.ssl-images-amazon.com/images/I/515kAZFx...| 1147|\n", "|https://images-na.ssl-images-amazon.com/images/I/41Dr%2BE...| 1143|\n", "|https://images-na.ssl-images-amazon.com/images/I/5178wkj-...| 1139|\n", "|https://images-na.ssl-images-amazon.com/images/I/512ymSqh...| 1137|\n", "|https://images-na.ssl-images-amazon.com/images/I/510Xa%2B...| 1136|\n", "|https://images-na.ssl-images-amazon.com/images/I/51lmT9ji...| 1124|\n", "|https://images-na.ssl-images-amazon.com/images/I/61JlSOs-...| 1119|\n", "|https://images-na.ssl-images-amazon.com/images/I/51mXr6Y1...| 1090|\n", "|https://images-na.ssl-images-amazon.com/images/I/41XzuJh-...| 1080|\n", "|https://images-na.ssl-images-amazon.com/images/I/41NP8tvh...| 1075|\n", "|https://images-na.ssl-images-amazon.com/images/I/41zJxsMe...| 1074|\n", "|https://images-na.ssl-images-amazon.com/images/I/41XwStyz...| 1061|\n", "|https://images-na.ssl-images-amazon.com/images/I/511wGwpJ...| 1050|\n", "|https://images-na.ssl-images-amazon.com/images/I/51ZwQkLF...| 1048|\n", "|https://images-na.ssl-images-amazon.com/images/I/5175oh4B...| 1043|\n", "|https://images-na.ssl-images-amazon.com/images/I/512m9mQa...| 1042|\n", "|https://images-na.ssl-images-amazon.com/images/I/41V3oOkn...| 1036|\n", "|https://images-na.ssl-images-amazon.com/images/I/51zx12Ej...| 1017|\n", "|https://images-na.ssl-images-amazon.com/images/I/612oY42Q...| 1016|\n", "|https://images-na.ssl-images-amazon.com/images/I/41t5yO0J...| 1011|\n", "|https://images-na.ssl-images-amazon.com/images/I/511t19om...| 999|\n", "|https://images-na.ssl-images-amazon.com/images/I/51FMPz24...| 992|\n", "|https://images-na.ssl-images-amazon.com/images/I/61eDZguR...| 978|\n", "|https://images-na.ssl-images-amazon.com/images/I/41CivmS%...| 974|\n", "|https://images-na.ssl-images-amazon.com/images/I/4111mVAu...| 972|\n", "|https://images-na.ssl-images-amazon.com/images/I/511omQLJ...| 968|\n", "|https://images-na.ssl-images-amazon.com/images/I/51FvjpBw...| 961|\n", "|https://images-na.ssl-images-amazon.com/images/I/315w8aVW...| 958|\n", "|https://images-na.ssl-images-amazon.com/images/I/51ZBcJZL...| 946|\n", "|https://images-na.ssl-images-amazon.com/images/I/21LO1x4C...| 945|\n", "|https://images-na.ssl-images-amazon.com/images/I/51eSrs1G...| 943|\n", "|https://images-na.ssl-images-amazon.com/images/I/41lz7Gsk...| 935|\n", "|https://images-na.ssl-images-amazon.com/images/I/31JM3R8S...| 935|\n", "|https://images-na.ssl-images-amazon.com/images/I/31eYgct2...| 934|\n", "|https://images-na.ssl-images-amazon.com/images/I/314t8KRH...| 914|\n", "|https://images-na.ssl-images-amazon.com/images/I/51GFdlkm...| 907|\n", "|https://images-na.ssl-images-amazon.com/images/I/51qgFs2a...| 907|\n", "|https://images-na.ssl-images-amazon.com/images/I/41q4%2BO...| 907|\n", "|https://images-na.ssl-images-amazon.com/images/I/51a4dU3Z...| 907|\n", "|https://images-na.ssl-images-amazon.com/images/I/41T2N%2B...| 907|\n", "|https://images-na.ssl-images-amazon.com/images/I/41YzLvrt...| 894|\n", "|https://images-na.ssl-images-amazon.com/images/I/51N7E-G4...| 893|\n", "|https://images-na.ssl-images-amazon.com/images/I/5198f2cJ...| 891|\n", "|https://images-na.ssl-images-amazon.com/images/I/51KiM5VI...| 890|\n", "|https://images-na.ssl-images-amazon.com/images/I/519omXaP...| 885|\n", "|https://images-na.ssl-images-amazon.com/images/I/613A9-QB...| 884|\n", "|https://images-na.ssl-images-amazon.com/images/I/41qy4pdl...| 880|\n", "|https://images-na.ssl-images-amazon.com/images/I/41WniSHa...| 879|\n", "|https://images-na.ssl-images-amazon.com/images/I/41S-qaMN...| 867|\n", "|https://images-na.ssl-images-amazon.com/images/I/41PmkMB2...| 859|\n", "|https://images-na.ssl-images-amazon.com/images/I/51rOsmdm...| 848|\n", "|https://images-na.ssl-images-amazon.com/images/I/41xc0ypd...| 843|\n", "|https://images-na.ssl-images-amazon.com/images/I/411wWi3F...| 840|\n", "|https://images-na.ssl-images-amazon.com/images/I/31mKidVH...| 840|\n", "|https://images-na.ssl-images-amazon.com/images/I/61FtiZzv...| 836|\n", "|https://images-na.ssl-images-amazon.com/images/I/51NiQxml...| 835|\n", "|https://images-na.ssl-images-amazon.com/images/I/41BCwOTo...| 829|\n", "|https://images-na.ssl-images-amazon.com/images/I/41877ckq...| 825|\n", "|https://images-na.ssl-images-amazon.com/images/I/31YYx7FR...| 818|\n", "|https://images-na.ssl-images-amazon.com/images/I/7155Uiyx...| 801|\n", "|https://images-na.ssl-images-amazon.com/images/I/51UaflyK...| 797|\n", "|https://images-na.ssl-images-amazon.com/images/I/51Pz0SHA...| 792|\n", "|https://images-na.ssl-images-amazon.com/images/I/31kIyFpp...| 788|\n", "|https://images-na.ssl-images-amazon.com/images/I/51ORrEiX...| 784|\n", "|https://images-na.ssl-images-amazon.com/images/I/51YTONOR...| 783|\n", "|https://images-na.ssl-images-amazon.com/images/I/51IlVeAm...| 783|\n", "|https://images-na.ssl-images-amazon.com/images/I/51eu0BXo...| 782|\n", "|https://images-na.ssl-images-amazon.com/images/I/31MPoevJ...| 777|\n", "|https://images-na.ssl-images-amazon.com/images/I/31xqjQ2A...| 771|\n", "|https://images-na.ssl-images-amazon.com/images/I/51nC6-ZM...| 770|\n", "|https://images-na.ssl-images-amazon.com/images/I/61L321Yz...| 764|\n", "|https://images-na.ssl-images-amazon.com/images/I/51X5j2Jj...| 761|\n", "|https://images-na.ssl-images-amazon.com/images/I/31pPsLIg...| 758|\n", "|https://images-na.ssl-images-amazon.com/images/I/41-dMlBM...| 757|\n", "|https://images-na.ssl-images-amazon.com/images/I/4115zzHY...| 754|\n", "|https://images-na.ssl-images-amazon.com/images/I/31xqjQ2A...| 745|\n", "|https://images-na.ssl-images-amazon.com/images/I/51nMpo-Z...| 738|\n", "|https://images-na.ssl-images-amazon.com/images/I/41VASyHs...| 733|\n", "|https://images-na.ssl-images-amazon.com/images/I/41v5Ae1A...| 733|\n", "|https://images-na.ssl-images-amazon.com/images/I/41pwvjuP...| 733|\n", "|https://images-na.ssl-images-amazon.com/images/I/41k3acJO...| 733|\n", "|https://images-na.ssl-images-amazon.com/images/I/41X0RrSQ...| 732|\n", "|https://images-na.ssl-images-amazon.com/images/I/517DLC1y...| 732|\n", "|https://images-na.ssl-images-amazon.com/images/I/41OgrLpa...| 732|\n", "|https://images-na.ssl-images-amazon.com/images/I/116f%2BB...| 731|\n", "|https://images-na.ssl-images-amazon.com/images/I/41oq8Eud...| 730|\n", "|https://images-na.ssl-images-amazon.com/images/I/518geEXb...| 728|\n", "|https://images-na.ssl-images-amazon.com/images/I/41ICy4hl...| 715|\n", "|https://images-na.ssl-images-amazon.com/images/I/31M9R%2B...| 714|\n", "|https://images-na.ssl-images-amazon.com/images/I/41X4ezE2...| 713|\n", "|https://images-na.ssl-images-amazon.com/images/I/41HsfMkh...| 710|\n", "|https://images-na.ssl-images-amazon.com/images/I/41nbLRVX...| 707|\n", "|https://images-na.ssl-images-amazon.com/images/I/51xv2tVr...| 705|\n", "|https://images-na.ssl-images-amazon.com/images/I/51ZFTTi7...| 704|\n", "|https://images-na.ssl-images-amazon.com/images/I/5138URAf...| 704|\n", "|https://images-na.ssl-images-amazon.com/images/I/41LZuvMW...| 702|\n", "|https://images-na.ssl-images-amazon.com/images/I/51uDJ7Y3...| 700|\n", "|https://images-na.ssl-images-amazon.com/images/I/41yOsVNE...| 697|\n", "|https://images-na.ssl-images-amazon.com/images/I/51n3w6Z5...| 692|\n", "|https://images-na.ssl-images-amazon.com/images/I/51Xgte%2...| 683|\n", "|https://images-na.ssl-images-amazon.com/images/I/21IrT3QA...| 678|\n", "|https://images-na.ssl-images-amazon.com/images/I/518geEXb...| 674|\n", "|https://images-na.ssl-images-amazon.com/images/I/31%2B1F1...| 673|\n", "|https://images-na.ssl-images-amazon.com/images/I/51f43aXL...| 673|\n", "|https://images-na.ssl-images-amazon.com/images/I/41%2BrSa...| 671|\n", "|https://images-na.ssl-images-amazon.com/images/I/418QrxWM...| 670|\n", "|https://images-na.ssl-images-amazon.com/images/I/31P6ABLg...| 669|\n", "|https://images-na.ssl-images-amazon.com/images/I/21ucXizq...| 669|\n", "|https://images-na.ssl-images-amazon.com/images/I/21-UuxEV...| 668|\n", "|https://images-na.ssl-images-amazon.com/images/I/41WiJOaP...| 666|\n", "|https://images-na.ssl-images-amazon.com/images/I/51G6m2px...| 664|\n", "|https://images-na.ssl-images-amazon.com/images/I/61DulS7B...| 664|\n", "|https://images-na.ssl-images-amazon.com/images/I/41SrR1rL...| 662|\n", "|https://images-na.ssl-images-amazon.com/images/I/41X-1x-3...| 659|\n", "|https://images-na.ssl-images-amazon.com/images/I/41ZADqWJ...| 656|\n", "|https://images-na.ssl-images-amazon.com/images/I/31U3GkSI...| 652|\n", "|https://images-na.ssl-images-amazon.com/images/I/51NLi3T9...| 652|\n", "|https://images-na.ssl-images-amazon.com/images/I/61j-YCrq...| 649|\n", "|https://images-na.ssl-images-amazon.com/images/I/41rIYRzo...| 647|\n", "|https://images-na.ssl-images-amazon.com/images/I/61bGRCCz...| 646|\n", "|https://images-na.ssl-images-amazon.com/images/I/611uU90u...| 646|\n", "|https://images-na.ssl-images-amazon.com/images/I/41nbLRVX...| 644|\n", "|https://images-na.ssl-images-amazon.com/images/I/31W7hPjQ...| 644|\n", "|https://images-na.ssl-images-amazon.com/images/I/51sDP8bd...| 644|\n", "|https://images-na.ssl-images-amazon.com/images/I/41kk3eML...| 642|\n", "|https://images-na.ssl-images-amazon.com/images/I/31r3lQ%2...| 640|\n", "|https://images-na.ssl-images-amazon.com/images/I/41WiJOaP...| 639|\n", "|https://images-na.ssl-images-amazon.com/images/I/41zJf8QQ...| 638|\n", "|https://images-na.ssl-images-amazon.com/images/I/51zYMGTT...| 637|\n", "|https://images-na.ssl-images-amazon.com/images/I/41-eTdVL...| 635|\n", "|https://images-na.ssl-images-amazon.com/images/I/31%2BUEb...| 628|\n", "|https://images-na.ssl-images-amazon.com/images/I/41W8SHuG...| 626|\n", "|https://images-na.ssl-images-amazon.com/images/I/51-z69oz...| 625|\n", "|https://images-na.ssl-images-amazon.com/images/I/51Uv0th5...| 624|\n", "|https://images-na.ssl-images-amazon.com/images/I/31DDee6x...| 621|\n", "+------------------------------------------------------------+------------+\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "from pyspark.sql import functions as F\n", "\n", "# --------------------------------------------------\n", "# 1️⃣ Motif: (product)-[:has_product_image]->(image)\n", "# --------------------------------------------------\n", "\n", "image_motif = g.find(\"\"\"\n", " (product)-[e]->(image)\n", "\"\"\").filter(\n", " (F.col(\"e.relationship\") == \"has_product_image\") &\n", " (F.col(\"product.type\") == \"product\") &\n", " (F.col(\"image.type\") == \"image\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 2️⃣ Count DISTINCT products per image\n", "# --------------------------------------------------\n", "\n", "image_stats = image_motif.groupBy(\n", " F.col(\"image.url\").alias(\"ImageURL\")\n", ").agg(\n", " F.countDistinct(\"product.id\").alias(\"ProductCount\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 3️⃣ Order and limit\n", "# --------------------------------------------------\n", "\n", "result_df = image_stats.orderBy(\n", " F.desc(\"ProductCount\")\n", ").limit(200)\n", "\n", "print(\"\\nImages Reused by Multiple Products\\n\")\n", "measure(result_df).show(200, truncate=60)\n" ] }, { "attachments": {}, "cell_type": "markdown", "id": "e74dad0a-35ad-46be-ab1a-c7a619f4d621", "metadata": {}, "source": [ "### Find the top 200 reviews with the highest helpful votes, returning each review’s reviewer, rating, number of helpful votes, and number of images attached.\n", "This helps us check whether reviews containing more images tend to be marked as more helpful." ] }, { "cell_type": "code", "execution_count": 38, "id": "60f7311d-2bce-4370-9cbe-1ae5cc7357b4", "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "\n", "Top 200 Reviews with Highest Helpful Votes\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] }, { "name": "stdout", "output_type": "stream", "text": [ "Time taken: 19.234 seconds\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "[Stage 916:===================================================> (195 + 5) / 200]]]]1]" ] }, { "name": "stdout", "output_type": "stream", "text": [ "+--------------+--------------+------+------------+----------+\n", "| ReviewerID| ReviewerName|Rating|HelpfulVotes|ImageCount|\n", "+--------------+--------------+------+------------+----------+\n", "|A158R1CBA09W28|A158R1CBA09W28| 1| 999| 0|\n", "|A2LXX47A0KMJVX|A2LXX47A0KMJVX| 3| 999| 0|\n", "|A3FLO5K3AE3D4H|A3FLO5K3AE3D4H| 5| 999| 0|\n", "|A3SANFVF57WY9J|A3SANFVF57WY9J| 3| 999| 0|\n", "|A3SANFVF57WY9J|A3SANFVF57WY9J| 3| 999| 0|\n", "|A1GW6XXRLAGE84|A1GW6XXRLAGE84| 1| 999| 0|\n", "| AKEZ5LBU48M63| AKEZ5LBU48M63| 5| 998| 0|\n", "|A3U52V5ZAHZZKU|A3U52V5ZAHZZKU| 5| 998| 0|\n", "|A1J437VM6OVMVQ|A1J437VM6OVMVQ| 5| 998| 1|\n", "|A11W096NRBG9RH|A11W096NRBG9RH| 1| 997| 1|\n", "| ARFLESOGLXAFD| ARFLESOGLXAFD| 5| 997| 0|\n", "|A1NEQZO2GNODWZ|A1NEQZO2GNODWZ| 5| 997| 0|\n", "|A129K4MV9IGLDC|A129K4MV9IGLDC| 5| 997| 0|\n", "| ATA4YSJ4JCBTR| ATA4YSJ4JCBTR| 5| 997| 0|\n", "|A1F50KG7O9A0JD|A1F50KG7O9A0JD| 5| 997| 0|\n", "|A1UED9IWEXZAVO|A1UED9IWEXZAVO| 1| 997| 0|\n", "|A2NP2TS8N2KODU|A2NP2TS8N2KODU| 5| 997| 0|\n", "|A27ZILBXPDJLXI|A27ZILBXPDJLXI| 2| 997| 0|\n", "| AUCXT9K30SHYF| AUCXT9K30SHYF| 2| 996| 0|\n", "| AL9WKVMRI6GFB| AL9WKVMRI6GFB| 4| 996| 0|\n", "|A2TWZX3FBC7WK7|A2TWZX3FBC7WK7| 5| 996| 0|\n", "|A3A473AJNV29KG|A3A473AJNV29KG| 5| 996| 0|\n", "|A1HK1LRON0UJ5X|A1HK1LRON0UJ5X| 5| 996| 0|\n", "|A3AKQW7PD2DW8S|A3AKQW7PD2DW8S| 5| 996| 0|\n", "|A19PX502GPXZC9|A19PX502GPXZC9| 1| 995| 1|\n", "|A327BKWXUTQHFB|A327BKWXUTQHFB| 5| 995| 0|\n", "|A1X1CEGHTHMBL1|A1X1CEGHTHMBL1| 3| 995| 0|\n", "| AF0WVLNPQCFRN| AF0WVLNPQCFRN| 1| 995| 1|\n", "|A27AOF6XLA1FM1|A27AOF6XLA1FM1| 4| 995| 0|\n", "|A3BSB9IH1VL1K6|A3BSB9IH1VL1K6| 5| 994| 0|\n", "| AQWFR9NFHUUU6| AQWFR9NFHUUU6| 3| 994| 0|\n", "| AEYIIVK0PQ648| AEYIIVK0PQ648| 1| 994| 0|\n", "|A1JBCJABMB3YCP|A1JBCJABMB3YCP| 3| 994| 0|\n", "|A3OJERPH6DUHWY|A3OJERPH6DUHWY| 1| 994| 0|\n", "| AC24H4N4HYNU5| AC24H4N4HYNU5| 5| 994| 0|\n", "|A11MRHDXQ0E2RH|A11MRHDXQ0E2RH| 5| 993| 0|\n", "|A3G9G5D0E107K0|A3G9G5D0E107K0| 5| 993| 10|\n", "| AD6CFKD56Q6X5| AD6CFKD56Q6X5| 3| 993| 3|\n", "|A1IUK8TBXA0ZJ0|A1IUK8TBXA0ZJ0| 5| 993| 0|\n", "| A19NWYCHXJPRK| A19NWYCHXJPRK| 5| 993| 1|\n", "|A1KY5G5FP31F2F|A1KY5G5FP31F2F| 5| 992| 0|\n", "|A1SXD4DZ3H8OWT|A1SXD4DZ3H8OWT| 1| 992| 0|\n", "|A2ES1B86C1U5H8|A2ES1B86C1U5H8| 5| 992| 1|\n", "|A1X1CEGHTHMBL1|A1X1CEGHTHMBL1| 5| 992| 0|\n", "|A14ETII5E9SDQC|A14ETII5E9SDQC| 1| 991| 0|\n", "|A2ATK9S267901U|A2ATK9S267901U| 1| 991| 0|\n", "|A1RHQG7G1BEIE2|A1RHQG7G1BEIE2| 5| 991| 0|\n", "|A2TZO9LN23JUD9|A2TZO9LN23JUD9| 4| 990| 0|\n", "| AJ46ZIFB2RZYA| AJ46ZIFB2RZYA| 3| 990| 0|\n", "|A1J66EAFYI9Y7A|A1J66EAFYI9Y7A| 5| 990| 0|\n", "|A1NYKD19R5JQWD|A1NYKD19R5JQWD| 5| 990| 6|\n", "|A1LE0GS647MSMQ|A1LE0GS647MSMQ| 5| 990| 0|\n", "|A14ZQ17DIPJ6UB|A14ZQ17DIPJ6UB| 5| 990| 20|\n", "|A19MKY6VLYA33V|A19MKY6VLYA33V| 5| 990| 0|\n", "|A3EK57ZHMA55G5|A3EK57ZHMA55G5| 5| 989| 0|\n", "|A30TM2QZM82Z91|A30TM2QZM82Z91| 1| 989| 0|\n", "|A1NUY110HLAY4C|A1NUY110HLAY4C| 5| 989| 2|\n", "| AN79WNQP4YX47| AN79WNQP4YX47| 2| 989| 0|\n", "| AF0WVLNPQCFRN| AF0WVLNPQCFRN| 1| 989| 1|\n", "|A35NBGDTL6F2XJ|A35NBGDTL6F2XJ| 1| 989| 0|\n", "|A2Q4NLHQ6ALWXJ|A2Q4NLHQ6ALWXJ| 5| 988| 0|\n", "| AP9FLXNSJ9NM5| AP9FLXNSJ9NM5| 4| 988| 0|\n", "|A3BDSPLA2BCUXZ|A3BDSPLA2BCUXZ| 1| 988| 0|\n", "|A2GZ5SPRP5PWS8|A2GZ5SPRP5PWS8| 5| 987| 0|\n", "|A2NZN9EFY97YR7|A2NZN9EFY97YR7| 5| 987| 2|\n", "|A26877IWJGISYM|A26877IWJGISYM| 5| 987| 0|\n", "|A20O7GFP55GFZB|A20O7GFP55GFZB| 5| 987| 0|\n", "|A32AD0V7IJZZ0A|A32AD0V7IJZZ0A| 5| 987| 0|\n", "| AIU2P47H2Y2VS| AIU2P47H2Y2VS| 5| 987| 0|\n", "|A2UR44HF5K9FE5|A2UR44HF5K9FE5| 4| 987| 5|\n", "| AOJAJGMSLDV2Z| AOJAJGMSLDV2Z| 4| 987| 0|\n", "| AIQ1BUSBGD9DQ| AIQ1BUSBGD9DQ| 4| 987| 0|\n", "| A4ZOOU89L8P1W| A4ZOOU89L8P1W| 3| 986| 0|\n", "|A2O11TM0PSMB26|A2O11TM0PSMB26| 5| 986| 0|\n", "|A1QAPX74LKU2AM|A1QAPX74LKU2AM| 1| 986| 0|\n", "|A16LB0T32T84YP|A16LB0T32T84YP| 2| 986| 0|\n", "|A3RP99WEQNL840|A3RP99WEQNL840| 2| 986| 3|\n", "|A3MQAQT8C6D1I7|A3MQAQT8C6D1I7| 5| 986| 0|\n", "|A14MWOCH7J0GSP|A14MWOCH7J0GSP| 4| 985| 0|\n", "|A22X4Y8D8GKB86|A22X4Y8D8GKB86| 1| 985| 0|\n", "| AAPD8AW5GBP9F| AAPD8AW5GBP9F| 4| 985| 0|\n", "|A22X4Y8D8GKB86|A22X4Y8D8GKB86| 1| 985| 0|\n", "|A10U8DJAPJJI8I|A10U8DJAPJJI8I| 5| 984| 2|\n", "|A30N8CW16N5UHE|A30N8CW16N5UHE| 3| 984| 0|\n", "|A27Z3NRAHIOA48|A27Z3NRAHIOA48| 3| 984| 0|\n", "|A3NUM3JIE6II0F|A3NUM3JIE6II0F| 4| 984| 0|\n", "| A8KYUL3DZK78X| A8KYUL3DZK78X| 4| 984| 0|\n", "| ADKA2MJQQCMTO| ADKA2MJQQCMTO| 1| 984| 0|\n", "|A1HNBFM3GG2G8L|A1HNBFM3GG2G8L| 1| 984| 0|\n", "|A1J7LKMZG3ZVLU|A1J7LKMZG3ZVLU| 4| 983| 0|\n", "|A3F00QUIXGDTOW|A3F00QUIXGDTOW| 5| 983| 0|\n", "|A2Z43IU4VKX4OY|A2Z43IU4VKX4OY| 5| 983| 0|\n", "|A2XHX9VYXDBQDO|A2XHX9VYXDBQDO| 1| 983| 0|\n", "|A17V9XL4CWTQ6G|A17V9XL4CWTQ6G| 2| 982| 0|\n", "| AZPCDY9OQ605E| AZPCDY9OQ605E| 1| 982| 2|\n", "|A21T2PB4ZIJJDF|A21T2PB4ZIJJDF| 1| 982| 0|\n", "|A3FSA18U0VDKFR|A3FSA18U0VDKFR| 5| 982| 0|\n", "|A1ZVUI3IX2X8IY|A1ZVUI3IX2X8IY| 5| 981| 0|\n", "| AMWSZ61FILX2J| AMWSZ61FILX2J| 4| 981| 0|\n", "|A2O4EPKBU2KPDA|A2O4EPKBU2KPDA| 1| 981| 0|\n", "|A2HVUYAFWT5Y3Z|A2HVUYAFWT5Y3Z| 5| 981| 0|\n", "|A1ZN69C23GQ2O0|A1ZN69C23GQ2O0| 5| 981| 0|\n", "|A164UM74G1SEAE|A164UM74G1SEAE| 5| 981| 0|\n", "| AQCPX66NZWEAA| AQCPX66NZWEAA| 1| 981| 0|\n", "| A4VJ32UBNFO3E| A4VJ32UBNFO3E| 5| 981| 0|\n", "| A82PGL24R73ZY| A82PGL24R73ZY| 1| 980| 1|\n", "|A3ETEBL6SRZYOZ|A3ETEBL6SRZYOZ| 5| 980| 0|\n", "|A17LWZIZCG9N28|A17LWZIZCG9N28| 1| 979| 1|\n", "|A3PEEY4N24MTWQ|A3PEEY4N24MTWQ| 5| 979| 0|\n", "|A17LWZIZCG9N28|A17LWZIZCG9N28| 1| 979| 1|\n", "|A2QWUKNIQJ237Q|A2QWUKNIQJ237Q| 5| 979| 0|\n", "| AOX4X7IUT7DI| AOX4X7IUT7DI| 5| 979| 0|\n", "|A36E9F4DQKGYXB|A36E9F4DQKGYXB| 5| 978| 0|\n", "|A3NKUDHSOFB85J|A3NKUDHSOFB85J| 5| 978| 0|\n", "|A2013JDMPUV6D9|A2013JDMPUV6D9| 4| 978| 0|\n", "|A1K41ALI96PBSN|A1K41ALI96PBSN| 5| 978| 0|\n", "|A35VU3H5XSM781|A35VU3H5XSM781| 4| 978| 0|\n", "|A1FS6M4HJUMG02|A1FS6M4HJUMG02| 5| 978| 0|\n", "|A3RJ0PE1NZUMIM|A3RJ0PE1NZUMIM| 1| 977| 0|\n", "|A3V675EETT7WRE|A3V675EETT7WRE| 5| 977| 1|\n", "|A1SFRMIDN4NY00|A1SFRMIDN4NY00| 5| 977| 0|\n", "| AW208Z017EIXE| AW208Z017EIXE| 5| 977| 7|\n", "|A115SCU64CSM6S|A115SCU64CSM6S| 5| 977| 0|\n", "|A26AM37K58619P|A26AM37K58619P| 4| 977| 0|\n", "| APSPHFJV4X85P| APSPHFJV4X85P| 3| 977| 0|\n", "|A3DZFEICHK5LF2|A3DZFEICHK5LF2| 3| 977| 0|\n", "|A1I7GW7XZNGTF2|A1I7GW7XZNGTF2| 5| 977| 11|\n", "|A2UO692G8DMW3R|A2UO692G8DMW3R| 4| 976| 0|\n", "|A1GJT0MJFR874Y|A1GJT0MJFR874Y| 5| 976| 0|\n", "| AO9KZ2D5EH0N4| AO9KZ2D5EH0N4| 5| 976| 1|\n", "|A3QAQUUPFIARWB|A3QAQUUPFIARWB| 5| 976| 0|\n", "|A3QRBGBSCXV1GH|A3QRBGBSCXV1GH| 5| 976| 0|\n", "|A1GL9GXG8I14H2|A1GL9GXG8I14H2| 5| 976| 0|\n", "|A1Z8B2FOL91SW4|A1Z8B2FOL91SW4| 5| 975| 0|\n", "|A1JW3SUO3O5P6Y|A1JW3SUO3O5P6Y| 5| 975| 0|\n", "| AEA6CWXGT8IZT| AEA6CWXGT8IZT| 5| 975| 0|\n", "|A1NSN3C3DE8YW2|A1NSN3C3DE8YW2| 5| 975| 0|\n", "|A1A4CBD8SE9H4R|A1A4CBD8SE9H4R| 5| 974| 0|\n", "| APKUVQQ1YVIVL| APKUVQQ1YVIVL| 5| 974| 0|\n", "|A1QXRGDWNVML1H|A1QXRGDWNVML1H| 4| 974| 0|\n", "|A23MAG8UKFL0QW|A23MAG8UKFL0QW| 5| 974| 0|\n", "|A1CGP9TYF73SDL|A1CGP9TYF73SDL| 1| 974| 0|\n", "|A36DZ94UV6DULB|A36DZ94UV6DULB| 1| 974| 0|\n", "|A2NMFVD61EGMY2|A2NMFVD61EGMY2| 5| 974| 0|\n", "| AANX9NO7ZERI2| AANX9NO7ZERI2| 4| 973| 0|\n", "|A16723ZETJAWIS|A16723ZETJAWIS| 3| 973| 0|\n", "|A1MJ6FKLN7OP2J|A1MJ6FKLN7OP2J| 1| 973| 1|\n", "|A28PY3HLG4YPVW|A28PY3HLG4YPVW| 5| 973| 0|\n", "|A12GHYBFMF6GIN|A12GHYBFMF6GIN| 4| 972| 0|\n", "|A3OHOCASMGUHAB|A3OHOCASMGUHAB| 5| 972| 4|\n", "| AY5IL05TZPIZO| AY5IL05TZPIZO| 5| 972| 0|\n", "|A3JIH1B4OGBPIZ|A3JIH1B4OGBPIZ| 3| 972| 0|\n", "|A3LWAG059F7TN7|A3LWAG059F7TN7| 5| 972| 0|\n", "|A3HVXEXV8IC3VQ|A3HVXEXV8IC3VQ| 2| 971| 0|\n", "| A9K7V1MKJ9BOJ| A9K7V1MKJ9BOJ| 4| 971| 2|\n", "| ALR35EFI69S5R| ALR35EFI69S5R| 5| 971| 0|\n", "|A1HYTIRP6VYE43|A1HYTIRP6VYE43| 1| 971| 1|\n", "|A2013JDMPUV6D9|A2013JDMPUV6D9| 4| 971| 0|\n", "|A17D6U4CP726UU|A17D6U4CP726UU| 1| 971| 0|\n", "|A35C058DJHEFXP|A35C058DJHEFXP| 3| 971| 0|\n", "|A1VOIQ80T0Q7Y4|A1VOIQ80T0Q7Y4| 1| 971| 0|\n", "| AQ6AGWKWMTY8H| AQ6AGWKWMTY8H| 2| 971| 0|\n", "|A2SPHU1KUW8GNP|A2SPHU1KUW8GNP| 1| 971| 0|\n", "|A362G9K7Q3CRL6|A362G9K7Q3CRL6| 4| 971| 0|\n", "|A2YCGJBLWGRYRI|A2YCGJBLWGRYRI| 5| 970| 0|\n", "| ANSNP17BANSM6| ANSNP17BANSM6| 3| 970| 0|\n", "|A3L33RPAYJ7Q8B|A3L33RPAYJ7Q8B| 4| 970| 0|\n", "|A1GHPJB2N0D0RL|A1GHPJB2N0D0RL| 4| 970| 4|\n", "| AL8AHEZI7SXHL| AL8AHEZI7SXHL| 5| 970| 0|\n", "|A2SWGQVH78I33R|A2SWGQVH78I33R| 5| 970| 0|\n", "| AW269WUHSW17C| AW269WUHSW17C| 5| 970| 0|\n", "| A2825S9Q5O7F2| A2825S9Q5O7F2| 1| 969| 1|\n", "|A20O7GFP55GFZB|A20O7GFP55GFZB| 5| 969| 0|\n", "|A2U0T8JQ4MGDNV|A2U0T8JQ4MGDNV| 1| 969| 0|\n", "|A3D7BGK0P5QY0X|A3D7BGK0P5QY0X| 4| 969| 0|\n", "|A3QATJDJ2N71MY|A3QATJDJ2N71MY| 5| 969| 0|\n", "| A4CJWB9R3P0A7| A4CJWB9R3P0A7| 5| 969| 10|\n", "|A3SBXIOSQ3455W|A3SBXIOSQ3455W| 1| 969| 0|\n", "|A2KF5EZZB4Z0IQ|A2KF5EZZB4Z0IQ| 1| 969| 3|\n", "|A1Y8B3UK2JF1MJ|A1Y8B3UK2JF1MJ| 2| 969| 0|\n", "|A1S1BJFTA644TU|A1S1BJFTA644TU| 3| 969| 0|\n", "| AF0DCILSFV7CW| AF0DCILSFV7CW| 1| 969| 0|\n", "|A3JYPW0DCZXQSZ|A3JYPW0DCZXQSZ| 2| 968| 0|\n", "| A7I4SK29SNGXX| A7I4SK29SNGXX| 3| 968| 0|\n", "|A3NEDSXFE0QX9E|A3NEDSXFE0QX9E| 5| 968| 0|\n", "|A1I3GL5GLBEQ7X|A1I3GL5GLBEQ7X| 5| 968| 0|\n", "| A7I4SK29SNGXX| A7I4SK29SNGXX| 3| 968| 0|\n", "| AQR0111TANX5K| AQR0111TANX5K| 4| 968| 1|\n", "| A5GO0ZBLQMBZ3| A5GO0ZBLQMBZ3| 1| 968| 0|\n", "| AILCWT1IIP7ZT| AILCWT1IIP7ZT| 4| 968| 0|\n", "|A1F58WQP58HTE4|A1F58WQP58HTE4| 5| 968| 0|\n", "|A14DMXU54BWJPL|A14DMXU54BWJPL| 5| 967| 4|\n", "|A1RKGJ41O6OZ03|A1RKGJ41O6OZ03| 4| 967| 0|\n", "| ARDXLWFEDSY30| ARDXLWFEDSY30| 1| 967| 0|\n", "| AE33Q2HFT8DJX| AE33Q2HFT8DJX| 5| 967| 0|\n", "|A2374KS5MTBWKI|A2374KS5MTBWKI| 4| 967| 15|\n", "|A2OQL24QJO8OSC|A2OQL24QJO8OSC| 4| 967| 1|\n", "|A10F49XV3OEJLW|A10F49XV3OEJLW| 5| 967| 0|\n", "|A27OO9A61OWIQ6|A27OO9A61OWIQ6| 1| 966| 0|\n", "| ABQWMANK4AR63| ABQWMANK4AR63| 4| 966| 0|\n", "+--------------+--------------+------+------------+----------+\n", "\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ " " ] } ], "source": [ "from pyspark.sql import functions as F\n", "\n", "# --------------------------------------------------\n", "# 1️⃣ Get Review vertices with non-null helpful votes\n", "# --------------------------------------------------\n", "\n", "reviews_df = g.vertices.filter(\n", " (F.col(\"type\") == \"review\") &\n", " (F.col(\"vote\").isNotNull())\n", ").select(\n", " F.col(\"id\").alias(\"review_id\"),\n", " F.col(\"reviewerID\"),\n", " F.col(\"rating\"),\n", " F.col(\"vote\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 2️⃣ Motif: (review)-[:has_review_image]->(reviewImage)\n", "# --------------------------------------------------\n", "\n", "review_image_motif = g.find(\"\"\"\n", " (review)-[e]->(reviewImage)\n", "\"\"\").filter(\n", " (F.col(\"e.relationship\") == \"has_review_image\") &\n", " (F.col(\"review.type\") == \"review\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 3️⃣ Count images per review\n", "# --------------------------------------------------\n", "\n", "review_image_counts = review_image_motif.groupBy(\n", " F.col(\"review.id\").alias(\"review_id\")\n", ").agg(\n", " F.count(\"reviewImage.id\").alias(\"ImageCount\")\n", ")\n", "\n", "# --------------------------------------------------\n", "# 4️⃣ LEFT JOIN (OPTIONAL MATCH equivalent)\n", "# --------------------------------------------------\n", "\n", "final_df = reviews_df.join(\n", " review_image_counts,\n", " \"review_id\",\n", " \"left\"\n", ").fillna(0, subset=[\"ImageCount\"])\n", "\n", "# --------------------------------------------------\n", "# 5️⃣ Final formatting and ordering\n", "# --------------------------------------------------\n", "\n", "result_df = final_df.select(\n", " F.col(\"reviewerID\").alias(\"ReviewerID\"),\n", " F.coalesce(F.col(\"reviewerID\"), F.lit(\"Unknown\")).alias(\"ReviewerName\"),\n", " F.col(\"rating\").alias(\"Rating\"),\n", " F.col(\"vote\").alias(\"HelpfulVotes\"),\n", " F.col(\"ImageCount\")\n", ").orderBy(\n", " F.desc(\"HelpfulVotes\")\n", ").limit(200)\n", "\n", "print(\"\\nTop 200 Reviews with Highest Helpful Votes\\n\")\n", "measure(result_df).show(200, truncate=60)\n" ] }, { "cell_type": "code", "execution_count": null, "id": "5fbd4bc6-6b01-4af0-82e5-c14bcf2c8534", "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 }