Data Scientist With Python/Data Manipulation with pandas/Module 2 - Aggregating DataFrames.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3f61ea85",
   "metadata": {},
   "source": [
    "# Aggregating DataFrames\n",
    "\n",
    "## Mean and median\n",
    "Summary statistics are exactly what they sound like - they summarize many numbers in one statistic. For example, mean, median, minimum, maximum, and standard deviation are summary statistics. Calculating summary statistics allows you to get a better sense of your data, even if there's a lot of it.\n",
    "\n",
    "`sales` is available and `pandas` is loaded as pd.\n",
    "\n",
    "* Explore your new DataFrame first by printing the first few rows of the sales DataFrame.\n",
    "* Print information about the columns in `sales`.\n",
    "* Print the mean of the `weekly_sales` column.\n",
    "* Print the median of the `weekly_sales` column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7ab94edc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "sales = pd.read_csv('C:/Users/Александр/pj/DataCamp_projects/Data Scientist With Python/Data Manipulation with pandas Course 3/datasets/sales_subset.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "416fc9c8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Unnamed: 0  store type  department        date  weekly_sales  is_holiday  \\\n",
      "0           0      1    A           1  2010-02-05      24924.50       False   \n",
      "1           1      1    A           1  2010-03-05      21827.90       False   \n",
      "2           2      1    A           1  2010-04-02      57258.43       False   \n",
      "3           3      1    A           1  2010-05-07      17413.94       False   \n",
      "4           4      1    A           1  2010-06-04      17558.09       False   \n",
      "\n",
      "   temperature_c  fuel_price_usd_per_l  unemployment  \n",
      "0       5.727778              0.679451         8.106  \n",
      "1       8.055556              0.693452         8.106  \n",
      "2      16.816667              0.718284         7.808  \n",
      "3      22.527778              0.748928         7.808  \n",
      "4      27.050000              0.714586         7.808  \n"
     ]
    }
   ],
   "source": [
    "# Print the head of the sales DataFrame\n",
    "print(sales.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "e8856269",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10774 entries, 0 to 10773\n",
      "Data columns (total 10 columns):\n",
      " #   Column                Non-Null Count  Dtype  \n",
      "---  ------                --------------  -----  \n",
      " 0   Unnamed: 0            10774 non-null  int64  \n",
      " 1   store                 10774 non-null  int64  \n",
      " 2   type                  10774 non-null  object \n",
      " 3   department            10774 non-null  int64  \n",
      " 4   date                  10774 non-null  object \n",
      " 5   weekly_sales          10774 non-null  float64\n",
      " 6   is_holiday            10774 non-null  bool   \n",
      " 7   temperature_c         10774 non-null  float64\n",
      " 8   fuel_price_usd_per_l  10774 non-null  float64\n",
      " 9   unemployment          10774 non-null  float64\n",
      "dtypes: bool(1), float64(4), int64(3), object(2)\n",
      "memory usage: 768.2+ KB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# Print the info about the sales DataFrame\n",
    "print(sales.info())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2d028937",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "23843.950148505668\n"
     ]
    }
   ],
   "source": [
    "# Print the mean of weekly_sales\n",
    "print(sales[\"weekly_sales\"].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "cb260b58",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "12049.064999999999\n"
     ]
    }
   ],
   "source": [
    "# Print the median of weekly_sales\n",
    "print(sales[\"weekly_sales\"].median())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd825997",
   "metadata": {},
   "source": [
    "## Summarizing dates\n",
    "Summary statistics can also be calculated on date columns that have values with the data type datetime64. Some summary statistics — like mean — don't make a ton of sense on dates, but others are super helpful, for example, minimum and maximum, which allow you to see what time range your data covers.\n",
    "\n",
    "`sales` is available and `pandas` is loaded as pd.\n",
    "\n",
    "* Print the maximum of the date column.\n",
    "* Print the minimum of the date column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a65a88a0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2012-10-26\n"
     ]
    }
   ],
   "source": [
    "# Print the maximum of the date column\n",
    "print(sales.date.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "37404c74",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2010-02-05\n"
     ]
    }
   ],
   "source": [
    "# Print the minimum of the date column\n",
    "print(sales.date.min())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "148bb4ff",
   "metadata": {},
   "source": [
    "Super summarizing! Taking the minimum and maximum of a column of dates is handy for figuring out what time period your data covers. In this case, there are data from February of 2010 to October of 2012.\n",
    "\n",
    "## Efficient summaries\n",
    "While pandas and NumPy have tons of functions, sometimes, you may need a different function to summarize your data.\n",
    "\n",
    "The `.agg()` method allows you to apply your own custom functions to a DataFrame, as well as apply functions to more than one column of a DataFrame at once, making your aggregations super-efficient. For example,\n",
    "```python\n",
    "df['column'].agg(function)\n",
    "```\n",
    "In the custom function for this exercise, \"IQR\" is short for inter-quartile range, which is the 75th percentile minus the 25th percentile. It's an alternative to standard deviation that is helpful if your data contains outliers.\n",
    "\n",
    "`sales` is available and `pandas` is loaded as pd.\n",
    "\n",
    "* Use the custom `iqr` function defined for you along with `.agg()` to print the IQR of the `temperature_c` column of sales."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "612a8f2e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "16.583333333333336\n"
     ]
    }
   ],
   "source": [
    "# A custom IQR function\n",
    "def iqr(column):\n",
    "    return column.quantile(0.75) - column.quantile(0.25)\n",
    "    \n",
    "# Print IQR of the temperature_c column\n",
    "print(sales[\"temperature_c\"].agg(iqr))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab3bba9c",
   "metadata": {},
   "source": [
    "* Update the column selection to use the custom `iqr` function with `.agg()` to print the IQR of temperature_c, fuel_price_usd_per_l, and unemployment, in that order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3fe09b72",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "temperature_c           16.583333\n",
      "fuel_price_usd_per_l     0.073176\n",
      "unemployment             0.565000\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Import NumPy and create custom IQR function\n",
    "import numpy as np\n",
    "def iqr(column):\n",
    "    return column.quantile(0.75) - column.quantile(0.25)\n",
    "\n",
    "# Update to print IQR and median of temperature_c, fuel_price_usd_per_l, & unemployment\n",
    "print(sales[[\"temperature_c\", \"fuel_price_usd_per_l\", \"unemployment\"]].agg(iqr))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11c3acad",
   "metadata": {},
   "source": [
    "* Update the aggregation functions called by `.agg()`: include `iqr` and `np.median` in that order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "398e8a68",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        temperature_c  fuel_price_usd_per_l  unemployment\n",
      "iqr         16.583333              0.073176         0.565\n",
      "median      16.966667              0.743381         8.099\n"
     ]
    }
   ],
   "source": [
    "# Import NumPy and create custom IQR function\n",
    "import numpy as np\n",
    "def iqr(column):\n",
    "    return column.quantile(0.75) - column.quantile(0.25)\n",
    "\n",
    "# Update to print IQR and median of temperature_c, fuel_price_usd_per_l, & unemployment\n",
    "print(sales[[\"temperature_c\", \"fuel_price_usd_per_l\", \"unemployment\"]].agg([iqr, np.median]))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d6e117f",
   "metadata": {},
   "source": [
    "## Cumulative statistics\n",
    "Cumulative statistics can also be helpful in tracking summary statistics over time. In this exercise, you'll calculate the cumulative sum and cumulative max of a department's weekly sales, which will allow you to identify what the total sales were so far as well as what the highest weekly sales were so far.\n",
    "\n",
    "A DataFrame called `sales_1_1` has been created for you, which contains the sales data for department 1 of store 1. `pandas` is loaded as pd.\n",
    "\n",
    "* Sort the rows of `sales_1_1` by the date column in ascending order.\n",
    "* Get the cumulative sum of `weekly_sales` and add it as a new column of `sales_1_1` called `cum_weekly_sales`.\n",
    "* Get the cumulative maximum of `weekly_sales`, and add it as a column called `cum_max_sales`.\n",
    "* Print the `date`, `weekly_sales`, `cum_weekly_sales`, and `cum_max_sales` columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "d021d65a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          date  weekly_sales  cum_weekly_sales  cum_max_sales\n",
      "0   2010-02-05      24924.50          24924.50       24924.50\n",
      "1   2010-03-05      21827.90          46752.40       24924.50\n",
      "2   2010-04-02      57258.43         104010.83       57258.43\n",
      "3   2010-05-07      17413.94         121424.77       57258.43\n",
      "4   2010-06-04      17558.09         138982.86       57258.43\n",
      "5   2010-07-02      16333.14         155316.00       57258.43\n",
      "6   2010-08-06      17508.41         172824.41       57258.43\n",
      "7   2010-09-03      16241.78         189066.19       57258.43\n",
      "8   2010-10-01      20094.19         209160.38       57258.43\n",
      "9   2010-11-05      34238.88         243399.26       57258.43\n",
      "10  2010-12-03      22517.56         265916.82       57258.43\n",
      "11  2011-01-07      15984.24         281901.06       57258.43\n"
     ]
    }
   ],
   "source": [
    "sales_1_1 = sales.query(\"department == 1 & store == 1\")\n",
    "\n",
    "# Sort sales_1_1 by date\n",
    "sales_1_1 = sales_1_1.sort_values(\"date\")\n",
    "\n",
    "# Get the cumulative sum of weekly_sales, add as cum_weekly_sales col\n",
    "sales_1_1[\"cum_weekly_sales\"] = sales_1_1[\"weekly_sales\"].cumsum()\n",
    "\n",
    "# Get the cumulative max of weekly_sales, add as cum_max_sales col\n",
    "sales_1_1[\"cum_max_sales\"] = sales_1_1[\"weekly_sales\"].cummax()\n",
    "\n",
    "# See the columns you calculated\n",
    "print(sales_1_1[[\"date\", \"weekly_sales\", \"cum_weekly_sales\", \"cum_max_sales\"]])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5631dbb1",
   "metadata": {},
   "source": [
    "## Dropping duplicates\n",
    "Removing duplicates is an essential skill to get accurate counts because often, you don't want to count the same thing multiple times. In this exercise, you'll create some new DataFrames using unique values from sales.\n",
    "\n",
    "`sales` is available and `pandas` is imported as pd.\n",
    "\n",
    "* Remove rows of sales with duplicate pairs of store and type and save as `store_types` and print the head.\n",
    "* Remove rows of sales with duplicate pairs of store and department and save as `store_depts` and print the head.\n",
    "* Subset the rows that are holiday weeks using the `is_holiday` column, and drop the duplicate dates, saving as `holiday_dates`.\n",
    "* Select the date column of `holiday_dates`, and print."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "b8fe0667",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      Unnamed: 0  store type  department        date  weekly_sales  \\\n",
      "0              0      1    A           1  2010-02-05      24924.50   \n",
      "901          901      2    A           1  2010-02-05      35034.06   \n",
      "1798        1798      4    A           1  2010-02-05      38724.42   \n",
      "2699        2699      6    A           1  2010-02-05      25619.00   \n",
      "3593        3593     10    B           1  2010-02-05      40212.84   \n",
      "\n",
      "      is_holiday  temperature_c  fuel_price_usd_per_l  unemployment  \n",
      "0          False       5.727778              0.679451         8.106  \n",
      "901        False       4.550000              0.679451         8.324  \n",
      "1798       False       6.533333              0.686319         8.623  \n",
      "2699       False       4.683333              0.679451         7.259  \n",
      "3593       False      12.411111              0.782478         9.765  \n",
      "    Unnamed: 0  store type  department        date  weekly_sales  is_holiday  \\\n",
      "0            0      1    A           1  2010-02-05      24924.50       False   \n",
      "12          12      1    A           2  2010-02-05      50605.27       False   \n",
      "24          24      1    A           3  2010-02-05      13740.12       False   \n",
      "36          36      1    A           4  2010-02-05      39954.04       False   \n",
      "48          48      1    A           5  2010-02-05      32229.38       False   \n",
      "\n",
      "    temperature_c  fuel_price_usd_per_l  unemployment  \n",
      "0        5.727778              0.679451         8.106  \n",
      "12       5.727778              0.679451         8.106  \n",
      "24       5.727778              0.679451         8.106  \n",
      "36       5.727778              0.679451         8.106  \n",
      "48       5.727778              0.679451         8.106  \n",
      "498     2010-09-10\n",
      "691     2011-11-25\n",
      "2315    2010-02-12\n",
      "6735    2012-09-07\n",
      "6810    2010-12-31\n",
      "6815    2012-02-10\n",
      "6820    2011-09-09\n",
      "Name: date, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# Drop duplicate store/type combinations\n",
    "store_types = sales.drop_duplicates(subset=[\"store\", \"type\"])\n",
    "print(store_types.head())\n",
    "\n",
    "# Drop duplicate store/department combinations\n",
    "store_depts = sales.drop_duplicates(subset=[\"store\", \"department\"])\n",
    "print(store_depts.head())\n",
    "\n",
    "# Subset the rows where is_holiday is True and drop duplicate dates\n",
    "holiday_dates = sales[sales[\"is_holiday\"]].drop_duplicates(subset=\"date\")\n",
    "\n",
    "# Print date col of holiday_dates\n",
    "print(holiday_dates[\"date\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a08a60f4",
   "metadata": {},
   "source": [
    "## Counting categorical variables\n",
    "Counting is a great way to get an overview of your data and to spot curiosities that you might not notice otherwise. In this exercise, you'll count the number of each type of store and the number of each department number using the DataFrames you created in the previous exercise:\n",
    "```python\n",
    "# Drop duplicate store/type combinations\n",
    "store_types = sales.drop_duplicates(subset=[\"store\", \"type\"])\n",
    "\n",
    "# Drop duplicate store/department combinations\n",
    "store_depts = sales.drop_duplicates(subset=[\"store\", \"department\"])\n",
    "```\n",
    "The `store_types` and `store_depts` DataFrames you created in the last exercise are available, and `pandas` is imported as pd.\n",
    "\n",
    "* Count the number of stores of each store type in `store_types`.\n",
    "* Count the proportion of stores of each store type in `store_types`.\n",
    "* Count the number of different departments in `store_depts`, sorting the counts in descending order.\n",
    "* Count the proportion of different departments in `store_depts`, sorting the proportions in descending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "c7895227",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A    11\n",
      "B     1\n",
      "Name: type, dtype: int64\n",
      "A    0.916667\n",
      "B    0.083333\n",
      "Name: type, dtype: float64\n",
      "1     12\n",
      "55    12\n",
      "72    12\n",
      "71    12\n",
      "67    12\n",
      "      ..\n",
      "37    10\n",
      "48     8\n",
      "50     6\n",
      "39     4\n",
      "43     2\n",
      "Name: department, Length: 80, dtype: int64\n",
      "1     0.012917\n",
      "55    0.012917\n",
      "72    0.012917\n",
      "71    0.012917\n",
      "67    0.012917\n",
      "        ...   \n",
      "37    0.010764\n",
      "48    0.008611\n",
      "50    0.006459\n",
      "39    0.004306\n",
      "43    0.002153\n",
      "Name: department, Length: 80, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Count the number of stores of each type\n",
    "store_counts = store_types[\"type\"].value_counts()\n",
    "print(store_counts)\n",
    "\n",
    "# Get the proportion of stores of each type\n",
    "store_props = store_types[\"type\"].value_counts(normalize=True)\n",
    "print(store_props)\n",
    "\n",
    "# Count the number of each department number and sort\n",
    "dept_counts_sorted = store_depts[\"department\"].value_counts(sort=True)\n",
    "print(dept_counts_sorted)\n",
    "\n",
    "# Get the proportion of departments of each number and sort\n",
    "dept_props_sorted = store_depts[\"department\"].value_counts(sort=True, normalize=True)\n",
    "print(dept_props_sorted)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5ad10646",
   "metadata": {},
   "source": [
    "## What percent of sales occurred at each store type?\n",
    "While `.groupby()` is useful, you can calculate grouped summary statistics without it.\n",
    "\n",
    "Walmart distinguishes three types of stores: \"supercenters,\" \"discount stores,\" and \"neighborhood markets,\" encoded in this dataset as type \"A,\" \"B,\" and \"C.\" In this exercise, you'll calculate the total sales made at each store type, without using `.groupby()`. You can then use these numbers to see what proportion of Walmart's total sales were made at each type.\n",
    "\n",
    "`sales` is available and `pandas` is imported as pd.\n",
    "\n",
    "* Calculate the total `weekly_sales` over the whole dataset.\n",
    "* Subset for type \"A\" stores, and calculate their total weekly sales.\n",
    "* Do the same for type \"B\" and type \"C\" stores.\n",
    "* Combine the A/B/C results into a list, and divide by `sales_all` to get the proportion of sales by type."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "360c295c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[0.9097747 0.0902253 0.       ]\n"
     ]
    }
   ],
   "source": [
    "# Calc total weekly sales\n",
    "sales_all = sales[\"weekly_sales\"].sum()\n",
    "\n",
    "# Subset for type A stores, calc total weekly sales\n",
    "sales_A = sales[sales[\"type\"] == \"A\"][\"weekly_sales\"].sum()\n",
    "\n",
    "# Subset for type B stores, calc total weekly sales\n",
    "sales_B = sales[sales[\"type\"] == \"B\"][\"weekly_sales\"].sum()\n",
    "\n",
    "# Subset for type C stores, calc total weekly sales\n",
    "sales_C = sales[sales[\"type\"] == \"C\"][\"weekly_sales\"].sum()\n",
    "\n",
    "# Get proportion for each type\n",
    "sales_propn_by_type = [sales_A, sales_B, sales_C] / sales_all\n",
    "print(sales_propn_by_type)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db8a21b1",
   "metadata": {},
   "source": [
    "## Calculations with .groupby()\n",
    "The `.groupby()` method makes life much easier. In this exercise, you'll perform the same calculations as last time, except you'll use the `.groupby()` method. You'll also perform calculations on data grouped by two variables to see if sales differ by store type depending on if it's a holiday week or not.\n",
    "\n",
    "`sales` is available and `pandas` is loaded as pd."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "bba4db10",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "type\n",
      "A    0.909775\n",
      "B    0.090225\n",
      "Name: weekly_sales, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Group by type; calc total weekly sales\n",
    "sales_by_type = sales.groupby(\"type\")[\"weekly_sales\"].sum()\n",
    "\n",
    "# Get proportion for each type\n",
    "sales_propn_by_type = sales_by_type / sum(sales_by_type)\n",
    "print(sales_propn_by_type)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bca6957a",
   "metadata": {},
   "source": [
    "* Group sales by \"type\" and \"is_holiday\", take the sum of `weekly_sales`, and store as `sales_by_type_is_holiday`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "734a1b19",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "type  is_holiday\n",
      "A     False         2.336927e+08\n",
      "      True          2.360181e+04\n",
      "B     False         2.317678e+07\n",
      "      True          1.621410e+03\n",
      "Name: weekly_sales, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# From previous step\n",
    "sales_by_type = sales.groupby(\"type\")[\"weekly_sales\"].sum()\n",
    "\n",
    "# Group by type and is_holiday; calc total weekly sales\n",
    "sales_by_type_is_holiday = sales.groupby([\"type\", \"is_holiday\"])[\"weekly_sales\"].sum()\n",
    "print(sales_by_type_is_holiday)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4759ec8e",
   "metadata": {},
   "source": [
    "## Multiple grouped summaries\n",
    "Earlier in this chapter, you saw that the `.agg()` method is useful to compute multiple statistics on multiple variables. It also works with grouped data. NumPy, which is imported as np, has many different summary statistics functions, including: `np.min`, `np.max`, `np.mean`, and `np.median`.\n",
    "\n",
    "* Import `numpy` with the alias np.\n",
    "* Get the min, max, mean, and median of `weekly_sales` for each store type using `.groupby()` and `.agg()`. Store this as `sales_stats`. Make sure to use numpy functions!\n",
    "* Get the min, max, mean, and median of unemployment and `fuel_price_usd_per_l` for each store type. Store this as `unemp_fuel_stats`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "34ec21ea",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        amin       amax          mean    median\n",
      "type                                           \n",
      "A    -1098.0  293966.05  23674.667242  11943.92\n",
      "B     -798.0  232558.51  25696.678370  13336.08\n",
      "     unemployment                         fuel_price_usd_per_l            \\\n",
      "             amin   amax      mean median                 amin      amax   \n",
      "type                                                                       \n",
      "A           3.879  8.992  7.972611  8.067             0.664129  1.107410   \n",
      "B           7.170  9.765  9.279323  9.199             0.760023  1.107674   \n",
      "\n",
      "                          \n",
      "          mean    median  \n",
      "type                      \n",
      "A     0.744619  0.735455  \n",
      "B     0.805858  0.803348  \n"
     ]
    }
   ],
   "source": [
    "# Import numpy with the alias np\n",
    "import numpy as np\n",
    "\n",
    "# For each store type, aggregate weekly_sales: get min, max, mean, and median\n",
    "sales_stats = sales.groupby(\"type\")[\"weekly_sales\"].agg([np.min, np.max, np.mean, np.median])\n",
    "\n",
    "# Print sales_stats\n",
    "print(sales_stats)\n",
    "\n",
    "# For each store type, aggregate unemployment and fuel_price_usd_per_l: get min, max, mean, and median\n",
    "unemp_fuel_stats = sales.groupby(\"type\")[[\"unemployment\", \"fuel_price_usd_per_l\"]].agg([np.min, np.max, np.mean, np.median])\n",
    "\n",
    "# Print unemp_fuel_stats\n",
    "print(unemp_fuel_stats)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df00fe0f",
   "metadata": {},
   "source": [
    "## Pivoting on one variable\n",
    "Pivot tables are the standard way of aggregating data in spreadsheets. In pandas, pivot tables are essentially just another way of performing grouped calculations. That is, the `.pivot_table()` method is just an alternative to `.groupby()`.\n",
    "\n",
    "In this exercise, you'll perform calculations using `.pivot_table()` to replicate the calculations you performed in the last lesson using `.groupby()`.\n",
    "\n",
    "* Get the mean `weekly_sales` by type using `.pivot_table()` and store as `mean_sales_by_type`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "c507bb1d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      weekly_sales\n",
      "type              \n",
      "A     23674.667242\n",
      "B     25696.678370\n"
     ]
    }
   ],
   "source": [
    "# Pivot for mean weekly_sales for each store type\n",
    "mean_sales_by_type = sales.pivot_table(values=\"weekly_sales\", index=\"type\")\n",
    "\n",
    "# Print mean_sales_by_type\n",
    "print(mean_sales_by_type)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "64da05bd",
   "metadata": {},
   "source": [
    "* Get the mean and median (using NumPy functions) of `weekly_sales` by type using `.pivot_table()` and store as `mean_med_sales_by_type`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a37bde1e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              mean       median\n",
      "      weekly_sales weekly_sales\n",
      "type                           \n",
      "A     23674.667242     11943.92\n",
      "B     25696.678370     13336.08\n"
     ]
    }
   ],
   "source": [
    "# Import NumPy as np\n",
    "import numpy as np\n",
    "\n",
    "# Pivot for mean and median weekly_sales for each store type\n",
    "mean_med_sales_by_type = sales.pivot_table(values=\"weekly_sales\", index=\"type\", aggfunc=[np.mean, np.median])\n",
    "\n",
    "# Print mean_med_sales_by_type\n",
    "print(mean_med_sales_by_type)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e903106d",
   "metadata": {},
   "source": [
    "* Get the mean of `weekly_sales` by type and is_holiday using `.pivot_table()` and store as `mean_sales_by_type_holiday`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "1f2020fb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "is_holiday         False       True\n",
      "type                               \n",
      "A           23768.583523  590.04525\n",
      "B           25751.980533  810.70500\n"
     ]
    }
   ],
   "source": [
    "# Pivot for mean weekly_sales by store type and holiday \n",
    "mean_sales_by_type_holiday = sales.pivot_table(values=\"weekly_sales\", index=\"type\", columns=\"is_holiday\")\n",
    "\n",
    "# Print mean_sales_by_type_holiday\n",
    "print(mean_sales_by_type_holiday)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "582941dc",
   "metadata": {},
   "source": [
    "## Fill in missing values and sum values with pivot tables\n",
    "The .pivot_table() method has several useful arguments, including fill_value and margins.\n",
    "\n",
    "* `fill_value` replaces missing values with a real value (known as imputation). What to replace missing values with is a topic big enough to have its own course (Dealing with Missing Data in Python), but the simplest thing to do is to substitute a dummy value.\n",
    "* `margins` is a shortcut for when you pivoted by two variables, but also wanted to pivot by each of those variables separately: it gives the row and column totals of the pivot table contents.\n",
    "\n",
    "In this exercise, you'll practice using these arguments to up your pivot table skills, which will help you crunch numbers more efficiently!\n",
    "\n",
    "\n",
    "* Print the mean `weekly_sales` by department and type, filling in any missing values with 0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "eae7808a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "type                    A              B\n",
      "department                              \n",
      "1            30961.725379   44050.626667\n",
      "2            67600.158788  112958.526667\n",
      "3            17160.002955   30580.655000\n",
      "4            44285.399091   51219.654167\n",
      "5            34821.011364   63236.875000\n",
      "...                   ...            ...\n",
      "95          123933.787121   77082.102500\n",
      "96           21367.042857    9528.538333\n",
      "97           28471.266970    5828.873333\n",
      "98           12875.423182     217.428333\n",
      "99             379.123659       0.000000\n",
      "\n",
      "[80 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "# Print mean weekly_sales by department and type; fill missing values with 0\n",
    "print(sales.pivot_table(values=\"weekly_sales\", index=\"department\", columns=\"type\", fill_value=0))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6606e2b",
   "metadata": {},
   "source": [
    "* Print the mean `weekly_sales` by department and type, filling in any missing values with 0 and summing all rows and columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "440ce049",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "type                   A              B           All\n",
      "department                                           \n",
      "1           30961.725379   44050.626667  32052.467153\n",
      "2           67600.158788  112958.526667  71380.022778\n",
      "3           17160.002955   30580.655000  18278.390625\n",
      "4           44285.399091   51219.654167  44863.253681\n",
      "5           34821.011364   63236.875000  37189.000000\n",
      "...                  ...            ...           ...\n",
      "96          21367.042857    9528.538333  20337.607681\n",
      "97          28471.266970    5828.873333  26584.400833\n",
      "98          12875.423182     217.428333  11820.590278\n",
      "99            379.123659       0.000000    379.123659\n",
      "All         23674.667242   25696.678370  23843.950149\n",
      "\n",
      "[81 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "# Print the mean weekly_sales by department and type; fill missing values with 0s; sum all rows and cols\n",
    "print(sales.pivot_table(values=\"weekly_sales\", index=\"department\", columns=\"type\", fill_value=0, margins=True))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd8717b8",
   "metadata": {},
   "source": [
    "Magnificent margin making! You are now armed with pivot table skills that can help you compute summaries at multiple grouped levels in one line of code."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}