clean_results_android_R2.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "aa80d57a-55de-4390-909c-8151853432bf",
   "metadata": {},
   "source": [
    "# Merge results, clean and remove duplicates\n",
    "## For Google Play Store (Round 2)"
   ]
  },
  {
   "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"
   ]
  },
  {
   "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 \"\""
   ]
  },
  {
   "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 = \"&hl=\"\n",
    "end_slang = \"&gl=\"\n",
    "file_begin_str = \"Google_Search_Results_\"\n",
    "results_path = \"scraped/android/data_miner_bystander_02_08_2022/\"\n",
    "clean_dir = \"cleaned/android/02_08_2022/\"\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",
    "- Remove non-app entries based on URL\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",
    "- 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": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "1) Reading 'Google_Search_Results_au_144.csv' ... 144 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract search domain country from filename ...\n",
      "4) Remove non-app entries based on url ... 8 rows deleted.\n",
      "5) Extract app store language from url (hl) ...\n",
      "6) Created cleaned CSV with duplicates per domain.\n",
      "7) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_de_199.csv' ... 199 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract search domain country from filename ...\n",
      "4) Remove non-app entries based on url ... 8 rows deleted.\n",
      "5) Extract app store language from url (hl) ...\n",
      "6) Created cleaned CSV with duplicates per domain.\n",
      "7) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_hk_178.csv' ... 178 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract search domain country from filename ...\n",
      "4) Remove non-app entries based on url ... 5 rows deleted.\n",
      "5) Extract app store language from url (hl) ...\n",
      "6) Created cleaned CSV with duplicates per domain.\n",
      "7) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_in_104.csv' ... 104 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract search domain country from filename ...\n",
      "4) Remove non-app entries based on url ... 4 rows deleted.\n",
      "5) Extract app store language from url (hl) ...\n",
      "6) Created cleaned CSV with duplicates per domain.\n",
      "7) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_kr_184.csv' ... 184 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract search domain country from filename ...\n",
      "4) Remove non-app entries based on url ... 4 rows deleted.\n",
      "5) Extract app store language from url (hl) ...\n",
      "6) Created cleaned CSV with duplicates per domain.\n",
      "7) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_uk_87.csv' ... 87 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract search domain country from filename ...\n",
      "4) Remove non-app entries based on url ... 8 rows deleted.\n",
      "5) Extract app store language from url (hl) ...\n",
      "6) Created cleaned CSV with duplicates per domain.\n",
      "7) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_us_69.csv' ... 69 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract search domain country from filename ...\n",
      "4) Remove non-app entries based on url ... 4 rows deleted.\n",
      "5) Extract app store language from url (hl) ...\n",
      "6) Created cleaned CSV with duplicates per domain.\n",
      "7) Created cleaned CSV without duplicates per domain.\n",
      "\n",
      "1) Reading 'Google_Search_Results_za_116.csv' ... 116 rows\n",
      "2) Extract appid from url ...\n",
      "3) Extract search domain country from filename ...\n",
      "4) Remove non-app entries based on url ... 4 rows deleted.\n",
      "5) Extract app store language from url (hl) ...\n",
      "6) Created cleaned CSV with duplicates per domain.\n",
      "7) Created cleaned CSV without duplicates per domain.\n",
      "------------------------\n",
      "1) Created CSV with duplicates 1036 rows.\n",
      "2) Created CSV without duplicates: 38 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",
    "    print(\"2) Extract appid from url ...\")\n",
    "    df2['appid'] = df2.apply(lambda x: find_between(x['URL_clean'], start_appid, end_appid), axis=1)\n",
    "    df2['appid'].replace(r'\\&gl=.*', '', inplace=True, regex=True) #clean appid in case extraction wasnt clean enough\n",
    "    \n",
    "    print(\"3) 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/store/apps/details?\n",
    "    print(\"4) Remove non-app entries based on url ...\", end=\" \")\n",
    "    df2 = df2[df2['URL_clean'].str.contains('details?')]\n",
    "    num_rows_new = len(df2.index)\n",
    "    print(str(num_rows - num_rows_new) + \" rows deleted.\")\n",
    "     \n",
    "    print(\"5) Extract app store language from url (hl) ...\")\n",
    "    df2['store_lang'] = df2.apply(lambda x: find_between(x['URL_clean'], start_slang, end_slang), axis=1)\n",
    "    df2['store_lang'].replace('', 'en', inplace=True, regex=True) #fill empty with en\n",
    "    \n",
    "    #add preference column\n",
    "    df2['pref'] = np.where(df2['store_lang'].str.contains('en'), 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 (\"6) 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 (\"7) 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(clean_dir + \"final_results_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(clean_dir + \"final_results_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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}