CODEWITHSUNDEEP
rindexingdata.table
jester
jester

Reputation: 309

replace value for each row in data table in R

I have a data table, DT, looks like this. For each row, I want to replace one element based on the variable "position" and "value'.

 DT=data.table(ID=c(1,2,3,4,8,6,12,8,9), position=c('A3',      
   'A1','B2','A2','B1','B3','B2','A1','B3'),
   value=c(15,22,92,17,55,37,16,35,13), A1= NA,A2=NA, A3=NA, B1=NA, B2=NA,  
    B3=NA)

         ID position value   A1  A2  A3  B1  B2  B3
 1:      1   A3        15    NA  NA  NA  NA  NA  NA 
 2:      2   A1        22    NA  NA  NA  NA  NA  NA 
 3:      3   B2        92    NA  NA  NA  NA  NA  NA 
 4:      4   A2        17    NA  NA  NA  NA  NA  NA 
 5:      8   B1        55    NA  NA  NA  NA  NA  NA
 6:      6   B3        37    NA  NA  NA  NA  NA  NA 
 7:     12   B2        16    NA  NA  NA  NA  NA  NA
 8:      8   A1        35    NA  NA  NA  NA  NA  NA
 9:      9   B3        13    NA  NA  NA  NA  NA  NA

For row 1, I want to change DT[1,A3 := 15]. A3 is from the position variable form the same row, 15 is from the value column from the same row.

My current code is:

for (i in 1:nrow(DT)){ 
   # find the column number position of the targeted column first
  column_position<-which.first(colnames(DT) == DT$position[i])

  LOB[i,(column_position) := LOB[i,value] ]
}

This is not working since 

  LOB[i,(column_position)]

is not a good indexing, I then tried

     LOB[i,paste0(LOB[i,position])]

for indexing, which also does not work.

Desired output,

         ID position value   A1  A2  A3  B1  B2  B3
 1:      1   A3        15    NA  NA  15  NA  NA  NA 
 2:      2   A1        22    22  NA  NA  NA  NA  NA 
 3:      3   B2        92    NA  NA  NA  NA  92  NA 
 4:      4   A2        17    NA  17  NA  NA  NA  NA 
 5:      8   B1        55    NA  NA  NA  55  NA  NA
 6:      6   B3        37    NA  NA  NA  NA  NA  37 
 7:     12   B2        16    NA  NA  NA  NA  16  NA
 8:      8   A1        35    35  NA  NA  NA  NA  NA
 9:      9   B3        13    NA  NA  NA  NA  NA  13

Thank you for the help,

Upvotes: 0

Views: 2064

Answers (5)

Daniela Costa
Daniela Costa

Reputation: 1

Why not do a dcast?

DT <- DT[,c("ID", "position", "value")]
dcast.data.table(DT, ID ~ position, value.var = "value")


   ID A1 A2 A3 B1 B2 B3
1:  1 NA NA 15 NA NA NA
2:  2 22 NA NA NA NA NA
3:  3 NA NA NA NA 92 NA
4:  4 NA 17 NA NA NA NA
5:  6 NA NA NA NA NA 37
6:  8 35 NA NA 55 NA NA
7:  9 NA NA NA NA NA 13
8: 12 NA NA NA NA 16 NA

Upvotes: 0

Kenneth Rios
Kenneth Rios

Reputation: 146

I'm sure someone more experienced could unloop this, but this works well.

EDIT: Using given construction code:

library('data.table')

DT <- data.table(ID=c(1,2,3,4,8,6,12,8,9), 
                 position=c('A3','A1','B2','A2','B1','B3','B2','A1','B3'),
                 value=c(15,22,92,17,55,37,16,35,13), 
                 A1=NA, A2=NA, A3=NA, B1=NA, B2=NA, B3=NA)

# Convert logical NAs to numeric NAs
DT[, 4:9] <- DT[, lapply(.SD, as.numeric), .SDcols=4:9]

# Generate "slot" vector using matching
slot <- match(DT$position, colnames(DT)[4:9])

# Loop thru each row of DT
for(i in 1:nrow(DT)){
  DT[i, 3+slot[i]] <- DT[i,]$value
}

print(DT)
# ID position value A1 A2 A3 B1 B2 B3
# 1:  1       A3    15 NA NA 15 NA NA NA
# 2:  2       A1    22 22 NA NA NA NA NA
# 3:  3       B2    92 NA NA NA NA 92 NA
# 4:  4       A2    17 NA 17 NA NA NA NA
# 5:  8       B1    55 NA NA NA 55 NA NA
# 6:  6       B3    37 NA NA NA NA NA 37
# 7: 12       B2    16 NA NA NA NA 16 NA
# 8:  8       A1    35 35 NA NA NA NA NA
# 9:  9       B3    13 NA NA NA NA NA 13

Upvotes: 1

Cybernetic
Cybernetic

Reputation: 13334

Original data.table:

enter image description here

Convert data types to character so replacement works:

dt <- dt[, lapply(.SD, as.character), by=ID]

Simple loop for conditionally replacing values in data.table:

for(row in 1:nrow(dt)) {
    pos <- dt[[row,'position']]
    val <- dt[[row,'value']]
    set(dt, i=row, j=pos, value=val)
}

dt

enter image description here

Upvotes: 1

GL_Li
GL_Li

Reputation: 1798

Try not loop through all rows. That will be very slow if you have many many rows. Instead loop though unique positions.

library(data.table)

# do not add columns A1, A2 ,...
DT=data.table(ID=c(1,2,3,4,8,6,12,8,9), 
              position=c('A3', 'A1','B2','A2','B1','B3','B2','A1','B3'),
              value=c(15,22,92,17,55,37,16,35,13))

# find unique positions
aaa <- sort(DT[, unique(position)])

# do not loop all rows, which is slow
for (pos in aaa){
    DT[position == pos, (pos) := value]
}

#    ID position value A1 A2 A3 B1 B2 B3
# 1:  1       A3    15 NA NA 15 NA NA NA
# 2:  2       A1    22 22 NA NA NA NA NA
# 3:  3       B2    92 NA NA NA NA 92 NA
# 4:  4       A2    17 NA 17 NA NA NA NA
# 5:  8       B1    55 NA NA NA 55 NA NA
# 6:  6       B3    37 NA NA NA NA NA 37
# 7: 12       B2    16 NA NA NA NA 16 NA
# 8:  8       A1    35 35 NA NA NA NA NA
# 9:  9       B3    13 NA NA NA NA NA 13

Upvotes: 1

Calum You
Calum You

Reputation: 15072

I think this should work. You can reference columns by name in R. But next time please provide data in a way that we can easily copy into R, either with constructor code, write_csv or dput, since without it we cannot test code.

for (i in 1:nrow(DT)){
    colname <- DT[i, "position"] # Get colname as string
    DT[i, colname] <- DT[i, "value"] # Replace value
}

Upvotes: 0

Related Questions