Pandas_Join_Merge-checkpoint.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d703cd9f",
   "metadata": {},
   "source": [
    "# Pandas: Joining and Merging DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "eb7a7f35",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "00118dd5",
   "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>student_id</th>\n",
       "      <th>name</th>\n",
       "      <th>marks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S1</td>\n",
       "      <td>Danniella Fenton</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S2</td>\n",
       "      <td>Ryder Storey</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S3</td>\n",
       "      <td>Bryce Jensen</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S4</td>\n",
       "      <td>Ed Bernal</td>\n",
       "      <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S5</td>\n",
       "      <td>Kwame Morin</td>\n",
       "      <td>199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S4</td>\n",
       "      <td>Scarlette Fisher</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S5</td>\n",
       "      <td>Carla Williamson</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S6</td>\n",
       "      <td>Dante Morse</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S7</td>\n",
       "      <td>Kaiser William</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S8</td>\n",
       "      <td>Madeeha Preston</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student_id              name  marks\n",
       "0         S1  Danniella Fenton    200\n",
       "1         S2      Ryder Storey    210\n",
       "2         S3      Bryce Jensen    190\n",
       "3         S4         Ed Bernal    222\n",
       "4         S5       Kwame Morin    199\n",
       "0         S4  Scarlette Fisher    201\n",
       "1         S5  Carla Williamson    200\n",
       "2         S6       Dante Morse    198\n",
       "3         S7    Kaiser William    219\n",
       "4         S8   Madeeha Preston    201"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q1\n",
    "student_data1 = pd.DataFrame({'student_id': ['S1', 'S2', 'S3', 'S4', 'S5'],\n",
    "         'name': ['Danniella Fenton', 'Ryder Storey', 'Bryce Jensen', 'Ed Bernal', 'Kwame Morin'], \n",
    "        'marks': [200, 210, 190, 222, 199]})\n",
    "\n",
    "student_data2 = pd.DataFrame({'student_id': ['S4', 'S5', 'S6', 'S7', 'S8'],\n",
    "        'name': ['Scarlette Fisher', 'Carla Williamson', 'Dante Morse', 'Kaiser William', 'Madeeha Preston'], \n",
    "        'marks': [201, 200, 198, 219, 201]})\n",
    "\n",
    "jd = pd.concat([student_data1, student_data2])\n",
    "jd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "e8c7c5d0",
   "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>student_id</th>\n",
       "      <th>name</th>\n",
       "      <th>marks</th>\n",
       "      <th>student_id</th>\n",
       "      <th>name</th>\n",
       "      <th>marks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S1</td>\n",
       "      <td>Danniella Fenton</td>\n",
       "      <td>200</td>\n",
       "      <td>S4</td>\n",
       "      <td>Scarlette Fisher</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S2</td>\n",
       "      <td>Ryder Storey</td>\n",
       "      <td>210</td>\n",
       "      <td>S5</td>\n",
       "      <td>Carla Williamson</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S3</td>\n",
       "      <td>Bryce Jensen</td>\n",
       "      <td>190</td>\n",
       "      <td>S6</td>\n",
       "      <td>Dante Morse</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S4</td>\n",
       "      <td>Ed Bernal</td>\n",
       "      <td>222</td>\n",
       "      <td>S7</td>\n",
       "      <td>Kaiser William</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S5</td>\n",
       "      <td>Kwame Morin</td>\n",
       "      <td>199</td>\n",
       "      <td>S8</td>\n",
       "      <td>Madeeha Preston</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student_id              name  marks student_id              name  marks\n",
       "0         S1  Danniella Fenton    200         S4  Scarlette Fisher    201\n",
       "1         S2      Ryder Storey    210         S5  Carla Williamson    200\n",
       "2         S3      Bryce Jensen    190         S6       Dante Morse    198\n",
       "3         S4         Ed Bernal    222         S7    Kaiser William    219\n",
       "4         S5       Kwame Morin    199         S8   Madeeha Preston    201"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q2\n",
    "col_joint = pd.concat([student_data1, student_data2], axis=1)\n",
    "col_joint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "629c5eaf",
   "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>student_id</th>\n",
       "      <th>name</th>\n",
       "      <th>marks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S1</td>\n",
       "      <td>Danniella Fenton</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S2</td>\n",
       "      <td>Ryder Storey</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S3</td>\n",
       "      <td>Bryce Jensen</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S4</td>\n",
       "      <td>Ed Bernal</td>\n",
       "      <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S5</td>\n",
       "      <td>Kwame Morin</td>\n",
       "      <td>199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S6</td>\n",
       "      <td>Scarlette Fisher</td>\n",
       "      <td>205</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student_id              name  marks\n",
       "0         S1  Danniella Fenton    200\n",
       "1         S2      Ryder Storey    210\n",
       "2         S3      Bryce Jensen    190\n",
       "3         S4         Ed Bernal    222\n",
       "4         S5       Kwame Morin    199\n",
       "5         S6  Scarlette Fisher    205"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q3\n",
    "new_row = pd.Series(['S6', 'Scarlette Fisher', 205], index=['student_id', 'name', 'marks'])\n",
    "add_data = student_data1.append(new_row, ignore_index= True)\n",
    "add_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "782279da",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(student_data1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d0b71413",
   "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>student_id</th>\n",
       "      <th>name</th>\n",
       "      <th>marks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S1</td>\n",
       "      <td>Danniella Fenton</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S2</td>\n",
       "      <td>Ryder Storey</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S3</td>\n",
       "      <td>Bryce Jensen</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S4</td>\n",
       "      <td>Ed Bernal</td>\n",
       "      <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S5</td>\n",
       "      <td>Kwame Morin</td>\n",
       "      <td>199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S6</td>\n",
       "      <td>Scarlette Fisher</td>\n",
       "      <td>203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S7</td>\n",
       "      <td>Bryce Jensen</td>\n",
       "      <td>207</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student_id              name  marks\n",
       "0         S1  Danniella Fenton    200\n",
       "1         S2      Ryder Storey    210\n",
       "2         S3      Bryce Jensen    190\n",
       "3         S4         Ed Bernal    222\n",
       "4         S5       Kwame Morin    199\n",
       "5         S6  Scarlette Fisher    203\n",
       "6         S7      Bryce Jensen    207"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q4\n",
    "dicts = [{'student_id': 'S6', 'name': 'Scarlette Fisher', 'marks': 203},\n",
    "         {'student_id': 'S7', 'name': 'Bryce Jensen', 'marks': 207}]\n",
    "combined_data = student_data1.append(dicts, ignore_index=True, sort=False)\n",
    "combined_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3a15ff1b",
   "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>student_id</th>\n",
       "      <th>name</th>\n",
       "      <th>marks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S1</td>\n",
       "      <td>Danniella Fenton</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S2</td>\n",
       "      <td>Ryder Storey</td>\n",
       "      <td>210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S3</td>\n",
       "      <td>Bryce Jensen</td>\n",
       "      <td>190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S4</td>\n",
       "      <td>Ed Bernal</td>\n",
       "      <td>222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S5</td>\n",
       "      <td>Kwame Morin</td>\n",
       "      <td>199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S4</td>\n",
       "      <td>Scarlette Fisher</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S5</td>\n",
       "      <td>Carla Williamson</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S6</td>\n",
       "      <td>Dante Morse</td>\n",
       "      <td>198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S7</td>\n",
       "      <td>Kaiser William</td>\n",
       "      <td>219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S8</td>\n",
       "      <td>Madeeha Preston</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student_id              name  marks\n",
       "0         S1  Danniella Fenton    200\n",
       "1         S2      Ryder Storey    210\n",
       "2         S3      Bryce Jensen    190\n",
       "3         S4         Ed Bernal    222\n",
       "4         S5       Kwame Morin    199\n",
       "0         S4  Scarlette Fisher    201\n",
       "1         S5  Carla Williamson    200\n",
       "2         S6       Dante Morse    198\n",
       "3         S7    Kaiser William    219\n",
       "4         S8   Madeeha Preston    201"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#q5\n",
    "exam_data = pd.DataFrame({'student_id': ['S1', 'S2', 'S3', 'S4', 'S5', 'S7', 'S8', 'S9', 'S10', 'S11', 'S12', 'S13'],\n",
    "        'exam_id': [23, 45, 12, 67, 21, 55, 33, 14, 56, 83, 88, 12]})\n",
    "results = pd.concat([student_data1, student_data2]) \n",
    "results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "46c0eb9d",
   "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>student_id</th>\n",
       "      <th>name</th>\n",
       "      <th>marks</th>\n",
       "      <th>exam_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S1</td>\n",
       "      <td>Danniella Fenton</td>\n",
       "      <td>200</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S2</td>\n",
       "      <td>Ryder Storey</td>\n",
       "      <td>210</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S3</td>\n",
       "      <td>Bryce Jensen</td>\n",
       "      <td>190</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S4</td>\n",
       "      <td>Ed Bernal</td>\n",
       "      <td>222</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S4</td>\n",
       "      <td>Scarlette Fisher</td>\n",
       "      <td>201</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S5</td>\n",
       "      <td>Kwame Morin</td>\n",
       "      <td>199</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S5</td>\n",
       "      <td>Carla Williamson</td>\n",
       "      <td>200</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>S7</td>\n",
       "      <td>Kaiser William</td>\n",
       "      <td>219</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>S8</td>\n",
       "      <td>Madeeha Preston</td>\n",
       "      <td>201</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student_id              name  marks  exam_id\n",
       "0         S1  Danniella Fenton    200       23\n",
       "1         S2      Ryder Storey    210       45\n",
       "2         S3      Bryce Jensen    190       12\n",
       "3         S4         Ed Bernal    222       67\n",
       "4         S4  Scarlette Fisher    201       67\n",
       "5         S5       Kwame Morin    199       21\n",
       "6         S5  Carla Williamson    200       21\n",
       "7         S7    Kaiser William    219       55\n",
       "8         S8   Madeeha Preston    201       33"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_data = pd.merge(results, exam_data, on='student_id')\n",
    "final_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "05a42ae4",
   "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>student_id</th>\n",
       "      <th>name_x</th>\n",
       "      <th>marks_x</th>\n",
       "      <th>name_y</th>\n",
       "      <th>marks_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S4</td>\n",
       "      <td>Ed Bernal</td>\n",
       "      <td>222</td>\n",
       "      <td>Scarlette Fisher</td>\n",
       "      <td>201</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S5</td>\n",
       "      <td>Kwame Morin</td>\n",
       "      <td>199</td>\n",
       "      <td>Carla Williamson</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student_id       name_x  marks_x            name_y  marks_y\n",
       "0         S4    Ed Bernal      222  Scarlette Fisher      201\n",
       "1         S5  Kwame Morin      199  Carla Williamson      200"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q6 inner join\n",
    "common_col = pd.merge(student_data1, student_data2, on='student_id', how=\"inner\")\n",
    "common_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "5f593e5f",
   "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>student_id</th>\n",
       "      <th>name_x</th>\n",
       "      <th>marks_x</th>\n",
       "      <th>name_y</th>\n",
       "      <th>marks_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S1</td>\n",
       "      <td>Danniella Fenton</td>\n",
       "      <td>200.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S2</td>\n",
       "      <td>Ryder Storey</td>\n",
       "      <td>210.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S3</td>\n",
       "      <td>Bryce Jensen</td>\n",
       "      <td>190.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S4</td>\n",
       "      <td>Ed Bernal</td>\n",
       "      <td>222.0</td>\n",
       "      <td>Scarlette Fisher</td>\n",
       "      <td>201.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S5</td>\n",
       "      <td>Kwame Morin</td>\n",
       "      <td>199.0</td>\n",
       "      <td>Carla Williamson</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Dante Morse</td>\n",
       "      <td>198.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kaiser William</td>\n",
       "      <td>219.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>S8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Madeeha Preston</td>\n",
       "      <td>201.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student_id            name_x  marks_x            name_y  marks_y\n",
       "0         S1  Danniella Fenton    200.0               NaN      NaN\n",
       "1         S2      Ryder Storey    210.0               NaN      NaN\n",
       "2         S3      Bryce Jensen    190.0               NaN      NaN\n",
       "3         S4         Ed Bernal    222.0  Scarlette Fisher    201.0\n",
       "4         S5       Kwame Morin    199.0  Carla Williamson    200.0\n",
       "5         S6               NaN      NaN       Dante Morse    198.0\n",
       "6         S7               NaN      NaN    Kaiser William    219.0\n",
       "7         S8               NaN      NaN   Madeeha Preston    201.0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q7 outer join\n",
    "match_record = pd.merge(student_data1, student_data2, on='student_id', how='outer')\n",
    "match_record"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9acbb9a9",
   "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>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>P</th>\n",
       "      <th>Q</th>\n",
       "      <th>R</th>\n",
       "      <th>S</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>P0</td>\n",
       "      <td>Q0</td>\n",
       "      <td>R0</td>\n",
       "      <td>S0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K0</td>\n",
       "      <td>K1</td>\n",
       "      <td>P1</td>\n",
       "      <td>Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>P2</td>\n",
       "      <td>Q2</td>\n",
       "      <td>R1</td>\n",
       "      <td>S1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>P2</td>\n",
       "      <td>Q2</td>\n",
       "      <td>R2</td>\n",
       "      <td>S2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>K2</td>\n",
       "      <td>K1</td>\n",
       "      <td>P3</td>\n",
       "      <td>Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2   P   Q    R    S\n",
       "0   K0   K0  P0  Q0   R0   S0\n",
       "1   K0   K1  P1  Q1  NaN  NaN\n",
       "2   K1   K0  P2  Q2   R1   S1\n",
       "3   K1   K0  P2  Q2   R2   S2\n",
       "4   K2   K1  P3  Q3  NaN  NaN"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q8 left join\n",
    "data1 = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],\n",
    "                     'key2': ['K0', 'K1', 'K0', 'K1'],\n",
    "                     'P': ['P0', 'P1', 'P2', 'P3'],\n",
    "                     'Q': ['Q0', 'Q1', 'Q2', 'Q3']}) \n",
    "data2 = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],\n",
    "                      'key2': ['K0', 'K0', 'K0', 'K0'],\n",
    "                      'R': ['R0', 'R1', 'R2', 'R3'],\n",
    "                      'S': ['S0', 'S1', 'S2', 'S3']})\n",
    "left_join = pd.merge(data1, data2, on=['key1', 'key2'], how='left') \n",
    "left_join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "1aa8a043",
   "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>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>P</th>\n",
       "      <th>Q</th>\n",
       "      <th>R</th>\n",
       "      <th>S</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>P0</td>\n",
       "      <td>Q0</td>\n",
       "      <td>R0</td>\n",
       "      <td>S0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>P2</td>\n",
       "      <td>Q2</td>\n",
       "      <td>R1</td>\n",
       "      <td>S1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>P2</td>\n",
       "      <td>Q2</td>\n",
       "      <td>R2</td>\n",
       "      <td>S2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K2</td>\n",
       "      <td>K0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R3</td>\n",
       "      <td>S3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2    P    Q   R   S\n",
       "0   K0   K0   P0   Q0  R0  S0\n",
       "1   K1   K0   P2   Q2  R1  S1\n",
       "2   K1   K0   P2   Q2  R2  S2\n",
       "3   K2   K0  NaN  NaN  R3  S3"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q9 right df keys join\n",
    "right_join = pd.merge(data1, data2, on=['key1', 'key2'], how='right')\n",
    "right_join"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "ea9ff347",
   "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>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>P</th>\n",
       "      <th>Q</th>\n",
       "      <th>R</th>\n",
       "      <th>S</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>P0</td>\n",
       "      <td>Q0</td>\n",
       "      <td>R0</td>\n",
       "      <td>S0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>P2</td>\n",
       "      <td>Q2</td>\n",
       "      <td>R1</td>\n",
       "      <td>S1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>P2</td>\n",
       "      <td>Q2</td>\n",
       "      <td>R2</td>\n",
       "      <td>S2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2   P   Q   R   S\n",
       "0   K0   K0  P0  Q0  R0  S0\n",
       "1   K1   K0  P2  Q2  R1  S1\n",
       "2   K1   K0  P2  Q2  R2  S2"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q10 multiple join keys\n",
    "merge_data = pd.merge(data1, data2, on=['key1', 'key2'])\n",
    "merge_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "cbb8b3f0",
   "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>column1</th>\n",
       "      <th>column2</th>\n",
       "      <th>column3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   column1  column2  column3\n",
       "0        0        0        0\n",
       "1        1        1        1\n",
       "2        2        2        4\n",
       "3        3        3        5"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q11\n",
    "s1 = pd.Series([0, 1, 2, 3], name='col1')\n",
    "s2 = pd.Series([0, 1, 2, 3])\n",
    "s3 = pd.Series([0, 1, 4, 5], name='col3')\n",
    "df = pd.concat([s1, s2, s3], axis=1, keys=['column1', 'column2', 'column3'])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "c9984914",
   "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>key1</th>\n",
       "      <th>key2_x</th>\n",
       "      <th>P</th>\n",
       "      <th>Q</th>\n",
       "      <th>key2_y</th>\n",
       "      <th>R</th>\n",
       "      <th>S</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>P0</td>\n",
       "      <td>Q0</td>\n",
       "      <td>K0</td>\n",
       "      <td>R0</td>\n",
       "      <td>S0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K0</td>\n",
       "      <td>K1</td>\n",
       "      <td>P1</td>\n",
       "      <td>Q1</td>\n",
       "      <td>K0</td>\n",
       "      <td>R0</td>\n",
       "      <td>S0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>P2</td>\n",
       "      <td>Q2</td>\n",
       "      <td>K0</td>\n",
       "      <td>R1</td>\n",
       "      <td>S1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>P2</td>\n",
       "      <td>Q2</td>\n",
       "      <td>K0</td>\n",
       "      <td>R2</td>\n",
       "      <td>S2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>K2</td>\n",
       "      <td>K1</td>\n",
       "      <td>P3</td>\n",
       "      <td>Q3</td>\n",
       "      <td>K0</td>\n",
       "      <td>R3</td>\n",
       "      <td>S3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2_x   P   Q key2_y   R   S\n",
       "0   K0     K0  P0  Q0     K0  R0  S0\n",
       "1   K0     K1  P1  Q1     K0  R0  S0\n",
       "2   K1     K0  P2  Q2     K0  R1  S1\n",
       "3   K1     K0  P2  Q2     K0  R2  S2\n",
       "4   K2     K1  P3  Q3     K0  R3  S3"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q12 many-to-many join case\n",
    "mg = pd.merge(data1, data2, on='key1')\n",
    "mg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "7e7f2e04",
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>K0</th>\n",
       "      <td>A0</td>\n",
       "      <td>B0</td>\n",
       "      <td>C0</td>\n",
       "      <td>D0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K1</th>\n",
       "      <td>A1</td>\n",
       "      <td>B1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>K2</th>\n",
       "      <td>A2</td>\n",
       "      <td>B2</td>\n",
       "      <td>C2</td>\n",
       "      <td>D2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A   B    C    D\n",
       "K0  A0  B0   C0   D0\n",
       "K1  A1  B1  NaN  NaN\n",
       "K2  A2  B2   C2   D2"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q13\n",
    "data1 = pd.DataFrame({'A': ['A0', 'A1', 'A2'],\n",
    "                      'B': ['B0', 'B1', 'B2']},\n",
    "                     index=['K0', 'K1', 'K2'])\n",
    "\n",
    "data2 = pd.DataFrame({'C': ['C0', 'C2', 'C3'],\n",
    "                      'D': ['D0', 'D2', 'D3']},\n",
    "                     index=['K0', 'K2', 'K3'])\n",
    "table = data1.join(data2)\n",
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "c9fb25f2",
   "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>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th>P</th>\n",
       "      <th>Q</th>\n",
       "      <th>R</th>\n",
       "      <th>S</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>P0</td>\n",
       "      <td>Q0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>K0</td>\n",
       "      <td>K1</td>\n",
       "      <td>P1</td>\n",
       "      <td>Q1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>P2</td>\n",
       "      <td>Q2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>K2</td>\n",
       "      <td>K1</td>\n",
       "      <td>P3</td>\n",
       "      <td>Q3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>K0</td>\n",
       "      <td>K0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R0</td>\n",
       "      <td>S0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R1</td>\n",
       "      <td>S1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>K1</td>\n",
       "      <td>K0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R2</td>\n",
       "      <td>S2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>K2</td>\n",
       "      <td>K0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R3</td>\n",
       "      <td>S3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2    P    Q    R    S\n",
       "0   K0   K0   P0   Q0  NaN  NaN\n",
       "1   K0   K1   P1   Q1  NaN  NaN\n",
       "2   K1   K0   P2   Q2  NaN  NaN\n",
       "3   K2   K1   P3   Q3  NaN  NaN\n",
       "4   K0   K0  NaN  NaN   R0   S0\n",
       "5   K1   K0  NaN  NaN   R1   S1\n",
       "6   K1   K0  NaN  NaN   R2   S2\n",
       "7   K2   K0  NaN  NaN   R3   S3"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q14\n",
    "data1 = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],\n",
    "                     'key2': ['K0', 'K1', 'K0', 'K1'],\n",
    "                     'P': ['P0', 'P1', 'P2', 'P3'],\n",
    "                     'Q': ['Q0', 'Q1', 'Q2', 'Q3']}) \n",
    "data2 = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],\n",
    "                      'key2': ['K0', 'K0', 'K0', 'K0'],\n",
    "                      'R': ['R0', 'R1', 'R2', 'R3'],\n",
    "                      'S': ['S0', 'S1', 'S2', 'S3']})\n",
    "reqd = pd.concat([data1, data2], axis=0, ignore_index=True)\n",
    "reqd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "98e8cc3a",
   "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>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     A    B\n",
       "0  1.0  3.0\n",
       "1  0.0  4.0\n",
       "2  3.0  5.0"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Q15\n",
    "df1 = pd.DataFrame({'A': [None, 0, None], 'B': [3, 4, 5]})\n",
    "df2 = pd.DataFrame({'A': [1, 1, 3], 'B': [3, None, 3]})\n",
    "df1.combine_first(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21127cb3",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}