Manipulating_DataFrames_with_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The data you will use the Pennsylvania election results. This data file election.csv is provided for you in data file. Load the csv file using read_cvs and before that import the pandas as pd. Use the 'county' column as index_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          state   total      Obama     Romney  winner  voters    turnout  \\\n",
      "county                                                                     \n",
      "Adams        PA   41973  35.482334  63.112001  Romney   61156  68.632677   \n",
      "Allegheny    PA  614671  56.640219  42.185820   Obama  924351  66.497575   \n",
      "Armstrong    PA   28322  30.696985  67.901278  Romney   42147  67.198140   \n",
      "Beaver       PA   80015  46.032619  52.637630  Romney  115157  69.483401   \n",
      "Bedford      PA   21444  22.057452  76.986570  Romney   32189  66.619031   \n",
      "\n",
      "              margin  \n",
      "county                \n",
      "Adams      27.629667  \n",
      "Allegheny  14.454399  \n",
      "Armstrong  37.204293  \n",
      "Beaver      6.605012  \n",
      "Bedford    54.929118  \n"
     ]
    }
   ],
   "source": [
    "\n",
    "import pandas as pd\n",
    "election = pd.read_csv('data/election.csv', index_col='county')\n",
    "print(election.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "election.loc['Bedford', 'winner']. That is, what is the row position of 'Bedford', and the column position of 'winner'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Romney\n"
     ]
    }
   ],
   "source": [
    "print(election.loc['Bedford','winner'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Find the who wind the election at Washington?. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Winner of election is Romney\n"
     ]
    }
   ],
   "source": [
    "print('Winner of election is ' + election.loc['Washington','winner'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a separate DataFrame results with the columns ['winner', 'total', 'voters'] and print the head of new dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           winner   total  voters\n",
      "county                           \n",
      "Adams      Romney   41973   61156\n",
      "Allegheny   Obama  614671  924351\n",
      "Armstrong  Romney   28322   42147\n",
      "Beaver     Romney   80015  115157\n",
      "Bedford    Romney   21444   32189\n"
     ]
    }
   ],
   "source": [
    "results = election[['winner', 'total','voters']]\n",
    "print(results.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Slicing rows\n",
    "The Pennsylvania US election results data set that you have been using so far is ordered by county name. This means that county names can be sliced alphabetically\n",
    "### EXE   \n",
    " Slice the row labels 'Perry' to 'Potter' and assingn your results to p_counties)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             state   total      Obama     Romney  winner   voters    turnout  \\\n",
      "county                                                                         \n",
      "Perry           PA   18240  29.769737  68.591009  Romney    27245  66.948064   \n",
      "Philadelphia    PA  653598  85.224251  14.051451   Obama  1099197  59.461407   \n",
      "Pike            PA   23164  43.904334  54.882576  Romney    41840  55.363289   \n",
      "Potter          PA    7205  26.259542  72.158223  Romney    10913  66.022175   \n",
      "\n",
      "                 margin  \n",
      "county                   \n",
      "Perry         38.821272  \n",
      "Philadelphia  71.172800  \n",
      "Pike          10.978242  \n",
      "Potter        45.898681  \n"
     ]
    }
   ],
   "source": [
    "p_counties =election['Perry':'Potter']\n",
    "print(p_counties)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### EXE 2:\n",
    "Slice the row labels 'Potter' to 'Perry' in reverse order. To do this for hypothetical row labels 'a' and 'b', you could use a stepsize of -1 like so: `df.loc['b':'a':-1]`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             state   total      Obama     Romney  winner   voters    turnout  \\\n",
      "county                                                                         \n",
      "Potter          PA    7205  26.259542  72.158223  Romney    10913  66.022175   \n",
      "Pike            PA   23164  43.904334  54.882576  Romney    41840  55.363289   \n",
      "Philadelphia    PA  653598  85.224251  14.051451   Obama  1099197  59.461407   \n",
      "Perry           PA   18240  29.769737  68.591009  Romney    27245  66.948064   \n",
      "\n",
      "                 margin  \n",
      "county                   \n",
      "Potter        45.898681  \n",
      "Pike          10.978242  \n",
      "Philadelphia  71.172800  \n",
      "Perry         38.821272  \n"
     ]
    }
   ],
   "source": [
    "p_counties_rev = election['Potter':'Perry':-1]\n",
    "print(p_counties_rev)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Slicing columns\n",
    "Similar to row slicing, columns can be sliced by value. In this exercise, your job is to slice column names from the Pennsylvania election results DataFrame using .loc[].\n",
    "### EXE\n",
    "\n",
    "1 - Slice the columns from the starting column to 'Obama' and assign the result to left_columns.\n",
    "\n",
    "2 - Slice the columns from 'Obama' to 'winner' and assign the result to middle_columns\n",
    "\n",
    "3 - Slice the columns from 'Romney' to the end and assign the result to right_columns and also print all of the assigned results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          state   total      Obama\n",
      "county                            \n",
      "Adams        PA   41973  35.482334\n",
      "Allegheny    PA  614671  56.640219\n",
      "Armstrong    PA   28322  30.696985\n",
      "Beaver       PA   80015  46.032619\n",
      "Bedford      PA   21444  22.057452\n"
     ]
    }
   ],
   "source": [
    "left_columns = election.loc[:,: 'Obama']\n",
    "print(left_columns.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               Obama     Romney  winner\n",
      "county                                 \n",
      "Adams      35.482334  63.112001  Romney\n",
      "Allegheny  56.640219  42.185820   Obama\n",
      "Armstrong  30.696985  67.901278  Romney\n",
      "Beaver     46.032619  52.637630  Romney\n",
      "Bedford    22.057452  76.986570  Romney\n"
     ]
    }
   ],
   "source": [
    "middle_columns = election.loc[:, 'Obama':'winner']\n",
    "print(middle_columns.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              Romney  winner  voters    turnout     margin\n",
      "county                                                    \n",
      "Adams      63.112001  Romney   61156  68.632677  27.629667\n",
      "Allegheny  42.185820   Obama  924351  66.497575  14.454399\n",
      "Armstrong  67.901278  Romney   42147  67.198140  37.204293\n",
      "Beaver     52.637630  Romney  115157  69.483401   6.605012\n",
      "Bedford    76.986570  Romney   32189  66.619031  54.929118\n"
     ]
    }
   ],
   "source": [
    "right_columns = election.loc[:, 'Romney' :]\n",
    "print(right_columns.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* You can use lists to select specific row and column labels with the .loc[] accessor. In this exercise, your job is to select the counties `['Philadelphia', 'Centre', 'Fulton']` and the columns `['winner','Obama','Romney']` from the election DataFrame, which has been pre-loaded for you with the index set to 'county'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "              winner      Obama     Romney\n",
      "county                                    \n",
      "Philadelphia   Obama  85.224251  14.051451\n",
      "Centre        Romney  48.948416  48.977486\n",
      "Fulton        Romney  21.096291  77.748861\n"
     ]
    }
   ],
   "source": [
    "rows = ['Philadelphia', 'Centre', 'Fulton']\n",
    "cols = ['winner','Obama','Romney']\n",
    "three_counties = election.loc[rows,cols]\n",
    "print(three_counties)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Thresholding data\n",
    "In this exercise,there is a column called 'turnout' that contains the percentage of voter turnout per county. Your job is to prepare a boolean array to select all of the rows and columns where voter turnout exceeded 70%.\n",
    "\n",
    "1-Create a boolean array of the condition where the 'turnout' column is greater than 70 and assign it to high_turnout.\n",
    "\n",
    "2-Filter the election DataFrame with the high_turnout array and assign it to high_turnout_df.\n",
    "\n",
    "3-Print the filtered DataFrame. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "             state   total      Obama     Romney  winner  voters    turnout  \\\n",
      "county                                                                        \n",
      "Bucks           PA  319407  49.966970  48.801686   Obama  435606  73.324748   \n",
      "Butler          PA   88924  31.920516  66.816607  Romney  122762  72.436096   \n",
      "Chester         PA  248295  49.228539  49.650617  Romney  337822  73.498766   \n",
      "Forest          PA    2308  38.734835  59.835355  Romney    3232  71.410891   \n",
      "Franklin        PA   62802  30.110506  68.583803  Romney   87406  71.850903   \n",
      "Montgomery      PA  401787  56.637223  42.286834   Obama  551105  72.905708   \n",
      "Westmoreland    PA  168709  37.567646  61.306154  Romney  238006  70.884347   \n",
      "\n",
      "                 margin  \n",
      "county                   \n",
      "Bucks          1.165284  \n",
      "Butler        34.896091  \n",
      "Chester        0.422079  \n",
      "Forest        21.100520  \n",
      "Franklin      38.473297  \n",
      "Montgomery    14.350390  \n",
      "Westmoreland  23.738508  \n"
     ]
    }
   ],
   "source": [
    "high_turnout = election.turnout > 70\n",
    "high_turnout_df = election[high_turnout]\n",
    "print(high_turnout_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have a pittsburgh2013.csv file that contain the daily weather data in Pittsburgh in 2013 obtained from Weather Underground. Read the file with read_csv functoin and name as weather. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Date  Max TemperatureF  Mean TemperatureF  Min TemperatureF  \\\n",
      "0  2013-1-1                32                 28                21   \n",
      "1  2013-1-2                25                 21                17   \n",
      "2  2013-1-3                32                 24                16   \n",
      "3  2013-1-4                30                 28                27   \n",
      "4  2013-1-5                34                 30                25   \n",
      "\n",
      "   Max Dew PointF  Mean Dew PointF  Min DewpointF  Max Humidity  \\\n",
      "0              30               27             16           100   \n",
      "1              14               12             10            77   \n",
      "2              19               15              9            77   \n",
      "3              21               19             17            75   \n",
      "4              23               20             16            75   \n",
      "\n",
      "   Mean Humidity  Min Humidity       ...        Max VisibilityMiles  \\\n",
      "0             89            77       ...                         10   \n",
      "1             67            55       ...                         10   \n",
      "2             67            56       ...                         10   \n",
      "3             68            59       ...                         10   \n",
      "4             68            61       ...                         10   \n",
      "\n",
      "   Mean VisibilityMiles  Min VisibilityMiles  Max Wind SpeedMPH  \\\n",
      "0                     6                    2                 10   \n",
      "1                    10                   10                 14   \n",
      "2                    10                   10                 17   \n",
      "3                    10                    6                 23   \n",
      "4                    10                   10                 16   \n",
      "\n",
      "   Mean Wind SpeedMPH  Max Gust SpeedMPH  PrecipitationIn   CloudCover  \\\n",
      "0                   8                NaN             0.00            8   \n",
      "1                   5                NaN             0.00            4   \n",
      "2                   8               26.0             0.00            3   \n",
      "3                  16               32.0             0.00            4   \n",
      "4                  10               23.0             0.21            5   \n",
      "\n",
      "   Events  WindDirDegrees  \n",
      "0    Snow             277  \n",
      "1     NaN             272  \n",
      "2     NaN             229  \n",
      "3     NaN             250  \n",
      "4     NaN             221  \n",
      "\n",
      "[5 rows x 23 columns]\n"
     ]
    }
   ],
   "source": [
    "weather = pd.read_csv('data/pittsburgh2013.csv')\n",
    "print(weather.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using apply() to transform a column\n",
    "The weather DataFrame has 'Mean TemperatureF' and 'Mean Dew PointF' columns. Write a function to convert degrees Fahrenheit to degrees Celsius and named as to_celsius:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def to_celsius(F):\n",
    "    return (F -32)*5/9"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1- Apply the to_celsius function over the ['Mean TemperatureF','Mean Dew PointF'] columns of the weather DataFrame.\n",
    "2- Reassign the columns of df_celsius to ['Mean TemperatureC','Mean Dew PointC']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   Mean TemperatureC  Mean Dew PointC\n",
      "0          -2.222222        -2.777778\n",
      "1          -6.111111       -11.111111\n",
      "2          -4.444444        -9.444444\n",
      "3          -2.222222        -7.222222\n",
      "4          -1.111111        -6.666667\n"
     ]
    }
   ],
   "source": [
    "df_celsius = weather[['Mean TemperatureF' , 'Mean Dew PointF']].apply(to_celsius)\n",
    "df_celsius.columns = ['Mean TemperatureC' , 'Mean Dew PointC']\n",
    "print(df_celsius.head())\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using .map() with a dictionary\n",
    "The .map() method is used to transform values according to a Python dictionary look-up. In this exercise you'll practice this method while returning to working with the election DataFrame.\n",
    "\n",
    "Your job is to use a dictionary to map the values 'Obama' and 'Romney' in the 'winner' column to the values 'blue' and 'red', and assign the output to the new column 'color'.\n",
    "\n",
    "1-Create a dictionary with the key:value pairs 'Obama':'Blue' and 'Romney':'red'.\n",
    "\n",
    "2-Use the .map() method on the 'winner' column using the red_vs_blue dictionary you created.\n",
    "\n",
    "3-Print the output of election.head(). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "          state   total      Obama     Romney  winner  voters    turnout  \\\n",
      "county                                                                     \n",
      "Adams        PA   41973  35.482334  63.112001  Romney   61156  68.632677   \n",
      "Allegheny    PA  614671  56.640219  42.185820   Obama  924351  66.497575   \n",
      "Armstrong    PA   28322  30.696985  67.901278  Romney   42147  67.198140   \n",
      "Beaver       PA   80015  46.032619  52.637630  Romney  115157  69.483401   \n",
      "Bedford      PA   21444  22.057452  76.986570  Romney   32189  66.619031   \n",
      "\n",
      "              margin color  \n",
      "county                      \n",
      "Adams      27.629667   Red  \n",
      "Allegheny  14.454399  Blue  \n",
      "Armstrong  37.204293   Red  \n",
      "Beaver      6.605012   Red  \n",
      "Bedford    54.929118   Red  \n"
     ]
    }
   ],
   "source": [
    "red_vs_blue = {'Obama' : 'Blue' , 'Romney' : 'Red'}\n",
    "election['color'] = election.winner.map(red_vs_blue)\n",
    "print(election.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Changing index of a DataFrame\n",
    " indexes are immutable objects. This means that if you want to change or modify the index in a DataFrame, then you need to change the whole index. You will do this now, using a list comprehension to create the new index.\n",
    "\n",
    "A list comprehension is a succinct way to generate a list in one line. For example, the following list comprehension generates a list that contains the cubes of all numbers from 0 to 9: cubes = [i**3 for i in range(10)]. This is equivalent to the following code:\n",
    "\n",
    "cubes = []\n",
    "for i in range(10):\n",
    "    cubes.append(i**3)\n",
    "    \n",
    "Before getting starte, you need the read cvs file named 'sales.cs' located in data/sales/\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       eggs  salt  spam\n",
      "month                  \n",
      "Jan      47  12.0    17\n",
      "Feb     110  50.0    31\n",
      "Mar     221  89.0    72\n",
      "Apr      77  87.0    20\n",
      "May     132   NaN    52\n"
     ]
    }
   ],
   "source": [
    "sales = pd.read_csv('data/sales/sales.csv', index_col = 'month')\n",
    "print(sales.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1-Create a list new_idx with the same elements as in sales.index, but with all characters capitalized.\n",
    "\n",
    "2-Assign new_idx to sales.index.\n",
    "\n",
    "3-Print the sales dataframe. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     eggs  salt  spam\n",
      "JAN    47  12.0    17\n",
      "FEB   110  50.0    31\n",
      "MAR   221  89.0    72\n",
      "APR    77  87.0    20\n",
      "MAY   132   NaN    52\n"
     ]
    }
   ],
   "source": [
    "new_idx = [idx.upper() for idx in sales.index] \n",
    "sales.index = new_idx\n",
    "print(sales.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1-Assign the string 'MONTHS' to sales.index.name to create a name for the index.\n",
    "\n",
    "2-Print the sales dataframe to see the index name you just created.\n",
    "\n",
    "3-Now assign the string 'PRODUCTS' to sales.columns.name to give a name to the set of columns.\n",
    "\n",
    "4-Print the sales dataframe again to see the columns name you just created."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PRODUCTS  eggs  salt  spam\n",
      "MONTHS                    \n",
      "JAN         47  12.0    17\n",
      "FEB        110  50.0    31\n",
      "MAR        221  89.0    72\n",
      "APR         77  87.0    20\n",
      "MAY        132   NaN    52\n",
      "JUN        205  60.0    55\n"
     ]
    }
   ],
   "source": [
    "sales.index.name = 'MONTHS'\n",
    "sales.columns.name = 'PRODUCTS'\n",
    "print(sales)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1-Generate a list months with the data `['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']`. This has been done for you.\n",
    "\n",
    "2-Assign months to sales.index.\n",
    "\n",
    "3-Print the modified sales dataframe and verify that you now have month information in the index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PRODUCTS  eggs  salt  spam\n",
      "Jan         47  12.0    17\n",
      "Feb        110  50.0    31\n",
      "Mar        221  89.0    72\n",
      "Apr         77  87.0    20\n",
      "May        132   NaN    52\n",
      "Jun        205  60.0    55\n"
     ]
    }
   ],
   "source": [
    "months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']\n",
    "sales.index = months\n",
    "print(sales)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    " Setting & sorting a MultiIndex:\n",
    "1-Reset index for sales dataframe\n",
    "\n",
    "2-pritn and see the new dataframe\n",
    "\n",
    "3-Create column followed that we can multiindex with this column\n",
    "sales['month'] = [1,2,1,2,1,2]\n",
    "sales['state'] = ['NA', 'NA', 'CA','CA','TX','TX']\n",
    "\n",
    "1-Create a MultiIndex by setting the index to be the columns ['state', 'month'].\n",
    "\n",
    "2-Sort the MultiIndex using the .sort_index() method.\n",
    "\n",
    "3-Print the sales DataFrame. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PRODUCTS  eggs  salt  spam\n",
      "Jan         47  12.0    17\n",
      "Feb        110  50.0    31\n",
      "Mar        221  89.0    72\n",
      "Apr         77  87.0    20\n",
      "May        132   NaN    52\n",
      "Jun        205  60.0    55\n"
     ]
    }
   ],
   "source": [
    "print(sales)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>PRODUCTS</th>\n",
       "      <th>index</th>\n",
       "      <th>eggs</th>\n",
       "      <th>salt</th>\n",
       "      <th>spam</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Jan</td>\n",
       "      <td>47</td>\n",
       "      <td>12.0</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Feb</td>\n",
       "      <td>110</td>\n",
       "      <td>50.0</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Mar</td>\n",
       "      <td>221</td>\n",
       "      <td>89.0</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Apr</td>\n",
       "      <td>77</td>\n",
       "      <td>87.0</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>May</td>\n",
       "      <td>132</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Jun</td>\n",
       "      <td>205</td>\n",
       "      <td>60.0</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "PRODUCTS index  eggs  salt  spam\n",
       "0          Jan    47  12.0    17\n",
       "1          Feb   110  50.0    31\n",
       "2          Mar   221  89.0    72\n",
       "3          Apr    77  87.0    20\n",
       "4          May   132   NaN    52\n",
       "5          Jun   205  60.0    55"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sales.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PRODUCTS  eggs  salt  spam  month state\n",
      "Jan         47  12.0    17      1    NA\n",
      "Feb        110  50.0    31      2    NA\n",
      "Mar        221  89.0    72      1    CA\n",
      "Apr         77  87.0    20      2    CA\n",
      "May        132   NaN    52      1    TX\n",
      "Jun        205  60.0    55      2    TX\n"
     ]
    }
   ],
   "source": [
    "sales['month'] = [1,2,1,2,1,2]\n",
    "sales['state'] = ['NA', 'NA', 'CA','CA','TX','TX']\n",
    "print(sales)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PRODUCTS     eggs  salt  spam\n",
      "state month                  \n",
      "CA    1       221  89.0    72\n",
      "      2        77  87.0    20\n",
      "NA    1        47  12.0    17\n",
      "      2       110  50.0    31\n",
      "TX    1       132   NaN    52\n",
      "      2       205  60.0    55\n"
     ]
    }
   ],
   "source": [
    "sales = sales.set_index(['state','month'])\n",
    "sales = sales.sort_index()\n",
    "print(sales)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1-Print sales.loc[['CA', 'TX']]. Note how New York is excluded.\n",
    "\n",
    "2-Print sales['CA':'TX']. Note how New York is included."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PRODUCTS     eggs  salt  spam\n",
      "state month                  \n",
      "CA    1       221  89.0    72\n",
      "      2        77  87.0    20\n",
      "TX    1       132   NaN    52\n",
      "      2       205  60.0    55\n"
     ]
    }
   ],
   "source": [
    "print(sales.loc[['CA','TX']])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PRODUCTS     eggs  salt  spam\n",
      "state month                  \n",
      "CA    1       221  89.0    72\n",
      "      2        77  87.0    20\n",
      "NA    1        47  12.0    17\n",
      "      2       110  50.0    31\n",
      "TX    1       132   NaN    52\n",
      "      2       205  60.0    55\n"
     ]
    }
   ],
   "source": [
    "print(sales.loc['CA':'TX'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Indexing multiple levels of a MultiIndex\n",
    "Looking up indexed data is fast and efficient. And you have already seen that lookups based on the outermost level of a MultiIndex work just like lookups on DataFrames that have a single-level Index.\n",
    "\n",
    "Looking up data based on inner levels of a MultiIndex can be a bit trickier. In this exercise, you will use your sales DataFrame to do some increasingly complex lookups.\n",
    "\n",
    "The trickiest of all these lookups are when you want to access some inner levels of the index. In this case, you need to use slice(None) in the slicing parameter for the outermost dimension(s) instead of the usual :, or use pd.IndexSlice\n",
    "\n",
    "    1-Look up data for the New York column ('NA') in month 1.\n",
    "\n",
    "    2-Look up data for the California and Texas columns ('CA', 'TX') in month 2.\n",
    "    \n",
    "    3-Look up data for all states in month 2. Use (slice(None), 2) to extract all rows in month 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PRODUCTS     eggs  salt  spam\n",
      "state month                  \n",
      "NA    1        47  12.0    17\n"
     ]
    }
   ],
   "source": [
    "NY_month1 = sales.loc[(['NA'], 1),:]  # for the multi index \n",
    "print(NY_month1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PRODUCTS     eggs  salt  spam\n",
      "state month                  \n",
      "CA    2        77  87.0    20\n",
      "TX    2       205  60.0    55\n"
     ]
    }
   ],
   "source": [
    "CA_TX_month2 = sales.loc[(['CA','TX'], 2), :]\n",
    "print(CA_TX_month2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PRODUCTS     eggs  salt  spam\n",
      "state month                  \n",
      "CA    2        77  87.0    20\n",
      "NA    2       110  50.0    31\n",
      "TX    2       205  60.0    55\n"
     ]
    }
   ],
   "source": [
    "all_month2 = sales.loc[(slice(None),2),:]\n",
    "print(all_month2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# Pivot Table\n",
    "\n",
    "pandas.pivot_table(`data`, `values=` 'column to aggregate', , `index=`' the column as want to use as index', `columns=`'take the cloumns value and turn them a column one by one', `aggfunc=`'mean', `fill_value=`Value to replace missing values with, margins=False :Add all row / columns , dropna=True : Do not include columns whose entries are all NaN, margins_name= Name of the row / column that will contain the totals when margins is True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's Create a data frame for the different treatment are response to the this treastment based on the gender. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  gender  id  response treatment\n",
      "0      F   1         5         A\n",
      "1      M   2         3         A\n",
      "2      F   3         8         B\n",
      "3      M   4         7         B\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame({'id' : [1, 2, 3, 4],\n",
    "                   'treatment' : ['A', 'A', 'B', 'B'],\n",
    "                   'gender' : ['F', 'M', 'F', 'M'],\n",
    "                   'response' : [5, 3, 8, 7]})\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So, If we want to see the How different  treatment  affect the cender. So we can pivot the table with pivot. to do this use the treatment as index, fot column use the Gender asd for the values use the response."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "gender     F  M\n",
      "treatment      \n",
      "A          5  3\n",
      "B          8  7\n"
     ]
    }
   ],
   "source": [
    "df_pivot = df.pivot(index = 'treatment', columns = 'gender' , values = 'response' )\n",
    "print(df_pivot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets check out different example and use also the aggfunc method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "     A    B      C  D\n",
      "0  foo  one  small  1\n",
      "1  foo  one  large  2\n",
      "2  foo  one  large  2\n",
      "3  foo  two  small  3\n",
      "4  foo  two  small  3\n",
      "5  bar  one  large  4\n",
      "6  bar  one  small  5\n",
      "7  bar  two  small  6\n",
      "8  bar  two  large  7\n"
     ]
    }
   ],
   "source": [
    "df2 = pd.DataFrame({\"A\": [\"foo\", \"foo\", \"foo\", \"foo\", \"foo\",\n",
    "                          \"bar\", \"bar\", \"bar\", \"bar\"],\n",
    "                    \"B\": [\"one\", \"one\", \"one\", \"two\", \"two\",\n",
    "                          \"one\", \"one\", \"two\", \"two\"],\n",
    "                   \"C\": [\"small\", \"large\", \"large\", \"small\",\n",
    "                          \"small\", \"large\", \"small\", \"small\",\n",
    "                          \"large\"],\n",
    "                   \"D\": [1, 2, 2, 3, 3, 4, 5, 6, 7]})\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "C        large  small\n",
      "A   B                \n",
      "bar one    4.0    5.0\n",
      "    two    7.0    6.0\n",
      "foo one    4.0    1.0\n",
      "    two    NaN    6.0\n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "df2_pivot = df2.pivot_table( values='D', index=['A', 'B'], columns=['C'], aggfunc=np.sum)\n",
    "print(df2_pivot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can load the 'users.csv' file in data file which contain the variable you want to use to index the rows ('weekday'), which variable you want to use to index the columns ('city'), and which variable will populate the values in the cells ('visitors')."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "      weekday    city  visitors  signups\n",
      "index                                   \n",
      "0         Sun  Austin       139        7\n",
      "1         Sun  Dallas       237       12\n",
      "2         Mon  Austin       326        3\n",
      "3         Mon  Dallas       456        5\n"
     ]
    }
   ],
   "source": [
    "users = pd.read_csv('data/users.csv', index_col ='index')\n",
    "print(users)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    1-Pivot the users DataFrame with the rows indexed by 'weekday', the columns indexed by 'city', and the values populated with 'visitors'.\n",
    "\n",
    "    2-Print the pivoted DataFrame. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "city     Austin  Dallas\n",
      "weekday                \n",
      "Mon         326     456\n",
      "Sun         139     237\n"
     ]
    }
   ],
   "source": [
    "users_pivot = users.pivot(index = 'weekday', columns = 'city', values = 'visitors') \n",
    "# so it helps how maay visitors visited for each city  based on the weekday.\n",
    "print(users_pivot)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    1-Pivot the users DataFrame with the 'signups' indexed by 'weekday' in the rows and 'city' in the columns.\n",
    "\n",
    "    2-Print the new DataFrame. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "city     Austin  Dallas\n",
      "weekday                \n",
      "Mon           3       5\n",
      "Sun           7      12\n"
     ]
    }
   ],
   "source": [
    "signups_pivot = users.pivot(index= 'weekday', columns= 'city', values = 'signups')\n",
    "print(signups_pivot)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you do not select any particular variables, all of them will be pivoted. In this case - with the users DataFrame - both 'visitors' and 'signups' will be pivoted, creating hierarchical column labels.\n",
    "\n",
    "    1-Pivot the users DataFrame with both 'signups' and 'visitors' pivoted - that is, all the variables. This will happen automatically if you do not specify an argument for the values parameter of .pivot().\n",
    "\n",
    "    2-Print the pivoted DataFrame.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        visitors        signups       \n",
      "city      Austin Dallas  Austin Dallas\n",
      "weekday                               \n",
      "Mon          326    456       3      5\n",
      "Sun          139    237       7     12\n"
     ]
    }
   ],
   "source": [
    "pivot_all = users.pivot(index = 'weekday', columns = 'city')\n",
    "print(pivot_all)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "DataFrame.stack : Pivot a level of the column labels (inverse operation from unstack)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "one  a    1.0\n",
      "     b    2.0\n",
      "two  a    3.0\n",
      "     b    4.0\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "# Lets create multuple intex \n",
    "import numpy as np\n",
    "index = pd.MultiIndex.from_tuples([('one', 'a'), ('one', 'b'),('two', 'a'), ('two', 'b')])\n",
    "s = pd.Series(np.arange(1.0,5.0), index=index)\n",
    "print(s)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       a    b\n",
      "one  1.0  2.0\n",
      "two  3.0  4.0\n"
     ]
    }
   ],
   "source": [
    "print(s.unstack(level=-1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   one  two\n",
      "a  1.0  3.0\n",
      "b  2.0  4.0\n"
     ]
    }
   ],
   "source": [
    "print(s.unstack(level=0))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Melting Data Frames\n",
    "A pivoted DataFrame to its original form, or to change it from a wide shape to a long shape. You can explicitly specify the columns that should remain in the reshaped DataFrame with id_vars, and list which columns to convert into values with value_vars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A  B  C\n",
      "0  a  1  2\n",
      "1  b  3  4\n",
      "2  c  5  6\n"
     ]
    }
   ],
   "source": [
    "df = pd.DataFrame({'A': {0: 'a', 1: 'b', 2: 'c'},\n",
    "                   'B': {0: 1, 1: 3, 2: 5},\n",
    "                   'C': {0: 2, 1: 4, 2: 6}})\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A variable  value\n",
      "0  a        B      1\n",
      "1  b        B      3\n",
      "2  c        B      5\n"
     ]
    }
   ],
   "source": [
    "#Lets melt column B , keep the column with id_vars and melt the column with value_vars\n",
    "df_B = df.melt(id_vars='A', value_vars='B')\n",
    "print(df_B)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A variable  value\n",
      "0  a        B      1\n",
      "1  b        B      3\n",
      "2  c        B      5\n",
      "3  a        C      2\n",
      "4  b        C      4\n",
      "5  c        C      6\n"
     ]
    }
   ],
   "source": [
    "#Lets melt B and C so you need the pass alist object to the value variable\n",
    "df_BC = df.melt(id_vars='A', value_vars=['B', 'C'])\n",
    "print(df_BC)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   A My var Name  MY value Name\n",
      "0  a           B              1\n",
      "1  b           B              3\n",
      "2  c           B              5\n",
      "3  a           C              2\n",
      "4  b           C              4\n",
      "5  c           C              6\n"
     ]
    }
   ],
   "source": [
    " # The names of ‘variable’ and ‘value’ columns can be customized with  var_name & value_name\n",
    "df_BC_name = df.melt(id_vars='A', value_vars=['B','C'], var_name='My var Name', value_name='MY value Name')\n",
    "print(df_BC_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}