{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e02d8339",
   "metadata": {},
   "source": [
    "# Queries for Blog 1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68e46fa1",
   "metadata": {},
   "source": [
    "## Scenario 1: Worldwide gross of movies which has been shot in New Zealand, with minimum 7.5 rating.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "484787c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin --store-to result\n",
    "\n",
    "g.V().has('place', 'name', containing('New Zealand')).in().has('movie', 'rating', gt(7.5)).dedup().valueMap(['name', 'gross_worldwide', 'rating', 'studio','id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15b40b70",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.DataFrame(result)\n",
    "for c in df:\n",
    "    df[c] = df[c].apply(lambda x: x[-1])\n",
    "\n",
    "df.sort_values(\"gross_worldwide\", ascending=False).reset_index(drop=True).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b602256",
   "metadata": {},
   "source": [
    "## Scenario 2: Top 50 movies which belong to Action and Drama genre and has Oscar winning actors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2adaf4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin --store result_action --silent\n",
    "\n",
    "g.V().has('genre', 'name', 'Action').in().has('movie', 'rating', gt(8.5)).dedup().valueMap(['name', 'year', 'poster'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51e52cca",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin --store result_drama --silent\n",
    "\n",
    "g.V().has('genre', 'name', 'Drama').in().has('movie', 'rating', gt(8.5)).dedup().valueMap(['name', 'year', 'poster'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "087ac2e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin --store result_actors --silent\n",
    "\n",
    "g.V().has('person', 'oscar_winner', true).in().has('movie', 'rating', gt(8.5)).dedup().valueMap(['name', 'year', 'poster'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ddb32501",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from IPython.display import Image, HTML\n",
    "\n",
    "df_action = pd.DataFrame(result_action)\n",
    "df_drama = pd.DataFrame(result_drama)\n",
    "df_actors = pd.DataFrame(result_actors)\n",
    "\n",
    "for df in [df_action, df_drama, df_actors]:\n",
    "    for c in df:\n",
    "        df[c] = df[c].apply(lambda x: x[-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4a9148a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.merge(df_drama, df_action, on=[\"name\", \"year\", \"poster\"])\n",
    "df = pd.merge(df, df_actors, on=[\"name\",\"year\", \"poster\"])\n",
    "\n",
    "df.poster = df.poster.apply(lambda x: f'<img src=\"{x}\" style=\"max-height:50px\"/>')\n",
    "\n",
    "HTML(df.to_html(escape=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4815caa5",
   "metadata": {},
   "source": [
    "## Scenario 3: Movies which has common keyword \"tattoo\" and \"assassin\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea8373c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin --store result\n",
    "\n",
    "\n",
    "g.V().has('keyword', 'name', 'assassin').in().has('keyword','name','tattoo').dedup().limit(10).valueMap(['name', 'year', 'poster'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b0c7840",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from IPython.display import Image, HTML\n",
    "\n",
    "df = pd.DataFrame(result)\n",
    "for c in df:\n",
    "    df[c] = df[c].apply(lambda x: x[-1])\n",
    "\n",
    "df.poster = df.poster.apply(lambda x: f'<img src=\"{x}\" style=\"max-height:30px\"/>')\n",
    "\n",
    "df.sort_values(\"year\", ascending=False).reset_index(drop=True)\n",
    "\n",
    "HTML(df.to_html(escape=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f29f9567",
   "metadata": {},
   "source": [
    "## Scenario 4: Movies that connect two actors Leonardo Dicaprio and Tom Hanks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "320892ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%gremlin --store result \n",
    "\n",
    "g.V().has('person', 'name', containing('Leonardo')).in().hasLabel('movie').out().hasLabel('person').has('name', 'Tom Hanks').path().by(valueMap('name', 'poster'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "008fe01f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from IPython.display import Image, HTML\n",
    "\n",
    "df = pd.DataFrame(result[0].objects)\n",
    "for c in df:\n",
    "    df[c] = df[c].apply(lambda x: x[-1])\n",
    "\n",
    "df.poster = df.poster.apply(lambda x: f'<img src=\"{x}\" style=\"max-height:30px\"/>')\n",
    "\n",
    "HTML(df.to_html(escape=False))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_amazonei_mxnet_p36",
   "language": "python",
   "name": "conda_amazonei_mxnet_p36"
  },
  "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.6.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}