we currently working in a hobby project with huge panel data (with a lot of waves, i.e., measurement timepoints), which can be quite challenging. To get an overview, one idea was to find variables that are common from the current to all consecutive waves.
Explanation with an example:
Wave 1: Var1, Var2, Var3, Var4
Wave 2: Var1, Var2, Var4, Var5
Wave 3: Var1, Var5, Var6, Var7
Here Wave 1: has Var1 and Var2 in common with Wave2, and only Var1 with Wave3. Wave2 has in common with Wave3: Var1 and Var5
Desired output
A tibble (or data.frame) that shows in the rows the wave of interest, and in the column for each consecutive wave which variables are in common.
| Starting | Wave1 | Wave2 | Wave3 |
|---|---|---|---|
| Wave1 | - | Var1, Var2 | Var1 |
| Wave2 | - | - | Var1, Var5 |
| Wave3 | - | - | - |
Simulation data:
pacman::p_load(tidyverse)
wave1 <- tibble(
id = seq_along(1:100),
a = runif(100, 0, 100),
o = runif(100, 0, 100),
x = runif(100, 0, 100),
y = runif(100, 0, 100),
z = runif(100, 0, 100)
)
# In wave2 some observations drop out & some new observations are added
wave2 <- tibble(
id = seq_along(1:150),
a = runif(150, 0, 100),
b = runif(150, 0, 100),
c = runif(150, 0, 100),
d = runif(150, 0, 100),
e = runif(150, 0, 100),
x = runif(150, 0, 100),
y = runif(150, 0, 100)
)
# Simulation of Dropout
wave2 %>%
filter(!id %in% sample(1:150, 23)) -> wave2
# Same with Wave 3
wave3 <- tibble(
id = c(wave2 %>% pull(id),151:200),
a = runif(nrow(wave2) + 50, 0, 100),
b = runif(nrow(wave2) + 50, 0, 100),
c = runif(nrow(wave2) + 50, 0, 100),
i = runif(nrow(wave2) + 50, 0, 100),
j = runif(nrow(wave2) + 50, 0, 100),
k = runif(nrow(wave2) + 50, 0, 100),
l = runif(nrow(wave2) + 50, 0, 100),
x = runif(nrow(wave2) + 50, 0, 100),
z = runif(nrow(wave2) + 50, 0, 100)
)
# Simulation of Dropout
wave3 %>%
filter(!id %in% sample(1:200, 33)) -> wave3
# Same with Wave 4
wave4 <- tibble(
id = c(wave3 %>% pull(id),201:300),
a = runif(nrow(wave3) + 100, 0, 100),
c = runif(nrow(wave3) + 100, 0, 100),
i = runif(nrow(wave3) + 100, 0, 100),
j = runif(nrow(wave3) + 100, 0, 100),
l = runif(nrow(wave3) + 100, 0, 100),
z = runif(nrow(wave3) + 100, 0, 100)
)
# Simulation of Dropout in Wave 4
wave4 %>%
filter(!id %in% sample(1:200, 41)) -> wave4
In the simulated data e.g. the Variable a would be present in all waves.
What I got so far
Iterate with a for-loop over the names of the waves (obtained by ls and a regex-pattern), get the current position and the next one up, get the data from the environment with get and read out the colnames. With intersect get the common column names between the current (in terms of the for loop) and the next wave. Save everything to a tibble (that was initialized empty).
And finally group_by and summarise to get everything in one column
##### Get Common Variables between all waves
# Get names of Tibbles (data) from environment
names_waves <- ls(pattern = "wave\\d+") %>% str_sort(numeric = TRUE)
waves_lagged_common2 <- tibble(wave = character(),common_vars = character())
for (wave in names_waves) {
cur_pos <- names_waves %>% match(x = wave)
print(cur_pos)
if (cur_pos != length(names_waves)) {
cur_wave_names <- get(wave) %>% names()
next_wave_names <- get(names_waves[cur_pos + 1]) %>% names()
intersect(cur_wave_names,next_wave_names) -> common_vars
waves_lagged_common2 <- waves_lagged_common2 %>% add_row(wave = wave, common_vars = common_vars)
}
}
# Merge the rows with group_by and summarise
waves_lagged_common2 %>%
group_by(wave) %>%
summarize(common_vars = paste(common_vars, collapse = ", "))
Current output:
# A tibble: 3 x 2
wave common_vars
<chr> <chr>
1 wave1 id, a, x, y
2 wave2 id, a, b, c, x
3 wave3 id, a, c, i, j, l, z
Further efforts to achieve desired output
Implement an additional for-loop inside the for-loop to get for the current wave (cur_pos) all consecutive waves. And work with add_row
common_vars_matrix <- tibble(Wave = names_waves) %>%
column_to_rownames("Wave")
for (wave in names_waves) {
cur_pos <- names_waves %>% match(x = wave)
print(cur_pos)
if (cur_pos != length(names_waves)) {
cur_wave_names <- get(wave) %>% names()
next_row_to_add <- c()
for (next_wave in names_waves[cur_pos+1:length(names_waves)]) {
next_wave_names <- get(next_wave) %>% names()
intersect(cur_wave_names,next_wave_names) -> common_vars
print(next_wave)
# print(common_vars)
next_row_to_add <- c(next_row_to_add,common_vars)
print(next_row_to_add)
}
}
}
However all these for-loops feel not really tidy.
Also I am running into out of index operators. At this point I am wondering if it is a good idea at all to use loops and "automate" the process, or if it would be just easier / more readable to work like this
wave1 %>% names -> names_wave1
wave2 %>% names -> names_wave2
wave3 %>% names -> names_wave3
wave4 %>% names -> names_wave4
common_1_2 <- intersect(names_wave1,names_wave2)
common_1_3 <- intersect(names_wave1,names_wave3)
common_1_4 <- intersect(names_wave1,names_wave4)
common_vars_matrix <- tibble(wave2=paste(common_1_2,collapse=","),
wave3=paste(common_1_3, collapse=","),
wave4=paste(common_1_4, collapse=","))
common_2_3 <- intersect(names_wave2,names_wave3)
common_2_4 <- intersect(names_wave2,names_wave4)
common_vars_matrix <- common_vars_matrix %>% add_row(wave2="-",
wave3=paste(common_2_3, collapse=","),
wave4=paste(common_2_4, collapse=","))
# And so forth