R - Combine two columns in one dataframe, create new column header, repeat over large dataset using lapply and merge

2
votes

I am a novice R user and I have a large dataframe (1700 columns) that is organized by data and flag columns:

df <- data.frame( "100249 MERCURY TOTAL ug/L" = runif(10),
              "100397 TRIHALOMETHANES ug/L" = runif(10),
              "100397 TRIHALOMETHANES ug/L FLAG" = c("L", "L", NA, "L", "L", NA, "L", NA, NA, NA),
              "100407 XYLENE ug/L" = runif(10),
              "100407 XYLENE ug/L FLAG" = c("L", NA, "L", "L", "L", NA, "L", NA, "L", "L"), 
              check.names=FALSE )

There is no pattern to the data, not every parameter has an associated FLAG column.

I would like to merge the matching data and flags together, with the flag first and then the numeric data in each cell, and rename the merged columns '100397 TRIHALOMETHANES ug/_COMB' and repeat for every pair of data & flag columns.

So far I have got it to search for the FLAG columns using grepl, create a new header using gsub and paste0, but the lapply and merge functions to combine the columns are not working for me. I have also have been reading that people use 'paste' or tidyr's 'unite' to merge columns, but nothing has been successful so far.

A sample of my code:

lapply(df, function(x) if (grepl("*\\FLAG$", colnames(df(x)))) {


  newcol = paste0("df$", gsub("*\\FLAG$", "", colnames(df(x))), "_COMB")
  assign(newcol, merge(df[x], df[x-1], all= T))

})

Any advice would be greatly appreciated, thank you.

1
rregexmergelapplypaste
Please show (not tell) the desired output. - Parfait

1 Answers

1
votes
Reduce(f = function(dat, col) {
  x <- sub(" ?FLAG$", "", col)
  if (!x %in% names(dat)) return(dat)
  dat[paste0(x, "_COMB")] <- paste(dat[[col]], dat[[x]])
  dat[c(col, x)] <- NULL
  dat
}, x = grep("FLAG$", names(df), value = TRUE), init = df)

#    100249 MERCURY TOTAL ug/L 100397 TRIHALOMETHANES ug/L_COMB 100407 XYLENE ug/L_COMB
# 1                 0.04353999              L 0.375519647961482      L 0.95818781433627
# 2                 0.49308933              L 0.931443430483341    NA 0.744603316066787
# 3                 0.68270299             NA 0.409499574452639     L 0.993966163368896
# 4                 0.26546071             L 0.0351015995256603     L 0.696171462768689
# 5                 0.95956891              L 0.603019695729017     L 0.709421107778326
# 6                 0.01842927              NA 0.96781362616457    NA 0.201458259951323
# 7                 0.12114176              L 0.734256325522438     L 0.457969205919653
# 8                 0.93771709             NA 0.309347201371565    NA 0.508297981694341
# 9                 0.47122685             NA 0.822285959031433      L 0.87013426842168
# 10                0.11501974              NA 0.56137450854294     L 0.153437153436244

Or:

dat <- df
for (col in grep("FLAG$", names(df), value = TRUE)) {
  x <- sub(" ?FLAG$", "", col)
  if (!x %in% names(dat)) next
  dat[paste0(x, "_COMB")] <- paste(dat[[col]], dat[[x]])
  dat[c(col, x)] <- NULL
}
dat