1-data-cleaning-emis_livestock.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create a livestock emission table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np \n",
    "import requests \n",
    "import zipfile\n",
    "import psycopg2\n",
    "import sqlalchemy\n",
    "\n",
    "import _functions_sql as fs\n",
    "import _functions_data_files as fdf\n",
    "\n",
    "# Specify source directory and file (required for the fdf function to work as expected)\n",
    "source_dir = 'faostat_emi_live'\n",
    "source_file = 'Emissions_livestock_E_All_Data_(Normalized).csv'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Import & examine emissions crops data from csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import raw data into a pandas dataframe\n",
    "df_emis_livestock = pd.read_csv(\n",
    "      fdf.get_path(source_file, source_dir)\n",
    "    , encoding='latin-1'\n",
    "    , converters={'Note': str} # handle DtypeWarning without 'low_memory=False' \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_emis_livestock.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_emis_livestock.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check for full duplicates\n",
    "df_emis_livestock.duplicated().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Check for NAs\n",
    "df_emis_livestock.isnull().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# List unique values in the Note column\n",
    "print(df_emis_livestock[\"Note\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(df_emis_livestock[\"Source\"].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dropping unnecessary columns\n",
    "df_emis_livestock = df_emis_livestock.drop(columns = 'Area Code (M49)')\n",
    "df_emis_livestock = df_emis_livestock.drop(columns = 'Item Code (CPC)')\n",
    "df_emis_livestock = df_emis_livestock.drop(columns = 'Year Code')\n",
    "df_emis_livestock = df_emis_livestock.drop(columns = 'Flag')\n",
    "df_emis_livestock = df_emis_livestock.drop(columns = 'Note')\n",
    "df_emis_livestock = df_emis_livestock.drop(columns = 'Source Code')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renaming columns\n",
    "df_emis_livestock = df_emis_livestock.rename(columns={'Area Code' : 'area_code', 'Area' : 'area',\n",
    "                                  'Item Code' : 'item_code', 'Item' : 'item',\n",
    "                                  'Element Code' : 'element_code', 'Element' : 'element',\n",
    "                                  'Year' : 'year', 'Unit' : 'unit', 'Value' : 'value',\n",
    "                                  'Source' : 'source'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_emis_livestock.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import additional table for AreaCodes (provided by the same download zip)\n",
    "source_file = 'Emissions_livestock_E_AreaCodes.csv'\n",
    "df_emis_livestock_areacodes = pd.read_csv(\n",
    "      fdf.get_path(source_file, source_dir)\n",
    "    , encoding='latin-1' \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_emis_livestock_areacodes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Verify no duplicated rows are present\n",
    "df_emis_livestock_areacodes.duplicated(keep='first').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Left merge on 'df_emis_livestock' to inspect potentially redundant columns\n",
    "df_emis_livestock_temp = df_emis_livestock.merge(df_emis_livestock_areacodes, how='left', left_on='area_code', right_on='Area Code')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_emis_livestock_temp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show divergent area values before and after merge\n",
    "df_emis_livestock_temp[['area', 'Area']][df_emis_livestock_temp['area'] != df_emis_livestock_temp['Area']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# compare divergent area values after replacing ';' with ','\n",
    "df_emis_livestock_temp['Area'] = df_emis_livestock_temp['Area'].str.replace(';', ',')\n",
    "df_emis_livestock_temp[['area', 'Area']][df_emis_livestock_temp['area'] != df_emis_livestock_temp['Area']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop 'area_code' column as it contains no additional info\n",
    "df_emis_livestock = df_emis_livestock.drop(columns = ['area_code'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import additional table for AreaCodes (provided by the same download zip)\n",
    "source_file = 'Emissions_livestock_E_ItemCodes.csv'\n",
    "df_emis_livestock_itemcodes = pd.read_csv(\n",
    "      fdf.get_path(source_file, source_dir)\n",
    "    , encoding='latin-1' \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_emis_livestock_itemcodes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Verify no duplicated rows are present\n",
    "df_emis_livestock_itemcodes.duplicated(keep='first').sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Left merge on 'df_emis_livestock' to inspect potentially redundant columns\n",
    "df_emis_livestock_temp = df_emis_livestock.merge(df_emis_livestock_itemcodes, how='left', left_on='item_code', right_on='Item Code')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_emis_livestock_temp.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show divergent area values before and after merge\n",
    "df_emis_livestock_temp[['item', 'Item']][df_emis_livestock_temp['item'] != df_emis_livestock_temp['Item']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Compare divergent Item values after replacing ';' with ','\n",
    "df_emis_livestock_temp['Item'] = df_emis_livestock_temp['Item'].str.replace(';', ',')\n",
    "df_emis_livestock_temp[['item', 'Item']][df_emis_livestock_temp['Item'] != df_emis_livestock_temp['Item']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_emis_livestock[['element_code', 'element', 'unit']].drop_duplicates().sort_values('element')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Element_code stands for a unique combination of element and unit; can be dropped\n",
    "df_emis_livestock = df_emis_livestock.drop(columns = ['element_code'])\t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_emis_livestock.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rearranging columns\n",
    "df_emis_livestock = df_emis_livestock[['area', 'year', 'item', 'item_code', 'element', 'unit', 'value', 'source']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_emis_livestock.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "fs.write_dataframe(df_emis_livestock, 'fao_emis_livestock_source')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "capstone",
   "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.9.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}