Hi,
The code I am providing is not very beautiful nor elegant, but it should do the trick and hopefully relieve you from your headache:)
library(dplyr)
library(tidyr)
temp = list.files(pattern="*.csv")
myfiles <- lapply(temp, function(filename){
dat <- read.csv(filename, stringsAsFactors = FALSE, header = TRUE, sep = ";")
dat$ID <- as.character(filename) # this adds an ID to each element of the list
return(dat)})
transform_fun <- function(x){
x %>% t() %>% as.data.frame() %>% mutate(Cat = rownames(x %>% t() %>% as.data.frame())) %>%
mutate(ID = x[1,21]) %>%
filter(Cat != "ID") %>%
separate(col = 2, into= c(as.character(1:15)),sep = ",") %>%
gather(subcategory, Value,2:16) %>%
mutate(Category = paste(Cat, subcategory, sep = "_")) %>%
select(Category, ID, Value) %>%
spread(Category, Value)
}
df <- lapply(myfiles, transform_fun) %>% do.call(bind_rows,.)
Hope that helps!
Edit: I changed rbind to bind_rows