Learning data.table - how to update values by row number and column name

Question

My last few posts have been written poorly, so I will attempt to do a better and cleaner job this time.

I am learning how to work with the data tables object, and one task I am struggling with is updating values in the data table by both row number and column name at the same time. With data.frames this is much easier and I just do the following:

my_df = as.data.frame(matrix(ncol = 10, nrow = (100)))
names(my_df) = c("P1", "P2", "P3", "P4", "P5", "Q1", "Q2", "Q3", "Q4", "Q5")
head(my_df)

  P1 P2 P3 P4 P5 Q1 Q2 Q3 Q4 Q5
1 NA NA NA NA NA NA NA NA NA NA
2 NA NA NA NA NA NA NA NA NA NA
3 NA NA NA NA NA NA NA NA NA NA
4 NA NA NA NA NA NA NA NA NA NA
5 NA NA NA NA NA NA NA NA NA NA
6 NA NA NA NA NA NA NA NA NA NA

replacement = c(1, 2, 3, 4, 5)
my_df[2, names(my_df)[1:5]] = replacement
head(my_df)

  P1 P2 P3 P4 P5 Q1 Q2 Q3 Q4 Q5
1 NA NA NA NA NA NA NA NA NA NA
2  1  2  3  4  5 NA NA NA NA NA
3 NA NA NA NA NA NA NA NA NA NA
4 NA NA NA NA NA NA NA NA NA NA
5 NA NA NA NA NA NA NA NA NA NA
6 NA NA NA NA NA NA NA NA NA NA

so, fairly easy with a dataframe. however, I am struggling with this same exact task with a datatable. using the same structure for an example data table as i did with the dataframe above, i've tried the following:

my_dt = data.table(matrix(ncol = 10, nrow = (100)))
names(my_dt) = c("P1", "P2", "P3", "P4", "P5", "Q1", "Q2", "Q3", "Q4", "Q5")
head(my_dt)

   P1 P2 P3 P4 P5 Q1 Q2 Q3 Q4 Q5
1: NA NA NA NA NA NA NA NA NA NA
2: NA NA NA NA NA NA NA NA NA NA
3: NA NA NA NA NA NA NA NA NA NA
4: NA NA NA NA NA NA NA NA NA NA
5: NA NA NA NA NA NA NA NA NA NA
6: NA NA NA NA NA NA NA NA NA NA

replacement = c(1, 2, 3, 4, 5)
# my_dt[i == 2, names(my_dt)[1:5]] = replacement
# my_dt[i == 2, names(my_dt)[1:5] := replacement]  
# my_dt[2, names(my_dt)[1:5]] = replacement
# my_dt[2, names(my_dt)[1:5] := replacement]

however none of the four commented lines did the correct substitution. appreciate any help!

Thanks, Canovice

Probably not what you are looking for, but how about writing your stuff into a data frame and then converting it to a data table? — FisherDisinformation
– FisherDisinformation, Commented Aug 9, 2016 at 15:05
i want to use data tables to speed up my code, performance is very important for this project. I frequently have to access subsets of this df / dt and update their values, and i thought accessing subsets of a dt was faster than accessing subsets of a df. so that's why i don't want to write into a dataframe — Canovice
– Canovice, Commented Aug 9, 2016 at 15:08
You shouldn't need this. That you do lets me suspect that you should use a sparse matrix instead of a data.table. — Roland
– Roland, Commented Aug 9, 2016 at 16:49

Sumedh · Accepted Answer · 2016-08-09 15:32:31Z

8

Or you can do this:

x <- names(my_dt)[1:5]

my_dt[, (x) := lapply(.SD, as.numeric), .SDcols = x]

my_dt[2,  (x):= as.list(replacement)]

First we convert the target columns in my_dt to numeric. .SDcols represents the subset of columns in .SD that we are interested in. .SD holds all the columns in the data.table (except the ones used in by).

Once we convert the target columns to numeric, we update the values by reference.

Note: It is not necessary to define x beforehand, everything can be done on the fly. However, if you define x, you need to wrap it in () to make sure data.table doesn't look for the column x

edited Aug 9, 2016 at 15:32

answered Aug 9, 2016 at 15:26

Sumedh

4,9952 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Canovice Over a year ago

yes thanks!, I like both this and mkt's solution, but will use yours because it is important that I initialize with NAs and keep track of NAs in my data table. thank you

Canovice Over a year ago

Last followup, thanks again. Can I update values by reference over multiple rows at a time? For example, if i wanted to update rows 2:5, columns (x) all with the replacement object? Yes I can! okay thanks again

mkt · Accepted Answer · 2016-08-09 15:22:47Z

4

Made a couple of small changes to your example, but this works:

#Filled data.table with integers instead of NAs to avoid converting 
#from logical later
#Left out names as it wasn't relevant to the example
my_dt = as.data.table(matrix(ncol = 10, nrow = (100), 1L))
head(my_dt)

replacement <- 1:5
#Loop through columns and use set to replace values without making a copy
for(k in 1:5) set(my_dt, i = 2L, j = k , value = replacement[k])
head(my_dt)

answered Aug 9, 2016 at 15:22

mkt

4378 silver badges21 bronze badges

2 Comments

Canovice Over a year ago

thanks!. do you think the issue may have been then that I was initializing my dataframe with all NAs?

mkt Over a year ago

No, that was not the only problem. I did that mainly for convenience in my response (it wasn't clear that NAs were important in your example). Your code was also trying to assign all 5 replacement values to each of the row and column elements.

Collectives™ on Stack Overflow

Learning data.table - how to update values by row number and column name

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related