library(tidyverse)
library(tidystata)
gss24 <- readRDS("gss24.rds")20 Combine datasets
A research project may store respondents’ background characteristics in one table and their questionnaire answers in another. Before combining them, we need to know what identifies a respondent and which respondents the result should retain. Matching the wrong records can produce a plausible-looking table with incorrect information.
By the end of this chapter, you will be able to:
- Stack observations while aligning columns by name.
- Match records using an explicit identifier and check the expected relationship.
- Identify records without a match and distinguish them from missing answers.
- Attach regional summaries, complete absent summary cells, and verify a saved table.
The examples build on Chapter 3, Chapter 6, and Chapter 19. We use the prepared GSS extract throughout. The smaller tables below are selections of actual records from this source; they let us inspect matching behavior without introducing another dataset.
Follow Appendix A, then load the saved extract in your working directory:
All joins in this chapter return a new table. Assignment saves that result; it does not change either source table.
20.1 Stack observations
Suppose two files contain different respondents but the same measurements. Combining them should add rows, with each row still representing one respondent. To demonstrate, divide gss24 into the northeast and all remaining respondents. Keep ID, region, schooling, and family income:
northeast <- gss24 |>
filter(region == "northeast") |>
select(id, region, educ, realinc)
other_respondents <- gss24 |>
filter(region != "northeast" | is.na(region)) |>
select(realinc, id, educ, region)
head(northeast)# A tibble: 6 × 4
id region educ realinc
<int> <fct> <dbl> <dbl>
1 1 northeast 16 43560
2 2 northeast 16 24502.
3 3 northeast 14 43560
4 4 northeast 12 58080
5 5 northeast 13 24502.
6 6 northeast 14 2722.
head(other_respondents)# A tibble: 6 × 4
realinc id educ region
<dbl> <int> <dbl> <fct>
1 3267 196 14 west
2 13612. 197 12 west
3 24502. 198 12 west
4 50820 199 14 west
5 36300 200 9 west
6 43560 201 12 west
other_respondents deliberately puts its columns in a different order. The same names still denote the same variables: educ is completed years of schooling, and realinc records family income in constant 1986 dollars. The second condition explicitly retains missing regions if any occur, so the two tables together cover every respondent. A missing region does not establish that someone lives outside the northeast; those records are simply retained in the remainder for this stacking demonstration.
Dplyr’s bind_rows() takes the tables in the order supplied and stacks their rows. It matches columns by name, not by position.
respondents <- bind_rows(northeast, other_respondents)
head(respondents)# A tibble: 6 × 4
id region educ realinc
<int> <fct> <dbl> <dbl>
1 1 northeast 16 43560
2 2 northeast 16 24502.
3 3 northeast 14 43560
4 4 northeast 12 58080
5 5 northeast 13 24502.
6 6 northeast 14 2722.
nrow(northeast)[1] 616nrow(other_respondents)[1] 3370nrow(respondents)[1] 3986The result contains 616 respondents from the first table followed by 3370 from the second, giving 3986 rows. Family income remains under realinc even though it was the first column in other_respondents. Output columns begin in the first table’s order.
A correct row total alone would not detect two copies of one respondent replacing another. Tidystata’s isid() checks that a named column uniquely identifies rows and contains no missing identifiers:
isid(respondents, id)Variable `id` uniquely identifies the rows.
The confirmation means each respondent ID appears once and none is missing. A repeated or missing identifier would raise an error. The function returns the table invisibly without changing its observations.
Before stacking real files, check both variable names and meanings. Equal names do not guarantee equal units. If a named column occurs in only one input, bind_rows() fills that column with missing values for rows from the other input. It does not infer that two differently named columns mean the same thing.
We can also compare stacked values with the original respondent records. Sort both tables by ID so rows line up. Base R’s all.equal() compares two objects and returns TRUE when they agree, or a description of differences otherwise:
source_respondents <- gss24 |>
select(id, region, educ, realinc) |>
arrange(id)
stacked_respondents <- respondents |>
arrange(id)
all.equal(stacked_respondents, source_respondents)[1] TRUERead the returned result before choosing a comparison rule. Attached labels are attributes, so a description of attribute differences need not mean a respondent value changed. Conversely, TRUE includes the attributes that this comparison checks. To isolate values when descriptive attributes may differ, add check.attributes = FALSE:
all.equal(
stacked_respondents, source_respondents,
check.attributes = FALSE
)[1] TRUEThis value comparison returns TRUE, including agreement in missing answers. Ignoring attributes cannot certify metadata preservation. Keep the source variable descriptions and inspect factor levels when preparing a reusable data file. Use a full comparison when attribute agreement is part of the requirement.
20.2 Match respondents by ID
Now we want to add columns for the same respondents. Create a background table and an education table from GSS. Reverse the education table’s row order using the familiar arrange(desc(id)):
people <- gss24 |>
select(id, age, sex)
education <- gss24 |>
select(id, degree, educ) |>
arrange(desc(id))
head(people)# A tibble: 6 × 3
id age sex
<int> <dbl> <fct>
1 1 33 male
2 2 64 male
3 3 69 female
4 4 19 male
5 5 70 female
6 6 53 male
head(education)# A tibble: 6 × 3
id degree educ
<int> <fct> <dbl>
1 3986 bachelor's 16
2 3985 graduate 16
3 3984 NA 12
4 3983 bachelor's 16
5 3982 associate/junior college 15
6 3981 bachelor's 16
isid(people, id)Variable `id` uniquely identifies the rows.
isid(education, id)Variable `id` uniquely identifies the rows.
Each ID identifies one respondent in both tables. The first displayed rows now refer to different respondents. Combining columns by row position would attach one person’s education to another person’s background. bind_cols() combines columns by position, so it is unsuitable for these reordered tables. We need the IDs to determine the matches.
left_join() adds matching columns from a second table while retaining the observations in the first, or left, table. The first argument below is people, the second is education, and by = "id" explicitly names their matching column.
people_education <- left_join(people, education, by = "id")
head(people_education)# A tibble: 6 × 5
id age sex degree educ
<int> <dbl> <fct> <fct> <dbl>
1 1 33 male bachelor's 16
2 2 64 male graduate 16
3 3 69 female associate/junior college 14
4 4 19 male high school 12
5 5 70 female high school 13
6 6 53 male associate/junior college 14
nrow(people_education)[1] 3986The ID match attaches education to the correct person even though the right table is reversed. It retains the left table’s rows and order. We already checked that IDs are unique and non-missing in both inputs.
Now make that expected matching relationship explicit. relationship = "one-to-one" states that each row in either input should match at most one row in the other. Dplyr stops if matching violates this contract. It does not require every ID to find a match; that needs a separate check.
people_education <- left_join(
people, education, by = "id", relationship = "one-to-one"
)
head(people_education)# A tibble: 6 × 5
id age sex degree educ
<int> <dbl> <fct> <fct> <dbl>
1 1 33 male bachelor's 16
2 2 64 male graduate 16
3 3 69 female associate/junior college 14
4 4 19 male high school 12
5 5 70 female high school 13
6 6 53 male associate/junior college 14
nrow(people_education)[1] 3986isid(people_education, id)Variable `id` uniquely identifies the rows.
The result retains 3986 respondents in the left table’s order and adds degree and educ by matching ID. degree is highest qualification, whereas educ is completed years of schooling. Neither row order in education nor a respondent’s answer determines the match.
A missing answer is possible even when the ID matches. Inspect respondents whose schooling is unavailable:
people_education |>
filter(is.na(educ)) |>
head()# A tibble: 6 × 5
id age sex degree educ
<int> <dbl> <fct> <fct> <dbl>
1 83 65 male less than high school NA
2 122 22 male less than high school NA
3 271 37 male less than high school NA
4 523 NA female less than high school NA
5 648 NA female less than high school NA
6 674 NA male less than high school NA
These rows retain their IDs and background information. Their education records existed in the source table, but the educ cells were already missing. Joining does not fill those answers. The next section checks missing matches directly rather than using an answer column as a substitute.
20.3 Find records without a match
Imagine receiving a health-answer file containing only respondents with a recorded health answer. Construct exactly that table from the existing observations:
health_answers <- gss24 |>
filter(!is.na(health)) |>
select(id, health)
isid(health_answers, id)Variable `id` uniquely identifies the rows.
nrow(health_answers)[1] 3964This table has 3964 rows. Its absent IDs represent people without a recorded health answer in the original extract. They have not disappeared from people.
First use the familiar left join to retain everyone in people:
people_health <- left_join(
people, health_answers, by = "id", relationship = "one-to-one"
)
nrow(people_health)[1] 3986It keeps 3986 respondents and supplies NA for health where no ID matches. Here an unavailable right-hand record leads to a missing joined health cell.
inner_join() adds matching columns but retains only rows with a matching key in both tables. Use the same ID and relationship contract so only the inclusion rule changes:
matched_health <- inner_join(
people, health_answers, by = "id", relationship = "one-to-one"
)
nrow(matched_health)[1] 3964The inner join keeps 3964 respondents. It changes the included respondents, not just the displayed columns. Choose the inclusion rule required by the research question and report exclusions.
To inspect the unmatched people, use anti_join(). It takes the same two tables and matching column, but returns only rows from the left table whose ID is absent on the right. It does not add the right table’s columns:
unmatched_health <- anti_join(people, health_answers, by = "id")
unmatched_health# A tibble: 22 × 3
id age sex
<int> <dbl> <fct>
1 122 22 male
2 505 66 female
3 509 NA male
4 522 NA male
5 674 NA male
6 727 45 female
7 1294 NA female
8 1318 60 male
9 1456 31 female
10 1584 25 NA
# ℹ 12 more rows
nrow(unmatched_health)[1] 22The 22 returned rows identify the people omitted by the inner join. Their columns remain id, age, and sex; anti_join() has selected records rather than attached health answers. Adding this count to the inner join’s row count recovers the left table’s count.
Now check the education table in the same way:
unmatched_education <- anti_join(people, education, by = "id")
nrow(unmatched_education)[1] 0sum(is.na(people_education$educ))[1] 34$ selects the educ column. is.na() marks its missing cells, and sum() counts those TRUE results. There are 0 unmatched education IDs but 34 missing schooling answers. These checks distinguish an absent record from a present record with an unknown value. In another file, both can occur, so inspect IDs before interpreting a missing joined cell.
20.4 Attach a regional summary
Sometimes several observations should share one matching record. We want each respondent alongside the sample’s average schooling for their region. First create one summary row per recorded region, using the grouping lesson’s .by argument:
region_summary <- respondents |>
filter(!is.na(region)) |>
summarise(mean_educ = mean(educ, na.rm = TRUE), .by = region)
region_summary# A tibble: 4 × 2
region mean_educ
<fct> <dbl>
1 northeast 14.4
2 west 14.5
3 midwest 14.0
4 south 14.2
isid(region_summary, region)Variable `region` uniquely identifies the rows.
mean_educ is completed years of schooling averaged across respondents with recorded schooling in each region. Each contributes equally; these are unweighted sample means. na.rm = TRUE excludes missing schooling from the mean without replacing it. The filter excludes unknown regions from the summary, and isid() confirms that each retained region appears once with a non-missing key.
Attach the summary using the shared region category:
respondents_with_region <- left_join(
respondents, region_summary,
by = "region", relationship = "many-to-one"
)
head(respondents_with_region)# A tibble: 6 × 5
id region educ realinc mean_educ
<int> <fct> <dbl> <dbl> <dbl>
1 1 northeast 16 43560 14.4
2 2 northeast 16 24502. 14.4
3 3 northeast 14 43560 14.4
4 4 northeast 12 58080 14.4
5 5 northeast 13 24502. 14.4
6 6 northeast 14 2722. 14.4
nrow(respondents_with_region)[1] 3986isid(respondents_with_region, id)Variable `id` uniquely identifies the rows.
"many-to-one" allows several respondents on the left to match one summary row on the right. Each respondent may match at most one right-hand row. The output still has 3986 respondent rows: educ describes that person’s schooling, while mean_educ repeats the regional comparison value. Repeating a regional mean does not add respondents or turn it into a person’s own measurement. A missing schooling value remains missing even when the regional mean is available. If a respondent had an unknown region, the left join would retain that respondent with a missing regional mean because the summary has no missing-region key.
20.5 Complete summary combinations
A summary can omit combinations that have no contributing records. We can make those combinations visible without adding respondents. Count schooling values within regions after selecting respondents with recorded region and schooling:
schooling_counts <- gss24 |>
filter(!is.na(region), !is.na(educ)) |>
count(region, educ)
head(schooling_counts)# A tibble: 6 × 3
region educ n
<fct> <dbl> <int>
1 northeast 0 1
2 northeast 3 1
3 northeast 4 1
4 northeast 5 2
5 northeast 6 2
6 northeast 7 3
nrow(schooling_counts)[1] 75sum(schooling_counts$n)[1] 3952The result has 75 observed combinations, representing 3952 respondents. Each row now describes a region-schooling cell. educ records completed years of schooling; the observed values are integers from 0 through 20. Our reporting domain includes all 21 integers, written 0:20, within each region. This is a specified domain for this extract, not a rule that every schooling variable must use.
complete() constructs combinations and attaches the existing summary values. Start without replacing any values:
schooling_grid_missing <- schooling_counts |>
complete(region, educ = 0:20)
schooling_grid_missing |>
filter(is.na(n))# A tibble: 9 × 3
region educ n
<fct> <dbl> <int>
1 northeast 1 NA
2 northeast 2 NA
3 midwest 1 NA
4 midwest 2 NA
5 midwest 3 NA
6 midwest 4 NA
7 midwest 5 NA
8 south 1 NA
9 south 4 NA
nrow(schooling_grid_missing)[1] 84levels(schooling_counts$region)[1] "northeast" "midwest" "south" "west" The output exposes nine absent combinations. Their counts are now NA, distinguishing new positions from the observed count rows. A factor contributes all its stored levels to the grid, including unused levels; the displayed levels confirm the four intended regions. The full grid contains four times 21 cells. These rows describe summary positions, not added respondents.
For this count summary, an absent combination means zero contributing respondents. fill = list(n = 0L) supplies an integer zero for n; the named list says which column receives which value. 0L is the integer form of zero:
schooling_grid_zero <- schooling_counts |>
complete(region, educ = 0:20, fill = list(n = 0L))
schooling_grid_zero |>
filter(n == 0)# A tibble: 9 × 3
region educ n
<fct> <dbl> <int>
1 northeast 1 0
2 northeast 2 0
3 midwest 1 0
4 midwest 2 0
5 midwest 3 0
6 midwest 4 0
7 midwest 5 0
8 south 1 0
9 south 4 0
The nine new rows now have zero counts. By default, fill would also replace explicit missing counts already present in the input. This summary has no such counts, but state the safer rule: explicit = FALSE limits replacement to newly created combinations and preserves original missing values.
schooling_grid <- schooling_counts |>
complete(region, educ = 0:20, fill = list(n = 0L), explicit = FALSE)
schooling_grid |>
filter(n == 0)# A tibble: 9 × 3
region educ n
<fct> <dbl> <int>
1 northeast 1 0
2 northeast 2 0
3 midwest 1 0
4 midwest 2 0
5 midwest 3 0
6 midwest 4 0
7 midwest 5 0
8 south 1 0
9 south 4 0
nrow(schooling_grid)[1] 84sum(schooling_grid$n)[1] 3952The final grid has 84 cells, including nine new zeros, while the respondent total remains 3952. A zero says no retained sample respondent occupies that cell. It does not establish zero population prevalence, supply a value for missing schooling, or change the underlying respondent records. This distinction between an absent summary cell and an unknown measurement also explains why the regional-mean join did not fill anyone’s missing schooling.
The related expand() constructs combinations without attaching the existing values. We use complete() because we need the grid and its counts together. The fill argument above also differs from the separate fill() function, which carries values between rows. Carrying a state forward requires evidence that the source records continuing spells or another justified rule. These summary counts supply no such rule, and the school-to-work source in Section 19.6 has no missing monthly states. Completing missing person-month records and filling their states would require a separate application with suitable data and documented recording rules.
20.6 Diagnose an incorrect key
A key must identify the unit we intend to match. Region identifies a regional summary, but it cannot distinguish respondents within the same region. Select the first three actual northeast respondents in ID order:
three_respondents <- gss24 |>
filter(region == "northeast") |>
arrange(id) |>
slice(1:3)
respondent_schooling <- three_respondents |>
select(id, region, educ)
respondent_income <- three_respondents |>
select(id, region, realinc)
respondent_schooling# A tibble: 3 × 3
id region educ
<int> <fct> <dbl>
1 1 northeast 16
2 2 northeast 16
3 3 northeast 14
respondent_income# A tibble: 3 × 3
id region realinc
<int> <fct> <dbl>
1 1 northeast 43560
2 2 northeast 24502.
3 3 northeast 43560
All three share the recorded region “northeast”. Their distinct IDs identify three different respondents. We want each person’s schooling matched to that same person’s family income. The following deliberately incorrect join asks R to match by region instead. It is expected to stop:
left_join(
respondent_schooling, respondent_income,
by = "region", relationship = "one-to-one"
)Error in `left_join()`:
! Each row in `x` must match at most 1 row in `y`.
ℹ Row 1 of `x` matches multiple rows in `y`.
Read the error as a failed assumption about matching: a row on the left matches multiple rows on the right. Without that check, each of the three respondents would match all three income records, creating nine combinations. Those combinations would not be nine respondents, and most would pair different people’s measurements.
Use count() to locate the repeated key values. It returns one row per value and an n column giving the number of records:
respondent_schooling |>
count(region)# A tibble: 1 × 2
region n
<fct> <int>
1 northeast 3
respondent_income |>
count(region)# A tibble: 1 × 2
region n
<fct> <int>
1 northeast 3
isid(respondent_schooling, id)Variable `id` uniquely identifies the rows.
isid(respondent_income, id)Variable `id` uniquely identifies the rows.
The repeated region is legitimate: these are three respondents in one region. ID is unique in each table, so it is the appropriate key here. A key’s meaning must agree across the files being joined; uniqueness alone does not establish that two files use the same ID system.
Keep only the respondent key and income column from the right table, then match by ID:
income_by_id <- respondent_income |>
select(id, realinc)
respondent_comparison <- left_join(
respondent_schooling, income_by_id,
by = "id", relationship = "one-to-one"
)
respondent_comparison# A tibble: 3 × 4
id region educ realinc
<int> <fct> <dbl> <dbl>
1 1 northeast 16 43560
2 2 northeast 16 24502.
3 3 northeast 14 43560
isid(respondent_comparison, id)Variable `id` uniquely identifies the rows.
The repaired result contains 3 respondent rows. Selecting the right table’s needed columns also avoids adding a second copy of region. We corrected the key; discarding duplicate region records would instead discard real respondents, and allowing multiple matches would preserve the incorrect pairings.
NoteOptional: match response positions
An identifier must distinguish the rows being matched. In Section 19.5, one respondent has three item positions. Recreate that long table, retaining unanswered positions, then separate its keys from its responses. Reverse the response table’s order to make positional matching unsuitable.
join_confidence_long <- gss24 |>
select(id, conbus, coneduc, conmedic) |>
pivot_longer(
cols = conbus:conmedic,
names_to = "item", values_to = "response",
values_drop_na = FALSE
)
response_positions <- join_confidence_long |>
select(id, item)
response_values <- join_confidence_long |>
arrange(desc(id), desc(item))
isid(response_positions, id, item)Variables `id`, `item` together uniquely identify the rows.
isid(response_values, id, item)Variables `id`, `item` together uniquely identify the rows.
Supplying both columns to isid() checks that their combination is unique and non-missing. Each confirmation concerns respondent-item positions, not unique respondents. id alone repeats for the three items and cannot identify the intended match.
In by = c("id", "item"), both named columns must agree. Keep the existing one-to-one relationship check:
recombined_responses <- left_join(
response_positions, response_values,
by = c("id", "item"), relationship = "one-to-one"
)
nrow(recombined_responses)[1] 11958identical(recombined_responses, join_confidence_long)[1] TRUEThe result contains 11958 respondent-item positions, including 4146 missing responses. TRUE confirms exact reconstruction, including row order, factor levels, and missing answers. Matching uses the two keys even though the response table was reordered. These positions still represent the original 3986 respondents; neither reshaping nor joining adds people or fills their answers. This is a table-layout check, not evidence that the items form a valid scale.
20.7 Check and save the completed table
Return to the complete respondent table. Start with the checked background-education table, then add the available health answers while retaining every respondent:
joined_gss <- left_join(
people_education, health_answers,
by = "id", relationship = "one-to-one"
)
head(joined_gss)# A tibble: 6 × 6
id age sex degree educ health
<int> <dbl> <fct> <fct> <dbl> <fct>
1 1 33 male bachelor's 16 good
2 2 64 male graduate 16 good
3 3 69 female associate/junior college 14 good
4 4 19 male high school 12 good
5 5 70 female high school 13 good
6 6 53 male associate/junior college 14 good
nrow(joined_gss)[1] 3986isid(joined_gss, id)Variable `id` uniquely identifies the rows.
anti_join(people, joined_gss, by = "id")# A tibble: 0 × 3
# ℹ 3 variables: id <int>, age <dbl>, sex <fct>
anti_join(joined_gss, people, by = "id")# A tibble: 0 × 6
# ℹ 6 variables: id <int>, age <dbl>, sex <fct>, degree <fct>, educ <dbl>,
# health <fct>
Each output row represents one original GSS respondent. The table has 3986 rows and 6 columns. Both unmatched-ID checks return zero rows: no original respondent was lost, and no new respondent was introduced. The uniqueness check also rules out repeated IDs.
Check row order and missing answers before exporting. Base R’s identical() returns TRUE only when its two inputs agree exactly, including their order and attributes:
identical(joined_gss$id, people$id)[1] TRUEsum(is.na(joined_gss$educ))[1] 34sum(is.na(joined_gss$health))[1] 22The IDs stay in the original order. The 34 missing schooling answers came from matched education records; the 22 missing health cells correspond to the absent IDs in health_answers. The completed table preserves both kinds of unavailable information. Its rows describe this survey sample without applying survey weights.
Use the RDS format from Section 18.3 to preserve factors and their levels. Save under a new filename, leaving the prepared gss24.rds extract intact, then read the saved object back:
saveRDS(joined_gss, "gss-person-education-health.rds")
restored_gss <- readRDS("gss-person-education-health.rds")
identical(restored_gss, joined_gss)[1] TRUESaving is silent. The final TRUE confirms that the restored table agrees with the completed table, including values, row order, factor classes, and category levels. The filename is relative to your working directory. Keep it with the script that constructs the table so that you can reproduce the checks.
20.8 Review
bind_rows()stacks observations and aligns columns by name; check that meanings and units also agree.isid()checks unique, non-missing identifiers before and after matching.left_join()retains left-hand observations;inner_join()retains matching observations. State the key and expected relationship.anti_join()finds left-hand records without a matching key. An absent record differs from a missing answer in a matched record.count()helps diagnose repeated keys. Match at the intended observation level rather than discarding legitimate records.complete()exposes absent summary combinations; filling new count cells with zero does not replace unknown respondent measurements.saveRDS()andreadRDS()preserve a completed table;identical()checks the restored object against it.- Optional:
isid()and explicit join keys can identify respondent-item positions through a combination of columns.
We have imported, reorganized, and combined research data. Chapter 21 next keeps regional respondent tables inside a list column while preserving their records. The later analysis part begins with Chapter 22.
20.9 Exercises
Each exercise starts with a fresh GSS import and recreates its own inputs.
- Stack and verify. Split the GSS into the northeast and remaining respondents, explicitly retaining unknown regions in the second group. Keep
id,region,educ, andrealinc, with the second table’s columns in another order. Stack, check IDs and row totals, and compare sorted values with the source while allowing descriptive attributes to differ. Explain why columns align correctly and what the attribute-excluding comparison cannot verify. - Match and diagnose keys. Recreate background and education tables, reverse the education rows, join by ID, and compare with the source. Explain the danger of matching by row position. Then select the first three northeast respondents in ID order and diagnose the incorrect region-key join. Repair it, check source values, and explain why dropping repeated regions would discard real respondents.
- Compare inclusion and unavailable answers. Construct a health table containing only recorded answers. Find unmatched background IDs and compare them with missing source health. Compare inner and left join row counts and explain each inclusion rule. Separately join the complete education table and show that missing schooling can remain even with no unmatched IDs.
- Attach a summary and complete a grid. Create one schooling-mean row per recorded region and join it to all respondents with the appropriate relationship check. Verify IDs, row count, source schooling, and retained missing answers. Separately recreate region-schooling counts and complete the four-region grid for schooling 0 through 20. Identify added cells and check count totals before and after completion. Explain the distinct units of the respondent table and summary grid, and why zero summary cells neither replace missing schooling nor establish zero population prevalence.
Worked answers
NoteStack respondents
Start a fresh session with the saved GSS extract:
library(tidyverse)
library(tidystata)
gss24 <- readRDS("gss24.rds")answer_northeast <- gss24 |>
filter(region == "northeast") |>
select(id, region, educ, realinc)
answer_others <- gss24 |>
filter(region != "northeast" | is.na(region)) |>
select(realinc, educ, id, region)
answer_stacked <- bind_rows(answer_northeast, answer_others)
nrow(answer_stacked)[1] 3986isid(answer_stacked, id)Variable `id` uniquely identifies the rows.
answer_original <- gss24 |>
select(id, region, educ, realinc) |>
arrange(id)
all.equal(
arrange(answer_stacked, id), answer_original,
check.attributes = FALSE
)[1] TRUEThe result contains 3986 unique respondents. The final TRUE verifies the data values after placing both tables in the same ID order; the comparison allows descriptive attributes to differ. Columns align by their names, so moving realinc to the front of the second input does not move its values into id. Checking against the source verifies more than the row count alone.
NoteMatch records and repair a key
Restore the prepared input independently:
library(tidyverse)
library(tidystata)
gss24 <- readRDS("gss24.rds")answer_people <- gss24 |>
select(id, age, sex)
answer_education <- gss24 |>
select(id, degree, educ) |>
arrange(desc(id))
isid(answer_people, id)Variable `id` uniquely identifies the rows.
isid(answer_education, id)Variable `id` uniquely identifies the rows.
answer_combined <- left_join(
answer_people, answer_education,
by = "id", relationship = "one-to-one"
)
nrow(answer_combined)[1] 3986isid(answer_combined, id)Variable `id` uniquely identifies the rows.
answer_source <- gss24 |>
select(id, age, sex, degree, educ)
identical(answer_combined, answer_source)[1] TRUEThe 3986-row table agrees exactly with the corresponding source columns, including missing answers and factor levels. Reversing the right table’s order does not alter the matches. A positional combination would instead assume that the first rows represent the same person, which they do not here.
The three-person example uses a different key problem. Recreate its source selection before diagnosing and repairing it:
Load the GSS extract for an independent answer:
library(tidyverse)
library(tidystata)
gss24 <- readRDS("gss24.rds")answer_three <- gss24 |>
filter(region == "northeast") |>
arrange(id) |>
slice(1:3)
answer_schooling <- answer_three |>
select(id, region, educ)
answer_income <- answer_three |>
select(id, region, realinc)
answer_schooling |>
count(region)# A tibble: 1 × 2
region n
<fct> <int>
1 northeast 3
answer_income |>
count(region)# A tibble: 1 × 2
region n
<fct> <int>
1 northeast 3
isid(answer_schooling, id)Variable `id` uniquely identifies the rows.
isid(answer_income, id)Variable `id` uniquely identifies the rows.
answer_income_keyed <- answer_income |>
select(id, realinc)
answer_repaired <- left_join(
answer_schooling, answer_income_keyed,
by = "id", relationship = "one-to-one"
)
answer_repaired# A tibble: 3 × 4
id region educ realinc
<int> <fct> <dbl> <dbl>
1 1 northeast 16 43560
2 2 northeast 16 24502.
3 3 northeast 14 43560
isid(answer_repaired, id)Variable `id` uniquely identifies the rows.
answer_respondent_source <- answer_three |>
select(id, region, educ, realinc)
identical(answer_repaired, answer_respondent_source)[1] TRUERegion occurs 3 times in each input, but ID is unique. The repaired result has 3 rows and agrees exactly with the source columns. Keeping only one record per region would discard two legitimate respondents; it would not identify which income belongs to which respondent. The correct repair preserves the observations and matches their respondent IDs.
NoteInclusion and missing answers
Begin from the prepared file rather than objects from an earlier answer:
library(tidyverse)
library(tidystata)
gss24 <- readRDS("gss24.rds")answer_background <- gss24 |>
select(id, age, sex)
answer_health <- gss24 |>
filter(!is.na(health)) |>
select(id, health)
isid(answer_background, id)Variable `id` uniquely identifies the rows.
isid(answer_health, id)Variable `id` uniquely identifies the rows.
answer_unmatched <- anti_join(answer_background, answer_health, by = "id")
answer_missing_source <- gss24 |>
filter(is.na(health)) |>
select(id, age, sex)
identical(answer_unmatched, answer_missing_source)[1] TRUEnrow(answer_unmatched)[1] 22answer_left <- left_join(
answer_background, answer_health,
by = "id", relationship = "one-to-one"
)
answer_inner <- inner_join(
answer_background, answer_health,
by = "id", relationship = "one-to-one"
)
nrow(answer_left)[1] 3986nrow(answer_inner)[1] 3964The equality check returns TRUE: all 22 unmatched records correspond to missing source health answers. The left join retains all 3986 background records, while the inner join retains the 3964 with recorded health. These counts reflect how we constructed this particular answer file. In other files, an unmatched ID may instead indicate a coding mismatch or incomplete coverage, so inspect the source before assigning a substantive explanation.
Now reconstruct education without excluding unknown schooling, and check record availability separately from answer availability:
answer_education_all <- gss24 |>
select(id, educ)
isid(answer_education_all, id)Variable `id` uniquely identifies the rows.
answer_education_join <- left_join(
answer_background, answer_education_all,
by = "id", relationship = "one-to-one"
)
anti_join(answer_background, answer_education_all, by = "id")# A tibble: 0 × 3
# ℹ 3 variables: id <int>, age <dbl>, sex <fct>
sum(is.na(answer_education_join$educ))[1] 34identical(answer_education_join$educ, gss24$educ)[1] TRUEThe unmatched result has zero rows, yet schooling still has its source missing answers. TRUE confirms preservation of the schooling column in respondent order. A joined NA alone cannot tell us whether a source record was absent or its answer was unknown; the key comparison supplies that distinction.
NoteRegional summaries and complete counts
Restore the GSS extract. This answer creates both a respondent table with attached regional means and a separate summary grid.
library(tidyverse)
library(tidystata)
answer_region_gss <- readRDS("gss24.rds")answer_respondents <- answer_region_gss |>
select(id, region, educ)
answer_region_summary <- answer_respondents |>
filter(!is.na(region)) |>
summarise(mean_educ = mean(educ, na.rm = TRUE), .by = region)
answer_region_summary# A tibble: 4 × 2
region mean_educ
<fct> <dbl>
1 northeast 14.4
2 west 14.5
3 midwest 14.0
4 south 14.2
isid(answer_region_summary, region)Variable `region` uniquely identifies the rows.
answer_region_attached <- left_join(
answer_respondents, answer_region_summary,
by = "region", relationship = "many-to-one"
)
head(answer_region_attached)# A tibble: 6 × 4
id region educ mean_educ
<int> <fct> <dbl> <dbl>
1 1 northeast 16 14.4
2 2 northeast 16 14.4
3 3 northeast 14 14.4
4 4 northeast 12 14.4
5 5 northeast 13 14.4
6 6 northeast 14 14.4
nrow(answer_respondents)[1] 3986nrow(answer_region_attached)[1] 3986isid(answer_region_attached, id)Variable `id` uniquely identifies the rows.
identical(answer_region_attached$id, answer_respondents$id)[1] TRUEidentical(answer_region_attached$educ, answer_respondents$educ)[1] TRUEsum(is.na(answer_region_attached$educ))[1] 34The regional table has one summary per recorded region; the join retains every original respondent. Its repeated means are in years, averaging recorded schooling within each region without survey weights. They do not replace missing individual schooling. The ID and schooling comparisons both return TRUE. A respondent with unknown region would remain with a missing regional mean.
Now answer the original summary-grid task independently from its saved source. Counts have a different row unit from respondents:
Prepare and restore the GSS extract independently:
library(tidyverse)
gss24 <- readRDS("gss24.rds")answer_schooling_counts <- gss24 |>
filter(!is.na(region), !is.na(educ)) |>
count(region, educ)
answer_schooling_grid <- answer_schooling_counts |>
complete(region, educ = 0:20, fill = list(n = 0L), explicit = FALSE)
answer_schooling_grid |>
filter(n == 0)# A tibble: 9 × 3
region educ n
<fct> <dbl> <int>
1 northeast 1 0
2 northeast 2 0
3 midwest 1 0
4 midwest 2 0
5 midwest 3 0
6 midwest 4 0
7 midwest 5 0
8 south 1 0
9 south 4 0
nrow(answer_schooling_counts)[1] 75nrow(answer_schooling_grid)[1] 84sum(answer_schooling_counts$n)[1] 3952sum(answer_schooling_grid$n)[1] 3952The nine new zero cells are schooling years 1 and 2 in the northeast; 1, 2, 3, 4, and 5 in the midwest; and 1 and 4 in the south. The summary grows from 75 to 84 cells, while both totals remain 3,952. These cells record combinations with no contributing sample respondents. They do not supply schooling for anyone with an unknown answer, and the counts cannot establish that the corresponding population groups are absent. The reporting grid covers the observed integer range in this extract; another source requires its own justified domain.
The completed count grid has one row per region-schooling position, whereas answer_region_attached has one row per respondent. The nine additional grid rows therefore cannot be interpreted as nine additional people. The constant total of counts verifies the number of contributing recorded-schooling respondents; it does not include respondents with unknown schooling.