Renaming duplicate rows in R
I have an R data frame that has two columns of rows. One of the columns (for example, column 1) has duplicate values. I need to move this column so that it duplicates rows renamed with ordered suffixes like Column1.new
Column1 Column2 Column1.new
1 A 1_1
1 B 1_2
2 C 2_1
2 D 2_2
3 E 3
4 F 4
Any ideas on how to do this would be appreciated.
Greetings,
Antti
source to share
Let's say your data (ordered by Column1
) is inside an object named tab
. First create a run length object
c1.rle <- rle(tab$Column1)
c1.rle
##lengths: int [1:4] 2 2 1 1
##values : int [1:4] 1 2 3 4
This gives you the values Column1
and the corresponding number of occurrences of each item. Then use this information to create a new column with unique IDs:
tab$Column1.new <- paste0(rep(c1.rle$values, times = c1.rle$lengths), "_",
unlist(lapply(c1.rle$lengths, seq_len)))
Not sure if this fits your situation, but you can just paste together Column1
and Column2
to create a unique ID ...
source to share
Maybe a little more workaround, but parts of this might be more useful and simpler for those who don't have exactly the same needs. make.names
with attribute unique=T
adds duplicate point and number names:
x <- make.names(tab$Column1,unique=T)
> print(x)
[1] "X1" "X1.1" "X2" "X2.1" "X3" "X4"
This may be enough for some people. Here you can grab the first elements of the repeating elements but not the repeating elements and then add .0
to the end.
y <- rle(tab$Column1)
tmp <- !duplicated(tab$Column1) & (tab$Column1 %in% y$values[y$lengths>1])
x[tmp] <- str_replace(x[tmp],"$","\\.0")
> print(x)
[1] "X1.0" "X1.1" "X2.0" "X2.1" "X3" "X4"
Replace dots and remove X
x <- str_replace(x,"X","")
x <- str_replace(x,"\\.","_")
> print(x)
[1] "1_0" "1_1" "2_0" "2_1" "3" "4"
Perhaps you will be good enough for you. But if you want indexing to start at 1, grab the numbers, add them, and return them.
z <- str_match(x,"_([0-9]*)$")[,2]
z <- as.character(as.numeric(z)+1)
x <- str_replace(x,"_([0-9]*)$",paste0("_",z))
> print(x)
[1] "1_1" "1_2" "2_1" "2_2" "3" "4"
As I said, there is a more workaround here, but gives some options.
source to share
d <- read.table(text='Column1 Column2
1 A
1 B
2 C
2 D
3 E
4 F', header=TRUE)
transform(d,
Column1.new = ifelse(duplicated(Column1) | duplicated(Column1, fromLast=TRUE),
paste(Column1, ave(Column1, Column1, FUN=seq_along), sep='_'),
Column1))
# Column1 Column2 Column1.new
# 1 1 A 1_1
# 2 1 B 1_2
# 3 2 C 2_1
# 4 2 D 2_2
# 5 3 E 3
# 6 4 F 4
source to share
@ Cão's answer with base R only:
x=read.table(text="
Column1 Column2 #Column1.new
1 A #1_1
1 B #1_2
2 C #2_1
2 D #2_2
3 E #3
4 F #4", stringsAsFactors=F, header=T)
string<-x$Column1
mstring <- make.unique(as.character(string) )
mstring<-sub("(.*)(\\.)([0-9]+)","\\1_\\3",mstring)
y <- rle(string)
tmp <- !duplicated(string) & (string %in% y$values[y$lengths>1])
mstring[tmp]<-gsub("(.*)","\\1_0", mstring[tmp])
end <- sub(".*_([0-9]+)","\\1",grep("_([0-9]*)$",mstring,value=T) )
beg <- sub("(.*_)[0-9]+","\\1",grep("_([0-9]*)$",mstring,value=T) )
newend <- as.numeric(end)+1
mstring[grep("_([0-9]*)$",mstring)]<-paste0(beg,newend)
x$Column1New<-mstring
x
source to share