TableJoin.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set the address of the host table (or the left table)\n",
    "addressOfOurBG = r\"C:\\MyFiles\\Dropbox\\Teaching\\Urban_Data_Analsysis\\Fall 2020\\Data\\NYC_BlockGroups\\NYCBlockGroups.csv\"\n",
    "# set the address of the guest table (or the right table)\n",
    "addressOfNewTable = r\"C:\\MyFiles\\Dropbox\\Teaching\\GIS_Intro\\Fall2020\\Layers\\NYC\\Census\\nhgis0068_csv\\nhgis0068_csv\\nhgis0068_ds239_20185_2018_blck_grp_education_data.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read the csv as a dataframe\n",
    "df_my_bg = pd.read_csv(addressOfOurBG)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_my_bg.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read the csv as a dataframe\n",
    "df_new_cols = pd.read_csv(addressOfNewTable)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_new_cols.sample(4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Join two dataframes using pd.merge function. Use the following resources.\n",
    "https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html\n",
    "\n",
    "https://datacarpentry.org/python-ecology-lesson/05-merging-data/index.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this function joins the right table to the left table. To understand the arguments here refer to the user guide of Pandas.\n",
    "# Also notice that here we defined the key columns in each table. 'GISJOIN' is the key (matching) column in both tables\n",
    "df_joined = pd.merge(left=df_my_bg, right=df_new_cols, how='left', left_on='GISJOIN', right_on='GISJOIN')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_joined.sample(4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['GISJOIN', 'YEAR', 'Median_age', 'Population', 'WhiteAlone',\n",
       "       'BlackAlone', 'NativeAlone', 'BlackAlonWithComb', 'Hisp_Latino',\n",
       "       'MedHouseholdIncome', 'PerCapitaIncome', 'MedYearBuilt',\n",
       "       'MedianHousingValue', 'PctNonWhite', 'PctBlackOnly', 'PctHisLatOnly',\n",
       "       'HsngUnits', 'HsngDenPerHac', 'pop_65above', 'Fem_65Above',\n",
       "       'Commute30Over', 'commute60Over', 'commutePtransitOver60',\n",
       "       'commuteWalkBike', 'LivingAloneOver65', 'LessThanHighSchool',\n",
       "       'TotPopulation', 'pct_fem65Over', 'pct_popOver65', 'pctCommute60Over',\n",
       "       'pctCommutePTnsitOver60', 'pctCommuteWalkBike', 'pct_LivingAlone65Over',\n",
       "       'pct_LessThanHighSchool', 'pctMobileHomes', 'Area_sqm'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_my_bg.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['GISJOIN', 'YEAR', 'REGIONA', 'DIVISIONA', 'STATE', 'STATEA', 'COUNTY',\n",
       "       'COUNTYA', 'COUSUBA', 'PLACEA', 'TRACTA', 'BLKGRPA', 'NAME_E',\n",
       "       'Total_population', 'pop_25Over', 'edLessCollege', 'edCollege',\n",
       "       'edAbovCollege'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_new_cols.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['GISJOIN', 'YEAR_x', 'Median_age', 'Population', 'WhiteAlone',\n",
       "       'BlackAlone', 'NativeAlone', 'BlackAlonWithComb', 'Hisp_Latino',\n",
       "       'MedHouseholdIncome', 'PerCapitaIncome', 'MedYearBuilt',\n",
       "       'MedianHousingValue', 'PctNonWhite', 'PctBlackOnly', 'PctHisLatOnly',\n",
       "       'HsngUnits', 'HsngDenPerHac', 'pop_65above', 'Fem_65Above',\n",
       "       'Commute30Over', 'commute60Over', 'commutePtransitOver60',\n",
       "       'commuteWalkBike', 'LivingAloneOver65', 'LessThanHighSchool',\n",
       "       'TotPopulation', 'pct_fem65Over', 'pct_popOver65', 'pctCommute60Over',\n",
       "       'pctCommutePTnsitOver60', 'pctCommuteWalkBike', 'pct_LivingAlone65Over',\n",
       "       'pct_LessThanHighSchool', 'pctMobileHomes', 'Area_sqm', 'YEAR_y',\n",
       "       'REGIONA', 'DIVISIONA', 'STATE', 'STATEA', 'COUNTY', 'COUNTYA',\n",
       "       'COUSUBA', 'PLACEA', 'TRACTA', 'BLKGRPA', 'NAME_E', 'Total_population',\n",
       "       'pop_25Over', 'edLessCollege', 'edCollege', 'edAbovCollege'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_joined.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6278"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_my_bg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15463"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_new_cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6278"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_joined)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_joined.to_csv(r\"C:\\MyFiles\\Dropbox\\Teaching\\Urban_Data_Analsysis\\Fall 2020\\Data\\NYC_BlockGroups\\NYCBlockGroups_joined_with_educationData.csv\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}