merge
Joins another file onto this one side by side, matching on a shared key.
What we have
1:1,m:1and1:m, with the relationship actually checkedkeep(),keepusing(),generate(),nogenerate,assert(),update,replacediagnose, and the notes about spaces, capitals and numbers stored as text- a key that is a number on one side and text on the other is refused, with the fix
Coming later
m:m, which is refused on purpose —joinbyis the honest way to do it
How you write it
merge 1:1 | m:1 | 1:m keyvars using "file" [, keep() assert() keepusing() generate() diagnose]
Square brackets mean optional. A vertical bar means choose one.
What it does
You have people in one file and the districts they live in in another; merge brings the district columns alongside each person. You say up front what shape you expect:
1:1— one row here, one row there. Employee records and their pay.m:1— many rows here, one there. People and their district.1:m— one row here, many there. A household and its members.
Afterwards a new column _merge says, row by row, what happened: 3 for matched, 1 for only in the data you had, 2 for only in the file you brought in. Look at it every time. A merge that silently matched half of what you expected is the most expensive kind of mistake in this manual.
Virata adds diagnostics Stata does not: when keys fail to match only because of trailing spaces, capital letters or a number stored as text, it says so and shows you an example. Add diagnose to list the keys that matched nothing on each side.
Try it
Every example below was run after sysuse vira.sample03, clear.
. keep id salary
The same thing in pandas and in R
pandas
df = df[['id', 'salary']]
R
library(dplyr) df <- df %>% select(id, salary) # This R was run in a real R (4.6.1) and gave the same numbers.
. save "pay.dta", replace
file pay.dta saved
The same thing in pandas and in R
pandas
df.to_stata("pay.dta", write_index=False)
R
library(haven) write_dta(df, "pay.dta") # This R has NOT been run against a real R yet: read it as documentation.
. sysuse vira.sample03, clear
(4,000 observations read from vira.sample03.dta)
The same thing in pandas and in R
R
library(haven)
df <- read_dta("vira.sample03.dta")
# This R has NOT been run against a real R yet: read it as documentation.
. drop salary
The same thing in pandas and in R
pandas
df = df.drop(columns=['salary'])
R
library(dplyr) df <- df %>% select(-c(salary)) # This R was run in a real R (4.6.1) and gave the same numbers.
. merge 1:1 id using "pay.dta"
Result Number of obs
------------------------------------------
not matched 0
from master 0 (_merge==1)
from using 0 (_merge==2)
matched 4,000 (_merge==3)
------------------------------------------
The same thing in pandas and in R
pandas
import pandas as pd
other = pd.read_stata('pay.dta')
df = df.merge(other, on=['id'], how="outer", indicator=True)
R
library(dplyr)
library(haven)
using <- read_dta("pay.dta")
df <- full_join(df, using, by = c("id"))
# This R was run in a real R (4.6.1) and gave the same numbers.
The full built-in help (help merge)
merge 1:1 | m:1 | 1:m keyvars using "file" [, keep() assert() keepusing()
generate() diagnose]
Stata's merge, and its result table, unchanged. Virata adds what the table
does not say:
* a number in one file and text in the other is refused, with the fix
* a repeated key is named, worst first: `id='17' (9 rows)`
* under the table, when unmatched keys would match if trailing or leading
spaces, or capital letters, were ignored, it says so and shows one
* `diagnose` lists the keys that matched nothing on each side
. merge 1:1 id using "lookup.dta", diagnose
matched 2 (_merge==3)
keys only in the master data (2): id='C3', id='F6'
keys only in the using data (1): id='c3'
note: 1 unmatched key would match if capital letters were ignored
(e.g. id='C3' vs id='c3').
A clean merge prints only Stata's table. The notes appear when there is
something to say.