Automate columns merging where merged values are separated with a delimiter in the container

user_13083838 0
Viewed 46

I have a big dataframe where a lot of columns's column names have . in their names. Here is my approach in the example below:

#this somewhat resembles what we have in hand
df <- data.frame(id= c("HD1", "HD2", "HD3", "HD4"),
                 mon.1= c(1, 0, 1, 4),
                 mon.2= c("a", "b", "c", "d"),
                 mon.2.4.1...1= c("#ji", "#ki", NA, "#ui"),
                 tue.6= c("1", "2", "3", "4"),
                 tue= c(190, 2345, 41, 89),
                 heh= c("1mn", "2a", "g78", "asd324"),
                 wed= c(1890, 9002, 14341, 657),
                 wed.01= c(NA, "@ksdf", NA, NA),
                 thu.0234= c("@jksdff", "@sfd", "@kukg.676", "@jdkfjk"),
                 rating= c(1,2,3,4))

#in order to collapse the columns, we can apply a mapply variant

#here i attach a new column to original df named combined1 which collapses all mon.....
df<- within(df, 
       combined1 <- Map(list, as.character(mon.1),
                        as.character(mon.2),
                        as.character(mon.2.4.1...1)))

#process repeats for others
df <- within(df,
         combined2 <- Map(list, as.character(tue.6),
                        as.character(tue)))

df <- within(df,
        combined3 <- Map(list, as.character(wed),
                        as.character(wed.01)))

Yields a comma delimintaed columns Combined1, Combined2,Combined3 :

# A tibble: 4 x 14
  id    mon.1 mon.2 mon.2.4.1...1 tue.6   tue heh     wed wed.01 thu.0234 rating
  <fct> <dbl> <fct> <fct>         <fct> <dbl> <fct> <dbl> <fct>  <fct>     <dbl>
1 HD1       1 a     #ji           1       190 1mn    1890 NA     @jksdff       1
2 HD2       0 b     #ki           2      2345 2a     9002 @ksdf  @sfd          2
3 HD3       1 c     NA            3        41 g78   14341 NA     @kukg.6~      3
4 HD4       4 d     #ui           4        89 asd3~   657 NA     @jdkfjk       4
# ... with 3 more variables: combined1 <named list>, combined2 <named list>,
#   combined3 <named list>

My problem is that there are about 20-30 columns for mon, tue and wed and I am having issue creating a method where it will read all for eg, wed, wed1.43654, wed.46 and so on. Then I don't have to manually type them in. Will appreciate your help!

EDIT Something like this 

> df[,11:14]
  rating combined1 combined2   combined3
1      1 1, a, #ji    1, 190    1890, NA
2      2 0, b, #ki   2, 2345 9002, @ksdf
3      3  1, c, NA     3, 41   14341, NA
4      4 4, d, #ui     4, 89     657, NA
data-wrangling data-cleaning dataframe r
3 Answers

Here is one option where we remove the suffix part of the column names starting with . ('nm1'), create a frequency table, extract those names having more than one count ('nm2'), loop over those unique names, extract the columns from the dataset with grep, and assign it to create 'combined' columns

nm1 <- sub("\\..*", "", names(df))
nm2 <- names(which(table(nm1) > 1))
df[paste0('combined', seq_along(nm2))] <- lapply(nm2, 
        function(x) df[grep(x, names(df))])



df$combined1
#  mon.1 mon.2 mon.2.4.1...1
#1     1     a           #ji
#2     0     b           #ki
#3     1     c          <NA>
#4     4     d           #ui

If we want this to be a list column

df[paste0('combined', seq_along(nm2))] <- lapply(nm2, 
        function(x) 
        apply(df[grep(x, names(df))], 1, function(x) as.list(c(x))))


df
#   id mon.1 mon.2 mon.2.4.1...1 tue.6  tue    heh   wed wed.01  thu.0234 rating combined1 combined2    combined3
#1 HD1     1     a           #ji     1  190    1mn  1890   <NA>   @jksdff      1 1, a, #ji   1,  190     1890, NA
#2 HD2     0     b           #ki     2 2345     2a  9002  @ksdf      @sfd      2 0, b, #ki   2, 2345  9002, @ksdf
#3 HD3     1     c          <NA>     3   41    g78 14341   <NA> @kukg.676      3  1, c, NA   3,   41    14341, NA
#4 HD4     4     d           #ui     4   89 asd324   657   <NA>   @jdkfjk      4 4, d, #ui   4,   89      657, NA
user_37322710

WolfgangBagdanow-

Yeah please, It would be nice if can have them in a list form or something because I will writing them to a file. My goal is to cut-down unnecessary column creations. I put down how I would like in my question above

This is sort of extension on @akrun's second answer. If you do decide to write this df to a csv file then know that neither readr, data.table nor base write csv method will work because they don't have method implemented yet that write a list to a csv file (as far as I know). In order to write your dataframe which will have a list columns to a csv file you would need something like this-

#@akrun 's list method-
#df <- data.frame(...)

df[paste0('combined', seq_along(nm2))] <- lapply(nm2, 
        function(x) 
        apply(df[grep(x, names(df))], 1, function(x) as.list(c(x))))

#------------------------------------
set_lists_to_chars <- function(x) {
  if(class(x) == 'list') {
    y <- paste(unlist(x[1]), sep='', collapse=', ')
  } else {
    y <- x 
  }
  return(y)
}
new_frame <- data.frame(lapply(tibble(df), set_lists_to_chars), stringsAsFactors = F)
write.csv(new_frame, file='test.csv')

Alternatively, you can also convert your list to char using apply method. Anyways, this would get you what want in csv. Hopefully. Nice question by the way, paired with an excellent answer given by @akrun. Good luck!

user_113405590

Well if you're trying to prep for csv a common dodge is using pipes to separate so full credit to @akrun for the hard part but...

nm1 <- sub("\\..*", "", names(df))
nm2 <- names(which(table(nm1) > 1))

df[paste0('combined', seq_along(nm2))] <- lapply(nm2, 
                                                 function(x) 
                                                   apply(df[grep(x, names(df))], 
                                                         1, 
                                                         function(x) str_replace_all(toString(x), 
                                                                                     pattern = ", ", 
                                                                                     replacement = "|")))

> df
   id mon.1 mon.2 mon.2.4.1...1 tue.6  tue    heh   wed wed.01  thu.0234 rating combined1 combined2   combined3
1 HD1     1     a           #ji     1  190    1mn  1890   <NA>   @jksdff      1   1|a|#ji    1| 190     1890|NA
2 HD2     0     b           #ki     2 2345     2a  9002  @ksdf      @sfd      2   0|b|#ki    2|2345  9002|@ksdf
3 HD3     1     c          <NA>     3   41    g78 14341   <NA> @kukg.676      3    1|c|NA    3|  41    14341|NA
4 HD4     4     d           #ui     4   89 asd324   657   <NA>   @jdkfjk      4   4|d|#ui    4|  89      657|NA
user_91900340
Related