Need help merging string data from column that runs into below rows. Problem in multiple columns, leaving empty data in cells for other columns

user_19832919 0
Viewed 28

In a nutshell - I have multiple columns in my data frame and some of the columns have string data that spill into the rows below, which means that those near-empty rows only have info for those spillover columns. I would like to merge the rows, and combine all the string data into that specific cell for that column with the spillover issue (I need to do this all in R please...). I also have this problem in different columns, and it does not happen in every row.... This is hard to explain with words, but my output below explains the problem the best. I figured that a dput output would be better than pasting a table here so that people could actually use this with code. This is a very simplified version of my data frame and the problem.

structure(list(SECTION = c(10207L, NA, 14097L, NA, NA, NA, NA, 
21290L, NA, 3359L, NA, NA, NA, NA, 50903L, NA), SCHOOL = c("ACAD", 
"", "ACCT", "", "", "", "", "ANSC", "", "LAW", "", "", "", "", 
"XPPD", "PPD"), COURSE_CODE = c("ACAD-181", "", "ACCT-410", "", 
"", "", "", "PR-463", "", "LAW-680A", "", "", "", "", "PPDE-630", 
""), COURSE_TITLE = c("Disruptive Innovation", "", "Foundations of Accounting", 
"", "", "", "", "Strategic Public Relations Research, Analysis", 
"and Insights", "Review of Law and Social Justice Editing", "", 
"", "", "", "Community Health Planning", ""), INSTRUCTOR_NAME = c("Smith, Tim", 
"Bob, Scott", "Gem, Silvia", "", "", "", "", "OBrien, James", 
"", "Harvey, Tony", "", "", "", "", "Sloth,  Ryan", ""), ASSIGNED_ROOM = c("IYH210/211", 
"", "ONLINE", "", "", "", "", "ONLINE", "", "ONLINE", "", "", 
"", "", "ONLINE", ""), TOTAL_ENR = c(32L, NA, 55L, NA, NA, NA, 
NA, 17L, NA, 13L, NA, NA, NA, NA, 16L, NA), COURSE_DESCRIPTION = c("Critical approaches to social and cultural changes.", 
"", "Non-technical presentation of accounting for users of accounting", 
"information; introduction to financial and managerial accounting.", 
"Not open to students with course credits in accounting. Not", 
"available for unit or course credit toward a degree in accounting", 
"or business administration.", "Identification of key strategic insights.", 
"", "Supervision of research and writing, and final editing of articles", 
"and comments for publication in the Review of Law and Social", 
"Justice.  For officers of the Review.  Open to law students only.", 
"Graded IP to CR/D/F.", "", "The role of planning in sustaining community health.", 
"")), class = "data.frame", row.names = c(NA, -16L))
na r merge string
1 Answers

I think this will work.

library(tidyverse)

X <- structure(list(SECTION = c(10207L, NA, 14097L, NA, NA, NA, NA, 21290L, NA, 3359L, NA, NA, NA, NA, 50903L, NA),
                    SCHOOL = c("ACAD", "", "ACCT", "", "", "", "", "ANSC", "", "LAW", "", "", "", "", "XPPD", "PPD"),
                    COURSE_CODE = c("ACAD-181", "", "ACCT-410", "", "", "", "", "PR-463", "", "LAW-680A", "", "", "", "", "PPDE-630", ""),
                    COURSE_TITLE = c("Disruptive Innovation", "", "Foundations of Accounting", "", "", "", "", "Strategic Public Relations Research, Analysis", "and Insights", "Review of Law and Social Justice Editing", "", "", "", "", "Community Health Planning", ""),
                    INSTRUCTOR_NAME = c("Smith, Tim", "Bob, Scott", "Gem, Silvia", "", "", "", "", "OBrien, James", "", "Harvey, Tony", "", "", "", "", "Sloth, Ryan", ""),
                    ASSIGNED_ROOM = c("IYH210/211", "", "ONLINE", "", "", "", "", "ONLINE", "", "ONLINE", "", "", "", "", "ONLINE", ""),
                    TOTAL_ENR = c(32L, NA, 55L, NA, NA, NA, NA, 17L, NA, 13L, NA, NA, NA, NA, 16L, NA),
                    COURSE_DESCRIPTION = c("Critical approaches to social and cultural changes.", "", "Non-technical presentation of accounting for users of accounting", "information; introduction to financial and managerial accounting.", "Not open to students with course credits in accounting. Not", "available for unit or course credit toward a degree in accounting", "or business administration.", "Identification of key strategic insights.", "", "Supervision of research and writing, and final editing of articles", "and comments for publication in the Review of Law and Social", "Justice. For officers of the Review. Open to law students only.", "Graded IP to CR/D/F.", "", "The role of planning in sustaining community health.", "")),
               class = "data.frame", row.names = c(NA, -16L))

X_collapsed <- X
for(i in seq(nrow(X), 2, -1)) { # Work on the table in bottom to top so we can merge the values
  if(is.na(X_collapsed[i, "SECTION"])) { # only work on rows with NA in the SECTION column
    # Work on the current row and the previous row.
    # Don't modify numeric columns (don't want to merge NA values).
    # Use lead() to check across rows, and turn NA values into an empty string.
    # Use paste() to combine the row values.
    # use trim() to get rid of any excess white space produced.
    X_collapsed[i-1,] <- (X_collapsed[c(i-1,i),] %>%
      mutate(across(.fns = ~ ifelse(is.numeric(.x), .x, trim(paste(.x, ifelse(is.na(lead(.x)), "", lead(.x)))))))
    )[1, ]
  }
}

X_collapsed <- X_collapsed %>%
  filter(!is.na(SECTION)) # remove rows we don't want.

X_collapsed

This also removes extra whitespace due to the use of trim(). Without it you may end up with trailing spaces.

user_23351340
Related