1

I have many csv files that are seperated by day. I wrote a code to manually import each one add a date column and export them again.

But this requires me entering hundreds of file names to change the same code over many lines. I want to create a function that loops through and adds the date column based on the name and then exports a new csv.

By doing this I essentially have to enter every file into this formula to achieve what I want. Is there a way to do this without all of the manual labor.

Oct0418 <- read.csv("10.04.18 (x).csv")
Oct0418$Date <- as.Date("2018/10/04")
write.csv(Oct0418, "10.04.18.csv")

Oct1118 <- read.csv("10.11.18 (x).csv")
Oct1118$Date <- as.Date("2018/10/11")
write.csv(Oct1118, "10.11.18.csv")
M--
  • 25,431
  • 8
  • 61
  • 93
Chris Morton
  • 11
  • 2
  • 1
    There are dozens (more?) of questions on SO when you search for [`[r] read multiple csvs`](https://stackoverflow.com/search?q=%5Br%5D+read+multiple+csvs). The good answers usually include phrases like *"use `lapply` and load them into a list"*. Once you have a named-list of frames, write a small function that adds your column (based on the filename, if necessary, which should be the "names" in the "named-list") and then `lapply` that function to each frame within the list ... and then save them all. – r2evans Jun 12 '19 at 21:51
  • 1
    @r2evans I should've found/linked couple different posts which OP could use to write up their own function (to keep up with the policy of helping users to write code rather than writing codes for them-- fishing and give fish :D) but I just remembered almost 9 years ago that I was asked to do the same. I ended up using `vba` but after I had already edited 100 of files manually. It was sympathy. Cheers. – M-- Jun 12 '19 at 21:58
  • I don't argue you for answering what is undeniably a duplicate (given time I'd look for a perfect fit), and I've been there as well. But `vba`? Ouch, sorry, that is just ... dang. On the topic of teaching to fish ... the SO question/answer-pool is a great fishing location, one can also teach to "fish around" before asking a question. Not being snotty, several questions I've asked or thought to ask turned out to be duplicates, and initially it is not always intuitive which words to focus-on in a search. – r2evans Jun 12 '19 at 22:21
  • @r2evans well OP needs to search for listing all files, reading csv files to r, adding a column, getting part of a string, formatting as date, and writing csv files. A dupe consisting all of that is hard to find. And c'mon, `vba` is not that bad. – M-- Jun 13 '19 at 00:07
  • Search/listing and reading all files is certainly a dupe. Adding a column based on part of the filename is just a regex or even substring, very likely a dupe. Writing csv files from the list of frames is really just an application of list/reading all files. So you are correct in that it is not in its entirety a true dupe, but then again many questions closed as dupes are *strongly related* where the last unique step is left as an (arguably trivial) step for the asker. Regardless, I haven't moved to close it yet, so there's hope :-) – r2evans Jun 13 '19 at 05:50
  • Sorry if it is a duplicate. I spent a considerable amount of time searching, but I guess like M-M said I wasn't searching the correct words. I am always learning so this is a huge help and I did not want to hop into VBA personally (R is fun, VBA not so much). – Chris Morton Jun 13 '19 at 12:21

1 Answers1

2
#list all the csv files in the desired folder
temp    <- list.files(pattern="*.csv")

#read them into r and store them in a list
myfiles <- lapply(temp, read.csv)

#use substr to get the part of their name that is going to be used as a date
#and bind that as a new column to the existing data in `myfiles` list
myfiles <- lapply(1:length(myfiles), 
                    function(i) cbind("Date"=as.Date(substr(temp[i], 1, 8), 
                                                      format = "%m.%d.%y"), 
                                       myfiles[[i]]))

#write these modified datasets back to csv files
           sapply(1:length(myfiles), 
                    function(i) write.csv(myfiles[[i]], temp[i]))
M--
  • 25,431
  • 8
  • 61
  • 93