basicMerging.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1e5e87f8",
   "metadata": {},
   "source": [
    "(merging-pd)=\n",
    "# 3. Merge with pandas\n",
    "\n",
    "Pandas has a lot of helpful ways to merge spreadsheets based on identifiers. In this lesson, we are going to learn how to use the pandas function `merge()` and the four basic merge types:\n",
    "\n",
    "- left\n",
    "- right\n",
    "- outer\n",
    "- inner \n",
    "\n",
    "```{admonition} New function\n",
    "[`pd.merge()`](https://pandas.pydata.org/docs/reference/api/pandas.merge.html): Merge DataFrame or named Series objects with a database-style join.</a>\n",
    "```\n",
    "\n",
    "## Types of merges\n",
    "\n",
    "First, let's talk about the type of merges. I find it really helpful to visualize the different types of merges, so we are going to discuss the four different types through examples. In these examples, we will merge two `DataFrames` that we are calling `frame_1` (the left frame) and `frame_2` (the right frame) using the four main types of merges: left, right, outer, and inner.\n",
    "\n",
    "```{admonition} Files\n",
    ":class: important\n",
    "You can {Download}`download frame_1.csv here<./frame_1.csv>`.<br>\n",
    "You can {Download}`download frame_2.csv here<./frame_2.csv>`.\n",
    "```\n",
    "\n",
    "```{attention}\n",
    "For now, we are just going to focus on the concepts and leave the coding for later in the lesson.\n",
    "```\n",
    "\n",
    "In pandas merges, which mimic traditional database merges in SQL, there will be a spreadsheet (or `DataFrame`) we consider the \"left\" frame and a spreadsheet that we consider the \"right\" frame. \n",
    "\n",
    "Selecting a frame to be \"right\" or \"left\" is arbitrary, we just have to be consistent as we consider our merges. \n",
    "\n",
    "`frame_1` (the left frame)\n",
    "\n",
    "|title                                                                           | subject_id  | \n",
    "|:-------------------------------------------------------------------------------|:------------|\n",
    "|Overcoming Data Challenges in Machine Translation                               | subject_001 |\n",
    "|Experimental Methods Towards Controlling the Glycoform                          | subject_002 |\n",
    "|Interactions of DNA Polymerase Theta and Ku70/80 with Oxidative DNA Damage      | subject_003 |\n",
    "|Control and Learning of Dynamics in Human Movement                              | subject_004 |\n",
    "|Local Public Health Performance and its Impact on Population Health             | subject_005 |\n",
    "|THz and microwave spectroscopy on strongly correlated conductors                | subject_007 |\n",
    "|Subjectivity in Flux: Youth in Latin American and Latino Literature             | subject_009 |\n",
    "\n",
    "`frame_2` (the right frame)\n",
    "\n",
    "| subject_id  | subject              |\n",
    "| :---------- | :--------------------|\n",
    "| subject_001 | Machine learning     |\n",
    "| subject_002 | Biochemistry         |\n",
    "| subject_003 | DNA polymerases      |\n",
    "| subject_004 | Dynamics             |\n",
    "| subject_006 | Surgical emergencies |\n",
    "| subject_007 | Superconductivity    |\n",
    "| subject_008 | Social conditions    |\n",
    "\n",
    "### Left merge\n",
    "\n",
    "A left merge only uses keys (i.e. identifiers) from the left frame. Similar to a SQL left outer join. What identifiers will be in the merged `DataFrame`?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c8d55d4",
   "metadata": {
    "tags": [
     "full-width"
    ]
   },
   "source": [
    "```{toggle}\n",
    "In other words, the left merge only matches on the identifiers found in the left frame:\n",
    "\n",
    "- subject_001\n",
    "- subject_002\n",
    "- subject_003\n",
    "- subject_004\n",
    "- subject_005\n",
    "- subject_007\n",
    "- subject_009\n",
    "\n",
    "Here's the frames merged together using a left merge.\n",
    "\n",
    "| title                                                        | subject_id  | subject           |\n",
    "| :----------------------------------------------------------- | :---------- | :---------------- |\n",
    "| Overcoming Data Challenges in Machine Translation            | subject_001 | Machine learning  |\n",
    "| Experimental Methods Towards Controlling the Glycoform       | subject_002 | Biochemistry      |\n",
    "| Interactions of DNA Polymerase Theta and Ku70/80 with Oxidative DNA Damage | subject_003 | DNA polymerases   |\n",
    "| Control and Learning of Dynamics in Human Movement           | subject_004 | Dynamics          |\n",
    "| Local Public Health Performance and its Impact on Population Health | subject_005 |                   |\n",
    "| THz and microwave spectroscopy on strongly correlated conductors | subject_007 | Superconductivity |\n",
    "| Subjectivity in Flux: Youth in Latin American and Latino Literature | subject_009 |                   |\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "09666871",
   "metadata": {},
   "source": [
    "### Right merge\n",
    "\n",
    "A right merge only uses keys (i.e. identifiers) from the right frame. Similar to a SQL right outer join. What identifiers will be in the merged `DataFrame`?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72fa9306",
   "metadata": {
    "tags": [
     "full-width"
    ]
   },
   "source": [
    "```{toggle}\n",
    "In other words, the right merge only matches on the identifiers found in the right frame:\n",
    "\n",
    "- subject_001\n",
    "- subject_002\n",
    "- subject_003\n",
    "- subject_004\n",
    "- subject_006\n",
    "- subject_007\n",
    "- subject_008\n",
    "\n",
    "Here's the frames merged together using a right merge.\n",
    "\n",
    "| title                                                        | subject_id  | subject              |\n",
    "| ------------------------------------------------------------ | ----------- | -------------------- |\n",
    "| Overcoming Data Challenges in Machine Translation            | subject_001 | Machine learning     |\n",
    "| Experimental Methods Towards Controlling the Glycoform       | subject_002 | Biochemistry         |\n",
    "| Interactions of DNA Polymerase Theta and Ku70/80 with Oxidative DNA Damage | subject_003 | DNA polymerases      |\n",
    "| Control and Learning of Dynamics in Human Movement           | subject_004 | Dynamics             |\n",
    "|                                                              | subject_006 | Surgical emergencies |\n",
    "| THz and microwave spectroscopy on strongly correlated conductors | subject_007 | Superconductivity    |\n",
    "|                                                              | subject_008 | Social conditions    |\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "406a1173",
   "metadata": {},
   "source": [
    "### Outer merge\n",
    "\n",
    "An outer merge uses union of keys from both frames. Similar to a SQL full outer join. In other words, the outer merge combines information from all identifiers in the frames. What identifiers will be in the merged `DataFrame`?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad3f87d4",
   "metadata": {
    "tags": [
     "full-width"
    ]
   },
   "source": [
    "```{toggle}\n",
    "All of them!\n",
    "\n",
    "- subject_001\n",
    "- subject_002\n",
    "- subject_003\n",
    "- subject_004\n",
    "- subject_005\n",
    "- subject_006\n",
    "- subject_007\n",
    "- subject_008\n",
    "- subject_009\n",
    "\n",
    "Here's the frames merged together using an outer merge.\n",
    "\n",
    "| title                                                        | subject_id  | subject              |\n",
    "| :----------------------------------------------------------- | :---------- | :------------------- |\n",
    "| Overcoming Data Challenges in Machine Translation            | subject_001 | Machine learning     |\n",
    "| Experimental Methods Towards Controlling the Glycoform       | subject_002 | Biochemistry         |\n",
    "| Interactions of DNA Polymerase Theta and Ku70/80 with Oxidative DNA Damage | subject_003 | DNA polymerases      |\n",
    "| Control and Learning of Dynamics in Human Movement           | subject_004 | Dynamics             |\n",
    "| Local Public Health Performance and its Impact on Population Health | subject_005 |                      |\n",
    "| THz and microwave spectroscopy on strongly correlated conductors | subject_007 | Superconductivity    |\n",
    "| Subjectivity in Flux: Youth in Latin American and Latino Literature | subject_009 |                      |\n",
    "|                                                              | subject_006 | Surgical emergencies |\n",
    "|                                                              | subject_008 | Social conditions    |\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12572063",
   "metadata": {},
   "source": [
    "### Inner merge\n",
    "\n",
    "An inner merge uses intersection of keys from both frames. Similar to a SQL inner join. In other words, the inner merge only matches on the identifiers found in **both** frames. What identifiers will be in the merged `DataFrame`?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59875d0d",
   "metadata": {
    "tags": [
     "full-width"
    ]
   },
   "source": [
    "```{toggle}\n",
    "- subject_001\n",
    "- subject_002\n",
    "- subject_003\n",
    "- subject_004\n",
    "- subject_007\n",
    "\n",
    "Here's the frames merged together using an inner merge.\n",
    "\n",
    "| title                                                        | subject_id  | subject           |\n",
    "| :----------------------------------------------------------- | :---------- | :---------------- |\n",
    "| Overcoming Data Challenges in Machine Translation            | subject_001 | Machine learning  |\n",
    "| Experimental Methods Towards Controlling the Glycoform       | subject_002 | Biochemistry      |\n",
    "| Interactions of DNA Polymerase Theta and Ku70/80 with Oxidative DNA Damage | subject_003 | DNA polymerases   |\n",
    "| Control and Learning of Dynamics in Human Movement           | subject_004 | Dynamics          |\n",
    "| THz and microwave spectroscopy on strongly correlated conductors | subject_007 | Superconductivity |\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "852a85ca",
   "metadata": {},
   "source": [
    "Here's another way to visual these types of merges:\n",
    "\n",
    "<img src=\"https://gitlab.com/mjanowiecki/python-lessons-for-librarians/-/raw/main/img/mergeTypes.png\" alt=\"visual of merges as venn diagrams\" width=\"450\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5e865ecd",
   "metadata": {},
   "source": [
    "## Repeating keys and merges\n",
    "\n",
    "Here's a fun question. What happens during a merge when a key appears more than once in a `DataFrame`?\n",
    "\n",
    "Let's find out by merging on `department_id` for these two example `DataFrames`. \n",
    "\n",
    "```{admonition} Files\n",
    ":class: important\n",
    "You can {Download}`download frame_3.csv here<./frame_3.csv>`.<br>\n",
    "You can {Download}`download frame_4.csv here<./frame_4.csv>`.\n",
    "```\n",
    "\n",
    "`frame_3` (the left frame)\n",
    "\n",
    "| local_id   | name           | department_id  |\n",
    "| :--------- | :------------- | :------------- |\n",
    "| person_001 | Ahuja, Allie   | department_003 |\n",
    "| person_002 | Petrov, Stella | department_003 |\n",
    "| person_003 | Chen, Rachel   | department_002 |\n",
    "| person_004 | Smith, Amber   | department_004 |\n",
    "\n",
    "`frame_4` (the right frame)\n",
    "\n",
    "| department_id  | department_name      | school                          |\n",
    "|:-------------- | :------------------- |:--------------------------------|\n",
    "| department_001 | Cognitive Science    |Krieger School of Arts & Sciences|\n",
    "| department_002 | Near Eastern Studies |Krieger School of Arts & Sciences|\n",
    "| department_003 | English              |Krieger School of Arts & Sciences|\n",
    "| department_004 | Public Health        |Bloomberg School of Public Health|\n",
    "\n",
    "### Left merge\n",
    "\n",
    "In the left merge, the identifiers from `frame_3`  (department_003, department_003, department_002, department_004) are all matched, in order, with the information from `frame_4`.\n",
    "\n",
    "| local_id   | name           | department_id  | department_name      | school                            |\n",
    "|:-----------|:-------------- | :------------- | :------------------- | :-------------------------------- |\n",
    "| person_001 | Ahuja, Allie   | department_003 | English              | Krieger School of Arts & Sciences |\n",
    "| person_002 | Petrov, Stella | department_003 | English              | Krieger School of Arts & Sciences |\n",
    "| person_003 | Chen, Rachel   | department_002 | Near Eastern Studies | Krieger School of Arts & Sciences |\n",
    "| person_004 | Smith, Amber   | department_004 | Public Health        | Bloomberg School of Public Health |\n",
    "\n",
    "### Right merge\n",
    "\n",
    "In the right merge, the identifiers from `frame_4`  (department_001, department_002, department_003, department_004) are all matched, in order, with the information from `frame_3`. However, you'll notice that department_003 repeats here too, to capture both of the matches of department_003 in `frame_3`.\n",
    "\n",
    "| local_id   | name           | department_id  | department_name      | school                            |\n",
    "| :--------- | :------------- | :------------- | :------------------- | :-------------------------------- |\n",
    "|            |                | department_001 | Cognitive Science    | Krieger School of Arts & Sciences |\n",
    "| person_003 | Chen, Rachel   | department_002 | Near Eastern Studies | Krieger School of Arts & Sciences |\n",
    "| person_001 | Ahuja, Allie   | department_003 | English              | Krieger School of Arts & Sciences |\n",
    "| person_002 | Petrov, Stella | department_003 | English              | Krieger School of Arts & Sciences |\n",
    "| person_004 | Smith, Amber   | department_004 | Public Health        | Bloomberg School of Public Health |\n",
    "\n",
    "### Outer merge\n",
    "\n",
    "In the outer merge, the identifiers from `frame_3` (department_003, department_003, department_002, department_004) are all matched, in order, with the information from `frame_4`. Then, the remaining identifier from `frame_4` (department_001) is added to spreadsheet but doesn't match with anything.\n",
    "\n",
    "| local_id   | name           | department_id  | department_name      | school                            |\n",
    "| :--------- | :------------- | :------------- | :------------------- | :-------------------------------- |\n",
    "| person_001 | Ahuja, Allie   | department_003 | English              | Krieger School of Arts & Sciences |\n",
    "| person_002 | Petrov, Stella | department_003 | English              | Krieger School of Arts & Sciences |\n",
    "| person_003 | Chen, Rachel   | department_002 | Near Eastern Studies | Krieger School of Arts & Sciences |\n",
    "| person_004 | Smith, Amber   | department_004 | Public Health        | Bloomberg School of Public Health |\n",
    "|            |                | department_001 | Cognitive Science    | Krieger School of Arts & Sciences |\n",
    "\n",
    "### Inner merge\n",
    "\n",
    "In the inner merge,  the identifiers found in both `frame_3` and `frame_4` are matched together. This happens to be the same results as the left merge.\n",
    "\n",
    "| local_id   | name           | department_id  | department_name      | school                            |\n",
    "| :--------- | :------------- | :------------- | :------------------- | :-------------------------------- |\n",
    "| person_001 | Ahuja, Allie   | department_003 | English              | Krieger School of Arts & Sciences |\n",
    "| person_002 | Petrov, Stella | department_003 | English              | Krieger School of Arts & Sciences |\n",
    "| person_003 | Chen, Rachel   | department_002 | Near Eastern Studies | Krieger School of Arts & Sciences |\n",
    "| person_004 | Smith, Amber   | department_004 | Public Health        | Bloomberg School of Public Health |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e97bfb6d",
   "metadata": {},
   "source": [
    "## Trying a merge\n",
    "\n",
    "Let's try a merge using pandas. For this exercise, we are going to use the `sampleData_cleaned.csv` that we cleaned up previously and a spreadsheet with name authority URIs for the different Hopkins schools called `schoolURIs.csv`.\n",
    "\n",
    "```{admonition} Files\n",
    ":class: important\n",
    "You can {Download}`download sampleData_cleaned.csv here<./sampleData_cleaned.csv>`.<br>\n",
    "You can {Download}`download schoolURIs.csv here<./schoolURIs.csv>`.\n",
    "```\n",
    "\n",
    "| degree_grantor      | viaf             | lcnaf                                            |\n",
    "| :------------------| :---------------| :----------------------------------------------- |\n",
    "| Johns Hopkins University. School of Medicine  | http://viaf.org/viaf/124344829   | http://id.loc.gov/authorities/names/n81070677    |\n",
    "| Johns Hopkins University. Krieger School of Arts and Sciences | http://viaf.org/viaf/130409033 | http://id.loc.gov/authorities/names/n2007184404  |\n",
    "| Johns Hopkins University. Whiting School of Engineering      | http://viaf.org/viaf/4816164191747718740000 | http://id.loc.gov/authorities/names/no2022000309 |\n",
    "| Johns Hopkins University. Bloomberg School of Public Health  | http://viaf.org/viaf/142356070              | http://id.loc.gov/authorities/names/nr2001039005 |\n",
    "\n",
    "### Deciding on the type of merge\n",
    "\n",
    "First, lets decide on the type of merge. If we want a spreadsheet that has all of the information from `sampleData_cleaned` with the additional location details from `schoolURIs`, what type of merge should we do? What identifier should we use?\n",
    "\n",
    "If we treat `sampleData_cleaned` as the left frame and `schoolURIs` as the right frame, we should do a **left merge** using the `degree_grantor` as the key.\n",
    "\n",
    "Why is this the best option? Let's think through the results of the other merging options.\n",
    "\n",
    "**Right merge**: If there are identifiers in the right frame that aren't in the left frame, a right merge would *include* them and their associated data. However, we don't need this data to meet our goal. Moreover, if there are identifiers in the left frame that aren't in the right frame, an right merge would *exclude* them. Since we want to keep all of the data from `sampleData_cleaned`,  this isn't a good option.\n",
    "\n",
    "**Outer merge**: If there are identifiers in the right frame that aren't in the left frame, an outer merge would *include* them  and their associated data. However, we don't need this data to meet our goal. \n",
    "\n",
    "**Inner merge**:  If there are identifiers in the left frame that aren't in the right frame, an inner merge would *exclude* them. However, we want to keep all of the data from `sampleData_cleaned` so this isn't a good option.\n",
    "\n",
    "### Writing the code\n",
    "\n",
    "Let's look more closely at the `merge()` function. The first two parameters are the left and rights objects to merge; this is where we want to put our `DataFrames`. The next parameter is the `how` parameter, and this is where we indicate what type of merge we want (`\"left\"`, `\"right\"`, `\"inner\"`, or `\"outer\"`). We want `how=\"left\"` for this merge. \n",
    "\n",
    "Next, we need to use the `on` parameter to tell the function what identifier (aka column/Series) we want to merge on. We need to set this to `degree_grantor`.  \n",
    "\n",
    "Finally, lets set the suffixes. These are suffixes like `_x` and `_y` that are added to your column names if you have matching column names in your two `DataFrames`. So, if you have a `Name` column in `df_1` and a `Name` column in `df_2`, the merged `DataFrame` will have columns named `Name_x` and `Name_y`. I like to set these suffixes as `_1` and `_2`.\n",
    "\n",
    "Great! Now let's create a new CSV with our merged data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2083c740",
   "metadata": {
    "tags": [
     "hide-output",
     "output_scroll"
    ]
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   item_identifier                   advisor                   creator  \\\n",
      "0                1        Wolberger, Cynthia         Daniels, Casey M.   \n",
      "1                2  Fallin, Margaret Daniele  Collado Torres, Leonardo   \n",
      "2                3  Neelon, Sara E. Benjamin       Caswell, Bess L. L.   \n",
      "3                4           Roter, Debra L.              Jamal, Leila   \n",
      "4                5          Sears, Cynthia L             Rouhani, Saba   \n",
      "\n",
      "  date_issued                                              title  \\\n",
      "0  2015-07-31  Characterization of the ADP-ribosylated proteo...   \n",
      "1  2016-07-25  Annotation-Agnostic Differential Expression an...   \n",
      "2  2016-09-07  Child diet over three seasons in rural Zambia:...   \n",
      "3  2017-02-02  Exploring Parental Involvement in Rare Disease...   \n",
      "4  2018-05-23  Gut Microbes, Enteropathy and Child Growth: Th...   \n",
      "\n",
      "                       degree_discipline  \\\n",
      "0                           Biochemistry   \n",
      "1                          Biostatistics   \n",
      "2                        Human Nutrition   \n",
      "3                              Bioethics   \n",
      "4  Global Disease Epidemiology & Control   \n",
      "\n",
      "                                      degree_grantor  \\\n",
      "0  Johns Hopkins University. Bloomberg School of ...   \n",
      "1  Johns Hopkins University. Bloomberg School of ...   \n",
      "2  Johns Hopkins University. Bloomberg School of ...   \n",
      "3  Johns Hopkins University. Bloomberg School of ...   \n",
      "4  Johns Hopkins University. Bloomberg School of ...   \n",
      "\n",
      "                    degree_department  \\\n",
      "0  Biochemistry and Molecular Biology   \n",
      "1                       Biostatistics   \n",
      "2                International Health   \n",
      "3        Health Policy and Management   \n",
      "4                International Health   \n",
      "\n",
      "                                    committee_member  \\\n",
      "0  Leung, Anthony K. L.|Matunis, Michael J.|Dingl...   \n",
      "1  Leek, Jeffrey T.|Hansen, Kasper D.|Battle, Ale...   \n",
      "2  West, Keith P., Jr.|Talegawkar, Sameera|Fanzo,...   \n",
      "3  Kass, Nancy E.|Saloner, Brendan|Bodurtha, Joan...   \n",
      "4  Kosek, Margaret  N|Dowdy, David W|Sack, David ...   \n",
      "\n",
      "                             viaf  \\\n",
      "0  http://viaf.org/viaf/142356070   \n",
      "1  http://viaf.org/viaf/142356070   \n",
      "2  http://viaf.org/viaf/142356070   \n",
      "3  http://viaf.org/viaf/142356070   \n",
      "4  http://viaf.org/viaf/142356070   \n",
      "\n",
      "                                              lcnaf  \n",
      "0  http://id.loc.gov/authorities/names/nr2001039005  \n",
      "1  http://id.loc.gov/authorities/names/nr2001039005  \n",
      "2  http://id.loc.gov/authorities/names/nr2001039005  \n",
      "3  http://id.loc.gov/authorities/names/nr2001039005  \n",
      "4  http://id.loc.gov/authorities/names/nr2001039005  \n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df_1 = pd.read_csv('sampleData_cleaned.csv')\n",
    "df_2 = pd.read_csv('schoolURIs.csv')\n",
    "\n",
    "merged = pd.merge(df_1, df_2, how=\"left\", on=\"degree_grantor\")\n",
    "print(merged.head())\n",
    "\n",
    "merged.to_csv('sampleData_cleaned2.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Tags",
  "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}