CODEWITHSUNDEEP
r
P.Hubert
P.Hubert

Reputation: 147

Copying values from one column to multiple other columns in dependency of another variable

I want to copy values from one column to a new variable, and than add this values to other columns based on conditions.

Minimal Example would be

VP <- c("1","1","2","1","1","2","2","1", "1")
Group <- c("1","1","1","2","2","2","3","3", "3")
Value<-c("6","4","7","2","3","8","4","3", "5")

df <- data.frame(cbind(VP, Group, Value))

The goal would be a result like this:

      VP Group Value NewVariable
      1     1     6     7
      1     1     4     7
      2     1     7
      1     2     2     8
      1     2     3     8
      2     2     8
      2     3     4
      1     3     3     4
      1     3     5     4

So taking the value for VP and copy it to every other person in the corresponding group, except for the own row.

Upvotes: 1

Views: 112

Answers (2)

Uwe
Uwe

Reputation: 42544

One possible approach is updating in a join:

library(data.table)
setDT(df)[df[VP == "2"][, VP := "1"], on = .(VP, Group), NewVariable := i.Value]
df

   VP Group Value NewVariable
1:  1     1     6           7
2:  1     1     4           7
3:  2     1     7        <NA>
4:  1     2     2           8
5:  1     2     3           8
6:  2     2     8        <NA>
7:  2     3     4        <NA>
8:  1     3     3           4
9:  1     3     5           4

Or, with NA replaced:

setDT(df)[df[VP == "2"][, VP := "1"], on = .(VP, Group), NewVariable := i.Value][
  is.na(NewVariable), NewVariable := ""]
df

   VP Group Value NewVariable
1:  1     1     6           7
2:  1     1     4           7
3:  2     1     7            
4:  1     2     2           8
5:  1     2     3           8
6:  2     2     8            
7:  2     3     4            
8:  1     3     3           4
9:  1     3     5           4

Upvotes: 1

Ronak Shah
Ronak Shah

Reputation: 388982

Assuming you would have one value for VP = 2 for every group we could do 

library(dplyr)
df %>%
  group_by(Group) %>%
  mutate(NewVar = ifelse(VP == 2, NA, Value[VP == 2]))


#  VP    Group Value NewVar
#  <chr> <chr> <chr> <chr> 
#1 1     1     6     7     
#2 1     1     4     7     
#3 2     1     7     NA    
#4 1     2     2     8     
#5 1     2     3     8     
#6 2     2     8     NA    
#7 2     3     4     NA    
#8 1     3     3     4     
#9 1     3     5     4

I am returning NA here instead of empty string. You could choose based on your preference.

data

VP <- c("1","1","2","1","1","2","2","1", "1")
Group <- c("1","1","1","2","2","2","3","3", "3")
Value<-c("6","4","7","2","3","8","4","3", "5")
df <- data.frame(VP, Group, Value, stringsAsFactors = FALSE)

Upvotes: 1

Related Questions