CapitolQuery_3 – Organizing your Research-Ready Database.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Phase III: Organizing Your Research-Ready Database, Then Joining it with External Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We've come a long way. We have our documents together (Phase I), and we have found additional, structured information within them (Phase II). Now, we just need to organize the data so that a computer will be able to quickly and efficiently search through it. Once that is done, we can link data from external sources to ALL the data we've collected and organized. Then, our research-ready database will be completely ready for computational text analysis research and public inquiry.\n",
    "\n",
    "In this section, we will introduce you to some of the constraints computers face when storing and searching through data. Once we understand those, it will be easy to understand how we should organize our data for the machine. And once that is complete, we will show you how easy it is to join additional data to your database.  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Storing Data Like a Machine: A Brief Introduction to Computer Memory and Storage\n",
    "\n",
    "Cognitive scientists are still trying to understand precisely how human memory works, how – in a distributed network of neurons – we are able to store and recall information ranging from today's grocery list to the details of early childhood events. In the case of computers, the picture is much more clear, because computer scientists have actually designed and engineered computer memory and storage systems. \n",
    "\n",
    "In this section, we will surely offend both cognitive scientists and computer scientists by offering you a simplistic working model of computer memory that builds off analogies to your everyday experience with your own memory. This brief introduction will be innacurate, but useful, much like a street map. A street map always gives an inaccurate depiction of a city, because the actual city does not include so many colorful lines on the streets, labeled with 30 foot letters. Moreover, the city includes literally tons of other details that don't even appear on the map. Nonetheless, the map is quite useful because it can orient the user to the cities' key navigable features _while_ she is exploring it. Similarly, we offer the following model of computer memory, so that you may better understand how to navigate within your computer's memory constraints. \n",
    "\n",
    "To understand computer memory, start by imagining yourself working at a desk in a time before computers were ever invented. Maybe you are doing mathematical calculations. Maybe you are writing some detailed explanation of some natural phenomenon. Maybe you are doing a bit of both: writing up a scientific research paper describing the physics of objects in motion. As you write your paper, you are thinking thoughts, and moving a pen on paper to record a linguisitc or mathematical representation of those thoughts. The transfer of thoughts to paper is not instantaneous. You need to keep the thought in your head long enough to transfer it to paper, but also let it disappear immediately once it is transferred so you can keep your stream of thought moving. Your ability to do this – to maintain a thought just long enough to use it, but not so long that your consciousness is cluttered with useless information – is often referred to as your 'short-term memory capacity' or 'working memory capacity.' Short-term memory capacity varies a bit in humans. Some of us can maintain focus on tasks easier than others, or multi-task better than others. Others of us can't ever seem to find our glasses because we forget that we hung them on our shirt collar 30 seconds ago. \n",
    "\n",
    "Computers' short-term memory capacities vary, too. Theirs is called RAM (random access memory), and if you've ever bought a computer you've probably at least wondered what RAM is and why you should pay more to have more of it. Like our short-term, working memory capacity, RAM allows the computer to temporarily hold onto information and do work on that information. If your computer has a lot of RAM, you can go ahead and open 50 tabs in your web browser while using your word processor and spreadsheet software, all while playing a fast-paced adventure game. With less RAM, your word processor is going to crash, and you're going to have trouble with your game because the video will keep lagging. With all of those programs – the word processor, spreadsheet software, and the game – you will be occassionally prompted to save your work. That's because RAM, like your own brain's short-term memory, is not set up to save every last bit of information. It is configured to hold just enough information just long enough to do some work. But if your computer shuts off unexpectedly, your work will be lost. Just like if you were knocked unconscious unexpectedly, you might forget the several seconds prior to losing consciousness. To save work, your computer relies on a couple other types of memory, which we will also introduce by analogy.  \n",
    "\n",
    "Imagine you are back at your desk in the time befor computers, writing your physics tract. You know your treatise will be quite long, but you are only a couple pages into the writing. Given your own limited short-term memory capacity, you know you can't think and write everything in the same moment, so you have a separate pad of paper on which you have written an outline. You refer to this outline now and then to ensure that your writing conforms with your overall plan. You've also listed a number of references in your outline. You know the second section of your treatise will rely on the mathematics of Leibniz, and the third section will refer back to some of Newton's early work on the motion of falling bodies. These and the works of many other authors are not only listed in your outline, they are also available in the next room of your imaginary home: your library. \n",
    "\n",
    "Computers not only have RAM, which behaves a lot like your short-term memory, they have two types of memory/storage that behave a lot like your outline and your library, respectively. The first is called 'cache' and the second is called 'storage' or 'disk storage.' These two different types of computer memory/storage are useful to computers for the same reasons that outlines and libraries are useful for humans. Computers, just like you, can only do so much in a single moment. You can't think about the entire set of knowledge in your library. And they can't 'think' about the entire set of information in all the files that are saved to their storage. As with our hypothetical library, they keep all those files in a separate room where they are easy to organize and are not cluttering all of the thought-intensive work happening at our desk. Sure, it takes a moment to walk into the library – and you will notice that opening or saving a file to your computer's storage takes a full second or two longer than it takes to do something fast using your computer's RAM, like typing each letter into your word processor – but that's okay. Given that we and our computers have limited short-term working memory, we'd rather use our working memory to keep us typing fast, surfing the web, and adventuring through our online games. Rather than keeping all of our files displayed on our monitors at all times – which would quickly swamp all of our RAM – we can leave the vast majority of those files in storage (like our library) to be retrieved and used only when we need them.\n",
    "\n",
    "If a computer's storage works like our library, its cache works like the notepad on which we have written our outline and list of references. We aren't doing our primary set of work in the notepad. And we aren't storing all of the knowledge we own there. But we keep a bit of information there, close at hand so we can refer to it repeatedly as we do our work. Computer's use their cache in the same way. The cache cannot contain nearly as much information as storage, but it keeps information nearby and quickly accessible. To see cache in action, just open a web browser and go to a website you have never visited before. It will take 2 or 3 seconds to load all of the information on the page. Next, click on any link in the website, which will take you to a different page. Now, click the 'back' button so you will return to the page you had started from. Notice how quickly the webpage loads: not in 2 or 3 seconds as it did before but in less than a second! That is cache in action. \n",
    "\n",
    "Let us explain: When you first went to that webpage, your computer had to send a message to another computer on the other end of the internet saying, \"Hey, please give me all the files I need to render your webpage here on my local machine.\" That other computer said, \"No problem. I'm sending all the files across hundreds or thousdands of miles now. Enjoy our website.\" That whole process took about 2 or 3 seconds, and then your computer was able to load the files to display the website. But your computer did something else, too. It went ahead and saved those files in its own local cache. So when you clicked to a different page and then hit the 'back' button on your web browser, it just fetched the files from your local machine. It didn't need to talk to any computer thousands of miles away.\n",
    "\n",
    "Your computer does this sort of caching (to cache is to save files in a cache) very frequently and across a range of programs. If you open your word processor or spreadsheet software, it usually gives you the option to 'Open Recent' files. Those files are temporarily stored in your cache so you can access them more quickly. (They are also in your storage and you can find them by using the 'Open' command and navigating through your file structure.) For the most part, computer programs use your cache automatically, saving recent or commonly used files for quicker access than you can get by saving them only in storage. This means that your computer also automatically removes from its cache files you have not used in a while. \n",
    "\n",
    "Caches, as we will see, not only allow you to keep some files conveniently nearby without clogging working memory (RAM), they also enable your computer to efficiently do tasks that use both RAM and storage. As a quick preview of what is to come: we will show you how to efficiently search through even extremely large databases of very lengthy texts using processes that rely on RAM, cache, and storage. This will all be explained in detail below, so don't worry if you have trouble following it just now, but here's the basic picture: Suppose you have a huge set of documents in storage and you want to find all of the documents fitting a particular set of criteria, maybe all of the documents categorized as 'Physics' 'books' that were written in the 18th century, and that contain the keywords: 'fulcrum' and 'acceleration.' You could formalize a set of criteria like these into a 'query' using a database querying program that runs using RAM. The query could be used to search through an index file conveniently stored in your computer's cache – an index file containing information _about_ all the documents housed in your computer's storage without actually holding all the textual information (which would take a fairly long time to search through, even for a computer). Once the query is run over the index file from your cache, your query software (again, using RAM) will display on your screen the 7 or 8 books that satisfy your search. You might scan the titles and information about those books, and pick out the 5 or 6 that you think will be useful. You would then click a button like 'retrieve' and the program would send a request to your computer's storage asking that it retrieve the 5 or 6 files and display them on your monitor using RAM. \n",
    "\n",
    "We will get into more of the specifics below. We hope you take away from this section the following: an understanding of RAM, cache, and storage as similar to your own working memory, a handy notepad, and a large library down the hall, respectively. Each of these types of memory/storage has its own value. And using them together, humans and computers can maintain focus on their primary tasks, while keeping some useful information close at hand, while also storing the full set of their knowledge for occassional use. With this basic understanding of human and computer memory/storage systems, you will better understand some of the choices we make in Phase III and (bonus) Phase IV as we organize and share our databases."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Storing Data Efficiently: One 'Flat' File vs. Multiple Relational Tables\n",
    "\n",
    "Most of us have used spreadsheet software before, probably Microsoft Excel or Google Sheets. Spreadsheets are great for storing data, whether you're compiling a list of names and email addresses of potential party guests, or making quarterly projections of cost and revenue for your startup business. Spreadsheets are also great for storing textual data. Consider the table below:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from IPython.display import IFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "IFrame(\"https://docs.google.com/spreadsheets/d/1deKCiv3b5Vxmj_0IT_5PfzZOca-bxN5DZIEjSoZ3T8g/edit#gid=0\",'100%', 400)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These data should look familiar. We learned to extract this information from correspondence documents in Phase II. We could also imagine this spreadsheet filled with thousands of emails from dozens of senders within an organization. The resulting spreadsheet would likely include quite a bit of duplicate information. If Jake Ryland Williams sent 1000 emails per year, his name would be repeated 1000 times. If he also received 1200 emails per year, his name would be repeated a total of 2,200 times. For an organization with a couple hundred members, these duplicate records start to pile up. Names would be repeated on the order of 440,000 times per year. This redundancy can present some minor challenges for the memory capacity of your computer – for its RAM, in particular. But these problems become far more problematic with larger text fields. For instance, if the body of each correspondence also included a copy of previous correspondence (as is common with email) the size of your data set would grow at a compounding rate. And soon, you would not be able to search your entire spreadsheet using RAM without waiting several minutes. \n",
    "\n",
    "Moreover, making changes to your spreadsheet will be harder than you want it to be. Employees sometimes change their names or addresses. To reflect such a change, the spreadsheet approach to storing data requires you to change each record.\n",
    "\n",
    "Because of these and other limitations of spreadsheets, people often organize their data in a somewhat different format. Instead of storing everything in a single spreadsheet (often called a flat file), they cut the data up and store it across multiple spreadsheets (often called a relational database approach). It is rather easy to illustrate the advantage of this latter approach with examples. Suppose Steve Thompson finally relinquishes his fears and admits to himself and everyone that he could live more comfortably as Rebecca Thompson. Using a flat file approach, supporting Steve's identity transition to Rebecca would require changing thousands of records. But with a database approach, only one record needs to be changed, the record in the table that links personal ID #s with first and last names. Since all the other tables in the database just use the 'personal id' from the 'persons' table, none of them need to be changed. And all previous corresondence from Steve can be identified with the present and future correspondence of Rebecca. Easy. (For a more culturally traditional example, just imagine employees changing names after a marriage or divorce.)\n",
    "\n",
    "Since we are managing archives meant to preserve historical records, we probably won't face the issue of needing to change records en masse. But, there are other reasons to prefer the relational database approach over the flat file approach. Namely, searching records. Especially when dealing with data like ours that include large text fields, we want to avoid situations where we are forcing the computer to (usually unnecessarily) load into memore and/or \"read\" through all of our documents."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Searching Like a Machine: A Brief Introduction to Relational Database Querying\n",
    "\n",
    "After pulling together the Congressional Hearings, and then parsing them to find speeches, we have over 2 million records of speeches. Some of those speeches are short. But some are quite long. If we used a flat file approach to this data, searching through the data would swamp our RAM and slow us down. But, a database approach allows us to use RAM and cache to very quickly identify which records we want. Then, we can go find and retrieve the appropriate records in the computer's storage (or from a computer on the other side of the internet that is storing the records). Drawing on the understanding of computer memory we sketched earlier: Our computer's RAM will search through an index of records held in cache. This index is a lot like a card catalog at the library. It allows the RAM to know whether the records it seeks even exist. Then, it gives RAM the information needed to find the record/s in storage.\n",
    "\n",
    "Searching through a database is usually referred to as _querying_ the database. Below, we guide you through some practice examples of a query, so you can experience what the computer experiences. You will notice that performing a query requires jumping around from sheet to sheet. That jumping might seem inefficient to you, but a computer does it much more efficiently. You will also notice that you don't actually get to the text of a speech until the last operation of the query. This is by design. We don't want you or the computer to have to \"read\" through or even load into memory or display any speeches or text _unless_ that text is relevant to our query. This way, we can keep large amounts of data in storage and only retrieve (and send to RAM) the minimal, necessary query results. As opposed to a flat file approach where we would send all the data to anyone interested in even a small portion of it, the database approach ensures data irrelevant to our query is not being shipped all around in your machine, on your organization's network, or over the internet.   \n",
    "\n",
    "Go ahead and try out the practice examples below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "IFrame(\"https://docs.google.com/spreadsheets/d/1Wd0RMKAMaTptZ43Bcll664HL9HtMjExrL4AmDHCee-A/edit#gid=128752762\",'100%', 400)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "### Practice:\n",
    "How many speeches were given by a member of the Popular Party in the Committee on Agriculture? Who gave the speech? What were the first ten words of the speech? And what was the title of the Hearing in which the speech was given? Track the different tables you had to search through and what information you took to and from each table."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Type your answer here:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "Answer: 1, Nicholas Beauregard Adams, \"When in the course of human events it becomes necessary\", \"Hearing on pontification\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## 4. Organizing Data for the Machine: Database \"Normalization\"\n",
    "\n",
    "As you click through the different 'sheets' of the Congressional data to perform the practice queries above, you probably noticed that many of the sheets (AKA tables or relations) include a column like 'Hearing ID.' Hearing ID is stored as a simple number, unique to each hearing fully described in the 'Hearing' table. 'Hearing ID' is so common across the tables (AKA relations) of our database for two reasons. First, a lot of our data relates in some way to hearings. A 'Speech' happens in hearings. The 'Attendance' table tracks the people who show up to a hearing. 'Bills,' 'laws,' 'US codes,' and other 'related items' are discussed during 'Hearings.' 'Hearing ID' shows up so much because Hearings are linked to so much of our data in reality (they are the site of a lot of action) and as a matter of record (because Congressional Stenographers record transcripts of hearings and data about hearings). But, second, 'Hearing ID' also shows up so much because it is a lot easier to manage than 'Hearing Title.' When we link all of these different tables together by Hearing ID, the computer only needs to reads a unique 3 or 4 digit 'Hearing ID' number to know that a record in any of these other tables refers to a particular hearing. The computer can read that 2 or 3 digit number much faster than it reads a long 'Hearing Title.' Similarly, our 'Speakers' table (AKA relation) uses a 'Speech ID' rather than repeating the text of the full speech. Not only is the use of a short number much faster for the computer, the unique ID #s also allow us (and the computer) to easily disambiguate the hundreds (if not thousands) of speeches in our database that simple read: \"I yield the balance of my time.\" Person ID #s allow us to disambiguate the multiple different people named Tom Jones who appear across Congressional Hearings; etc. Moreover, \n",
    "we want to ensure that each individual cell in any of our spreadsheets/tables/relations contains just one piece of searchable information. For instance, each 'Person ID' relates to a unique combination of FirstName, LastName, and Honorific/Title variables. This way it will be computationally efficient and easy for an end user to later search for all speeches by people with the last name Kennedy without writing a specialized search that looks for a 'Kennedy' string somewhere in a Full Name field. Such specialized searches are more prone to human error, and slow down a computer that could otherwise perform simpler numeric searches based on unique IDs.\n",
    "\n",
    "These practices – linking data across tables (AKA relations), reducing the repetition of computationally costly data (like long strings), and ensuring each cell contains just one piece of information – are just three principles of database design and refinement that are often called \"database normalization.\" The goal of 'normalization' is to ensure that the computer can trace important relationships through (and across) the tables/relations of your database without spending time scanning through duplicate information or requiring special search algorithms.\n",
    "\n",
    "There are entire books written on database normalization, starting with Edgar Codd's foundational work. But here's some good starting advice (which we recommended in \"Phase O – Begin by Wishing\"): Make a separate table/relation for each object in your database, where 'object' is defined as some countable and comparable unit described by variables that will appear as columns in your table. In our case, a speech is an object, a speaker is an object, a hearing is an object, etc. But the date of a speech is not an object. It is a variable/property of the speech. So, it could appear as a column in our 'Speech' table/relation. \n",
    "\n",
    "### Practice: \n",
    "Go ahead and take a look in the 'speech' table/relation. What do you see? Is there a column for date? Why or why not?  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Type your answer here:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The reason there is no date field for 'Speech' boils down to another principle of database normalization: Never store more data than is necessary. We don't need to store speech date, because speeches always occur within hearings, and our 'Hearing' table/relation already holds date information. This principle of normalization is well illustrated by imagining the absurdity of designing your database by the opposite principle. Every one of our tables/relations could be expanded with more data. 'Speakers' could include full speech text in addition to 'Speech ID.' It could include full information on the people who were speaking. It could even include full information on the Hearing in which the speaker offered a speech, and on the committee which hosted the hearing. Very quickly, we could expand the size of our database by a couple orders of magnitude by bloating each table with duplicate information alrady found in the other tables/relations. Your job is to do the opposite. Remove all the bloat. Ensure your tables are inter-linked with ID variables. And your computer will be able to very quickly jump from one table to the next, gathering just the information relevant to users' queries without getting bogged down by duplicate information. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Distributing Data from Phase II to the Tables of your Database\n",
    "\n",
    "You have now collected all your data, parsed all your data and designed the structure that allows you and other researchers to run queries quickly and comfortably. You have come a long way. The final step to build your database is to cast the parsed data into its new structure. Now is the time to take this step.\n",
    "\n",
    "Let's take a single hearing as our example. For each hearing, we collected the text file including the transcript as well as an XML file including the metadata. Like when coming home from grocery shopping, it's time to sort what's in your two bags into the places it belongs. The milk goes into the fridge, the apples into the fruit basket. Taking our database design as our checklist, we will now fill each one of its tables by taking the contents of the parsed hearings transcript or the metadata file and putting it where it belongs.\n",
    "\n",
    "But let's first understand what's in our bags.\n",
    "\n",
    "### Hearings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import re\n",
    "\n",
    "hearing=pd.read_json(\"data/example hearing.json\")\n",
    "print('Table dimensions: ', hearing.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In our example, we have imported a hearing into a table of 7 rows and 58 columns. \n",
    "Each row respresents a conversation. Conversations are speeches that our parser identified as belonging to each other. \n",
    "The columns number is the maximum number of speeches belonging to the longest conversation. In our example hearing, one conversation apparently included a total of 58 individual speeches.\n",
    "\n",
    "Let's look briefly at the hearing:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hearing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Apparently, the conversations in this hearing have vastly different lengths. It starts with an opening statement i.e. only one speech in the first row. Then comes a brief back-and-forth in a total of five speeches in the second row, and so on.\n",
    "\n",
    "As you can see, the cells of this table include more information than just the text of the speech. The way we parsed the speeches was to collect not only the text, but also the speaker's name and some other information about it. \n",
    "\n",
    "Let's display the complete list for our opening statement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hearing[0][0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Besides the speech itself, we collected the name of the speaker, the date, committee and congressional session of the speech. We would also have stored the name of the previous speaker if there were one.\n",
    "\n",
    "Let's now import and look at the metadata.\n",
    "\n",
    "### Metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadata=pd.read_json(\"data/example metadata.json\")\n",
    "\n",
    "print('Table dimensions: ', metadata.shape)\n",
    "\n",
    "metadata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In our metadata, we collected only 2 rows but 60 columns. The metadata came in the form of an XML file. XML has the same structure as HTML. An XML file includes various tags. The tags here are our columns e.g. \"chamber\".\n",
    "\n",
    "Each tag can contain an attribute (\"attrs\"), a value (\"values\") or both. For our present hearing, the chamber's value is \"SENATE\", for example. Here is our hearing's title:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "metadata['title']['values']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "It is in this fashion, that we will fill our database structure. For each entry in our new database, we will point what piece of what file to allocate where in the new design. \n",
    "\n",
    "Here is a list of all the tags included in our metadata:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "list(metadata)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Not all of these tags are useful for our purposes. But many will be. \n",
    "\n",
    "### Putting it all together\n",
    "\n",
    "To see how the data is cast into your database structure, let's work through a table together. Our committee table will have 5 different variables in it. Since this is the first hearing we include in the datbase, we will set the committeed ID to 1. All other values are taken from our metadata by pointing at the relevant row-column combinations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "committee = pd.DataFrame({ \n",
    "            \"committee.id\" : 1,\n",
    "            \"type\": metadata['congcommittee']['attrs'][0]['type'], \n",
    "            \"committee.name\": metadata['congcommittee']['values'], \n",
    "            \"chamber\": metadata['chamber']['values'],\n",
    "            \"congress.session\": metadata['congcommittee']['attrs'][0]['congress'],\n",
    "            \"committee.session\":metadata['session']['values']\n",
    "        })\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And this is what it looks like:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "committee"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To complete our database, we now have to write a similar statement for each table we want to create. In the final step, we have to create a loop that takes the process through all the files we collect and simply extends the database tables, file by file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## 6. Joining Additional External Data to Your Database\n",
    "\n",
    "Now that your database is in good condition, and we understand the basic goals and principles of database normalization, expanding your database to include more data is as easy as ever. Of course, it will be easy enough to add more records to your current database. As Congress holds more hearings, for instance, our programming scripts from Phase I and II can pull together the hearing transcripts and add them into our database automatically. But, you can add other data to your database, too. It's as simple as adding tables that link to any of your current tables/relations. Look above at the 'Constituency_Characteristics' table/relation. None of this data is supplied by the GPO website. It will not appear in any Hearing transcript. Almost all the data in this table was gathered together then compiled from the U.S. Census website. But, it looks completely at home in our database. And it is. It links to the 'Constituency' table, which in turn links to our 'Congressmember' table, which links to our 'Person' table, which links to our 'Speakers' table, which links to our 'Speech' table. \n",
    "\n",
    "Without this census data, our database would have allowed us to search for speeches made by CongressMembers from particular States or Congressional districts. But we wanted more. As political sociologists, we have read a fair amount of research suggesting that the needs of urban constituencies differ quite a lot from the needs of rural constituencies. By linking census data to our database – which only required finding an alignment between Congressional Districts and the postal ZIP codes recorded in the census data – we are now able to query the Congressional Hearings for speeches made by CongressMembers representing districts across the range of population density. \n",
    "\n",
    "### Practice:\n",
    "What other data could we link to our Hearings data to better understand the factors influencing Congressional speeches?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Type your answer here:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In Appendix B, you will find a 'joining script' that tells the computer how to link Census data to U.S. Congressional districts. These joining tasks can require different amounts of work depending on how much your external data differ from data that is already in your database. But the joining process always finishes the same way, with the linking of your new data to existing data by the creation of a new table that includes the new data and a column with an object ID that already appears in your database. "
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}