R - Combine non numerical and numerical data together in the same cell in a dataframe

Question

I want to combine cell values with numeric and character data into the same cell.

Specifically, I'm trying to combine named columns with the 'SEM' columns, with a ± symbol in between, to eventually become a table I can publish with latex

# A tibble: 4 x 10
  Variety       n Probes Probes SEM Walks Walks SEM Cleans Cleans SEM Off_Leaf Off_SEM
  <chr>     <int>  <dbl>        <dbl> <dbl>       <dbl>  <dbl>        <dbl>    <dbl>   <dbl>
1 10LB mean    41   1.40        0.140 0.710       0.170  0.460       0.140    0.120   0.0520
2 3LB mean     48   1.50        0.130 0.880       0.170  0.310       0.0900   0.190   0.0710
3 4LB mean     43   1.80        0.160 1.10        0.190  0.370       0.120    0.280   0.190 
4 RB mean      44   2.80        0.390 1.50        0.260  0.180       0.0750   0.0910  0.0440

Is there any way to make the previous table look like this:

# A tibble: 4 x 6
  Variety     n Probes    Walks     Cleans     Off Leaf 
  <chr>   <int> <chr>     <chr>     <chr>      <chr>      
1 10LB       41 1.4 ± 0.1 0.7 ± 0.2 0.5 ± 0.1  0.1 ± 0.05 
2 3LB        48 1.5 ± 0.1 0. 9± 0.2 0.3 ± 0.09 0.2 ± 0.07 
3 4LB        43 1.8 ± 0.2 1.1 ± 0.2 0.4 ± 0.1  0.3 ± 0.2  
4 RB         44 2.8 ± 0.4 1.5 ± 0.3 0.2 ± 0.07 0.09 ± 0.04

while remaining in R?

Could you just `paste` the columns together - e.g.: `paste(1:3, "±", c(0.1,0.2,0.3))` ? So something like `paste(dat$x, "±", dat$y)` — thelatemail, Mar 09 '18 at 00:49

score 1 · Accepted Answer · answered Mar 09 '18 at 01:19

With the following dataset.

ds <- tibble::tribble(
  ~Variety, ~n, ~Probes, ~`Probes SEM`, ~`Walks`, ~`Walks SEM`, ~`Cleans`, ~`Cleans SEM`, ~`Off_Leaf`, ~`Off_SEM`,
  "10LB mean"   , 41L, 1.40, 0.140, 0.710, 0.170, 0.460, 0.140   , 0.120 ,  0.0520,
  "3LB mean"    , 48L, 1.50, 0.130, 0.880, 0.170, 0.310, 0.0900  , 0.190 ,  0.0710,
  "4LB mean"    , 43L, 1.80, 0.160, 1.10 , 0.190, 0.370, 0.120   , 0.280 ,  0.190 ,
  "RB mean"     , 44L, 2.80, 0.390, 1.50 , 0.260, 0.180, 0.0750  , 0.0910,  0.0440
)

The \u00b1 character will produce the plus/minus in a way that's hopefully portable across files with different encodings.

library(magrittr)
ds %>% 
  dplyr::mutate(
    Probes    = sprintf("%2.1f \u00B1 %3.3f", .data$Probes  , .data$`Probes SEM`),
    Walks     = sprintf("%3.2f \u00B1 %3.3f", .data$Walks   , .data$`Walks SEM` ),
    Cleans    = sprintf("%3.2f \u00B1 %3.3f", .data$Cleans  , .data$`Cleans SEM`),
    Off_Leaf  = sprintf("%3.2f \u00B1 %2.2f", .data$Off_Leaf, .data$Off_SEM     )
  ) %>% 
  dplyr::select(
    -`Probes SEM`, -`Walks SEM`, -`Cleans SEM`, -Off_SEM
  )

Although it's not part of your question, I recommend something like sprintf() to make sure the elements have the same number of digits all the way down a column. It looks better with padded zeros, and it takes some of the burden off LaTeX to align it correctly.

Output:

# A tibble: 4 x 6
  Variety       n Probes      Walks        Cleans       Off_Leaf   
  <chr>     <int> <chr>       <chr>        <chr>        <chr>      
1 10LB mean    41 1.4 ± 0.140 0.71 ± 0.170 0.46 ± 0.140 0.12 ± 0.05
2 3LB mean     48 1.5 ± 0.130 0.88 ± 0.170 0.31 ± 0.090 0.19 ± 0.07
3 4LB mean     43 1.8 ± 0.160 1.10 ± 0.190 0.37 ± 0.120 0.28 ± 0.19
4 RB mean      44 2.8 ± 0.390 1.50 ± 0.260 0.18 ± 0.075 0.09 ± 0.04

Also be aware of the LaTeX \pm command if you use a table-formatting package like kableExtra or xtable that may process the unicode different, but allows you to escape the \pm.

akrun · Answer 2 · 2018-03-09T01:53:23.383

We can do this with data.table by melting into 'long' format by specifying the measure argument with the patterns and then do the paste

library(data.table)
melt(setDT(df1), measure = patterns("Probes", "Walks", "Cleans", "Off"), 
  value.name = c("Probes", "Walks", "Cleans", "Off"))[, 
  lapply(.SD, function(x) paste(round(x[variable == 1], 1), 
                     round(x[variable ==2], 2), sep=" ± ")) , 
         by = .(Variety, n), .SDcols = Probes:Off]
#    Variety  n     Probes      Walks     Cleans        Off
#1: 10LB mean 41 1.4 ± 0.14 0.7 ± 0.17 0.5 ± 0.14 0.1 ± 0.05
#2:  3LB mean 48 1.5 ± 0.13 0.9 ± 0.17 0.3 ± 0.09 0.2 ± 0.07
#3:  4LB mean 43 1.8 ± 0.16 1.1 ± 0.19 0.4 ± 0.12 0.3 ± 0.19
#4:   RB mean 44 2.8 ± 0.39 1.5 ± 0.26 0.2 ± 0.08 0.1 ± 0.04

If we are using tidyverse, a similar approach (though it doesn't take any patterns - as the values are all numeric type) would be to gatherinto 'long' format, and then spread

library(tidyverse)
df1 %>% 
   gather(key, val, Probes:Off_SEM) %>%
   separate(key, into = c('key1', 'key2')) %>% 
   group_by(Variety, n, key1) %>%
   summarise(val = paste(first(val), last(val), sep= " ± ")) %>% 
   spread(key1, val)
# A tibble: 4 x 6
# Groups: Variety, n [4]
#   Variety       n Cleans       Off           Probes     Walks      
#* <chr>     <int> <chr>        <chr>         <chr>      <chr>      
#1 10LB mean    41 0.46 ± 0.14  0.12 ± 0.052  1.4 ± 0.14 0.71 ± 0.17
#2 3LB mean     48 0.31 ± 0.09  0.19 ± 0.071  1.5 ± 0.13 0.88 ± 0.17
#3 4LB mean     43 0.37 ± 0.12  0.28 ± 0.19   1.8 ± 0.16 1.1 ± 0.19 
#4 RB mean      44 0.18 ± 0.075 0.091 ± 0.044 2.8 ± 0.39 1.5 ± 0.26

data

df1 <-  structure(list(Variety = c("10LB mean", "3LB mean", "4LB mean", 
"RB mean"), n = c(41L, 48L, 43L, 44L), Probes = c(1.4, 1.5, 1.8, 
2.8), Probes_SEM = c(0.14, 0.13, 0.16, 0.39), Walks = c(0.71, 
0.88, 1.1, 1.5), Walks_SEM = c(0.17, 0.17, 0.19, 0.26), Cleans = c(0.46, 
0.31, 0.37, 0.18), Cleans_SEM = c(0.14, 0.09, 0.12, 0.075), Off_Leaf = c(0.12, 
0.19, 0.28, 0.091), Off_SEM = c(0.052, 0.071, 0.19, 0.044)), .Names = c("Variety", 
"n", "Probes", "Probes_SEM", "Walks", "Walks_SEM", "Cleans", 
"Cleans_SEM", "Off_Leaf", "Off_SEM"), class = "data.frame", row.names = c("1", 
"2", "3", "4"))

Onyambu · Answer 3 · 2018-03-09T06:21:48.493

We can also use base R:

df2=df1   #I am creating a copy in order not to mess with the original
u=grep("SEM",names(df1))#Find the columns that have the SEM word
df2[,u]=round(df2[,u],1)# Round te Sem columns.
m=gsub("(\\d+\\S+)\\s(\\d+\\S+)?","\\1±\\2",do.call(paste,c(df2[-(1:2)])))#Colapse the columns

cbind(df2[1:2],read.table(text=m))#Read the columns and cbind them to df1[1:2]

 Variety  n      V1       V2       V3       V4
1 10LB mean 41 1.4±0.1 0.71±0.2 0.46±0.1 0.12±0.1
2  3LB mean 48 1.5±0.1 0.88±0.2 0.31±0.1 0.19±0.1
3  4LB mean 43 1.8±0.2  1.1±0.2 0.37±0.1 0.28±0.2
4   RB mean 44 2.8±0.4  1.5±0.3 0.18±0.1  0.091±0

You can also Set the names of the columns.

setNames(cbind(df2[1:2],read.table(text=m)),names(df1[-u]))
    Variety  n  Probes    Walks   Cleans Off_Leaf
1 10LB mean 41 1.4±0.1 0.71±0.2 0.46±0.1 0.12±0.1
2  3LB mean 48 1.5±0.1 0.88±0.2 0.31±0.1 0.19±0.1
3  4LB mean 43 1.8±0.2  1.1±0.2 0.37±0.1 0.28±0.2
4   RB mean 44 2.8±0.4  1.5±0.3 0.18±0.1  0.091±0

If you do not round, And maybe you also need the spacing between the signs:

 u=grep("SEM",names(df1))
 m=gsub("(\\d+[.]\\d+):(\\d+[.]\\d+)","\\1 ± \\2",do.call(paste,c(df1[-(1:2)],sep=":")))
 setNames(cbind(df1[1:2],read.table(text=m,sep=":")),names(df1[-u]))

    Variety  n     Probes       Walks       Cleans      Off_Leaf
1 10LB mean 41 1.4 ± 0.14 0.71 ± 0.17  0.46 ± 0.14  0.12 ± 0.052
2  3LB mean 48 1.5 ± 0.13 0.88 ± 0.17  0.31 ± 0.09  0.19 ± 0.071
3  4LB mean 43 1.8 ± 0.16  1.1 ± 0.19  0.37 ± 0.12   0.28 ± 0.19
4   RB mean 44 2.8 ± 0.39  1.5 ± 0.26 0.18 ± 0.075 0.091 ± 0.044

You can decide not to round. And just use the lines `u=..` and `m=` and the `cbind..` In this case you do not need to create a copy since you are using the whole values as they are. Also not that no `*apply` family or `forloops` needed. — Onyambu, Mar 09 '18 at 05:32

R - Combine non numerical and numerical data together in the same cell in a dataframe

3 Answers3

data