class: center, middle, inverse, title-slide # Joining Data Sets <br><br> 🖇 ## Fall 2017 ### Tyson S. Barrett --- # What can be done with data... .large[.huge[.center[ [Tesla Autopilot 2.0](https://www.youtube.com/watch?v=C3DbrYx-SN4) ]]] --- # Joining Data Sets  --- # Some Vocabulary .pull-left[ .large[.large[ - .dcoral[**Join (merge)**] = combining two data sets (or *tables*) based on some **primary key(s)** - .nicegreen[**Primary Key**] = a variable that uniquely identifies observations in a table ]]] .footnote[ [R For Data Science](http://r4ds.had.co.nz/relational-data.html) ] -- .pull-right[ .large[.large[ - .bluer[**Foreign Key**] = a variable that uniquely identifies an observation in another table - .dcoral[**Relation**] = the relationship between a primary key and a foreign key ]]] --- background-image: url(key_diagram.jpg) background-position: 50% 85% background-size: 800px # Example --- # Types of Joins .pull-left[.large[.large[ .dcoral[**Inner Join**] = joining only the observations that are in both tables .bluer[**Left Join**] = joining all observations in first table but drop those in the second that are not in the first ]]] -- .pull-right[.large[.large[ .nicegreen[**Right Join**] = joining all observations in second table but drop those in the first that are not in the second .dcoral[**Full (Outer) Join**] = joining all the observations whether they are in one or both tables ]]] --- background-image: url(animated-inner-join.gif) background-position: 50% 60% background-size: 700px # Inner Join .footnote[Thanks to [Garrick Aden-Buie](garrickadenbuie.com) for the gganimate code for these figures!] --- background-image: url(animated-left-join.gif) background-position: 50% 60% background-size: 700px # Left Join .footnote[Thanks to [Garrick Aden-Buie](garrickadenbuie.com) for the gganimate code for these figures!] ??? Anything stand out at you compared to the last diagram? --- background-image: url(animated-right-join.gif) background-position: 50% 60% background-size: 700px # Right Join .footnote[Thanks to [Garrick Aden-Buie](garrickadenbuie.com) for the gganimate code for these figures!] --- background-image: url(animated-full-join.gif) background-position: 50% 60% background-size: 600px # Full Join .footnote[Thanks to [Garrick Aden-Buie](garrickadenbuie.com) for the gganimate code for these figures!] --- # Quiz! .pull-left[   ] .pull-right[  ] --- # Quiz! .huge[ - Explain joining in a sentence or two. ] -- .huge[ - If you have more individuals in data set 1 than data set 2 and you want to keep all of the observations, what kind of join is best? ] -- .huge[ - Thought Experiment: What would happen if you tried to join on a foreign key? ] --- # Answers .huge[ - Joining is taking two data sets and joining them based on identifiers (keys) or something similar to that. ] -- .huge[ - Either left join or full join. ] -- .huge[ - This situation is called duplicate keys. See next slide for an example. ] --- background-image: url(duplicatekeys_diagram.png) background-position: 50% 80% background-size: 800px # Duplicate Keys .huge[For `x` this is a foreign key and for `y` it is a primary key. Why is that?] --- background-image: url(key_diagram.jpg) background-position: 50% 85% background-size: 800px # Let's Join! --- # Health <div id="htmlwidget-f3d232dec4584f8fd634" style="width:100%;height:auto;" class="datatables html-widget"></div> <script type="application/json" data-for="htmlwidget-f3d232dec4584f8fd634">{"x":{"filter":"none","data":[["Michael","Pam","Jim","Dwight","Stanley","Phyllis","Creed","Meredith","Oscar","Angela","Kevin","Kelley","Ryan","Toby","Andy","Jan","April","Andy","Leslie","Ron","Tom","Donna","Ben","Chris","Gary (Larry, Jerry)","Jean Ralphio","Mona Lisa","Ann","Kyle","The Other Ron","Craig","Shauna Molwaytweep","Councilman Jamm","Ethel","Councilman Howser","Tammy","Tammy II","Diane","Michael","Pam","Jim","Dwight","Stanley","Phyllis","Creed","Meredith","Oscar","Angela","Kevin","Kelley","Ryan","Toby","Andy","Jan","April","Andy","Leslie","Ron","Tom","Donna","Ben","Chris","Gary (Larry, Jerry)","Jean Ralphio","Mona Lisa","Ann","Kyle","The Other Ron","Craig","Shauna Molwaytweep","Councilman Jamm","Ethel","Councilman Howser","Tammy","Tammy II","Diane"],["The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec"],[1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2],[2,3,3,5,4,4,1,3,5,4,2,3,2,4,3,4,1,1,5,3,2,2,5,4,3,1,1,5,3,null,null,4,null,2,5,null,5,null,4,3,5,7,5,3,1,4,7,4,3,5,5,5,6,2,1,3,7,3,3,1,5,4,3,2,0,6,3,3,4,7,1,4,7,4,8,5],[15,8,5,12,13,10,null,13,10,9,14,17,11,19,16,10,15,7,5,5,12,10,9,2,10,15,15,9,17,null,null,17,null,10,8,null,15,null,16,9,5,13,13,11,13,12,10,9,14,16,11,18,16,17,15,7,5,4,13,12,9,1,10,18,16,8,17,5,15,16,18,10,6,5,15,8],[5,2,3,4,1,1,3,4,3,4,3,2,2,1,3,1,18,15,10,5,10,8,10,14,16,19,19,10,15,null,null,10,null,10,4,null,15,null,6,1,2,4,-0,-0,2,4,2,4,2,1,1,-0,2,-0,25,20,13,6,13,10,13,19,22,26,26,13,20,7,17,13,19,13,4,5,20,4],[3,8,8,6,7,8,2,5,7,5,6,5,2,1,5,6,6,2,8,8,5,7,8,6,5,1,1,8,5,null,null,6,null,5,6,null,5,null,4,8,10,7,8,10,1,7,8,7,6,6,4,3,7,2,6,5,9,7,6,9,10,7,7,3,4,9,6,5,4,7,3,7,6,5,6,4]],"container":"<table class=\"display\">\n <thead>\n <tr>\n <th>nam<\/th>\n <th>show<\/th>\n <th>time<\/th>\n <th>prod<\/th>\n <th>depr<\/th>\n <th>awkw<\/th>\n <th>ment<\/th>\n <\/tr>\n <\/thead>\n<\/table>","options":{"pageLength":4,"autoWidth":true,"columnDefs":[{"className":"dt-right","targets":[2,3,4,5,6]}],"order":[],"orderClasses":false,"lengthMenu":[4,10,25,50,100]}},"evals":[],"jsHooks":[]}</script> --- # Demographics <div id="htmlwidget-966c147907ce42bc6f74" style="width:100%;height:auto;" class="datatables html-widget"></div> <script type="application/json" data-for="htmlwidget-966c147907ce42bc6f74">{"x":{"filter":"none","data":[["Michael","Pam","Jim","Dwight","Stanley","Phyllis","Creed","Meredith","Oscar","Angela","Kevin","Kelley","Ryan","Toby","Andy","Jan","April","Andy","Leslie","Ron","Tom","Donna","Ben","Chris","Gary (Larry, Jerry)","Jean Ralphio","Mona Lisa","Ann","Kyle","The Other Ron","Craig","Shauna Molwaytweep","Councilman Jamm","Ethel","Councilman Howser","Tammy","Tammy II","Diane"],["The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec"],[8,7,8,8,4,4,4,4,7,7,2,5,5,6,7,6,4,2,7,7,5,6,5,8,3,2,1,8,2,7,6,5,5,2,6,5,3,7],[0,1,1,0,1,1,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,1,0,0,0,1,0,0,0,1,0,null,0,0,1],[0,1,0,0,0,1,0,1,0,1,0,1,0,0,0,1,1,0,1,0,0,1,0,0,0,0,1,1,1,0,1,0,1,0,1,0,0,0],["White","White","White","White","Black","White","White","White","Mexican American","White","White","Indian","White","White","White","White","Mexican American","White","White","White","Indian","Black","White","White","White","White","White","White","White","White","White","White","White","White","Black","White","White","White"],[4,2,2,3,5,5,5,4,3,3,3,2,2,3,3,3,1,2,3,4,2,3,3,4,5,1,1,3,4,5,3,2,4,5,5,5,4,5],[55,35,70,70,70,70,45,40,50,50,45,40,40,60,60,80,25,15,45,55,35,70,65,70,40,10,10,40,35,35,35,45,90,40,60,55,40,90]],"container":"<table class=\"display\">\n <thead>\n <tr>\n <th>nam<\/th>\n <th>show<\/th>\n <th>phys<\/th>\n <th>marr<\/th>\n <th>gend<\/th>\n <th>race<\/th>\n <th>age<\/th>\n <th>inco<\/th>\n <\/tr>\n <\/thead>\n<\/table>","options":{"pageLength":4,"columnDefs":[{"className":"dt-right","targets":[2,3,4,6,7]}],"order":[],"autoWidth":false,"orderClasses":false,"lengthMenu":[4,10,25,50,100]}},"evals":[],"jsHooks":[]}</script> --- # Show Info <div id="htmlwidget-0c4e56f841f3e8f93fea" style="width:100%;height:auto;" class="datatables html-widget"></div> <script type="application/json" data-for="htmlwidget-0c4e56f841f3e8f93fea">{"x":{"filter":"none","data":[["The Office","Parks and Rec"],["March 24, 2005","April 9, 2009"],["May 16, 2013","February 24, 2015"]],"container":"<table class=\"display\">\n <thead>\n <tr>\n <th>show<\/th>\n <th>start_date<\/th>\n <th>last_aired<\/th>\n <\/tr>\n <\/thead>\n<\/table>","options":{"pageLength":4,"dom":"t","order":[],"autoWidth":false,"orderClasses":false,"lengthMenu":[4,10,25,50,100]}},"evals":[],"jsHooks":[]}</script> --- # Joining in `R` .huge[ We will use the .dcoral[`tidyverse`] package: ] .large[.large[ 1. .nicegreen[`inner_join`].dcoral[`(`]`data1, data2`.dcoral[`)`] 2. .nicegreen[`left_join`].dcoral[`(`]`data1, data2`.dcoral[`)`] 3. .nicegreen[`right_join`].dcoral[`(`]`data1, data2`.dcoral[`)`] 4. .nicegreen[`full_join`].dcoral[`(`]`data1, data2`.dcoral[`)`] ]] --- background-image: url(key_diagram.jpg) background-position: 95% 90% background-size: 250px # Inner Join ```r inner_join(show, demo, by = "show") ``` <div id="htmlwidget-ca846cb2d1cc364a77f6" style="width:100%;height:auto;" class="datatables html-widget"></div> <script type="application/json" data-for="htmlwidget-ca846cb2d1cc364a77f6">{"x":{"filter":"none","data":[["The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec"],["March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009"],["May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015"],["Michael","Pam","Jim","Dwight","Stanley","Phyllis","Creed","Meredith","Oscar","Angela","Kevin","Kelley","Ryan","Toby","Andy","Jan","April","Andy","Leslie","Ron","Tom","Donna","Ben","Chris","Gary (Larry, Jerry)","Jean Ralphio","Mona Lisa","Ann","Kyle","The Other Ron","Craig","Shauna Molwaytweep","Councilman Jamm","Ethel","Councilman Howser","Tammy","Tammy II","Diane"],[8,7,8,8,4,4,4,4,7,7,2,5,5,6,7,6,4,2,7,7,5,6,5,8,3,2,1,8,2,7,6,5,5,2,6,5,3,7],[0,1,1,0,1,1,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,1,0,0,0,1,0,0,0,1,0,null,0,0,1],[0,1,0,0,0,1,0,1,0,1,0,1,0,0,0,1,1,0,1,0,0,1,0,0,0,0,1,1,1,0,1,0,1,0,1,0,0,0],["White","White","White","White","Black","White","White","White","Mexican American","White","White","Indian","White","White","White","White","Mexican American","White","White","White","Indian","Black","White","White","White","White","White","White","White","White","White","White","White","White","Black","White","White","White"],[4,2,2,3,5,5,5,4,3,3,3,2,2,3,3,3,1,2,3,4,2,3,3,4,5,1,1,3,4,5,3,2,4,5,5,5,4,5],[55,35,70,70,70,70,45,40,50,50,45,40,40,60,60,80,25,15,45,55,35,70,65,70,40,10,10,40,35,35,35,45,90,40,60,55,40,90]],"container":"<table class=\"display\">\n <thead>\n <tr>\n <th>show<\/th>\n <th>start_date<\/th>\n <th>last_aired<\/th>\n <th>nam<\/th>\n <th>phys<\/th>\n <th>marr<\/th>\n <th>gend<\/th>\n <th>race<\/th>\n <th>age<\/th>\n <th>inco<\/th>\n <\/tr>\n <\/thead>\n<\/table>","options":{"pageLength":4,"dom":"t","columnDefs":[{"className":"dt-right","targets":[4,5,6,8,9]}],"order":[],"autoWidth":false,"orderClasses":false,"lengthMenu":[4,10,25,50,100]}},"evals":[],"jsHooks":[]}</script> --- background-image: url(key_diagram.jpg) background-position: 95% 90% background-size: 250px # Left and Right Joins ### Left ```r left_join(show, demo, by = "show") ``` ### Right ```r right_join(show, demo, by = "show") ``` --- background-image: url(key_diagram.jpg) background-position: 95% 90% background-size: 250px # Full Joins ```r full_join(show, demo, by = "show") ``` <div id="htmlwidget-aca71431895d9f8685b2" style="width:100%;height:auto;" class="datatables html-widget"></div> <script type="application/json" data-for="htmlwidget-aca71431895d9f8685b2">{"x":{"filter":"none","data":[["The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec"],["March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","March 24, 2005","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009","April 9, 2009"],["May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","May 16, 2013","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015","February 24, 2015"],["Michael","Pam","Jim","Dwight","Stanley","Phyllis","Creed","Meredith","Oscar","Angela","Kevin","Kelley","Ryan","Toby","Andy","Jan","April","Andy","Leslie","Ron","Tom","Donna","Ben","Chris","Gary (Larry, Jerry)","Jean Ralphio","Mona Lisa","Ann","Kyle","The Other Ron","Craig","Shauna Molwaytweep","Councilman Jamm","Ethel","Councilman Howser","Tammy","Tammy II","Diane"],[8,7,8,8,4,4,4,4,7,7,2,5,5,6,7,6,4,2,7,7,5,6,5,8,3,2,1,8,2,7,6,5,5,2,6,5,3,7],[0,1,1,0,1,1,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,1,0,0,0,1,0,0,0,1,0,null,0,0,1],[0,1,0,0,0,1,0,1,0,1,0,1,0,0,0,1,1,0,1,0,0,1,0,0,0,0,1,1,1,0,1,0,1,0,1,0,0,0],["White","White","White","White","Black","White","White","White","Mexican American","White","White","Indian","White","White","White","White","Mexican American","White","White","White","Indian","Black","White","White","White","White","White","White","White","White","White","White","White","White","Black","White","White","White"],[4,2,2,3,5,5,5,4,3,3,3,2,2,3,3,3,1,2,3,4,2,3,3,4,5,1,1,3,4,5,3,2,4,5,5,5,4,5],[55,35,70,70,70,70,45,40,50,50,45,40,40,60,60,80,25,15,45,55,35,70,65,70,40,10,10,40,35,35,35,45,90,40,60,55,40,90]],"container":"<table class=\"display\">\n <thead>\n <tr>\n <th>show<\/th>\n <th>start_date<\/th>\n <th>last_aired<\/th>\n <th>nam<\/th>\n <th>phys<\/th>\n <th>marr<\/th>\n <th>gend<\/th>\n <th>race<\/th>\n <th>age<\/th>\n <th>inco<\/th>\n <\/tr>\n <\/thead>\n<\/table>","options":{"pageLength":4,"dom":"t","columnDefs":[{"className":"dt-right","targets":[4,5,6,8,9]}],"order":[],"autoWidth":false,"orderClasses":false,"lengthMenu":[4,10,25,50,100]}},"evals":[],"jsHooks":[]}</script> --- background-image: url(key_diagram.jpg) background-position: 95% 90% background-size: 300px # Join Health and Demographics .huge[ Notable aspects of these sets: ] .large[.large[ - Health is in long form (two time points) - Demographics and health have multiple overlapping primary keys (`nam` and `show`) ]] --- # Join Health and Demographics ```r full_join(health, demo, by = c("nam", "show")) ## or inner_, left_,, or right_ ``` <div id="htmlwidget-8007fdba147be030551c" style="width:100%;height:auto;" class="datatables html-widget"></div> <script type="application/json" data-for="htmlwidget-8007fdba147be030551c">{"x":{"filter":"none","data":[["Michael","Pam","Jim","Dwight","Stanley","Phyllis","Creed","Meredith","Oscar","Angela","Kevin","Kelley","Ryan","Toby","Andy","Jan","April","Andy","Leslie","Ron","Tom","Donna","Ben","Chris","Gary (Larry, Jerry)","Jean Ralphio","Mona Lisa","Ann","Kyle","The Other Ron","Craig","Shauna Molwaytweep","Councilman Jamm","Ethel","Councilman Howser","Tammy","Tammy II","Diane","Michael","Pam","Jim","Dwight","Stanley","Phyllis","Creed","Meredith","Oscar","Angela","Kevin","Kelley","Ryan","Toby","Andy","Jan","April","Andy","Leslie","Ron","Tom","Donna","Ben","Chris","Gary (Larry, Jerry)","Jean Ralphio","Mona Lisa","Ann","Kyle","The Other Ron","Craig","Shauna Molwaytweep","Councilman Jamm","Ethel","Councilman Howser","Tammy","Tammy II","Diane"],["The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","The Office","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec","Parks and Rec"],[1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2],[2,3,3,5,4,4,1,3,5,4,2,3,2,4,3,4,1,1,5,3,2,2,5,4,3,1,1,5,3,null,null,4,null,2,5,null,5,null,4,3,5,7,5,3,1,4,7,4,3,5,5,5,6,2,1,3,7,3,3,1,5,4,3,2,0,6,3,3,4,7,1,4,7,4,8,5],[15,8,5,12,13,10,null,13,10,9,14,17,11,19,16,10,15,7,5,5,12,10,9,2,10,15,15,9,17,null,null,17,null,10,8,null,15,null,16,9,5,13,13,11,13,12,10,9,14,16,11,18,16,17,15,7,5,4,13,12,9,1,10,18,16,8,17,5,15,16,18,10,6,5,15,8],[5,2,3,4,1,1,3,4,3,4,3,2,2,1,3,1,18,15,10,5,10,8,10,14,16,19,19,10,15,null,null,10,null,10,4,null,15,null,6,1,2,4,-0,-0,2,4,2,4,2,1,1,-0,2,-0,25,20,13,6,13,10,13,19,22,26,26,13,20,7,17,13,19,13,4,5,20,4],[3,8,8,6,7,8,2,5,7,5,6,5,2,1,5,6,6,2,8,8,5,7,8,6,5,1,1,8,5,null,null,6,null,5,6,null,5,null,4,8,10,7,8,10,1,7,8,7,6,6,4,3,7,2,6,5,9,7,6,9,10,7,7,3,4,9,6,5,4,7,3,7,6,5,6,4],[8,7,8,8,4,4,4,4,7,7,2,5,5,6,7,6,4,2,7,7,5,6,5,8,3,2,1,8,2,7,6,5,5,2,6,5,3,7,8,7,8,8,4,4,4,4,7,7,2,5,5,6,7,6,4,2,7,7,5,6,5,8,3,2,1,8,2,7,6,5,5,2,6,5,3,7],[0,1,1,0,1,1,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,1,0,0,0,1,0,0,0,1,0,null,0,0,1,0,1,1,0,1,1,0,0,0,0,0,0,0,0,0,1,1,1,0,0,0,0,0,0,1,0,0,0,1,0,0,0,1,0,null,0,0,1],[0,1,0,0,0,1,0,1,0,1,0,1,0,0,0,1,1,0,1,0,0,1,0,0,0,0,1,1,1,0,1,0,1,0,1,0,0,0,0,1,0,0,0,1,0,1,0,1,0,1,0,0,0,1,1,0,1,0,0,1,0,0,0,0,1,1,1,0,1,0,1,0,1,0,0,0],["White","White","White","White","Black","White","White","White","Mexican American","White","White","Indian","White","White","White","White","Mexican American","White","White","White","Indian","Black","White","White","White","White","White","White","White","White","White","White","White","White","Black","White","White","White","White","White","White","White","Black","White","White","White","Mexican American","White","White","Indian","White","White","White","White","Mexican American","White","White","White","Indian","Black","White","White","White","White","White","White","White","White","White","White","White","White","Black","White","White","White"],[4,2,2,3,5,5,5,4,3,3,3,2,2,3,3,3,1,2,3,4,2,3,3,4,5,1,1,3,4,5,3,2,4,5,5,5,4,5,4,2,2,3,5,5,5,4,3,3,3,2,2,3,3,3,1,2,3,4,2,3,3,4,5,1,1,3,4,5,3,2,4,5,5,5,4,5],[55,35,70,70,70,70,45,40,50,50,45,40,40,60,60,80,25,15,45,55,35,70,65,70,40,10,10,40,35,35,35,45,90,40,60,55,40,90,55,35,70,70,70,70,45,40,50,50,45,40,40,60,60,80,25,15,45,55,35,70,65,70,40,10,10,40,35,35,35,45,90,40,60,55,40,90]],"container":"<table class=\"display\">\n <thead>\n <tr>\n <th>nam<\/th>\n <th>show<\/th>\n <th>time<\/th>\n <th>prod<\/th>\n <th>depr<\/th>\n <th>awkw<\/th>\n <th>ment<\/th>\n <th>phys<\/th>\n <th>marr<\/th>\n <th>gend<\/th>\n <th>race<\/th>\n <th>age<\/th>\n <th>inco<\/th>\n <\/tr>\n <\/thead>\n<\/table>","options":{"pageLength":4,"columnDefs":[{"className":"dt-right","targets":[2,3,4,5,6,7,8,9,11,12]}],"order":[],"autoWidth":false,"orderClasses":false,"lengthMenu":[4,10,25,50,100]}},"evals":[],"jsHooks":[]}</script> --- # What if we .dcoral[forgot] one of the .nicegreen[keys]? ```r full_join(health, demo, by = "nam") ``` .large[.large[ This provides 80 rows (not the correct 76) ]] -- <br> ```r full_join(health, demo, by = "show") ``` .large[.large[ This provides 1,480 rows (not the correct 76) ]] -- <br> .center[.large[.large[.dcoral[ Why might this happen? ]]]] --- background-image: url(fig_bad_join.jpg) background-position: 50% 85% background-size: 1000px # .nicegreen[Unique] Identifiers .huge[If we don't have unique identifiers the data can blow up] --- # Common Errors... .huge[ 1. Understand your data (both primary and foreign keys, missing values) 2. Trying to join when should be "binding" ] --- # What About Missing? .huge[ Notes: 1. If .dcoral[key] (primary or foreign) is missing, those cannot be joined with the correct data (it will keep those in `full_join()`) 2. Other missing values are carried into the joined set ] --- # .dcoral[Bind], Instead of Join .huge[ What if we have two versions of the demographics table - One that contains info on "Parks and Rec" - The other on "The Office" .bluer[Do we have keys we can join on?] ] -- .huge[ Nope... ] --- background-image: url(binding_diagram.jpg) background-position: 50% 90% background-size: 600px # .dcoral[Bind], Instead of Join ```r rbind(office_health, parks_health) ``` --- # Awesome Resource .huge[ [R For Data Science by Garrett Grolemund and Hadley Wickham](http://r4ds.had.co.nz/relational-data.html) ] --- # Now Can Join Like a Cool Kid <div style="width:100%;height:0;padding-bottom:75%;position:relative;"><iframe src="https://giphy.com/embed/3o6nUP7jsZCYziNxlK" width="60%" height="60%" style="position:absolute" frameBorder="0" class="giphy-embed" allowFullScreen></iframe></div><p><a href="https://giphy.com/gifs/heyarnold-nicksplat-3o6nUP7jsZCYziNxlK">via GIPHY</a></p>