03-compare_prices_ratings.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from scipy.stats import spearmanr\n",
    "from scripts.utils import save_fig\n",
    "\n",
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "plt.rcParams[\"figure.figsize\"] = (12, 4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# WA Primeur Ratings\n",
    "\n",
    "A noter: Clinet, La Conseillante, Ducru-Beaucaillou"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ratings = pd.read_excel('data/ratings/ratings.xlsx')\n",
    "ratings['Rating'] = (ratings['Rating - LB'] + ratings['Rating - HB'])/2\n",
    "\n",
    "#Select ratings up to 2017\n",
    "ratings = ratings[ratings['Vintage'] <= 2017]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# RP Ratings vs Price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wines_corpus = ratings['Chateau'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "prices = pd.read_excel(\"data/prices/prices_feb_2022.xlsx\", header=0, index_col=0)\n",
    "prices = prices.drop([2018, 2019, 2020], axis=1)\n",
    "prices = prices.reset_index().groupby([\"index\", \"Appellation\"]).first()\n",
    "prices.index.names = [\"Vineyard\", \"Appellation\"]\n",
    "prices = (\n",
    "    prices.transpose()\n",
    "    .reset_index()\n",
    "    .melt(id_vars=\"index\")\n",
    "    .rename(columns={\"index\": \"Vintage\", \"value\": \"Price\"})\n",
    ")\n",
    "prices[\"Price\"] = np.log(prices[\"Price\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "wines_corpus = [\n",
    "    \"Château Angélus\",\n",
    "    \"Château Ausone\",\n",
    "    \"Château Cheval Blanc\",\n",
    "    \"Château Figeac\",\n",
    "    \"Château Haut-Brion\",\n",
    "    \"Château L'Evangile\",\n",
    "    \"Château La Mission Haut-Brion\",\n",
    "    \"Château Lafite Rothschild\",\n",
    "    \"Château Léoville Las Cases\",\n",
    "    \"Château Margaux\",\n",
    "    \"Château Palmer\",\n",
    "    \"Château Pape Clément\",\n",
    "    \"Château Pavie\",\n",
    "    \"Château Trotanoy\",\n",
    "    \"Château Mouton Rothschild\",\n",
    "    \"Château Trotte Vieille\",\n",
    "    \"Petrus\",\n",
    "    'Château Montrose',\n",
    "    \"Vieux Château Certan\"\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "BEGIN_YEAR = 1994\n",
    "END_YEAR = 2013\n",
    "corrs = pd.DataFrame(index=wines_corpus, columns=[\"Corr\"])\n",
    "for vineyard in wines_corpus:\n",
    "    vineyard_prices = (\n",
    "        prices[prices[\"Vineyard\"] == vineyard]\n",
    "        .groupby(\"Vintage\")[[\"Price\"]]\n",
    "        .mean()\n",
    "        .interpolate()\n",
    "    )\n",
    "    vineyard_ratings = ratings.loc[\n",
    "        ratings[\"Chateau\"] == vineyard, [\"Vintage\", \"Rating\"]\n",
    "    ].set_index(\"Vintage\")\n",
    "    selected_ratings = vineyard_ratings.loc[BEGIN_YEAR:END_YEAR]\n",
    "    selected_prices = vineyard_prices.loc[BEGIN_YEAR:END_YEAR]\n",
    "    corrs.loc[vineyard, \"Corr\"] = spearmanr(selected_ratings, selected_prices)[0]\n",
    "corrs[\"Corr\"] = pd.to_numeric(corrs[\"Corr\"])\n",
    "corrs = corrs.sort_index()\n",
    "display(corrs)\n",
    "corrs.to_excel(\"data/rp_ratings_prices_spearman_correlations.xlsx\")\n",
    "display(\n",
    "    f\"Median: {round(corrs.median().values[0], 3)}, mean {round(corrs.mean().values[0], 3)}\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Evolution of correlations through time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "labels = []\n",
    "width = 10\n",
    "interval_starts = list(range(2017 - 1994 - width + 1))\n",
    "correlations = pd.DataFrame(columns=wines_corpus)\n",
    "for i in interval_starts:\n",
    "    begin = 1994 + i\n",
    "    end = begin + width\n",
    "    corr = []\n",
    "    for vineyard in wines_corpus:\n",
    "        price = prices.loc[\n",
    "            prices[\"Vineyard\"] == vineyard, [\"Vintage\", \"Price\"]\n",
    "        ].set_index(\"Vintage\")\n",
    "        rating = ratings.loc[\n",
    "            ratings[\"Chateau\"] == vineyard, [\"Vintage\", \"Rating\"]\n",
    "        ].set_index(\"Vintage\")\n",
    "        concat = (\n",
    "            pd.concat([price, rating], axis=1).dropna(how=\"any\", axis=0).loc[begin:end]\n",
    "        )\n",
    "        correlations.loc[begin, vineyard] = round(\n",
    "            spearmanr(concat.iloc[:, 0], concat.iloc[:, 1])[0], 3\n",
    "        )\n",
    "    labels.append(str(begin) + \"-\" + str(end))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "correlations = correlations.apply(pd.to_numeric).reset_index().rename(columns={'index':'Vintage'})\n",
    "correlations = correlations.melt(id_vars='Vintage', var_name='Chateau', value_name='Correlation')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(6, 4.5))\n",
    "font = {\"weight\": \"regular\", \"family\": \"serif\", \"size\": 16}\n",
    "plt.rc(\"font\", **font)\n",
    "plt.rc(\"xtick\", labelsize=\"13\")\n",
    "plt.rc(\"ytick\", labelsize=\"14\")\n",
    "plt.grid(True, which=\"major\", alpha=0.5)\n",
    "\n",
    "PROPS = {\n",
    "    \"boxprops\": {\"facecolor\": \"lightgrey\", \"edgecolor\": \"k\"},\n",
    "    \"medianprops\": {\"color\": \"k\"},\n",
    "    \"whiskerprops\": {\"color\": \"k\"},\n",
    "    \"capprops\": {\"color\": \"k\"},\n",
    "}\n",
    "sns.boxplot(data=correlations, x=\"Vintage\", y=\"Correlation\", **PROPS, width=0.6)\n",
    "ax = plt.gca()\n",
    "ax.set_xticklabels(labels, rotation=75)\n",
    "plt.ylim((0, 1))\n",
    "ax.set_ylabel(\"Spearman correlation\", labelpad=10)\n",
    "ax.set_xlabel(\"Vintage groups\", labelpad=10)\n",
    "ax.set_axisbelow(True)\n",
    "plt.tight_layout()\n",
    "save_fig(\"views/ratings_corr_through_time\", width_column=\"single\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mann Kendall Tau test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from scipy.stats import kendalltau\n",
    "\n",
    "kendalltau(correlations['Vintage'], correlations['Correlation'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "# End of notebook\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.5 ('base')",
   "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.5"
  },
  "vscode": {
   "interpreter": {
    "hash": "6264f468922f00fb0b021562f7c8975e1d70a24288160f1875032f8709927306"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}