Data_wrangling_World_food_consumption.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data source [from kaggle.com](https://www.kaggle.com/dorbicycle/world-foodfeed-production)  \n",
    "World food consumption trends [article on FAO.org](http://www.fao.org/docrep/005/ac911e/ac911e05.htm)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 401,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Importing Necessary Libraries\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 402,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "raw_df = pd.read_csv(r'C:\\Users\\pooja\\Desktop\\FAO.csv', encoding='latin1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 403,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make a copy of original dataframe\n",
    "df = raw_df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 404,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of Observations in df:(21477, 63)\n"
     ]
    },
    {
     "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>Area Abbreviation</th>\n",
       "      <th>Area Code</th>\n",
       "      <th>Area</th>\n",
       "      <th>Item Code</th>\n",
       "      <th>Item</th>\n",
       "      <th>Element Code</th>\n",
       "      <th>Element</th>\n",
       "      <th>Unit</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>...</th>\n",
       "      <th>Y2004</th>\n",
       "      <th>Y2005</th>\n",
       "      <th>Y2006</th>\n",
       "      <th>Y2007</th>\n",
       "      <th>Y2008</th>\n",
       "      <th>Y2009</th>\n",
       "      <th>Y2010</th>\n",
       "      <th>Y2011</th>\n",
       "      <th>Y2012</th>\n",
       "      <th>Y2013</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AFG</td>\n",
       "      <td>2</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2511</td>\n",
       "      <td>Wheat and products</td>\n",
       "      <td>5142</td>\n",
       "      <td>Food</td>\n",
       "      <td>1000 tonnes</td>\n",
       "      <td>33.94</td>\n",
       "      <td>67.71</td>\n",
       "      <td>...</td>\n",
       "      <td>3249.0</td>\n",
       "      <td>3486.0</td>\n",
       "      <td>3704.0</td>\n",
       "      <td>4164.0</td>\n",
       "      <td>4252.0</td>\n",
       "      <td>4538.0</td>\n",
       "      <td>4605.0</td>\n",
       "      <td>4711.0</td>\n",
       "      <td>4810</td>\n",
       "      <td>4895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AFG</td>\n",
       "      <td>2</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2805</td>\n",
       "      <td>Rice (Milled Equivalent)</td>\n",
       "      <td>5142</td>\n",
       "      <td>Food</td>\n",
       "      <td>1000 tonnes</td>\n",
       "      <td>33.94</td>\n",
       "      <td>67.71</td>\n",
       "      <td>...</td>\n",
       "      <td>419.0</td>\n",
       "      <td>445.0</td>\n",
       "      <td>546.0</td>\n",
       "      <td>455.0</td>\n",
       "      <td>490.0</td>\n",
       "      <td>415.0</td>\n",
       "      <td>442.0</td>\n",
       "      <td>476.0</td>\n",
       "      <td>425</td>\n",
       "      <td>422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AFG</td>\n",
       "      <td>2</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2513</td>\n",
       "      <td>Barley and products</td>\n",
       "      <td>5521</td>\n",
       "      <td>Feed</td>\n",
       "      <td>1000 tonnes</td>\n",
       "      <td>33.94</td>\n",
       "      <td>67.71</td>\n",
       "      <td>...</td>\n",
       "      <td>58.0</td>\n",
       "      <td>236.0</td>\n",
       "      <td>262.0</td>\n",
       "      <td>263.0</td>\n",
       "      <td>230.0</td>\n",
       "      <td>379.0</td>\n",
       "      <td>315.0</td>\n",
       "      <td>203.0</td>\n",
       "      <td>367</td>\n",
       "      <td>360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AFG</td>\n",
       "      <td>2</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2513</td>\n",
       "      <td>Barley and products</td>\n",
       "      <td>5142</td>\n",
       "      <td>Food</td>\n",
       "      <td>1000 tonnes</td>\n",
       "      <td>33.94</td>\n",
       "      <td>67.71</td>\n",
       "      <td>...</td>\n",
       "      <td>185.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>60.0</td>\n",
       "      <td>72.0</td>\n",
       "      <td>78</td>\n",
       "      <td>89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AFG</td>\n",
       "      <td>2</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2514</td>\n",
       "      <td>Maize and products</td>\n",
       "      <td>5521</td>\n",
       "      <td>Feed</td>\n",
       "      <td>1000 tonnes</td>\n",
       "      <td>33.94</td>\n",
       "      <td>67.71</td>\n",
       "      <td>...</td>\n",
       "      <td>120.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>233.0</td>\n",
       "      <td>249.0</td>\n",
       "      <td>247.0</td>\n",
       "      <td>195.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>191.0</td>\n",
       "      <td>200</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 63 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Area Abbreviation  Area Code         Area  Item Code  \\\n",
       "0               AFG          2  Afghanistan       2511   \n",
       "1               AFG          2  Afghanistan       2805   \n",
       "2               AFG          2  Afghanistan       2513   \n",
       "3               AFG          2  Afghanistan       2513   \n",
       "4               AFG          2  Afghanistan       2514   \n",
       "\n",
       "                       Item  Element Code Element         Unit  latitude  \\\n",
       "0        Wheat and products          5142    Food  1000 tonnes     33.94   \n",
       "1  Rice (Milled Equivalent)          5142    Food  1000 tonnes     33.94   \n",
       "2       Barley and products          5521    Feed  1000 tonnes     33.94   \n",
       "3       Barley and products          5142    Food  1000 tonnes     33.94   \n",
       "4        Maize and products          5521    Feed  1000 tonnes     33.94   \n",
       "\n",
       "   longitude  ...     Y2004   Y2005   Y2006   Y2007   Y2008   Y2009   Y2010  \\\n",
       "0      67.71  ...    3249.0  3486.0  3704.0  4164.0  4252.0  4538.0  4605.0   \n",
       "1      67.71  ...     419.0   445.0   546.0   455.0   490.0   415.0   442.0   \n",
       "2      67.71  ...      58.0   236.0   262.0   263.0   230.0   379.0   315.0   \n",
       "3      67.71  ...     185.0    43.0    44.0    48.0    62.0    55.0    60.0   \n",
       "4      67.71  ...     120.0   208.0   233.0   249.0   247.0   195.0   178.0   \n",
       "\n",
       "    Y2011  Y2012  Y2013  \n",
       "0  4711.0   4810   4895  \n",
       "1   476.0    425    422  \n",
       "2   203.0    367    360  \n",
       "3    72.0     78     89  \n",
       "4   191.0    200    200  \n",
       "\n",
       "[5 rows x 63 columns]"
      ]
     },
     "execution_count": 404,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(f\"Number of Observations in df:{df.shape}\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Assesment\n",
    "### Tidiness\n",
    "* Year columns showing the quantity of food and feed consumption to be melted\n",
    "* Unnessesary columns removed\n",
    "### Quality\n",
    "* 'Y' before the years to be removed\n",
    "* `Item` values to be further grouped into categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 405,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of Countries:174\n"
     ]
    }
   ],
   "source": [
    "print(f\"Number of Countries:{df['Area'].nunique()}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 406,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Area', 'Item', 'Element', 'Y1961', 'Y1962', 'Y1963', 'Y1964', 'Y1965',\n",
       "       'Y1966', 'Y1967', 'Y1968', 'Y1969', 'Y1970', 'Y1971', 'Y1972', 'Y1973',\n",
       "       'Y1974', 'Y1975', 'Y1976', 'Y1977', 'Y1978', 'Y1979', 'Y1980', 'Y1981',\n",
       "       'Y1982', 'Y1983', 'Y1984', 'Y1985', 'Y1986', 'Y1987', 'Y1988', 'Y1989',\n",
       "       'Y1990', 'Y1991', 'Y1992', 'Y1993', 'Y1994', 'Y1995', 'Y1996', 'Y1997',\n",
       "       'Y1998', 'Y1999', 'Y2000', 'Y2001', 'Y2002', 'Y2003', 'Y2004', 'Y2005',\n",
       "       'Y2006', 'Y2007', 'Y2008', 'Y2009', 'Y2010', 'Y2011', 'Y2012', 'Y2013'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 406,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Drop unnessesary columns\n",
    "columns_to_drop = ['Area Abbreviation','Area Code', 'Item Code', 'Element Code', 'Unit','latitude','longitude']\n",
    "df.drop(columns_to_drop, axis=1, inplace = True)\n",
    "\n",
    "# Check the outcome\n",
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 407,
   "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>Area</th>\n",
       "      <th>Item</th>\n",
       "      <th>Element</th>\n",
       "      <th>Year</th>\n",
       "      <th>Weight (1000 tons)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Wheat and products</td>\n",
       "      <td>Food</td>\n",
       "      <td>Y1961</td>\n",
       "      <td>1928.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Rice (Milled Equivalent)</td>\n",
       "      <td>Food</td>\n",
       "      <td>Y1961</td>\n",
       "      <td>183.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Barley and products</td>\n",
       "      <td>Feed</td>\n",
       "      <td>Y1961</td>\n",
       "      <td>76.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Barley and products</td>\n",
       "      <td>Food</td>\n",
       "      <td>Y1961</td>\n",
       "      <td>237.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Maize and products</td>\n",
       "      <td>Feed</td>\n",
       "      <td>Y1961</td>\n",
       "      <td>210.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Area                      Item Element   Year  Weight (1000 tons)\n",
       "0  Afghanistan        Wheat and products    Food  Y1961              1928.0\n",
       "1  Afghanistan  Rice (Milled Equivalent)    Food  Y1961               183.0\n",
       "2  Afghanistan       Barley and products    Feed  Y1961                76.0\n",
       "3  Afghanistan       Barley and products    Food  Y1961               237.0\n",
       "4  Afghanistan        Maize and products    Feed  Y1961               210.0"
      ]
     },
     "execution_count": 407,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Year columns showing the quantity of food and feed consumption to be melted\n",
    "columns_to_keep = ['Area','Item','Element']\n",
    "df = pd.melt(df, id_vars=columns_to_keep, var_name=\"Year\", value_name=\"Weight (1000 tons)\")\n",
    "\n",
    "# Check the outcome\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 408,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    1961\n",
       "1    1961\n",
       "Name: Year, dtype: int32"
      ]
     },
     "execution_count": 408,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Strip 'Y' from the year\n",
    "df['Year'] = df['Year'].str.strip('Y').astype(int)\n",
    "\n",
    "# Check the outcome\n",
    "df['Year'].head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 409,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "115"
      ]
     },
     "execution_count": 409,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Group food and feed items into categories\n",
    "\n",
    "df['Item'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 410,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create lists of groups based on http://www.fao.org/nutrition/education/food-dietary-guidelines/en/\n",
    "grains = ['Wheat and products','Rice (Milled Equivalent)','Barley and products','Maize and products',\n",
    "          'Millet and products','Cereals, Other','Cereals - Excluding Beer','Rye and products','Oats',\n",
    "          'Sorghum and products','Cassava and products']\n",
    "\n",
    "fruits = ['Olives (including preserved)','Tomatoes and products','Oranges, Mandarines','Citrus, Other',\n",
    "          'Bananas','Apples and products','Pineapples and products','Dates','Grapes and products (excl wine)',\n",
    "          'Fruits, Other','Fruits - Excluding Wine','Lemons, Limes and products',\n",
    "             'Grapefruit and products','Sugar cane']\n",
    "\n",
    "vegetables = ['Potatoes and products','Vegetables, Other','Starchy Roots','Vegetables','Sweet potatoes',\n",
    "              'Roots, Other','Onions','Plantains','Pimento','Aquatic Plants','Yams','Sugar beet']\n",
    "\n",
    "sugars_sweeteners = ['Sugar (Raw Equivalent)','Sweeteners, Other','Sugar Crops',\n",
    "                      'Sugar & Sweeteners','Sugar non-centrifugal']\n",
    "\n",
    "legumes = ['Pulses, Other and products','Coffee and products','Cocoa Beans and products','Pulses',\n",
    "          'Beans','Peas','Soyabeans','Groundnuts (Shelled Eq)']\n",
    "\n",
    "nuts_seeds = ['Nuts and products','Coconuts - Incl Copra','Sesame seed','Treenuts','Rape and Mustardseed',\n",
    "              'Palm kernels','Sunflower seed','Cottonseed']\n",
    "\n",
    "oils = ['Soyabean Oil','Groundnut Oil','Sunflowerseed Oil','Rape and Mustard Oil','Cottonseed Oil',\n",
    "        'Palm Oil','Sesameseed Oil','Olive Oil','Oilcrops Oil, Other','Oilcrops','Vegetable Oils',\n",
    "        'Oilcrops, Other','Maize Germ Oil','Coconut Oil','Palmkernel Oil','Ricebran Oil']\n",
    "\n",
    "beverages = ['Tea (including mate)','Wine','Beer','Beverages, Alcoholic',\n",
    "             'Alcoholic Beverages','Beverages, Fermented']\n",
    "\n",
    "spices = ['Pepper','Spices, Other','Spices','Cloves']\n",
    "\n",
    "meat = ['Bovine Meat','Mutton & Goat Meat','Poultry Meat','Meat, Other','Meat','Pigmeat']\n",
    "\n",
    "sea_food = ['Freshwater Fish','Fish, Seafood','Demersal Fish','Pelagic Fish','Marine Fish, Other','Crustaceans',\n",
    "                'Cephalopods','Molluscs, Other','Aquatic Animals, Others','Aquatic Products, Other','Meat, Aquatic Mammals']\n",
    "\n",
    "dairy = ['Butter, Ghee','Cream','Eggs','Milk - Excluding Butter','Infant food']\n",
    "\n",
    "other_animal_products = ['Honey','Fish, Body Oil','Fish, Liver Oil','Offals','Offals, Edible','Animal fats','Fats, Animals, Raw']\n",
    "\n",
    "misc = ['Stimulants','Miscellaneous']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 411,
   "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>Area</th>\n",
       "      <th>Item</th>\n",
       "      <th>Element</th>\n",
       "      <th>Year</th>\n",
       "      <th>Weight (1000 tons)</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Wheat and products</td>\n",
       "      <td>Food</td>\n",
       "      <td>1961</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Rice (Milled Equivalent)</td>\n",
       "      <td>Food</td>\n",
       "      <td>1961</td>\n",
       "      <td>183.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Barley and products</td>\n",
       "      <td>Feed</td>\n",
       "      <td>1961</td>\n",
       "      <td>76.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Barley and products</td>\n",
       "      <td>Food</td>\n",
       "      <td>1961</td>\n",
       "      <td>237.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Maize and products</td>\n",
       "      <td>Feed</td>\n",
       "      <td>1961</td>\n",
       "      <td>210.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Area                      Item Element  Year  Weight (1000 tons)  \\\n",
       "0  Afghanistan        Wheat and products    Food  1961              1928.0   \n",
       "1  Afghanistan  Rice (Milled Equivalent)    Food  1961               183.0   \n",
       "2  Afghanistan       Barley and products    Feed  1961                76.0   \n",
       "3  Afghanistan       Barley and products    Food  1961               237.0   \n",
       "4  Afghanistan        Maize and products    Feed  1961               210.0   \n",
       "\n",
       "   Category  \n",
       "0       NaN  \n",
       "1       NaN  \n",
       "2       NaN  \n",
       "3       NaN  \n",
       "4       NaN  "
      ]
     },
     "execution_count": 411,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Create a column Category\n",
    "df['Category'] = np.repeat(np.nan,df.shape[0])\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 412,
   "metadata": {},
   "outputs": [],
   "source": [
    "food_dict = {'Grains':grains,\n",
    "             'Fruits':fruits, \n",
    "             'Vegetables':vegetables, \n",
    "             'Sugars_sweeteners':sugars_sweeteners,\n",
    "             'Legumes':legumes, \n",
    "             'Nuts_seeds':nuts_seeds, \n",
    "            'Oils':oils, \n",
    "            'Beverages':beverages, \n",
    "            'Spices':spices, \n",
    "            'Meat':meat, \n",
    "            'Sea_food':sea_food, \n",
    "            'Dairy':dairy, \n",
    "            'Other_animal_products':other_animal_products, \n",
    "            'Miscellaneous':misc }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 413,
   "metadata": {},
   "outputs": [],
   "source": [
    "for key,value in food_dict.items():\n",
    "    mask = df['Item'].isin(food_dict[key])\n",
    "    df.loc[mask,'Category']  = key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 414,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Grains                   153806\n",
       "Oils                     131387\n",
       "Fruits                   125663\n",
       "Vegetables               119621\n",
       "Sea_food                 112625\n",
       "Legumes                   87450\n",
       "Other_animal_products     76903\n",
       "Dairy                     75154\n",
       "Meat                      56657\n",
       "Beverages                 54166\n",
       "Nuts_seeds                52629\n",
       "Sugars_sweeteners         39856\n",
       "Spices                    34450\n",
       "Miscellaneous             17914\n",
       "Name: Category, dtype: int64"
      ]
     },
     "execution_count": 414,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Category'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 415,
   "metadata": {
    "scrolled": true
   },
   "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>Area</th>\n",
       "      <th>Item</th>\n",
       "      <th>Element</th>\n",
       "      <th>Year</th>\n",
       "      <th>Weight (1000 tons)</th>\n",
       "      <th>Category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Wheat and products</td>\n",
       "      <td>Food</td>\n",
       "      <td>1961</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>Grains</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Rice (Milled Equivalent)</td>\n",
       "      <td>Food</td>\n",
       "      <td>1961</td>\n",
       "      <td>183.0</td>\n",
       "      <td>Grains</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Barley and products</td>\n",
       "      <td>Feed</td>\n",
       "      <td>1961</td>\n",
       "      <td>76.0</td>\n",
       "      <td>Grains</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Barley and products</td>\n",
       "      <td>Food</td>\n",
       "      <td>1961</td>\n",
       "      <td>237.0</td>\n",
       "      <td>Grains</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Maize and products</td>\n",
       "      <td>Feed</td>\n",
       "      <td>1961</td>\n",
       "      <td>210.0</td>\n",
       "      <td>Grains</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Area                      Item Element  Year  Weight (1000 tons)  \\\n",
       "0  Afghanistan        Wheat and products    Food  1961              1928.0   \n",
       "1  Afghanistan  Rice (Milled Equivalent)    Food  1961               183.0   \n",
       "2  Afghanistan       Barley and products    Feed  1961                76.0   \n",
       "3  Afghanistan       Barley and products    Food  1961               237.0   \n",
       "4  Afghanistan        Maize and products    Feed  1961               210.0   \n",
       "\n",
       "  Category  \n",
       "0   Grains  \n",
       "1   Grains  \n",
       "2   Grains  \n",
       "3   Grains  \n",
       "4   Grains  "
      ]
     },
     "execution_count": 415,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Final look\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Additional information on world population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 416,
   "metadata": {},
   "outputs": [],
   "source": [
    "# I'm addding one more variable - Population\n",
    "df1 = pd.read_csv(r'C:\\Projects\\World_food_consumption_trends-master\\FoodBalanceSheets_E_All_Data_(Normalized).csv', encoding = 'latin1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 417,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Area Code         int64\n",
       "Area             object\n",
       "Item Code         int64\n",
       "Item             object\n",
       "Element Code      int64\n",
       "Element          object\n",
       "Year Code         int64\n",
       "Year              int64\n",
       "Unit             object\n",
       "Value           float64\n",
       "Flag             object\n",
       "dtype: object"
      ]
     },
     "execution_count": 417,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 418,
   "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>Area Code</th>\n",
       "      <th>Area</th>\n",
       "      <th>Item Code</th>\n",
       "      <th>Item</th>\n",
       "      <th>Element Code</th>\n",
       "      <th>Element</th>\n",
       "      <th>Year Code</th>\n",
       "      <th>Year</th>\n",
       "      <th>Unit</th>\n",
       "      <th>Value</th>\n",
       "      <th>Flag</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2501</td>\n",
       "      <td>Population</td>\n",
       "      <td>511</td>\n",
       "      <td>Total Population - Both sexes</td>\n",
       "      <td>1961</td>\n",
       "      <td>1961</td>\n",
       "      <td>1000 persons</td>\n",
       "      <td>8954.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2501</td>\n",
       "      <td>Population</td>\n",
       "      <td>511</td>\n",
       "      <td>Total Population - Both sexes</td>\n",
       "      <td>1962</td>\n",
       "      <td>1962</td>\n",
       "      <td>1000 persons</td>\n",
       "      <td>9142.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2501</td>\n",
       "      <td>Population</td>\n",
       "      <td>511</td>\n",
       "      <td>Total Population - Both sexes</td>\n",
       "      <td>1963</td>\n",
       "      <td>1963</td>\n",
       "      <td>1000 persons</td>\n",
       "      <td>9340.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2501</td>\n",
       "      <td>Population</td>\n",
       "      <td>511</td>\n",
       "      <td>Total Population - Both sexes</td>\n",
       "      <td>1964</td>\n",
       "      <td>1964</td>\n",
       "      <td>1000 persons</td>\n",
       "      <td>9547.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>2501</td>\n",
       "      <td>Population</td>\n",
       "      <td>511</td>\n",
       "      <td>Total Population - Both sexes</td>\n",
       "      <td>1965</td>\n",
       "      <td>1965</td>\n",
       "      <td>1000 persons</td>\n",
       "      <td>9765.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Area Code         Area  Item Code        Item  Element Code  \\\n",
       "0          2  Afghanistan       2501  Population           511   \n",
       "1          2  Afghanistan       2501  Population           511   \n",
       "2          2  Afghanistan       2501  Population           511   \n",
       "3          2  Afghanistan       2501  Population           511   \n",
       "4          2  Afghanistan       2501  Population           511   \n",
       "\n",
       "                         Element  Year Code  Year          Unit   Value Flag  \n",
       "0  Total Population - Both sexes       1961  1961  1000 persons  8954.0  NaN  \n",
       "1  Total Population - Both sexes       1962  1962  1000 persons  9142.0  NaN  \n",
       "2  Total Population - Both sexes       1963  1963  1000 persons  9340.0  NaN  \n",
       "3  Total Population - Both sexes       1964  1964  1000 persons  9547.0  NaN  \n",
       "4  Total Population - Both sexes       1965  1965  1000 persons  9765.0  NaN  "
      ]
     },
     "execution_count": 418,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 419,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Strip the data to leave only values relating to population\n",
    "df1 = df1.loc[df1['Item'] == 'Population',:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 420,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 10393 entries, 0 to 11406278\n",
      "Data columns (total 11 columns):\n",
      "Area Code       10393 non-null int64\n",
      "Area            10393 non-null object\n",
      "Item Code       10393 non-null int64\n",
      "Item            10393 non-null object\n",
      "Element Code    10393 non-null int64\n",
      "Element         10393 non-null object\n",
      "Year Code       10393 non-null int64\n",
      "Year            10393 non-null int64\n",
      "Unit            10393 non-null object\n",
      "Value           10393 non-null float64\n",
      "Flag            2054 non-null object\n",
      "dtypes: float64(1), int64(5), object(5)\n",
      "memory usage: 974.3+ KB\n"
     ]
    }
   ],
   "source": [
    "# Check the size of dataframe\n",
    "df1.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 421,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop unnessesary columns, we only want population values\n",
    "columns_to_drop = ['Area Code', 'Item Code', 'Item', 'Element Code','Element','Year Code','Unit','Flag']\n",
    "df1.drop(columns_to_drop, axis=1, inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 422,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Mere the two dataframes to reflect the population and store them in a new dataframe\n",
    "df3 = pd.merge(df, df1, on=['Area','Year'], how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 423,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename Values to Population\n",
    "df3.rename(columns={'Area':'Country','Value': 'Population (1000 ppl)'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 424,
   "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>Country</th>\n",
       "      <th>Item</th>\n",
       "      <th>Element</th>\n",
       "      <th>Year</th>\n",
       "      <th>Weight (1000 tons)</th>\n",
       "      <th>Category</th>\n",
       "      <th>Population (1000 ppl)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Wheat and products</td>\n",
       "      <td>Food</td>\n",
       "      <td>1961</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>Grains</td>\n",
       "      <td>8954.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Rice (Milled Equivalent)</td>\n",
       "      <td>Food</td>\n",
       "      <td>1961</td>\n",
       "      <td>183.0</td>\n",
       "      <td>Grains</td>\n",
       "      <td>8954.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Barley and products</td>\n",
       "      <td>Feed</td>\n",
       "      <td>1961</td>\n",
       "      <td>76.0</td>\n",
       "      <td>Grains</td>\n",
       "      <td>8954.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Barley and products</td>\n",
       "      <td>Food</td>\n",
       "      <td>1961</td>\n",
       "      <td>237.0</td>\n",
       "      <td>Grains</td>\n",
       "      <td>8954.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Maize and products</td>\n",
       "      <td>Feed</td>\n",
       "      <td>1961</td>\n",
       "      <td>210.0</td>\n",
       "      <td>Grains</td>\n",
       "      <td>8954.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Country                      Item Element  Year  Weight (1000 tons)  \\\n",
       "0  Afghanistan        Wheat and products    Food  1961              1928.0   \n",
       "1  Afghanistan  Rice (Milled Equivalent)    Food  1961               183.0   \n",
       "2  Afghanistan       Barley and products    Feed  1961                76.0   \n",
       "3  Afghanistan       Barley and products    Food  1961               237.0   \n",
       "4  Afghanistan        Maize and products    Feed  1961               210.0   \n",
       "\n",
       "  Category  Population (1000 ppl)  \n",
       "0   Grains                 8954.0  \n",
       "1   Grains                 8954.0  \n",
       "2   Grains                 8954.0  \n",
       "3   Grains                 8954.0  \n",
       "4   Grains                 8954.0  "
      ]
     },
     "execution_count": 424,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Final look\n",
    "df3.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 425,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Saving the result\n",
    "df3.to_csv('FAO_master_population.csv', encoding='latin1')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}