Combine multiple columns keeping variable name as part of data

Question

I have data as below

      df=data.frame(
     Id=c("001","002","003","004"),
     author=c('John Cage','Thomas Carlyle'),
     circa=c('1988', '1817'),
     quote=c('I cant understand why people are frightened of new ideas. Im frightened of the old ones.',
      'My books are friends that never fail me.')
  )
df

I would like to combine 3 columns to obtain the data frame below

     df2 = data.frame(
          Id=c("001","002"),
          text = c(    
              'Author: 
              John Cage

             Circa:
             1988

             quote: 
             I cant understand why people are frightened of new ideas. Im frightened of the old ones.
             ',
               'Author: 
               Thomas Carlyle

             Circa:
             1817

             quote: 
             My books are friends that never fail me.
              '
         )
         )
     df2

I am aware I can use paste or unite from tidyr, but how can I pass the column names to be within the new created column?

Ronak Shah · Accepted Answer · 2019-12-12 12:52:15Z

1

You can get the data in long format and then paste by group.

library(dplyr)

df %>%
  tidyr::pivot_longer(cols = -Id) %>%
  group_by(Id) %>%
  summarise(text = paste(name, value, sep = ":", collapse = "\n"))

# A tibble: 4 x 2
#  Id    text                                                                    
#  <fct> <chr>                                                                   
#1 001   "author:John Cage\ncirca:1988\nquote:I cant understand why people are f…
#2 002   "author:Thomas Carlyle\ncirca:1817\nquote:My books are friends that nev…
#3 003   "author:John Cage\ncirca:1988\nquote:I cant understand why people are f…
#4 004   "author:Thomas Carlyle\ncirca:1817\nquote:My books are friends that nev…

answered Dec 12, 2019 at 12:52

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ThomasIsCoding · Accepted Answer · 2019-12-12 12:34:50Z

Here is a solution with base R, where paste0() is used. Maybe the following code can help you make it

res <- cbind(df[1],text = apply(apply(df[-1], 1, function(v) paste0(names(df[-1]),": ",v)), 2, paste0, collapse = "\n"))

such that

> res
   Id                                                                                                                            text
1 001 author: John Cage\ncirca: 1988\nquote: I cant understand why people are frightened of new ideas. Im frightened of the old ones.
2 002                                            author: Thomas Carlyle\ncirca: 1817\nquote: My books are friends that never fail me.

DATA

df <- structure(list(Id = structure(1:2, .Label = c("001", "002"), class = "factor"), 
    author = structure(1:2, .Label = c("John Cage", "Thomas Carlyle"
    ), class = "factor"), circa = structure(2:1, .Label = c("1817", 
    "1988"), class = "factor"), quote = structure(1:2, .Label = c("I cant understand why people are frightened of new ideas. Im frightened of the old ones.", 
    "My books are friends that never fail me."), class = "factor")), class = "data.frame", row.names = c(NA, 
-2L))

akrun · Accepted Answer · 2019-12-12 13:51:52Z

1

We can use melt in data.table

library(data.table)
melt(setDT(df), id.var = 'Id')[, .(text = paste(variable, 
       value, sep=":", collapse="\n")), Id]
#     Id                                                                                                                         text
#1: 001 author:John Cage\ncirca:1988\nquote:I cant understand why people are frightened of new ideas. Im frightened of the old ones.
#2: 002                                            author:Thomas Carlyle\ncirca:1817\nquote:My books are friends that never fail me.

answered Dec 12, 2019 at 13:51

akrun

891k38 gold badges590 silver badges700 bronze badges

Collectives™ on Stack Overflow

Combine multiple columns keeping variable name as part of data

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related