CODEWITHSUNDEEP
rdataframerenamegsub
T Richard
T Richard

Reputation: 601

Insert dots in column names in wide data using R

The following data set is in the wide format and has repeated measures of "ql", "st" and "xy" prefixed by "a", "b" and "c";

df<-data.frame(id=c(1,2,3,4),
               ex=c(1,0,0,1),
               aql=c(5,4,NA,6),
               bql=c(5,7,NA,9),
               cql=c(5,7,NA,9),
               bst=c(3,7,8,9),
               cst=c(8,7,5,3),
               axy=c(1,9,4,4),
               cxy=c(5,3,1,4))

I'm looking for a way to insert dots after the prefixed letters "a", "b" and "c", while keeping other columns (i.e. id, ex) unchanged. I've been working around this using gsub function, e.g.

names(df) <- gsub("", "\\.", names(df))

but got undesired results. The expected output would look like

   id ex a.ql b.ql c.ql b.st c.st a.xy c.xy
1  1  1    5    5    5    3    8    1    5
2  2  0    4    7    7    7    7    9    3
3  3  0   NA   NA   NA    8    5    4    1
4  4  1    6    9    9    9    3    4    4

Upvotes: 1

Views: 150

Answers (4)

Mike V
Mike V

Reputation: 1364

Another way you can try

library(dplyr)
df %>% 
  rename_at(vars(aql:cxy), ~ str_replace(., "(?<=\\w{1})", "\\."))
#   id ex a.ql b.ql c.ql b.st c.st a.xy c.xy
# 1  1  1    5    5    5    3    8    1    5
# 2  2  0    4    7    7    7    7    9    3
# 3  3  0   NA   NA   NA    8    5    4    1
# 4  4  1    6    9    9    9    3    4    4

Upvotes: 1

Darren Tsai
Darren Tsai

Reputation: 35594

Try

sub("(^[a-c])(.+)", "\\1.\\2", names(df))

# [1] "id"   "ex"   "a.ql" "b.ql" "c.ql" "b.st" "c.st" "a.xy" "c.xy"

or

sub("(?<=^[a-c])", ".", names(df), perl = TRUE)

# [1] "id"   "ex"   "a.ql" "b.ql" "c.ql" "b.st" "c.st" "a.xy" "c.xy"

Upvotes: 2

Allan Cameron
Allan Cameron

Reputation: 174278

You can do

setNames(df, sub("(ql$)|(st$)|(xy$)", "\\.\\1\\2\\3", names(df)))

#>   id ex a.ql b.ql c.ql b.st c.st a.xy c.xy
#> 1  1  1    5    5    5    3    8    1    5
#> 2  2  0    4    7    7    7    7    9    3
#> 3  3  0   NA   NA   NA    8    5    4    1
#> 4  4  1    6    9    9    9    3    4    4

Upvotes: 1

Duck
Duck

Reputation: 39613

You can also try a tidyverse approach reshaping your data like this:

library(tidyverse)
#Data
df<-data.frame(id=c(1,2,3,4),
               ex=c(1,0,0,1),
               aql=c(5,4,NA,6),
               bql=c(5,7,NA,9),
               cql=c(5,7,NA,9),
               bst=c(3,7,8,9),
               cst=c(8,7,5,3),
               axy=c(1,9,4,4),
               cxy=c(5,3,1,4))
#Reshape
df %>% pivot_longer(-c(1,2)) %>%
  mutate(name=paste0(substring(name,1,1),'.',substring(name,2,nchar(name)))) %>%
  pivot_wider(names_from = name,values_from=value)

Output:

# A tibble: 4 x 9
     id    ex  a.ql  b.ql  c.ql  b.st  c.st  a.xy  c.xy
  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1     1     1     5     5     5     3     8     1     5
2     2     0     4     7     7     7     7     9     3
3     3     0    NA    NA    NA     8     5     4     1
4     4     1     6     9     9     9     3     4     4

Upvotes: 0

Related Questions