paste0 is converting everything into character

Question

I have a R dataframe which contains all types of mixed datatypes e.g., Factor, int and POSIXct variables. I want to concatenate all variables and combine them without changing their format.

My end output should look like this

('a', 2 ,'2019-02-11 15:57:56')('b', 3 ,'2019-02-11 15:57:56')

While using paste0 , it is converting everything into character.. Need your help on this. Is there any other alternative apart from paste0 to combine everything and get output like above.

library(lubridate)
library(dplyr)
dat <- data.frame(id = letters[1:2], x = 2:3, date = now())
dat
str(dat)
'data.frame':   2 obs. of  3 variables:
 $ id  : Factor w/ 2 levels "a","b": 1 2
 $ x   : int  2 3
 $ date: POSIXct, format: "2019-02-11 15:57:56" "2019-02-11 15:57:56"

dat[1,]
  id x                date
1  a 2 2019-02-11 15:57:56

Currently I am using this but gave undesired results.

paste0('(',paste0(dat[1,],collapse = "','"), ')')
[1] "(1','2','1549880876.5498)"

Answer 1

I realized now what you meant - you want variables of type character to be quoted in the string, and the others not. A couple of problems - First, the way you are collapsing with ',', you are adding quotes on everything. Second, the way you are creating the dataframe is converting the dates to a double, and the letters to an integer:

> library(lubridate)
> library(dplyr)
> dat <- data.frame(id = letters[1:2], x = 2:3, date = now())
> typeof(dat$id[1])
[1] "integer"
> typeof(dat$date[1])
[1] "double"
>

so it will be hard to detect programmatically you meant these are strings. Use I to make sure characters remain as such, and use format to make sure the date is a string (It has options, but I am not using them):

> dat <- data.frame(id = I(letters[1:2]), x = 2:3, date = I(format(now())))
> dat
  id x                date
1  a 2 2019-02-11 15:01:52
2  b 3 2019-02-11 15:01:52
> typeof(dat$date[1])
[1] "character"
> typeof(dat$id[1])
[1] "character"

Now that out dataframe has the right types, let's prepare it before pasting to already quote stuff we want quoted in the end:

> as.data.frame(lapply(dat,function(x) { if (is.character(x)) paste0("'",x,"'") else x }))
   id x                  date
1 'a' 2 '2019-02-11 15:01:52'
2 'b' 3 '2019-02-11 15:01:52'

Now pasting becomes simpler - we need to collapse each row, and surround it with parenthesis, and then collapse the resulting strings. In a single line this is (after fixing dat as above):

> paste0(
    apply(
        as.data.frame(
            lapply(dat,function(x) { if (is.character(x)) paste0("'",x,"'") else x })),
        1,function(x){paste0('(',paste0(x,collapse=','),')')})
    ,collapse=',')   
 [1] "('a',2,'2019-02-11 15:01:52'),('b',3,'2019-02-11 15:01:52')"

A long line, so I broke it up a bit.

Answer 2

The glue package is fantastic for these use-cases:

library(tidyverse)
library(glue)

dat <- data.frame(id = letters[1:2], x = 2:3, date = lubridate::now())

dat %>%
  mutate(
    description = glue("('{id}', {x}, '{date}')")
  )
#>   id x                date                     description
#> 1  a 2 2019-02-11 09:53:29 ('a', 2, '2019-02-11 09:53:29')
#> 2  b 3 2019-02-11 09:53:29 ('b', 3, '2019-02-11 09:53:29')

Moreover, if you want just the new description column, you could add %>% pull(description) to the pipeline to get:

(above code) %>% pull(description)

('a', 2, '2019-02-11 09:52:14')
('b', 3, '2019-02-11 09:52:14')

And then perhaps even a paste(collapse = ",") depending on what you plan to do with the output:

(above code) %>% pull(description) %>% paste(collapse = ",")

[1] "('a', 2, '2019-02-11 09:52:14'),('b', 3, '2019-02-11 09:52:14')"