{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Compare datasets\n", "Compares NASS county totals with values seen in the EWG dataset" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "%matplotlib inline" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### DEQ Data (pulled from online)" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "#Get the DEQ datset (online) and pivot so that animals are all in one column\n", "dfDEQ_raw = pd.read_excel('https://files.nc.gov/ncdeq/List_Permitted_Animal_Facilities2-12-2019.xls',skiprows=2)\n", "dfDEQ = dfDEQ_raw.pivot_table(index='County Name',\n", " columns='Regulated Operation',\n", " values='Allowable Count',\n", " aggfunc = 'sum').fillna(0)\n", "#Combine poultry\n", "dfDEQ['Poultry'] = dfDEQ['Dry Poultry'] + dfDEQ['Wet Poultry']\n", "#Drop unused columns\n", "dfDEQ.drop(['Horses','Other Animals ','Dry Poultry','Wet Poultry'],axis=1,inplace=True)\n", "#Rename columns\n", "dfDEQ.columns = ['DEQ_Cattle','DEQ_Hogs','DEQ_Poultry']" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### EWG Data (from local scraped copies)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "#Get the EWG Datasets\n", "dfEWG_Poultry = pd.read_csv('../data/raw/ScrapedEWG/EWG_Poultry.csv',usecols=['BIRD_COUNT','COUNTYNAME'])\n", "dfEWG_Swine = pd.read_csv('../data/raw/ScrapedEWG/EWG_Swine.csv',usecols=['ANIMAL_COUNT','COUNTYNAME'])\n", "dfEWG_Cattle = pd.read_csv('../data/raw/ScrapedEWG/EWG_Cattle.csv',usecols=['ANIMAL_COUNT','COUNTYNAME'])\n", "\n", "#Pivot each dataset on county\n", "dfEWG_P1 = dfEWG_Poultry.groupby('COUNTYNAME').agg({'BIRD_COUNT':'sum'})\n", "dfEWG_S1 = dfEWG_Swine.groupby('COUNTYNAME').agg({'ANIMAL_COUNT':'sum'})\n", "dfEWG_C1 = dfEWG_Cattle.groupby('COUNTYNAME').agg({'ANIMAL_COUNT':'sum'})" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", " | EWG_Poultry | \n", "EWG_Hogs | \n", "EWG_Cattle | \n", "
---|---|---|---|
COUNTYNAME | \n", "\n", " | \n", " | \n", " |
Alamance | \n", "929430.0 | \n", "600.0 | \n", "1590.0 | \n", "
Alexander | \n", "3694536.0 | \n", "NaN | \n", "650.0 | \n", "
Alleghany | \n", "14239.0 | \n", "NaN | \n", "2117.0 | \n", "
Anson | \n", "4284486.0 | \n", "46158.0 | \n", "700.0 | \n", "
Ashe | \n", "28478.0 | \n", "NaN | \n", "NaN | \n", "