DataFrame_pivot_table.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# Load Data\n",
    "userHeader = ['user_id', 'gender', 'age', 'ocupation', 'zip']\n",
    "users = pd.read_csv('~/Documents/Git/Example_Pandas/dataSet/users.txt', engine='python',\n",
    "                    sep='::', header=None, names=userHeader)\n",
    "\n",
    "movieHeader = ['movie_id', 'title', 'genders']\n",
    "movies = pd.read_csv('~/Documents/Git/Example_Pandas/dataSet/movies.txt', engine='python',\n",
    "                     sep='::', header=None, names=movieHeader)\n",
    "\n",
    "ratingHeader = ['user_id', 'movie_id', 'rating', 'timestamp']\n",
    "ratings = pd.read_csv('~/Documents/Git/Example_Pandas/dataSet/ratings.txt', engine='python',\n",
    "                      sep='::', header=None, names=ratingHeader)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge data\n",
    "mergeRatings = pd.merge(pd.merge(users, ratings), movies)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Clone DataFrame\n",
    "\n",
    "\n",
    "#modif pour faire des conflits\n",
    "\n",
    "#modif pour faire un conflit si tuveux vraimen!\n",
    "\n",
    "def cloneDF(df):\n",
    "    return pd.DataFrame(df.values.copy(), df.index.copy(), df.columns.copy()).apply(pd.to_numeric, errors='ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns(movie_id + title) to Index: \n",
      "                                                   age  ocupation    rating  \\\n",
      "movie_id title                                                                \n",
      "1        Toy Story (1995)                    27.700530   8.067886  4.146846   \n",
      "2        Jumanji (1995)                      27.800285   7.680456  3.201141   \n",
      "3        Grumpier Old Men (1995)             29.276151   7.826360  3.016736   \n",
      "4        Waiting to Exhale (1995)            27.788235   6.752941  2.729412   \n",
      "5        Father of the Bride Part II (1995)  27.425676   7.506757  3.006757   \n",
      "\n",
      "                                                timestamp      user_id  \n",
      "movie_id title                                                          \n",
      "1        Toy Story (1995)                    9.705586e+08  3053.819933  \n",
      "2        Jumanji (1995)                      9.717322e+08  3027.977175  \n",
      "3        Grumpier Old Men (1995)             9.734544e+08  2632.156904  \n",
      "4        Waiting to Exhale (1995)            9.716206e+08  3268.841176  \n",
      "5        Father of the Bride Part II (1995)  9.718301e+08  3143.152027  \n",
      "\n",
      "==================================================================\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Simple pivot (Groupby + avg)\n",
    "df_1 = cloneDF(mergeRatings)\n",
    "df_1 = df_1.pivot_table(index=['movie_id', 'title'])\n",
    "print('Columns(movie_id + title) to Index: \\n%s' % df_1[:5])\n",
    "print('\\n==================================================================\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns(movie_id + title) to Index and avg by values \n",
      "                                                   age    rating\n",
      "movie_id title                                                  \n",
      "1        Toy Story (1995)                    27.700530  4.146846\n",
      "2        Jumanji (1995)                      27.800285  3.201141\n",
      "3        Grumpier Old Men (1995)             29.276151  3.016736\n",
      "4        Waiting to Exhale (1995)            27.788235  2.729412\n",
      "5        Father of the Bride Part II (1995)  27.425676  3.006757\n",
      "\n",
      "==================================================================\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Pivot by specific columns (Groupby + avg by rating and age)\n",
    "df_2 = cloneDF(mergeRatings)\n",
    "df_2 = df_2.pivot_table(index=['movie_id', 'title'], values=['rating', 'age'])\n",
    "print('Columns(movie_id + title) to Index and avg by values \\n%s' % df_2[:5])\n",
    "print('\\n==================================================================\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns(movie_id + title) to Index and specific functions by values \n",
      "                                               sum   size      mean\n",
      "                                            rating rating    rating\n",
      "movie_id title                                                     \n",
      "1        Toy Story (1995)                     8613   2077  4.146846\n",
      "2        Jumanji (1995)                       2244    701  3.201141\n",
      "3        Grumpier Old Men (1995)              1442    478  3.016736\n",
      "4        Waiting to Exhale (1995)              464    170  2.729412\n",
      "5        Father of the Bride Part II (1995)    890    296  3.006757\n",
      "\n",
      "==================================================================\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Pivot by specific columns, applying a functions\n",
    "df_3 = cloneDF(mergeRatings)\n",
    "df_3 = df_3.pivot_table(index=['movie_id', 'title'], values=[\n",
    "                        'rating'], aggfunc=[np.sum, np.size, np.mean])\n",
    "print('Columns(movie_id + title) to Index and specific functions by values \\n%s' %\n",
    "      df_3[:5])\n",
    "print('\\n==================================================================\\n')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Columns(movie_id + title) to Index and avg rating applied by gender \n",
      "                                                 mean                    \n",
      "                                               rating                    \n",
      "gender                                              F         M       All\n",
      "movie_id title                                                           \n",
      "1        Toy Story (1995)                    4.187817  4.130552  4.146846\n",
      "2        Jumanji (1995)                      3.278409  3.175238  3.201141\n",
      "3        Grumpier Old Men (1995)             3.073529  2.994152  3.016736\n",
      "4        Waiting to Exhale (1995)            2.976471  2.482353  2.729412\n",
      "5        Father of the Bride Part II (1995)  3.212963  2.888298  3.006757\n"
     ]
    }
   ],
   "source": [
    "# Pivot by specific index, specific values and specific columns, applying a functions\n",
    "df_4 = cloneDF(mergeRatings)\n",
    "df_4 = df_4.pivot_table(index=['movie_id', 'title'], values=['rating'], columns=['gender'], aggfunc=[np.mean],\n",
    "                        fill_value=-1, margins=True)\n",
    "print('Columns(movie_id + title) to Index and avg rating applied by gender \\n%s' %\n",
    "      df_4[:5])"
   ]
  }
 ],
 "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"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}