05_process_json.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/kh/mxr2kgs928zbf9svb0_h7ttw0000gn/T/ipykernel_74239/4065862980.py:111: FutureWarning: The behavior of DataFrame concatenation with empty or all-NA entries is deprecated. In a future version, this will no longer exclude empty or all-NA columns when determining the result dtypes. To retain the old behavior, exclude the relevant entries before the concat operation.\n",
      "  table3 = pd.concat(\n"
     ]
    }
   ],
   "source": [
    "# import pandas as pd\n",
    "\n",
    "import json\n",
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "\n",
    "from amazon_product import AmazonProduct\n",
    "\n",
    "\n",
    "# Constant json fields: Product\n",
    "PRODUCT_ASIN = \"asin\"\n",
    "PRODUCT_NAME = \"name\"\n",
    "PRODUCT_OVERALL_RATING = \"overall_rating\"\n",
    "PRODUCT_TOTAL_RATING_COUNT = \"total_rating_count\"\n",
    "PRODUCT_TOTAL_REVIEWS_COUNT = \"total_reviews_count\"\n",
    "PRODUCT_REVIEW_LIST = \"review_list\"\n",
    "PRODUCT_FAILED_URLS = \"failed_urls\"\n",
    "\n",
    "# Constant json fields: Review\n",
    "REVIEW_ID = \"id\"\n",
    "REVIEW_RATING = \"rating\"\n",
    "REVIEW_TITLE = \"title\"\n",
    "REVIEW_HREF = \"href\"\n",
    "REVIEW_COUNTRY = \"country\"\n",
    "REVIEW_DATE = \"date\"\n",
    "REVIEW_BODY = \"body\"\n",
    "REVIEW_VERIFIED_PURCHASE = \"verified_purchase\"\n",
    "REVIEW_FOUND_HELPFUL = \"found_helpful\"\n",
    "REVIEW_USERNAME = \"username\"\n",
    "REVIEW_USERNAME_URL = \"username_url\"\n",
    "REVIEW_IMAGES = \"images\"\n",
    "REVIEW_VIDEOS = \"videos\"\n",
    "REVIEW_FOUND_UNDER = \"found_under\"\n",
    "\n",
    "\n",
    "path = Path(\"./data/pfw/results\")\n",
    "\n",
    "# Initialize DataFrames for the three tables\n",
    "table1 = pd.DataFrame(\n",
    "    columns=[PRODUCT_ASIN, REVIEW_ID, REVIEW_VERIFIED_PURCHASE, REVIEW_FOUND_HELPFUL]\n",
    ")\n",
    "table2 = pd.DataFrame(columns=[PRODUCT_ASIN, \"review_id\", REVIEW_VIDEOS, REVIEW_IMAGES])\n",
    "table3 = pd.DataFrame(\n",
    "    columns=[\n",
    "        PRODUCT_ASIN,\n",
    "        REVIEW_ID,\n",
    "        REVIEW_TITLE,\n",
    "        REVIEW_BODY,\n",
    "        REVIEW_DATE,\n",
    "        REVIEW_RATING,\n",
    "        REVIEW_USERNAME,\n",
    "        REVIEW_USERNAME_URL,\n",
    "    ]\n",
    ")\n",
    "\n",
    "# Process each file in the directory\n",
    "for root, dirs, files in path.walk(top_down=True, on_error=print):\n",
    "    for file in files:\n",
    "        try:\n",
    "            # Open and read the JSON file\n",
    "            with open((root / file), \"r\") as f:\n",
    "                json_text = f.read()\n",
    "                # Parse the JSON content\n",
    "                json_data = json.loads(json_text)\n",
    "\n",
    "                # Convert JSON to AmazonProduct object\n",
    "                product = AmazonProduct.from_json(json_data)\n",
    "\n",
    "                # Extract ASIN and reviews\n",
    "                asin = product.asin\n",
    "                reviews = product.review_list\n",
    "\n",
    "                for review in reviews:\n",
    "                    # Table 1: asin, review_id, verified_purchase, found_helpful\n",
    "                    table1 = pd.concat(\n",
    "                        [\n",
    "                            table1,\n",
    "                            pd.DataFrame(\n",
    "                                {\n",
    "                                    PRODUCT_ASIN: [asin],\n",
    "                                    REVIEW_ID: [review.get(REVIEW_ID)],\n",
    "                                    REVIEW_VERIFIED_PURCHASE: [\n",
    "                                        review.get(REVIEW_VERIFIED_PURCHASE)\n",
    "                                    ],\n",
    "                                    REVIEW_FOUND_HELPFUL: [\n",
    "                                        review.get(REVIEW_FOUND_HELPFUL)\n",
    "                                    ],\n",
    "                                }\n",
    "                            ),\n",
    "                        ],\n",
    "                        ignore_index=True,\n",
    "                    )\n",
    "\n",
    "                    # Table 2: asin, review_id, review_videos, review_images\n",
    "                    table2 = pd.concat(\n",
    "                        [\n",
    "                            table2,\n",
    "                            pd.DataFrame(\n",
    "                                {\n",
    "                                    PRODUCT_ASIN: [asin],\n",
    "                                    REVIEW_ID: [review.get(REVIEW_ID)],\n",
    "                                    REVIEW_VIDEOS: [review.get(REVIEW_VIDEOS, [])],\n",
    "                                    REVIEW_IMAGES: [review.get(REVIEW_IMAGES, [])],\n",
    "                                }\n",
    "                            ),\n",
    "                        ],\n",
    "                        ignore_index=True,\n",
    "                    )\n",
    "\n",
    "                    # Table 3: asin, review_id, review_header, review_text, date, rating, username, username_url\n",
    "                    table3 = pd.concat(\n",
    "                        [\n",
    "                            table3,\n",
    "                            pd.DataFrame(\n",
    "                                {\n",
    "                                    PRODUCT_ASIN: [asin],\n",
    "                                    REVIEW_ID: [review.get(REVIEW_ID)],\n",
    "                                    REVIEW_TITLE: [review.get(REVIEW_TITLE)],\n",
    "                                    REVIEW_BODY: [review.get(REVIEW_BODY)],\n",
    "                                    REVIEW_DATE: [review.get(REVIEW_DATE)],\n",
    "                                    REVIEW_RATING: [review.get(REVIEW_RATING)],\n",
    "                                    REVIEW_USERNAME: [review.get(REVIEW_USERNAME)],\n",
    "                                    REVIEW_USERNAME_URL: [\n",
    "                                        review.get(REVIEW_USERNAME_URL)\n",
    "                                    ],\n",
    "                                }\n",
    "                            ),\n",
    "                        ],\n",
    "                        ignore_index=True,\n",
    "                    )\n",
    "        except Exception as e:\n",
    "            print(f\"Error processing file {file}: {e}\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Table 1:\n",
      "            asin              id verified_purchase found_helpful\n",
      "0     0881886157  R3SIFIFPFUB4V7              True             0\n",
      "1     0881886157  R1UG31WWM2ZXW2              True             2\n",
      "2     0881886157  R2Z7BJPXRABL8D              True             0\n",
      "3     0881886157  R2L6GPUWC13R50              True             0\n",
      "4     0881886157  R2HFXNJOK5AEZX              True             0\n",
      "...          ...             ...               ...           ...\n",
      "8416  B009SPJESM  R1YH4XBK4ATKA5              True             0\n",
      "8417  B009SPJESM   RLCO7NPQJZF3N              True             0\n",
      "8418  B009SPJESM  R30SGER7MHDFLY              True             2\n",
      "8419  B009SPJESM  R3MOXMXKSWHT43              True             0\n",
      "8420  B009SPJESM  R1YWWH83LI30TT              True             0\n",
      "\n",
      "[8421 rows x 4 columns]\n"
     ]
    }
   ],
   "source": [
    "# Display the resulting DataFrames\n",
    "print(\"Table 1:\")\n",
    "print(table1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Table 2:\n",
      "            asin review_id videos  \\\n",
      "0     0881886157       NaN     []   \n",
      "1     0881886157       NaN     []   \n",
      "2     0881886157       NaN     []   \n",
      "3     0881886157       NaN     []   \n",
      "4     0881886157       NaN     []   \n",
      "...          ...       ...    ...   \n",
      "8416  B009SPJESM       NaN     []   \n",
      "8417  B009SPJESM       NaN     []   \n",
      "8418  B009SPJESM       NaN     []   \n",
      "8419  B009SPJESM       NaN     []   \n",
      "8420  B009SPJESM       NaN     []   \n",
      "\n",
      "                                                 images              id  \n",
      "0     [https://m.media-amazon.com/images/I/61t8YVxHD...  R3SIFIFPFUB4V7  \n",
      "1     [https://m.media-amazon.com/images/I/71KLki1R2...  R1UG31WWM2ZXW2  \n",
      "2                                                    []  R2Z7BJPXRABL8D  \n",
      "3                                                    []  R2L6GPUWC13R50  \n",
      "4                                                    []  R2HFXNJOK5AEZX  \n",
      "...                                                 ...             ...  \n",
      "8416                                                 []  R1YH4XBK4ATKA5  \n",
      "8417                                                 []   RLCO7NPQJZF3N  \n",
      "8418                                                 []  R30SGER7MHDFLY  \n",
      "8419                                                 []  R3MOXMXKSWHT43  \n",
      "8420                                                 []  R1YWWH83LI30TT  \n",
      "\n",
      "[8421 rows x 5 columns]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "print(\"\\nTable 2:\")\n",
    "print(table2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Table 3:\n",
      "            asin              id                             title  \\\n",
      "0     0881886157  R3SIFIFPFUB4V7                       Wrong words   \n",
      "1     0881886157  R1UG31WWM2ZXW2  Phantom of the Opera Piano Notes   \n",
      "2     0881886157  R2Z7BJPXRABL8D                        Five Stars   \n",
      "3     0881886157  R2L6GPUWC13R50             Not too hard to learn   \n",
      "4     0881886157  R2HFXNJOK5AEZX                        Five Stars   \n",
      "...          ...             ...                               ...   \n",
      "8416  B009SPJESM  R1YH4XBK4ATKA5                       Great Socks   \n",
      "8417  B009SPJESM   RLCO7NPQJZF3N    The quality looked good. maria   \n",
      "8418  B009SPJESM  R30SGER7MHDFLY              Like these the best.   \n",
      "8419  B009SPJESM  R3MOXMXKSWHT43     Did not like feel of stocking   \n",
      "8420  B009SPJESM  R1YWWH83LI30TT  Good Material & Size as expected   \n",
      "\n",
      "                                                   body                 date  \\\n",
      "0     Instead of All I ask of you should be say you ...  2022-11-27 00:00:00   \n",
      "1     Excellent! Finally arrived and made me very ha...  2018-04-20 00:00:00   \n",
      "2     My wife has wanted to play this music every si...  2016-11-23 00:00:00   \n",
      "3     Has all the songs I wanted and the pictures ar...  2016-09-21 00:00:00   \n",
      "4                                                 great  2016-08-15 00:00:00   \n",
      "...                                                 ...                  ...   \n",
      "8416  My friend loved them, they really helped her a...  2016-08-18 00:00:00   \n",
      "8417  I ordered incorrectly.  The quality looked goo...  2016-08-11 00:00:00   \n",
      "8418  These fit great, give the right amount of comp...  2016-03-05 00:00:00   \n",
      "8419  according to chart I would wear medium.  Feet ...  2014-09-08 00:00:00   \n",
      "8420  Good material, firm grip , comfortable feeling...  2018-06-07 00:00:00   \n",
      "\n",
      "      rating                        username  \\\n",
      "0        4.0                         Daniela   \n",
      "1        5.0                           Mopsy   \n",
      "2        5.0                         David H   \n",
      "3        5.0                 Michael Hancock   \n",
      "4        5.0                   Mark S. Smith   \n",
      "...      ...                             ...   \n",
      "8416     5.0                    Designlady54   \n",
      "8417     4.0  Maria I. Barrows. M.D. retired   \n",
      "8418     5.0                 Amazon Customer   \n",
      "8419     3.0                     Mary Cooper   \n",
      "8420     5.0                    Customer 999   \n",
      "\n",
      "                                           username_url  \n",
      "0     /gp/profile/amzn1.account.AHMSQCCP66OXFOHOBXPZ...  \n",
      "1                                                        \n",
      "2     /gp/profile/amzn1.account.AHIEPSRTAKDR3HFSDDT4...  \n",
      "3     /gp/profile/amzn1.account.AFMFD73YFUZFAPUWG3KM...  \n",
      "4     /gp/profile/amzn1.account.AEREDLT5DIW7YC3D3SQZ...  \n",
      "...                                                 ...  \n",
      "8416  /gp/profile/amzn1.account.AHHJDAWG72PN4S5DOHOH...  \n",
      "8417  /gp/profile/amzn1.account.AHQUI3K566RDKUEPDNVB...  \n",
      "8418  /gp/profile/amzn1.account.AGC34Z7ZHB7HFGNUUWCF...  \n",
      "8419  /gp/profile/amzn1.account.AG3Y2L5H27PNJX3PKOLO...  \n",
      "8420                                                     \n",
      "\n",
      "[8421 rows x 8 columns]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "print(\"\\nTable 3:\")\n",
    "print(table3)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "zon-crawler-iOPTyq1w",
   "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.12.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}