Renaming columns with a value from another column

Question

I have a set of column names which are as follows

  "fb_metrics.3.name"                                     
  "fb_metrics.3.period"                                   
  "fb_metrics.3.values.0.value.share"                     
  "fb_metrics.3.values.0.value.like"                      
  "fb_metrics.3.values.0.value.comment"                   
  "fb_metrics.3.title"                                    
  "fb_metrics.3.description"

There is only one unique value each for the name and period. For example

> df[,"fb_metrics.3.name"]
[1] post_storytellers_by_action_type
Levels: post_storytellers_by_action_type

> df[, "fb_metrics.3.period"]
[1] lifetime
Levels: lifetime

I want to rename column 3,4,5 like this

[1] "post_storytellers_by_action_type.lifetime.share"  
[2] "post_storytellers_by_action_type.lifetime.like"   
[3] "post_storytellers_by_action_type.lifetime.comment"

I have managed the replacement bit like this

i=3
new_column_name <- paste(as.character(df[1,paste("fb_metrics.",
                    i,".name", sep = "")]),as.character(df[1,paste("fb_metrics.",
                    i,".period", sep = "")]), sep = "." )

sub(pattern = ".*value",replacement = new_column_name,x = colnames(df[,
        grep(paste("fb_metrics.",i,".values.*",sep = ""), column_names)]))

And I have extracted the columns to be replaced like this

column_names <- colnames(df)

list_of_columns <- colnames(df[,grep("fb_metrics.3.values.*", column_names)])

My question is how should I rename the extracted columns with the column names I just created? Also, is there an easier way to do it?

EDIT:

Ok I renamed it like this

library(data.table)
setnames(df, old = list_of_columns, new = sub(pattern = ".*value",replacement = new_column_name,x = colnames(df[,grep(paste("fb_metrics.",i,".values.*",sep = ""), column_names)])))

But is there a simpler way to do the whole process?

I tried this : colnames(df[,grep("fb_metrics.3.values.*", column_names)])<- sub(pattern = ".*value",replacement = new_column_name,x = colnames(df[, grep(paste("fb_metrics.",i,".values.*",sep = ""), column_names)])) but it didn't change the names — Sujay DSa
– Sujay DSa, Commented Apr 27, 2017 at 15:32
actually this is just one set among 100 odd sets of column names. So I wanted to write something generic which I could use in some kind of loop. I had assigned i a value just to mimic one of the iterations. — Sujay DSa
– Sujay DSa, Commented Apr 27, 2017 at 15:35

Silence Dogood · Accepted Answer · 2017-04-27 16:34:35Z

When faced with complex/multiple replacements,you can simplify the process by creating a custom function with varying input parameters and apply it sequentially to reach the desired column name structure.

InputData:

DF1 = data.frame(fb_metrics.3.name="post_storytellers_by_action_type",fb_metrics.3.period="lifetime",fb_metrics.3.values.0.value.share=1:5,fb_metrics.3.values.0.value.like=1:5,
fb_metrics.3.values.0.value.comment=1:5,stringsAsFactors=FALSE)


DF1
#                 fb_metrics.3.name fb_metrics.3.period fb_metrics.3.values.0.value.share
#1 post_storytellers_by_action_type            lifetime                                 1
#2 post_storytellers_by_action_type            lifetime                                 2
#3 post_storytellers_by_action_type            lifetime                                 3
#4 post_storytellers_by_action_type            lifetime                                 4
#5 post_storytellers_by_action_type            lifetime                                 5

#  fb_metrics.3.values.0.value.like fb_metrics.3.values.0.value.comment
#1                                1                                   1
#2                                2                                   2
#3                                3                                   3
#4                                4                                   4
#5                                5                                   5

CustomFunction:

#Custom function to aid replacements

 fn_modify_str = function(pattern="a",replacement="str",suffix=".",inputString="abcd") {

 gsub(pattern,paste0(replacement,suffix),inputString)

 }

Pararmeters:

 inputColumn1 = "^fb_metrics.3.name$"
 inputColumn2 = "^fb_metrics.3.period$"

 replacePattern1 = "fb_metrics.3.values.0[.]"
 replacePattern2 = "value"

 uniqValue1 = unique(DF1[,grep(inputColumn1,colnames(DF1))])

uniqValue1
#[1] "post_storytellers_by_action_type"

 uniqValue2 = unique(DF1[,grep(inputColumn2,colnames(DF1))])

uniqValue2
#[1] "lifetime"

Replacement:

#apply replacements using custom function  sequentially for both patterns

strPart1 =  fn_modify_str(pattern=replacePattern1,replacement = uniqValue1,suffix=".",inputString = colnames(DF3))

strPart2 =  fn_modify_str(pattern=replacePattern2,replacement = uniqValue2,suffix="",inputString = strPart1)


#you can rename columns in the same dataset by just simple assignment
#colnames(DF1) = strPart2

#OR, you can create a backup dataset and rename the columns in the new DF
DF2 = DF1

colnames(DF2) = strPart2

Output:

DF2
#                 fb_metrics.3.name fb_metrics.3.period post_storytellers_by_action_type.lifetime.share
#1 post_storytellers_by_action_type            lifetime                                               1
#2 post_storytellers_by_action_type            lifetime                                               2
#3 post_storytellers_by_action_type            lifetime                                               3
#4 post_storytellers_by_action_type            lifetime                                               4
#5 post_storytellers_by_action_type            lifetime                                               5
#  post_storytellers_by_action_type.lifetime.like post_storytellers_by_action_type.lifetime.comment
#1                                              1                                                 1
#2                                              2                                                 2
#3                                              3                                                 3
#4                                              4                                                 4
#5                                              5                                                 5

Collectives™ on Stack Overflow

Renaming columns with a value from another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related