\documentclass[10pt,american]{beamer}
\usepackage{lmodern}
\renewcommand{\sfdefault}{lmss}
\renewcommand{\ttdefault}{lmtt}
\usepackage[T1]{fontenc}
\usepackage[utf8]{inputenc}
\setcounter{secnumdepth}{3}
\setcounter{tocdepth}{3}
\setlength{\parindent}{0.1cm}
\usepackage{babel}
\ifx\hypersetup\undefined
  \AtBeginDocument{%
    \hypersetup{unicode=true,pdfusetitle,
 bookmarks=true,bookmarksnumbered=false,bookmarksopen=false,
 breaklinks=true,pdfborder={0 0 1},backref=false,colorlinks=true}
  }
\else
  \hypersetup{unicode=true,pdfusetitle,
 bookmarks=true,bookmarksnumbered=false,bookmarksopen=false,
 breaklinks=true,pdfborder={0 0 1},backref=false,colorlinks=true}
\fi
\usepackage{breakurl}

\makeatletter
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% Textclass specific LaTeX commands.
\usepackage[natbibapa]{apacite}

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% User specified LaTeX commands.
\usepackage{Sweave}
\usepackage{dcolumn}
\usepackage{booktabs}
\usepackage{longtable}
\newcommand\makebeamertitle{\frame{\maketitle}}%
\renewcommand{\doiprefix}{doi:\kern-1pt}
\setlength{\bibsep}{10pt}

% use 'handout' to produce handouts
%\documentclass[handout]{beamer}
\usepackage{wasysym}
\usepackage{pgfpages}
\newcommand{\vn}[1]{\mbox{{\it #1}}}\newcommand{\vb}{\vspace{\baselineskip}}\newcommand{\vh}{\vspace{.5\baselineskip}}\newcommand{\vf}{\vspace{\fill}}\newcommand{\splus}{\textsf{S-PLUS}}\newcommand{\R}{\textsf{R}}


\usepackage{graphicx}
\usepackage{listings}


%% Paste Sweavel content here from guidePreambleSweavel.tex
%% This controls display of code chunks
\usepackage{ae,fancyvrb,relsize}

\providecommand{\Sweavesize}{\normalsize}
\providecommand{\Rsize}{}
\renewcommand{\Rsize}{\normalsize}
\providecommand{\Routsize}{\scriptsize}

\providecommand{\Rcolor}{\color[rgb]{0.1, 0.1, 0.1}}
\providecommand{\Routcolor}{\color[rgb]{0.2, 0.2, 0.2}}
\providecommand{\Rcommentcolor}{\color[rgb]{0.101, 0.43, 0.432}}

\providecommand{\Rbackground}{\color[gray]{0.91}}
\providecommand{\Routbackground}{\color[gray]{0.935}}
% Can specify \color[gray]{1} for white background or just \color{white}

\lstdefinestyle{Rinput}{
  language=R,
  escapechar=`,
  fancyvrb=false,%
  tabsize=2,%
  breaklines=true,
  breakatwhitespace=true,%
  captionpos=b,%
  frame=single,%
  framerule=0.2pt,%
  framesep=1pt,%
  showstringspaces=false,%
  basicstyle=\Rsize\Rcolor\ttfamily,%
  columns=fixed%,
  \lst@ifdisplaystyle\scriptsize\fi,%,
  commentstyle=\Rcommentcolor\ttfamily,%
  identifierstyle=,%
  keywords=\bfseries,%
  keywordstyle=\color[rgb]{0, 0.5, 0.5},
  escapeinside={(*}{*)},
  literate={~}{{$\sim$}}1 {==}{{=\,=}}2,
  alsoother={$},
  alsoletter={.<-},%
otherkeywords={!,!=,~,$$,*,\&,\%/\%,\%*\%,\%\%,<-,<<-,/},%
  backgroundcolor=\Rbackground,%
  numbers=left,%
  numberstyle={\tiny}
}%

% Other options of interest:
% frame=single,framerule=0.1pt,framesep=1pt,rulecolor=\color{blue},
% numbers=left,numberstyle=\tiny,stepnumber=1,numbersep=7pt,
% keywordstyle={\bf\Rcolor}

\lstdefinestyle{Routput}{fancyvrb=false,
literate={~}{{$\sim$}}1{R^2}{{$R^{2}$}}2{^}{{$^{\scriptstyle\wedge}$}}1{R-squared}{{$R^{2}$}}2,%
  basicstyle=\Routcolor\Routsize\ttfamily,%
  backgroundcolor=\Routbackground,
  language=R,
  escapechar=`,
  fancyvrb=false,%
  tabsize=2,%
  breaklines=true,
  breakatwhitespace=true,%
  captionpos=b,%
  frame=single,%
  framerule=0.2pt,%
  framesep=1pt,%
  showstringspaces=false,%
  columns=fixed%,
  \lst@ifdisplaystyle\scriptsize\fi,%
  identifierstyle=,%
  keywords=\bfseries,%
  keywordstyle=\color[rgb]{0, 0.5, 0.5},
  escapeinside={(*}{*)},
  literate={~}{{$\sim$}}1 {==}{{=\,=}}2,
  alsoother={$},
  alsoletter={.<-},%
otherkeywords={!,!=,~,$,*,\&,\%/\%,\%*\%,\%\%,<-,<<-,/},
  numbers=left,
  numberstyle={\tiny}
}

\renewenvironment{Schunk}{}{}
\renewenvironment{Sinput}{}{}
\let\Sinput\relax
\let\Scode\relax
\let\Soutput\relax
\lstnewenvironment{Sinput}{\lstset{style=Rinput}}{}
\lstnewenvironment{Scode}{\lstset{style=Rinput}}{}
\lstnewenvironment{Soutput}{\lstset{style=Routput}}{}
%%end paste in from guidePreambleSweavel.tex


\lstset{tabsize=2, breaklines=true,style=Rinput,breakatwhitespace=true}

\fvset{listparameters={\setlength{\topsep}{0em}}}
\def\Sweavesize{\normalsize}
\def\Rcolor{\color{black}}
\def\Rbackground{\color[gray]{0.90}}

\usepackage[natbibapa]{apacite}
\providecommand*{\code}[1]{\texttt{#1}}

\usepackage{xcolor}
\definecolor{light-gray}{gray}{0.90}
\usepackage{realboxes}
\providecommand*{\code}[1]{\texttt{#1}}
\renewcommand{\code}[1]{
\Colorbox{light-gray}{#1}
}

\definecolor{darkblue}{HTML}{1e2277}
\hypersetup{colorlinks, linkcolor=, citecolor=darkblue, urlcolor=darkblue}

\mode<presentation>

\usetheme{Antibes}

% get rid of navigation symbols at bottom of slide
\beamertemplatenavigationsymbolsempty

% In document Latex options:

\expandafter\def\expandafter\insertshorttitle\expandafter{%
 \insertshorttitle\hfill\insertframenumber\,/\,\inserttotalframenumber}

\setbeamertemplate{frametitle continuation}[from second]
\renewcommand\insertcontinuationtext{...}

%\usepackage{handoutWithNotes}
%\pgfpagesuselayout{3 on 1 with notes}[letterpaper, border shrink=5mm]

\makeatother

\begin{document}
\input{winteR-7-merge-concordance}


% In document Latex options:
\fvset{listparameters={\setlength{\topsep}{0em}}}



\title[WinteR - merge]{WinteR Statistical Workshop}
\subtitle[]{Merge}

\author{Zack Roman\inst{1,2}}

\institute[K.U.]{\inst{1}Center for Research Methods and Data Analysis \\\smallskip
                \inst{2}Department of Psychology}



\date{2017}

\makebeamertitle

\AtBeginSubsection[]{
  \frame<beamer>{ 
    \frametitle{Outline}
    \tableofcontents[currentsubsection] 
  }
}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Outline}

\tableofcontents{}

\end{frame}


\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Goals of This Session}

\textbf{Conceptual:}
\begin{itemize}
\item Types of merges
\item Merging vocabulary
\item When to use merges 
\end{itemize}
\textbf{Skill Building:}
\begin{itemize}
\item Practicing merging variants
\item Different implementations of merging in R
\item Dangers associated with improper merging and how to avoid them

\end{itemize}
\end{frame}


%%%Begin Slides

\section{What is Merging}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Small Example}

\begin{Schunk}
\begin{Sinput}
 authors
\end{Sinput}
\begin{Soutput}
   surname nationality deceased
1    Tukey          US      yes
2 Venables   Australia       no
3  Tierney          US       no
4   Ripley          UK       no
5   McNeil   Australia       no
\end{Soutput}
\begin{Sinput}
 books
\end{Sinput}
\begin{Soutput}
      name                     title     other_author
1    Tukey Exploratory Data Analysis             <NA>
2 Venables Modern Applied Statistics           Ripley
3  Tierney                 LISP-STAT             <NA>
4   Ripley        Spatial Statistics             <NA>
5   Ripley     Stochastic Simulation             <NA>
6   McNeil Interactive Data Analysis             <NA>
7   R Core      An Introduction to R Venables & Smith
\end{Soutput}
\begin{Sinput}
 merge(x = authors, y = books, by.x = "surname", by.y = "name")
\end{Sinput}
\begin{Soutput}
   surname nationality deceased                     title
1   McNeil   Australia       no Interactive Data Analysis
2   Ripley          UK       no        Spatial Statistics
3   Ripley          UK       no     Stochastic Simulation
4  Tierney          US       no                 LISP-STAT
5    Tukey          US      yes Exploratory Data Analysis
6 Venables   Australia       no Modern Applied Statistics
  other_author
1         <NA>
2         <NA>
3         <NA>
4         <NA>
5         <NA>
6       Ripley
\end{Soutput}
\end{Schunk}
\end{frame}




\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Merge Arguments}

\begin{Schunk}
\begin{Sinput}
 merge(x, y, by.x, by.y, by, incomparables, sort, all.x, all.y, all )
\end{Sinput}
\end{Schunk}

\begin{enumerate}
\item \code{x} Specifies the left data set
\item \code{y} Specifies the right data set
\item \code{by.x, by.y, by} specifies the key as a character
  string. \code{by} is common to both \code{x} and \code{y}.
\item \code{incomparables} provides values in the key to not be used
  for matching, such as NA, blank space, or NaN (not a number).
\item \code{sort} Logical (TRUE or FALSE), sorts the output 
\item \code{all.x, all.y, all} Logical, will help us determine the behavior of the merge. We will talk more about this as we go
\end{enumerate}
\end{frame}


\section{Types of Merges}
\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Binding is not a merging}
\begin{itemize}
  \item The functions \code{rbind()} and \code{cbind()} can be used to
    ``stack'' matrices on top of each other (rows bound together), or
    place them side by side (columns bound together)
\item Binding puts data sets together, but if the rows (or columns) are not in
  exactly the same order, it will corrupt the result. Binding two data
  sets is not merging
\item Merging takes into account a "Key" variable (typically an ID \#
  or Name), so that the correct rows are aligned with each other.
\end{itemize}
\end{frame}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{SQL Terminology}

\begin{itemize}
  \item SQL = ``Structured Query Language''. Very widely used general
    purpose data-base framework.
  \item R merge developed in isolation, used different terminology.
  \item Next we show that the SQL terms ``left join'', ``inner join''
    and so forth can be achieved by properly setting the value of the
    merges \code{all} parameter (\code{all}, \code{all.x}, and
    \code{all.y})
\end{itemize}
\end{frame}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Left Join} 

The "Left Join" is used when the goal data set should \textbf{only} have rows that
are present in X. The key variable is used to scan Y for matches,
which are then merged with the X rows.

\begin{Schunk}
\begin{Sinput}
 dat_legs
\end{Sinput}
\begin{Soutput}
  animal legs
1    dog    4
2   cats    4
3  human    2
4  snake    0
5   tree    0
\end{Soutput}
\begin{Sinput}
 dat_fur
\end{Sinput}
\begin{Soutput}
  animal    fur
1    dog    yes
2   cats Mostly
3  human     No
4   bird     No
\end{Soutput}
\begin{Sinput}
 merge(x = dat_legs, y = dat_fur, by = "animal", all.x = TRUE)
\end{Sinput}
\begin{Soutput}
  animal legs    fur
1   cats    4 Mostly
2    dog    4    yes
3  human    2     No
4  snake    0   <NA>
5   tree    0   <NA>
\end{Soutput}
\end{Schunk}

Setting "all.x" to \textbf{TRUE} produces an "Inner Join". The output
data will only contain rows that have matching key values on
\textbf{both} input data sets.
\end{frame}

\begin{frame}[containsverbatim]
\frametitle{Left Join}
\begin{figure}
\includegraphics[]{importfigs/left_join_ven.pdf}
\end{figure}
\end{frame}



\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Left Join Switched}
Let's do a Left Join again, but switch the data sets.
\begin{Schunk}
\begin{Sinput}
 dat_legs
\end{Sinput}
\begin{Soutput}
  animal legs
1    dog    4
2   cats    4
3  human    2
4  snake    0
5   tree    0
\end{Soutput}
\begin{Sinput}
 dat_fur
\end{Sinput}
\begin{Soutput}
  animal    fur
1    dog    yes
2   cats Mostly
3  human     No
4   bird     No
\end{Soutput}
\begin{Sinput}
 merge(x = dat_fur, y = dat_legs, by = "animal", all.x = TRUE)
\end{Sinput}
\begin{Soutput}
  animal    fur legs
1   bird     No   NA
2   cats Mostly    4
3    dog    yes    4
4  human     No    2
\end{Soutput}
\end{Schunk}
\end{frame}


\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Situations calling for Left Join}
\begin{itemize}
\item You want to investigate the relationship between fur and legs in
  animals
\item You have a data set of the animals you are interested in and
  their fur status
\item You obtain a list of \textbf{all} animals legs count
\begin{itemize}
\item Key = Animal Name
\item Output data is the length of the fur data set
\end{itemize}
\item You want to investigate the effect of tuition on retention rate
  in Florida
\item You have Floridian school tuition rates data set
\item You obtain a nationwide data set of retention rates
\begin{itemize}
\item Key = School Name
\item Output data is the length of the tuition rates data set
\end{itemize}
\end{itemize}
\end{frame}


\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Inner Join}

The "Inner join" is used when the goal data set should only have
rows that have keys in both the X and Y data.

\begin{Schunk}
\begin{Sinput}
 dat_legs
\end{Sinput}
\begin{Soutput}
  animal legs
1    dog    4
2   cats    4
3  human    2
4  snake    0
5   tree    0
\end{Soutput}
\begin{Sinput}
 dat_fur
\end{Sinput}
\begin{Soutput}
  animal    fur
1    dog    yes
2   cats Mostly
3  human     No
4   bird     No
\end{Soutput}
\begin{Sinput}
 merge(x = dat_legs, y = dat_fur, by = "animal", all = FALSE)
\end{Sinput}
\begin{Soutput}
  animal legs    fur
1   cats    4 Mostly
2    dog    4    yes
3  human    2     No
\end{Soutput}
\end{Schunk}

Setting "all" to \textbf{FALSE} produces an "Inner Join". The output
data will only contain rows that have matching key values on
\textbf{both} input data sets.
\end{frame}


\begin{frame}[containsverbatim]
\frametitle{Inner Join}
\begin{figure}
\includegraphics[]{importfigs/inner_join.pdf}
\end{figure}
\end{frame}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Qualities of Inner Joins}
\begin{itemize}
\item Pro, result data set will be more complete than other merges. 
\item Con, result data set looses more information than other merges.
\end{itemize}
\end{frame}





\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Full Join}

Full Join keeps all data rows, filling in unmatched rows with missing
values.
\begin{Schunk}
\begin{Sinput}
 dat_legs
\end{Sinput}
\begin{Soutput}
  animal legs
1    dog    4
2   cats    4
3  human    2
4  snake    0
5   tree    0
\end{Soutput}
\begin{Sinput}
 dat_fur
\end{Sinput}
\begin{Soutput}
  animal    fur
1    dog    yes
2   cats Mostly
3  human     No
4   bird     No
\end{Soutput}
\end{Schunk}
\end{frame}


\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Full Join}
\begin{Schunk}
\begin{Sinput}
 merge(x = dat_legs, y = dat_fur, by = "animal", all = TRUE)
\end{Sinput}
\begin{Soutput}
  animal legs    fur
1   bird   NA     No
2   cats    4 Mostly
3    dog    4    yes
4  human    2     No
5  snake    0   <NA>
6   tree    0   <NA>
\end{Soutput}
\end{Schunk}
\end{frame}


\begin{frame}[containsverbatim]
\frametitle{Full Join}
\begin{figure}
\includegraphics[]{importfigs/full_join.pdf}
\end{figure}
\end{frame}


\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Properties of Full Joins}
\begin{itemize}
\item You want an output set with all cases from both data sets
\item There will be lots of "missing" values
\item You don't loose anything, but working with the data is harder
\begin{itemize}
\item Need to subset before plotting 
\item Need to deal with potentially large missing proportion
\end{itemize}
\end{itemize}
\end{frame}



\section{Practice}
\begin{frame}[containsverbatim]
\frametitle{Practice}

\begin{Schunk}
\begin{Sinput}
 dat1
\end{Sinput}
\begin{Soutput}
  Company Earnings
1       A   126345
2       B   492012
3       C   234512
4       D   -28124
5       E   128675
\end{Soutput}
\begin{Sinput}
 dat2
\end{Sinput}
\begin{Soutput}
  Company    Region
1       A   Midwest
2       B Southeast
3       C      West
4       F     North
\end{Soutput}
\end{Schunk}
Can you:
\begin{itemize}
\item Left Join the data so we have all Earnings in the Output set.
\item Left Join the data so we have all Regions in the Output set.
\item Inner Join the data so we have no missing data.
\item Full Join the data so we have everything in the Output set. 
\end{itemize}
\end{frame}




\begin{frame}[containsverbatim]
\frametitle{Practice: Answer 1}
\begin{Schunk}
\begin{Sinput}
 merge(x = dat1, y = dat2, by = "Company", all.x = TRUE)
\end{Sinput}
\begin{Soutput}
  Company Earnings    Region
1       A   126345   Midwest
2       B   492012 Southeast
3       C   234512      West
4       D   -28124      <NA>
5       E   128675      <NA>
\end{Soutput}
\end{Schunk}
\begin{itemize}
\item Left Join the data so we have all Earnings in the Output set.
\end{itemize}
\end{frame}



\begin{frame}[containsverbatim]
\frametitle{Practice: Answer 2}
\begin{Schunk}
\begin{Sinput}
 merge(x = dat2, y = dat1, by = "Company", all.x = TRUE)
\end{Sinput}
\begin{Soutput}
  Company    Region Earnings
1       A   Midwest   126345
2       B Southeast   492012
3       C      West   234512
4       F     North       NA
\end{Soutput}
\end{Schunk}
\begin{itemize}
\item Left Join the data so we have all Regions in the Output set.
\end{itemize}
\end{frame}


\begin{frame}[containsverbatim]
\frametitle{Practice: Answer 3}
\begin{Schunk}
\begin{Sinput}
 merge(x = dat1, y = dat2, by = "Company", all = FALSE)
\end{Sinput}
\begin{Soutput}
  Company Earnings    Region
1       A   126345   Midwest
2       B   492012 Southeast
3       C   234512      West
\end{Soutput}
\end{Schunk}
\begin{itemize}
\item Inner Join the data so we have no missing data.
\end{itemize}
\end{frame}


\begin{frame}[containsverbatim]
\frametitle{Practice: Answer 4}
\begin{Schunk}
\begin{Sinput}
 merge(x = dat1, y = dat2, by = "Company", all = TRUE)
\end{Sinput}
\begin{Soutput}
  Company Earnings    Region
1       A   126345   Midwest
2       B   492012 Southeast
3       C   234512      West
4       D   -28124      <NA>
5       E   128675      <NA>
6       F       NA     North
\end{Soutput}
\end{Schunk}
\begin{itemize}
\item Full Join the data so we have everything in the Output set. 
\end{itemize}
\end{frame}



\section{Merging Long Data: Multiple IDs}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Longitudinal Data}

\begin{itemize}
\item Data comes in 2 typical formats
\begin{enumerate}
\item Wide: Columns that describe units of observation (one row per state,
  or per school, or per child)
  
  \begin{tabular}{rr}
    state & region\tabularnewline
    \hline
    Alabama & south\tabularnewline
    Alaska & north\tabularnewline
    $\vdots$ & \tabularnewline
  \end{tabular}
\item Long: Repeated observations, several times for each unit. 
  
  \begin{tabular}{rrr}
year & state & poverty\tabularnewline
\hline
2000 & Alabama & 13\tabularnewline
2001 & Alabama & 12\tabularnewline
$\vdots$ & & \tabularnewline
2017 & Wisconsin & 11\tabularnewline
\end{tabular}
  
\end{enumerate}
\item We often want to merge the information about the units from the
  wide format onto the longitudinal data that is in the long format.

\end{itemize}
\end{frame}



\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Example: Merging Wide data onto Longitudinal Data}

The longitudinal data is about children measured at 3 time points
\begin{Schunk}
\begin{Sinput}
 dat_long
\end{Sinput}
\begin{Soutput}
  child_id Time FSIQ
1      110    1   98
2      110    2  102
3      110    3  104
4      210    1   89
5      210    2   91
6      210    3   95
\end{Soutput}
\end{Schunk}

Separate data about the education of parents is available
for some children
\begin{Schunk}
\begin{Sinput}
 dat_edu
\end{Sinput}
\begin{Soutput}
  child_id par_edu
1      210      BA
2      110      HS
\end{Soutput}
\end{Schunk}
\end{frame}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Longitudinal Data: Long}

\begin{Schunk}
\begin{Sinput}
 merge(x = dat_long, y = dat_edu, by = "child_id", all = TRUE)
\end{Sinput}
\begin{Soutput}
  child_id Time FSIQ par_edu
1      110    1   98      HS
2      110    2  102      HS
3      110    3  104      HS
4      210    1   89      BA
5      210    2   91      BA
6      210    3   95      BA
\end{Soutput}
\end{Schunk}
\begin{itemize}
  \item This is a full join
  \item No problems encountered, result \emph{seems} adequate. 
\end{itemize} 
\end{frame}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Points of caution in the full join}
    \begin{enumerate}
      \item If information about some families is missing from the
        wide data, then missing values will be created in the result
 
        Example:
        
        We change the wide data by removing one child

\begin{Schunk}
\begin{Soutput}
  child_id par_edu
1      210      BA
\end{Soutput}
\end{Schunk}

\begin{Schunk}
\begin{Sinput}
 merge(x = dat_long, y = dat_edu2, by = "child_id", all = TRUE)
\end{Sinput}
\begin{Soutput}
  child_id Time FSIQ par_edu
1      110    1   98    <NA>
2      110    2  102    <NA>
3      110    3  104    <NA>
4      210    1   89      BA
5      210    2   91      BA
6      210    3   95      BA
\end{Soutput}
\end{Schunk}

\framebreak
    \item If wide data includes information about children/families
        that are not tracked in the long data, then the full join will
        create ``extra'' all missing lines in the longitudinal part.
    
    Example:
    

We only change \code{dat\_edu} by inserting additional rows for some
children. 
    
\begin{Schunk}
\begin{Soutput}
  child_id par_edu
1      210      BA
2      110      HS
3      400      ES
4      501      HS
\end{Soutput}
\end{Schunk}

Why would this happen in real life? Suppose these are child/parent
data rows from a different study in which some of the children
participated.  

\framebreak
\begin{Schunk}
\begin{Sinput}
 merge(x = dat_long, y = dat_edu2, by = "child_id", all = TRUE)
\end{Sinput}
\begin{Soutput}
  child_id Time FSIQ par_edu
1      110    1   98      HS
2      110    2  102      HS
3      110    3  104      HS
4      210    1   89      BA
5      210    2   91      BA
6      210    3   95      BA
7      400   NA   NA      ES
8      501   NA   NA      HS
\end{Soutput}
\end{Schunk}
\framebreak

\item Some users may prefer to think of this as a left join, keeping
  only rows about children in a study (and omitting rows about families
  of children who are not in the study)
  
\begin{Schunk}
\begin{Sinput}
 merge(x = dat_long, y = dat_edu2, by = "child_id", all.x = TRUE, all.y = FALSE)
\end{Sinput}
\begin{Soutput}
  child_id Time FSIQ par_edu
1      110    1   98      HS
2      110    2  102      HS
3      110    3  104      HS
4      210    1   89      BA
5      210    2   91      BA
6      210    3   95      BA
\end{Soutput}
\end{Schunk}
\end{enumerate}
\end{frame}    
        
\begin{frame}[containsverbatim]
\frametitle{Longitudinal Data: Long Data by Long Data}
\begin{Schunk}
\begin{Sinput}
 dat_long1
\end{Sinput}
\begin{Soutput}
  child_id Time FSIQ
1      110    1   98
2      110    2  102
3      110    3  104
4      210    1   89
5      210    2   91
6      210    3   95
\end{Soutput}
\begin{Sinput}
 dat_long2
\end{Sinput}
\begin{Soutput}
  child_id Time Reaction
1      210    1     0.34
2      210    2     0.28
3      210    3     0.19
4      110    1     0.33
5      110    2     0.32
6      110    3     0.28
\end{Soutput}
\end{Schunk}
Notice here, the dangers are repeating ID's in both data sets.
\end{frame}


\begin{frame}[containsverbatim]
\frametitle{Longitudinal Data: Long Data by Long Data}
\begin{Schunk}
\begin{Sinput}
 head(merge(x = dat_long1, y = dat_long2, by = "child_id", all.x = TRUE), 12)
\end{Sinput}
\begin{Soutput}
   child_id Time.x FSIQ Time.y Reaction
1       110      1   98      1     0.33
2       110      1   98      2     0.32
3       110      1   98      3     0.28
4       110      2  102      1     0.33
5       110      2  102      2     0.32
6       110      2  102      3     0.28
7       110      3  104      1     0.33
8       110      3  104      2     0.32
9       110      3  104      3     0.28
10      210      1   89      1     0.34
11      210      1   89      2     0.28
12      210      1   89      3     0.19
\end{Soutput}
\end{Schunk}
This is \textbf{WRONG!!!} look closely.
\end{frame}


\begin{frame}[containsverbatim]
\frametitle{Longitudinal Data: Long Data by Long Data}
To solve our problem we provide multiple Keys to the "by" argument:
\begin{Schunk}
\begin{Sinput}
 merge(x = dat_long1, y = dat_long2, by = c("child_id", "Time"), all.x = TRUE)
\end{Sinput}
\begin{Soutput}
  child_id Time FSIQ Reaction
1      110    1   98     0.33
2      110    2  102     0.32
3      110    3  104     0.28
4      210    1   89     0.34
5      210    2   91     0.28
6      210    3   95     0.19
\end{Soutput}
\end{Schunk}
That is much better, notice the fix:
\begin{Schunk}
\begin{Sinput}
 by = c("child_id", "Time")
\end{Sinput}
\end{Schunk}
\end{frame}


\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Longitudinal Data: Long Data by Long Data}
An intuitive way to determine when you need to supply multiple keys to the "by" argument is to ask yourself:
\begin{itemize}
\item Can every occurrence of my ID variable be uniquely identified ?
\item If not, which other variable is necessary to produce an uniquely identified ID ?
\end{itemize}
\end{frame}



\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Longitudinal Data: QUIZ}
Which columns together create the proper uniquely identifiable key set?

\begin{Schunk}
\begin{Sinput}
 dat_nat
\end{Sinput}
\begin{Soutput}
   ID Year Quarter population illnesses
1 USA 1990      Q1  10.585529  97.15840
2 USA 1990      Q2  10.709466  90.80678
3 USA 1991      Q1   9.890697  98.83752
4 USA 1991      Q2   9.546503 118.17312
5  UK 1990      Q1  10.605887 103.70628
6  UK 1990      Q2   8.182044 105.20216
7  UK 1991      Q1  10.630099  92.49468
8  UK 1991      Q2   9.723816 108.16900
\end{Soutput}
\end{Schunk}
\end{frame}


\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Longitudinal Data: A Useful way to Identify Keys}
\begin{Schunk}
\begin{Sinput}
 table(dat_nat$ID)
\end{Sinput}
\begin{Soutput}
 UK USA 
  4   4 
\end{Soutput}
\end{Schunk}
Not unique, we need another key
\begin{Schunk}
\begin{Sinput}
 table(dat_nat$ID, dat_nat$Quarter)
\end{Sinput}
\begin{Soutput}
      Q1 Q2
  UK   2  2
  USA  2  2
\end{Soutput}
\end{Schunk}
getting closer
\begin{Schunk}
\begin{Sinput}
 table(dat_nat$ID, dat_nat$Quarter, dat_nat$Year)
\end{Sinput}
\begin{Soutput}
, ,  = 1990

     
      Q1 Q2
  UK   1  1
  USA  1  1

, ,  = 1991

     
      Q1 Q2
  UK   1  1
  USA  1  1
\end{Soutput}
\end{Schunk}
Winner! Each data point can be uniquely identified as being collected from a country, during a year, and a quarter. 
\end{frame}


\section{Typical Issues and How to Avoid Them}
\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Different Key Names}
\begin{Schunk}
\begin{Sinput}
 head(datX)
\end{Sinput}
\begin{Soutput}
   ID Year Quarter       pop illnesses
1 USA 1990      Q1  9.113642  84.02290
2 USA 1990      Q2  9.668422 118.05098
3 USA 1991      Q1 11.120713  95.18353
4 USA 1991      Q2 10.298724 106.20380
5  UK 1990      Q1 10.779622 106.12123
6  UK 1990      Q2 11.455785  98.37689
\end{Soutput}
\begin{Sinput}
 head(datY)
\end{Sinput}
\begin{Soutput}
  Country year Semester percipitation      cars
1     USA 1990       Q1     12.049190 111.28511
2     USA 1990       Q2     11.632446  76.19642
3     USA 1991       Q1     10.254271  89.39734
4     USA 1991       Q2     10.491188 109.37141
5      UK 1990       Q1      9.675913 108.54452
6      UK 1990       Q2      8.337950 114.60729
\end{Soutput}
\end{Schunk}
\end{frame}








\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Different Key Names}
\begin{Schunk}
\begin{Sinput}
 head(datX)
\end{Sinput}
\begin{Soutput}
   ID Year Quarter       pop illnesses
1 USA 1990      Q1 10.583188 106.91171
2 USA 1990      Q2  8.693201 108.23795
3 USA 1991      Q1  9.459614 121.45065
4 USA 1991      Q2 11.947693  76.53056
5  UK 1990      Q1 10.053590 101.49592
6  UK 1990      Q2 10.351663  86.57469
\end{Soutput}
\begin{Sinput}
 head(datY)
\end{Sinput}
\begin{Soutput}
  Country year Semester percipitation      cars
1     USA 1990       Q1      9.413120  89.50647
2     USA 1990       Q2      8.167623 123.30512
3     USA 1991       Q1     10.888139 114.02705
4     USA 1991       Q2     11.593488 109.42601
5      UK 1990       Q1     10.516855 108.26258
6      UK 1990       Q2      8.704328  91.88460
\end{Soutput}
\end{Schunk}
\end{frame}


\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Different Key Names}
\begin{Schunk}
\begin{Sinput}
 merge(x = datX, y = datY, by.x = c("ID", "Year", "Quarter"), by.y = c("Country", "year", "Semester"),all = TRUE)
\end{Sinput}
\begin{Soutput}
   ID Year Quarter       pop illnesses percipitation      cars
1  UK 1990      Q1 10.053590 101.49592     10.516855 108.26258
2  UK 1990      Q2 10.351663  86.57469      8.704328  91.88460
3  UK 1991      Q1  9.329023 105.53303     10.054616 104.76248
4  UK 1991      Q2 10.277954 115.89963      9.215351 110.21258
5 USA 1990      Q1 10.583188 106.91171      9.413120  89.50647
6 USA 1990      Q2  8.693201 108.23795      8.167623 123.30512
7 USA 1991      Q1  9.459614 121.45065     10.888139 114.02705
8 USA 1991      Q2 11.947693  76.53056     11.593488 109.42601
\end{Soutput}
\end{Schunk}
\end{frame}



\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Matching Missing}
\begin{Schunk}
\begin{Sinput}
 datX
\end{Sinput}
\begin{Soutput}
   ID cars      fear
1 111    6  90.61873
2 112    5  97.35806
3  NA    7  91.15475
4 114    6  94.99807
5 115    5 106.76902
6 116    5 114.09072
7  NA    9 109.50524
\end{Soutput}
\begin{Sinput}
 datY
\end{Sinput}
\begin{Soutput}
   ID pets
1 111    5
2  NA    4
3 113    4
4 114    8
5 115    6
6  NA    4
7 117    7
\end{Soutput}
\end{Schunk}
\end{frame}


\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Matching Missing:The Problem}
\begin{Schunk}
\begin{Sinput}
 merge(x = datX, y = datY, by = "ID", all.x = TRUE)
\end{Sinput}
\begin{Soutput}
   ID cars      fear pets
1 111    6  90.61873    5
2 112    5  97.35806   NA
3 114    6  94.99807    8
4 115    5 106.76902    6
5 116    5 114.09072   NA
6  NA    7  91.15475    4
7  NA    7  91.15475    4
8  NA    9 109.50524    4
9  NA    9 109.50524    4
\end{Soutput}
\end{Schunk}
\textbf{Oops!} That is a dangerous outcome:  NA columns were merged together
\end{frame}



\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Matching Missing:The Remedy}
\code{incomparables} to the rescue
\begin{Schunk}
\begin{Sinput}
 merge(x = datX, y = datY, by = "ID", all= FALSE, incomparables = "NA")
\end{Sinput}
\begin{Soutput}
   ID cars      fear pets
1 111    6  90.61873    5
2 114    6  94.99807    8
3 115    5 106.76902    6
\end{Soutput}
\end{Schunk}
That is much better! Always remember to use the \code{incomparables} argument if you have any missing data on keys.
\end{frame}

\section{Further Help and Resources}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Kutils::mergeCheck}
\begin{Schunk}
\begin{Sinput}
 df1
\end{Sinput}
\begin{Soutput}
  id          x
1  1 -0.9806329
2  2  0.6873321
3  3 -0.5050435
4  4  2.1577198
5  5 -0.5997976
6  6 -0.6945467
7  7  0.2239254
\end{Soutput}
\begin{Sinput}
 df2
\end{Sinput}
\begin{Soutput}
  id          x
1  2 -1.1562233
2  3  0.4224185
3  4 -1.3247553
4  5  0.1410843
5  6 -0.5360480
6  9 -0.3116061
7 10  1.5561096
\end{Soutput}
\end{Schunk}
\end{frame}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Kutils::mergeCheck}
\begin{Schunk}
\begin{Sinput}
 library(kutils)
 mergeCheck(df1, df2, by = "id")
\end{Sinput}
\begin{Soutput}
Merge difficulties detected

Unmatched cases from df1 and df2 :
df1 
  id          x
1  1 -0.9806329
7  7  0.2239254
df2 
  id          x
6  9 -0.3116061
7 10  1.5561096
\end{Soutput}
\end{Schunk}
\begin{itemize}
\item mergeCheck alerts you to potential merging issues
\item ID 1 and 7 in the X data frame dont have matching Y IDs
\item Further, ID 9 and 10, in the Y data frame dont have matching X IDs
\end{itemize}
\end{frame}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Kutils::mergeCheck}
\begin{Schunk}
\begin{Sinput}
 df1
\end{Sinput}
\begin{Soutput}
  idx           x
1   1 -0.44803329
2   2  0.32112354
3   3 -1.23017225
4   4 -1.32405869
5   5  1.26124227
6  NA  1.31923172
7 NaN -0.08075376
\end{Soutput}
\begin{Sinput}
 df2
\end{Sinput}
\begin{Soutput}
  idy           x
1   2 -0.50508981
2   3 -0.05215359
3   4  0.62886063
4   5  2.18000240
5   6 -0.06901731
6   9  1.54486360
7  10  1.32145202
\end{Soutput}
\end{Schunk}
\end{frame}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Kutils::mergeCheck}
\begin{Schunk}
\begin{Sinput}
 mergeCheck(df1, df2, by.x = "idx", by.y = "idy")
\end{Sinput}
\begin{Soutput}
Merge difficulties detected

Unacceptable key values
df1 
  idx           x
6  NA  1.31923172
7 NaN -0.08075376
Unmatched cases from df1 and df2 :
df1 
  idx           x
1   1 -0.44803329
6  NA  1.31923172
7 NaN -0.08075376
df2 
  idy           x
5   6 -0.06901731
6   9  1.54486360
7  10  1.32145202
\end{Soutput}
\end{Schunk}
\begin{itemize}
\item In this situation we are warned of:
\begin{itemize}
\item Unacceptable key values: NA and NaN
\item Again, unmatched IDs: 1,6,7,9,10
\end{itemize}
\end{itemize}
\end{frame}

\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{Kutils::mergeCheck}
Load \code{library(kutils)} and run \code{example(mergeCheck)} to learn more about the function. Our kutils package has much more to offer! check out the kutils help page with \code{help(package = "kutils")}

\end{frame}




\begin{frame}[containsverbatim, allowframebreaks]
\frametitle{More Information}
\begin{itemize}
\item{The CRMDA has a guide available on merges:}
\begin{itemize}
\item \url{https://crmda.ku.edu/guide-41-merge_R_SQL}
\end{itemize}
\end{itemize}

\end{frame}


\end{document}
