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