Virata manual

joinby

Pairs up every matching row on both sides — a many-to-many join.

What we have

  • every matching pair on both sides, on any key

Coming later

Nothing known to be missing.

How you write it

joinby keyvars using "file.dta"

Square brackets mean optional. A vertical bar means choose one.

What it does

When both files have several rows per key and you genuinely want every combination. Three doctors in a clinic and four patients gives twelve rows.

merge refuses this deliberately, because it is usually a mistake. joinby is how you say it is not one.

Watch out. The result can be enormous: the number of rows multiplies rather than adds. Count the rows per key first.

Try it

Every example below was run after sysuse vira.sample03, clear.

. keep in 1/20
(3,980 observations deleted)
The same thing in pandas and in R

pandas

import numpy as np

mask = (np.arange(1, len(df) + 1) >= 1) & (np.arange(1, len(df) + 1) <= 20)
df = df[mask].reset_index(drop=True)

R

library(dplyr)
df <- df %>% filter(row_number() >= 1 & row_number() <= 20)

# This R was run in a real R (4.6.1) and gave the same numbers.
. keep id dept
The same thing in pandas and in R

pandas

df = df[['id', 'dept']]

R

library(dplyr)
df <- df %>% select(id, dept)

# This R was run in a real R (4.6.1) and gave the same numbers.
. save "small.dta", replace
file small.dta saved
The same thing in pandas and in R

pandas

df.to_stata("small.dta", write_index=False)

R

library(haven)
write_dta(df, "small.dta")

# This R has NOT been run against a real R yet: read it as documentation.
. joinby dept using "small.dta"
(20 observations joined into 96)
The same thing in pandas and in R

pandas

import pandas as pd

other = pd.read_stata('small.dta')
df = df.merge(other, on=['dept'], how="inner")

R

# no R form for `joinby` yet -- see docs/r-equivalents.md

Nearby

merge append cross collapse contract reshape