normalizing-supply-and-demand.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Normalizing Supply & Demand Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "import numpy as np\n",
    "import requests\n",
    "from geopy.geocoders import Nominatim\n",
    "\n",
    "#supressing fragmented data warnings as the techniques used here do not degrade performance too badly\n",
    "import warnings  \n",
    "warnings.simplefilter(action='ignore', category=pd.errors.PerformanceWarning)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Input Data\n",
    "Read more about Input daa in the Readme file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Input Data Files\n",
    "\n",
    "# Crop and Consumption Data\n",
    "cdl_codes = pd.read_csv(\"input-data/cdl-codes.csv\")\n",
    "county_pixels = pd.read_csv(\"input-data/county-crops-conus-all.csv\")\n",
    "state_fp_codes = pd.read_csv(\"input-data/state-fp-codes.csv\")\n",
    "scd_calories = pd.read_csv(\"input-data/stability_crop_diversity.csv\")\n",
    "cdl_scd_crosswalk = pd.read_csv(\"input-data/crosswalk-cdl<>stability-crop-diversity.csv\")\n",
    "income_consumption = pd.read_csv(\"input-data/income-consumption.csv\")\n",
    "lafa_calories = pd.read_csv(\"input-data/food-availability-2007-2017.csv\")\n",
    "\n",
    "# Imports/Exports Data\n",
    "food_exports = pd.read_csv(\"input-data/food-exports-2017.csv\", low_memory=False)\n",
    "food_imports = pd.read_csv(\"input-data/food-imports-2017.csv\")\n",
    "imports_crosswalk = pd.read_csv(\"input-data/import-commodities.csv\")\n",
    "port_codes = pd.read_csv(\"input-data/port-codes.csv\", delimiter=\"|\")\n",
    "\n",
    "# Population Data\n",
    "age_sex_pop = pd.read_csv(\"input-data/ACSST5Y2021.S0101-Data.csv\", low_memory=False) \n",
    "poverty_pop = pd.read_csv(\"input-data/ACSST5Y2021.S1701-Data.csv\", low_memory=False)\n",
    "race_pop = pd.read_csv(\"input-data/ACSST5Y2021.S1701-Data.csv\", low_memory=False)\n",
    "\n",
    "# County geo data\n",
    "county_boundaries = gpd.read_file(\"input-data/geo-boundaries-2022/cb_2022_us_county_500k.shp\")\n",
    "counties_2021 = gpd.read_file('input-data/geo-boundaries-2021/cb_2021_us_county_500k.shp')\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Output Data\n",
    "There are several intermediate outputs from this work that we've also made available. (will explain these in more detail in the readme)\n",
    "- Grown Supply (county level)\n",
    "- Imported Supply\n",
    "- Ports geojson\n",
    "- Consumption by demographics pdf conversions\n",
    "- Population by county (CONUS and Full US)\n",
    "- County distance matrix with geojson lines\n",
    "- Pixels of crops per county\n",
    "- Pixels of crops per 1km grid"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Crop Data Normalization"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## CDL Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drops all blank crop codes from the CDL code list\n",
    "cdl_codes = cdl_codes[cdl_codes.Class_Names != ' ']\n",
    "cdl_codes = cdl_codes.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# removing any rows that have less than 100 pixels of crops which is roughly 1 square kilometer\n",
    "for c in county_pixels.columns:\n",
    "    if c.startswith(\"crop\"):\n",
    "        county_pixels[c] = county_pixels[c].mask(county_pixels[c] < 100, 0)\n",
    "        # might need to change all 0's to NaNs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Joins state FIP codes to the counties crops data. This is used to join the state name to the counties crops data\n",
    "state_fp_codes.columns = state_fp_codes.columns.str.lower()\n",
    "county_pixels[\"statefp\"] = county_pixels[\"statefp\"].astype(int)\n",
    "state_fp_codes = state_fp_codes[state_fp_codes[\"state\"].notna()]\n",
    "state_fp_codes[\"fips\"] = state_fp_codes[\"fips\"].astype(int)\n",
    "county_pixels[\"statefp\"] = county_pixels[\"statefp\"].astype(str)\n",
    "state_fp_codes[\"fips\"] = state_fp_codes[\"fips\"].astype(str)\n",
    "\n",
    "# join State column and Postal column to counties based on FIPS and STATEFP\n",
    "county_pixels = county_pixels.merge(state_fp_codes, left_on=\"statefp\", right_on=\"fips\")\n",
    "county_pixels = county_pixels.drop(columns=[\"unnamed: 3\", \"unnamed: 4\", \"unnamed: 5\", \"fips\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# removing any rows that have less than 100 pixels of crops which is roughly 1 square kilometer\n",
    "# for every column that begins with crop\n",
    "for c in county_pixels.columns:\n",
    "    if c.startswith(\"crop\"):\n",
    "        # if a value in c is less than 100, replace it with 0\n",
    "        county_pixels[c] = county_pixels[c].mask(county_pixels[c] < 100, 0)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Crosswalk 1 CDL -> Stabilty Crop Diversity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Some of the crop names in the CDL Data refer to a single crop name in the stability crop diversity data\n",
    "# In these cases we \"rollup\" these crop values into a single crop value\n",
    "county_pixels[\"crop_42\"] = county_pixels[\"crop_42\"] + county_pixels[\"crop_51\"]\n",
    "county_pixels[\"crop_22\"] = county_pixels[\"crop_22\"] + county_pixels[\"crop_23\"] + county_pixels[\"crop_24\"]\n",
    "county_pixels = county_pixels.drop(columns=[\"crop_51\", \"crop_23\", \"crop_24\"])"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Crosswalk 2 Stabilty Crop Diversity -> CDL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# production has data from many years \n",
    "# This takes each crop from each state and adds the row that is closest to 2017 (the year our cdl data is from) to a dataframe called rel_prod\n",
    "rel_prod_list = []\n",
    "for s in scd_calories[\"State_Abbr\"].unique():\n",
    "    state = scd_calories[scd_calories[\"State_Abbr\"] == s]\n",
    "    for c in state[\"Crop_Name\"].unique():\n",
    "        crop = state[state[\"Crop_Name\"] == c]\n",
    "        rel_prod_list.append(crop.iloc[(crop['Year']-2017).abs().argsort()[:1]])\n",
    "\n",
    "\n",
    "rel_prod = pd.concat(rel_prod_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This function controls how crop names from the stability crop diversity dataset are consolidated\n",
    "# This will be called for crops in the stability crop diversity dataset that need to be combined into one CDL crop code\n",
    "def crop_rollup(crops, rel_prod):\n",
    "    roll_df = pd.DataFrame()\n",
    "    for s in rel_prod[\"State_Abbr\"].unique():\n",
    "        state = rel_prod[rel_prod[\"State_Abbr\"] == s]\n",
    "        temp = state[state[\"Crop_Name\"].isin(crops)]\n",
    "        if len(temp) > 0:\n",
    "            roll_df = pd.concat([\n",
    "                roll_df, \n",
    "                pd.DataFrame({\n",
    "                    \"State_Abbr\": s, \n",
    "                    \"Year\": temp[\"Year\"].max(), \n",
    "                    \"Crop_Name\": crops[0], \n",
    "                    \"Price_Received_USD_kg\": temp[\"Price_Received_USD_kg\"].mean(), \n",
    "                    \"Crop_Area_ha\": temp[\"Crop_Area_ha\"].sum(), \n",
    "                    \"Production_kg\": temp[\"Production_kg\"].sum(), \n",
    "                    \"FIPS\": temp[\"FIPS\"].iloc[0], \n",
    "                    \"kcal_kg\": temp[\"kcal_kg\"].mean(),\n",
    "                    \"Production_kcal\": temp[\"Production_kcal\"].sum(), \n",
    "                    \"Production_USD\": temp[\"Production_USD\"].sum()\n",
    "                }, \n",
    "                index=[0])\n",
    "            ])\n",
    "    roll_df = roll_df.drop_duplicates(subset=\"State_Abbr\", keep=\"first\")\n",
    "    rel_prod = rel_prod[~rel_prod[\"Crop_Name\"].isin(crops)]\n",
    "    rel_prod = pd.concat([rel_prod, roll_df])\n",
    "    return rel_prod"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "roll1 = [\"LETTUCE-HEAD\", \"LETTUCE-LEAF\", \"LETTUCE-ROMAINE\"]\n",
    "rel_prod = crop_rollup(roll1, rel_prod)\n",
    "\n",
    "roll2 = [\"PEAS-GREEN\", \"PEAS-DRY EDIBLE\", \"PEAS-AUSTRIAN WINTER\"]\n",
    "rel_prod = crop_rollup(roll2, rel_prod)\n",
    "\n",
    "roll3 = [ \"GRAPEFRUIT-ALL CLASSES\", \"LEMONS-ALL CLASSES\", \"TANGELOS-ALL CLASSES\", \"TANGERINES-ALL CLASSES\", ]\n",
    "rel_prod = crop_rollup(roll3, rel_prod)\n",
    "\n",
    "rel_prod = rel_prod.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HOPS-ALL CLASSES\n",
      "dropping COTTON-PIMA\n",
      "dropping ARTICHOKES-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping BRUSSELS SPROUTS-ALL CLASSES\n",
      "dropping MELONS-HONEYDEW\n",
      "dropping PEPPERS-BELL\n",
      "dropping ESCAROLE & ENDIVE-ALL CLASSES\n",
      "dropping BOYSENBERRIES-ALL CLASSES\n",
      "dropping DATES-ALL CLASSES\n",
      "dropping FIGS-ALL CLASSES\n",
      "dropping KIWIFRUIT-ALL CLASSES\n",
      "dropping RASPBERRIES-ALL CLASSES\n",
      "dropping BEANS-GREEN, LIMA\n",
      "dropping SPINACH-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping BEANS-GREEN, LIMA\n",
      "dropping BEANS-SNAP\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping ESCAROLE & ENDIVE-ALL CLASSES\n",
      "dropping PEPPERS-BELL\n",
      "dropping OKRA-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping BEANS-GREEN, LIMA\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping BEANS-GREEN, LIMA\n",
      "dropping BEANS-SNAP\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping PEPPERS-BELL\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping PEPPERS-BELL\n",
      "dropping BEANS-SNAP\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping BEETS-ALL CLASSES\n",
      "dropping ESCAROLE & ENDIVE-ALL CLASSES\n",
      "dropping PEPPERS-BELL\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping ESCAROLE & ENDIVE-ALL CLASSES\n",
      "dropping PEPPERS-BELL\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HOPS-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping HAZELNUTS-ALL CLASSES\n",
      "dropping BLACKBERRIES-ALL CLASSES\n",
      "dropping BOYSENBERRIES-ALL CLASSES\n",
      "dropping LOGANBERRIES-ALL CLASSES\n",
      "dropping RASPBERRIES-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping PEPPERS-BELL\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping COTTON-PIMA\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping MELONS-HONEYDEW\n",
      "dropping PEPPERS-BELL\n",
      "dropping OKRA-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping SPINACH-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HOPS-ALL CLASSES\n",
      "dropping HAZELNUTS-ALL CLASSES\n",
      "dropping RASPBERRIES-ALL CLASSES\n",
      "dropping BEANS-GREEN, LIMA\n",
      "dropping BEANS-SNAP\n",
      "dropping BEANS-SNAP\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping BEETS-ALL CLASSES\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping OKRA-ALL CLASSES\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping COTTON-PIMA\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping MELONS-HONEYDEW\n",
      "dropping DATES-ALL CLASSES\n",
      "dropping SPINACH-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping OKRA-ALL CLASSES\n",
      "dropping PEPPERS-BELL\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HOPS-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping PEPPERS-BELL\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping COTTON-PIMA\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping ESCAROLE & ENDIVE-ALL CLASSES\n",
      "dropping PEPPERS-BELL\n",
      "dropping SPINACH-ALL CLASSES\n",
      "dropping COTTON-PIMA\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping COTTON-UPLAND\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping BEANS-SNAP\n",
      "dropping PEPPERS-BELL\n",
      "dropping BEANS-GREEN, LIMA\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n",
      "dropping TOBACCO-ALL CLASSES\n",
      "dropping HAY-ALL CLASSES\n"
     ]
    }
   ],
   "source": [
    "# This adds crop_code to rel_prod and drops any rows from rel_prod that are not in final_crops (e.g. Tobacco)\n",
    "# MIGHT NEED TO DO MORE HERE TO ROLL UP ROWS AND THEN MAKE THE DROPS\n",
    "for r in rel_prod[\"Crop_Name\"]:\n",
    "    if r in cdl_scd_crosswalk[\"production_name\"].values:\n",
    "        # add the crop_code to rel_prod\n",
    "        rel_prod.loc[rel_prod[\"Crop_Name\"] == r, \"crop_code\"] = cdl_scd_crosswalk.loc[cdl_scd_crosswalk[\"production_name\"] == r, \"crop_code\"].iloc[0]\n",
    "    else:\n",
    "        # drop the row from rel_prod if it is not in final_crops\n",
    "        rel_prod = rel_prod[rel_prod[\"Crop_Name\"] != r]\n",
    "        print (\"dropping \"+ r)\n",
    "rel_prod = rel_prod.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# in counties drop any column whose column name starts with \"crop\" and is not in final_crop[crop_code] column\n",
    "# this is to remove any columns that are not in the final crop list\n",
    "for c in county_pixels.columns:\n",
    "    if c.startswith(\"crop\") and c not in cdl_scd_crosswalk[\"crop_code\"].values:\n",
    "        county_pixels = county_pixels.drop(c, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop crop_215 and 210 (Avocadoes and Prunes) from rel_prod because it is not in the CDL data\n",
    "# For some reason, there were no pixels of avocadoes or prunes in the CDL data\n",
    "rel_prod = rel_prod[rel_prod[\"crop_code\"] != \"crop_215\"]\n",
    "rel_prod = rel_prod[rel_prod[\"crop_code\"] != \"crop_210\"]\n",
    "rel_prod = rel_prod.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# adding total state pixels to rel_prod\n",
    "for i in range(len(rel_prod)):\n",
    "    # get the crop_code from the i'th row of rel_prod\n",
    "    crop_code = rel_prod.loc[i, \"crop_code\"]\n",
    "    # get the state from the i'th row of rel_prod\n",
    "    state = rel_prod.loc[i, \"State_Abbr\"]\n",
    "    # get the value of the column in county_pixels that matches crop_code and where rel_counties[\"Postal\"] matches state\n",
    "    # then, sum the values in that column this is the total number of pixels in that state that are of that crop type.\n",
    "    state_pixels = county_pixels.loc[county_pixels[\"postal\"] == state, crop_code].sum()\n",
    "    # add the value of state_pixels to the row in rel_prod\n",
    "    rel_prod.loc[i, \"state_pixels\"] = state_pixels\n",
    "\n",
    "# add kcal_pixel_state as a column to rel_prod\n",
    "#this column is the ratio of kcal/pixel for that specific state\n",
    "rel_prod[\"kcal_pixel_state\"] = rel_prod[\"Production_kcal\"] / rel_prod[\"state_pixels\"]\n",
    "#this column is the ratio of kcal/pixel for that specific state\n",
    "rel_prod[\"kg_pixel_state\"] = rel_prod[\"Production_kg\"] / rel_prod[\"state_pixels\"]\n",
    "\n",
    "# change infinite values to NaN\n",
    "rel_prod.loc[rel_prod[\"kcal_pixel_state\"] == np.inf, \"kcal_pixel_state\"] = np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/3v/8xxt4zw96bn2l6qwb8w7_5r40000gn/T/ipykernel_59435/3685293702.py:20: RuntimeWarning: invalid value encountered in scalar multiply\n",
      "  county_pixels.loc[i, state_kg_col_name] = state_kg * crop_pixels\n"
     ]
    }
   ],
   "source": [
    "for i in range(len(county_pixels)):\n",
    "    # total_state_kcal = 0\n",
    "    # total_state_kg = 0\n",
    "    state = county_pixels.loc[i, \"postal\"]\n",
    "    for c in county_pixels.columns:\n",
    "        if c.startswith(\"crop\") and not pd.isna(county_pixels.loc[i, c]):\n",
    "            crop_code = c\n",
    "            crop_pixels = county_pixels.loc[i, c]\n",
    "            state_kcal_col_name = \"kcal_state_\"+crop_code\n",
    "            state_kg_col_name = \"kg_state_\"+crop_code\n",
    "            try:\n",
    "                # kcal calculation\n",
    "                state_kcal = rel_prod.loc[(rel_prod[\"crop_code\"] == crop_code) & (rel_prod[\"State_Abbr\"] == state), \"kcal_pixel_state\"].iloc[0]\n",
    "                # kg calculation\n",
    "                state_kg = rel_prod.loc[(rel_prod[\"crop_code\"] == crop_code) & (rel_prod[\"State_Abbr\"] == state), \"kg_pixel_state\"].iloc[0]\n",
    "            except:\n",
    "                state_kcal = np.nan\n",
    "                state_kg = np.nan\n",
    "            county_pixels.loc[i, state_kcal_col_name] = state_kcal * crop_pixels\n",
    "            county_pixels.loc[i, state_kg_col_name] = state_kg * crop_pixels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'crop_1': 373150787,\n",
       " 'crop_3': 9583913,\n",
       " 'crop_4': 23490620,\n",
       " 'crop_5': 367925803,\n",
       " 'crop_6': 5519649,\n",
       " 'crop_10': 5671798,\n",
       " 'crop_12': 626744,\n",
       " 'crop_21': 9739556,\n",
       " 'crop_22': 188895736,\n",
       " 'crop_27': 2866515,\n",
       " 'crop_28': 7069477,\n",
       " 'crop_29': 3095268,\n",
       " 'crop_31': 8670916,\n",
       " 'crop_32': 940553,\n",
       " 'crop_33': 743887,\n",
       " 'crop_35': 371859,\n",
       " 'crop_41': 4742563,\n",
       " 'crop_42': 8792263,\n",
       " 'crop_43': 3730983,\n",
       " 'crop_46': 538012,\n",
       " 'crop_48': 230276,\n",
       " 'crop_49': 691123,\n",
       " 'crop_50': 274165,\n",
       " 'crop_52': 5086219,\n",
       " 'crop_53': 6035383,\n",
       " 'crop_54': 606999,\n",
       " 'crop_66': 748669,\n",
       " 'crop_67': 195679,\n",
       " 'crop_68': 1501536,\n",
       " 'crop_69': 4383980,\n",
       " 'crop_72': 452376,\n",
       " 'crop_74': 2716823,\n",
       " 'crop_75': 5503767,\n",
       " 'crop_76': 2355737,\n",
       " 'crop_77': 159730,\n",
       " 'crop_204': 1786478,\n",
       " 'crop_206': 219677,\n",
       " 'crop_207': 12979,\n",
       " 'crop_208': 89736,\n",
       " 'crop_209': 167057,\n",
       " 'crop_211': 156680,\n",
       " 'crop_212': 3561138,\n",
       " 'crop_214': 68989,\n",
       " 'crop_216': 85220,\n",
       " 'crop_218': 11913,\n",
       " 'crop_220': 239092,\n",
       " 'crop_221': 101253,\n",
       " 'crop_222': 131161,\n",
       " 'crop_223': 1303,\n",
       " 'crop_227': 198771,\n",
       " 'crop_229': 138578,\n",
       " 'crop_242': 787267,\n",
       " 'crop_243': 95279,\n",
       " 'crop_244': 13902,\n",
       " 'crop_245': 6243,\n",
       " 'crop_246': 54271,\n",
       " 'crop_248': 1337,\n",
       " 'crop_250': 90455,\n",
       " 'kcal_state_crop_1': 1354502664000000.0,\n",
       " 'kg_state_crop_1': 371096469088.4,\n",
       " 'kcal_state_crop_3': 29402530000000.0,\n",
       " 'kg_state_crop_3': 8084279497.8,\n",
       " 'kcal_state_crop_4': 30832550449160.0,\n",
       " 'kg_state_crop_4': 9371594931.599998,\n",
       " 'kcal_state_crop_5': 535488803417879.94,\n",
       " 'kg_state_crop_5': 120064797915.0,\n",
       " 'kcal_state_crop_6': 3068148546117.0,\n",
       " 'kg_state_crop_6': 972778950.0000001,\n",
       " 'kcal_state_crop_10': 18296572285220.0,\n",
       " 'kg_state_crop_10': 3226909589.6000004,\n",
       " 'kcal_state_crop_12': 355025822192.0,\n",
       " 'kg_state_crop_12': 1145247225.3000002,\n",
       " 'kcal_state_crop_21': 11528405979168.0,\n",
       " 'kg_state_crop_21': 3265838227.594,\n",
       " 'kcal_state_crop_22': 158153538194964.0,\n",
       " 'kg_state_crop_22': 47379736107.0,\n",
       " 'kcal_state_crop_27': 1000566692984.0,\n",
       " 'kg_state_crop_27': 296025584.8,\n",
       " 'kcal_state_crop_28': 2918251422750.0,\n",
       " 'kg_state_crop_28': 750193260.0,\n",
       " 'kcal_state_crop_29': 1250887880160.0,\n",
       " 'kg_state_crop_29': 330922914.0,\n",
       " 'kcal_state_crop_31': 8895931721672.0,\n",
       " 'kg_state_crop_31': 1385658989.1,\n",
       " 'kcal_state_crop_32': 526835441192.0,\n",
       " 'kg_state_crop_32': 98658260.80000001,\n",
       " 'kcal_state_crop_33': 210509056777.0,\n",
       " 'kg_state_crop_33': 79829097.75,\n",
       " 'kcal_state_crop_35': 5634634995.0,\n",
       " 'kg_state_crop_35': 22448745.0,\n",
       " 'kcal_state_crop_41': 22788040566500.0,\n",
       " 'kg_state_crop_41': 32554333725.0,\n",
       " 'kcal_state_crop_42': 5933543087664.0,\n",
       " 'kg_state_crop_42': 1692397111.1999998,\n",
       " 'kcal_state_crop_43': 12134642428717.0,\n",
       " 'kg_state_crop_43': 20994188205.6,\n",
       " 'kcal_state_crop_46': 1011327497842.0,\n",
       " 'kg_state_crop_46': 1633808144.5300002,\n",
       " 'kcal_state_crop_48': 298237193592.0,\n",
       " 'kg_state_crop_48': 1911776882.0,\n",
       " 'kcal_state_crop_49': 1368820713824.0,\n",
       " 'kg_state_crop_49': 3802280299.2000003,\n",
       " 'kcal_state_crop_50': 128580696605.2,\n",
       " 'kg_state_crop_50': 880689702.78,\n",
       " 'kcal_state_crop_52': 1194608280960.0,\n",
       " 'kg_state_crop_52': 339377534.4,\n",
       " 'kcal_state_crop_53': 1779728129075.0,\n",
       " 'kg_state_crop_53': 931378608.5800002,\n",
       " 'kcal_state_crop_54': 2073585984349.0002,\n",
       " 'kg_state_crop_54': 11088668789.4,\n",
       " 'kcal_state_crop_66': 204795674348.5,\n",
       " 'kg_state_crop_66': 398435652.05,\n",
       " 'kcal_state_crop_67': 228106354639.3,\n",
       " 'kg_state_crop_67': 609909547.35,\n",
       " 'kcal_state_crop_68': 2466132330952.0,\n",
       " 'kg_state_crop_68': 5269514094.0,\n",
       " 'kcal_state_crop_69': 3724561197943.0005,\n",
       " 'kg_state_crop_69': 6686824347.599999,\n",
       " 'kcal_state_crop_72': 708388462771.9999,\n",
       " 'kg_state_crop_72': 2390500830.0,\n",
       " 'kcal_state_crop_74': 524814781076.0,\n",
       " 'kg_state_crop_74': 143313695.1,\n",
       " 'kcal_state_crop_75': 5960620000000.0,\n",
       " 'kg_state_crop_75': 1029467700.0,\n",
       " 'kcal_state_crop_76': 1681999999999.9998,\n",
       " 'kg_state_crop_76': 571526550.0,\n",
       " 'kcal_state_crop_77': 348061534181.9001,\n",
       " 'kg_state_crop_77': 678483661.55,\n",
       " 'kcal_state_crop_204': 808014000000.0,\n",
       " 'kg_state_crop_204': 272242053.0,\n",
       " 'kcal_state_crop_206': 557404476644.0,\n",
       " 'kg_state_crop_206': 1527135436.4499998,\n",
       " 'kcal_state_crop_207': 4044072051.2999997,\n",
       " 'kg_state_crop_207': 38151623.120000005,\n",
       " 'kcal_state_crop_208': 304979343193.4,\n",
       " 'kg_state_crop_208': 235323529.6,\n",
       " 'kcal_state_crop_209': 120108349329.80002,\n",
       " 'kg_state_crop_209': 694267915.2,\n",
       " 'kcal_state_crop_211': 171835000000.0,\n",
       " 'kg_state_crop_211': 174451675.5,\n",
       " 'kcal_state_crop_212': 1583352568014.0,\n",
       " 'kg_state_crop_212': 4616186523.0,\n",
       " 'kcal_state_crop_214': 191392292314.0,\n",
       " 'kg_state_crop_214': 924601932.8,\n",
       " 'kcal_state_crop_216': 45138572851.8,\n",
       " 'kg_state_crop_216': 154584153.6,\n",
       " 'kcal_state_crop_218': 47899368000.0,\n",
       " 'kg_state_crop_218': 119748420.0,\n",
       " 'kcal_state_crop_220': 46205472168.0,\n",
       " 'kg_state_crop_220': 106957111.5,\n",
       " 'kcal_state_crop_221': 370275745547.8,\n",
       " 'kg_state_crop_221': 1230150575.44,\n",
       " 'kcal_state_crop_222': 80948073679.0,\n",
       " 'kg_state_crop_222': 385467017.52,\n",
       " 'kcal_state_crop_223': 3502661243.57,\n",
       " 'kg_state_crop_223': 7853500.545,\n",
       " 'kcal_state_crop_227': 610616515211.9999,\n",
       " 'kg_state_crop_227': 4550276186.8,\n",
       " 'kcal_state_crop_229': 130393638648.0,\n",
       " 'kg_state_crop_229': 716448564.0,\n",
       " 'kcal_state_crop_242': 127591120174.59998,\n",
       " 'kg_state_crop_242': 235407970.79999998,\n",
       " 'kcal_state_crop_243': 234325627200.0,\n",
       " 'kg_state_crop_243': 1171628136.0,\n",
       " 'kcal_state_crop_244': 40506899579.8,\n",
       " 'kg_state_crop_244': 413335710.0,\n",
       " 'kcal_state_crop_245': 7755606734.0,\n",
       " 'kg_state_crop_245': 56200048.8,\n",
       " 'kcal_state_crop_246': 1273686336.0,\n",
       " 'kg_state_crop_246': 8845044.0,\n",
       " 'kcal_state_crop_248': 7228215316.0,\n",
       " 'kg_state_crop_248': 35606972.0,\n",
       " 'kcal_state_crop_250': 161699281924.0,\n",
       " 'kg_state_crop_250': 358534992.52}"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# using this to do a spot check on the kcal values\n",
    "check_dict = {}\n",
    "for c in county_pixels.columns:\n",
    "    if c.startswith(\"crop\") or c.startswith(\"kcal\") or c.startswith(\"kg\"):\n",
    "        check_dict[c] = county_pixels[c].sum()\n",
    "check_dict"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Income Consumption Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>Food Type</th>\n",
       "      <th>low_2007-08</th>\n",
       "      <th>high_2007-08</th>\n",
       "      <th>avg_2007-08</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Fruit; total</td>\n",
       "      <td>53.233557</td>\n",
       "      <td>54.762162</td>\n",
       "      <td>53.997860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Apples; total</td>\n",
       "      <td>14.106711</td>\n",
       "      <td>13.063450</td>\n",
       "      <td>13.585080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Apples from fruit</td>\n",
       "      <td>5.402281</td>\n",
       "      <td>6.300393</td>\n",
       "      <td>5.851337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Apple juice</td>\n",
       "      <td>8.758862</td>\n",
       "      <td>6.504509</td>\n",
       "      <td>7.631685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Bananas</td>\n",
       "      <td>4.295516</td>\n",
       "      <td>4.944153</td>\n",
       "      <td>4.619835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Oils; other</td>\n",
       "      <td>0.462664</td>\n",
       "      <td>0.589670</td>\n",
       "      <td>0.526167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>Caloric sweeteners</td>\n",
       "      <td>36.772703</td>\n",
       "      <td>35.720370</td>\n",
       "      <td>36.246537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Nuts; total</td>\n",
       "      <td>2.653513</td>\n",
       "      <td>4.694677</td>\n",
       "      <td>3.674095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>Peanuts</td>\n",
       "      <td>2.023020</td>\n",
       "      <td>3.016387</td>\n",
       "      <td>2.519704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Tree nuts</td>\n",
       "      <td>0.653172</td>\n",
       "      <td>1.660147</td>\n",
       "      <td>1.156660</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Food Type  low_2007-08  high_2007-08  avg_2007-08\n",
       "0         Fruit; total    53.233557     54.762162    53.997860\n",
       "1        Apples; total    14.106711     13.063450    13.585080\n",
       "2    Apples from fruit     5.402281      6.300393     5.851337\n",
       "3          Apple juice     8.758862      6.504509     7.631685\n",
       "4              Bananas     4.295516      4.944153     4.619835\n",
       "..                 ...          ...           ...          ...\n",
       "58         Oils; other     0.462664      0.589670     0.526167\n",
       "59  Caloric sweeteners    36.772703     35.720370    36.246537\n",
       "60         Nuts; total     2.653513      4.694677     3.674095\n",
       "61             Peanuts     2.023020      3.016387     2.519704\n",
       "62           Tree nuts     0.653172      1.660147     1.156660\n",
       "\n",
       "[63 rows x 4 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# keep only most recent data from income consumption data 2007-08\n",
    "for c in income_consumption.columns:\n",
    "    if c.startswith(\"low\") or c.startswith(\"high\"):\n",
    "        if \"2007\" not in c:\n",
    "            income_consumption = income_consumption.drop(c, axis=1)\n",
    "\n",
    "# convert pounds to kilograms\n",
    "income_consumption[\"low_2007-08\"] = income_consumption[\"low_2007-08\"] * 0.453592\n",
    "income_consumption[\"high_2007-08\"] = income_consumption[\"high_2007-08\"] * 0.453592\n",
    "\n",
    "# make an average column that is the average of low and high and income consumption\n",
    "#this is used for our \"other population\" later on\n",
    "income_consumption[\"avg_2007-08\"] = (income_consumption[\"low_2007-08\"] + income_consumption[\"high_2007-08\"]) / 2\n",
    "\n",
    "income_consumption"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "income_consumption = income_consumption.replace(\"Apples; total\", \"Apples\")\n",
    "income_consumption = income_consumption.replace(\"Oranges; total\", \"Oranges\")\n",
    "\n",
    "# These are the Crops we're using moving forward. These were selected based on what could be matched to CDL data\n",
    "curated_crop_names = ['Tree nuts', 'Apples', 'Stone fruits', 'Legumes', 'Berries', 'Broccoli and cauliflower', 'Carrots', 'Celery', 'Corn flour', 'Cucumbers', 'Grapes', 'Lettuce', 'Melons', 'Oat flour', 'Onions', 'Oranges', 'Peanuts', 'Green peas', 'Peppers', 'Potatoes', 'Rice dried', 'Sweet corn', 'Tomatoes', 'Wheat flour', 'Other citrus fruits', \"Grains; total\", \"Brassica; total\", \"Bananas\", \"Tropical fruits\"]\n",
    "# Drop all rows that are not in curated_crop_names\n",
    "mask = income_consumption['Food Type'].isin(curated_crop_names)\n",
    "income_consumption = income_consumption.loc[mask]\n",
    "income_consumption = income_consumption.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Food Type</th>\n",
       "      <th>low_2007-08</th>\n",
       "      <th>high_2007-08</th>\n",
       "      <th>avg_2007-08</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apples</td>\n",
       "      <td>14.106711</td>\n",
       "      <td>13.06345</td>\n",
       "      <td>13.58508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bananas</td>\n",
       "      <td>4.295516</td>\n",
       "      <td>4.944153</td>\n",
       "      <td>4.619835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Berries</td>\n",
       "      <td>2.476612</td>\n",
       "      <td>3.347509</td>\n",
       "      <td>2.912061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Grapes</td>\n",
       "      <td>3.96893</td>\n",
       "      <td>4.331804</td>\n",
       "      <td>4.150367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Melons</td>\n",
       "      <td>1.873335</td>\n",
       "      <td>2.544651</td>\n",
       "      <td>2.208993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Oranges</td>\n",
       "      <td>15.540062</td>\n",
       "      <td>15.06379</td>\n",
       "      <td>15.301926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Other citrus fruits</td>\n",
       "      <td>2.458469</td>\n",
       "      <td>2.875773</td>\n",
       "      <td>2.667121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Stone fruits</td>\n",
       "      <td>2.662585</td>\n",
       "      <td>3.528946</td>\n",
       "      <td>3.095765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Tropical fruits</td>\n",
       "      <td>2.889381</td>\n",
       "      <td>2.394966</td>\n",
       "      <td>2.642173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Brassica; total</td>\n",
       "      <td>3.40194</td>\n",
       "      <td>4.599423</td>\n",
       "      <td>4.000681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Broccoli and cauliflower</td>\n",
       "      <td>1.605716</td>\n",
       "      <td>2.385894</td>\n",
       "      <td>1.995805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Carrots</td>\n",
       "      <td>1.560356</td>\n",
       "      <td>2.0457</td>\n",
       "      <td>1.803028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Celery</td>\n",
       "      <td>1.138516</td>\n",
       "      <td>1.329025</td>\n",
       "      <td>1.23377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Cucumbers</td>\n",
       "      <td>1.020582</td>\n",
       "      <td>1.03419</td>\n",
       "      <td>1.027386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Green peas</td>\n",
       "      <td>0.771106</td>\n",
       "      <td>0.802858</td>\n",
       "      <td>0.786982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Lettuce</td>\n",
       "      <td>4.966832</td>\n",
       "      <td>7.153146</td>\n",
       "      <td>6.059989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Onions</td>\n",
       "      <td>3.442763</td>\n",
       "      <td>4.100472</td>\n",
       "      <td>3.771617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Peppers</td>\n",
       "      <td>3.011851</td>\n",
       "      <td>3.592449</td>\n",
       "      <td>3.30215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Tomatoes</td>\n",
       "      <td>12.764079</td>\n",
       "      <td>14.578447</td>\n",
       "      <td>13.671263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Sweet corn</td>\n",
       "      <td>2.739696</td>\n",
       "      <td>2.884845</td>\n",
       "      <td>2.81227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Potatoes</td>\n",
       "      <td>22.366622</td>\n",
       "      <td>24.394178</td>\n",
       "      <td>23.3804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Legumes</td>\n",
       "      <td>2.476612</td>\n",
       "      <td>2.531043</td>\n",
       "      <td>2.503828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Corn flour</td>\n",
       "      <td>6.90367</td>\n",
       "      <td>5.610933</td>\n",
       "      <td>6.257302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Oat flour</td>\n",
       "      <td>1.006974</td>\n",
       "      <td>1.424279</td>\n",
       "      <td>1.215627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Rice dried</td>\n",
       "      <td>5.511143</td>\n",
       "      <td>5.411353</td>\n",
       "      <td>5.461248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Wheat flour</td>\n",
       "      <td>41.317695</td>\n",
       "      <td>45.613212</td>\n",
       "      <td>43.465453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Peanuts</td>\n",
       "      <td>2.02302</td>\n",
       "      <td>3.016387</td>\n",
       "      <td>2.519704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Tree nuts</td>\n",
       "      <td>0.653172</td>\n",
       "      <td>1.660147</td>\n",
       "      <td>1.15666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Other grain</td>\n",
       "      <td>4.21387</td>\n",
       "      <td>4.939617</td>\n",
       "      <td>4.576743</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Food Type low_2007-08 high_2007-08 avg_2007-08\n",
       "0                     Apples   14.106711     13.06345    13.58508\n",
       "1                    Bananas    4.295516     4.944153    4.619835\n",
       "2                    Berries    2.476612     3.347509    2.912061\n",
       "3                     Grapes     3.96893     4.331804    4.150367\n",
       "4                     Melons    1.873335     2.544651    2.208993\n",
       "5                    Oranges   15.540062     15.06379   15.301926\n",
       "6        Other citrus fruits    2.458469     2.875773    2.667121\n",
       "7               Stone fruits    2.662585     3.528946    3.095765\n",
       "8            Tropical fruits    2.889381     2.394966    2.642173\n",
       "9            Brassica; total     3.40194     4.599423    4.000681\n",
       "10  Broccoli and cauliflower    1.605716     2.385894    1.995805\n",
       "11                   Carrots    1.560356       2.0457    1.803028\n",
       "12                    Celery    1.138516     1.329025     1.23377\n",
       "13                 Cucumbers    1.020582      1.03419    1.027386\n",
       "14                Green peas    0.771106     0.802858    0.786982\n",
       "15                   Lettuce    4.966832     7.153146    6.059989\n",
       "16                    Onions    3.442763     4.100472    3.771617\n",
       "17                   Peppers    3.011851     3.592449     3.30215\n",
       "18                  Tomatoes   12.764079    14.578447   13.671263\n",
       "19                Sweet corn    2.739696     2.884845     2.81227\n",
       "20                  Potatoes   22.366622    24.394178     23.3804\n",
       "21                   Legumes    2.476612     2.531043    2.503828\n",
       "23                Corn flour     6.90367     5.610933    6.257302\n",
       "24                 Oat flour    1.006974     1.424279    1.215627\n",
       "25                Rice dried    5.511143     5.411353    5.461248\n",
       "26               Wheat flour   41.317695    45.613212   43.465453\n",
       "27                   Peanuts     2.02302     3.016387    2.519704\n",
       "28                 Tree nuts    0.653172     1.660147     1.15666\n",
       "29               Other grain     4.21387     4.939617    4.576743"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating an other grain row from Total grain - ('Corn flour' + 'Wheat flour' + 'Rice dried' = 'Oat flour')\n",
    "grain_rows = income_consumption[income_consumption['Food Type'].isin(['Corn flour', 'Wheat flour', 'Rice dried', 'Oat flour'])]\n",
    "grain_sum = pd.DataFrame(grain_rows.sum()).transpose()\n",
    "grain_sum[\"Food Type\"] = \"Other grain\"\n",
    "grain_sum[\"low_2007-08\"] = income_consumption.loc[income_consumption[\"Food Type\"] == \"Grains; total\", \"low_2007-08\"].iloc[0] - grain_sum[\"low_2007-08\"]\n",
    "grain_sum[\"high_2007-08\"] = income_consumption.loc[income_consumption[\"Food Type\"] == \"Grains; total\", \"high_2007-08\"].iloc[0] - grain_sum[\"high_2007-08\"]\n",
    "grain_sum[\"avg_2007-08\"] = income_consumption.loc[income_consumption[\"Food Type\"] == \"Grains; total\", \"avg_2007-08\"].iloc[0] - grain_sum[\"avg_2007-08\"]\n",
    "income_consumption = pd.concat([income_consumption, grain_sum], ignore_index=True)\n",
    "income_consumption = income_consumption[income_consumption['Food Type'] != 'Grains; total']\n",
    "income_consumption"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Food Type</th>\n",
       "      <th>low_2007-08</th>\n",
       "      <th>high_2007-08</th>\n",
       "      <th>avg_2007-08</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Apples</td>\n",
       "      <td>14.106711</td>\n",
       "      <td>13.06345</td>\n",
       "      <td>13.58508</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Bananas</td>\n",
       "      <td>4.295516</td>\n",
       "      <td>4.944153</td>\n",
       "      <td>4.619835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Berries</td>\n",
       "      <td>2.476612</td>\n",
       "      <td>3.347509</td>\n",
       "      <td>2.912061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Grapes</td>\n",
       "      <td>3.96893</td>\n",
       "      <td>4.331804</td>\n",
       "      <td>4.150367</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Melons</td>\n",
       "      <td>1.873335</td>\n",
       "      <td>2.544651</td>\n",
       "      <td>2.208993</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Oranges</td>\n",
       "      <td>15.540062</td>\n",
       "      <td>15.06379</td>\n",
       "      <td>15.301926</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Other citrus fruits</td>\n",
       "      <td>2.458469</td>\n",
       "      <td>2.875773</td>\n",
       "      <td>2.667121</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Stone fruits</td>\n",
       "      <td>2.662585</td>\n",
       "      <td>3.528946</td>\n",
       "      <td>3.095765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Tropical fruits</td>\n",
       "      <td>2.889381</td>\n",
       "      <td>2.394966</td>\n",
       "      <td>2.642173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Brassica; total</td>\n",
       "      <td>3.40194</td>\n",
       "      <td>4.599423</td>\n",
       "      <td>4.000681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Broccoli and cauliflower</td>\n",
       "      <td>1.605716</td>\n",
       "      <td>2.385894</td>\n",
       "      <td>1.995805</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Carrots</td>\n",
       "      <td>1.560356</td>\n",
       "      <td>2.0457</td>\n",
       "      <td>1.803028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Celery</td>\n",
       "      <td>1.138516</td>\n",
       "      <td>1.329025</td>\n",
       "      <td>1.23377</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Cucumbers</td>\n",
       "      <td>1.020582</td>\n",
       "      <td>1.03419</td>\n",
       "      <td>1.027386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Green peas</td>\n",
       "      <td>0.771106</td>\n",
       "      <td>0.802858</td>\n",
       "      <td>0.786982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Lettuce</td>\n",
       "      <td>4.966832</td>\n",
       "      <td>7.153146</td>\n",
       "      <td>6.059989</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Onions</td>\n",
       "      <td>3.442763</td>\n",
       "      <td>4.100472</td>\n",
       "      <td>3.771617</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Peppers</td>\n",
       "      <td>3.011851</td>\n",
       "      <td>3.592449</td>\n",
       "      <td>3.30215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Tomatoes</td>\n",
       "      <td>12.764079</td>\n",
       "      <td>14.578447</td>\n",
       "      <td>13.671263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>Sweet corn</td>\n",
       "      <td>2.739696</td>\n",
       "      <td>2.884845</td>\n",
       "      <td>2.81227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Potatoes</td>\n",
       "      <td>22.366622</td>\n",
       "      <td>24.394178</td>\n",
       "      <td>23.3804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Legumes</td>\n",
       "      <td>2.476612</td>\n",
       "      <td>2.531043</td>\n",
       "      <td>2.503828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Corn flour</td>\n",
       "      <td>6.90367</td>\n",
       "      <td>5.610933</td>\n",
       "      <td>6.257302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Oat flour</td>\n",
       "      <td>1.006974</td>\n",
       "      <td>1.424279</td>\n",
       "      <td>1.215627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Rice dried</td>\n",
       "      <td>5.511143</td>\n",
       "      <td>5.411353</td>\n",
       "      <td>5.461248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Wheat flour</td>\n",
       "      <td>41.317695</td>\n",
       "      <td>45.613212</td>\n",
       "      <td>43.465453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>Peanuts</td>\n",
       "      <td>2.02302</td>\n",
       "      <td>3.016387</td>\n",
       "      <td>2.519704</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Tree nuts</td>\n",
       "      <td>0.653172</td>\n",
       "      <td>1.660147</td>\n",
       "      <td>1.15666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>Other grain</td>\n",
       "      <td>4.21387</td>\n",
       "      <td>4.939617</td>\n",
       "      <td>4.576743</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Radishes</td>\n",
       "      <td>0.898112</td>\n",
       "      <td>1.106764</td>\n",
       "      <td>1.002438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Cabbage</td>\n",
       "      <td>0.898112</td>\n",
       "      <td>1.106764</td>\n",
       "      <td>1.002438</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Food Type low_2007-08 high_2007-08 avg_2007-08\n",
       "0                     Apples   14.106711     13.06345    13.58508\n",
       "1                    Bananas    4.295516     4.944153    4.619835\n",
       "2                    Berries    2.476612     3.347509    2.912061\n",
       "3                     Grapes     3.96893     4.331804    4.150367\n",
       "4                     Melons    1.873335     2.544651    2.208993\n",
       "5                    Oranges   15.540062     15.06379   15.301926\n",
       "6        Other citrus fruits    2.458469     2.875773    2.667121\n",
       "7               Stone fruits    2.662585     3.528946    3.095765\n",
       "8            Tropical fruits    2.889381     2.394966    2.642173\n",
       "9            Brassica; total     3.40194     4.599423    4.000681\n",
       "10  Broccoli and cauliflower    1.605716     2.385894    1.995805\n",
       "11                   Carrots    1.560356       2.0457    1.803028\n",
       "12                    Celery    1.138516     1.329025     1.23377\n",
       "13                 Cucumbers    1.020582      1.03419    1.027386\n",
       "14                Green peas    0.771106     0.802858    0.786982\n",
       "15                   Lettuce    4.966832     7.153146    6.059989\n",
       "16                    Onions    3.442763     4.100472    3.771617\n",
       "17                   Peppers    3.011851     3.592449     3.30215\n",
       "18                  Tomatoes   12.764079    14.578447   13.671263\n",
       "19                Sweet corn    2.739696     2.884845     2.81227\n",
       "20                  Potatoes   22.366622    24.394178     23.3804\n",
       "21                   Legumes    2.476612     2.531043    2.503828\n",
       "22                Corn flour     6.90367     5.610933    6.257302\n",
       "23                 Oat flour    1.006974     1.424279    1.215627\n",
       "24                Rice dried    5.511143     5.411353    5.461248\n",
       "25               Wheat flour   41.317695    45.613212   43.465453\n",
       "26                   Peanuts     2.02302     3.016387    2.519704\n",
       "27                 Tree nuts    0.653172     1.660147     1.15666\n",
       "28               Other grain     4.21387     4.939617    4.576743\n",
       "29                  Radishes    0.898112     1.106764    1.002438\n",
       "30                   Cabbage    0.898112     1.106764    1.002438"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a new row called radishes that is Brassica; total- Broccoli and cauliflower / 2\n",
    "Radishes = income_consumption[income_consumption['Food Type'] == 'Brassica; total'].copy()\n",
    "Radishes.loc[Radishes['Food Type'] == \"Brassica; total\", 'Food Type'] = \"Radishes\"\n",
    "Radishes.loc[:, 'low_2007-08'] = (Radishes['low_2007-08'] - income_consumption.loc[income_consumption['Food Type'] == 'Broccoli and cauliflower', 'low_2007-08'].iloc[0]) / 2\n",
    "Radishes.loc[:, 'high_2007-08'] = (Radishes['high_2007-08'] - income_consumption.loc[income_consumption['Food Type'] == 'Broccoli and cauliflower', 'high_2007-08'].iloc[0]) / 2\n",
    "Radishes.loc[:, 'avg_2007-08'] = (Radishes['avg_2007-08'] - income_consumption.loc[income_consumption['Food Type'] == 'Broccoli and cauliflower', 'avg_2007-08'].iloc[0]) / 2\n",
    "\n",
    "# create a new row called Cabbage that is Brassica; total- Broccoli and cauliflower / 2\n",
    "Cabbage = income_consumption[income_consumption['Food Type'] == 'Brassica; total'].copy()\n",
    "Cabbage.loc[Cabbage['Food Type'] == 'Brassica; total', 'Food Type'] = 'Cabbage'\n",
    "Cabbage.loc[:, 'low_2007-08'] = (Cabbage['low_2007-08'] - income_consumption.loc[income_consumption['Food Type'] == 'Broccoli and cauliflower', 'low_2007-08'].iloc[0]) / 2\n",
    "Cabbage.loc[:, 'high_2007-08'] = (Cabbage['high_2007-08'] - income_consumption.loc[income_consumption['Food Type'] == 'Broccoli and cauliflower', 'high_2007-08'].iloc[0]) / 2\n",
    "Cabbage.loc[:, 'avg_2007-08'] = (Cabbage['avg_2007-08'] - income_consumption.loc[income_consumption['Food Type'] == 'Broccoli and cauliflower', 'avg_2007-08'].iloc[0]) / 2\n",
    "\n",
    "\n",
    "income_consumption = pd.concat([income_consumption, Radishes, Cabbage], ignore_index=True)\n",
    "# income_consumption = income_consumption.drop(\"Brassica; total\", axis=1)\n",
    "income_consumption"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Transposing income consumption to make it easier to work with during the next steps\n",
    "income_consumption = income_consumption.set_index(\"Food Type\").transpose()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Crosswalk 3 County Production -> Consumption by income\n",
    "1. drop irrelevent crops, do the rollups as needed, and rename all columns to match the demographic consumption data\n",
    "2. adjust the county_pixelsuction data by how much was consumed by each demographic\n",
    "\n",
    "*Note we are using income as it's most indicative of the consumption*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# many of the categories originally from CDL data need to be grouped to match up with the consumption by income data\n",
    "\n",
    "# create Tree Nut column from crop_75, crop_74, crop_204, crop_76\n",
    "county_pixels[\"pixels_Tree nuts\"] = county_pixels[\"crop_75\"] + county_pixels[\"crop_74\"] + county_pixels[\"crop_204\"] + county_pixels[\"crop_76\"]\n",
    "county_pixels[\"kcal_Tree nuts\"] = county_pixels[\"kcal_state_crop_75\"] + county_pixels[\"kcal_state_crop_74\"] + county_pixels[\"kcal_state_crop_204\"] + county_pixels[\"kcal_state_crop_76\"]\n",
    "county_pixels[\"kg_Tree nuts\"] = county_pixels[\"kg_state_crop_75\"] + county_pixels[\"kg_state_crop_74\"] + county_pixels[\"kg_state_crop_204\"] + county_pixels[\"kg_state_crop_76\"]\n",
    "county_pixels = county_pixels.drop([\"crop_75\", \"crop_74\", \"crop_204\", \"crop_76\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kcal_state_crop_75\", \"kcal_state_crop_74\", \"kcal_state_crop_204\", \"kcal_state_crop_76\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kg_state_crop_75\", \"kg_state_crop_74\", \"kg_state_crop_204\", \"kg_state_crop_76\"], axis=1)\n",
    "\n",
    "# create Other grain column from crop_27 and crop_21\n",
    "county_pixels[\"pixels_Other grain\"] = county_pixels[\"crop_27\"] + county_pixels[\"crop_21\"]\n",
    "county_pixels[\"kcal_Other grain\"] = county_pixels[\"kcal_state_crop_27\"] + county_pixels[\"kcal_state_crop_21\"]\n",
    "county_pixels[\"kg_Other grain\"] = county_pixels[\"kg_state_crop_27\"] + county_pixels[\"kg_state_crop_21\"]\n",
    "county_pixels = county_pixels.drop([\"crop_27\", \"crop_21\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kcal_state_crop_27\", \"kcal_state_crop_21\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kg_state_crop_27\", \"kg_state_crop_21\"], axis=1)\n",
    "\n",
    "# create Broccoli and cauliflower column from crop_214 and crop_244\n",
    "county_pixels[\"pixels_Broccoli and cauliflower\"] = county_pixels[\"crop_214\"] + county_pixels[\"crop_244\"]\n",
    "county_pixels[\"kcal_Broccoli and cauliflower\"] = county_pixels[\"kcal_state_crop_214\"] + county_pixels[\"kcal_state_crop_244\"]\n",
    "county_pixels[\"kg_Broccoli and cauliflower\"] = county_pixels[\"kg_state_crop_214\"] + county_pixels[\"kg_state_crop_244\"]\n",
    "county_pixels = county_pixels.drop([\"crop_214\", \"crop_244\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kcal_state_crop_214\", \"kcal_state_crop_244\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kg_state_crop_214\", \"kg_state_crop_244\"], axis=1)\n",
    "\n",
    "# create Stone fruits column from crop_223  crop_66  crop_67  crop_220  crop_210\n",
    "county_pixels[\"pixels_Stone fruits\"] = county_pixels[\"crop_223\"] + county_pixels[\"crop_66\"] + county_pixels[\"crop_67\"] + county_pixels[\"crop_220\"]\n",
    "county_pixels[\"kcal_Stone fruits\"] = county_pixels[\"kcal_state_crop_223\"] + county_pixels[\"kcal_state_crop_66\"] + county_pixels[\"kcal_state_crop_67\"] + county_pixels[\"kcal_state_crop_220\"]\n",
    "county_pixels[\"kg_Stone fruits\"] = county_pixels[\"kg_state_crop_223\"] + county_pixels[\"kg_state_crop_66\"] + county_pixels[\"kg_state_crop_67\"] + county_pixels[\"kg_state_crop_220\"]\n",
    "county_pixels = county_pixels.drop([\"crop_223\", \"crop_66\", \"crop_67\", \"crop_220\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kcal_state_crop_223\", \"kcal_state_crop_66\", \"kcal_state_crop_67\", \"kcal_state_crop_220\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kg_state_crop_223\", \"kg_state_crop_66\", \"kg_state_crop_67\", \"kg_state_crop_220\"], axis=1)\n",
    "\n",
    "# create Berries column from crop_242 crop_250 crop_221\n",
    "county_pixels[\"pixels_Berries\"] = county_pixels[\"crop_242\"] + county_pixels[\"crop_250\"] + county_pixels[\"crop_221\"]\n",
    "county_pixels[\"kcal_Berries\"] = county_pixels[\"kcal_state_crop_242\"] + county_pixels[\"kcal_state_crop_250\"] + county_pixels[\"kcal_state_crop_221\"]\n",
    "county_pixels[\"kg_Berries\"] = county_pixels[\"kg_state_crop_242\"] + county_pixels[\"kg_state_crop_250\"] + county_pixels[\"kg_state_crop_221\"]\n",
    "county_pixels = county_pixels.drop([\"crop_242\", \"crop_250\", \"crop_221\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kcal_state_crop_242\", \"kcal_state_crop_250\", \"kcal_state_crop_221\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kg_state_crop_242\", \"kg_state_crop_250\", \"kg_state_crop_221\"], axis=1)\n",
    "\n",
    "# create Legumes column from crop_42 crop_52\n",
    "county_pixels[\"pixels_Legumes\"] = county_pixels[\"crop_42\"] + county_pixels[\"crop_52\"]\n",
    "county_pixels[\"kcal_Legumes\"] = county_pixels[\"kcal_state_crop_42\"] + county_pixels[\"kcal_state_crop_52\"]\n",
    "county_pixels[\"kg_Legumes\"] = county_pixels[\"kg_state_crop_42\"] + county_pixels[\"kg_state_crop_52\"]\n",
    "county_pixels = county_pixels.drop([\"crop_42\", \"crop_52\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kcal_state_crop_42\", \"kcal_state_crop_52\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kg_state_crop_42\", \"kg_state_crop_52\"], axis=1)\n",
    "\n",
    "# create Melons column from crop_209 crop_48\n",
    "county_pixels[\"pixels_Melons\"] = county_pixels[\"crop_209\"] + county_pixels[\"crop_48\"]\n",
    "county_pixels[\"kcal_Melons\"] = county_pixels[\"kcal_state_crop_209\"] + county_pixels[\"kcal_state_crop_48\"]\n",
    "county_pixels[\"kg_Melons\"] = county_pixels[\"kg_state_crop_209\"] + county_pixels[\"kg_state_crop_48\"]\n",
    "county_pixels = county_pixels.drop([\"crop_209\", \"crop_48\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kcal_state_crop_209\", \"kcal_state_crop_48\"], axis=1)\n",
    "county_pixels = county_pixels.drop([\"kg_state_crop_209\", \"kg_state_crop_48\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop crops not listed in consumption data\n",
    "drop_list = [ \"crop_207\", \"crop_215\", \"crop_31\", \"crop_248\", \"crop_32\", \"crop_208\", \"crop_29\", \"crop_35\", \"crop_218\", \"crop_211\", \"crop_77\", \"crop_229\", \"crop_33\", \"crop_4\", \"crop_5\", \"crop_222\", \"crop_41\", \"crop_6\", \"crop_46\", ]\n",
    "kcal_drop_list = [\"kcal_state_\" + item for item in drop_list]\n",
    "kg_drop_list = [\"kg_state_\" + item for item in drop_list]\n",
    "\n",
    "# for every value in drop_list drop the column from county_pixels if it exists\n",
    "for item in drop_list:\n",
    "    if item in county_pixels.columns:\n",
    "        county_pixels = county_pixels.drop(item, axis=1)\n",
    "for item in kcal_drop_list:\n",
    "    if item in county_pixels.columns:\n",
    "        county_pixels = county_pixels.drop(item, axis=1)\n",
    "for item in kg_drop_list:\n",
    "    if item in county_pixels.columns:\n",
    "        county_pixels = county_pixels.drop(item, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# making a dictionary that will be used to rename columns in county_pixels\n",
    "code_name_map = { 'crop_68': 'Apples', 'crop_206': 'Carrots', 'crop_245': 'Celery', 'crop_1': 'Corn flour', 'crop_50': 'Cucumbers', 'crop_69': 'Grapes', 'crop_227': 'Lettuce', 'crop_28': 'Oat flour', 'crop_49': 'Onions', 'crop_212': 'Oranges', 'crop_10': 'Peanuts', 'crop_53': 'Green peas', 'crop_216': 'Peppers', 'crop_43': 'Potatoes', 'crop_3': 'Rice dried', 'crop_12': 'Sweet corn', 'crop_54': 'Tomatoes', 'crop_22': 'Wheat flour', 'crop_72': 'Other citrus fruits', 'crop_243': 'Cabbage', 'crop_246': 'Radishes'}\n",
    "kcal_code_name_map = { \"kcal_state_\" + key: value for (key, value) in code_name_map.items() }\n",
    "kcal_code_name_map = { key: \"kcal_\" + value for (key, value) in kcal_code_name_map.items() }\n",
    "kg_code_name_map = { \"kg_state_\" + key: value for (key, value) in code_name_map.items() }\n",
    "kg_code_name_map = { key: \"kg_\" + value for (key, value) in kg_code_name_map.items() }\n",
    "code_name_map = { key: \"pixels_\" + value for (key, value) in code_name_map.items() }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "statefp\n",
      "countyfp\n",
      "countyns\n",
      "geoid\n",
      "name\n",
      "namelsad\n",
      "lsad\n",
      "classfp\n",
      "mtfcc\n",
      "csafp\n",
      "cbsafp\n",
      "metdivfp\n",
      "funcstat\n",
      "aland\n",
      "awater\n",
      "intptlat\n",
      "intptlon\n",
      "pixels_Corn flour\n",
      "pixels_Rice dried\n",
      "pixels_Peanuts\n",
      "pixels_Sweet corn\n",
      "pixels_Wheat flour\n",
      "pixels_Oat flour\n",
      "pixels_Potatoes\n",
      "pixels_Onions\n",
      "pixels_Cucumbers\n",
      "pixels_Green peas\n",
      "pixels_Tomatoes\n",
      "pixels_Apples\n",
      "pixels_Grapes\n",
      "pixels_Other citrus fruits\n",
      "pixels_Carrots\n",
      "pixels_Oranges\n",
      "pixels_Peppers\n",
      "pixels_Lettuce\n",
      "pixels_Cabbage\n",
      "pixels_Celery\n",
      "pixels_Radishes\n",
      "state\n",
      "postal\n",
      "kcal_Corn flour\n",
      "kg_Corn flour\n",
      "kcal_Rice dried\n",
      "kg_Rice dried\n",
      "kcal_Peanuts\n",
      "kg_Peanuts\n",
      "kcal_Sweet corn\n",
      "kg_Sweet corn\n",
      "kcal_Wheat flour\n",
      "kg_Wheat flour\n",
      "kcal_Oat flour\n",
      "kg_Oat flour\n",
      "kcal_Potatoes\n",
      "kg_Potatoes\n",
      "kcal_Onions\n",
      "kg_Onions\n",
      "kcal_Cucumbers\n",
      "kg_Cucumbers\n",
      "kcal_Green peas\n",
      "kg_Green peas\n",
      "kcal_Tomatoes\n",
      "kg_Tomatoes\n",
      "kcal_Apples\n",
      "kg_Apples\n",
      "kcal_Grapes\n",
      "kg_Grapes\n",
      "kcal_Other citrus fruits\n",
      "kg_Other citrus fruits\n",
      "kcal_Carrots\n",
      "kg_Carrots\n",
      "kcal_Oranges\n",
      "kg_Oranges\n",
      "kcal_Peppers\n",
      "kg_Peppers\n",
      "kcal_Lettuce\n",
      "kg_Lettuce\n",
      "kcal_Cabbage\n",
      "kg_Cabbage\n",
      "kcal_Celery\n",
      "kg_Celery\n",
      "kcal_Radishes\n",
      "kg_Radishes\n",
      "pixels_Tree nuts\n",
      "kcal_Tree nuts\n",
      "kg_Tree nuts\n",
      "pixels_Other grain\n",
      "kcal_Other grain\n",
      "kg_Other grain\n",
      "pixels_Broccoli and cauliflower\n",
      "kcal_Broccoli and cauliflower\n",
      "kg_Broccoli and cauliflower\n",
      "pixels_Stone fruits\n",
      "kcal_Stone fruits\n",
      "kg_Stone fruits\n",
      "pixels_Berries\n",
      "kcal_Berries\n",
      "kg_Berries\n",
      "pixels_Legumes\n",
      "kcal_Legumes\n",
      "kg_Legumes\n",
      "pixels_Melons\n",
      "kcal_Melons\n",
      "kg_Melons\n"
     ]
    }
   ],
   "source": [
    "# for every column in county_prod that matches a key in code_name_map, rename the column to the value in code_name_map\n",
    "county_pixels = county_pixels.rename(columns=code_name_map)\n",
    "county_pixels = county_pixels.rename(columns=kcal_code_name_map)\n",
    "county_pixels = county_pixels.rename(columns=kg_code_name_map)\n",
    "for c in county_pixels.columns:\n",
    "    print (c)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Crosswalk 4 2007 consumption -> 2017 consumption\n",
    "This is necessary as our prodction data is from 2017 but our consumption data is currently from 2007. We're going to scale this by the Food abailability per capita data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# cleaning income_consumption data\n",
    "# income_consumption = income_consumption.rename(columns={\"Unnamed: 0\": \"Income\"})\n",
    "income_consumption = income_consumption.drop(\"Brassica; total\", axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "lafa_calories = lafa_calories.set_index(\"Year\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for every column in income_consumption multiply that columns values by the value in the 3rd row of the food_availability column with the same name\n",
    "for c in income_consumption.columns:\n",
    "    income_consumption[c] = income_consumption[c] / lafa_calories.loc[\"ratio 2007/2017\"][c]\n",
    "income_consumption = income_consumption.rename(index={\"low_2007-08\": \"low_2017\", \"high_2007-08\": \"high_2017\", \"avg_2007-08\": \"avg_2017\"})"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Population Data"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Age and Sex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Keeping only relevant columns\n",
    "rel_columns = [\"Geography\", \"Geographic Area Name\", \"Estimate!!Total!!Total population\", \"Estimate!!Male!!Total population\", \"Estimate!!Male!!Total population!!AGE!!Under 5 years\", \"Estimate!!Male!!Total population!!AGE!!5 to 9 years\", \"Estimate!!Male!!Total population!!AGE!!10 to 14 years\", \"Estimate!!Male!!Total population!!AGE!!15 to 19 years\", \"Estimate!!Male!!Total population!!AGE!!20 to 24 years\", \"Estimate!!Male!!Total population!!AGE!!25 to 29 years\", \"Estimate!!Male!!Total population!!AGE!!30 to 34 years\", \"Estimate!!Male!!Total population!!AGE!!35 to 39 years\", \"Estimate!!Male!!Total population!!AGE!!40 to 44 years\", \"Estimate!!Male!!Total population!!AGE!!45 to 49 years\", \"Estimate!!Male!!Total population!!AGE!!50 to 54 years\", \"Estimate!!Male!!Total population!!AGE!!55 to 59 years\", \"Estimate!!Male!!Total population!!AGE!!60 to 64 years\", \"Estimate!!Male!!Total population!!AGE!!65 to 69 years\", \"Estimate!!Male!!Total population!!AGE!!70 to 74 years\", \"Estimate!!Male!!Total population!!AGE!!75 to 79 years\", \"Estimate!!Male!!Total population!!AGE!!80 to 84 years\", \"Estimate!!Male!!Total population!!AGE!!85 years and over\", \"Estimate!!Female!!Total population\", \"Estimate!!Female!!Total population!!AGE!!Under 5 years\", \"Estimate!!Female!!Total population!!AGE!!5 to 9 years\", \"Estimate!!Female!!Total population!!AGE!!10 to 14 years\", \"Estimate!!Female!!Total population!!AGE!!15 to 19 years\", \"Estimate!!Female!!Total population!!AGE!!20 to 24 years\", \"Estimate!!Female!!Total population!!AGE!!25 to 29 years\", \"Estimate!!Female!!Total population!!AGE!!30 to 34 years\", \"Estimate!!Female!!Total population!!AGE!!35 to 39 years\", \"Estimate!!Female!!Total population!!AGE!!40 to 44 years\", \"Estimate!!Female!!Total population!!AGE!!45 to 49 years\", \"Estimate!!Female!!Total population!!AGE!!50 to 54 years\", \"Estimate!!Female!!Total population!!AGE!!55 to 59 years\", \"Estimate!!Female!!Total population!!AGE!!60 to 64 years\", \"Estimate!!Female!!Total population!!AGE!!65 to 69 years\", \"Estimate!!Female!!Total population!!AGE!!70 to 74 years\", \"Estimate!!Female!!Total population!!AGE!!75 to 79 years\", \"Estimate!!Female!!Total population!!AGE!!80 to 84 years\", \"Estimate!!Female!!Total population!!AGE!!85 years and over\", ]\n",
    "age_sex_pop = age_sex_pop[[col for col in age_sex_pop.columns if col in rel_columns]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Cleaning column names\n",
    "age_sex_pop.columns = age_sex_pop.columns.str.replace('Estimate!!', '')\n",
    "age_sex_pop.columns = age_sex_pop.columns.str.replace('AGE!!', '')\n",
    "age_sex_pop.columns = age_sex_pop.columns.str.replace('Male!!Total population!!', 'male_')\n",
    "age_sex_pop.columns = age_sex_pop.columns.str.replace('Female!!Total population!!', 'female_')\n",
    "age_sex_pop.columns = age_sex_pop.columns.str.replace('Male!!Total population', 'male_total')\n",
    "age_sex_pop.columns = age_sex_pop.columns.str.replace('Female!!Total population', 'female_total')\n",
    "age_sex_pop.columns = age_sex_pop.columns.str.replace('Total!!Total population', 'total_population')\n",
    "age_sex_pop.columns = age_sex_pop.columns.str.replace(' to ', '-')\n",
    "age_sex_pop.columns = age_sex_pop.columns.str.replace(' years', '')\n",
    "age_sex_pop.columns = age_sex_pop.columns.str.replace(' and over', '+')\n",
    "age_sex_pop.columns = age_sex_pop.columns.str.replace(' ', '_')\n",
    "age_sex_pop.columns = age_sex_pop.columns.str.lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Grouping into kids and adults\n",
    "grouped_pop = age_sex_pop[['geography', 'geographic_area_name', 'total_population', 'male_total', 'female_total',]].copy()\n",
    "grouped_pop['male_kids'] = age_sex_pop.loc[:, 'male_under_5':'male_15-19'].sum(axis=1)\n",
    "grouped_pop['male_adults'] = age_sex_pop.loc[:, 'male_20-24':'male_85+'].sum(axis=1)\n",
    "grouped_pop['female_kids'] = age_sex_pop.loc[:, 'female_under_5':'female_15-19'].sum(axis=1)\n",
    "grouped_pop['female_adults'] = age_sex_pop.loc[:, 'female_20-24':'female_85+'].sum(axis=1)\n",
    "grouped_pop['kid_total'] = grouped_pop['female_kids'] + grouped_pop['male_kids']\n",
    "grouped_pop['adult_total'] = grouped_pop['female_adults'] + grouped_pop['male_adults']"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Poverty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "poverty_pop = poverty_pop[[col for col in poverty_pop.columns if 'Annotation' not in col]]\n",
    "poverty_pop = poverty_pop[[col for col in poverty_pop.columns if 'Margin of Error' not in col]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "pov_columns = [\"Geography\", \"Geographic Area Name\", \"Estimate!!Total!!Population for whom poverty status is determined\", \"Estimate!!Total!!Population for whom poverty status is determined!!ALL INDIVIDUALS WITH INCOME BELOW THE FOLLOWING POVERTY RATIOS!!185 percent of poverty level\"]\n",
    "poverty_pop = poverty_pop[[col for col in poverty_pop.columns if col in pov_columns]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Grouping into high and low income. Also adding total population column\n",
    "poverty_pop = poverty_pop.rename(columns={'Geography': 'geography'})\n",
    "poverty_pop = poverty_pop.merge(grouped_pop[['geography', 'total_population']], on='geography', how='left')\n",
    "\n",
    "# Cleaning up column names\n",
    "poverty_pop = poverty_pop.rename(columns={'Estimate!!Total!!Population for whom poverty status is determined!!ALL INDIVIDUALS WITH INCOME BELOW THE FOLLOWING POVERTY RATIOS!!185 percent of poverty level': 'low_income'})\n",
    "poverty_pop = poverty_pop.rename(columns={'Estimate!!Total!!Population for whom poverty status is determined': 'total_population_determined'})\n",
    "poverty_pop['high_income'] = poverty_pop['total_population_determined'] - poverty_pop['low_income']\n",
    "poverty_pop['unknown_income'] = poverty_pop['total_population'] - poverty_pop['total_population_determined']\n",
    "poverty_pop.columns = poverty_pop.columns.str.lower()\n",
    "poverty_pop.columns = poverty_pop.columns.str.replace(' ', '_')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Race"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "race_columns = [\"Geography\", \"Geographic Area Name\", \"Estimate!!Total!!Population for whom poverty status is determined\", \"Estimate!!Total!!Population for whom poverty status is determined!!RACE AND HISPANIC OR LATINO ORIGIN!!White alone, not Hispanic or Latino\", \"Estimate!!Total!!Population for whom poverty status is determined!!RACE AND HISPANIC OR LATINO ORIGIN!!Black or African American alone\", \"Estimate!!Total!!Population for whom poverty status is determined!!RACE AND HISPANIC OR LATINO ORIGIN!!Hispanic or Latino origin (of any race)\"]\n",
    "race_pop = race_pop[[col for col in race_pop.columns if col in race_columns]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# grouping columns and cleaning up column names\n",
    "race_pop = race_pop.rename(columns={'Geography': 'geography'})\n",
    "race_pop = race_pop.merge(grouped_pop[['geography', 'total_population']], on='geography', how='left')\n",
    "race_pop = race_pop.rename(columns={'Estimate!!Total!!Population for whom poverty status is determined': 'total_population_determined'})\n",
    "race_pop = race_pop.rename(columns={'Estimate!!Total!!Population for whom poverty status is determined!!RACE AND HISPANIC OR LATINO ORIGIN!!Black or African American alone': 'black-african_american'})\n",
    "race_pop = race_pop.rename(columns={'Estimate!!Total!!Population for whom poverty status is determined!!RACE AND HISPANIC OR LATINO ORIGIN!!Hispanic or Latino origin (of any race)': 'hispanic-latino'})\n",
    "race_pop = race_pop.rename(columns={'Estimate!!Total!!Population for whom poverty status is determined!!RACE AND HISPANIC OR LATINO ORIGIN!!White alone, not Hispanic or Latino': 'white'})\n",
    "race_pop['other_race'] = race_pop['total_population_determined']- (race_pop['black-african_american'] + race_pop['hispanic-latino'] + race_pop['white'])\n",
    "race_pop['unknown_race'] = race_pop['total_population'] - race_pop['total_population_determined']\n",
    "race_pop.columns = race_pop.columns.str.lower()\n",
    "race_pop.columns = race_pop.columns.str.replace(' ', '_')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joining Population Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "race_pop.columns = ['race_' + col if col not in ['geographic_area_name', 'geography'] else col for col in race_pop.columns]\n",
    "poverty_pop.columns = ['income_' + col if col not in ['geographic_area_name', 'geography'] else col for col in poverty_pop.columns]\n",
    "grouped_pop.columns = ['agesex_' + col if col not in ['geographic_area_name', 'geography'] else col for col in grouped_pop.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "race_pov = poverty_pop.merge(race_pop, on='geography')\n",
    "agesex_race_pov = race_pov.merge(grouped_pop, on='geography')\n",
    "columns_to_keep = ['geography', 'geographic_area_name', 'agesex_total_population', 'agesex_male_total', 'agesex_female_total', 'agesex_male_kids', 'agesex_male_adults', 'agesex_female_kids', 'agesex_female_adults', 'agesex_kid_total', 'agesex_adult_total', 'income_total_population_determined', 'income_low_income', 'income_total_population', 'income_high_income', 'income_unknown_income', 'race_total_population_determined', 'race_black-african_american', 'race_hispanic-latino', 'race_white', 'race_total_population', 'race_other_race', 'race_unknown_race']\n",
    "joined_pop = agesex_race_pov[columns_to_keep]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "joined_pop = joined_pop.rename(columns={'agesex_total_population': 'total_population'})\n",
    "joined_pop = joined_pop.drop(columns=['income_total_population', 'race_total_population'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write to output csv\n",
    "joined_pop.to_csv('output-data/population-data.csv', index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Joining Population to County Geodata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "county_boundaries = county_boundaries.set_geometry(\"geometry\")\n",
    "county_boundaries = county_boundaries.to_crs(epsg=4326)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this is to deal with Connecticut now using management regions instead of counties\n",
    "counties_2021 = counties_2021.set_geometry(\"geometry\")\n",
    "counties_2021 = counties_2021.to_crs(epsg=4326)\n",
    "county_boundaries = county_boundaries[county_boundaries[\"STUSPS\"] != \"CT\"]\n",
    "ct_df = counties_2021[counties_2021[\"STUSPS\"] == \"CT\"]\n",
    "ct_df = ct_df.set_geometry(\"geometry\")\n",
    "ct_df = ct_df.to_crs(epsg=4326)\n",
    "\n",
    "# add CT boundaries to county_boundaries\n",
    "county_boundaries = pd.concat([county_boundaries, ct_df], ignore_index=True)  \n",
    "county_boundaries.columns = county_boundaries.columns.str.lower()\n",
    "county_boundaries = county_boundaries.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "# merging population with counties\n",
    "pop_counties = joined_pop.merge(county_boundaries, left_on='geography', right_on='affgeoid')\n",
    "pop_counties = pop_counties.drop(columns=['affgeoid'])\n",
    "pop_counties.columns = pop_counties.columns.str.lower()\n",
    "pop_counties = gpd.GeoDataFrame(pop_counties, geometry='geometry')\n",
    "pop_counties = pop_counties.to_crs(epsg=4326)\n",
    "pop_counties_conus = pop_counties[~pop_counties['state_name'].isin(['Puerto Rico', 'Alaska', 'Hawaii'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_counties.to_file(\"output-data/population-demographics-county.geojson\", driver='GeoJSON')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import Data"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Import Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "07* export request made\n",
      "07* import request made\n",
      "08* export request made\n",
      "08* import request made\n",
      "10* export request made\n",
      "10* import request made\n",
      "11* export request made\n",
      "11* import request made\n",
      "20* export request made\n",
      "20* import request made\n"
     ]
    }
   ],
   "source": [
    "# This was run to get all food imported to and exported from the USA in 2017\n",
    "# This was saved to a csv file and read in at the beginning of this notebook.\n",
    "# To run this code change fetch_imports to True\n",
    "\n",
    "fetch_imports = True\n",
    "\n",
    "if fetch_imports == True:\n",
    "    import_url = 'https://api.census.gov/data/timeseries/intltrade/imports/porths?get=PORT,CTY_CODE,I_COMMODITY,I_COMMODITY_SDESC,GEN_VAL_MO,AIR_VAL_MO,AIR_WGT_MO,CNT_WGT_MO,CNT_VAL_MO&YEAR=2017&SUMMARY_LVL=DET&COMM_LVL=HS6&I_COMMODITY='\n",
    "    export_url = 'https://api.census.gov/data/timeseries/intltrade/exports/porths?get=PORT,CTY_CODE,E_COMMODITY,E_COMMODITY_SDESC,AIR_VAL_MO,AIR_WGT_MO,CNT_WGT_MO,CNT_VAL_MO&YEAR=2017&SUMMARY_LVL=DET&COMM_LVL=HS6&E_COMMODITY='\n",
    "    categories = ['07*', '08*', '10*', '11*', '20*']\n",
    "    food_imports = pd.DataFrame()\n",
    "    food_exports = pd.DataFrame()\n",
    "    for c in categories:\n",
    "        r = requests.get(export_url+c)\n",
    "        print (c + \" export request made\")\n",
    "        food_exports = pd.concat([food_exports, pd.DataFrame(r.json()[1:], columns=r.json()[0])])\n",
    "        r = requests.get(import_url+c)\n",
    "        print (c + \" import request made\")\n",
    "        food_imports = pd.concat([food_imports, pd.DataFrame(r.json()[1:], columns=r.json()[0])])\n",
    "else:\n",
    "    try:\n",
    "        food_imports = pd.read_csv(\"input-data/food-imports.csv\")\n",
    "        food_exports = pd.read_csv(\"input-data/food-exports.csv\")\n",
    "    except:\n",
    "        print (\"No file found. Try setting fetch_imports to True and run again.\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this cell adds total calories column, adds FDC ID column, and drops rows with no total calories\n",
    "# converts kg to kcal using the import commodities table\n",
    "def import_kg_to_kcal(commodity, kg):\n",
    "    return int(kg) * imports_crosswalk[imports_crosswalk['I_COMMODITY_SDESC'] == commodity]['kcal/kg'].values[0]\n",
    "def import_commodity_to_FDCID(commodity):\n",
    "    return imports_crosswalk[imports_crosswalk['I_COMMODITY_SDESC'] == commodity]['FDC ID'].values[0]\n",
    "def import_commodity_to_croptype(commodity):\n",
    "    return imports_crosswalk[imports_crosswalk['I_COMMODITY_SDESC'] == commodity]['crop_type'].values[0]\n",
    "\n",
    "# create a total_kg column of food_imports that is the sum of the CNT_WGT_MO column and the AIR_WGT_MO column\n",
    "food_imports['total_kg'] = food_imports['CNT_WGT_MO'] + food_imports['AIR_WGT_MO']\n",
    "# for each row in food_imports, call import_kg_to_kcal with the I_COMMODITY_SDESC and total_kg columns as arguments and assign the result to a new column called total_kcal\n",
    "food_imports['total_kcal'] = food_imports.apply(lambda row: import_kg_to_kcal(row['I_COMMODITY_SDESC'], row['total_kg']), axis=1)\n",
    "# for each row in food_imports, call import_commodity_to_croptype with the I_COMMODITY_SDESC and total_kg columns as arguments and assign the result to a new column called crop_type\n",
    "food_imports['crop_type'] = food_imports.apply(lambda row: import_commodity_to_croptype(row['I_COMMODITY_SDESC']), axis=1)\n",
    "# for each row in food_imports, call import_commodity_to_FDCID with the I_COMMODITY_SDESC and total_kg columns as arguments and assign the result to a new column called FDC ID\n",
    "food_imports['FDC ID'] = food_imports.apply(lambda row: import_commodity_to_FDCID(row['I_COMMODITY_SDESC']), axis=1)\n",
    "\n",
    "# Sometimes imports are only reported in value, not weight. this section drops those rows\n",
    "food_imports = food_imports.dropna(subset=['total_kcal'])\n",
    "\n",
    "food_imports.reset_index(inplace=True, drop=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This adds geocoordinates for each of the ports\n",
    "geolocator = Nominatim(user_agent=\"https://theplotline.org/\")\n",
    "for port in port_codes['Name']:\n",
    "    try:\n",
    "        location = geolocator.geocode(port)\n",
    "        lat = location.raw['lat']\n",
    "        lon = location.raw['lon']\n",
    "        port_codes.loc[port_codes['Name'] == port, 'lat'] = lat\n",
    "        port_codes.loc[port_codes['Name'] == port, 'lon'] = lon\n",
    "    except:\n",
    "        port_codes.loc[port_codes['Name'] == port, 'lat'] = np.nan\n",
    "        port_codes.loc[port_codes['Name'] == port, 'lon'] = np.nan\n",
    "\n",
    "# creating a geodataframe from port_codes dataframe\n",
    "port_codes_gdf = gpd.GeoDataFrame(port_codes, geometry=gpd.points_from_xy(port_codes['lon'], port_codes['lat']))\n",
    "port_codes_gdf = port_codes_gdf.set_crs(epsg=4326)\n",
    "# drop anything without a lat or lon\n",
    "port_codes_gdf = port_codes_gdf.dropna(subset=['lon'])\n",
    "# drop lat and lon as we have a geometry column now\n",
    "port_codes_gdf.drop(['lat', 'lon'], axis=1, inplace=True)\n",
    "port_codes_gdf.reset_index(inplace=True, drop=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reformatting port codes as strings rather than floats and ensuring all are in proper 4 digit format\n",
    "port_codes_gdf['Port Code'] = port_codes_gdf['Port Code'].astype(str)\n",
    "port_codes_gdf['Port Code'] = port_codes_gdf['Port Code'].astype(str).str.replace('.0', '')\n",
    "port_codes_gdf['Port Code'] = port_codes_gdf['Port Code'].apply(lambda x: '{0:0>4}'.format(x))\n",
    "port_codes_gdf['Port Code'] = port_codes_gdf['Port Code'].replace('0nan', np.nan)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/homebrew/Caskroom/miniforge/base/envs/geo/lib/python3.10/site-packages/IPython/core/interactiveshell.py:3445: FutureWarning: The `op` parameter is deprecated and will be removed in a future release. Please use the `predicate` parameter instead.\n",
      "  if await self.run_code(code, result, async_=asy):\n"
     ]
    }
   ],
   "source": [
    "# adding county names to port_codes_gdf\n",
    "port_codes_gdf = gpd.sjoin(port_codes_gdf, pop_counties[['geoid', 'geography', 'geographic_area_name', 'geometry']], how='left', op='within')\n",
    "port_codes_gdf.drop('index_right', axis=1, inplace=True)\n",
    "#writing to output file\n",
    "port_codes_gdf.to_file(\"output-data/us-port-locations.geojson\", driver='GeoJSON')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "# joining food imports and port codes. Note, port codes includes a geometry and county name at this point.\n",
    "food_imports = food_imports.merge(port_codes_gdf, left_on='PORT', right_on='Port Code', how='left')\n",
    "food_imports.drop('Port Code', axis=1, inplace=True)\n",
    "\n",
    "# drop all values with drop as crop_type\n",
    "food_imports = food_imports[food_imports['crop_type'] != 'drop']\n",
    "food_imports = food_imports.dropna(subset=['geoid'])\n",
    "\n",
    "# drop any values that are total_kg = 0\n",
    "food_imports = food_imports[food_imports['total_kg'] != 0]\n",
    "food_imports['geoid'] = food_imports['geoid'].astype(str).replace('\\.0$', '', regex=True)\n",
    "food_imports.reset_index(inplace=True, drop=True)\n",
    "\n",
    "food_imports.to_csv(\"output-data/food-imports-ports.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>PORT</th>\n",
       "      <th>CTY_CODE</th>\n",
       "      <th>I_COMMODITY_SDESC</th>\n",
       "      <th>GEN_VAL_MO</th>\n",
       "      <th>AIR_VAL_MO</th>\n",
       "      <th>AIR_WGT_MO</th>\n",
       "      <th>CNT_WGT_MO</th>\n",
       "      <th>CNT_VAL_MO</th>\n",
       "      <th>YEAR</th>\n",
       "      <th>SUMMARY_LVL</th>\n",
       "      <th>...</th>\n",
       "      <th>total_kg</th>\n",
       "      <th>total_kcal</th>\n",
       "      <th>crop_type</th>\n",
       "      <th>FDC ID</th>\n",
       "      <th>District Code</th>\n",
       "      <th>Name</th>\n",
       "      <th>geometry</th>\n",
       "      <th>geoid</th>\n",
       "      <th>geography</th>\n",
       "      <th>geographic_area_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0106</td>\n",
       "      <td>1220</td>\n",
       "      <td>POTATOES, EXCEPT SEED, FRESH OR CHILLED, NESOI</td>\n",
       "      <td>6384710</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2017</td>\n",
       "      <td>DET</td>\n",
       "      <td>...</td>\n",
       "      <td>00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Potatoes</td>\n",
       "      <td>2344876.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HOULTON, ME</td>\n",
       "      <td>POINT (-67.84079 46.12513)</td>\n",
       "      <td>23003</td>\n",
       "      <td>0500000US23003</td>\n",
       "      <td>Aroostook County, Maine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0108</td>\n",
       "      <td>1220</td>\n",
       "      <td>POTATOES, EXCEPT SEED, FRESH OR CHILLED, NESOI</td>\n",
       "      <td>79716</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2017</td>\n",
       "      <td>DET</td>\n",
       "      <td>...</td>\n",
       "      <td>00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Potatoes</td>\n",
       "      <td>2344876.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VAN BUREN, ME</td>\n",
       "      <td>POINT (-67.93533 47.15751)</td>\n",
       "      <td>23003</td>\n",
       "      <td>0500000US23003</td>\n",
       "      <td>Aroostook County, Maine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0115</td>\n",
       "      <td>1220</td>\n",
       "      <td>POTATOES, EXCEPT SEED, FRESH OR CHILLED, NESOI</td>\n",
       "      <td>766670</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2017</td>\n",
       "      <td>DET</td>\n",
       "      <td>...</td>\n",
       "      <td>00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Potatoes</td>\n",
       "      <td>2344876.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CALAIS, ME</td>\n",
       "      <td>POINT (-67.27814 45.18903)</td>\n",
       "      <td>23029</td>\n",
       "      <td>0500000US23029</td>\n",
       "      <td>Washington County, Maine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0127</td>\n",
       "      <td>1220</td>\n",
       "      <td>POTATOES, EXCEPT SEED, FRESH OR CHILLED, NESOI</td>\n",
       "      <td>486864</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2017</td>\n",
       "      <td>DET</td>\n",
       "      <td>...</td>\n",
       "      <td>00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Potatoes</td>\n",
       "      <td>2344876.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BRIDGEWATER, ME</td>\n",
       "      <td>POINT (-67.84401 46.42792)</td>\n",
       "      <td>23003</td>\n",
       "      <td>0500000US23003</td>\n",
       "      <td>Aroostook County, Maine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0209</td>\n",
       "      <td>1220</td>\n",
       "      <td>POTATOES, EXCEPT SEED, FRESH OR CHILLED, NESOI</td>\n",
       "      <td>184956</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2017</td>\n",
       "      <td>DET</td>\n",
       "      <td>...</td>\n",
       "      <td>00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Potatoes</td>\n",
       "      <td>2344876.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DERBY LINE, VT</td>\n",
       "      <td>POINT (-72.09963 45.00533)</td>\n",
       "      <td>50019</td>\n",
       "      <td>0500000US50019</td>\n",
       "      <td>Orleans County, Vermont</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75969</th>\n",
       "      <td>4909</td>\n",
       "      <td>3330</td>\n",
       "      <td>CRANBERRY JUICE, LINGONBERRY JUICE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2017</td>\n",
       "      <td>DET</td>\n",
       "      <td>...</td>\n",
       "      <td>00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Berries</td>\n",
       "      <td>2003594.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SAN JUAN, PR</td>\n",
       "      <td>POINT (-66.11667 18.46530)</td>\n",
       "      <td>72127</td>\n",
       "      <td>0500000US72127</td>\n",
       "      <td>San Juan Municipio, Puerto Rico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75970</th>\n",
       "      <td>5201</td>\n",
       "      <td>4280</td>\n",
       "      <td>CRANBERRY JUICE, LINGONBERRY JUICE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2017</td>\n",
       "      <td>DET</td>\n",
       "      <td>...</td>\n",
       "      <td>00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Berries</td>\n",
       "      <td>2003594.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MIAMI, FL</td>\n",
       "      <td>POINT (-80.19362 25.77417)</td>\n",
       "      <td>12086</td>\n",
       "      <td>0500000US12086</td>\n",
       "      <td>Miami-Dade County, Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75971</th>\n",
       "      <td>5201</td>\n",
       "      <td>4700</td>\n",
       "      <td>CRANBERRY JUICE, LINGONBERRY JUICE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2017</td>\n",
       "      <td>DET</td>\n",
       "      <td>...</td>\n",
       "      <td>00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Berries</td>\n",
       "      <td>2003594.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MIAMI, FL</td>\n",
       "      <td>POINT (-80.19362 25.77417)</td>\n",
       "      <td>12086</td>\n",
       "      <td>0500000US12086</td>\n",
       "      <td>Miami-Dade County, Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75972</th>\n",
       "      <td>5201</td>\n",
       "      <td>7290</td>\n",
       "      <td>CRANBERRY JUICE, LINGONBERRY JUICE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2017</td>\n",
       "      <td>DET</td>\n",
       "      <td>...</td>\n",
       "      <td>00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Berries</td>\n",
       "      <td>2003594.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>MIAMI, FL</td>\n",
       "      <td>POINT (-80.19362 25.77417)</td>\n",
       "      <td>12086</td>\n",
       "      <td>0500000US12086</td>\n",
       "      <td>Miami-Dade County, Florida</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75973</th>\n",
       "      <td>5203</td>\n",
       "      <td>3330</td>\n",
       "      <td>CRANBERRY JUICE, LINGONBERRY JUICE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>2017</td>\n",
       "      <td>DET</td>\n",
       "      <td>...</td>\n",
       "      <td>00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Berries</td>\n",
       "      <td>2003594.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>PORT EVERGLADES, FL</td>\n",
       "      <td>POINT (-80.12726 26.09685)</td>\n",
       "      <td>12011</td>\n",
       "      <td>0500000US12011</td>\n",
       "      <td>Broward County, Florida</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>75974 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       PORT CTY_CODE                               I_COMMODITY_SDESC   \n",
       "0      0106     1220  POTATOES, EXCEPT SEED, FRESH OR CHILLED, NESOI  \\\n",
       "1      0108     1220  POTATOES, EXCEPT SEED, FRESH OR CHILLED, NESOI   \n",
       "2      0115     1220  POTATOES, EXCEPT SEED, FRESH OR CHILLED, NESOI   \n",
       "3      0127     1220  POTATOES, EXCEPT SEED, FRESH OR CHILLED, NESOI   \n",
       "4      0209     1220  POTATOES, EXCEPT SEED, FRESH OR CHILLED, NESOI   \n",
       "...     ...      ...                                             ...   \n",
       "75969  4909     3330              CRANBERRY JUICE, LINGONBERRY JUICE   \n",
       "75970  5201     4280              CRANBERRY JUICE, LINGONBERRY JUICE   \n",
       "75971  5201     4700              CRANBERRY JUICE, LINGONBERRY JUICE   \n",
       "75972  5201     7290              CRANBERRY JUICE, LINGONBERRY JUICE   \n",
       "75973  5203     3330              CRANBERRY JUICE, LINGONBERRY JUICE   \n",
       "\n",
       "      GEN_VAL_MO AIR_VAL_MO AIR_WGT_MO CNT_WGT_MO CNT_VAL_MO  YEAR   \n",
       "0        6384710          0          0          0          0  2017  \\\n",
       "1          79716          0          0          0          0  2017   \n",
       "2         766670          0          0          0          0  2017   \n",
       "3         486864          0          0          0          0  2017   \n",
       "4         184956          0          0          0          0  2017   \n",
       "...          ...        ...        ...        ...        ...   ...   \n",
       "75969          0          0          0          0          0  2017   \n",
       "75970          0          0          0          0          0  2017   \n",
       "75971          0          0          0          0          0  2017   \n",
       "75972          0          0          0          0          0  2017   \n",
       "75973          0          0          0          0          0  2017   \n",
       "\n",
       "      SUMMARY_LVL  ... total_kg total_kcal  crop_type     FDC ID   \n",
       "0             DET  ...       00        0.0   Potatoes  2344876.0  \\\n",
       "1             DET  ...       00        0.0   Potatoes  2344876.0   \n",
       "2             DET  ...       00        0.0   Potatoes  2344876.0   \n",
       "3             DET  ...       00        0.0   Potatoes  2344876.0   \n",
       "4             DET  ...       00        0.0   Potatoes  2344876.0   \n",
       "...           ...  ...      ...        ...        ...        ...   \n",
       "75969         DET  ...       00        0.0    Berries  2003594.0   \n",
       "75970         DET  ...       00        0.0    Berries  2003594.0   \n",
       "75971         DET  ...       00        0.0    Berries  2003594.0   \n",
       "75972         DET  ...       00        0.0    Berries  2003594.0   \n",
       "75973         DET  ...       00        0.0    Berries  2003594.0   \n",
       "\n",
       "       District Code                 Name                    geometry  geoid   \n",
       "0                NaN          HOULTON, ME  POINT (-67.84079 46.12513)  23003  \\\n",
       "1                NaN        VAN BUREN, ME  POINT (-67.93533 47.15751)  23003   \n",
       "2                NaN           CALAIS, ME  POINT (-67.27814 45.18903)  23029   \n",
       "3                NaN      BRIDGEWATER, ME  POINT (-67.84401 46.42792)  23003   \n",
       "4                NaN       DERBY LINE, VT  POINT (-72.09963 45.00533)  50019   \n",
       "...              ...                  ...                         ...    ...   \n",
       "75969            NaN         SAN JUAN, PR  POINT (-66.11667 18.46530)  72127   \n",
       "75970            NaN            MIAMI, FL  POINT (-80.19362 25.77417)  12086   \n",
       "75971            NaN            MIAMI, FL  POINT (-80.19362 25.77417)  12086   \n",
       "75972            NaN            MIAMI, FL  POINT (-80.19362 25.77417)  12086   \n",
       "75973            NaN  PORT EVERGLADES, FL  POINT (-80.12726 26.09685)  12011   \n",
       "\n",
       "            geography             geographic_area_name  \n",
       "0      0500000US23003          Aroostook County, Maine  \n",
       "1      0500000US23003          Aroostook County, Maine  \n",
       "2      0500000US23029         Washington County, Maine  \n",
       "3      0500000US23003          Aroostook County, Maine  \n",
       "4      0500000US50019          Orleans County, Vermont  \n",
       "...               ...                              ...  \n",
       "75969  0500000US72127  San Juan Municipio, Puerto Rico  \n",
       "75970  0500000US12086       Miami-Dade County, Florida  \n",
       "75971  0500000US12086       Miami-Dade County, Florida  \n",
       "75972  0500000US12086       Miami-Dade County, Florida  \n",
       "75973  0500000US12011          Broward County, Florida  \n",
       "\n",
       "[75974 rows x 21 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "food_imports = food_imports.drop('I_COMMODITY', axis=1)\n",
    "food_imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "#convert food_imports to a geodataframe and write to file\n",
    "food_imports_gdf = gpd.GeoDataFrame(food_imports, geometry='geometry')\n",
    "food_imports_gdf = food_imports_gdf.set_crs(epsg=4326)\n",
    "food_imports_gdf.to_file(\"output-data/food-imports-ports.geojson\", driver='GeoJSON')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Integrating Import Data with Production Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Banana and Tropical fruit handler. Because none of this category is grown in the US we need to manually add these columns to the county_prod dataframe\n",
    "# adding a \"pixels_Bananas\" and \"pixels_Tropical fruits column to county_prod in case we want to use later\n",
    "county_pixels[\"pixels_Bananas\"] = 0\n",
    "county_pixels[\"kcal_Bananas\"] = 0\n",
    "county_pixels[\"kg_Bananas\"] = 0\n",
    "county_pixels[\"pixels_Tropical fruits\"] = 0\n",
    "county_pixels[\"kcal_Tropical fruits\"] = 0\n",
    "county_pixels[\"kg_Tropical fruits\"] = 0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 02240\n",
      "skipping 02240\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02240\n",
      "skipping 02020\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 02020\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72113\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 15003\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n",
      "skipping 72127\n"
     ]
    }
   ],
   "source": [
    "# doing this to make math work when adding pixels to NaN rows\n",
    "county_pixels = county_pixels.fillna(0)\n",
    "\n",
    "for r in food_imports.index:\n",
    "\n",
    "    county_row = county_pixels[county_pixels[\"geoid\"] == int(food_imports.loc[r, \"geoid\"])]\n",
    "    crop_type = food_imports.loc[r, \"crop_type\"]\n",
    "    crop_kg = int(food_imports.loc[r, \"total_kg\"])\n",
    "    crop_kcal = int(food_imports.loc[r, \"total_kcal\"])\n",
    "\n",
    "    # add crop_kg to the value in column of county_row that matches \"kg_\"+crop_type and kcal_crop_type. \n",
    "    # Rows we skip are non-CONUS geoids\n",
    "    try:\n",
    "        county_pixels.loc[county_pixels[\"geoid\"] == int(food_imports.loc[r, \"geoid\"]), \"kg_\"+crop_type] = county_row[\"kg_\"+crop_type].iloc[0] + int(crop_kg)\n",
    "        county_pixels.loc[county_pixels[\"geoid\"] == int(food_imports.loc[r, \"geoid\"]), \"kcal_\"+crop_type] = county_row[\"kcal_\"+crop_type].iloc[0] + int(crop_kcal)\n",
    "    except:\n",
    "        print (\"skipping \" + str(food_imports.loc[r, \"geoid\"]))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Scaling Production to Consumption"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/homebrew/Caskroom/miniforge/base/envs/geo/lib/python3.10/site-packages/geopandas/geodataframe.py:1443: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  super().__setitem__(key, value)\n"
     ]
    }
   ],
   "source": [
    "county_pixels[\"geoid\"] = county_pixels[\"geoid\"].astype(int)\n",
    "county_pixels = county_pixels.set_index(\"geoid\")\n",
    "pop_counties_conus[\"geoid\"] = pop_counties_conus[\"geoid\"].astype(int)\n",
    "pop_counties_conus = pop_counties_conus.set_index(\"geoid\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this adds consumption columns to pop_counties_conus\n",
    "# these columns are named in the following schema low_kg_consumed_crop type\n",
    "# for example low_kg_consumed_apples\n",
    "for c in income_consumption.columns:\n",
    "    low_consumption_kg = income_consumption[c][\"low_2017\"] * pop_counties_conus[\"income_low_income\"]\n",
    "    high_consumption_kg = income_consumption[c][\"high_2017\"] * pop_counties_conus[\"income_high_income\"]\n",
    "    other_consumption_kg = income_consumption[c][\"avg_2017\"] * pop_counties_conus[\"income_unknown_income\"]\n",
    "    pop_counties_conus[\"low_kg_consumed_\" + c] = low_consumption_kg\n",
    "    pop_counties_conus[\"high_kg_consumed_\" + c] = high_consumption_kg\n",
    "    pop_counties_conus[\"other_kg_consumed_\" + c] = other_consumption_kg\n",
    "    pop_counties_conus[\"total_kg_consumed_\" + c] = low_consumption_kg + high_consumption_kg + other_consumption_kg\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# This calculates a kcal/kg ratio and adds a column to pop_counties_conus with the number of kcal's consumed by income demographic\n",
    "for c in county_pixels.columns:\n",
    "    if c.startswith(\"kg_\"):\n",
    "        crop_name = c[3:]\n",
    "        kcal_kg_ratio = county_pixels[\"kcal_\" + crop_name].sum() / county_pixels[c].sum()\n",
    "        pop_counties_conus[\"low_kcal_consumed_\"+crop_name] = pop_counties_conus[\"low_kg_consumed_\" + crop_name] * kcal_kg_ratio\n",
    "        pop_counties_conus[\"high_kcal_consumed_\"+crop_name] = pop_counties_conus[\"high_kg_consumed_\" + crop_name] * kcal_kg_ratio\n",
    "        pop_counties_conus[\"other_kcal_consumed_\"+crop_name] = pop_counties_conus[\"other_kg_consumed_\" + crop_name] * kcal_kg_ratio\n",
    "        pop_counties_conus[\"total_kcal_consumed_\"+crop_name] = pop_counties_conus[\"total_kg_consumed_\" + crop_name] * kcal_kg_ratio"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "# write county_pixels to output csv\n",
    "county_pixels.to_csv('output-data/full_production_2017.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this creates a new dataframe with the values from county_prod scaled to match the total kcal consumed\n",
    "for c in county_pixels.columns:\n",
    "    if c.startswith(\"total_kcal_consumed_\"):\n",
    "        crop_name = c[20:]\n",
    "        crop_total_consumed = county_pixels[c].sum()\n",
    "        scale_ratio = crop_total_consumed / county_pixels[\"kcal_\" + crop_name].sum()\n",
    "        county_pixels[\"kcal_\" + crop_name] = county_pixels[\"kcal_\" + crop_name] * scale_ratio\n",
    "        county_pixels[\"kg_\" + crop_name] = county_pixels[\"kg_\" + crop_name] * scale_ratio\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this joins kg and kcal values from county_prod_scaled to pop_counties_conus on geoid\n",
    "pop_counties_conus = pop_counties_conus.join(county_pixels.filter(regex=\"^kg_|^kcal_\"))\n",
    "\n",
    "for c in pop_counties_conus.columns:\n",
    "    if c.startswith(\"kg_\"):\n",
    "        crop_name = c[3:]\n",
    "        pop_counties_conus = pop_counties_conus.rename(columns={c: \"kg_produced_\" + crop_name})\n",
    "    if c.startswith(\"kcal_\"):\n",
    "        crop_name = c[5:]\n",
    "        pop_counties_conus = pop_counties_conus.rename(columns={c: \"kcal_produced_\" + crop_name})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Corn flour numbers look good!\n",
      "Rice dried numbers look good!\n",
      "Peanuts numbers look good!\n",
      "Sweet corn numbers look good!\n",
      "Wheat flour numbers look good!\n",
      "Oat flour numbers look good!\n",
      "Potatoes numbers look good!\n",
      "Onions numbers look good!\n",
      "Cucumbers numbers look good!\n",
      "Green peas numbers look good!\n",
      "Tomatoes numbers look good!\n",
      "Apples numbers look good!\n",
      "Grapes numbers look good!\n",
      "Other citrus fruits numbers look good!\n",
      "Carrots numbers look good!\n",
      "Oranges numbers look good!\n",
      "Peppers numbers look good!\n",
      "Lettuce numbers look good!\n",
      "Cabbage numbers look good!\n",
      "Celery numbers look good!\n",
      "Radishes numbers look good!\n",
      "Tree nuts numbers look good!\n",
      "Other grain numbers look good!\n",
      "Broccoli and cauliflower numbers look good!\n",
      "Stone fruits numbers look good!\n",
      "Berries numbers look good!\n",
      "Legumes numbers look good!\n",
      "Melons numbers look good!\n",
      "Bananas numbers look good!\n",
      "Tropical fruits numbers look good!\n"
     ]
    }
   ],
   "source": [
    "# This is a double check on our data to make sure it's scaled correctly. Summed production and consumption should be the same\n",
    "for c in pop_counties_conus.columns:\n",
    "    if c.startswith(\"kcal_produced_\"):\n",
    "        dc_crop_name = c[14:]\n",
    "        double_check = pop_counties_conus[c].sum() - pop_counties_conus[\"kcal_produced_\"+dc_crop_name].sum()\n",
    "        if double_check == 0:\n",
    "            print (dc_crop_name + \" numbers look good!\")\n",
    "        else: \n",
    "            print (dc_crop_name + \" numbers don't match, difference is \" + str(double_check))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "pop_counties_conus.to_file(\"output-data/county-population-consumption-production-scaled.geojson\", driver=\"GeoJSON\")\n",
    "pop_counties_conus.to_csv(\"output-data/county-population-consumption-production-scaled.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "geo",
   "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.10.10"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}