extract.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this notebook contains code for extracting movie features from wikidata. here are the steps that I follow:\n",
    "\n",
    "# 1. get a list of all the movies\n",
    "# 2. run entity linking use the `find_wikidata_id` function to link them to a wikidata entity\n",
    "# 3. run the two SPARQL queries (3a and 3b)\n",
    "# 4. format data and write it to a csv\n",
    "\n",
    "import re\n",
    "from typing import List\n",
    "from more_itertools import chunked\n",
    "\n",
    "import mkwikidata\n",
    "import pandas as pd\n",
    "from tqdm import tqdm\n",
    "tqdm.pandas()\n",
    "\n",
    "\n",
    "from recommenders.datasets import movielens\n",
    "from recommenders.datasets.wikidata import find_wikidata_id # resolve movie titles to a QID\n",
    "\n",
    "\n",
    "# constants\n",
    "MOVIELENS_DATA_SIZE = \"100k\"\n",
    "WIKIDATA_BATCH_SIZE = 300\n",
    "SEED = 42"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 4.81k/4.81k [00:01<00:00, 3.05kKB/s]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>userID</th>\n",
       "      <th>itemID</th>\n",
       "      <th>rating</th>\n",
       "      <th>title</th>\n",
       "      <th>genre</th>\n",
       "      <th>year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>196</td>\n",
       "      <td>242</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Kolya (1996)</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>63</td>\n",
       "      <td>242</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Kolya (1996)</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>226</td>\n",
       "      <td>242</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Kolya (1996)</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>154</td>\n",
       "      <td>242</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Kolya (1996)</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>306</td>\n",
       "      <td>242</td>\n",
       "      <td>5.0</td>\n",
       "      <td>Kolya (1996)</td>\n",
       "      <td>Comedy</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userID  itemID  rating         title   genre  year\n",
       "0     196     242     3.0  Kolya (1996)  Comedy  1996\n",
       "1      63     242     3.0  Kolya (1996)  Comedy  1996\n",
       "2     226     242     5.0  Kolya (1996)  Comedy  1996\n",
       "3     154     242     3.0  Kolya (1996)  Comedy  1996\n",
       "4     306     242     5.0  Kolya (1996)  Comedy  1996"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# step 1: get a list of movies\n",
    "\n",
    "\n",
    "movielens_df = movielens.load_pandas_df(\n",
    "    size=MOVIELENS_DATA_SIZE,\n",
    "    genres_col='genre',\n",
    "    title_col='title',\n",
    "    year_col='year',\n",
    "    header=[\"userID\", \"itemID\", \"rating\"]\n",
    ")\n",
    "\n",
    "movielens_df.head(5) # quick look at the data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      " 91%|█████████ | 1525/1682 [07:51<00:51,  3.08it/s]ENTITY NOT FOUND\n",
      " 93%|█████████▎| 1565/1682 [08:02<00:34,  3.44it/s]ENTITY NOT FOUND\n",
      " 96%|█████████▋| 1621/1682 [08:21<00:20,  3.03it/s]ENTITY NOT FOUND\n",
      "100%|██████████| 1682/1682 [08:40<00:00,  3.23it/s]\n"
     ]
    }
   ],
   "source": [
    "# step 2: apply entity linking to all of the titles\n",
    "items_df = movielens_df[[\"itemID\",\"title\"]].drop_duplicates()\n",
    "items_df[\"wikiID\"] = items_df[\"title\"].progress_apply(find_wikidata_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemID</th>\n",
       "      <th>title</th>\n",
       "      <th>wikiID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>99626</th>\n",
       "      <td>1569</td>\n",
       "      <td>Vie est belle, La (Life is Rosey) (1987)</td>\n",
       "      <td>entityNotFound</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99795</th>\n",
       "      <td>1345</td>\n",
       "      <td>Day the Sun Turned Cold, The (Tianguo niezi) (...</td>\n",
       "      <td>entityNotFound</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99916</th>\n",
       "      <td>1634</td>\n",
       "      <td>Etz Hadomim Tafus (Under the Domin Tree) (1994)</td>\n",
       "      <td>entityNotFound</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       itemID                                              title  \\\n",
       "99626    1569           Vie est belle, La (Life is Rosey) (1987)   \n",
       "99795    1345  Day the Sun Turned Cold, The (Tianguo niezi) (...   \n",
       "99916    1634    Etz Hadomim Tafus (Under the Domin Tree) (1994)   \n",
       "\n",
       "               wikiID  \n",
       "99626  entityNotFound  \n",
       "99795  entityNotFound  \n",
       "99916  entityNotFound  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "items_df[items_df[\"wikiID\"] == \"entityNotFound\"] # we failed to find wiki IDs for only 3 movies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# step 3: extract features\n",
    "\n",
    "def sparql2pd(query: str, movie_qids: List[str], id_col: str = \"item\") -> pd.DataFrame:\n",
    "    # this function will retrieve data from wikidata for a list of movie ids\n",
    "    # i use the library `mkwikidata` to do this because python formatting strings and SPARQL do not mix well.\n",
    "    qids_format = \" \".join([f\"wd:{qid}\" for qid in movie_qids])\n",
    "\n",
    "    \n",
    "    result = mkwikidata.run_query(query, params={ \"movies\": qids_format })\n",
    "\n",
    "    # assert len(result[\"results\"][\"bindings\"]) == len(movie_qids) \n",
    "    # useful for ensuring queries don't have duplicate fields \n",
    "    \n",
    "    cols = result[\"head\"][\"vars\"]\n",
    "    data = pd.DataFrame.from_records(\n",
    "        { col: i[col][\"value\"] if col in i else \"\" for col in cols }\n",
    "        for i in result[\"results\"][\"bindings\"]\n",
    "    )\n",
    "    data[\"wikiID\"] = data.pop(id_col).apply(lambda url: re.findall(\"Q[0-9]+\",url)[0] )\n",
    "\n",
    "    return data\n",
    "\n",
    "# test_qids = [\"Q222720\",\"Q17738\",\"Q153723\",\"Q44578\",\"Q271830\"]\n",
    "qids = [q for q in items_df[\"wikiID\"].unique() if q !='entityNotFound']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# extracts:\n",
    "# - MPA rating\n",
    "# - Bechdel/mako mori P/F\n",
    "# - rotten tomatoes score\n",
    "query_3a = \"\"\"\n",
    "#title: Movie Metadata\n",
    "SELECT ?item ?itemLabel ?bechdelOutcomeLabel ?makoMoriOutcomeLabel ?mpaRatingLabel ?tomatoScore\n",
    "WHERE \n",
    "{\n",
    "  VALUES ?item {\n",
    "     $movies\n",
    "  }\n",
    "\n",
    "  OPTIONAL {           \n",
    "    ?item p:P5021 ?test .    \n",
    "    ?test ps:P5021\twd:Q4165246 ;  # filter for bechdel test\n",
    "          pq:P9259 ?bechdelOutcome \n",
    "  }\n",
    "\n",
    "  OPTIONAL {           \n",
    "    ?item p:P5021 ?test2 .    \n",
    "    ?test2 ps:P5021\twd:Q85783379 ;  # filter for mako mori test\n",
    "           pq:P9259 ?makoMoriOutcome \n",
    "  }\n",
    "  \n",
    "  OPTIONAL {\n",
    "    ?item p:P444 ?tomato .\n",
    "    ?tomato pq:P459 wd:Q108403393 ;\n",
    "            ps:P444 ?scoreString\n",
    "    \n",
    "    BIND(xsd:decimal(REPLACE(?scoreString, \"%\", \"\"))/100 AS ?tomatoScore)\n",
    "  }\n",
    "\n",
    "  OPTIONAL { ?item wdt:P1657 ?mpaRating . }   # MPA film rating\n",
    "  SERVICE wikibase:label { bd:serviceParam wikibase:language \"[AUTO_LANGUAGE],en\". }\n",
    "}\n",
    "\"\"\"\n",
    "\n",
    "df_3a = pd.concat( \n",
    "    sparql2pd(query_3a,qids_batch) for qids_batch in chunked(qids, WIKIDATA_BATCH_SIZE) # run queries with a few qids at a time\n",
    ").reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Q168154    2\n",
       "Q174284    2\n",
       "Q190956    2\n",
       "Q170564    2\n",
       "Q659609    2\n",
       "Q464032    2\n",
       "Q271830    2\n",
       "Q276523    2\n",
       "Q193570    2\n",
       "Q45354     1\n",
       "Name: wikiID, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# there are some duplicate ids returned by this query\n",
    "df_3a[\"wikiID\"].value_counts().head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>itemLabel</th>\n",
       "      <th>bechdelOutcomeLabel</th>\n",
       "      <th>makoMoriOutcomeLabel</th>\n",
       "      <th>mpaRatingLabel</th>\n",
       "      <th>tomatoScore</th>\n",
       "      <th>wikiID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Before Sunrise</td>\n",
       "      <td>fails</td>\n",
       "      <td>passes</td>\n",
       "      <td>R</td>\n",
       "      <td>1</td>\n",
       "      <td>Q659609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Before Sunrise</td>\n",
       "      <td>passes</td>\n",
       "      <td>passes</td>\n",
       "      <td>R</td>\n",
       "      <td>1</td>\n",
       "      <td>Q659609</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>Terminator 2: Judgment Day</td>\n",
       "      <td>fails</td>\n",
       "      <td>passes</td>\n",
       "      <td>R</td>\n",
       "      <td>0.92</td>\n",
       "      <td>Q170564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>Terminator 2: Judgment Day</td>\n",
       "      <td>passes</td>\n",
       "      <td>passes</td>\n",
       "      <td>R</td>\n",
       "      <td>0.92</td>\n",
       "      <td>Q170564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>Raiders of the Lost Ark</td>\n",
       "      <td>passes</td>\n",
       "      <td></td>\n",
       "      <td>PG</td>\n",
       "      <td>0.93</td>\n",
       "      <td>Q174284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>Raiders of the Lost Ark</td>\n",
       "      <td>fails</td>\n",
       "      <td></td>\n",
       "      <td>PG</td>\n",
       "      <td>0.93</td>\n",
       "      <td>Q174284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>Amadeus</td>\n",
       "      <td>passes</td>\n",
       "      <td></td>\n",
       "      <td>PG</td>\n",
       "      <td>0.89</td>\n",
       "      <td>Q190956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>Amadeus</td>\n",
       "      <td>passes</td>\n",
       "      <td></td>\n",
       "      <td>R</td>\n",
       "      <td>0.89</td>\n",
       "      <td>Q190956</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>560</th>\n",
       "      <td>The Third Man</td>\n",
       "      <td>fails</td>\n",
       "      <td>fails</td>\n",
       "      <td></td>\n",
       "      <td>0.99</td>\n",
       "      <td>Q271830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561</th>\n",
       "      <td>The Third Man</td>\n",
       "      <td>passes</td>\n",
       "      <td>fails</td>\n",
       "      <td></td>\n",
       "      <td>0.99</td>\n",
       "      <td>Q271830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>622</th>\n",
       "      <td>Sunset Boulevard</td>\n",
       "      <td>fails</td>\n",
       "      <td>passes</td>\n",
       "      <td></td>\n",
       "      <td>0.99</td>\n",
       "      <td>Q193570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>623</th>\n",
       "      <td>Sunset Boulevard</td>\n",
       "      <td>passes</td>\n",
       "      <td>passes</td>\n",
       "      <td></td>\n",
       "      <td>0.99</td>\n",
       "      <td>Q193570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>644</th>\n",
       "      <td>Batman &amp; Robin</td>\n",
       "      <td>passes</td>\n",
       "      <td>passes</td>\n",
       "      <td></td>\n",
       "      <td>0.12</td>\n",
       "      <td>Q276523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>645</th>\n",
       "      <td>Batman &amp; Robin</td>\n",
       "      <td>fails</td>\n",
       "      <td>passes</td>\n",
       "      <td></td>\n",
       "      <td>0.12</td>\n",
       "      <td>Q276523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>693</th>\n",
       "      <td>Cinema Paradiso</td>\n",
       "      <td>fails</td>\n",
       "      <td>fails</td>\n",
       "      <td>PG</td>\n",
       "      <td>0.9</td>\n",
       "      <td>Q464032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694</th>\n",
       "      <td>Cinema Paradiso</td>\n",
       "      <td>fails</td>\n",
       "      <td>fails</td>\n",
       "      <td>R</td>\n",
       "      <td>0.9</td>\n",
       "      <td>Q464032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>918</th>\n",
       "      <td>Once Upon a Time in the West</td>\n",
       "      <td>fails</td>\n",
       "      <td>passes</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>0.95</td>\n",
       "      <td>Q168154</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>919</th>\n",
       "      <td>Once Upon a Time in the West</td>\n",
       "      <td>passes</td>\n",
       "      <td>passes</td>\n",
       "      <td>PG-13</td>\n",
       "      <td>0.95</td>\n",
       "      <td>Q168154</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        itemLabel bechdelOutcomeLabel makoMoriOutcomeLabel  \\\n",
       "44                 Before Sunrise               fails               passes   \n",
       "45                 Before Sunrise              passes               passes   \n",
       "126    Terminator 2: Judgment Day               fails               passes   \n",
       "127    Terminator 2: Judgment Day              passes               passes   \n",
       "131       Raiders of the Lost Ark              passes                        \n",
       "132       Raiders of the Lost Ark               fails                        \n",
       "145                       Amadeus              passes                        \n",
       "146                       Amadeus              passes                        \n",
       "560                 The Third Man               fails                fails   \n",
       "561                 The Third Man              passes                fails   \n",
       "622              Sunset Boulevard               fails               passes   \n",
       "623              Sunset Boulevard              passes               passes   \n",
       "644                Batman & Robin              passes               passes   \n",
       "645                Batman & Robin               fails               passes   \n",
       "693               Cinema Paradiso               fails                fails   \n",
       "694               Cinema Paradiso               fails                fails   \n",
       "918  Once Upon a Time in the West               fails               passes   \n",
       "919  Once Upon a Time in the West              passes               passes   \n",
       "\n",
       "    mpaRatingLabel tomatoScore   wikiID  \n",
       "44               R           1  Q659609  \n",
       "45               R           1  Q659609  \n",
       "126              R        0.92  Q170564  \n",
       "127              R        0.92  Q170564  \n",
       "131             PG        0.93  Q174284  \n",
       "132             PG        0.93  Q174284  \n",
       "145             PG        0.89  Q190956  \n",
       "146              R        0.89  Q190956  \n",
       "560                       0.99  Q271830  \n",
       "561                       0.99  Q271830  \n",
       "622                       0.99  Q193570  \n",
       "623                       0.99  Q193570  \n",
       "644                       0.12  Q276523  \n",
       "645                       0.12  Q276523  \n",
       "693             PG         0.9  Q464032  \n",
       "694              R         0.9  Q464032  \n",
       "918          PG-13        0.95  Q168154  \n",
       "919          PG-13        0.95  Q168154  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# there are not so many so let's look at them all.\n",
    "dup_qids = list(df_3a[\"wikiID\"].value_counts().head(9).index)\n",
    "df_3a[df_3a[\"wikiID\"].isin(dup_qids)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "618      2\n",
       "619      2\n",
       "334      2\n",
       "328      2\n",
       "630      2\n",
       "        ..\n",
       "1333    21\n",
       "1334    21\n",
       "1335    21\n",
       "1475    21\n",
       "1661    21\n",
       "Name: priority, Length: 1653, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# deduplicate do this programatically by sorting in terms of 'priority' and then dropping duplicates\n",
    "\n",
    "priority_map = {\n",
    "    key: priority for priority, key in enumerate(['passes', 'fails','G','PG','PG-13','R','NC-17',''])\n",
    "}\n",
    "\n",
    "def get_priority(x) -> int:\n",
    "    # rank statements according to the following rules:\n",
    "    # - if there are conflicting tests, assume that it passed\n",
    "    # - if multiple MPA ratings, apply the least restrictive \n",
    "    duplicate_features = ['bechdelOutcomeLabel','makoMoriOutcomeLabel','mpaRatingLabel']\n",
    "    return sum([ priority_map.get(x[i], len(priority_map)) for i in duplicate_features ])    \n",
    "\n",
    "    \n",
    "df_3a[\"priority\"] = df_3a.apply(get_priority,axis=1)\n",
    "df_3a = df_3a.sort_values(by=[\"priority\"],ascending=True).drop_duplicates(subset=['wikiID','itemLabel'],keep=\"first\")\n",
    "df_3a.pop('priority')\n",
    "\n",
    "df_3a[df_3a[\"wikiID\"].isin(dup_qids)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# extracts: \n",
    "# - movie duration\n",
    "# - country of origin\n",
    "# - count of academy awards\n",
    "# - count of academy award nominations\n",
    "query_3b = \"\"\"\n",
    "#title: movie metadata that requires a GROUP BY statement\n",
    "SELECT \n",
    "  ?item\n",
    "  ( MAX(?duration) as ?maxDuration )\n",
    "  ( GROUP_CONCAT(DISTINCT ?originCountryLabel; SEPARATOR = \"|\") AS ?origin)\n",
    "  ( COUNT(DISTINCT ?award) AS ?academyAwardCount)\n",
    "  ( COUNT(DISTINCT ?nomination) AS ?academyNominationCount)\n",
    "\n",
    "WHERE\n",
    "{ \n",
    "  VALUES ?item {\n",
    "    $movies\n",
    "  }\n",
    "  \n",
    "  OPTIONAL { ?item wdt:P2047 ?duration }   # movie length (there are sommetimes multiple)  \n",
    "  \n",
    "  OPTIONAL {\n",
    "    # origin country\n",
    "    ?item wdt:P495 ?originCountry .\n",
    "    ?originCountry rdfs:label ?originCountryLabel\n",
    "    FILTER(LANG(?originCountryLabel) = \"en\")\n",
    "  }\n",
    "  \n",
    "  OPTIONAL {\n",
    "    # academy nominations\n",
    "    ?item p:P1411 ?nomination .\n",
    "    FILTER EXISTS {\n",
    "      ?nomination ps:P1411 ?nominationType .\n",
    "      ?nominationType wdt:P31 wd:Q19020 \n",
    "    } \n",
    "  }\n",
    "  \n",
    "  OPTIONAL {\n",
    "    # academy awards\n",
    "    ?item p:P166 ?award .\n",
    "    FILTER EXISTS {\n",
    "      ?award ps:P166 ?awardType .\n",
    "      ?awardType wdt:P31 wd:Q19020 \n",
    "    } \n",
    "  }\n",
    "\n",
    "  \n",
    "  SERVICE wikibase:label { bd:serviceParam wikibase:language \"en\". }\n",
    "}\n",
    "GROUP BY ?item ?itemLabel\n",
    "\"\"\"\n",
    "\n",
    "df_3b = pd.concat( \n",
    "    sparql2pd(query_3b,qids_batch) for qids_batch in chunked(qids, WIKIDATA_BATCH_SIZE) # run queries with a few qids at a time\n",
    ").reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1653, 1653, 1653)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(qids), len(df_3b[\"wikiID\"]),df_3b[\"wikiID\"].nunique() # no duplicate issue this time because of the GROUP BY statement\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# step 4: join and clean up\n",
    "\n",
    "movie_features_all = df_3a.merge(df_3b,on=\"wikiID\")\n",
    "movie_features_all.pop(\"itemLabel\")\n",
    "out = items_df.merge(movie_features_all,on=\"wikiID\",how=\"left\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "out.sort_values(by=\"itemID\").to_csv(\n",
    "    \"data/items_movielens_{size}.csv\".format(size=MOVIELENS_DATA_SIZE),\n",
    "    index=False\n",
    ")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.4"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}