WQ_Data_Munging.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import matplotlib\n",
    "import seaborn as sns\n",
    "matplotlib.rcParams['savefig.dpi'] = 144"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Munging"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Relational Data\n",
    "\n",
    "The simplest type of data we have see might consist a single table with a some columns and some rows.  This sort of data is easy to analyze and compute and we generally want to reduce our data to a single table before we start running machine learning algorithms.  Yet, real world data doesn't necessarily fit into this paradigm.  Most real world data is messy and complicated which doesn't fit well into a tabular format and we will have to do some work to reduce this complexity.  Additionally, in many case we can reduce our memory cost by not keeping data in a single table, but instead in a set of data structures with defined relations between them. \n",
    "\n",
    "Here we will explore a bit of data and see how combining different sets of data can help us generate useful features.\n",
    "\n",
    "First we need some data.  We will make use of some data from Wikipedia and we will use the pandas `read_html` function to scrape the data from a particular webpage.  We will study the top 10 companies in the Fortune Global 500 which conveniently have their own Wikipedia page.\n",
    "\n",
    "We will download the data in tabular form, but work with it as a list of dictionaries, this will allow us to get used to working with unstructured data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "df = pd.read_html('https://en.wikipedia.org/wiki/Fortune_Global_500', header=0)[0]\n",
    "fortune_500 = json.loads(df.to_json(orient=\"records\"))\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets look at the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fortune_500"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is a great start to our analysis, however, there really isn't that much information here, we will need to bring in additional data sources to get any further understanding of these companies.\n",
    "\n",
    "The first question we might want to ask is how many employees does it take to get that revenue, in other words, what is the revenue per employee?  Luckily, we can use Wikipedia to get that data as well, we have scraped this data manually (all from Wikipedia) and created the following dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "other_data = [\n",
    "    {\"name\": \"Walmart\",\n",
    "     \"employees\": 2300000,\n",
    "     \"year founded\": 1962\n",
    "    },\n",
    "    {\"name\": \"State Grid Corporation of China\",\n",
    "     \"employees\": 927839,\n",
    "     \"year founded\": 2002},\n",
    "    {\"name\": \"China Petrochemical Corporation\",\n",
    "     \"employees\":358571,\n",
    "     \"year founded\": 1998\n",
    "     },\n",
    "    {\"name\": \"China National Petroleum Corporation\",\n",
    "     \"employees\": 1636532,\n",
    "     \"year founded\": 1988},\n",
    "    {\"name\": \"Toyota Motor Corporation\",\n",
    "     \"employees\": 364445,\n",
    "     \"year founded\": 1937},\n",
    "    {\"name\": \"Volkswagen AG\",\n",
    "     \"employees\": 642292,\n",
    "     \"year founded\": 1937},\n",
    "    {\"name\": \"Royal Dutch Shell\",\n",
    "     \"employees\": 92000,\n",
    "     \"year founded\": 1907},\n",
    "    {\"name\": \"Berkshire Hathaway Inc.\",\n",
    "     \"employees\":377000,\n",
    "     \"year founded\": 1839},\n",
    "    {\"name\": \"Apple Inc.\",\n",
    "     \"employees\": 123000,\n",
    "     \"year founded\": 1976},\n",
    "    {\"name\": \"Exxon Mobile Corporation\",\n",
    "     \"employees\": 69600,\n",
    "     \"year founded\": 1999}\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some data have a slightly different name than in our original set, so we will keep a dictionary of mappings between the two.  Notice, we only include the mapping in the dictionary if there is a difference."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mapping = {\"China Petrochemical Corporation\":\"Sinopec Group\",\n",
    "           \"State Grid Corporation of China\":\"State Grid\",\n",
    "           \"China National Petroleum Corporation\":\"China National Petroleum\",\n",
    "           \"Toyota Motor Corporation\": \"Toyota Motor\",\n",
    "           \"Volkswagen AG\":\"Volkswagen\",\n",
    "           \"Berkshire Hathaway Inc.\":\"Berkshire Hathaway\",\n",
    "           \"Apple Inc.\": \"Apple\",\n",
    "           \"Exxon Mobile Corporation\": \"Exxon Mobil\"\n",
    "          }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This data is one to one, meaning the data contained in one source only aligns with a single element in the other source, thus we should be able to put these together.  However, we know that the data isn't in a great form to be joined at the moment.  This is for two reasons\n",
    "\n",
    "1. All the names will not align (we need to use our mapping)\n",
    "2. The `list` structure is not optimized for looking through elements. \n",
    "\n",
    "While for 10 elements the second reason won't really matter, for larger data sets such performance considerations are extremely important.  We can turn this list of dictionaries into a dictionary of dictionaries, so we can quickly access each element of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_data = {k[\"name\"] : k for k in other_data}\n",
    "dict_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Question:** If we had many entries in `other_data`, we could display a small piece by printing `other_data[:5]`. With dataframes we might use `df.head()`. Can you think of a way to print out a small piece of a dictionary?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can easily compute the revenue per employee, we need to map the \"Company\" value in our original data with the \"name\" column of this other data, but we also need to use the mapping to ensure the columns will line up.  We in general don't want to mutate our original data, so lets make a new list of dictionaries with this new feature (revenue per employee).  On the course of doing this, we will need to handle converting some numbers like `$500 Billion` to a numeric value.  Lets create a function to do this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_revenue(x):\n",
    "    return float(x.lstrip('$').rstrip('billion')) * 1e9\n",
    "\n",
    "assert convert_revenue('$500 billion') == 500e9"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can convert this revenue, but unfortunately the mapping we have written goes the wrong way, lets invert it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "inv_map = {v : k for k,v in mapping.iteritems()}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Question:** What would happen if `mapping` was not one-to-one, i.e. multiple keys had the same value?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we should be able to create a few functions to compute this revenue per employee and create a data list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rev_per_emp(company):\n",
    "    name = company[u'Company']\n",
    "    n_employees = dict_data[inv_map.get(name, name)].get('employees')\n",
    "    company['rev per emp'] = convert_revenue(company[u'Revenue in USD'])/n_employees\n",
    "    return company\n",
    "\n",
    "def compute_copy(d, func):\n",
    "    return func({k:v for k,v in d.iteritems()})\n",
    "\n",
    "data = map(lambda x : compute_copy(x, rev_per_emp), fortune_500)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets take a look at our new data and also the old data to ensure we didn't mutate anything."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fortune_500[:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can sort these values.  We first can select out on the elements we care about and then sort that list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rev_per_emp = sorted([(i[u'Company'], i['rev per emp']) for i in data], \n",
    "                   key=lambda x : x[1],\n",
    "                   reverse=True)\n",
    "rev_per_emp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This results in a much different order.  What does this tell us about the companies?\n",
    "\n",
    "Now lets pull in some other data (this is data science, more data is always better!).  We can see that these companies are in a few different industries, let find out which ones."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import Counter\n",
    "Counter(i[u'Industry'] for i in data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One thing we might want to know is what sort of market share they have of the specific industry to which they belong.  Let's look at the two industries that categorize the 6 of the top 10, `Automobiles` and `Petroleum`.  We can select only those elements of our data to work with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sub_data = [i for i in data if i[u'Industry'] in [u'Automobiles', u'Petroleum']]\n",
    "sub_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It might be the case that the each particular category has a different relevant metric for market share.  For example, we could look at total revenue for a car company or we could look at cars produced.  \n",
    "\n",
    "So for the automobile industry we will look at the percent total of cars produced.  We can get this data again from Wikipedia."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list = pd.read_html(\"https://en.wikipedia.org/wiki/Automotive_industry\", header=0)\n",
    "car_totals = json.loads(df_list[0].to_json(orient=\"records\"))\n",
    "car_by_man = json.loads(df_list[2].to_json(orient='records'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "car_totals[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "car_by_man[:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now lets get only the groups we care about and divide by the total production which we will take as the latest year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_prod = sorted((i[u\"Year\"], i[u'Production']) for i in car_totals)[-1][1]\n",
    "total_prod"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can find the market share for each of the car companies. We will keep track of a market share dictionary.  We will again need to keep track of some slight name differences."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "car_by_man_dict = {i[u'Group']:i[u'Vehicles'] for i in car_by_man}\n",
    "market_share = {}\n",
    "for name, orig_name in zip(['Toyota', 'Volkswagen Group'], ['Toyota', 'Volkswagen']):\n",
    "    market_share[orig_name] = car_by_man_dict[name]/ float(total_prod)\n",
    "    \n",
    "market_share"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can do the same for the Petroleum industry, but in this case, lets compute the market share by revenue.  On Wikipedia, we can find a list of oil companies by revenue.  Although its not a complete list, it has enough companies that we don't expect the companies left off the list to contribute greatly to our analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rev = pd.read_html(\"https://en.wikipedia.org/wiki/List_of_largest_oil_and_gas_companies_by_revenue\", header=1)[0]\n",
    "rev = rev.iloc[:, 1:3]\n",
    "rev.columns = ['Company', 'Revenue']\n",
    "oil_data = json.loads(rev.to_json(orient=\"records\"))\n",
    "oil_data[:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can compute the totals and market share.  Since the data here might be slightly different (perhaps older) than our original data, we will compute the market share of each company within this data set, then pull out the numbers we care about."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total = sum([float(i[u'Revenue'].rstrip('*')) for i in oil_data])\n",
    "shares = {i[u'Company']:float(i[u'Revenue'].rstrip('*'))/total for i in oil_data}\n",
    "print total"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can pull out the companies we care about in the petroleum industry."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "petro_companies = [i[u'Company'] for i in data if i['Industry'] == u'Petroleum']\n",
    "petro_companies"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets check if these are all in the our shares dictionary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[(i, i in shares) for i in petro_companies]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Some of these companies are directly there, and looking through our dictionary, we can see the others are there without exact names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "shares.keys()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So lets make a fuzzy match, this will be a pretty simple one where it will try to match words in a name and take the maximum number of matches."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def fuzzy_match(word, s):\n",
    "    words = set(word.split(' '))\n",
    "    overlaps = [(k, len(v.intersection(words))) for k, v in s.iteritems()]\n",
    "    return max(overlaps, key=lambda x : x[1])[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "split_names = {i: set(i.split(' ')) for i in shares.keys()}\n",
    "for i in petro_companies:\n",
    "    match = fuzzy_match(i, split_names)\n",
    "    print \"matched {} to {}\".format(i, match)\n",
    "    market_share[i] = shares[match]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "market_share"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## By industry\n",
    "We have some nice examples of data munging, now lets see an example of keeping data in a relational fashion.  Lets say we want to add another feature which is the growth of each industry.  If we were to store this data as a single quantity, we would be saving a bunch of extra information, we would be much better off extracting this information and keeping it in a single table so we are not replicating by industry."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## With Pandas\n",
    "\n",
    "Now we can also perform these same computations with Pandas, lets see how this compares."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_html('https://en.wikipedia.org/wiki/Fortune_Global_500', header=0)[0]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['rev'] = df['Revenue in USD'].apply(convert_revenue)\n",
    "df['employees'] = df['Company'].apply(lambda x : dict_data[inv_map.get(x, x)].get('employees'))\n",
    "df['rev_per_employee'] = df['rev'] / df['employees'].astype(float)\n",
    "df.sort_values(by='rev_per_employee', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list = pd.read_html(\"https://en.wikipedia.org/wiki/Automotive_industry\", header=0)\n",
    "df_totals = df_list[0]\n",
    "df_by_man = df_list[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_prod = df_totals.sort_values(by='Year').iloc[-1]['Production']\n",
    "total_prod"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_by_man['share'] = df_by_man['Vehicles'].apply(lambda x : float(x)/ total_prod)\n",
    "market_share = df_by_man.set_index('Group')['share'][['Toyota', 'Volkswagen Group']]\n",
    "market_share"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rev = pd.read_html(\"https://en.wikipedia.org/wiki/List_of_largest_oil_and_gas_companies_by_revenue\", header=1)[0]\n",
    "rev = rev.iloc[:, 1:3]\n",
    "rev.columns = ['Company', 'Revenue']\n",
    "rev"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rev['rev_clean'] = rev['Revenue'].apply(lambda x : float(x.rstrip('*')))\n",
    "total = rev['rev_clean'].sum()\n",
    "total"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rev['share'] = rev['rev_clean'] / total\n",
    "rev"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "rev = rev[rev['Company'].isin(['Exxon Mobil', 'Sinopec', 'China National Petroleum Corporation', 'Royal Dutch Shell'])].copy()\n",
    "rev"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# do fuzzy search\n",
    "split_names = {i: set(i.split(' ')) for i in df['Company']}\n",
    "\n",
    "def fuzzy(word):\n",
    "    return fuzzy_match(word, split_names)\n",
    "\n",
    "rev['name'] = rev['Company'].apply(fuzzy)\n",
    "rev"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ms2 = df.merge(rev[['share', 'name']], left_on='Company', right_on='name')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we want to put these together and get only the company and the market share."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ms = market_share.reset_index()[['Group','share']]\n",
    "ms.columns = ['Company', 'share']\n",
    "pd.concat([ms, ms2[['Company', 'share']]])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "*Copyright &copy; 2018 The Data Incubator.  All rights reserved.*"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "nbclean": true
 },
 "nbformat": 4,
 "nbformat_minor": 0
}