1

I work in a call center and this data is all of the pertinent data for our auto-dialer system. Starting in 2019 our company started multiple campaigns to reach out to members for many possible prescriptions instead of 1.

What we are running into now is member X will have potentially 6 prescriptions that we are trying to contact them about, and are often times duplicate. The department that gathers all of this data looks at IDCARD_MBR_ID and TARGET_DOC_ columns for unique values paying no attention to the prescription drug name.

My goal is to remove duplicate prescription drugs. and refill in any vacated space

The order of the drugs is not always the same either.. a brief sample of this might look like:

H0000000001 (member data columns) Target1, Target2 
H0000000001 (same member data)    Target3, Target1, Target2

what I would like to see is:

H0000000001 (member data columns) Target1, Target2,Target3

I have already worked my data via a gather, removed duplicates, renumbered my targets 1-6 (so I can create a new line if >3 are found) and spread it back out. My issue is with my spread.. I got something that looks like this:

structure(list(IDCARD_MBR_ID = c("H0000000001", "H0000000001", 
"H0000000001", "H0000000002", "H0000000002", "H0000000002", "H0000000003", 
"H0000000003", "H0000000003", "H0000000004", "H0000000004", "H0000000004", 
"H0000000005", "H0000000005", "H0000000005", "H0000000006", "H0000000006", 
"H0000000006", "H0000000007", "H0000000007", "H0000000007", "H0000000008", 
"H0000000008", "H0000000008", "H0000000009", "H0000000009", "H0000000009", 
"H0000000010", "H0000000010", "H0000000010", "H0000000011", "H0000000011", 
"H0000000011", "H0000000012", "H0000000012", "H0000000012"), 
    SDR_PERSON_ID = c("ID000000001", "ID000000001", "ID000000001", 
    "ID000000002", "ID000000002", "ID000000002", "ID000000003", 
    "ID000000003", "ID000000003", "ID000000004", "ID000000004", 
    "ID000000004", "ID000000005", "ID000000005", "ID000000005", 
    "ID000000006", "ID000000006", "ID000000006", "ID000000007", 
    "ID000000007", "ID000000007", "ID000000008", "ID000000008", 
    "ID000000008", "ID000000009", "ID000000009", "ID000000009", 
    "ID000000010", "ID000000010", "ID000000010", "ID000000011", 
    "ID000000011", "ID000000011", "ID000000012", "ID000000012", 
    "ID000000012"), FIRST_NAME = c("First1", "First1", "First1", 
    "First2", "First2", "First2", "First3", "First3", "First3", 
    "First4", "First4", "First4", "First5", "First5", "First5", 
    "First6", "First6", "First6", "First7", "First7", "First7", 
    "First8", "First8", "First8", "First9", "First9", "First9", 
    "First10", "First10", "First10", "First11", "First11", "First11", 
    "First12", "First12", "First12"), LAST_NAME = c("Last1", 
    "Last1", "Last1", "Last2", "Last2", "Last2", "Last3", "Last3", 
    "Last3", "Last4", "Last4", "Last4", "Last5", "Last5", "Last5", 
    "Last6", "Last6", "Last6", "Last7", "Last7", "Last7", "Last8", 
    "Last8", "Last8", "Last9", "Last9", "Last9", "Last10", "Last10", 
    "Last10", "Last11", "Last11", "Last11", "Last12", "Last12", 
    "Last12"), GENDER = c("F", "F", "F", "M", "M", "M", "M", 
    "M", "M", "F", "F", "F", "F", "F", "F", "F", "F", "F", "M", 
    "M", "M", "F", "F", "F", "F", "F", "F", "M", "M", "M", "F", 
    "F", "F", "F", "F", "F"), DOB = c("1901-01-01", "1901-01-01", 
    "1901-01-01", "1901-01-02", "1901-01-02", "1901-01-02", "1901-01-03", 
    "1901-01-03", "1901-01-03", "1901-01-04", "1901-01-04", "1901-01-04", 
    "1901-01-05", "1901-01-05", "1901-01-05", "1901-01-06", "1901-01-06", 
    "1901-01-06", "1901-01-07", "1901-01-07", "1901-01-07", "1901-01-08", 
    "1901-01-08", "1901-01-08", "1901-01-09", "1901-01-09", "1901-01-09", 
    "1901-01-10", "1901-01-10", "1901-01-10", "1901-01-11", "1901-01-11", 
    "1901-01-11", "1901-01-12", "1901-01-12", "1901-01-12"), 
    ADDRESS1 = c("123 Testing Way", "123 Testing Way", "123 Testing Way", 
    "456 Testing Way", "456 Testing Way", "456 Testing Way", 
    "789 Testing Way", "789 Testing Way", "789 Testing Way", 
    "1122 Testing Way", "1122 Testing Way", "1122 Testing Way", 
    "1455 Testing Way", "1455 Testing Way", "1455 Testing Way", 
    "1788 Testing Way", "1788 Testing Way", "1788 Testing Way", 
    "2121 Testing Way", "2121 Testing Way", "2121 Testing Way", 
    "2454 Testing Way", "2454 Testing Way", "2454 Testing Way", 
    "2787 Testing Way", "2787 Testing Way", "2787 Testing Way", 
    "3120 Testing Way", "3120 Testing Way", "3120 Testing Way", 
    "3453 Testing Way", "3453 Testing Way", "3453 Testing Way", 
    "3786 Testing Way", "3786 Testing Way", "3786 Testing Way"
    ), ADDRESS2 = c(NA, NA, NA, NA, NA, NA, "APT 302", "APT 302", 
    "APT 302", "APT 225", "APT 225", "APT 225", NA, NA, NA, NA, 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    NA, NA, NA, NA, NA), CITY = c("CLARKSVILLE", "CLARKSVILLE", 
    "CLARKSVILLE", "MONROE", "MONROE", "MONROE", "LOS ANGELES", 
    "LOS ANGELES", "LOS ANGELES", "TULSA", "TULSA", "TULSA", 
    "RED BUD", "RED BUD", "RED BUD", "MISSION VIEJO", "MISSION VIEJO", 
    "MISSION VIEJO", "MILLCREEK", "MILLCREEK", "MILLCREEK", "COLUMBIA", 
    "COLUMBIA", "COLUMBIA", "HOUSTON", "HOUSTON", "HOUSTON", 
    "APOPKA", "APOPKA", "APOPKA", "SEALY", "SEALY", "SEALY", 
    "SEWARD", "SEWARD", "SEWARD"), ST = c("IN", "IN", "IN", "MI", 
    "MI", "MI", "CA", "CA", "CA", "OK", "OK", "OK", "IL", "IL", 
    "IL", "CA", "CA", "CA", "UT", "UT", "UT", "MO", "MO", "MO", 
    "TX", "TX", "TX", "FL", "FL", "FL", "TX", "TX", "TX", "NE", 
    "NE", "NE"), ZIP = c(47129L, 47129L, 47129L, 48162L, 48162L, 
    48162L, 90028L, 90028L, 90028L, 74136L, 74136L, 74136L, 62278L, 
    62278L, 62278L, 92692L, 92692L, 92692L, 84124L, 84124L, 84124L, 
    65201L, 65201L, 65201L, 77057L, 77057L, 77057L, 32703L, 32703L, 
    32703L, 77474L, 77474L, 77474L, 68434L, 68434L, 68434L), 
    EMAIL_ADDRESS = c("Test-mail@test.com", "Test-mail@test.com", 
    "Test-mail@test.com", NA, NA, NA, NA, NA, NA, NA, NA, NA, 
    "Test-mail@test.com", "Test-mail@test.com", "Test-mail@test.com", 
    NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "Test-mail@test.com", 
    "Test-mail@test.com", "Test-mail@test.com", NA, NA, NA, NA, 
    NA, NA), PHONE1 = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L, 4L, 
    4L, 4L, 5L, 5L, 5L, 6L, 6L, 6L, 7L, 7L, 7L, 8L, 8L, 8L, 9L, 
    9L, 9L, 10L, 10L, 10L, 11L, 11L, 11L, 12L, 12L, 12L), ARG_CLIENT_NUM = c(6L, 
    6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 
    6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 
    6L, 6L, 6L, 6L, 6L), FORMULARYID = c(19430L, 19430L, 19431L, 
    19431L, 19431L, 19430L, 19430L, 19430L, 19430L, 19430L, 19430L, 
    19430L, 19430L, 19430L, 19430L, 19431L, 19431L, 19431L, 19430L, 
    19430L, 19431L, 19431L, 19431L, 19430L, 19430L, 19430L, 19431L, 
    19430L, 19430L, 19430L, 19430L, 19430L, 19430L, 19430L, 19430L, 
    19431L), CAMPAIGN_TYPE = c("PDP_Lean MAPD", "PDP_Lean MAPD", 
    "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", 
    "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", 
    "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", 
    "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", 
    "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", 
    "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", 
    "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", 
    "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", 
    "PDP_Lean MAPD", "PDP_Lean MAPD"), LIS_INDICATOR = c("N", 
    "N", "N", "N", "N", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", 
    "Y", "N", "N", "N", "N", "Y", "Y", "N", "N", "N", "N", "N", 
    "N", "N", "Y", "Y", "N", "Y", "Y", "Y", "Y", "Y", "N"), MONTH = c("February / DIALER", 
    "February / DIALER", "January / DIALER", "January / DIALER", 
    "January / DIALER", "January / DIALER", "February / DIALER", 
    "February / DIALER", "January / DIALER", "January / DIALER", 
    "January / DIALER", "January / DIALER", "January / DIALER", 
    "January / DIALER", "January / DIALER", "January / DIALER", 
    "January / DIALER", "January / DIALER", "January / DIALER", 
    "January / DIALER", "February / DIALER", "January / DIALER", 
    "January / DIALER", "February / DIALER", "January / DIALER", 
    "January / DIALER", "January / DIALER", "January / DIALER", 
    "January / DIALER", "February / DIALER", "January / DIALER", 
    "January / DIALER", "January / DIALER", "January / DIALER", 
    "January / DIALER", "January / DIALER"), Calltype1 = c("Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings"), Calltype2 = c("Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings"), Calltype3 = c("Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings"), TARGET_DOC_1 = c("A000000000025001", 
    NA, NA, "A000000000026001", NA, NA, "A000000000027001", NA, 
    NA, "A000000000028001", NA, NA, "A000000000029001", NA, NA, 
    "A000000000030001", NA, NA, "A000000000031001", NA, NA, "A000000000032001", 
    NA, NA, "A000000000033001", NA, NA, "A000000000034001", NA, 
    NA, "A000000000035001", NA, NA, "A000000000036001", NA, NA
    ), TARGET_DOC_2 = c(NA, "A000000000013001", NA, NA, "A000000000014001", 
    NA, NA, "A000000000015001", NA, NA, "A000000000016001", NA, 
    NA, "A000000000017001", NA, NA, "A000000000018001", NA, NA, 
    "A000000000019001", NA, NA, "A000000000020001", NA, NA, "A000000000021001", 
    NA, NA, "A000000000022001", NA, NA, "A000000000023001", NA, 
    NA, "A000000000024001", NA), TARGET_DOC_3 = c(NA, NA, "A000000000001001", 
    NA, NA, "A000000000002001", NA, NA, "A000000000003001", NA, 
    NA, "A000000000004001", NA, NA, "A000000000005001", NA, NA, 
    "A000000000006001", NA, NA, "A000000000007001", NA, NA, "A000000000008001", 
    NA, NA, "A000000000009001", NA, NA, "A000000000010001", NA, 
    NA, "A000000000011001", NA, NA, "A000000000012001"), TARGET_DRUG_ID_1 = c(57237014401, 
    NA, NA, 65862058301, NA, NA, 57237014301, NA, NA, 68180075701, 
    NA, NA, 65862058401, NA, NA, 55111033901, NA, NA, 65862058201, 
    NA, NA, 65862058405, NA, NA, 65862058601, NA, NA, 68180075801, 
    NA, NA, 65862058601, NA, NA, 68180076001, NA, NA), TARGET_DRUG_ID_2 = c(NA, 
    57237014401, NA, NA, 65862058301, NA, NA, 57237014301, NA, 
    NA, 68180075701, NA, NA, 65862058401, NA, NA, 55111033901, 
    NA, NA, 65862058201, NA, NA, 65862058405, NA, NA, 65862058601, 
    NA, NA, 68180075801, NA, NA, 65862058601, NA, NA, 68180076001, 
    NA), TARGET_DRUG_ID_3 = c(NA, NA, 65862058701, NA, NA, 65862058701, 
    NA, NA, 55111034001, NA, NA, 65862058601, NA, NA, 65862058701, 
    NA, NA, 55111034001, NA, NA, 65862058401, NA, NA, 65862058405, 
    NA, NA, 68180047301, NA, NA, 68180075801, NA, NA, 65862058701, 
    NA, NA, 65862058301), TARGET_NAME_1 = c("Target11", NA, NA, 
    "Target12", NA, NA, "Target13", NA, NA, "Target14", NA, NA, 
    "Target15", NA, NA, "Target16", NA, NA, "Target17", NA, NA, 
    "Target18", NA, NA, "Target19", NA, NA, "Target20", NA, NA, 
    "Target21", NA, NA, "Target22", NA, NA), TARGET_NAME_2 = c(NA, 
    "Target21", NA, NA, "Target22", NA, NA, "Target23", NA, NA, 
    "Target24", NA, NA, "Target25", NA, NA, "Target26", NA, NA, 
    "Target27", NA, NA, "Target28", NA, NA, "Target29", NA, NA, 
    "Target30", NA, NA, "Target31", NA, NA, "Target32", NA), 
    TARGET_NAME_3 = c(NA, NA, "Target31", NA, NA, "Target32", 
    NA, NA, "Target33", NA, NA, "Target34", NA, NA, "Target35", 
    NA, NA, "Target36", NA, NA, "Target37", NA, NA, "Target38", 
    NA, NA, "Target39", NA, NA, "Target40", NA, NA, "Target41", 
    NA, NA, "Target42")), class = "data.frame", row.names = c(NA, 
-36L))}

Each set of targets are on their own line.. and I can't figure out how to collapse them and remove the NA so that I end up with a result that looks like this:

structure(list(IDCARD_MBR_ID = c("H0000000001", "H0000000002", 
"H0000000003", "H0000000004", "H0000000005", "H0000000006", "H0000000007", 
"H0000000008", "H0000000009", "H0000000010", "H0000000011", "H0000000012"
), SDR_PERSON_ID = c("ID000000001", "ID000000002", "ID000000003", 
"ID000000004", "ID000000005", "ID000000006", "ID000000007", "ID000000008", 
"ID000000009", "ID000000010", "ID000000011", "ID000000012"), 
    FIRST_NAME = c("First1", "First2", "First3", "First4", "First5", 
    "First6", "First7", "First8", "First9", "First10", "First11", 
    "First12"), LAST_NAME = c("Last1", "Last2", "Last3", "Last4", 
    "Last5", "Last6", "Last7", "Last8", "Last9", "Last10", "Last11", 
    "Last12"), GENDER = c("F", "M", "M", "F", "F", "F", "M", 
    "F", "F", "M", "F", "F"), DOB = c("1/1/1901", "1/2/1901", 
    "1/3/1901", "1/4/1901", "1/5/1901", "1/6/1901", "1/7/1901", 
    "1/8/1901", "1/9/1901", "1/10/1901", "1/11/1901", "1/12/1901"
    ), ADDRESS1 = c("123 Testing Way", "456 Testing Way", "789 Testing Way", 
    "1122 Testing Way", "1455 Testing Way", "1788 Testing Way", 
    "2121 Testing Way", "2454 Testing Way", "2787 Testing Way", 
    "3120 Testing Way", "3453 Testing Way", "3786 Testing Way"
    ), ADDRESS2 = c(NA, NA, "APT 302", "APT 225", NA, NA, NA, 
    NA, NA, NA, NA, NA), CITY = c("CLARKSVILLE", "MONROE", "LOS ANGELES", 
    "TULSA", "RED BUD", "MISSION VIEJO", "MILLCREEK", "COLUMBIA", 
    "HOUSTON", "APOPKA", "SEALY", "SEWARD"), ST = c("IN", "MI", 
    "CA", "OK", "IL", "CA", "UT", "MO", "TX", "FL", "TX", "NE"
    ), ZIP = c(47129L, 48162L, 90028L, 74136L, 62278L, 92692L, 
    84124L, 65201L, 77057L, 32703L, 77474L, 68434L), EMAIL_ADDRESS = c("Test-mail@test.com", 
    NA, NA, NA, "Test-mail@test.com", NA, NA, NA, NA, "Test-mail@test.com", 
    NA, NA), PHONE1 = 1:12, ARG_CLIENT_NUM = c(6L, 6L, 6L, 6L, 
    6L, 6L, 6L, 6L, 6L, 6L, 6L, 6L), FORMULARYID = c(19430L, 
    19431L, 19430L, 19430L, 19430L, 19431L, 19430L, 19431L, 19430L, 
    19430L, 19430L, 19430L), CAMPAIGN_TYPE = c("PDP_Lean MAPD", 
    "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", 
    "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD", 
    "PDP_Lean MAPD", "PDP_Lean MAPD", "PDP_Lean MAPD"), LIS_INDICATOR = c("N", 
    "N", "Y", "Y", "Y", "N", "Y", "N", "N", "Y", "Y", "Y"), MONTH = c("February / DIALER", 
    "January / DIALER", "February / DIALER", "January / DIALER", 
    "January / DIALER", "January / DIALER", "January / DIALER", 
    "January / DIALER", "January / DIALER", "January / DIALER", 
    "January / DIALER", "January / DIALER"), Calltype1 = c("Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings"), Calltype2 = c("Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings"), Calltype3 = c("Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings", "Cost Savings", 
    "Cost Savings", "Cost Savings", "Cost Savings"), TARGET_DOC_1 = c("A000000000025001", 
    "A000000000026001", "A000000000027001", "A000000000028001", 
    "A000000000029001", "A000000000030001", "A000000000031001", 
    "A000000000032001", "A000000000033001", "A000000000034001", 
    "A000000000035001", "A000000000036001"), TARGET_DOC_2 = c("A000000000013001", 
    "A000000000014001", "A000000000015001", "A000000000016001", 
    "A000000000017001", "A000000000018001", "A000000000019001", 
    "A000000000020001", "A000000000021001", "A000000000022001", 
    "A000000000023001", "A000000000024001"), TARGET_DOC_3 = c("A000000000001001", 
    "A000000000002001", "A000000000003001", "A000000000004001", 
    "A000000000005001", "A000000000006001", "A000000000007001", 
    "A000000000008001", "A000000000009001", "A000000000010001", 
    "A000000000011001", "A000000000012001"), TARGET_DRUG_ID_1 = c(57237014401, 
    65862058301, 57237014301, 68180075701, 65862058401, 55111033901, 
    65862058201, 65862058405, 65862058601, 68180075801, 65862058601, 
    68180076001), TARGET_DRUG_ID_2 = c(57237014401, 65862058301, 
    57237014301, 68180075701, 65862058401, 55111033901, 65862058201, 
    65862058405, 65862058601, 68180075801, 65862058601, 68180076001
    ), TARGET_DRUG_ID_3 = c(65862058701, 65862058701, 55111034001, 
    65862058601, 65862058701, 55111034001, 65862058401, 65862058405, 
    68180047301, 68180075801, 65862058701, 65862058301), TARGET_NAME_1 = c("Target11", 
    "Target12", "Target13", "Target14", "Target15", "Target16", 
    "Target17", "Target18", "Target19", "Target20", "Target21", 
    "Target22"), TARGET_NAME_2 = c("Target21", "Target22", "Target23", 
    "Target24", "Target25", "Target26", "Target27", "Target28", 
    "Target29", "Target30", "Target31", "Target32"), TARGET_NAME_3 = c("Target31", 
    "Target32", "Target33", "Target34", "Target35", "Target36", 
    "Target37", "Target38", "Target39", "Target40", "Target41", 
    "Target42")), class = "data.frame", row.names = c(NA, -12L
))

I have been looking at similar questions such as this one: Collapse duplicated rows Collapse duplicated rows with different values in few columns using R

My biggest issue is my column count is so much greater (by necessity.. all rows need to stay together for integrity) I get lost very quickly.

EDIT 1: Adding a 'head(12)' of DF (note DF is very wide and hard to paste to maintain formatting.. Here are the columns affected by the issue)... also, not sure why in this test data some NA are and some are NA.. in my real data they are true missing value NA's.


     Calltype3     TARGET_DOC_1     TARGET_DOC_2     TARGET_DOC_3 TARGET_DRUG_ID_1 TARGET_DRUG_ID_2 TARGET_DRUG_ID_3
1  Cost Savings A000000000025001             <NA>             <NA>      57237014401               NA               NA
2  Cost Savings             <NA> A000000000013001             <NA>               NA      57237014401               NA
3  Cost Savings             <NA>             <NA> A000000000001001               NA               NA      65862058701
4  Cost Savings A000000000026001             <NA>             <NA>      65862058301               NA               NA
5  Cost Savings             <NA> A000000000014001             <NA>               NA      65862058301               NA
6  Cost Savings             <NA>             <NA> A000000000002001               NA               NA      65862058701
7  Cost Savings A000000000027001             <NA>             <NA>      57237014301               NA               NA
8  Cost Savings             <NA> A000000000015001             <NA>               NA      57237014301               NA
9  Cost Savings             <NA>             <NA> A000000000003001               NA               NA      55111034001
10 Cost Savings A000000000028001             <NA>             <NA>      68180075701               NA               NA
11 Cost Savings             <NA> A000000000016001             <NA>               NA      68180075701               NA
12 Cost Savings             <NA>             <NA> A000000000004001               NA               NA      65862058601
   TARGET_NAME_1 TARGET_NAME_2 TARGET_NAME_3
1       Target11          <NA>          <NA>
2           <NA>      Target21          <NA>
3           <NA>          <NA>      Target31
4       Target12          <NA>          <NA>
5           <NA>      Target22          <NA>
6           <NA>          <NA>      Target32
7       Target13          <NA>          <NA>
8           <NA>      Target23          <NA>
9           <NA>          <NA>      Target33
10      Target14          <NA>          <NA>
11          <NA>      Target24          <NA>
12          <NA>          <NA>      Target34

EDIT 4: based on Humpelstielzchen's suggestion I ran this: My spread DF = spread1

spread2 <- spread1 %>% 
  group_by(IDCARD_MBR_ID) %>% 
  mutate(MONTH = MONTH[row_number()==1],
         FORMULARYID = FORMULARYID[row_number()==1],
         LIS_INDICATOR = LIS_INDICATOR[row_number() ==1]) %>% 
  group_by_at(vars(-starts_with("TARGET"))) %>% 
  summarise_at(vars(starts_with("TARGET")), list(. = ~.[which(!is.na(.))[1]]))

and it appears to have worked correctly; I have a data frame of over 1million starting rows, this has helped me reduce to ~600k; it will take me a little bit of time to verify accuracy, but looks great!

Checht
  • 45
  • 10
  • For such long `structure` objects, can you show what the data look like (e.g. `head()`)? – Tung Apr 11 '19 at 22:21
  • 1
    I will try to get a head() as soon as possible; I am at my parents house for dinner, but when I get home I will look.. The above R structure is already a shortened Head(24) (but 30+ columns).. I wanted enough rows to show the patterns I was referring to without totally overwhelming anyone.. Appears I need to shorten it more :) – Checht Apr 12 '19 at 02:19
  • No you don't need to shorten them. Just post the first few lines when print them out with `head` so others can see the format without the need to copy and paste `structure` in their own `R` workspace – Tung Apr 12 '19 at 02:50
  • I have added the head(12).. formatting is hard to keep on a DF that wide, so I only posted the columns to the far right of the DF that are affected. – Checht Apr 12 '19 at 03:41
  • Take a look at this https://stackoverflow.com/a/52524260/786542. Might be helpful – Tung Apr 12 '19 at 03:43
  • Aggregate groups your data.frame by the columns inside `by=list(...)`, so to have them all included in the result you have to add them all inside `by=list(...)` or join them later on to your result. `dplyr` does all that with `group_by_at(vars(-starts_with("TARGET")))` – Humpelstielzchen Apr 12 '19 at 18:20

1 Answers1

3

Try this:

library(dplyr)


df1 %>%
  group_by(IDCARD_MBR_ID) %>%
  mutate(MONTH = MONTH[row_number() == 1],
         FORMULARYID = FORMULARYID[row_number() == 1],
         LIS_INDICATOR = LIS_INDICATOR[row_number() == 1]) %>%
  group_by_at(vars(-starts_with("TARGET"))) %>%
  summarise_at(vars(starts_with("TARGET")), list(. = ~.[which(!is.na(.))[1]])) -> res


> res[,c("IDCARD_MBR_ID", "TARGET_NAME_1_.", "TARGET_NAME_2_.", "TARGET_NAME_3_." )]
# A tibble: 12 x 4
   IDCARD_MBR_ID TARGET_NAME_1_. TARGET_NAME_2_. TARGET_NAME_3_.
   <chr>         <chr>           <chr>           <chr>          
 1 H0000000001   Target11        Target21        Target31       
 2 H0000000002   Target12        Target22        Target32       
 3 H0000000003   Target13        Target23        Target33       
 4 H0000000004   Target14        Target24        Target34       
 5 H0000000005   Target15        Target25        Target35       
 6 H0000000006   Target16        Target26        Target36       
 7 H0000000007   Target17        Target27        Target37       
 8 H0000000008   Target18        Target28        Target38       
 9 H0000000009   Target19        Target29        Target39       
10 H0000000010   Target20        Target30        Target40       
11 H0000000011   Target21        Target31        Target41       
12 H0000000012   Target22        Target32        Target42 

I had to equalize some grouping variables (MONTH, FORMULARYID, LIS_INDICATOR), because they varied within the same ID. I just picked the first one for each ID, you may adjust this to your liking.

Humpelstielzchen
  • 6,126
  • 3
  • 14
  • 34
  • My first attempt at this failed.. So that I can post the code I used (and the error it through) with formatting I will adjust my OP. – Checht Apr 12 '19 at 14:50
  • What version of dplyr are you using? `group_by_at()` is relatively new and it worked pretty satisfactory in my case. – Humpelstielzchen Apr 12 '19 at 18:08
  • I just reinstalled all my packages after moving from a PC to a laptop ~4 days ago. According to the packages tab in Rstudio I am using 1.2.2 – Checht Apr 12 '19 at 18:27
  • I just noticed, you are not using `group_by_at` as in my code but `group_by`. You see? – Humpelstielzchen Apr 12 '19 at 18:35
  • DOH! I did not notice that at first.. I see it now; I will give that a whirl as soon as I have a spare minute. Thanks for pointing it out! :) – Checht Apr 12 '19 at 20:44
  • Sorry for the late response, this seems to do exactly what I was looking for. I have one other quirk I am working through, though that may be an issue somewhere else in my work. – Checht Apr 15 '19 at 19:02