1

I'm fairly new to R and XTS. Pardon the confusing title, the example below should illustrate my question better. The question, How to get value by column name in R?, didn't help me much, perhaps because I'm working with an XTS object.

I have a column of strings that are names of other columns in the XTS object.

xts_bars <- structure(c("1", "1", "1", "1", "-1", "-1", "-1", "-1", "action4",
"action4", "action", "action"), .indexCLASS = c("POSIXct", "POSIXt"),
tclass = c("POSIXct", "POSIXt"), tzone = "", class = c("xts", "zoo"),
index = c(1506620100, 1506620400, 1506620700, 1506621000), .Dim = c(4L, 3L),
.Dimnames = list(NULL, c("action", "action4", "column_names")))

I want to create a new column and populate each row with the value from the named column at each row.

xts_bars$column_names
xts_bars$new_column = xts_bars[,xts_bars$column_names]

This is not working, it's creating 3 extra columns before the 'new_column' column:

                    action action4 column_names action4.1 action4.2 action.1 new_column
2017-09-28 12:35:00 "1"    "-1"    "action4"    "-1"      "-1"      "1"      "1"       
2017-09-28 12:40:00 "1"    "-1"    "action4"    "-1"      "-1"      "1"      "1"       
2017-09-28 12:45:00 "1"    "-1"    "action"     "-1"      "-1"      "1"      "1"       
2017-09-28 12:50:00 "1"    "-1"    "action"     "-1"      "-1"      "1"      "1"

The 'new_column' column should contain -1, -1, 1, 1.

Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
sshemtov
  • 13
  • 5
  • While it is sometimes said that, "a picture is worth a thousand words", this does not apply to pictures of data. Please provide a [reproducible example](https://stackoverflow.com/q/5963269/271616). Your picture of data is not an xts object. You cannot mix types in an xts object, and xts objects do not have a `"Date"` column. – Joshua Ulrich Oct 07 '17 at 00:49
  • @JoshuaUlrich the "Date" column was from the CSV file when write.zoo() the XTS object from your IBrokers package. Regarding your note about not mixing types in XTS objects, in RStudio I got an NA coercion warning when adding the string column, but when I ran that line a second time it forced it into the XTS object, and still showed xts/zoo after class(xts_bars). – sshemtov Oct 07 '17 at 02:42

1 Answers1

0

The first thing that comes to mind is to use a matrix to subset the object. You can't currently do matrix subsetting on xts objects, but you can use coredata() to extract the underlying matrix.

First, you need to construct the matrix you will use to subset the xts object. Each row needs two columns. The first column is the row number of the element to extract, and the second column is the column number of the element to extract. You can think of each row as i, j pairs of the element you want in the extracted vector.

Since you want to extract by column name, you need to find the column number corresponding to each name. You can do that with the match() function. Row numbers are simply 1:nrow(xts_bars).

# Find the value in xts_bar$column_names in colnames(xts_bars)
col_nums <- match(xts_bars$column_names, colnames(xts_bars))
# Create subset matrix
subset_matrix <- cbind(1:nrow(xts_bars), col_nums)

Now you need to subset your xts object using subset_matrix. Since that's not currently possible, you can use coredata() to extract the underlying matrix, then subset that.

# Create new column
xts_bars$new_column <- coredata(xts_bars)[subset_matrix]
xts_bars
#                     action action4 column_names new_column
# 2017-09-28 12:35:00 "1"    "-1"    "action4"    "-1"      
# 2017-09-28 12:40:00 "1"    "-1"    "action4"    "-1"      
# 2017-09-28 12:45:00 "1"    "-1"    "action"     "1"       
# 2017-09-28 12:50:00 "1"    "-1"    "action"     "1"
Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
  • that worked perfectly even across more columns than the sample data. Also appreciate you editing my question, that was helpful for me for future questions. I tried Up-voting your answer but since I'm new to SO it didn't register. Perhaps someone else can Up-vote it for me. Thanks! – sshemtov Oct 07 '17 at 14:25
  • @sshemtov: Glad to help! Thanks for responding positively to my request for a reproducible example; it made it a lot easier to help you. While you may not be able to up-vote, you can still accept my answer by clicking the check mark. That will let others know that your question has been answered to your satisfaction. – Joshua Ulrich Oct 07 '17 at 15:02