Virata manual

merge

Joins another file onto this one side by side, matching on a shared key.

What we have

  • 1:1, m:1 and 1:m, with the relationship actually checked
  • keep(), keepusing(), generate(), nogenerate, assert(), update, replace
  • diagnose, 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 — joinby is 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:

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.

Watch out. A key stored as a number in one file and text in the other is refused rather than quietly matched to nothing — the error tells you how to fix it.

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.

Nearby

append joinby cross collapse contract reshape