CODEWITHSUNDEEP
rdataframeapplymatrix-indexingtibble
spindash_st
spindash_st

Reputation: 442

Elegant way of adding columns on a specific position in a data frame

I have a data.frame with 3 cols: date, rate, price. I want to add columns that come from a matrix, after rate and before price.

df = tibble('date' = c('01/01/2000', '02/01/2000', '03/01/2000'),
        'rate' = c(7.50, 6.50, 5.54),
        'price' = c(92, 94, 96))

I computed the lags of rate using a function that outputs a matrix:

rate_Lags = matrix(data = c(NA, 7.50, 5.54, NA, NA, 7.50), ncol=2, dimnames=list(c(), c('rate_tMinus1', 'rate_tMinus2'))

I want to insert those lags after rate (and before price) using names indexing rather than column order.

The add_column function from tibble package (Adding a column between two columns in a data.frame) does not work because it only accepts an atomic vector (hence if I have 10 lags I will have to call add_column 10 times). I could use apply in my rate_Lags matrix. Then, however, I lose the dimnames from my rate_Lags matrix.

Using number indexing (subsetting) (https://stat.ethz.ch/pipermail/r-help/2011-August/285534.html) could work if I knew the position of a specific column name (any function that retrieves the position of a column name?).

Is there any simple way of inserting a bunch of columns in a specific position in a data frame/tibble object?

Upvotes: 8

Views: 5448

Answers (3)

akrun
akrun

Reputation: 887891

We could unclass the dataset to a list and then use append to insert 'rate_Lags' at specific locations, reconvert the list to data.frame

i1 <- match('rate', names(df))
data.frame(append(unclass(df), as.data.frame(rate_Lags), after = i1))
#        date rate rate_tMinus1 rate_tMinus2 price
#1 01/01/2000 7.50           NA           NA    92
#2 02/01/2000 6.50         7.50           NA    94
#3 03/01/2000 5.54         5.54          7.5    96

Or with tidyverse

library(tidyverse)
rate_Lags %>%
       as_tibble %>%
       append(unclass(df), ., after = i1) %>% 
       bind_cols
# A tibble: 3 x 5
#  date        rate rate_tMinus1 rate_tMinus2 price
#  <chr>      <dbl>        <dbl>        <dbl> <dbl>
#1 01/01/2000  7.5         NA            NA      92
#2 02/01/2000  6.5          7.5          NA      94
#3 03/01/2000  5.54         5.54          7.5    96

Upvotes: 1

Rui Barradas
Rui Barradas

Reputation: 76653

Maybe this is not very elegant, but you only call the function once and I believe it's more or less general purpose.

fun <- function(DF, M){
  nms_DF <- colnames(DF)
  nms_M <- colnames(M)
  inx <- which(sapply(nms_DF, function(x) length(grep(x, nms_M)) > 0))
  cbind(DF[seq_len(inx)], M, DF[ seq_along(nms_DF)[-seq_len(inx)] ])
}

fun(df, rate_Lags)
#        date rate rate_tMinus1 rate_tMinus2 price
#1 01/01/2000 7.50           NA           NA    92
#2 02/01/2000 6.50         7.50           NA    94
#3 03/01/2000 5.54         5.54          7.5    96

Upvotes: 1

CPak
CPak

Reputation: 13591

You may be overlooking the following

library(dplyr)
I <- which(names(df) == "rate")
if (I == ncol(df)) {
    cbind(df, rate_Lags)
} else {
    cbind(select(df, 1:I), rate_Lags, select(df, (I+1):ncol(df))) 
}

#     date rate rate_tMinus1 rate_tMinus2 price
# 1 0.0005 7.50           NA           NA    92
# 2 0.0010 6.50         7.50           NA    94
# 3 0.0015 5.54         5.54          7.5    96

Upvotes: 4

Related Questions