clean_results_apple_R2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "aa80d57a-55de-4390-909c-8151853432bf",
   "metadata": {},
   "source": [
    "# Merge results, clean and remove duplicates\n",
    "## For Apple App Store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8a3b3d8a-2ab4-4e71-9c3d-d314473d144e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys, os, glob, re\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from markdownify import markdownify as md\n",
    "from urllib.parse import unquote"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "af8528cb-40e4-4cc9-86ee-6dc9fdc5d22b",
   "metadata": {},
   "source": [
    "### Define some functions\n",
    "#### Find a substring between two strings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6fb1d4d7-9015-4730-8026-83e37d9278e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "def find_between(s, first, last):\n",
    "    try:\n",
    "        if (last in s):\n",
    "          start = s.index(first) + len( first)\n",
    "          end = s.index(last, start)\n",
    "          return s[start:end]\n",
    "        else:\n",
    "          start = s.index(first) + len(first)\n",
    "          return s[start:]\n",
    "    except ValueError:\n",
    "        return \"\"\n",
    "\n",
    "def title_parse(title):\n",
    "    title = unquote(title)\n",
    "    return title"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be1d2983-096c-4c94-b13b-ba526377a70b",
   "metadata": {},
   "source": [
    "### Define some variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9aab22ab-9e62-4759-972c-95a74274a77a",
   "metadata": {},
   "outputs": [],
   "source": [
    "count = 0\n",
    "fullcount = 0\n",
    "start_appid = \"?id=\"\n",
    "end_appid = \"&hl=\"\n",
    "start_slang = \".com/\"\n",
    "end_slang = \"/app/\"\n",
    "file_begin_str = \"Google_Search_Results_apple_\"\n",
    "results_path = \"scraped/apple/data_miner_bystander_16_05_2023/\"\n",
    "clean_dir = \"cleaned/apple/16_05_2023/\"\n",
    "final_out_dir = \"final_results/\"\n",
    "df = pd.DataFrame()\n",
    "stats_total_raw = 0\n",
    "stats_total_cleaned = 0\n",
    "\n",
    "# create some arrays\n",
    "domains = ['google.com','google.co.uk','google.de','google.com.au', 'google.com.hk', 'google.co.kr', 'google.co.za', 'google.co.in']\n",
    "gl = ['us', 'uk', 'de', 'au', 'hk', 'kr', 'za', 'in']\n",
    "location = ['United States', 'United Kingdom', 'Germany', 'Australia', 'Hong Kong', 'South Korea', 'South Africa', 'India']\n",
    "\n",
    "#create an empty dataframe to collect some stats\n",
    "stats = pd.DataFrame(columns = ['Domain', 'Location', 'Raw¹', 'Cleaned', 'Unique²'])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4bdbd998-fe5a-43db-ba57-2839690ab337",
   "metadata": {},
   "source": [
    "### Iterate through each file and process it\n",
    "- Read in each CSV file with results collected with Data-Miner Scraper\n",
    "- Extract appid from URL\n",
    "- Extract search domain country from filename\n",
    "- Extract store language from URL\n",
    "- Add new column \"pref\" (Preference for results in English)\n",
    "- Add new column \"dupe_count\" (count of duplicates per search domain\n",
    "- Remove non-app entries based on URL\n",
    "- Save datasets in CSV with and without duplicates\n",
    "- Create markdown stats file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "fba0d7c9-ff3d-417d-abc1-0e0e977ba29f",
   "metadata": {
    "editable": true,
    "slideshow": {
     "slide_type": ""
    },
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "1) Reading 'Google_Search_Results_apple_au_297.csv' ... 297 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract and decode app URL name id from url ...\n",
      "4) Extract search domain country from filename ...\n",
      "5) Remove non-app entries based on url ... 31 rows deleted.\n",
      "6) Extract app store language from url ...\n",
      "7) Created cleaned CSV with duplicates per domain.\n",
      "8) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_apple_de_296.csv' ... 296 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract and decode app URL name id from url ...\n",
      "4) Extract search domain country from filename ...\n",
      "5) Remove non-app entries based on url ... 31 rows deleted.\n",
      "6) Extract app store language from url ...\n",
      "7) Created cleaned CSV with duplicates per domain.\n",
      "8) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_apple_hk_293.csv' ... 293 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract and decode app URL name id from url ...\n",
      "4) Extract search domain country from filename ...\n",
      "5) Remove non-app entries based on url ... 34 rows deleted.\n",
      "6) Extract app store language from url ...\n",
      "7) Created cleaned CSV with duplicates per domain.\n",
      "8) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_apple_in_295.csv' ... 295 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract and decode app URL name id from url ...\n",
      "4) Extract search domain country from filename ...\n",
      "5) Remove non-app entries based on url ... 32 rows deleted.\n",
      "6) Extract app store language from url ...\n",
      "7) Created cleaned CSV with duplicates per domain.\n",
      "8) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_apple_kr_292.csv' ... 292 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract and decode app URL name id from url ...\n",
      "4) Extract search domain country from filename ...\n",
      "5) Remove non-app entries based on url ... 31 rows deleted.\n",
      "6) Extract app store language from url ...\n",
      "7) Created cleaned CSV with duplicates per domain.\n",
      "8) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_apple_uk_298.csv' ... 298 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract and decode app URL name id from url ...\n",
      "4) Extract search domain country from filename ...\n",
      "5) Remove non-app entries based on url ... 31 rows deleted.\n",
      "6) Extract app store language from url ...\n",
      "7) Created cleaned CSV with duplicates per domain.\n",
      "8) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_apple_us_296.csv' ... 296 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract and decode app URL name id from url ...\n",
      "4) Extract search domain country from filename ...\n",
      "5) Remove non-app entries based on url ... 32 rows deleted.\n",
      "6) Extract app store language from url ...\n",
      "7) Created cleaned CSV with duplicates per domain.\n",
      "8) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_apple_za_297.csv' ... 297 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract and decode app URL name id from url ...\n",
      "4) Extract search domain country from filename ...\n",
      "5) Remove non-app entries based on url ... 31 rows deleted.\n",
      "6) Extract app store language from url ...\n",
      "7) Created cleaned CSV with duplicates per domain.\n",
      "8) Created cleaned CSV without duplicates per domain.\n",
      "------------------------\n",
      "1) Created CSV with duplicates 2111 rows.\n",
      "2) Created CSV without duplicates: 87 rows.\n",
      "3) Saved stats in markdown file\n"
     ]
    }
   ],
   "source": [
    "for file in sorted(glob.iglob(results_path  + '*.csv')):\n",
    "    \n",
    "    count = count + 1\n",
    "    filename = os.path.basename(file)\n",
    "    \n",
    "    print()\n",
    "    print(\"1) Reading '\" + filename + \"' ...\", end=\" \")\n",
    "    df2 = pd.read_csv(file, sep=',', header = 0, dtype='unicode')\n",
    "    num_rows = len(df2.index)\n",
    "    print(str(num_rows) + \" rows\")\n",
    "    \n",
    "    # https://apps.apple.com/us/app/cpr-save/id1486979583\n",
    "    print(\"2) Extract appid from url ...\")\n",
    "    df2['appid'] = df2['URL_clean'].str.extract(r'(id[0-9]+)')\n",
    "    \n",
    "    print(\"3) Extract and decode app URL name id from url ...\")\n",
    "    df2['nameid'] = df2['URL_clean'].str.extract(r'((?<=\\/app\\/).*?(?=).*(?=\\/))')\n",
    "    df2[\"nameid\"] = df2[\"nameid\"].astype(str).apply(lambda x: unquote(x))\n",
    "    \n",
    "    print(\"4) Extract search domain country from filename ...\")\n",
    "    df2['search_domain'] = filename[len(file_begin_str):len(file_begin_str)+2]\n",
    "    stats_domain_index = gl.index(filename[len(file_begin_str):len(file_begin_str)+2])\n",
    "    stats_domain = domains[stats_domain_index]\n",
    "    stats_location = location[stats_domain_index]\n",
    "    stats_raw_rows = len(df2.index)\n",
    "    \n",
    "    #drop anything other than https://play.google.com/??/apps/??\n",
    "    print(\"5) Remove non-app entries based on url ...\", end=\" \")\n",
    "    df2 = df2[df2['URL_clean'].str.contains('/app/')]\n",
    "    num_rows_new = len(df2.index)\n",
    "    print(str(num_rows - num_rows_new) + \" rows deleted.\")\n",
    "    \n",
    "    print(\"6) Extract app store language from url ...\")\n",
    "    df2['store_lang'] = df2.apply(lambda x: find_between(x['URL_clean'], start_slang, end_slang), axis=1)\n",
    "    \n",
    "    #add preference column\n",
    "    searchfor = ['us','gb','ie','au','ca']\n",
    "    df2['pref'] = np.where(df2['store_lang'].str.contains('|'.join(searchfor)), True, False)\n",
    "    \n",
    "    #copy dataframe and save cleaned results for specific google domain\n",
    "    df3 = df2.copy()\n",
    "    df3.sort_values(by=['appid', 'pref'], inplace=True)\n",
    "    df3['dupe_count'] = df3.groupby('appid').appid.transform('count')-1\n",
    "    stats_cleaned_rows = len(df3.index)\n",
    "    filename_dupes = clean_dir + \"dupes_clean_\" + re.sub('\\d+', str(stats_cleaned_rows), filename)\n",
    "    df3.to_csv(filename_dupes, index = False)\n",
    "    print (\"7) Created cleaned CSV with duplicates per domain.\")\n",
    "    \n",
    "    #save a copy of unique records per domain\n",
    "    df3 = df3.drop_duplicates(subset=['appid'], keep='last')\n",
    "    stats_unique_rows = len(df3.index)\n",
    "    filename_uni = clean_dir + \"unique_clean_\" + re.sub('\\d+', str(stats_unique_rows), filename)\n",
    "    df3.to_csv(filename_uni, index = False)\n",
    "    print (\"8) Created cleaned CSV without duplicates per domain.\")\n",
    "    \n",
    "    #add dataframe from next csv to existing dataframe\n",
    "    df = pd.concat([df,df2])\n",
    "    fullcount = fullcount + num_rows_new\n",
    "    \n",
    "    stats.loc[count] = [stats_domain, stats_location, stats_raw_rows, stats_cleaned_rows, stats_unique_rows]\n",
    "    stats_total_raw = stats_total_raw + stats_raw_rows\n",
    "    stats_total_cleaned = stats_total_cleaned + stats_cleaned_rows\n",
    "    \n",
    "#count duplicates and add to new column\n",
    "df.sort_values(by=['appid', 'pref'], inplace=True)\n",
    "df['dupe_count']=df.groupby('appid').appid.transform('count')-1\n",
    "    \n",
    "#save dataframe with dupes to csv\n",
    "print(\"------------------------\")\n",
    "df.to_csv(final_out_dir + \"final_results_apple_bystander_w_dupes.csv\", index = False)\n",
    "print (\"1) Created CSV with duplicates \" + str(fullcount) + \" rows.\")  \n",
    "\n",
    "#remove duplicates\n",
    "df = df.drop_duplicates(subset=['appid'], keep='last')\n",
    "num_rows_uni = len(df.index)\n",
    "\n",
    "#save unique dataframe to csv\n",
    "df.to_csv(final_out_dir + \"final_results_apple_bystander_unique.csv\", index = False)\n",
    "print (\"2) Created CSV without duplicates: \" + str(num_rows_uni) + \" rows.\")\n",
    "\n",
    "#get stats for unique entries (which entries were kept?)\n",
    "kept_arr = df['search_domain'].value_counts().reindex(gl, fill_value=0)\n",
    "kept_arr.sort_index(inplace = True)\n",
    "stats['Kept³'] = kept_arr.to_numpy()\n",
    "\n",
    "with open(clean_dir + 'stats.md', 'w') as f:\n",
    "    stats.loc[count+1] = [\"Totals\", \"\", str(stats_total_raw), str(stats_total_cleaned), \"–\" , str(sum(kept_arr))]\n",
    "    dfAsString = stats.to_html(index = False)\n",
    "    h = md(dfAsString, heading_style=\"ATX\")\n",
    "    f.write(h)\n",
    "    \n",
    "print (\"3) Saved stats in markdown file\")"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}