DS_Data_Munging.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "init_cell": true
   },
   "outputs": [],
   "source": [
    "%logstop\n",
    "%logstart -rtq ~/.logs/DS_Data_Munging.py append\n",
    "%matplotlib inline\n",
    "import matplotlib\n",
    "import seaborn as sns\n",
    "sns.set()\n",
    "matplotlib.rcParams['figure.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](https://en.wikipedia.org/w/index.php?title=Fortune_Global_500&oldid=855890446).\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": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rank</th>\n",
       "      <th>Company</th>\n",
       "      <th>Country</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Revenue in USD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Walmart</td>\n",
       "      <td>United States</td>\n",
       "      <td>Retail</td>\n",
       "      <td>$500 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>State Grid</td>\n",
       "      <td>China</td>\n",
       "      <td>Power</td>\n",
       "      <td>$349 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Sinopec Group</td>\n",
       "      <td>China</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$327 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>China National Petroleum</td>\n",
       "      <td>China</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$326 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Royal Dutch Shell</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$312 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Toyota Motor</td>\n",
       "      <td>Japan</td>\n",
       "      <td>Automobiles</td>\n",
       "      <td>$265 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Volkswagen</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Automobiles</td>\n",
       "      <td>$260 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>BP</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$245 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Exxon Mobil</td>\n",
       "      <td>United States</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$244 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Berkshire Hathaway</td>\n",
       "      <td>United States</td>\n",
       "      <td>Products</td>\n",
       "      <td>$242 billion</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rank                   Company         Country     Industry Revenue in USD\n",
       "0     1                   Walmart   United States       Retail   $500 billion\n",
       "1     2                State Grid           China        Power   $349 billion\n",
       "2     3             Sinopec Group           China    Petroleum   $327 billion\n",
       "3     4  China National Petroleum           China    Petroleum   $326 billion\n",
       "4     5         Royal Dutch Shell     Netherlands    Petroleum   $312 billion\n",
       "5     6              Toyota Motor           Japan  Automobiles   $265 billion\n",
       "6     7                Volkswagen         Germany  Automobiles   $260 billion\n",
       "7     8                        BP  United Kingdom    Petroleum   $245 billion\n",
       "8     9               Exxon Mobil   United States    Petroleum   $244 billion\n",
       "9    10        Berkshire Hathaway   United States     Products   $242 billion"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "df = pd.read_html('https://en.wikipedia.org/w/index.php?title=Fortune_Global_500&oldid=855890446', header=0)[0]\n",
    "fortune_500 = json.loads(df.to_json(orient=\"records\"))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "TypeError",
     "evalue": "read_html() got an unexpected keyword argument 'sidebar'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-6-49ea464f52f5>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mdf2\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_html\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'https://www.homeandlearn.org/'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0msidebar\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      2\u001b[0m \u001b[0mdf2\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mTypeError\u001b[0m: read_html() got an unexpected keyword argument 'sidebar'"
     ]
    }
   ],
   "source": [
    "df2=pd.read_html('https://www.homeandlearn.org/',sidebar=0)[0]\n",
    "df2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Lets look at the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Rank': 1,\n",
       "  'Company': 'Walmart',\n",
       "  'Country': 'United States',\n",
       "  'Industry': 'Retail',\n",
       "  'Revenue in USD': '$500 billion'},\n",
       " {'Rank': 2,\n",
       "  'Company': 'State Grid',\n",
       "  'Country': 'China',\n",
       "  'Industry': 'Power',\n",
       "  'Revenue in USD': '$349 billion'},\n",
       " {'Rank': 3,\n",
       "  'Company': 'Sinopec Group',\n",
       "  'Country': 'China',\n",
       "  'Industry': 'Petroleum',\n",
       "  'Revenue in USD': '$327 billion'},\n",
       " {'Rank': 4,\n",
       "  'Company': 'China National Petroleum',\n",
       "  'Country': 'China',\n",
       "  'Industry': 'Petroleum',\n",
       "  'Revenue in USD': '$326 billion'},\n",
       " {'Rank': 5,\n",
       "  'Company': 'Royal Dutch Shell',\n",
       "  'Country': 'Netherlands',\n",
       "  'Industry': 'Petroleum',\n",
       "  'Revenue in USD': '$312 billion'},\n",
       " {'Rank': 6,\n",
       "  'Company': 'Toyota Motor',\n",
       "  'Country': 'Japan',\n",
       "  'Industry': 'Automobiles',\n",
       "  'Revenue in USD': '$265 billion'},\n",
       " {'Rank': 7,\n",
       "  'Company': 'Volkswagen',\n",
       "  'Country': 'Germany',\n",
       "  'Industry': 'Automobiles',\n",
       "  'Revenue in USD': '$260 billion'},\n",
       " {'Rank': 8,\n",
       "  'Company': 'BP',\n",
       "  'Country': 'United Kingdom',\n",
       "  'Industry': 'Petroleum',\n",
       "  'Revenue in USD': '$245 billion'},\n",
       " {'Rank': 9,\n",
       "  'Company': 'Exxon Mobil',\n",
       "  'Country': 'United States',\n",
       "  'Industry': 'Petroleum',\n",
       "  'Revenue in USD': '$244 billion'},\n",
       " {'Rank': 10,\n",
       "  'Company': 'Berkshire Hathaway',\n",
       "  'Country': 'United States',\n",
       "  'Industry': 'Products',\n",
       "  'Revenue in USD': '$242 billion'}]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 5,
   "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",
    "    {\"name\": \"BP plc\",\n",
    "     \"employees\": 74000,\n",
    "     \"year founded\": 1908}\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": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "mapping = {\n",
    "    'Apple': 'Apple Inc.',\n",
    "    'BP': 'BP plc',\n",
    "    'Berkshire Hathaway': 'Berkshire Hathaway Inc.',\n",
    "    'China National Petroleum': 'China National Petroleum Corporation',\n",
    "    'Exxon Mobil': 'Exxon Mobile Corporation',\n",
    "    'Sinopec Group': 'China Petrochemical Corporation',\n",
    "    'State Grid': 'State Grid Corporation of China',\n",
    "    'Toyota Motor': 'Toyota Motor Corporation',\n",
    "    'Volkswagen': 'Volkswagen AG'\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": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'name': 'Walmart', 'employees': 2300000, 'year founded': 1962},\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",
       " {'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', 'employees': 642292, 'year founded': 1937},\n",
       " {'name': 'Royal Dutch Shell', 'employees': 92000, 'year founded': 1907},\n",
       " {'name': 'Berkshire Hathaway Inc.',\n",
       "  'employees': 377000,\n",
       "  'year founded': 1839},\n",
       " {'name': 'Apple Inc.', 'employees': 123000, 'year founded': 1976},\n",
       " {'name': 'Exxon Mobile Corporation',\n",
       "  'employees': 69600,\n",
       "  'year founded': 1999},\n",
       " {'name': 'BP plc', 'employees': 74000, 'year founded': 1908}]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "other_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Walmart': {'name': 'Walmart', 'employees': 2300000, 'year founded': 1962},\n",
       " 'State Grid Corporation of China': {'name': 'State Grid Corporation of China',\n",
       "  'employees': 927839,\n",
       "  'year founded': 2002},\n",
       " 'China Petrochemical Corporation': {'name': 'China Petrochemical Corporation',\n",
       "  'employees': 358571,\n",
       "  'year founded': 1998},\n",
       " 'China National Petroleum Corporation': {'name': 'China National Petroleum Corporation',\n",
       "  'employees': 1636532,\n",
       "  'year founded': 1988},\n",
       " 'Toyota Motor Corporation': {'name': 'Toyota Motor Corporation',\n",
       "  'employees': 364445,\n",
       "  'year founded': 1937},\n",
       " 'Volkswagen AG': {'name': 'Volkswagen AG',\n",
       "  'employees': 642292,\n",
       "  'year founded': 1937},\n",
       " 'Royal Dutch Shell': {'name': 'Royal Dutch Shell',\n",
       "  'employees': 92000,\n",
       "  'year founded': 1907},\n",
       " 'Berkshire Hathaway Inc.': {'name': 'Berkshire Hathaway Inc.',\n",
       "  'employees': 377000,\n",
       "  'year founded': 1839},\n",
       " 'Apple Inc.': {'name': 'Apple Inc.',\n",
       "  'employees': 123000,\n",
       "  'year founded': 1976},\n",
       " 'Exxon Mobile Corporation': {'name': 'Exxon Mobile Corporation',\n",
       "  'employees': 69600,\n",
       "  'year founded': 1999},\n",
       " 'BP plc': {'name': 'BP plc', 'employees': 74000, 'year founded': 1908}}"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_data={company[\"name\"]:  company for company in other_data}\n",
    "dict_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Walmart': {'name': 'Walmart', 'employees': 2300000, 'year founded': 1962},\n",
       " 'State Grid Corporation of China': {'name': 'State Grid Corporation of China',\n",
       "  'employees': 927839,\n",
       "  'year founded': 2002},\n",
       " 'China Petrochemical Corporation': {'name': 'China Petrochemical Corporation',\n",
       "  'employees': 358571,\n",
       "  'year founded': 1998},\n",
       " 'China National Petroleum Corporation': {'name': 'China National Petroleum Corporation',\n",
       "  'employees': 1636532,\n",
       "  'year founded': 1988},\n",
       " 'Toyota Motor Corporation': {'name': 'Toyota Motor Corporation',\n",
       "  'employees': 364445,\n",
       "  'year founded': 1937},\n",
       " 'Volkswagen AG': {'name': 'Volkswagen AG',\n",
       "  'employees': 642292,\n",
       "  'year founded': 1937},\n",
       " 'Royal Dutch Shell': {'name': 'Royal Dutch Shell',\n",
       "  'employees': 92000,\n",
       "  'year founded': 1907},\n",
       " 'Berkshire Hathaway Inc.': {'name': 'Berkshire Hathaway Inc.',\n",
       "  'employees': 377000,\n",
       "  'year founded': 1839},\n",
       " 'Apple Inc.': {'name': 'Apple Inc.',\n",
       "  'employees': 123000,\n",
       "  'year founded': 1976},\n",
       " 'Exxon Mobile Corporation': {'name': 'Exxon Mobile Corporation',\n",
       "  'employees': 69600,\n",
       "  'year founded': 1999},\n",
       " 'BP plc': {'name': 'BP plc', 'employees': 74000, 'year founded': 1908}}"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Walmart', {'name': 'Walmart', 'employees': 2300000, 'year founded': 1962}),\n",
       " ('State Grid Corporation of China',\n",
       "  {'name': 'State Grid Corporation of China',\n",
       "   'employees': 927839,\n",
       "   'year founded': 2002}),\n",
       " ('China Petrochemical Corporation',\n",
       "  {'name': 'China Petrochemical Corporation',\n",
       "   'employees': 358571,\n",
       "   'year founded': 1998}),\n",
       " ('China National Petroleum Corporation',\n",
       "  {'name': 'China National Petroleum Corporation',\n",
       "   'employees': 1636532,\n",
       "   'year founded': 1988}),\n",
       " ('Toyota Motor Corporation',\n",
       "  {'name': 'Toyota Motor Corporation',\n",
       "   'employees': 364445,\n",
       "   'year founded': 1937}),\n",
       " ('Volkswagen AG',\n",
       "  {'name': 'Volkswagen AG', 'employees': 642292, 'year founded': 1937})]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(dict_data.items())[:6]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1962"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "name='Walmart'\n",
    "dict_data[mapping.get(name, name)].get('year founded')"
   ]
  },
  {
   "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": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rev_per_emp(company):\n",
    "    name = company[u'Company']\n",
    "    n_employees = dict_data[mapping.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.items()})\n",
    "\n",
    "data = list(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": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Rank': 1,\n",
       "  'Company': 'Walmart',\n",
       "  'Country': 'United States',\n",
       "  'Industry': 'Retail',\n",
       "  'Revenue in USD': '$500 billion',\n",
       "  'rev per emp': 217391.30434782608},\n",
       " {'Rank': 2,\n",
       "  'Company': 'State Grid',\n",
       "  'Country': 'China',\n",
       "  'Industry': 'Power',\n",
       "  'Revenue in USD': '$349 billion',\n",
       "  'rev per emp': 376142.84374767606}]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Rank': 1,\n",
       "  'Company': 'Walmart',\n",
       "  'Country': 'United States',\n",
       "  'Industry': 'Retail',\n",
       "  'Revenue in USD': '$500 billion'},\n",
       " {'Rank': 2,\n",
       "  'Company': 'State Grid',\n",
       "  'Country': 'China',\n",
       "  'Industry': 'Power',\n",
       "  'Revenue in USD': '$349 billion'}]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Exxon Mobil', 3505747.1264367816),\n",
       " ('Royal Dutch Shell', 3391304.347826087),\n",
       " ('BP', 3310810.810810811),\n",
       " ('Sinopec Group', 911953.2812190612),\n",
       " ('Toyota Motor', 727133.0379069544),\n",
       " ('Berkshire Hathaway', 641909.8143236075),\n",
       " ('Volkswagen', 404800.30889377417),\n",
       " ('State Grid', 376142.84374767606),\n",
       " ('Walmart', 217391.30434782608),\n",
       " ('China National Petroleum', 199201.72657790987)]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Counter({'Retail': 1,\n",
       "         'Power': 1,\n",
       "         'Petroleum': 5,\n",
       "         'Automobiles': 2,\n",
       "         'Products': 1})"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Rank': 3,\n",
       "  'Company': 'Sinopec Group',\n",
       "  'Country': 'China',\n",
       "  'Industry': 'Petroleum',\n",
       "  'Revenue in USD': '$327 billion',\n",
       "  'rev per emp': 911953.2812190612},\n",
       " {'Rank': 4,\n",
       "  'Company': 'China National Petroleum',\n",
       "  'Country': 'China',\n",
       "  'Industry': 'Petroleum',\n",
       "  'Revenue in USD': '$326 billion',\n",
       "  'rev per emp': 199201.72657790987},\n",
       " {'Rank': 5,\n",
       "  'Company': 'Royal Dutch Shell',\n",
       "  'Country': 'Netherlands',\n",
       "  'Industry': 'Petroleum',\n",
       "  'Revenue in USD': '$312 billion',\n",
       "  'rev per emp': 3391304.347826087},\n",
       " {'Rank': 6,\n",
       "  'Company': 'Toyota Motor',\n",
       "  'Country': 'Japan',\n",
       "  'Industry': 'Automobiles',\n",
       "  'Revenue in USD': '$265 billion',\n",
       "  'rev per emp': 727133.0379069544},\n",
       " {'Rank': 7,\n",
       "  'Company': 'Volkswagen',\n",
       "  'Country': 'Germany',\n",
       "  'Industry': 'Automobiles',\n",
       "  'Revenue in USD': '$260 billion',\n",
       "  'rev per emp': 404800.30889377417},\n",
       " {'Rank': 8,\n",
       "  'Company': 'BP',\n",
       "  'Country': 'United Kingdom',\n",
       "  'Industry': 'Petroleum',\n",
       "  'Revenue in USD': '$245 billion',\n",
       "  'rev per emp': 3310810.810810811},\n",
       " {'Rank': 9,\n",
       "  'Company': 'Exxon Mobil',\n",
       "  'Country': 'United States',\n",
       "  'Industry': 'Petroleum',\n",
       "  'Revenue in USD': '$244 billion',\n",
       "  'rev per emp': 3505747.1264367816}]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list = pd.read_html(\"https://en.wikipedia.org/w/index.php?title=Automotive_industry&oldid=875776152\", 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": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Year': 1997, 'Production': 54434000, 'Change': '—', 'Source': '[17]'},\n",
       " {'Year': 1998, 'Production': 52987000, 'Change': '2.7%', 'Source': '[17]'}]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "car_totals[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Rank': 1, 'Group': 'Toyota', 'Country': 'Japan', 'Vehicles': 10213486},\n",
       " {'Rank': 2,\n",
       "  'Group': 'Volkswagen Group',\n",
       "  'Country': 'Germany',\n",
       "  'Vehicles': 10126281}]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97302534"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_prod = sorted((i[u\"Year\"], i[u'Production']) for i in car_totals)[-1][1]\n",
    "total_prod"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2017"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted((i[u\"Year\"], i[u'Production']) for i in car_totals)[-1][0]"
   ]
  },
  {
   "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": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(('Toyota', 'Toyota'), ('Volkswagen Group', 'Volkswagen'))\n"
     ]
    }
   ],
   "source": [
    "tmp=zip(['Toyota', 'Volkswagen Group'], ['Toyota', 'Volkswagen'])\n",
    "print(tuple(tmp))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Toyota': 10.496628998377371, 'Volkswagen': 10.407006460900597}"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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)*100\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": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'Company': 'Saudi Aramco', 'Revenue': '465.49'},\n",
       " {'Company': 'Sinopec Group', 'Revenue': '448.00'}]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rev = pd.read_html(\"https://en.wikipedia.org/w/index.php?title=List_of_largest_oil_and_gas_companies_by_revenue&oldid=871711850\", header=1)[0]\n",
    "rev = rev.iloc[:, 1:3]\n",
    "rev.columns = ['Company', 'Revenue']\n",
    "rev = rev[~(rev['Company'] == 'Company name')]\n",
    "oil_data = json.loads(rev.to_json(orient=\"records\"))\n",
    "oil_data[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "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": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5482.090000000001\n"
     ]
    }
   ],
   "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": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Sinopec Group',\n",
       " 'China National Petroleum',\n",
       " 'Royal Dutch Shell',\n",
       " 'BP',\n",
       " 'Exxon Mobil']"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Sinopec Group', True),\n",
       " ('China National Petroleum', False),\n",
       " ('Royal Dutch Shell', True),\n",
       " ('BP', True),\n",
       " ('Exxon Mobil', True)]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['Saudi Aramco', 'Sinopec Group', 'China National Petroleum Corporation', 'Exxon Mobil', 'Royal Dutch Shell', 'Kuwait Petroleum Corporation', 'BP', 'Total SA', 'Lukoil', 'Eni', 'Valero Energy', 'Chevron Corporation', 'PDVSA', 'Pemex', 'National Iranian Oil Company', 'Gazprom', 'Phillips 66', 'Petronas', 'China National Offshore Oil', 'Marathon Petroleum', 'PTT', 'Rosneft', 'JX Holdings', 'Engie', 'Petrobras', 'Equinor', 'Indian Oil Corporation', 'Sonatrach', 'Reliance Industries', 'Pertamina', 'SOCAR', 'GS Caltex', 'Enterprise Products', 'Repsol', 'Centrica', 'Bharat Petroleum', 'OMV Group', 'Idemitsu Kosan', 'Hindustan Petroleum', 'PKN Orlen', 'Schlumberger', 'Suncor Energy', 'Hellenic Petroleum', 'ConocoPhillips', 'Motor Oil Hellas', 'Ecopetrol', 'CEPSA', 'MOL'])"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 31,
   "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.items()]\n",
    "    return max(overlaps, key=lambda x : x[1])[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "matched Sinopec Group to Sinopec Group\n",
      "matched China National Petroleum to China National Petroleum Corporation\n",
      "matched Royal Dutch Shell to Royal Dutch Shell\n",
      "matched BP to BP\n",
      "matched Exxon Mobil to Exxon Mobil\n"
     ]
    }
   ],
   "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": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Toyota': 10.496628998377371,\n",
       " 'Volkswagen': 10.407006460900597,\n",
       " 'Sinopec Group': 0.0817206576323993,\n",
       " 'China National Petroleum': 0.07818550954106918,\n",
       " 'Royal Dutch Shell': 0.04833922828702191,\n",
       " 'BP': 0.040641434197541446,\n",
       " 'Exxon Mobil': 0.0490506357976611}"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rank</th>\n",
       "      <th>Company</th>\n",
       "      <th>Country</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Revenue in USD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Walmart</td>\n",
       "      <td>United States</td>\n",
       "      <td>Retail</td>\n",
       "      <td>$500 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>State Grid</td>\n",
       "      <td>China</td>\n",
       "      <td>Power</td>\n",
       "      <td>$349 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Sinopec Group</td>\n",
       "      <td>China</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$327 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>China National Petroleum</td>\n",
       "      <td>China</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$326 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Royal Dutch Shell</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$312 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Toyota Motor</td>\n",
       "      <td>Japan</td>\n",
       "      <td>Automobiles</td>\n",
       "      <td>$265 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Volkswagen</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Automobiles</td>\n",
       "      <td>$260 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>BP</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$245 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Exxon Mobil</td>\n",
       "      <td>United States</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$244 billion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Berkshire Hathaway</td>\n",
       "      <td>United States</td>\n",
       "      <td>Products</td>\n",
       "      <td>$242 billion</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rank                   Company         Country     Industry Revenue in USD\n",
       "0     1                   Walmart   United States       Retail   $500 billion\n",
       "1     2                State Grid           China        Power   $349 billion\n",
       "2     3             Sinopec Group           China    Petroleum   $327 billion\n",
       "3     4  China National Petroleum           China    Petroleum   $326 billion\n",
       "4     5         Royal Dutch Shell     Netherlands    Petroleum   $312 billion\n",
       "5     6              Toyota Motor           Japan  Automobiles   $265 billion\n",
       "6     7                Volkswagen         Germany  Automobiles   $260 billion\n",
       "7     8                        BP  United Kingdom    Petroleum   $245 billion\n",
       "8     9               Exxon Mobil   United States    Petroleum   $244 billion\n",
       "9    10        Berkshire Hathaway   United States     Products   $242 billion"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_html('https://en.wikipedia.org/w/index.php?title=Fortune_Global_500&oldid=855890446', header=0)[0]\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rank</th>\n",
       "      <th>Company</th>\n",
       "      <th>Country</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Revenue in USD</th>\n",
       "      <th>rev</th>\n",
       "      <th>employees</th>\n",
       "      <th>rev_per_employee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Exxon Mobil</td>\n",
       "      <td>United States</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$244 billion</td>\n",
       "      <td>2.440000e+11</td>\n",
       "      <td>69600</td>\n",
       "      <td>3.505747e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Royal Dutch Shell</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$312 billion</td>\n",
       "      <td>3.120000e+11</td>\n",
       "      <td>92000</td>\n",
       "      <td>3.391304e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>BP</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$245 billion</td>\n",
       "      <td>2.450000e+11</td>\n",
       "      <td>74000</td>\n",
       "      <td>3.310811e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Sinopec Group</td>\n",
       "      <td>China</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$327 billion</td>\n",
       "      <td>3.270000e+11</td>\n",
       "      <td>358571</td>\n",
       "      <td>9.119533e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Toyota Motor</td>\n",
       "      <td>Japan</td>\n",
       "      <td>Automobiles</td>\n",
       "      <td>$265 billion</td>\n",
       "      <td>2.650000e+11</td>\n",
       "      <td>364445</td>\n",
       "      <td>7.271330e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Berkshire Hathaway</td>\n",
       "      <td>United States</td>\n",
       "      <td>Products</td>\n",
       "      <td>$242 billion</td>\n",
       "      <td>2.420000e+11</td>\n",
       "      <td>377000</td>\n",
       "      <td>6.419098e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Volkswagen</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Automobiles</td>\n",
       "      <td>$260 billion</td>\n",
       "      <td>2.600000e+11</td>\n",
       "      <td>642292</td>\n",
       "      <td>4.048003e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>State Grid</td>\n",
       "      <td>China</td>\n",
       "      <td>Power</td>\n",
       "      <td>$349 billion</td>\n",
       "      <td>3.490000e+11</td>\n",
       "      <td>927839</td>\n",
       "      <td>3.761428e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Walmart</td>\n",
       "      <td>United States</td>\n",
       "      <td>Retail</td>\n",
       "      <td>$500 billion</td>\n",
       "      <td>5.000000e+11</td>\n",
       "      <td>2300000</td>\n",
       "      <td>2.173913e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>China National Petroleum</td>\n",
       "      <td>China</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$326 billion</td>\n",
       "      <td>3.260000e+11</td>\n",
       "      <td>1636532</td>\n",
       "      <td>1.992017e+05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rank                   Company         Country     Industry Revenue in USD  \\\n",
       "8     9               Exxon Mobil   United States    Petroleum   $244 billion   \n",
       "4     5         Royal Dutch Shell     Netherlands    Petroleum   $312 billion   \n",
       "7     8                        BP  United Kingdom    Petroleum   $245 billion   \n",
       "2     3             Sinopec Group           China    Petroleum   $327 billion   \n",
       "5     6              Toyota Motor           Japan  Automobiles   $265 billion   \n",
       "9    10        Berkshire Hathaway   United States     Products   $242 billion   \n",
       "6     7                Volkswagen         Germany  Automobiles   $260 billion   \n",
       "1     2                State Grid           China        Power   $349 billion   \n",
       "0     1                   Walmart   United States       Retail   $500 billion   \n",
       "3     4  China National Petroleum           China    Petroleum   $326 billion   \n",
       "\n",
       "            rev  employees  rev_per_employee  \n",
       "8  2.440000e+11      69600      3.505747e+06  \n",
       "4  3.120000e+11      92000      3.391304e+06  \n",
       "7  2.450000e+11      74000      3.310811e+06  \n",
       "2  3.270000e+11     358571      9.119533e+05  \n",
       "5  2.650000e+11     364445      7.271330e+05  \n",
       "9  2.420000e+11     377000      6.419098e+05  \n",
       "6  2.600000e+11     642292      4.048003e+05  \n",
       "1  3.490000e+11     927839      3.761428e+05  \n",
       "0  5.000000e+11    2300000      2.173913e+05  \n",
       "3  3.260000e+11    1636532      1.992017e+05  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['rev'] = df['Revenue in USD'].apply(convert_revenue)\n",
    "df['employees'] = df['Company'].apply(lambda x : dict_data[mapping.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": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_list = pd.read_html(\"https://en.wikipedia.org/w/index.php?title=Automotive_industry&oldid=875776152\", header=0)\n",
    "df_totals = df_list[0]\n",
    "df_by_man = df_list[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97302534"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_prod = df_totals.sort_values(by='Year').iloc[-1]['Production']\n",
    "total_prod"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Group\n",
       "Toyota              0.104966\n",
       "Volkswagen Group    0.104070\n",
       "Name: share, dtype: float64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_by_man['share'] = df_by_man['Vehicles'].astype(float) / total_prod\n",
    "market_share = df_by_man.set_index('Group')['share'][['Toyota', 'Volkswagen Group']]\n",
    "market_share"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company</th>\n",
       "      <th>Revenue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Saudi Aramco</td>\n",
       "      <td>465.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sinopec Group</td>\n",
       "      <td>448.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>China National Petroleum Corporation</td>\n",
       "      <td>428.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Exxon Mobil</td>\n",
       "      <td>268.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Royal Dutch Shell</td>\n",
       "      <td>265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Kuwait Petroleum Corporation</td>\n",
       "      <td>251.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>BP</td>\n",
       "      <td>222.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Total SA</td>\n",
       "      <td>212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Lukoil</td>\n",
       "      <td>144.17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Eni</td>\n",
       "      <td>131.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Valero Energy</td>\n",
       "      <td>130.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Chevron Corporation</td>\n",
       "      <td>129.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>PDVSA</td>\n",
       "      <td>128.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Pemex</td>\n",
       "      <td>117.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>National Iranian Oil Company</td>\n",
       "      <td>110.00**</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Gazprom</td>\n",
       "      <td>106.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Phillips 66</td>\n",
       "      <td>102.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Petronas</td>\n",
       "      <td>100.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>China National Offshore Oil</td>\n",
       "      <td>98.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Marathon Petroleum</td>\n",
       "      <td>97.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>PTT</td>\n",
       "      <td>93.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Rosneft</td>\n",
       "      <td>91.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>JX Holdings</td>\n",
       "      <td>90.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Engie</td>\n",
       "      <td>89.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Petrobras</td>\n",
       "      <td>88.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Equinor</td>\n",
       "      <td>82.48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Indian Oil Corporation</td>\n",
       "      <td>81.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Sonatrach</td>\n",
       "      <td>76.10**</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Reliance Industries</td>\n",
       "      <td>73.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Pertamina</td>\n",
       "      <td>70.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>SOCAR</td>\n",
       "      <td>54.79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>GS Caltex</td>\n",
       "      <td>50.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Enterprise Products</td>\n",
       "      <td>47.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Repsol</td>\n",
       "      <td>47.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Centrica</td>\n",
       "      <td>45.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Bharat Petroleum</td>\n",
       "      <td>43.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>OMV Group</td>\n",
       "      <td>43.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Idemitsu Kosan</td>\n",
       "      <td>38.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Hindustan Petroleum</td>\n",
       "      <td>37.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>PKN Orlen</td>\n",
       "      <td>36.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Schlumberger</td>\n",
       "      <td>35.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Suncor Energy</td>\n",
       "      <td>34.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Hellenic Petroleum</td>\n",
       "      <td>31.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>ConocoPhillips</td>\n",
       "      <td>29.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Motor Oil Hellas</td>\n",
       "      <td>28.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Ecopetrol</td>\n",
       "      <td>26.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>CEPSA</td>\n",
       "      <td>26.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>MOL</td>\n",
       "      <td>24.63</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Company   Revenue\n",
       "0                           Saudi Aramco    465.49\n",
       "1                          Sinopec Group    448.00\n",
       "2   China National Petroleum Corporation    428.62\n",
       "3                            Exxon Mobil     268.9\n",
       "4                      Royal Dutch Shell       265\n",
       "5           Kuwait Petroleum Corporation    251.94\n",
       "6                                     BP     222.8\n",
       "7                               Total SA       212\n",
       "8                                 Lukoil    144.17\n",
       "9                                    Eni    131.82\n",
       "10                         Valero Energy    130.84\n",
       "11                   Chevron Corporation     129.9\n",
       "12                                 PDVSA    128.44\n",
       "13                                 Pemex    117.50\n",
       "14          National Iranian Oil Company  110.00**\n",
       "15                               Gazprom     106.3\n",
       "16                           Phillips 66    102.35\n",
       "17                              Petronas    100.74\n",
       "18           China National Offshore Oil     98.53\n",
       "19                    Marathon Petroleum     97.81\n",
       "20                                   PTT     93.55\n",
       "21                               Rosneft     91.72\n",
       "22                           JX Holdings     90.67\n",
       "23                                 Engie     89.64\n",
       "24                             Petrobras      88.8\n",
       "25                               Equinor     82.48\n",
       "26                Indian Oil Corporation     81.55\n",
       "27                             Sonatrach   76.10**\n",
       "28                   Reliance Industries     73.10\n",
       "29                             Pertamina     70.65\n",
       "30                                 SOCAR     54.79\n",
       "31                             GS Caltex     50.26\n",
       "32                   Enterprise Products     47.95\n",
       "33                                Repsol     47.29\n",
       "34                              Centrica     45.29\n",
       "35                      Bharat Petroleum     43.46\n",
       "36                             OMV Group     43.09\n",
       "37                        Idemitsu Kosan     38.58\n",
       "38                   Hindustan Petroleum     37.85\n",
       "39                             PKN Orlen      36.1\n",
       "40                          Schlumberger     35.47\n",
       "41                         Suncor Energy     34.66\n",
       "42                    Hellenic Petroleum     31.28\n",
       "43                        ConocoPhillips     29.11\n",
       "44                      Motor Oil Hellas     28.98\n",
       "45                             Ecopetrol     26.99\n",
       "46                                 CEPSA     26.90\n",
       "47                                   MOL     24.63"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rev = pd.read_html(\"https://en.wikipedia.org/w/index.php?title=List_of_largest_oil_and_gas_companies_by_revenue&oldid=871711850\", header=1)[0]\n",
    "rev = rev.iloc[:, 1:3]\n",
    "rev.columns = ['Company', 'Revenue']\n",
    "rev = rev[~(rev['Company'] == 'Company name')]\n",
    "rev"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5482.09"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company</th>\n",
       "      <th>Revenue</th>\n",
       "      <th>rev_clean</th>\n",
       "      <th>share</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Saudi Aramco</td>\n",
       "      <td>465.49</td>\n",
       "      <td>465.49</td>\n",
       "      <td>0.084911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Sinopec Group</td>\n",
       "      <td>448.00</td>\n",
       "      <td>448.00</td>\n",
       "      <td>0.081721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>China National Petroleum Corporation</td>\n",
       "      <td>428.62</td>\n",
       "      <td>428.62</td>\n",
       "      <td>0.078186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Exxon Mobil</td>\n",
       "      <td>268.9</td>\n",
       "      <td>268.90</td>\n",
       "      <td>0.049051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Royal Dutch Shell</td>\n",
       "      <td>265</td>\n",
       "      <td>265.00</td>\n",
       "      <td>0.048339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Kuwait Petroleum Corporation</td>\n",
       "      <td>251.94</td>\n",
       "      <td>251.94</td>\n",
       "      <td>0.045957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>BP</td>\n",
       "      <td>222.8</td>\n",
       "      <td>222.80</td>\n",
       "      <td>0.040641</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Total SA</td>\n",
       "      <td>212</td>\n",
       "      <td>212.00</td>\n",
       "      <td>0.038671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Lukoil</td>\n",
       "      <td>144.17</td>\n",
       "      <td>144.17</td>\n",
       "      <td>0.026298</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Eni</td>\n",
       "      <td>131.82</td>\n",
       "      <td>131.82</td>\n",
       "      <td>0.024046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Valero Energy</td>\n",
       "      <td>130.84</td>\n",
       "      <td>130.84</td>\n",
       "      <td>0.023867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Chevron Corporation</td>\n",
       "      <td>129.9</td>\n",
       "      <td>129.90</td>\n",
       "      <td>0.023695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>PDVSA</td>\n",
       "      <td>128.44</td>\n",
       "      <td>128.44</td>\n",
       "      <td>0.023429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Pemex</td>\n",
       "      <td>117.50</td>\n",
       "      <td>117.50</td>\n",
       "      <td>0.021433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>National Iranian Oil Company</td>\n",
       "      <td>110.00**</td>\n",
       "      <td>110.00</td>\n",
       "      <td>0.020065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Gazprom</td>\n",
       "      <td>106.3</td>\n",
       "      <td>106.30</td>\n",
       "      <td>0.019390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Phillips 66</td>\n",
       "      <td>102.35</td>\n",
       "      <td>102.35</td>\n",
       "      <td>0.018670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Petronas</td>\n",
       "      <td>100.74</td>\n",
       "      <td>100.74</td>\n",
       "      <td>0.018376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>China National Offshore Oil</td>\n",
       "      <td>98.53</td>\n",
       "      <td>98.53</td>\n",
       "      <td>0.017973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Marathon Petroleum</td>\n",
       "      <td>97.81</td>\n",
       "      <td>97.81</td>\n",
       "      <td>0.017842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>PTT</td>\n",
       "      <td>93.55</td>\n",
       "      <td>93.55</td>\n",
       "      <td>0.017065</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Rosneft</td>\n",
       "      <td>91.72</td>\n",
       "      <td>91.72</td>\n",
       "      <td>0.016731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>JX Holdings</td>\n",
       "      <td>90.67</td>\n",
       "      <td>90.67</td>\n",
       "      <td>0.016539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Engie</td>\n",
       "      <td>89.64</td>\n",
       "      <td>89.64</td>\n",
       "      <td>0.016351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Petrobras</td>\n",
       "      <td>88.8</td>\n",
       "      <td>88.80</td>\n",
       "      <td>0.016198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Equinor</td>\n",
       "      <td>82.48</td>\n",
       "      <td>82.48</td>\n",
       "      <td>0.015045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Indian Oil Corporation</td>\n",
       "      <td>81.55</td>\n",
       "      <td>81.55</td>\n",
       "      <td>0.014876</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Sonatrach</td>\n",
       "      <td>76.10**</td>\n",
       "      <td>76.10</td>\n",
       "      <td>0.013882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Reliance Industries</td>\n",
       "      <td>73.10</td>\n",
       "      <td>73.10</td>\n",
       "      <td>0.013334</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Pertamina</td>\n",
       "      <td>70.65</td>\n",
       "      <td>70.65</td>\n",
       "      <td>0.012887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>SOCAR</td>\n",
       "      <td>54.79</td>\n",
       "      <td>54.79</td>\n",
       "      <td>0.009994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>GS Caltex</td>\n",
       "      <td>50.26</td>\n",
       "      <td>50.26</td>\n",
       "      <td>0.009168</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Enterprise Products</td>\n",
       "      <td>47.95</td>\n",
       "      <td>47.95</td>\n",
       "      <td>0.008747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Repsol</td>\n",
       "      <td>47.29</td>\n",
       "      <td>47.29</td>\n",
       "      <td>0.008626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Centrica</td>\n",
       "      <td>45.29</td>\n",
       "      <td>45.29</td>\n",
       "      <td>0.008261</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Bharat Petroleum</td>\n",
       "      <td>43.46</td>\n",
       "      <td>43.46</td>\n",
       "      <td>0.007928</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>OMV Group</td>\n",
       "      <td>43.09</td>\n",
       "      <td>43.09</td>\n",
       "      <td>0.007860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Idemitsu Kosan</td>\n",
       "      <td>38.58</td>\n",
       "      <td>38.58</td>\n",
       "      <td>0.007037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>Hindustan Petroleum</td>\n",
       "      <td>37.85</td>\n",
       "      <td>37.85</td>\n",
       "      <td>0.006904</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>PKN Orlen</td>\n",
       "      <td>36.1</td>\n",
       "      <td>36.10</td>\n",
       "      <td>0.006585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>Schlumberger</td>\n",
       "      <td>35.47</td>\n",
       "      <td>35.47</td>\n",
       "      <td>0.006470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Suncor Energy</td>\n",
       "      <td>34.66</td>\n",
       "      <td>34.66</td>\n",
       "      <td>0.006322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>Hellenic Petroleum</td>\n",
       "      <td>31.28</td>\n",
       "      <td>31.28</td>\n",
       "      <td>0.005706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>ConocoPhillips</td>\n",
       "      <td>29.11</td>\n",
       "      <td>29.11</td>\n",
       "      <td>0.005310</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>Motor Oil Hellas</td>\n",
       "      <td>28.98</td>\n",
       "      <td>28.98</td>\n",
       "      <td>0.005286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>Ecopetrol</td>\n",
       "      <td>26.99</td>\n",
       "      <td>26.99</td>\n",
       "      <td>0.004923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>CEPSA</td>\n",
       "      <td>26.90</td>\n",
       "      <td>26.90</td>\n",
       "      <td>0.004907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>MOL</td>\n",
       "      <td>24.63</td>\n",
       "      <td>24.63</td>\n",
       "      <td>0.004493</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 Company   Revenue  rev_clean     share\n",
       "0                           Saudi Aramco    465.49     465.49  0.084911\n",
       "1                          Sinopec Group    448.00     448.00  0.081721\n",
       "2   China National Petroleum Corporation    428.62     428.62  0.078186\n",
       "3                            Exxon Mobil     268.9     268.90  0.049051\n",
       "4                      Royal Dutch Shell       265     265.00  0.048339\n",
       "5           Kuwait Petroleum Corporation    251.94     251.94  0.045957\n",
       "6                                     BP     222.8     222.80  0.040641\n",
       "7                               Total SA       212     212.00  0.038671\n",
       "8                                 Lukoil    144.17     144.17  0.026298\n",
       "9                                    Eni    131.82     131.82  0.024046\n",
       "10                         Valero Energy    130.84     130.84  0.023867\n",
       "11                   Chevron Corporation     129.9     129.90  0.023695\n",
       "12                                 PDVSA    128.44     128.44  0.023429\n",
       "13                                 Pemex    117.50     117.50  0.021433\n",
       "14          National Iranian Oil Company  110.00**     110.00  0.020065\n",
       "15                               Gazprom     106.3     106.30  0.019390\n",
       "16                           Phillips 66    102.35     102.35  0.018670\n",
       "17                              Petronas    100.74     100.74  0.018376\n",
       "18           China National Offshore Oil     98.53      98.53  0.017973\n",
       "19                    Marathon Petroleum     97.81      97.81  0.017842\n",
       "20                                   PTT     93.55      93.55  0.017065\n",
       "21                               Rosneft     91.72      91.72  0.016731\n",
       "22                           JX Holdings     90.67      90.67  0.016539\n",
       "23                                 Engie     89.64      89.64  0.016351\n",
       "24                             Petrobras      88.8      88.80  0.016198\n",
       "25                               Equinor     82.48      82.48  0.015045\n",
       "26                Indian Oil Corporation     81.55      81.55  0.014876\n",
       "27                             Sonatrach   76.10**      76.10  0.013882\n",
       "28                   Reliance Industries     73.10      73.10  0.013334\n",
       "29                             Pertamina     70.65      70.65  0.012887\n",
       "30                                 SOCAR     54.79      54.79  0.009994\n",
       "31                             GS Caltex     50.26      50.26  0.009168\n",
       "32                   Enterprise Products     47.95      47.95  0.008747\n",
       "33                                Repsol     47.29      47.29  0.008626\n",
       "34                              Centrica     45.29      45.29  0.008261\n",
       "35                      Bharat Petroleum     43.46      43.46  0.007928\n",
       "36                             OMV Group     43.09      43.09  0.007860\n",
       "37                        Idemitsu Kosan     38.58      38.58  0.007037\n",
       "38                   Hindustan Petroleum     37.85      37.85  0.006904\n",
       "39                             PKN Orlen      36.1      36.10  0.006585\n",
       "40                          Schlumberger     35.47      35.47  0.006470\n",
       "41                         Suncor Energy     34.66      34.66  0.006322\n",
       "42                    Hellenic Petroleum     31.28      31.28  0.005706\n",
       "43                        ConocoPhillips     29.11      29.11  0.005310\n",
       "44                      Motor Oil Hellas     28.98      28.98  0.005286\n",
       "45                             Ecopetrol     26.99      26.99  0.004923\n",
       "46                                 CEPSA     26.90      26.90  0.004907\n",
       "47                                   MOL     24.63      24.63  0.004493"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rev['share'] = rev['rev_clean'] / total\n",
    "rev"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company</th>\n",
       "      <th>Revenue</th>\n",
       "      <th>rev_clean</th>\n",
       "      <th>share</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>China National Petroleum Corporation</td>\n",
       "      <td>428.62</td>\n",
       "      <td>428.62</td>\n",
       "      <td>0.078186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Exxon Mobil</td>\n",
       "      <td>268.9</td>\n",
       "      <td>268.90</td>\n",
       "      <td>0.049051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Royal Dutch Shell</td>\n",
       "      <td>265</td>\n",
       "      <td>265.00</td>\n",
       "      <td>0.048339</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Company Revenue  rev_clean     share\n",
       "2  China National Petroleum Corporation  428.62     428.62  0.078186\n",
       "3                           Exxon Mobil   268.9     268.90  0.049051\n",
       "4                     Royal Dutch Shell     265     265.00  0.048339"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rev = rev[rev['Company'].isin(['Exxon Mobil', 'Sinopec', 'China National Petroleum Corporation', 'Royal Dutch Shell'])].copy()\n",
    "rev"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company</th>\n",
       "      <th>Revenue</th>\n",
       "      <th>rev_clean</th>\n",
       "      <th>share</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>China National Petroleum Corporation</td>\n",
       "      <td>428.62</td>\n",
       "      <td>428.62</td>\n",
       "      <td>0.078186</td>\n",
       "      <td>China National Petroleum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Exxon Mobil</td>\n",
       "      <td>268.9</td>\n",
       "      <td>268.90</td>\n",
       "      <td>0.049051</td>\n",
       "      <td>Exxon Mobil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Royal Dutch Shell</td>\n",
       "      <td>265</td>\n",
       "      <td>265.00</td>\n",
       "      <td>0.048339</td>\n",
       "      <td>Royal Dutch Shell</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Company Revenue  rev_clean     share  \\\n",
       "2  China National Petroleum Corporation  428.62     428.62  0.078186   \n",
       "3                           Exxon Mobil   268.9     268.90  0.049051   \n",
       "4                     Royal Dutch Shell     265     265.00  0.048339   \n",
       "\n",
       "                       name  \n",
       "2  China National Petroleum  \n",
       "3               Exxon Mobil  \n",
       "4         Royal Dutch Shell  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Rank</th>\n",
       "      <th>Company</th>\n",
       "      <th>Country</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Revenue in USD</th>\n",
       "      <th>rev</th>\n",
       "      <th>employees</th>\n",
       "      <th>rev_per_employee</th>\n",
       "      <th>share</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>China National Petroleum</td>\n",
       "      <td>China</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$326 billion</td>\n",
       "      <td>3.260000e+11</td>\n",
       "      <td>1636532</td>\n",
       "      <td>1.992017e+05</td>\n",
       "      <td>0.078186</td>\n",
       "      <td>China National Petroleum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>Royal Dutch Shell</td>\n",
       "      <td>Netherlands</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$312 billion</td>\n",
       "      <td>3.120000e+11</td>\n",
       "      <td>92000</td>\n",
       "      <td>3.391304e+06</td>\n",
       "      <td>0.048339</td>\n",
       "      <td>Royal Dutch Shell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>Exxon Mobil</td>\n",
       "      <td>United States</td>\n",
       "      <td>Petroleum</td>\n",
       "      <td>$244 billion</td>\n",
       "      <td>2.440000e+11</td>\n",
       "      <td>69600</td>\n",
       "      <td>3.505747e+06</td>\n",
       "      <td>0.049051</td>\n",
       "      <td>Exxon Mobil</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Rank                   Company        Country   Industry Revenue in USD  \\\n",
       "0     4  China National Petroleum          China  Petroleum   $326 billion   \n",
       "1     5         Royal Dutch Shell    Netherlands  Petroleum   $312 billion   \n",
       "2     9               Exxon Mobil  United States  Petroleum   $244 billion   \n",
       "\n",
       "            rev  employees  rev_per_employee     share  \\\n",
       "0  3.260000e+11    1636532      1.992017e+05  0.078186   \n",
       "1  3.120000e+11      92000      3.391304e+06  0.048339   \n",
       "2  2.440000e+11      69600      3.505747e+06  0.049051   \n",
       "\n",
       "                       name  \n",
       "0  China National Petroleum  \n",
       "1         Royal Dutch Shell  \n",
       "2               Exxon Mobil  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ms2 = df.merge(rev[['share', 'name']], left_on='Company', right_on='name')\n",
    "ms2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>share</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.078186</td>\n",
       "      <td>China National Petroleum</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.049051</td>\n",
       "      <td>Exxon Mobil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.048339</td>\n",
       "      <td>Royal Dutch Shell</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      share                      name\n",
       "2  0.078186  China National Petroleum\n",
       "3  0.049051               Exxon Mobil\n",
       "4  0.048339         Royal Dutch Shell"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rev[['share', '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": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Company</th>\n",
       "      <th>share</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Toyota</td>\n",
       "      <td>0.104966</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Volkswagen Group</td>\n",
       "      <td>0.104070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>China National Petroleum</td>\n",
       "      <td>0.078186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Royal Dutch Shell</td>\n",
       "      <td>0.048339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Exxon Mobil</td>\n",
       "      <td>0.049051</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    Company     share\n",
       "0                    Toyota  0.104966\n",
       "1          Volkswagen Group  0.104070\n",
       "0  China National Petroleum  0.078186\n",
       "1         Royal Dutch Shell  0.048339\n",
       "2               Exxon Mobil  0.049051"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "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; 2020 The Data Incubator.  All rights reserved.*"
   ]
  }
 ],
 "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.3"
  },
  "nbclean": true
 },
 "nbformat": 4,
 "nbformat_minor": 1
}