R Error: new columns would leave holes after existing columns; How to solve this?

Question

I try to run my code but always receive the following error message:

Fehler in `[<-.data.frame`(`*tmp*`, , i, value = list(`NOVN SW Equity PX_LAST` = c(6.54, : new columns would leave holes after existing columns

My code looks as following

library(tidyverse)
Daten_bloomberg <- read_excel("~/Desktop/master thesis topics/Data for R/Daten_bloomberg.xlsx", na = "NA")
Event <- read_excel("~/Desktop/master thesis topics/Data for R/Event.xlsx", col_types = c("numeric", "text"))
Daten_bloomberg <- as.data.frame(sapply(Daten_bloomberg, as.numeric)) 
Daten_bloomberg[is.na(Daten_bloomberg)] <- NA

Data_last_price <- data.frame(Data1 = rep(1,7875) )

for (i in 1:133) {
  EventTicker <-  as.character(Event[i,2])
  EventTicker1 <- paste(as.character(Event[i,2]) , "PX_Last") 
  Data1 <- select(Daten_bloomberg , contains(EventTicker1))
  Data_last_price[,i] <- rep(Data1,1)
}

With that Code I basically try to make a new dataframe with columns containing the ticker and the string PX_Last. Daten_bloomberg contains 7875 rows and 884 variables. Event contains 133 observations and two variables.

However, always when I run this loop the above error message is occuring and the loop stops at position 87. I guess the problem is that the columns in the Dataset Daten_bloomberg contains a lot of NA Values. But I do not know how i can solve that problem. Has anyone an idea?

Oliver Oliver · Accepted Answer · 2021-03-14T17:39:40

Alright we have a few things to tackle here.

First, welcome to SO, and your code is not reproducible, as it is lacking an example dataset. You can also leave out rep(data1, 1) as rep(x, 1) == x.

Next it is incredible that you have obtained your specific error, meaning I had to dig into the source code to find it as I've never seen it in the 6 years of my R experience. And it might not be easy to see, but your error is not from iteration 87 but iteration 86.

Lets illustrate with a reproducible example (notice the names at first).

names(mtcars)
[1] "mpg"  "cyl"  "disp" "hp"   "drat" "wt"   "qsec" "vs"   "am"   "gear" "carb"
library(tidyverse)
mtcars2 <- mtcars
newcols <- c("I'm not column", "cyl") # <= one column exist, one does not
for(i in seq_along(newcols)){
  mtcars[, ncol(mtcars) + i] <- select(mtcars2, contains(newcols[i]))
}
print(i) 
[1] 2

Oh notice how it threw an error on cyl (i = 2), so the most obvious thought is "there's something wrong with my cyl column". But lets look at mtcars for a moment

names(mtcars)
[1] "mpg"  "cyl"  "disp" "hp"   "drat" "wt"   "qsec" "vs"   "am"   "gear" "carb"

Oh.. There's no new column for "I'm not a column" (should be named "V1"), which I tried to place into position 12. Next we try to place cyl into position 13, but there is no column 12, so [<-.data.frame tries to create column 12, but as we haven't supplied any value for this column, it throws an error, as seem in the smaller example below:

mtcars[, 13] <- mtcars2$cyl

So in short, for your specific case running the code below should result in the last line being printed (which is your error)

i <- 86
EventTicker <-  as.character(Event[i,2])
EventTicker1 <- paste(as.character(Event[i,2]) , "PX_Last") 
Data1 <- select(Daten_bloomberg , contains(EventTicker1))
nrow(Data1)
[1] 0

R Error: new columns would leave holes after existing columns; How to solve this?

1 Answers