Reading in a .csv that contains vectors (c(x,y)) in R

Question

I'm in a bit of a pickle. I have a bunch (thousands) of .csv files where a few lines contain a vector of numbers instead of a single value that I need to read into a tibble or data frame with the vector as a character for further processing. For example:

"col1","col2","col3"
"a",1,integer(0)
"c",c(3,4),5
"e",6,7

should end up as

  col1   col2        col3     
            
1 a         1          integer(0)  
2 c         c(3,4)     5
3 e         7          7

The vector is only ever in "col2" and contains integers. The vector usually contains 2 entries, but it could be more. In reality, there are two columns in the middle that could contain multiple entries, but I know the positions of both.

I can't work out how to read these to R successfully. read.csv or read_csv can't seem to handle them. Is there a way I could read in the files line by line (they're thankfully not long) and eval() the line, maybe, before splitting by commas? I thought about replacing c( with "c( and ) with )" in bash before reading the files in (and will have to do this to integer(.

Alternatively, I've thought of splitting the .csvs in bash into ones that contain "normal" lines and ones that contain the vectors (grep c() but I'm not sure how to then nest 2:length(-1) of the columns back into a vector.

However, I'd definitely prefer a method that was self-contained in R. Any ideas appreciated!

user_5724376 · Answer

I typed your example into a csv file, then brought it in with read.csv and specified that column 2 is character. Using gsub I replace the letter c and the open and close parentheses. Then I loop through column 2 to find cases where a comma appears and convert those instances to a list of integers.

data <- read.csv("SO question.csv", colClasses = c("character","character","integer"))

data$col2 <- gsub("(c|$|$)","",data$col2)

for (i in 1:nrow(data)) {
  
  if (grepl(",", data$col2[i]) == TRUE) {
    
    temp <- unlist(strsplit(data$col2[i],","))
    data$col2[i] <- list(as.integer(temp))
    
  } 
}

data

user_516548 · Answer

It looks like both col2 and col3 have complex contents. Assuming that the possible complex contents are c(...) and integer(0) we enclose both in double quotes and read them in as character converting from character to list in the final line. (We have used the r'{...}' literal string constant notation introduced in R 4.0 to avoid double backslashes. Modify as needed if you are using an earlier version of R.)

library(dplyr)

DF <- "myfile.csv" %>%
  readLines %>%
  gsub(r'{(c$.*?$|integer$0$)}', r'{"\1"}', .) %>%
  read.csv(text = .) %>%
  mutate(across(2:3, ~ lapply(., function(x) eval(parse(text = x)))))

giving:

> str(DF)
'data.frame':   3 obs. of  3 variables:
 $ col1: chr  "a" "c" "e"
 $ col2:List of 3
  ..$ : num 1
  ..$ : num  3 4
  ..$ : num 6
 $ col3:List of 3
  ..$ : int 
  ..$ : num 5
  ..$ : num 7

Note

We assume the file is as shwon reproducibly below:

Lines <- "\"col1\",\"col2\",\"col3\"\n\"a\",1,integer(0)\n\"c\",c(3,4),5\n\"e\",6,7\n"
cat(Lines, file = "myfile.csv")

Reading in a .csv that contains vectors (c(x,y)) in R

2 Answers

Note