Using a while loop to create a new variable based on duplicated values in R

user_8667256 0
Viewed 143

So I have a set of data that looks like this:

group  ID
aa     123
ab     123
bb     345
bb     345
bb     999
bb     999
cc     567
cd     567

When the ID is the same, the group should be equal to the second entry for that ID. So the corrected data should be this:

group  ID   group2
aa     123  ab
ab     123  ab
bb     345  bb
bb     345  bb
bb     999  bb
bb     999  bb
cc     567  cd
cd     567  cd

I also need to create a new variable to store the correct group. Here is what I have been trying:

n <- 1 + (1:(as.numeric(nrow(data))))
l <- 1:(as.numeric(nrow(data)))
while (data[n,1] == data[l,1]) { data$group2 <- data[n,1] }
while-loop r
4 Answers

Here is an option using data.table. After grouping by 'ID', select the second observation of 'group' and assign (:=) it to 'group2'

library(data.table)
setDT(df1)[, group2 := group[2], ID]
df1
#   group  ID group2
#1:    aa 123     ab
#2:    ab 123     ab
#3:    bb 345     bb
#4:    bb 345     bb
#5:    bb 999     bb
#6:    bb 999     bb
#7:    cc 567     cd
#8:    cd 567     cd
user_37322710

If you want to do all in one process, you want to tell R that you want the second element of group in group2 for each ID group. If group is in character, you can do the following. Your data is called mydf.

mydf %>%
group_by(ID) %>%
mutate(group2 = group[2])

#  group    ID group2
#  <chr> <int>  <chr>
#1    aa   123     ab
#2    ab   123     ab
#3    bb   345     bb
#4    bb   345     bb
#5    bb   999     bb
#6    bb   999     bb
#7    cc   567     cd
#8    cd   567     cd
user_33044710

Assuming your group column is a character and not a factor, the following trick using dplyr package will mostly work:

library(dplyr)
answer = data %>% mutate(group2 = ifelse(ID == lead(ID), lead(group), group))

I say "mostly work" because the last item will have a group2 value of NA. That's easily fixed, though:

answer$group2[nrow(answer)] = answer$group[nrow(answer)]
user_41879330

In base R

df$group2 = df$group[ave(1:NROW(df), df$ID, FUN = max)]
#If each ID can have more than two rows
#and you specifically want the value from second row, use 
#df$group[ave(1:NROW(df), df$ID, FUN = function(x) x[2])]
df
#  group  ID group2
#1    aa 123     ab
#2    ab 123     ab
#3    bb 345     bb
#4    bb 345     bb
#5    bb 999     bb
#6    bb 999     bb
#7    cc 567     cd
#8    cd 567     cd

You could also use a for loop but its not really necessary

group2 = c()
for(x in df$ID){
    temp = subset(df, df$ID == x)
    group2 = c(group2, temp$group[2])
}
group2
#[1] "ab" "ab" "bb" "bb" "bb" "bb" "cd" "cd"

DATA

df = structure(list(group = c("aa", "ab", "bb", "bb", "bb", "bb", 
"cc", "cd"), ID = c(123L, 123L, 345L, 345L, 999L, 999L, 567L, 
567L)), .Names = c("group", "ID"), row.names = c(NA, -8L), class = "data.frame")
user_71289340
Related