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?
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