Manipulating Dataframe with Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"List all column names of a dataframe\"\"\"\n",
    "list(dataframe_name)\n",
    "\n",
    "###################### EXTRACTING AND TRANSFORMING DATA ##########################\n",
    "\"\"\"Notice how data was extracted by mentioning the index column and then how a subset of columns are made into a \n",
    "separate dataframe\"\"\"\n",
    "# Import pandas\n",
    "import pandas as pd\n",
    "# Read in filename and set the index: election\n",
    "election = pd.read_csv(filename, index_col='county')\n",
    "# Create a separate dataframe with the columns ['winner', 'total', 'voters']: results\n",
    "results = election[['winner','total','voters']]\n",
    "# Print the output of results.head()\n",
    "print(results.head())\n",
    "\n",
    "\n",
    "\"\"\"Selecting a slice and printing in reverse order | Notice how -1 is used while slicing to indicate reverse order\"\"\"\n",
    "# Slice the row labels 'Perry' to 'Potter': p_counties. .loc means rows are selected first\n",
    "p_counties = election.loc['Perry':'Potter']\n",
    "# Print the p_counties DataFrame\n",
    "print(p_counties)\n",
    "# Slice the row labels 'Potter' to 'Perry' in reverse order: p_counties_rev\n",
    "p_counties_rev = election.loc['Potter':'Perry':-1]\n",
    "# Print the p_counties_rev DataFrame\n",
    "print(p_counties_rev)\n",
    "\n",
    "\n",
    "\"\"\"Slicing columns using .loc\"\"\"\n",
    "# Slice the columns from the starting column to 'Obama': left_columns\n",
    "left_columns = election.loc[:,:'Obama']`\n",
    "# Print the output of left_columns.head()\n",
    "print(left_columns.head())\n",
    "# Slice the columns from 'Obama' to 'winner': middle_columns\n",
    "middle_columns = election.loc[:,'Obama':'winner']\n",
    "# Print the output of middle_columns.head()\n",
    "print(middle_columns.head())\n",
    "# Slice the columns from 'Romney' to the end: 'right_columns'\n",
    "right_columns = election.loc[:,'Romney':]\n",
    "# Print the output of right_columns.head()\n",
    "print(right_columns.head())\n",
    "\n",
    "\n",
    "\"\"\"Selecting specific rows and columns with loc\"\"\"\n",
    "# Create the list of row labels: rows\n",
    "rows = ['Philadelphia', 'Centre', 'Fulton']\n",
    "# Create the list of column labels: cols\n",
    "cols = ['winner','Obama','Romney']\n",
    "# Create the new DataFrame: three_counties\n",
    "three_counties = election.loc[rows,cols]\n",
    "# Print the three_counties DataFrame\n",
    "print(three_counties)\n",
    "\n",
    "\n",
    "\"\"\"Creating simple filter and obtaining data\"\"\"\n",
    "# Create the boolean array: high_turnout\n",
    "high_turnout = election['turnout']>70\n",
    "# Filter the election DataFrame with the high_turnout array: high_turnout_df\n",
    "high_turnout_df = election[high_turnout]\n",
    "# Print the high_turnout_results DataFrame\n",
    "print(high_turnout_df)\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"Take action on one column based on values on another one. Notice how winner column is accessed\"\"\"\n",
    "# Import numpy\n",
    "import numpy as np\n",
    "# Create the boolean array: too_close\n",
    "too_close = election['margin']<1\n",
    "# Assign np.nan to the 'winner' column where the results were too close to call\n",
    "election.winner[too_close] = np.nan\n",
    "# Print the output of election.info()\n",
    "print(election.info())\n",
    "\n",
    "\n",
    "\"\"\"Using dropna. Notice the use of thresh keyword.\"\"\"\n",
    "# Select the 'age' and 'cabin' columns: df\n",
    "df = titanic[['age','cabin']]\n",
    "# Print the shape of df\n",
    "print(df.shape)\n",
    "# Drop rows in df with how='any' and print the shape\n",
    "print(df.dropna(how='any').shape)\n",
    "# Drop rows in df with how='all' and print the shape\n",
    "print(df.dropna(how='all').shape)\n",
    "# Drop columns in titanic with less than 1000 non-missing values\n",
    "print(titanic.dropna(thresh=1000, axis='columns').info())\n",
    "\n",
    "\n",
    "\"\"\"Wrote a function. Then used apply to apply it over specific columns. Then renamed the columns\"\"\"\n",
    "# Write a function to convert degrees Fahrenheit to degrees Celsius: to_celsius\n",
    "def to_celsius(F):\n",
    "    return 5/9*(F - 32)\n",
    "# Apply the function over 'Mean TemperatureF' and 'Mean Dew PointF': df_celsius\n",
    "df_celsius = weather[['Mean TemperatureF','Mean Dew PointF']].apply(to_celsius)\n",
    "# Reassign the columns df_celsius\n",
    "df_celsius.columns = ['Mean TemperatureC', 'Mean Dew PointC']\n",
    "# Print the output of df_celsius.head()\n",
    "print(df_celsius.head())\n",
    "\n",
    "\n",
    "\"\"\"Mapping one column using a dictionary to form a new column. Notice how dictionary keys are existing column values and the\n",
    "value is the intended mapped value of the new column\"\"\"\n",
    "# Create the dictionary: red_vs_blue\n",
    "red_vs_blue = {'Obama':'blue','Romney':'red'}\n",
    "# Use the dictionary to map the 'winner' column to the new column: election['color']\n",
    "election['color'] = election['winner'].map(red_vs_blue)\n",
    "# Print the output of election.head()\n",
    "print(election.head())\n",
    "\n",
    "\n",
    "\"\"\"We applied zscore function to a whole columns and then assigned that to a new column\"\"\"\n",
    "# Import zscore from scipy.stats\n",
    "from scipy.stats import zscore\n",
    "# Call zscore with election['turnout'] as input: turnout_zscore\n",
    "turnout_zscore = zscore(election['turnout'])\n",
    "# Print the type of turnout_zscore\n",
    "print(type(turnout_zscore))\n",
    "# Assign turnout_zscore to a new column: election['turnout_zscore']\n",
    "election['turnout_zscore']=turnout_zscore\n",
    "# Print the output of election.head()\n",
    "print(election.head())\n",
    "\n",
    "\n",
    "\n",
    "################################  INDEXES  ##############################\n",
    "\"\"\"Changing all indexes to uppercase using list comprehension\"\"\"\n",
    "# Create the list of new indexes: new_idx\n",
    "new_idx = [x.upper() for x in sales.index]\n",
    "# Assign new_idx to sales.index\n",
    "sales.index = new_idx\n",
    "# Print the sales DataFrame\n",
    "print(sales)\n",
    "\n",
    "\n",
    "\"\"\"Naming index column and all other columns to a common name\"\"\"\n",
    "# Assign the string 'MONTHS' to sales.index.name\n",
    "sales.index.name = 'MONTHS'\n",
    "# Print the sales DataFrame\n",
    "print(sales)\n",
    "# Assign the string 'PRODUCTS' to sales.columns.name \n",
    "sales.columns.name = 'PRODUCTS'\n",
    "# Print the sales dataframe again\n",
    "print(sales)\n",
    "\n",
    "\n",
    "\"\"\"Generating index separately and assigning it\"\"\"\n",
    "# Generate the list of months: months\n",
    "months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun']\n",
    "# Assign months to sales.index\n",
    "sales.index = months\n",
    "# Print the modified sales DataFrame\n",
    "print(sales)\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"Setting a multiindex and then sorting that index\"\"\"\n",
    "# Set the index to be the columns ['state', 'month']: sales\n",
    "sales = sales.set_index(['state','month'])\n",
    "# Sort the MultiIndex: sales\n",
    "sales = sales.sort_index()\n",
    "# Print the sales DataFrame\n",
    "print(sales)\n",
    "\n",
    "\n",
    "\"\"\"Setting an index and then using .loc to extract a particular value's rows\"\"\"\n",
    "# Set the index to the column 'state': sales\n",
    "sales = sales.set_index(['state'])\n",
    "# Print the sales DataFrame\n",
    "print(sales)\n",
    "# Access the data from 'NY'\n",
    "print(sales.loc['NY'])\n",
    "\n",
    "\n",
    "\"\"\"Multilevel indexing. Notice how we are extracting specific data using slice n all\"\"\"\n",
    "# Look up data for NY in month 1: NY_month1\n",
    "NY_month1 = sales.loc[('NY',1)]\n",
    "# Look up data for CA and TX in month 2: CA_TX_month2\n",
    "CA_TX_month2 = sales.loc[(['CA','TX'],2),:]\n",
    "# Look up data for all states in month 2: all_month2\n",
    "all_month2 = sales.loc[(slice(None),2),:]\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"Pivot table users so that the focus is on 'visitors', \n",
    "with the columns indexed by 'city' and the rows indexed by 'weekday'\"\"\"\n",
    "# Pivot the users DataFrame: visitors_pivot\n",
    "visitors_pivot = users.pivot(index='weekday', columns='city',values='visitors')\n",
    "# Print the pivoted DataFrame\n",
    "print(visitors_pivot)\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"pivoting users table with multiple values.\"\"\"\n",
    "# Pivot users with signups indexed by weekday and city: signups_pivot\n",
    "signups_pivot = users.pivot(index='weekday',columns='city',values='signups')\n",
    "# Print signups_pivot\n",
    "print(signups_pivot)\n",
    "# Pivot users pivoted by both signups and visitors: pivot\n",
    "pivot = users.pivot(index='weekday',columns='city')\n",
    "# Print the pivoted DataFrame\n",
    "print(pivot)\n",
    "\n",
    "\n",
    "\n",
    "######################## STACKING AND UNSTACKING ########################\n",
    "\"\"\"Below stacking and unstacking is done. Notice how columns change when stacked and unstacked\"\"\"\n",
    "# Unstack users by 'weekday': byweekday\n",
    "byweekday = users.unstack('weekday')\n",
    "# Print the byweekday DataFrame\n",
    "print(byweekday)\n",
    "# Stack byweekday by 'weekday' and print it\n",
    "print(byweekday.stack('weekday'))\n",
    "\n",
    "###\"\"\"RESULT\"\"\"\n",
    "\"\"\"\n",
    "        visitors      signups    \n",
    "weekday      Mon  Sun     Mon Sun\n",
    "city                             \n",
    "Austin       326  139       3   7\n",
    "Dallas       456  237       5  12\n",
    "                visitors  signups\n",
    "city   weekday                   \n",
    "Austin Mon           326        3\n",
    "       Sun           139        7\n",
    "Dallas Mon           456        5\n",
    "       Sun           237       12\n",
    "    \n",
    "\"\"\"   \n",
    "\n",
    "\n",
    "\"\"\"Another sample of stack and unstack\"\"\"\n",
    "# Unstack users by 'city': bycity\n",
    "bycity = users.unstack(level='city')\n",
    "# Print the bycity DataFrame\n",
    "print(bycity)\n",
    "# Stack bycity by 'city' and print it\n",
    "print(bycity.stack(level='city'))\n",
    "\n",
    "\"\"\"RESULTS\n",
    "            visitors        signups       \n",
    "    city      Austin Dallas  Austin Dallas\n",
    "    weekday                               \n",
    "    Mon          326    456       3      5\n",
    "    Sun          139    237       7     12\n",
    "                    visitors  signups\n",
    "    weekday city                     \n",
    "    Mon     Austin       326        3\n",
    "            Dallas       456        5\n",
    "    Sun     Austin       139        7\n",
    "            Dallas       237       12\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "\"\"\"Here we are swapping indexes and sorting them\"\"\"\n",
    "# Stack 'city' back into the index of bycity: newusers\n",
    "newusers = bycity.stack('city')\n",
    "# Swap the levels of the index of newusers: newusers\n",
    "newusers = newusers.swaplevel(0,1)\n",
    "# Print newusers and verify that the index is not sorted\n",
    "print(newusers)\n",
    "# Sort the index of newusers: newusers\n",
    "newusers = newusers.sort_index()\n",
    "# Print newusers and verify that the index is now sorted\n",
    "print(newusers)\n",
    "# Verify that the new DataFrame is equal to the original\n",
    "print(newusers.equals(users))\n",
    "\n",
    "\n",
    "\"\"\"Melting dataframes.The goal of melting is to restore a pivoted DataFrame to its original form, \n",
    "or to change it from a wide shape to a long shape. You can explicitly specify the \n",
    "columns that should remain in the reshaped DataFrame with id_vars, and \n",
    "list which columns to convert into values with value_vars.If you don't pass a name to the values in pd.melt(), \n",
    "you will lose the name of your variable. You can fix this by using the value_name keyword argument.\n",
    "Notice that 'weekday' was a column and 'city' was the pivoted column. If we do not mention id_vars, then the\n",
    "rest all are clubbed together.\n",
    "\"\"\"\n",
    "# Reset the index: visitors_by_city_weekday\n",
    "visitors_by_city_weekday = visitors_by_city_weekday.reset_index()\n",
    "# Print visitors_by_city_weekday\n",
    "print(visitors_by_city_weekday)\n",
    "# Melt visitors_by_city_weekday: visitors\n",
    "visitors = pd.melt(visitors_by_city_weekday, id_vars='weekday', value_name='visitors')\n",
    "# Print visitors\n",
    "print(visitors)\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"Notice here how we kept 2 columns and melted the rest\"\"\"\n",
    "# Melt users: skinny\n",
    "skinny = pd.melt(users, id_vars=['weekday','city'])\n",
    "# Print skinny\n",
    "print(skinny)\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"In this one not clear what is done but I see that 2 other index columns that were set as multiindex has\n",
    "disappeared. Last 2 column names have become values in 1 column and their corresponding values the values in the \n",
    "separate column. Allegedly doing key-value pair.Notice how indexing from 0 of column is used.\"\"\"\n",
    "# Set the new index: users_idx\n",
    "users_idx = users.set_index(['city','weekday'])\n",
    "# Print the users_idx DataFrame\n",
    "print(users_idx)\n",
    "# Obtain the key-value pairs: kv_pairs\n",
    "kv_pairs = pd.melt(users_idx,col_level=0)\n",
    "# Print the key-value pairs\n",
    "print(kv_pairs)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"below we are using pivot_table a.k.a. expressing data as a function of 2 variables\"\"\"\n",
    "# Create the DataFrame with the appropriate pivot table: by_city_day\n",
    "by_city_day = users.pivot_table(index='weekday',columns='city')\n",
    "# Print by_city_day\n",
    "print(by_city_day)\n",
    "\n",
    "\n",
    "\"\"\"Here we are using different aggfunc. No sense to me\"\"\"\n",
    "# Use a pivot table to display the count of each column: count_by_weekday1\n",
    "count_by_weekday1 = users.pivot_table(index='weekday',aggfunc='count')\n",
    "# Print count_by_weekday\n",
    "print(count_by_weekday1)\n",
    "# Replace 'aggfunc='count'' with 'aggfunc=len': count_by_weekday2\n",
    "count_by_weekday2 = users.pivot_table(index='weekday',aggfunc='count')\n",
    "# Verify that the same result is obtained\n",
    "print('==========================================')\n",
    "print(count_by_weekday1.equals(count_by_weekday2))\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"Here we are also calculating the sum or total. Note the column of city disappear for god knows why.\n",
    "Notice that an All columns appears summing things up at the bottom\"\"\"\n",
    "# Create the DataFrame with the appropriate pivot table: signups_and_visitors\n",
    "signups_and_visitors = users.pivot_table(index='weekday',aggfunc=sum)\n",
    "# Print signups_and_visitors\n",
    "print(signups_and_visitors)\n",
    "# Add in the margins: signups_and_visitors_total \n",
    "signups_and_visitors_total = users.pivot_table(index='weekday',aggfunc=sum, margins=True)\n",
    "# Print signups_and_visitors_total\n",
    "print(signups_and_visitors_total)\n",
    "\n",
    "\n",
    "\n",
    "############################ AGREEGATE AND GROUPBY ##############################\n",
    "\"\"\"Below we do groupby and then see what are the values of particular columns when grouped by\"\"\"\n",
    "# Group titanic by 'pclass'\n",
    "by_class = titanic.groupby('pclass')\n",
    "# Aggregate 'survived' column of by_class by count\n",
    "count_by_class = by_class['survived'].count()\n",
    "# Print count_by_class\n",
    "print(count_by_class)\n",
    "# Group titanic by 'embarked' and 'pclass'\n",
    "by_mult = titanic.groupby(['embarked','pclass'])\n",
    "# Aggregate 'survived' column of by_mult by count\n",
    "count_mult = by_mult['survived'].count()\n",
    "# Print count_mult\n",
    "print(count_mult)\n",
    "\n",
    "\n",
    "\"\"\"Here we are grouping by different dataframes since they share the same index. Notice how we made the index common.\n",
    "Then we group by from different tables.\"\"\"\n",
    "# Read life_fname into a DataFrame: life\n",
    "life = pd.read_csv(life_fname, index_col='Country')\n",
    "# Read regions_fname into a DataFrame: regions\n",
    "regions = pd.read_csv(regions_fname, index_col='Country')\n",
    "# Group life by regions['region']: life_by_region\n",
    "life_by_region = life.groupby(regions['region'])\n",
    "# Print the mean over the '2010' column of life_by_region\n",
    "print(life_by_region['2010'].mean())\n",
    "\n",
    "\n",
    "\"\"\"Below we use groupby to select various columns and many aggreegate functions together.\n",
    "Notice how aggregated looks like. When we select column hierarchy of age and max.....it selects age and then\n",
    "max of age is selected. Similar for fare and median. Madian and Max were 2 aggreegate functions calculated together\"\"\"\n",
    "# Group titanic by 'pclass': by_class\n",
    "by_class = titanic.groupby('pclass')\n",
    "# Select 'age' and 'fare'\n",
    "by_class_sub = by_class[['age','fare']]\n",
    "# Aggregate by_class_sub by 'max' and 'median': aggregated\n",
    "aggregated = by_class_sub.agg(['max','median'])\n",
    "# Print the maximum age in each class\n",
    "print(aggregated.loc[:, ('age','max')])\n",
    "# Print the median fare in each class\n",
    "print(aggregated.loc[:,('fare','median')])\n",
    "\n",
    "\n",
    "####OUTPUT\n",
    "\"\"\"\n",
    "aggregated:\n",
    "         age             fare         \n",
    "         max median       max   median\n",
    "pclass                                \n",
    "1       80.0   39.0  512.3292  60.0000\n",
    "2       70.0   29.0   73.5000  15.0458\n",
    "3       74.0   24.0   69.5500   8.0500\n",
    "\n",
    "pclass\n",
    "1    80.0\n",
    "2    70.0\n",
    "3    74.0\n",
    "Name: (age, max), dtype: float64\n",
    "pclass\n",
    "1    60.0000\n",
    "2    15.0458\n",
    "3     8.0500\n",
    "Name: (fare, median), dtype: float64\n",
    "\n",
    "<script.py> output:\n",
    "    pclass\n",
    "    1    80.0\n",
    "    2    70.0\n",
    "    3    74.0\n",
    "    Name: (age, max), dtype: float64\n",
    "    pclass\n",
    "    1    60.0000\n",
    "    2    15.0458\n",
    "    3     8.0500\n",
    "    Name: (fare, median), dtype: float64\n",
    "\"\"\"\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"Below we not only set index to 3 columns together but we also use dictionary to agreegate differnt columns with \n",
    "different aggregate functions.Notice we also defined a custom aggregate function\"\"\"\n",
    "# Read the CSV file into a DataFrame and sort the index: gapminder\n",
    "gapminder = pd.read_csv('gapminder.csv',index_col=['Year','region','Country']).sort_index()\n",
    "# Group gapminder by 'Year' and 'region': by_year_region\n",
    "by_year_region = gapminder.groupby(['Year','region'])\n",
    "# Define the function to compute spread: spread\n",
    "def spread(series):\n",
    "    return series.max() - series.min()\n",
    "# Create the dictionary: aggregator\n",
    "aggregator = {'population':'sum', 'child_mortality':'mean', 'gdp':spread}\n",
    "# Aggregate by_year_region using the dictionary: aggregated\n",
    "aggregated = by_year_region.agg(aggregator)\n",
    "# Print the last 6 entries of aggregated \n",
    "print(aggregated.tail(6))\n",
    "\n",
    "\n",
    "\"\"\"below we are grouping on a function of the index. See how we used date transformation\"\"\"\n",
    "# Read file: sales\n",
    "sales = pd.read_csv('sales.csv', index_col='Date', parse_dates=True)\n",
    "# Create a groupby object: by_day\n",
    "by_day = sales.groupby(sales.index.strftime('%a'))\n",
    "# Create sum: units_sum\n",
    "units_sum = by_day['Units'].sum()\n",
    "# Print units_sum\n",
    "print(units_sum)\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"Here we are doing transform function. Below we are grouping by region and then applying the transform on 2 columns.\n",
    "Notice how we mention the columns. Then we do a boolean filtering of the outliers. Notice how we use loc to filetr them\"\"\"\n",
    "# Import zscore\n",
    "from scipy.stats import zscore\n",
    "# Group gapminder_2010: standardized\n",
    "standardized = gapminder_2010.groupby('region')['life','fertility'].transform(zscore)\n",
    "# Construct a Boolean Series to identify outliers: outliers\n",
    "outliers = (standardized['life'] < -3) | (standardized['fertility'] > 3)\n",
    "# Filter gapminder_2010 by the outliers: gm_outliers\n",
    "gm_outliers = gapminder_2010.loc[outliers]\n",
    "# Print gm_outliers\n",
    "print(gm_outliers)\n",
    "\n",
    "\n",
    "\"\"\"Below we are using transform to impute median.\"\"\"\n",
    "# Create a groupby object: by_sex_class\n",
    "by_sex_class = titanic.groupby(['sex','pclass'])\n",
    "# Write a function that imputes median\n",
    "def impute_median(series):\n",
    "    return series.fillna(series.median())\n",
    "# Impute age and assign to titanic['age']\n",
    "titanic.age = by_sex_class['age'].transform(impute_median)\n",
    "# Print the output of titanic.tail(10)\n",
    "print(titanic.tail(10))\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"Below we have a disparity function. We are applying the disparity function instead of transform in place\"\"\"\n",
    "def disparity(gr):\n",
    "    # Compute the spread of gr['gdp']: s\n",
    "    s = gr['gdp'].max() - gr['gdp'].min()\n",
    "    # Compute the z-score of gr['gdp'] as (gr['gdp']-gr['gdp'].mean())/gr['gdp'].std(): z\n",
    "    z = (gr['gdp'] - gr['gdp'].mean())/gr['gdp'].std()\n",
    "    # Return a DataFrame with the inputs {'z(gdp)':z, 'regional spread(gdp)':s}\n",
    "    return pd.DataFrame({'z(gdp)':z , 'regional spread(gdp)':s})\n",
    "\n",
    "# Group gapminder_2010 by 'region': regional\n",
    "regional = gapminder_2010.groupby('region')\n",
    "# Apply the disparity function on regional: reg_disp\n",
    "reg_disp = regional.apply(disparity)\n",
    "# Print the disparity of 'United States', 'United Kingdom', and 'China'\n",
    "print(reg_disp.loc[['United States','United Kingdom','China'],:])\n",
    "\n",
    "\n",
    "################### GROUPBY WITH FILTERING #########################\n",
    "\"\"\"Below we are defining a function to see how many fraction survived in C class cabins. We are applying that \n",
    "function to the group by. So it is applied separately to male and female groups. Remember that dictionary with\n",
    "key being each group is created? The final result is fraction of females survived and fraction of males survived\"\"\"\n",
    "def c_deck_survival(gr):\n",
    "    c_passengers = gr['cabin'].str.startswith('C').fillna(False)\n",
    "    return gr.loc[c_passengers, 'survived'].mean()\n",
    "\n",
    "\n",
    "# Create a groupby object using titanic over the 'sex' column: by_sex\n",
    "by_sex = titanic.groupby('sex')\n",
    "# Call by_sex.apply with the function c_deck_survival\n",
    "c_surv_by_sex = by_sex.apply(c_deck_survival)\n",
    "# Print the survival rates\n",
    "print(c_surv_by_sex)\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"You can use groupby with the .filter() method to remove whole groups of rows from a DataFrame \n",
    "based on a boolean condition. Here we filter out only companies that had more than 35 units. Notice that\n",
    "the result here yields not one column indicating the sum of each valid company but all the seperate columns\n",
    "of each company without summing\"\"\"\n",
    "# Read the CSV file into a DataFrame: sales\n",
    "sales = pd.read_csv('sales.csv', index_col='Date', parse_dates=True)\n",
    "# Group sales by 'Company': by_company\n",
    "by_company = sales.groupby('Company')\n",
    "# Compute the sum of the 'Units' of by_company: by_com_sum\n",
    "by_com_sum = by_company['Units'].sum()\n",
    "print(by_com_sum)\n",
    "# Filter 'Units' where the sum is > 35: by_com_filt\n",
    "by_com_filt = by_company.filter(lambda g:g['Units'].sum()>35)\n",
    "print(by_com_filt)\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"very informative below. Notice how we created a mapping function based on a boolean condition. Then we\n",
    "are using that function to group by to get children under 10 who survived and also over 10. Next we group by function \n",
    "as well as a column to get survival of children under 10 by class.\"\"\"\n",
    "# Create the Boolean Series: under10\n",
    "under10 = (titanic['age']<10).map({True:'under 10',False:'over 10'})\n",
    "# Group by under10 and compute the survival rate\n",
    "survived_mean_1 = titanic.groupby(under10)['survived'].mean()\n",
    "print(survived_mean_1)\n",
    "# Group by under10 and pclass and compute the survival rate\n",
    "survived_mean_2 = titanic.groupby([under10,'pclass'])['survived'].mean()\n",
    "print(survived_mean_2)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "###################### CASE STUDY OF OLYMPIC MEDALS #####################\n",
    "\"\"\"Note the usage of value counts. It gives you how many times a value of a column appears \n",
    "and arranges in descending order. Here we got the results of how many medals each country won in olympics\"\"\"\n",
    "# Select the 'NOC' column of medals: country_names\n",
    "country_names = medals['NOC']\n",
    "# Count the number of medals won by each country: medal_counts\n",
    "medal_counts = country_names.value_counts()\n",
    "# Print top 15 countries ranked by medals\n",
    "print(medal_counts.head(15))\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"Below we are separately counting in total how many medals each country  won. G,S and B. Also adding a total row\"\"\"\n",
    "# Construct the pivot table: counted\n",
    "counted = medals.pivot_table(index='NOC', values='Athlete', columns='Medal', aggfunc='count')\n",
    "# Create the new column: counted['totals']\n",
    "counted['totals'] = counted.sum(axis='columns')\n",
    "# Sort counted by the 'totals' column\n",
    "counted = counted.sort_values('totals', ascending=False)\n",
    "# Print the top 15 rows of counted\n",
    "print(counted.head(15))\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"Here we are finding unique values or unique permutated combinations of column values. Notice how\n",
    "drop_duplicates is used for the purpose\"\"\"\n",
    "# Select columns: ev_gen\n",
    "ev_gen = medals[['Event_gender','Gender']]\n",
    "# Drop duplicate pairs: ev_gen_uniques\n",
    "ev_gen_uniques = ev_gen.drop_duplicates()\n",
    "# Print ev_gen_uniques\n",
    "print(ev_gen_uniques)\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"Below we are locating errors with data by grouping and finding how many rows for each group combo is there.\n",
    "We found only 1 row for a particular combination\"\"\"\n",
    "# Group medals by the two columns: medals_by_gender\n",
    "medals_by_gender = medals.groupby(['Event_gender','Gender'])\n",
    "# Create a DataFrame with a group count: medal_count_by_gender\n",
    "medal_count_by_gender = medals_by_gender.count()\n",
    "# Print medal_count_by_gender\n",
    "print(medal_count_by_gender)\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"After the above analysis we are locating the errant row using boolean condition. Note the use of &\"\"\"\n",
    "# Create the Boolean Series: sus\n",
    "sus = (medals.Event_gender == 'W') & (medals.Gender == 'Men')\n",
    "# Create a DataFrame with the suspicious row: suspect\n",
    "suspect = medals[sus] \n",
    "# Print suspect\n",
    "print(suspect)\n",
    "\n",
    "\n",
    "    \n",
    "\"\"\"below we are trying to find which country won medals in most categories. Notice how nunique is used \n",
    "to count number of unique values. Also notice how sort_values is used to sort\"\"\"\n",
    "# Group medals by 'NOC': country_grouped\n",
    "country_grouped = medals.groupby('NOC')\n",
    "# Compute the number of distinct sports in which each country won medals: Nsports\n",
    "Nsports = country_grouped['Sport'].nunique()\n",
    "# Sort the values of Nsports in descending order\n",
    "Nsports = Nsports.sort_values(ascending=False)\n",
    "# Print the top 15 rows of Nsports\n",
    "print(Nsports.head(15))\n",
    "\n",
    "\n",
    "\"\"\"Below we want to compare USA and USSR in between certain years nd categories of sport\n",
    "where medals were won. notice the use of isin there.\"\"\"\n",
    "# Extract all rows for which the 'Edition' is between 1952 & 1988: during_cold_war\n",
    "during_cold_war = (medals['Edition'] >= 1952) & (medals['Edition'] <= 1988)\n",
    "# Extract rows for which 'NOC' is either 'USA' or 'URS': is_usa_urs\n",
    "is_usa_urs = medals.NOC.isin(['USA','URS'])\n",
    "# Use during_cold_war and is_usa_urs to create the DataFrame: cold_war_medals\n",
    "cold_war_medals = medals.loc[during_cold_war & is_usa_urs]\n",
    "# Group cold_war_medals by 'NOC'\n",
    "country_grouped = cold_war_medals.groupby('NOC')\n",
    "# Create Nsports\n",
    "Nsports = country_grouped['Sport'].nunique().sort_values(ascending=False)\n",
    "# Print Nsports\n",
    "print(Nsports)\n",
    "\n",
    "\n",
    "\"\"\"We wanted to know in which year which country won most medals. Notice how idxmax is used. notice we can\n",
    "also use .loc with grouped by index\"\"\"\n",
    "# Create the pivot table: medals_won_by_country\n",
    "medals_won_by_country = medals.pivot_table(index='Edition',columns='NOC',values='Athlete',aggfunc='count')\n",
    "# Slice medals_won_by_country: cold_war_usa_urs_medals\n",
    "cold_war_usa_urs_medals = medals_won_by_country.loc[1952:1988, ['USA',\"URS\"]]\n",
    "# Create most_medals \n",
    "most_medals = cold_war_usa_urs_medals.idxmax(axis='columns')\n",
    "# Print most_medals.value_counts()\n",
    "print(most_medals.value_counts)\n",
    "\n",
    "\n",
    "\n",
    "\"\"\"Plotting medals USA got over the years. Note how unstack is done\"\"\"\n",
    "# Create the DataFrame: usa\n",
    "usa = medals[medals.NOC == 'USA']\n",
    "# Group usa by ['Edition', 'Medal'] and aggregate over 'Athlete'\n",
    "usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()\n",
    "# Reshape usa_medals_by_year by unstacking\n",
    "usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')\n",
    "# Plot the DataFrame usa_medals_by_year\n",
    "usa_medals_by_year.plot()\n",
    "plt.show()\n",
    "\n",
    "\n",
    "\"\"\"Notice how an area plot is done\"\"\"\n",
    "# Create the DataFrame: usa\n",
    "usa = medals[medals.NOC == 'USA']\n",
    "# Group usa by 'Edition', 'Medal', and 'Athlete'\n",
    "usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()\n",
    "# Reshape usa_medals_by_year by unstacking\n",
    "usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')\n",
    "# Create an area plot of usa_medals_by_year\n",
    "usa_medals_by_year.plot.area()\n",
    "plt.show()\n",
    "\n",
    "\n",
    "\"\"\"Here we use Categorical() to rearrange order of categorical data\"\"\"\n",
    "# Redefine 'Medal' as an ordered categorical\n",
    "medals.Medal = pd.Categorical(values=medals.Medal,categories=['Bronze','Silver','Gold'],ordered=True)\n",
    "# Create the DataFrame: usa\n",
    "usa = medals[medals.NOC == 'USA']\n",
    "# Group usa by 'Edition', 'Medal', and 'Athlete'\n",
    "usa_medals_by_year = usa.groupby(['Edition', 'Medal'])['Athlete'].count()\n",
    "# Reshape usa_medals_by_year by unstacking\n",
    "usa_medals_by_year = usa_medals_by_year.unstack(level='Medal')\n",
    "# Create an area plot of usa_medals_by_year\n",
    "usa_medals_by_year.plot.area()\n",
    "plt.show()\n",
    "\n"
   ]
  }
 ],
 "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.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}