notebooks/05 - Combining data sources.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Combining data sources\n",
    "\n",
    "Data can come from different files and you can combine them in a single dataframe.\n",
    "\n",
    "When working with data, storing data across multiple files or data sources is common. Combining data from different files or data sources into a single dataframe can be useful for data analysis and modelling. \n",
    "\n",
    "In Pandas, combining data from additional files or sources is done using the merge or join functions. These functions allow us to connect data based on a common key or index, which can be helpful when analyzing related data. \n",
    "\n",
    "This notebook will explore how to merge or join data from different files or sources into a single Pandas dataframe. We will also discuss different types of joins and merge operations and how to handle missing or duplicate data during the merging process."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How To"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"data/housing.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>housing_median_age</th>\n",
       "      <th>total_rooms</th>\n",
       "      <th>total_bedrooms</th>\n",
       "      <th>population</th>\n",
       "      <th>households</th>\n",
       "      <th>median_income</th>\n",
       "      <th>median_house_value</th>\n",
       "      <th>ocean_proximity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-122.23</td>\n",
       "      <td>37.88</td>\n",
       "      <td>41.0</td>\n",
       "      <td>880.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>322.0</td>\n",
       "      <td>126.0</td>\n",
       "      <td>8.3252</td>\n",
       "      <td>452600.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-122.22</td>\n",
       "      <td>37.86</td>\n",
       "      <td>21.0</td>\n",
       "      <td>7099.0</td>\n",
       "      <td>1106.0</td>\n",
       "      <td>2401.0</td>\n",
       "      <td>1138.0</td>\n",
       "      <td>8.3014</td>\n",
       "      <td>358500.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-122.24</td>\n",
       "      <td>37.85</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1467.0</td>\n",
       "      <td>190.0</td>\n",
       "      <td>496.0</td>\n",
       "      <td>177.0</td>\n",
       "      <td>7.2574</td>\n",
       "      <td>352100.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-122.25</td>\n",
       "      <td>37.85</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1274.0</td>\n",
       "      <td>235.0</td>\n",
       "      <td>558.0</td>\n",
       "      <td>219.0</td>\n",
       "      <td>5.6431</td>\n",
       "      <td>341300.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-122.25</td>\n",
       "      <td>37.85</td>\n",
       "      <td>52.0</td>\n",
       "      <td>1627.0</td>\n",
       "      <td>280.0</td>\n",
       "      <td>565.0</td>\n",
       "      <td>259.0</td>\n",
       "      <td>3.8462</td>\n",
       "      <td>342200.0</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   longitude  latitude  housing_median_age  total_rooms  total_bedrooms  \\\n",
       "0    -122.23     37.88                41.0        880.0           129.0   \n",
       "1    -122.22     37.86                21.0       7099.0          1106.0   \n",
       "2    -122.24     37.85                52.0       1467.0           190.0   \n",
       "3    -122.25     37.85                52.0       1274.0           235.0   \n",
       "4    -122.25     37.85                52.0       1627.0           280.0   \n",
       "\n",
       "   population  households  median_income  median_house_value ocean_proximity  \n",
       "0       322.0       126.0         8.3252            452600.0        NEAR BAY  \n",
       "1      2401.0      1138.0         8.3014            358500.0        NEAR BAY  \n",
       "2       496.0       177.0         7.2574            352100.0        NEAR BAY  \n",
       "3       558.0       219.0         5.6431            341300.0        NEAR BAY  \n",
       "4       565.0       259.0         3.8462            342200.0        NEAR BAY  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>ocean_proximity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37.88</td>\n",
       "      <td>-122.23</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>37.86</td>\n",
       "      <td>-122.22</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37.85</td>\n",
       "      <td>-122.24</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>37.85</td>\n",
       "      <td>-122.25</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37.85</td>\n",
       "      <td>-122.25</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   latitude  longitude ocean_proximity\n",
       "0     37.88    -122.23        NEAR BAY\n",
       "1     37.86    -122.22        NEAR BAY\n",
       "2     37.85    -122.24        NEAR BAY\n",
       "3     37.85    -122.25        NEAR BAY\n",
       "4     37.85    -122.25        NEAR BAY"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_geo = df[[\"latitude\", \"longitude\", \"ocean_proximity\"]]\n",
    "df_geo.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>ocean_proximity</th>\n",
       "      <th>median_house_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37.88</td>\n",
       "      <td>-122.23</td>\n",
       "      <td>NEAR BAY</td>\n",
       "      <td>452600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>37.86</td>\n",
       "      <td>-122.22</td>\n",
       "      <td>NEAR BAY</td>\n",
       "      <td>358500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37.85</td>\n",
       "      <td>-122.24</td>\n",
       "      <td>NEAR BAY</td>\n",
       "      <td>352100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>37.85</td>\n",
       "      <td>-122.25</td>\n",
       "      <td>NEAR BAY</td>\n",
       "      <td>341300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37.85</td>\n",
       "      <td>-122.25</td>\n",
       "      <td>NEAR BAY</td>\n",
       "      <td>342200.0</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>20635</th>\n",
       "      <td>39.48</td>\n",
       "      <td>-121.09</td>\n",
       "      <td>INLAND</td>\n",
       "      <td>78100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20636</th>\n",
       "      <td>39.49</td>\n",
       "      <td>-121.21</td>\n",
       "      <td>INLAND</td>\n",
       "      <td>77100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20637</th>\n",
       "      <td>39.43</td>\n",
       "      <td>-121.22</td>\n",
       "      <td>INLAND</td>\n",
       "      <td>92300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20638</th>\n",
       "      <td>39.43</td>\n",
       "      <td>-121.32</td>\n",
       "      <td>INLAND</td>\n",
       "      <td>84700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20639</th>\n",
       "      <td>39.37</td>\n",
       "      <td>-121.24</td>\n",
       "      <td>INLAND</td>\n",
       "      <td>89400.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20640 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       latitude  longitude ocean_proximity  median_house_value\n",
       "0         37.88    -122.23        NEAR BAY            452600.0\n",
       "1         37.86    -122.22        NEAR BAY            358500.0\n",
       "2         37.85    -122.24        NEAR BAY            352100.0\n",
       "3         37.85    -122.25        NEAR BAY            341300.0\n",
       "4         37.85    -122.25        NEAR BAY            342200.0\n",
       "...         ...        ...             ...                 ...\n",
       "20635     39.48    -121.09          INLAND             78100.0\n",
       "20636     39.49    -121.21          INLAND             77100.0\n",
       "20637     39.43    -121.22          INLAND             92300.0\n",
       "20638     39.43    -121.32          INLAND             84700.0\n",
       "20639     39.37    -121.24          INLAND             89400.0\n",
       "\n",
       "[20640 rows x 4 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_geo.join(df[\"median_house_value\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_price = df[[\"longitude\", \"latitude\", \"median_house_value\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>latitude</th>\n",
       "      <th>longitude_x</th>\n",
       "      <th>ocean_proximity</th>\n",
       "      <th>longitude_y</th>\n",
       "      <th>median_house_value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37.88</td>\n",
       "      <td>-122.23</td>\n",
       "      <td>NEAR BAY</td>\n",
       "      <td>-122.23</td>\n",
       "      <td>452600.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>37.88</td>\n",
       "      <td>-122.23</td>\n",
       "      <td>NEAR BAY</td>\n",
       "      <td>-122.34</td>\n",
       "      <td>350000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37.88</td>\n",
       "      <td>-122.23</td>\n",
       "      <td>NEAR BAY</td>\n",
       "      <td>-122.29</td>\n",
       "      <td>216700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>37.88</td>\n",
       "      <td>-122.23</td>\n",
       "      <td>NEAR BAY</td>\n",
       "      <td>-122.28</td>\n",
       "      <td>261300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37.88</td>\n",
       "      <td>-122.23</td>\n",
       "      <td>NEAR BAY</td>\n",
       "      <td>-122.26</td>\n",
       "      <td>391800.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1775707</th>\n",
       "      <td>35.96</td>\n",
       "      <td>-119.04</td>\n",
       "      <td>INLAND</td>\n",
       "      <td>-119.04</td>\n",
       "      <td>51700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1775708</th>\n",
       "      <td>35.86</td>\n",
       "      <td>-119.46</td>\n",
       "      <td>INLAND</td>\n",
       "      <td>-119.46</td>\n",
       "      <td>42700.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1775709</th>\n",
       "      <td>35.85</td>\n",
       "      <td>-119.12</td>\n",
       "      <td>INLAND</td>\n",
       "      <td>-119.12</td>\n",
       "      <td>58300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1775710</th>\n",
       "      <td>35.89</td>\n",
       "      <td>-119.27</td>\n",
       "      <td>INLAND</td>\n",
       "      <td>-119.27</td>\n",
       "      <td>53300.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1775711</th>\n",
       "      <td>35.88</td>\n",
       "      <td>-119.27</td>\n",
       "      <td>INLAND</td>\n",
       "      <td>-119.27</td>\n",
       "      <td>43700.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1775712 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         latitude  longitude_x ocean_proximity  longitude_y  \\\n",
       "0           37.88      -122.23        NEAR BAY      -122.23   \n",
       "1           37.88      -122.23        NEAR BAY      -122.34   \n",
       "2           37.88      -122.23        NEAR BAY      -122.29   \n",
       "3           37.88      -122.23        NEAR BAY      -122.28   \n",
       "4           37.88      -122.23        NEAR BAY      -122.26   \n",
       "...           ...          ...             ...          ...   \n",
       "1775707     35.96      -119.04          INLAND      -119.04   \n",
       "1775708     35.86      -119.46          INLAND      -119.46   \n",
       "1775709     35.85      -119.12          INLAND      -119.12   \n",
       "1775710     35.89      -119.27          INLAND      -119.27   \n",
       "1775711     35.88      -119.27          INLAND      -119.27   \n",
       "\n",
       "         median_house_value  \n",
       "0                  452600.0  \n",
       "1                  350000.0  \n",
       "2                  216700.0  \n",
       "3                  261300.0  \n",
       "4                  391800.0  \n",
       "...                     ...  \n",
       "1775707             51700.0  \n",
       "1775708             42700.0  \n",
       "1775709             58300.0  \n",
       "1775710             53300.0  \n",
       "1775711             43700.0  \n",
       "\n",
       "[1775712 rows x 5 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_geo.merge(df_price, left_on=\"latitude\", right_on=\"latitude\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>median_house_value</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>ocean_proximity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-122.23</td>\n",
       "      <td>37.88</td>\n",
       "      <td>452600.0</td>\n",
       "      <td>37.88</td>\n",
       "      <td>-122.23</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-122.22</td>\n",
       "      <td>37.86</td>\n",
       "      <td>358500.0</td>\n",
       "      <td>37.86</td>\n",
       "      <td>-122.22</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-122.24</td>\n",
       "      <td>37.85</td>\n",
       "      <td>352100.0</td>\n",
       "      <td>37.85</td>\n",
       "      <td>-122.24</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-122.25</td>\n",
       "      <td>37.85</td>\n",
       "      <td>341300.0</td>\n",
       "      <td>37.85</td>\n",
       "      <td>-122.25</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-122.25</td>\n",
       "      <td>37.85</td>\n",
       "      <td>342200.0</td>\n",
       "      <td>37.85</td>\n",
       "      <td>-122.25</td>\n",
       "      <td>NEAR BAY</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20635</th>\n",
       "      <td>-121.09</td>\n",
       "      <td>39.48</td>\n",
       "      <td>78100.0</td>\n",
       "      <td>39.48</td>\n",
       "      <td>-121.09</td>\n",
       "      <td>INLAND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20636</th>\n",
       "      <td>-121.21</td>\n",
       "      <td>39.49</td>\n",
       "      <td>77100.0</td>\n",
       "      <td>39.49</td>\n",
       "      <td>-121.21</td>\n",
       "      <td>INLAND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20637</th>\n",
       "      <td>-121.22</td>\n",
       "      <td>39.43</td>\n",
       "      <td>92300.0</td>\n",
       "      <td>39.43</td>\n",
       "      <td>-121.22</td>\n",
       "      <td>INLAND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20638</th>\n",
       "      <td>-121.32</td>\n",
       "      <td>39.43</td>\n",
       "      <td>84700.0</td>\n",
       "      <td>39.43</td>\n",
       "      <td>-121.32</td>\n",
       "      <td>INLAND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20639</th>\n",
       "      <td>-121.24</td>\n",
       "      <td>39.37</td>\n",
       "      <td>89400.0</td>\n",
       "      <td>39.37</td>\n",
       "      <td>-121.24</td>\n",
       "      <td>INLAND</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>20640 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       longitude  latitude  median_house_value  latitude  longitude  \\\n",
       "0        -122.23     37.88            452600.0     37.88    -122.23   \n",
       "1        -122.22     37.86            358500.0     37.86    -122.22   \n",
       "2        -122.24     37.85            352100.0     37.85    -122.24   \n",
       "3        -122.25     37.85            341300.0     37.85    -122.25   \n",
       "4        -122.25     37.85            342200.0     37.85    -122.25   \n",
       "...          ...       ...                 ...       ...        ...   \n",
       "20635    -121.09     39.48             78100.0     39.48    -121.09   \n",
       "20636    -121.21     39.49             77100.0     39.49    -121.21   \n",
       "20637    -121.22     39.43             92300.0     39.43    -121.22   \n",
       "20638    -121.32     39.43             84700.0     39.43    -121.32   \n",
       "20639    -121.24     39.37             89400.0     39.37    -121.24   \n",
       "\n",
       "      ocean_proximity  \n",
       "0            NEAR BAY  \n",
       "1            NEAR BAY  \n",
       "2            NEAR BAY  \n",
       "3            NEAR BAY  \n",
       "4            NEAR BAY  \n",
       "...               ...  \n",
       "20635          INLAND  \n",
       "20636          INLAND  \n",
       "20637          INLAND  \n",
       "20638          INLAND  \n",
       "20639          INLAND  \n",
       "\n",
       "[20640 rows x 6 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df_price, df_geo], join=\"inner\", axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>ocean_proximity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>37.88</td>\n",
       "      <td>-122.23</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>37.86</td>\n",
       "      <td>-122.22</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37.85</td>\n",
       "      <td>-122.24</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>37.85</td>\n",
       "      <td>-122.25</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37.85</td>\n",
       "      <td>-122.25</td>\n",
       "      <td>NEAR BAY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13619</th>\n",
       "      <td>34.13</td>\n",
       "      <td>-117.25</td>\n",
       "      <td>INLAND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16169</th>\n",
       "      <td>37.79</td>\n",
       "      <td>-122.55</td>\n",
       "      <td>NEAR OCEAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19726</th>\n",
       "      <td>39.07</td>\n",
       "      <td>-121.70</td>\n",
       "      <td>INLAND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9337</th>\n",
       "      <td>37.97</td>\n",
       "      <td>-122.59</td>\n",
       "      <td>NEAR OCEAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4788</th>\n",
       "      <td>34.02</td>\n",
       "      <td>-118.32</td>\n",
       "      <td>&lt;1H OCEAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>21640 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       latitude  longitude ocean_proximity\n",
       "0         37.88    -122.23        NEAR BAY\n",
       "1         37.86    -122.22        NEAR BAY\n",
       "2         37.85    -122.24        NEAR BAY\n",
       "3         37.85    -122.25        NEAR BAY\n",
       "4         37.85    -122.25        NEAR BAY\n",
       "...         ...        ...             ...\n",
       "13619     34.13    -117.25          INLAND\n",
       "16169     37.79    -122.55      NEAR OCEAN\n",
       "19726     39.07    -121.70          INLAND\n",
       "9337      37.97    -122.59      NEAR OCEAN\n",
       "4788      34.02    -118.32       <1H OCEAN\n",
       "\n",
       "[21640 rows x 3 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_geo.append(df_geo.sample(1000))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise\n",
    "Familiarize yourself with the merge operations and try how `inner`, `left`, `right`, and `outer` change the merge results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.concat([df_price, df_geo], join=\"inner\", axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Additional Resources"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- [Merge, join, concatenate and compare](https://pandas.pydata.org/pandas-docs/stable/user_guide/merging.html)"
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}