R folder of csv's into one JSON array

Question

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']
}

Here is some test data:

file_list <- list.files()
file_list <- c("Art.csv", "Cars.csv", "Food.csv")
test <- data.frame(words = c("Word 1", "Word 2"))
lapply(file_list, function(x) write.csv(test, x, row.names = F))

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.

trim <- function (x) gsub("^\\s+|\\s+$", "", x)

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']
    }

write.table has an option to set if you want to quote (and therefore scape any quote inside), but see [this](http://stackoverflow.com/a/24662360/2886003) answer . Please provide your efforts to create the names without `.csv.txt`. — llrs, Nov 03 '16 at 12:06
That helped, Ive edited the question. The names part is where I made a vector of clean names. But unsure how to add these to the json within the file — Oli, Nov 03 '16 at 12:29
Sorry I don't understand your question. Could you please clarify? — llrs, Nov 03 '16 at 12:31

score 2 · Accepted Answer · answered Nov 03 '16 at 13:33

2

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\"]}"

answered Nov 03 '16 at 13:33

Tobias Dekker

980
8
19

Could you provide some data than I can fix the bug – Tobias Dekker Nov 03 '16 at 14:17
Ignore that, it works when starting fresh. The only issue I have now is I need to remove some whitespace. I tried the following but it failed: list_df <-lapply(files, function(x) read.csv(x, strip.white = T)) – Oli Nov 03 '16 at 14:39
You could add list_df <- lapply(list_df, function(x) gsub(" ", "", x$words)) after the read.csv – Tobias Dekker Nov 03 '16 at 14:50
Ill try that. currently doing: list_df <-lapply(files, function(x){read.csv(x) + trim(read.csv(x)$words)}) where trim <- function (x) gsub("^\\s+|\\s+$", "", x) - some words are actually two words so didnt want to kill all the whitespace – Oli Nov 03 '16 at 14:53
Failed, but I did: two_list <- lapply(list_df3, function(x) trim(x$words)) which looks like its got me there finally – Oli Nov 03 '16 at 15:10
Okay good job next time try to give a better example, it will make life for me and you much easier – Tobias Dekker Nov 03 '16 at 15:12
Will do, dont think the question should be -1 though. – Oli Nov 03 '16 at 16:53

R folder of csv's into one JSON array

1 Answers1