Repetition of a variable based on unique IDs

kuttan98 · February 17, 2023, 3:57pm

Hi,
I have a data frame that consists of unique IDs for 110 people in the sample below I have given 3 IDs in the data frame data1. They are supposed to conduct surveys of school kids. I have the dataset of school students as given in the data frame data2 below. I want an output in such a way that each enumerator is mapped to all the students as shown in the data frame data_output. How can I do this?

library(tidyverse)
library(janitor)

data1<-tibble::tribble(
         ~enumerator_id,
               "BEN001",
               "BEN002",
               "BEN003"
         )


data2<-tibble::tribble(
  ~selectedregionid, ~selectedclusterid,                                       ~selectedschoolid,  ~selectedteachername,                             ~child_name_1, ~student_id, ~child_gender, ~child_age2, ~child_class,
   "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",       "Savita B Naik",                 "Basavaraj.Y.Tarlaghatti",    "D13366",            1L,          6L,           1L,
   "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",     "Jayamma M Masur",           "Chetan Shivanda Dyavappanavar",    "0MI10H",            1L,          6L,           1L,
   "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",       "Savita B Naik",                     "Deepu Nagaraj Pujar",    "H3TZBT",            2L,          6L,           1L,
   "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",       "Savita B Naik",                          "Parvati Malagi",    "KMML2W",            2L,          6L,           1L,
   "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",     "Jayamma M Masur",                     "Preeti Shengannavar",    "B9FP3J",            2L,          6L,           1L,
   "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",     "Jayamma M Masur",                         "Varsha.M.Kamaar",    "5SLOJL",            2L,          6L,           1L,
   "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur",           "Abhishek tippanna siddannavar",    "5H9AUO",            1L,          6L,           1L,
   "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur",             "Omkar maruthi siddannanavar",    "VOK6Y2",            1L,          6L,           1L,
   "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur",                       "Rakesh Majjigudda",    "J8F9JV",            1L,          6L,           1L,
   "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur", "Siddalingayya Shekarayya Amrutayyanavar",    "DT17JL",            1L,          6L,           1L,
   "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",                "Aanjaneya laxman talakal",    "KLYCRS",            1L,          6L,           1L,
   "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",              "Aarush manjunath navalgund",    "2QY5G7",            1L,          6L,           1L,
   "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",            "Akkabarbsha Javid muddebihal",    "MM8LYQ",            1L,          6L,           1L,
   "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",                "Anjana.Basappa Khanapur.",    "ZFSIMQ",            2L,          6L,           1L,
   "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",                   "Bharatha P Arkacharya",    "Q9VCAZ",            1L,          6L,           1L
  )

data_output<-tibble::tribble(
         ~enumerator_id, ~selectedregionid, ~selectedclusterid,                                       ~selectedschoolid,  ~selectedteachername,                             ~child_name_1, ~student_id, ~child_gender, ~child_age2, ~child_class,
               "BEN001",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",       "Savita B Naik",                 "Basavaraj.Y.Tarlaghatti",    "D13366",            1L,          6L,           1L,
               "BEN001",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",     "Jayamma M Masur",           "Chetan Shivanda Dyavappanavar",    "0MI10H",            1L,          6L,           1L,
               "BEN001",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",       "Savita B Naik",                     "Deepu Nagaraj Pujar",    "H3TZBT",            2L,          6L,           1L,
               "BEN001",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",       "Savita B Naik",                          "Parvati Malagi",    "KMML2W",            2L,          6L,           1L,
               "BEN001",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",     "Jayamma M Masur",                     "Preeti Shengannavar",    "B9FP3J",            2L,          6L,           1L,
               "BEN001",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",     "Jayamma M Masur",                         "Varsha.M.Kamaar",    "5SLOJL",            2L,          6L,           1L,
               "BEN001",  "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur",           "Abhishek tippanna siddannavar",    "5H9AUO",            1L,          6L,           1L,
               "BEN001",  "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur",             "Omkar maruthi siddannanavar",    "VOK6Y2",            1L,          6L,           1L,
               "BEN001",  "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur",                       "Rakesh Majjigudda",    "J8F9JV",            1L,          6L,           1L,
               "BEN001",  "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur", "Siddalingayya Shekarayya Amrutayyanavar",    "DT17JL",            1L,          6L,           1L,
               "BEN001",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",                "Aanjaneya laxman talakal",    "KLYCRS",            1L,          6L,           1L,
               "BEN001",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",              "Aarush manjunath navalgund",    "2QY5G7",            1L,          6L,           1L,
               "BEN001",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",            "Akkabarbsha Javid muddebihal",    "MM8LYQ",            1L,          6L,           1L,
               "BEN001",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",                "Anjana.Basappa Khanapur.",    "ZFSIMQ",            2L,          6L,           1L,
               "BEN001",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",                   "Bharatha P Arkacharya",    "Q9VCAZ",            1L,          6L,           1L,
               "BEN002",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",       "Savita B Naik",                 "Basavaraj.Y.Tarlaghatti",    "D13366",            1L,          6L,           1L,
               "BEN002",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",     "Jayamma M Masur",           "Chetan Shivanda Dyavappanavar",    "0MI10H",            1L,          6L,           1L,
               "BEN002",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",       "Savita B Naik",                     "Deepu Nagaraj Pujar",    "H3TZBT",            2L,          6L,           1L,
               "BEN002",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",       "Savita B Naik",                          "Parvati Malagi",    "KMML2W",            2L,          6L,           1L,
               "BEN002",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",     "Jayamma M Masur",                     "Preeti Shengannavar",    "B9FP3J",            2L,          6L,           1L,
               "BEN002",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",     "Jayamma M Masur",                         "Varsha.M.Kamaar",    "5SLOJL",            2L,          6L,           1L,
               "BEN002",  "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur",           "Abhishek tippanna siddannavar",    "5H9AUO",            1L,          6L,           1L,
               "BEN002",  "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur",             "Omkar maruthi siddannanavar",    "VOK6Y2",            1L,          6L,           1L,
               "BEN002",  "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur",                       "Rakesh Majjigudda",    "J8F9JV",            1L,          6L,           1L,
               "BEN002",  "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur", "Siddalingayya Shekarayya Amrutayyanavar",    "DT17JL",            1L,          6L,           1L,
               "BEN002",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",                "Aanjaneya laxman talakal",    "KLYCRS",            1L,          6L,           1L,
               "BEN002",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",              "Aarush manjunath navalgund",    "2QY5G7",            1L,          6L,           1L,
               "BEN002",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",            "Akkabarbsha Javid muddebihal",    "MM8LYQ",            1L,          6L,           1L,
               "BEN002",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",                "Anjana.Basappa Khanapur.",    "ZFSIMQ",            2L,          6L,           1L,
               "BEN002",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",                   "Bharatha P Arkacharya",    "Q9VCAZ",            1L,          6L,           1L,
               "BEN003",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",       "Savita B Naik",                 "Basavaraj.Y.Tarlaghatti",    "D13366",            1L,          6L,           1L,
               "BEN003",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",     "Jayamma M Masur",           "Chetan Shivanda Dyavappanavar",    "0MI10H",            1L,          6L,           1L,
               "BEN003",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",       "Savita B Naik",                     "Deepu Nagaraj Pujar",    "H3TZBT",            2L,          6L,           1L,
               "BEN003",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",       "Savita B Naik",                          "Parvati Malagi",    "KMML2W",            2L,          6L,           1L,
               "BEN003",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",     "Jayamma M Masur",                     "Preeti Shengannavar",    "B9FP3J",            2L,          6L,           1L,
               "BEN003",  "Hubballi Rural",           "NOOLVI",         "SCH087-DPEP MURARHALLI ADARGUNCHI-29090603818",     "Jayamma M Masur",                         "Varsha.M.Kamaar",    "5SLOJL",            2L,          6L,           1L,
               "BEN003",  "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur",           "Abhishek tippanna siddannavar",    "5H9AUO",            1L,          6L,           1L,
               "BEN003",  "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur",             "Omkar maruthi siddannanavar",    "VOK6Y2",            1L,          6L,           1L,
               "BEN003",  "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur",                       "Rakesh Majjigudda",    "J8F9JV",            1L,          6L,           1L,
               "BEN003",  "Hubballi Rural",           "MANTUR",                      "SCH249-GHPKBS MANTUR-29090200801",       "Nirmala Masur", "Siddalingayya Shekarayya Amrutayyanavar",    "DT17JL",            1L,          6L,           1L,
               "BEN003",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",                "Aanjaneya laxman talakal",    "KLYCRS",            1L,          6L,           1L,
               "BEN003",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",              "Aarush manjunath navalgund",    "2QY5G7",            1L,          6L,           1L,
               "BEN003",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",            "Akkabarbsha Javid muddebihal",    "MM8LYQ",            1L,          6L,           1L,
               "BEN003",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",                "Anjana.Basappa Khanapur.",    "ZFSIMQ",            2L,          6L,           1L,
               "BEN003",  "Hubballi Urban",   "HALE HUBLI HBL", "SCH300-GHPKBS NO.20 BOYS SCHOOL, CHENNPET-29090700803", "Mr. Pradeep kumar H",                   "Bharatha P Arkacharya",    "Q9VCAZ",            1L,          6L,           1L
         )

FJCC · February 17, 2023, 4:09pm

I thin a cross join will get you what you want.

library(dplyr)
data_output <- full_join(data1, data2, by = character())

kuttan98 · February 17, 2023, 6:19pm

Oh thanks.. that worked in the way I wanted it.. can you just explain the logic behind it? Like what does by=character() do?

FJCC · February 17, 2023, 8:09pm

The by argument tells the function which columns to match in the two data frames when doing the join. Using character() tells the function to do a cross join, matching every row in each data frame with every row in the other. There is no strong reason I can think of that character() is used for this purpose. I suppose that, since a column can't have that name, it is a reasonable choice. The help for full_join() says:

To perform a cross-join, generating all combinations of x and y, use by = character().

kuttan98 · February 18, 2023, 4:06am

Thank a lot for the help.

system · February 25, 2023, 4:07am

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.