Extract every ID element from multiple df in list and then create new df showing each df the ID was a part of in the list in r

Question

On a recurring basis I need to provide a list of specific IDs which are in multiple data-frames. I also need to identify each data frame that the ID is in. It could be one, it could be all of them. These multiple data frames are in a list consisting of only these data frames.

Points to consider:

1. These list are already created from previous process, I am married to them
2. Each data frame in the list will be named and that name is what I need as the column names in the desired_result file
3. The dfs within the list are not consistent from list to list or run to run which is what creates my inability to get through this.
4. the goal is to show which IDs are in multiple data frames and which specific one(s) as in the "desired_results"
5. I AM NOT married to the results format. Anything that conveys the message will work.

#creates list
name <- c("Bill","Fred","Bob","John","Tom","Larry")
ID <- as.character(c(123,345,456,567,678,789))
df <- data.frame(name,ID)
bosses <- df[1:2,]
coworkers <- df[3:6,]
losers <- df[2:4,]
the_list <- list(bosses,coworkers,losers)
newnames <- c("bosses","coworkers","losers")
names(the_list) <- newnames

#creates desired results
a <- c("x","x",NA,NA,NA,NA)
b <- c(NA,NA,"x","x","x","x")
c <- c(NA,"x","x","x",NA,NA)
desired_result <- data.frame(ID,a,b,c)
colnames(desired_result) <- c("ID","bosses","coworkers","losers")

#clean up
remove(bosses)
remove(coworkers)
remove(df)
remove(losers)
remove(a)
remove(b)
remove(c)
remove(ID)
remove(name)
remove(newnames)

I did go back and confirm each element in the list is named. the value is shown as tibble if that matters but I was under impression all table format data is a tibble in list.

Additional code for debug: Yes I have confirmed case and spelling in my code.

bind_rows(passes, .Reference_ID = 'grp') %>% pivot_wider(names_from = grp, values_from = Authentication_Status)

question from:https://stackoverflow.com/questions/65836053/extract-every-id-element-from-multiple-df-in-list-and-then-create-new-df-showing

Answer 1

We can bind them in a single data and use pivot_wider

library(dplyr)
library(tidyr)
bind_rows(the_list, .id = 'grp') %>%
   pivot_wider(names_from = grp, values_from = name)