<jupyter_start><jupyter_text>Filtering `requests` Search Results<jupyter_code>%load_ext lab_black
%load_ext autoreload
%autoreload 2
import os
import time
from glob import glob

import pandas as pd
%aimport src.utils
from src.utils import show_df, show_df_dtypes_nans<jupyter_output><empty_output><jupyter_text>[Table of Contents](table-of-contents)0. [About](about)1. [User Inputs](user-inputs)2. [`selenium`-based files](`selenium`-based-files)   - 2.1. [Load and Process all Listings files acquired using `selenium`](load-and-process-all-listings-files-acquired-using-`selenium`)   - 2.2. [Load and Process all Search Results files acquired using `selenium`](load-and-process-all-search-results-files-acquired-using-`selenium`)   - 2.3. [Merge listings with search results acquired using `selenium`](merge-listings-with-search-results-acquired-using-`selenium`)3. [`requests`-based files](`requests`-based-files)   - 3.1. [Load and Process all Search Results files acquired using `requests`](load-and-process-all-search-results-files-acquired-using-`requests`)   - 3.2. [Slice columns to use for scraping with `requests` and export to file](slice-columns-to-use-for-scraping-with-using-`requests`-and-export-to-file)  0. [About](about) In this notebook, our primary objective will be to create a list of game listings that will be scraped using the `requests` library. It is not known if the scraped search results (using `requests`, in `3_requests_download.ipynb`) contain any duplicated game listings. Since it is not desired to scrape a listing more than once, we'll need to remove any duplicated listings from the search results dataset scraped with `requests` before scraping those listings to get additional attributes beyond platform and price. This will be discussed in [section 3.](`requests`-based-files) of this notebook.A secondary objective is to briefly explore the presence of duplicates in the search results scraped using the `selenium` library in `2_selenium.ipynb`. This will not result in creating a new dataset but will provide a means of comparing the possible presence of duplicated search results in this dataset to those in the search results scraped using `requests`. This will be discussed in [section 2.](`selenium`-based-files) of this notebook. This choice will help us to decide how to merge the search results (which contains information about supported platforms and price) and listings (which does not include platform or price).  1. [User Inputs](user-inputs) Define variables that can be changed when running this notebook<jupyter_code>PROJ_ROOT_DIR = os.getcwd()
# Columns to hide (for display purposes only)
cols_to_hide = ["Title", "Release Date", "Genre", "page_num", "listing_num"]<jupyter_output><empty_output><jupyter_text>Define variables that depend on the variables defined above- get paths to directories where single-row CSV files (produced after scraping) will be stored- create lists of  - search results files created using webscraping with Selenium and requests  - listings files created using webscraping with Selenium<jupyter_code># Path to data/raw
data_dir = os.path.join(PROJ_ROOT_DIR, "data")
raw_data_dir = os.path.join(data_dir, "raw")

# Path to data/raw/requests
requests_files_dir = os.path.join(raw_data_dir, "requests")

# Path to data/raw/selenium
selenium_files_dir = os.path.join(raw_data_dir, "selenium")

# List of CSV files created using selenium
fpaths = glob(os.path.join(selenium_files_dir, "p*_*.csv"))

# List of search results files created by Selenium
selenium_search_results_pages = glob(
    os.path.join(selenium_files_dir, "search_results_page_*_*.parquet.gzip")
)

# List of search results files created by requests
requests_search_results_pages = glob(
    os.path.join(requests_files_dir, "search_results_page_*_*.parquet.gzip")
)<jupyter_output><empty_output><jupyter_text>2. [`selenium`-based files](`selenium`-based-files)  2.1. [Load and Process all Listings files acquired using `selenium`](load-and-process-all-listings-files-acquired-using-`selenium`) In order to briefly explore the scraped listings acquired using `selenium`, we'll iterate over all the scraped listing files (from the above list of CSV files) and concatenate the files into a single `DataFrame` with all the listings<jupyter_code>%%time
df_listings = pd.concat(
    [
        pd.read_csv(fpath)
        for fpath in fpaths
    ],
    ignore_index=True,
).dropna(subset=["Title"]).sort_values(by=["page_num", "listing_num"]).reset_index(drop=True)
show_df(df_listings, 1)
show_df_dtypes_nans(df_listings)<jupyter_output><empty_output><jupyter_text>As we can see below, the `Title` column of this dataset is unique since there is one row for each scraped listing `Title`<jupyter_code>print(df_listings["Title"].nunique(), len(df_listings))<jupyter_output>609 609<jupyter_text>2.2. [Load and Process all Search Results files acquired using `selenium`](load-and-process-all-search-results-files-acquired-using-`selenium`) We'll now get list of all the search results files collected with `selenium`<jupyter_code>selenium_search_results_individual_pages = glob(
    os.path.join(selenium_files_dir, "search_results_page_*_2021*.parquet.gzip")
)
print(
    f"Found {len(selenium_search_results_individual_pages)} "
    "batched search results *.parquet.gzip file(s)."
)<jupyter_output>Found 35 batched search results *.parquet.gzip file(s).<jupyter_text>Similar to concatenating listings, we'll concatenate all the search results into a single `DataFrame`<jupyter_code>df_search_results = (
    pd.concat(
        [
            pd.read_parquet(
                selenium_search_results_indivudual_page,
                engine="auto",
            )
            for selenium_search_results_indivudual_page in selenium_search_results_individual_pages
        ],
        ignore_index=True,
    )
    .astype({"page": int})
    .dropna(subset=["title"])
    .sort_values(by=["page", "listing_counter"])
    .reset_index(drop=True)
)
print(df_search_results["title"].nunique(), len(df_search_results))<jupyter_output>655 875<jupyter_text>Again, we'll verify that there are no listings with multiple URLs for a single title<jupyter_code>listings_with_multiple_urls = (
    df_search_results.groupby("title")["url"].nunique().sort_values(ascending=False)
)
print(
    "Number of listing titles with more than one URL = "
    f"{len(listings_with_multiple_urls[listings_with_multiple_urls > 1])}"
)<jupyter_output>Number of listing titles with more than one URL = 0<jupyter_text>**Observations**1. Again, we can see that there are no titles in the search results collected with `selenium` that have multiple listing URLs associated with them. However, there are listings that have the same `title`<jupyter_code>display(
    df_search_results[
        df_search_results.duplicated(subset=["title"], keep=False)
    ].sort_values(by="title")
)<jupyter_output><empty_output><jupyter_text>**Observations**1. These are valid duplicated listings and need to be dropped. They appeared on a different page number when the Selenium webdriver queried the Steam store search. We'll drop one occurrence of each duplicated listing and keep the other.2. A unique listing in a search result will have a unique combination of listing title and URL. No other listing should have the same combination of these two columns. So, we can use these two columns to identify and remove duplicated entries from this dataset. So, we will use the `title` and `url` columns to remove duplicates from this dataset of search results (collected with `selenium`)<jupyter_code>df_search_results = df_search_results.drop_duplicates(
    subset=["title", "url"]
).reset_index(drop=True)
print(
    f"Number of unique titles = {df_search_results['title'].nunique()}, "
    f"Number of rows = {len(df_search_results)}"
)
show_df(df_search_results, 5)<jupyter_output>Number of unique titles = 655, Number of rows = 655<jupyter_text>All the columns scraped with Selenium (from each listing) are shown below<jupyter_code>print("Columns scraped using selenium")
list(df_listings)<jupyter_output>Columns scraped using selenium<jupyter_text>Since the listing URL was not scraped from the individual listings, it is only present in the search results dataset. The URL would be unique for each listing and could be an alternative to the `title` column when merging these two datasets. However, its absence from the listings dataset means we have to use the `title` column on its own instead.  2.3. [Merge listings with search results acquired using `selenium`](merge-listings-with-search-results-acquired-using-`selenium`) Brief considerations for direction of merge when combining the search results and listings datasets.Recall, when scraping with Selenium, we loaded a page of search results, clicked on a single listing, scraped it and returned (moved back) to the search results. This was done for a single page of search results at a time. As scraping with Selenium was slow, scraping was performed over multiple days. Over this time, Steam updated the search results. So, listings appearing on page 4 (during a given day's scraping) could appear on page 5 during the next day's scraping. In this scenario, the webscraping code checks for previously scraped listings (by listing name, which contains the Title of the listing) and skips re-scraping these listings during the next iterations through the search results pages. Since the listings that appeared in multiple search results are considered as duplicates and their listings were not clicked on (or scraped) a second time. This means a search results dataset could have more rows than the listings dataset for the same page. For this reason, the search results `DataFrame` will be `LEFT JOIN`ed to the listings dataset and the `JOIN`ed dataset will have rows with columns that are populated with values from fields scraped from the search results page but with missing values for columns that were scraped from the listing pages. The search results and listings datasets are merged below<jupyter_code>dfm = df_search_results.merge(
    df_listings,
    left_on=["title"],
    right_on=["Title"],
    how="left",
)
show_df(dfm.drop(columns=["url", "user_defined_tags", "languages", "Genre"]), 1)<jupyter_output><empty_output><jupyter_text>We won't be using this merged dataset further in this notebook, but the same logic will be used when we do need to combine these two datasets (collected with `selenium`) for exploratory data analysis in section 2.3 of a later notebook `6_merge_searches_listings.ipynb`.  3. [`requests`-based files](`requests`-based-files) We'll now filter the search results scraped with `requests` in `3_requests.ipynb` to eliminate duplicate listings. This is the primary objective of this notebook.  3.1. [Load and Process all Search Results files acquired using `requests`](load-and-process-all-search-results-files-acquired-using-`requests`) Combine (vertically concatenate) the datasets for all the scraped search results pages. We'll also remove any listings not supporting English and extract an `app_id` from the URL<jupyter_code>%%time
df_search_results_requests = (
    pd.concat(
        [
            pd.read_parquet(f, engine="auto")
            for f in requests_search_results_pages
        ],
        ignore_index=True,
    )
    .drop(columns=["request_status_code"])
    .astype({"page": int}).dropna(subset=["title"])
    .sort_values(by=["page", "listing_counter"])
    .reset_index(drop=True)
)
print(f"Total number of search results scraped with requests = {len(df_search_results_requests),}")

# Only select listing titles available in English
df_search_results_requests = df_search_results_requests[
    df_search_results_requests['title'].map(lambda x: x.isascii())
]

# Append an app_id column using a regex extract() from the URL
df_search_results_requests = df_search_results_requests.assign(
    app_id=df_search_results_requests["url"].str.extract(
        r"https://store.steampowered.com/app/(\d+)/*/"
    )
)

print(
    "Number of English language listings in the search results = "
    f"{len(df_search_results_requests),}"
)<jupyter_output><empty_output><jupyter_text>Count how many listings are missing a title<jupyter_code>print(
    f"Missing title = {len(df_search_results_requests[df_search_results_requests['title'] == ''])}"
)<jupyter_output><empty_output><jupyter_text>Since there are only two such rows, we'll now remove these listings that are missing a value in the `title` column<jupyter_code>df_search_results_requests = df_search_results_requests[
    df_search_results_requests["title"] != ""
]<jupyter_output><empty_output><jupyter_text>We'll now count how many rows have an empty string in the `platform_names` or `release_date` columns (separately)<jupyter_code>for c in ["url", "release_date", "platform_names"]:
    blank_values = (df_search_results_requests[c] == "").sum()
    print(f"Missing {c} = {blank_values}")<jupyter_output><empty_output><jupyter_text>We'll count how many listings are missing a value in the `app_id` column<jupyter_code>missing_app_ids = df_search_results_requests["app_id"].isna().sum()
print(f"Missing app_ids = {missing_app_ids}")<jupyter_output><empty_output><jupyter_text>Since the `release_date` column has several blank values and the `app_id` is missing several values as well, we'll use only `url`, `platform_names` and `title` to find duplicated listings. Duplicates in all three of these columns will be true duplicate listings and should be dropped from this dataset. We'll show duplicated listings below<jupyter_code># Find duplicates - use keep=False to return all duplicated rows
dup_search_res_requests = df_search_results_requests[
    df_search_results_requests.duplicated(
        subset=["url", "title", "platform_names"], keep=False
    )
].sort_values(by=["url"])
display(dup_search_res_requests)<jupyter_output><empty_output><jupyter_text>**Observations**1. These are true duplicated listings. They have been listed multiple times in the search results, each time on a different page. They need to be dropped. We'll use the `url`, `platform_names` and `title` columns to drop duplicated listings (keeping only the first such listing and dropping the others)<jupyter_code>df_search_results_requests = df_search_results_requests.drop_duplicates(
    subset=["url", "title", "platform_names"]
)<jupyter_output><empty_output><jupyter_text>If we now consider the listing `title` to be unique, then we should expect that this dataset should have a single URL per `title`. Below are the listings with more than one URL for each `title`<jupyter_code>%%time
multiple_urls = (
    df_search_results_requests.groupby("title")["url"]
    .nunique()
    .sort_values(ascending=False)
)
multiple_urls_titles = multiple_urls[multiple_urls > 1].index.tolist()
show_df(
    df_search_results_requests[
        df_search_results_requests["title"].isin(multiple_urls_titles)
    ].sort_values(by=["title"]), 5
)<jupyter_output><empty_output><jupyter_text>**Observations**1. Since the listing `title` was cleaned to remove special characters, this has caused different listings whose title names only differed by special characters to be incorrectly given the same `title`. This means the `title` column is not a valid indicator on its own of a unique listing in this search results dataset (with `requests`). While this was also done when scraping search results with `selenium`, the smaller size of the dataset acquired with the webdriver means we did not encounter such an occurrence. So, with `selenium`, dropping duplicates based on the `Title` column was valid but this is invalid for search results scraped with the `requests` library. Next, we'll check if the same problem persists for the URL column, by counting the number of listing`titles` with more than one URL<jupyter_code>%%time
multiple_urls = df_search_results_requests.groupby("url")["title"].count().sort_values(
    ascending=False
)
print(f"Number of URLs with more than one Title = {len(multiple_urls[multiple_urls > 1])}")
assert len(multiple_urls[multiple_urls > 1]) == 0<jupyter_output><empty_output><jupyter_text>We'll also check if the number of unique URLs and the number of rows in this search results dataset (scraped with `requests`) agree with eachother<jupyter_code>print(
    f"Number of unique URLs in search results dataset = {df_search_results_requests['url'].nunique():,}\n"
    f"Number of rows in search results dataset = {len(df_search_results_requests):,}"
)
assert df_search_results_requests["url"].nunique() == len(df_search_results_requests)<jupyter_output><empty_output><jupyter_text>**Notes**1. Preprocessing to remove special characters was not performed on the URL column of this dataset. **Observations**1. As we can see from the two checks above, the URL column of this dataset is unique. There are no listings which share the same URL but have different `title`s, and there is a unique URL for every row of the `DataFrame`. We can now use the `requests` library to scrape listings from this dataset where each URL corresponds to a single listing. When that dataset is collected and we need to merge it with this dataset of search results, we will do so on the `url` column of both datasets since that is a unique column. View the search results dataset, with only English listings (as is the case with the `selenium` dataset), and with no duplicates<jupyter_code>show_df(df_search_results_requests, 2)
show_df_dtypes_nans(df_search_results_requests)<jupyter_output><empty_output><jupyter_text>3.2. [Slice columns to use for scraping with `requests` and export to file](slice-columns-to-use-for-scraping-with-using-`requests`-and-export-to-file) Finally, we'll now export this filtered search results dataset to a CSV file<jupyter_code>df_search_results_requests[["page", "listing_counter", "title", "url"]].to_csv(
    os.path.join(requests_files_dir, "requests_listings_to_scrape.csv"),
    index=False,
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08888025315050241, 'fraction_numerical': 0.00449675234552823, 'mean_word_length': 5.639882049391817, 'pattern_counts': {'":': 2, '<': 91, '<?xml version=': 0, '>': 95, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '558682', 'license': 'MIT', 'n_tokens_mistral': 5385, 'n_tokens_neox': 5126, 'n_words': 1869, 'path': '4_filter_requests_listings.ipynb', 'repo_name': 'elsdes3/steam-games-web-scraping-eda', 'size': '18013'}