Merging and Concat concept.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge and concat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({\n",
    "'A':[1,2,3,4],\n",
    "'B':[True,False,True,True],\n",
    "'C':['C1','C2','C3','C4']\n",
    "})\n",
    "df2 = pd.DataFrame({\n",
    "'A':[5,7,8,5],\n",
    "'B':[False,False,True,False],\n",
    "'D':['D1','D2','D3','D4']\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df1)\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Concat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df1,df2],axis=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df1,df2],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df1,df2],axis=0,ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({\n",
    "'A':[1,2,3,4],\n",
    "'B':[True,False,True,True],\n",
    "'C':['C1','C2','C3','C4']\n",
    "})\n",
    "df2 = pd.DataFrame({\n",
    "'A':[5,7,8,'NULL'],\n",
    "'B':[False,False,True,False],\n",
    "'D':['D1','D2','D3','NULL']\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "print(df1)\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df1,df2],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df1,df2],axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge\n",
    "#### LEFT MERGE\n",
    "- Keep every row in the left dataframe. Where there are missing values of the “on” variable in the right dataframe, add empty / NaN values in the result.\n",
    "- SYNTAX : pd.merge(left_dataframe, right_dataframe, on = 'column_name', how = 'left')\n",
    "- Alternate SYNTAX : pd.merge(left_dataframe, right_dataframe, left_on = 'column_name', right_on = 'column_name', how = 'left')\n",
    "#### RIGHT MERGE\n",
    "- Here, the merge operation keeps everything from the right dataframe\n",
    "- To perform the right merge, we just repeat the code above by simply changing the parameter of how from left to right. \n",
    "#### INNER MERGE\n",
    "- Pandas uses “inner” merge by default. This keeps only the common values in both the left and right dataframes for the merged data.\n",
    "- SYNTAX : pd.merge(left_dataframe, right_dataframe, on = 'column_name', how = 'inner')\n",
    "- Alternate SYNTAX : pd.merge(left_dataframe, right_dataframe, left_on = 'column_name', right_on = 'column_name', how = 'inner')\n",
    "#### OUTER MERGE\n",
    "- The “outer” merge combines all the rows for left and right dataframes with NaN when there are no matched values in the rows.\n",
    "- SYNTAX : pd.merge(left_dataframe, right_dataframe, on = 'column_name', how = 'outer')\n",
    "- Alternate SYNTAX : pd.merge(left_dataframe, right_dataframe, left_on = 'column_name', right_on = 'column_name', how = 'outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df1 = pd.DataFrame({\n",
    "# 'A':[1,2,3,4],\n",
    "# 'B':[True,False,True,True],\n",
    "# 'C':['C1','C2','C3','C4']\n",
    "# })\n",
    "# df2 = pd.DataFrame({\n",
    "# 'A':[4,7,8,'NULL'],\n",
    "# 'B':[True,False,True,False],\n",
    "# 'D':['D1','D2','D3','NULL']\n",
    "# })\n",
    "\n",
    "\n",
    "left = pd.DataFrame(\n",
    "    {\n",
    "        \"key1\": [\"K0\", \"K0\", \"K1\", \"K2\"],\n",
    "        \"key2\": [\"K0\", \"K1\", \"K0\", \"K1\"],\n",
    "        \"A\": [\"A0\", \"A1\", \"A2\", \"A3\"],\n",
    "        \"B\": [\"B0\", \"B1\", \"B2\", \"B3\"],\n",
    "    }\n",
    ")\n",
    "\n",
    "\n",
    "right = pd.DataFrame(\n",
    "    {\n",
    "        \"key1\": [\"K0\", \"K1\", \"K1\", \"K2\"],\n",
    "        \"key2\": [\"K0\", \"K0\", \"K0\", \"K0\"],\n",
    "        \"C\": [\"C0\", \"C1\", \"C2\", \"C3\"],\n",
    "        \"D\": [\"D0\", \"D1\", \"D2\", \"D3\"],\n",
    "    }\n",
    ")\n",
    "\n",
    "\n",
    "# result = pd.merge(left, right, on=[\"key1\", \"key2\"])\n",
    "# result = pd.merge(left, right, how=\"left\", on=[\"key1\", \"key2\"])\n",
    "# result = pd.merge(left, right, how=\"right\", on=[\"key1\", \"key2\"])\n",
    "# result = pd.merge(left, right, how=\"outer\", on=[\"key1\", \"key2\"])\n",
    "# result = pd.merge(left, right, how=\"inner\", on=[\"key1\", \"key2\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df1)\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "?pd.merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.merge(df1, df2, on = 'A', how = 'right')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "1bba0ed8"
   },
   "source": [
    "## Data analysis and manipulations using Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "72bef358"
   },
   "source": [
    "**Agenda**\n",
    "\n",
    "* Pandas Introduction\n",
    "* Loading DataFrames from file data\n",
    "* Preliminary data analysis\n",
    "* Data type conversion\n",
    "* Merging dataframes\n",
    "* Handling duplicate data\n",
    "* Handling missing data\n",
    "* Identifying anamolies in the data\n",
    "* Data aggregations\n",
    "* Date and Time related data manipulations\n",
    "* Understanding multi-indexing\n",
    "* Pivot tables - data reshape\n",
    "* Writing custom functions"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "efff48ba"
   },
   "source": [
    "### <font color='blue'>Problem Statement:</font>\n",
    "A retail giant which operates a chain of hyper markets across the multiple cities wants to develop new strategies for improving the business. \n",
    "\n",
    "In this process, the marketing team has provided a data to data analytics team to understand the patterns in their customer transactions in addition to profiling the customers based on their demographics and transactions. \n",
    "\n",
    "The data analytics team is tasked to extract insights in the data which should help in developing new strategies for improving the business. \n",
    "\n",
    "High level overview of the data:\n",
    "- Data has been provided for July- Sep 2018 Quarter which is from multiple data sources\n",
    "  - `Demographics.csv`: Consists of demographic data of about 5.9k records that has  information about the customer demographics.\n",
    "  - `Transactions.xlsx`: Consists of transactions data of about 260k records that has information about what product the customer purchased for what price\n",
    "  - `Products.tsv`: Consists of products data of about 3.5k records which has a mapping of the product ids with product category.\n",
    "\n",
    "- Given this data, we need to provide insights for developing new strategies\n",
    "- We use basic statistics and aggregations in this context."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "3a1c1491"
   },
   "source": [
    "Datasets shared in compressed file or\n",
    "In the shared server, data sets are available at the path **`/home/datasets/lab/`**."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5fc702ee"
   },
   "source": [
    "Import libraries under the respective aliases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "0229ce3c"
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "013a388f"
   },
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "52e71959"
   },
   "source": [
    "### Load data and create dataframes\n",
    "\n",
    "- Read csv files using the read_csv function.\n",
    "- Read tsv files using the read_csv function with a `tab \\t` seperator.\n",
    "- Read excel files using the read_excel function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "70e5c8d6"
   },
   "outputs": [],
   "source": [
    "demographics = pd.read_csv(\"datasets/Demographics.csv\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dcb54bc2"
   },
   "source": [
    "Display the sample content from each dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "4256bdcf"
   },
   "outputs": [],
   "source": [
    "demographics.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "221c37f6"
   },
   "source": [
    "List column names from each dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "f52d492d"
   },
   "outputs": [],
   "source": [
    "demographics.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "0bdd3a55"
   },
   "source": [
    "### Preliminary data analysis\n",
    "\n",
    "As the files are read, Do preliminary data analysis\n",
    "\n",
    "Generally this consists of understanding the data.\n",
    "- How many records and how many attributes are there?\n",
    "- What are the datatypes of these attributes?\n",
    "- Are there any missing values in the data?\n",
    "\n",
    "To check the first and last 5 records of the data, we use head and tail\n",
    "- dataframe.head() and \n",
    "- dataframe.tail(). \n",
    "\n",
    "We can specify a number in the parenthesis and those many records would be displayed.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "33425262"
   },
   "source": [
    "Verify first 3 records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ea2564d2"
   },
   "outputs": [],
   "source": [
    "demographics.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "c8bc281c"
   },
   "source": [
    "Verify the last 3 records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "996386c4"
   },
   "outputs": [],
   "source": [
    "demographics.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2533bc2d"
   },
   "source": [
    "Verify random sample data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "d8fb897d"
   },
   "outputs": [],
   "source": [
    "demographics.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "5fb1e7b3"
   },
   "source": [
    "To get an overall understanding of data at a high level\n",
    "- we use data.describe() function\n",
    "\n",
    "describe produces multiple summary statistics in one shot."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7cbf7f73"
   },
   "outputs": [],
   "source": [
    "demographics.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "aacf8aac"
   },
   "source": [
    "Observe that we have only the partial information. This is because, describe by default gives the summary of only numeric attributes. If we need to get information about all the attributes then we need to pass additional parameter to describe function.\n",
    "\n",
    "On non-numeric data, describe produces alternative summary statistics."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "46d73dfb"
   },
   "outputs": [],
   "source": [
    "demographics.describe(include=\"all\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "e0b2789f"
   },
   "outputs": [],
   "source": [
    "## Check the dimensions of a dataframe\n",
    "\n",
    "demographics.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "a3f6c384"
   },
   "source": [
    "To observe the data types of each attribute\n",
    "- We use dataframe.dtypes\n",
    "- The data types given are how python interpreted them. Sometimes, they may be interpreted incorrectly. \n",
    "\n",
    "In such cases, we may have to change them to appropriate data types\n",
    "    - Data types we have Integer/Numeric, Object (Category), Boolean."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "09f0d3f1"
   },
   "outputs": [],
   "source": [
    "# Understanding the data types for each data\n",
    "demographics.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "d205fb83"
   },
   "source": [
    "### How do we differentiate between numeric and categorical attributes?\n",
    "\n",
    "- Understading the variable names and doing a simple test. Does applying a statistical function like mean/average makes sense on that variable. If it does, then it is numeric else it is categorical\n",
    "- Looking at the data. If a particular variable has only a few values which are repeating, probably they are categorical."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "4868c64b"
   },
   "outputs": [],
   "source": [
    "cat_cols = ['Gender','Occupation','City_Category','Stay_In_Current_City_Years','Marital_Status']\n",
    "for i in cat_cols:\n",
    "    demographics[i] = demographics[i].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "c10042b0"
   },
   "outputs": [],
   "source": [
    "demographics.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cf284730"
   },
   "source": [
    "### Understand what is there in each data file\n",
    "\n",
    "- Are there any duplicates in the data?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "523b68f1"
   },
   "outputs": [],
   "source": [
    "demographics.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "4ed6300d"
   },
   "outputs": [],
   "source": [
    "demographics['User_ID'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "0d115a02"
   },
   "outputs": [],
   "source": [
    "demographics[demographics.duplicated(keep=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "389f0fc2"
   },
   "outputs": [],
   "source": [
    "demographics[demographics.duplicated(keep='last')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "59e94353"
   },
   "outputs": [],
   "source": [
    "demographics[demographics.duplicated(keep='first')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "4bd0b05e"
   },
   "outputs": [],
   "source": [
    "# Ignoring the duplicate records\n",
    "demo_no_duplicates = demographics.drop_duplicates(keep='first')\n",
    "\n",
    "print(demo_no_duplicates.shape)\n",
    "print(demo_no_duplicates['User_ID'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "4b631f48"
   },
   "outputs": [],
   "source": [
    "# If we need to consider set of columns for duplicates use subset attribute\n",
    "demographics[demographics.duplicated(subset=['User_ID','Gender','Age'],keep='first')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "bd6f6c98"
   },
   "source": [
    "### Check the distribution"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "a960ca67"
   },
   "outputs": [],
   "source": [
    "## To check the distribtuion of males and females \n",
    "\n",
    "# Of these 5900 customers, how many are male and how many are female\n",
    "demo_no_duplicates['Gender'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "43798db7"
   },
   "outputs": [],
   "source": [
    "# Inorder to get the distribution in probabilities or percentages\n",
    "demo_no_duplicates['Gender'].value_counts(normalize=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "c469ba65"
   },
   "source": [
    "- Observe that there are 4231 (≈72%) Males and 1669 (≈28%) Females. Males are nearly 2.5 times more than Females."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9a55b6e2"
   },
   "source": [
    "### Missing Value and Extreme Value analysis\n",
    "\n",
    "- Whenever we get the data, we need to check if the data has any missing or extreme values/ anomalies in the data which might affect our analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "07377628"
   },
   "outputs": [],
   "source": [
    "demo_no_duplicates.isna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "cf5b545c"
   },
   "outputs": [],
   "source": [
    "demo_no_duplicates.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "253fd87e"
   },
   "source": [
    "Observe that there are 3 missing values in the Age column. \n",
    "\n",
    "How to deal with these missing values?\n",
    "  -  We ask the business if the data for these missing records available. If yes, then we can get this information from them else we need to look at other approaches.\n",
    "  - One approach is to see what proportion of the data we have missing values. Is this a large value or a small value. If this value is very small compared to the number of records, we may choose to ignore these records. \n",
    "  -Other approach is to impute these missing values. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "02aad594"
   },
   "source": [
    "**Dropping the records**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "d353eaea"
   },
   "outputs": [],
   "source": [
    "# Method 1\n",
    "data_ig = demo_no_duplicates.dropna()\n",
    "print(\"Records after removing rows with missing values are: \",data_ig.shape)\n",
    "print(\"Records in original dataframe are: \",demo_no_duplicates.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7196e5f2"
   },
   "outputs": [],
   "source": [
    "print(\"Missing values in the dataframe after removal:\")\n",
    "print(data_ig.isna().sum())\n",
    "print(\"\\nMissing values in the original dataframe:\")\n",
    "print(demo_no_duplicates.isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "554afd60"
   },
   "outputs": [],
   "source": [
    "data_ig[\"Age\"].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "14bca691"
   },
   "source": [
    "**Imputing the missing values**\n",
    "  - Imputing is basically a method of \"guessing\" the value in place of missing value. One method of imputation is central imputation where you replace the null value with the mean value of the column\n",
    "  - But before imputing, we need to check if there are any extreme values in the data because the extreme values can impact the mean values.\n",
    "\n",
    "Observe that there are some points above 100 and the max is about 129 (recall that we got the max value when we used the describe function on the data. These values could be anamolies. One of the approaches to tackle these extremevalues isto ignore the records before imputing the data. We can discuss other methods later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "bb70bca5"
   },
   "outputs": [],
   "source": [
    "demo_no_duplicates['Age'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "24f7de71"
   },
   "outputs": [],
   "source": [
    "## General mistakes people do. If you use below command then null records also be filtered\n",
    "demo_no_duplicates[demo_no_duplicates['Age']<=100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "5bc6790d"
   },
   "outputs": [],
   "source": [
    "data1 = demo_no_duplicates[-demo_no_duplicates['Age'].isin(range(100,200))]\n",
    "print(\"Dimensions of the processed data: \", data1.shape)\n",
    "print(\"Missing values in Data processed\", data1.isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "6382d4ea"
   },
   "outputs": [],
   "source": [
    "# Lets describe the data again\n",
    "print(data1['Age'].describe())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "e7a387ca"
   },
   "outputs": [],
   "source": [
    "# Now let's impute the age\n",
    "print(\"The number of missing values before imputation:\\n\",data1.isna().sum())\n",
    "\n",
    "data1['Age'].fillna(data1['Age'].mean(), inplace=True)\n",
    "\n",
    "print(\"\\n\",data1.Age.describe(),\"\\n\")\n",
    "print(\"The number of missing values after imputation:\\n\",data1.isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "83b6dd44"
   },
   "outputs": [],
   "source": [
    "# Fill with a value\n",
    "# data1.fillna(0)\n",
    "# Fill with different values for different columns\n",
    "# data1.fillna({'Age': 0, 'Gender': 'M'})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "6149224f"
   },
   "source": [
    " ### Discretization and Binning\n",
    "\n",
    "Continuous data is often discretized or otherwise separated into “bins” for analysis.\n",
    "\n",
    "Suppose if we want to group Age into discrete age buckets.\n",
    "\n",
    "- Age Group binning\n",
    "    - Age is numeric data. But the question we need to ask is does a 50 year old person behaviur would be different from 51 year old. Or a 21 year old behaviour be different from 22 year old. If not, then we need not want individual information on age but we need a collective information like what products are being purchased by a specific age group ( and not individual)\n",
    "    - In order to group the customers based on Age we create our custom bins like 0-17 years, 18-25 years, 26-35 years, 36-45 years, 46-50 years, 51-55 years and 55+."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "0f18ccaf"
   },
   "outputs": [],
   "source": [
    "bins = [0, 17, 25, 35, 45, 50, 55, 100]\n",
    "labels=['0-17','18-25','26-35','36-45','46-50','51-55','55+'] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ec4858e6"
   },
   "outputs": [],
   "source": [
    "data1['Age_Group'] = pd.cut(data1['Age'], bins=bins, labels=labels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "c8acae21"
   },
   "outputs": [],
   "source": [
    "data1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "c5587638"
   },
   "source": [
    "Observe that people in the age group of 26-35 followed by 36-45 are more in number compared to other groups.\n",
    "\n",
    "\n",
    "**Pair-wise frequencies**\n",
    "- Let's visualize each of these in the form of a table. In excel we have a pivot table to do such analysis, here we have a crosstab\n",
    "    - How many customers from each Age_Group are there in each City_Category.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "2f99ad80"
   },
   "outputs": [],
   "source": [
    "pd.crosstab(data1['Age_Group'], data1['City_Category'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "949f1b9e"
   },
   "source": [
    "Observe that City_Category C has more customers in all the age groups\n",
    "\n",
    "**Question: How many customers of each gender belonging to each occupation?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "c9d0c46c"
   },
   "outputs": [],
   "source": [
    "pd.crosstab(data1['Gender'], data1['Occupation'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "77f552a2"
   },
   "source": [
    "## Pivot tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "99ee2fc2"
   },
   "outputs": [],
   "source": [
    "demo_no_duplicates.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "c590af5e"
   },
   "outputs": [],
   "source": [
    "## What is the average age for different genders\n",
    "pd.pivot_table(demo_no_duplicates, index=['Gender'],aggfunc=np.mean,values=['Age'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "da7dbbe2"
   },
   "outputs": [],
   "source": [
    "## Calculate median age of different genders in different cities\n",
    "table = pd.pivot_table(demo_no_duplicates,index=['Gender','City_Category','Marital_Status'],aggfunc=[np.median,np.mean],values=['Age'])\n",
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(demo_no_duplicates.head())\n",
    "print(table.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "e216bcd2"
   },
   "outputs": [],
   "source": [
    "table.query('Gender==[\"F\"]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "70d26e26"
   },
   "outputs": [],
   "source": [
    "table.query('City_Category==[\"A\",\"B\"]')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cc2cac6d"
   },
   "source": [
    "## Groupby"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "8cfcec23"
   },
   "outputs": [],
   "source": [
    "demo_no_duplicates.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "2c5214b5"
   },
   "outputs": [],
   "source": [
    "## What is the mean age of Female and Male\n",
    "\n",
    "demo_no_duplicates.groupby(['Gender'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "632a8c24"
   },
   "source": [
    "#### Note: The groupby() function returns a GroupBy object, but essentially describes how the rows of the original data set has been split. the GroupBy object .groups variable is a dictionary whose keys are the computed unique groups and corresponding values being the axis labels belonging to each group. For example:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7a2282d3"
   },
   "outputs": [],
   "source": [
    "demo_no_duplicates.groupby(['Gender']).groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "2b5cd3f5"
   },
   "outputs": [],
   "source": [
    "demo_no_duplicates.groupby(['Gender']).groups.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "347fb62f"
   },
   "outputs": [],
   "source": [
    "len(demo_no_duplicates.groupby(['Gender']).groups['F'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ee21c021"
   },
   "outputs": [],
   "source": [
    "## Getting the average of each gender group\n",
    "\n",
    "demo_no_duplicates.groupby(['Gender'])['Age'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "4c12b535"
   },
   "outputs": [],
   "source": [
    "## In city A, what is the average male and female ?\n",
    "\n",
    "demo_no_duplicates[demo_no_duplicates['City_Category'] == 'A'].groupby(['Gender'])['Age'].mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "2296209c"
   },
   "outputs": [],
   "source": [
    "## We can do group by on more than one variable\n",
    "\n",
    "## Question: City wise male and female count\n",
    "\n",
    "demo_no_duplicates.groupby(['City_Category','Gender'])['Age'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "f44c1ca9"
   },
   "source": [
    "### Groupby output format – Series or DataFrame?\n",
    "The output from a groupby and aggregation operation varies between Pandas Series and Pandas Dataframes, which can be confusing for new users. As a rule of thumb, if you calculate more than one column of results, your result will be a Dataframe. For a single column of results, the agg function, by default, will produce a Series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "3605de98"
   },
   "outputs": [],
   "source": [
    "#produces pandas series\n",
    "demo_no_duplicates.groupby(['Gender'])['Age'].sum()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7b293a5a"
   },
   "outputs": [],
   "source": [
    "#produces pandas dataframe\n",
    "gender_age_analysis = demo_no_duplicates.groupby(['Gender'])[['Age']].sum()\n",
    "gender_age_analysis\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "72915769"
   },
   "outputs": [],
   "source": [
    "temp = demo_no_duplicates.groupby(['City_Category','Gender'])[['Age']].mean()\n",
    "temp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ec2e8522"
   },
   "source": [
    "### Multi indexing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "e45ca91e"
   },
   "outputs": [],
   "source": [
    "temp.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "1c5bcb70"
   },
   "outputs": [],
   "source": [
    "temp.loc[('A','F'),:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "248003ee"
   },
   "outputs": [],
   "source": [
    "temp.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "c760bf7e"
   },
   "outputs": [],
   "source": [
    "temp.unstack(level=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "dd95d09a"
   },
   "outputs": [],
   "source": [
    "temp.unstack(level=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "d60137c3"
   },
   "source": [
    "### Multiple statistics per group"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "5c670b13"
   },
   "outputs": [],
   "source": [
    "demo_no_duplicates.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "f606ad87"
   },
   "outputs": [],
   "source": [
    "demo_no_duplicates.groupby(['Gender']).agg(\n",
    "        {'Age':'mean',\n",
    "         'Occupation':'count'})\n",
    "         \n",
    "         \n",
    "         "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "e8d7dab6"
   },
   "outputs": [],
   "source": [
    "## Applying multiple functions to columns in groups\n",
    "\n",
    "group1 = demo_no_duplicates.groupby(['Gender']).agg(\n",
    "        {'Age':['mean','min','max'],\n",
    "         'Occupation':['count','nunique']})\n",
    "\n",
    "group1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "c04fd975"
   },
   "outputs": [],
   "source": [
    "group1.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "8aaf46cf"
   },
   "outputs": [],
   "source": [
    "group1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(group1.columns)\n",
    "print(group1.columns.ravel())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "0c58b90b"
   },
   "outputs": [],
   "source": [
    "group1.columns = [\"_\".join(x) for x in group1.columns.ravel()]\n",
    "group1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "5d8c7842"
   },
   "outputs": [],
   "source": [
    "group1.loc['F','Age_mean']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "d9fdc61a"
   },
   "outputs": [],
   "source": [
    "\n",
    "group2= demo_no_duplicates.groupby(['Gender']).agg(\n",
    "        mean_Age           = ('Age','mean'),\n",
    "        min_age            = ('Age','min'),\n",
    "        max_age            = ('Age','max'),\n",
    "        occupation_count   = ('Occupation','count'),\n",
    "        occupation_nunique = ('Occupation','nunique'))\n",
    "\n",
    "group2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "964b96c6"
   },
   "outputs": [],
   "source": [
    "group2.loc['F','mean_Age']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "93cbeb6e"
   },
   "source": [
    "## Good to know"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "8a0d40d8"
   },
   "outputs": [],
   "source": [
    "products = pd.read_csv(\"datasets/Products.tsv\", sep = \"\\t\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "d7e95556"
   },
   "outputs": [],
   "source": [
    "transactions = pd.read_excel(\"datasets/Transactions.xlsx\", sheet_name=\"TransactionsData\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "871d33c2"
   },
   "outputs": [],
   "source": [
    "transactions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "products.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "name": "Day6_Preprocessing.ipynb",
   "provenance": []
  },
  "interpreter": {
   "hash": "d76a6713ea065ccca64d993c02c6b9e279dcce4f62a99101c64210a1216fb1dc"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}