How to rename a whitespace contained column names with look up table using dplyr

Question

I have the following data frame. The column names to be replaced contained whitespace. So this is different from previous post.

library(tidyverse)

dat <- tribble(
  ~group, ~y, ~`ARE(NR)/LNCAP-AR-ChIP-Seq(GSE27824)/Homer Best Motif log-odds Score`, ~`Znf263/K562-Znf263-ChIP-Seq/Homer Best Motif log-odds Score` ,
  "group_1",  "foo", 10,  3, 
  "group_2",  "bar", 700, 4,
  "group_2",  "qux", 150, 5
)

dat 
#> # A tibble: 3 x 4
#>     group     y
#>     <chr> <chr>
#> 1 group_1   foo
#> 2 group_2   bar
#> 3 group_2   qux
#> # ... with 2 more variables: `ARE(NR)/LNCAP-AR-ChIP-Seq(GSE27824)/Homer
#> #   Best Motif log-odds Score` <dbl>, `Znf263/K562-Znf263-ChIP-Seq/Homer
#> #   Best Motif log-odds Score` <dbl>

lookup_dat <- tribble(
  ~old, ~new,
  'ARE(NR)/LNCAP-AR-ChIP-Seq(GSE27824)/Homer Best Motif log-odds Score', 'ARE',
  'Znf263/K562-Znf263-ChIP-Seq/Homer Best Motif log-odds Score', 'Znf263'
)

And a lookup table for converting column names. If the column name in dat is not contained in the lookup_dat$old then retain the column name.

lookup_dat
#> # A tibble: 2 x 2
#>                                                                   old
#>                                                                 <chr>
#> 1 ARE(NR)/LNCAP-AR-ChIP-Seq(GSE27824)/Homer Best Motif log-odds Score
#> 2         Znf263/K562-Znf263-ChIP-Seq/Homer Best Motif log-odds Score
#> # ... with 1 more variables: new <chr>

The final new data frame I hope to get is this:

  group      y   ARE Znf263
  group_1  foo    10    3 
  group_2  bar   700    4
  group_2  qux   150    5

How can I do that?

I tried with this, but with error:

> dat %>% 
+   rename_(.dots=with(lookup_dat, setNames(as.list(as.character(old)), new)))
Error in parse(text = x) : <text>:1:43: unexpected symbol
1: ARE(NR)/LNCAP-AR-ChIP-Seq(GSE27824)/Homer Best
                                              ^

@jdobres I tried. Won't do. Would you give the working example? — littleworth, Oct 11 '17 at 01:10
@mt1022 It's different. Coz in my post the column name contain whitespace. — littleworth, Oct 11 '17 at 01:19

Psidom · Answer 1 · 2017-10-11T01:30:25.783

Use rename with UQS (or !!!); setNames(lookup_dat$old, lookup_dat$new) creates a named vector mapping from old names to new names, !!! splice the vector as separate arguments to rename:

rename(dat, !!!setNames(lookup_dat$old, lookup_dat$new))

# A tibble: 3 x 4
#    group     y   ARE Znf263
#    <chr> <chr> <dbl>  <dbl>
#1 group_1   foo    10      3
#2 group_2   bar   700      4
#3 group_2   qux   150      5

score 1 · Answer 2 · answered Oct 11 '17 at 01:27

One could also use tidyr to gather up all the troublesome column names, then merge with the lookup table, and spread using the new column names:

dat.long <- gather(dat, column, value, -group, -y) %>% 
  left_join(lookup_dat, by = c(column = 'old')) %>% 
  select(-column) %>% 
  spread(new, value)

# A tibble: 3 × 4
    group     y   ARE Znf263
*   <chr> <chr> <dbl>  <dbl>
1 group_1   foo    10      3
2 group_2   bar   700      4
3 group_2   qux   150      5

How to rename a whitespace contained column names with look up table using dplyr

2 Answers2