Oli Paul Oli Paul - 1 month ago 8
R Question

R folder of csv's into one JSON array

I have a folder of csv's:

Art.csv
Cars.csv
Food.csv


Each csv has a column called
words


I would like to loop through the folder and use the content of the column AND using the title create some JSON in the format of:

{
    "Art" : ['word 1', 'word 2'],
    "Cars" : ['word 1'],
"Food" : ['word 1', 'word 2', 'word 3']
}


So far I have gotten:

files <- list.files(path="sec", pattern=".csv", recursive = F)

for(i in files){s <- read.csv(paste0("Z:/sec/phrases/",i), stringsAsFactors = F, strip.white = T)
s$words <- trim(s$words)
t <- as.vector(s$words)
t <- iconv(t, "UTF-8", "UTF-8", sub='')
t <- toJSON(t)
write(paste0("Z:/sec/json/",i,".json"))}


trim was a function I created because strip.white didnt work in the loop strangely.

The above simply writes the columns in JSON and saves the text into a folder.

Change the name from
.csv.json
to
.json
:

for(i in list.files("Z:/sec/json", full.names = T)){file.rename(from=i,to=sub(pattern=".csv.json",replacement=".json",i))}


There may be a totally different method that is better which I'm more than open to. I have around 5000 files.

EDIT Let me try and be more clear.

What I have managed to create is a folder with json files like:

Art.json
Cars.json
Food.json


Art.json
looks like:

['word 1', 'word 2']


Now what I'd like to do is merge all of these files into one big json like:

{
    "Art" : ['word 1', 'word 2'],
    "Cars" : ['word 1'],
"Food" : ['word 1', 'word 2', 'word 3']
}

Answer

Could you maybe add the next time an example which could be reused like I did in my code. I found the following solution, hopefully it works for you:

file_list <- list.files()
file_list <- c("Art.csv", "Cars.csv", "Food.csv")
# Make an example that can be used
test <- data.frame(words = c("Word 1", "Word 2"))
lapply(file_list, function(x) write.csv(test, x, row.names = F))
list_df <-lapply(file_list, function(x) read.csv(x))
one_list <- lapply(list_df, function(x) x$words)
names(one_list) <- gsub(".csv","",file_list)
toJSON(one_list)
> toJSON(one_list)
[1] "{\"Art\":[\"Word 1\",\"Word 2\"],\"Cars\":[\"Word 1\",\"Word 2\"],\"Food\":[\"Word 1\",\"Word 2\"]}"
Comments