Creating multiple columns in data table with a for loop

Question

My data table looks like:

head(data)
    Date         AI   AGI      ADI   ASI   ARI   ERI   NVRI  SRI   FRI  IRI
1: 1991-09-06    NA 2094.19    NA    NA    NA    NA    NA    NA    NA    NA
2: 1991-09-13    NA 2204.94    NA    NA    NA    NA    NA    NA    NA    NA
3: 1991-09-20    NA 2339.10    NA    NA    NA    NA    NA    NA    NA    NA
4: 1991-09-27    NA 2387.81    NA    NA    NA    NA    NA    NA    NA    NA
5: 1991-10-04    NA 2459.94    NA    NA    NA    NA    NA    NA    NA    NA
6: 1991-10-11    NA 2571.07    NA    NA    NA    NA    NA    NA    NA    NA

Don't worry about the NAs. What I want to do is make a "percentage change" column for each of the columns apart from date.

What I've done so far is:

names_no_date <- unique(names(data))[!unique(names(data)) %in% "Date"]

for (i in names_no_date){
      data_ch <- data[, paste0(i, "ch") := i/shift(i, n = 1, type = "lag")-1]}

I get the error:

Error in i/shift(i, n = 1, type = "lag") : 
  non-numeric argument to binary operator

I'm wondering how I get around this error?

please be more clear, what do you mean by "percentage change" -you mean a growth function? — Cyrus Mohammadian
– Cyrus Mohammadian, Commented Sep 14, 2016 at 2:21
You 'get around it' by only passing 'numerics' into a 'binary operator'. Your i / shift(i, ...) is expecting numeric values as both the numerator & denominator. Maybe you need to worry about the NAs? And make sure you're not lagging to a non-existent row. — SymbolixAU
– SymbolixAU, Commented Sep 14, 2016 at 2:28
If you're using data.table, use data.table syntax. To make a new data.table, dt[, lapply(.SD, function(i){i / shift(i)}), .SDcols = -1]. To add on to the existing one; dt[, paste0(names(dt)[-1], 'ch') := lapply(.SD, function(i){i / shift(i)}), .SDcols = -1]. And if you must use for loops, preallocate an object of the appropriate size. — alistaire
– alistaire, Commented Sep 14, 2016 at 4:16

Community · Accepted Answer · 2017-05-23 12:08:20Z

4

i is a string, so you are trying to divide a string in i/shift(i, n = 1, type = "lag"):

> "AI"/NA
Error in "AI"/NA : non-numeric argument to binary operator

Instead, do

for (i in names_no_date){
      data[, paste0(i, "ch") := get(i)/shift(get(i), n = 1, type = "lag")-1]
}

Also see Referring to data.table columns by names saved in variables.

Edit: @Frank writes in the comments that a more concise way to produce OP's output is

data[, paste0(names_no_date, "_pch") := .SD/shift(.SD) - 1, .SDcols=names_no_date]

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Sep 14, 2016 at 3:31

Weihuang Wong

13.2k2 gold badges30 silver badges51 bronze badges

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

6 Comments

Gin_Salmon Over a year ago

Thanks, that's excellent! I read the ?get help file, but i couldn't understand what it does. Do you mind explaining what get() does?

Weihuang Wong Over a year ago

get("x") returns the object with the name "x" in the environment where get was called. For example, try x <- rnorm(5); get("x").

Weihuang Wong Over a year ago

...that's how you get around this error *ducks*

Frank Over a year ago

You can also do it in one step, I guess: data[, paste0(names_no_date, "_pch") := .SD/shift(.SD) - 1, .SDcols=names_no_date]

Gin_Salmon Over a year ago

Hi @Frank, I'm curious, what does the .SD mean?

|

Collectives™ on Stack Overflow

Creating multiple columns in data table with a for loop

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related