Extracting dates from filenames and adding them to data.frame

Question

I combine 12 different txt files into a dataframe, which looks like this:

Each file contains a different number of data, and the file name was name with the date, e.g. "Student-Score-2010-10.txt".

Each file represents one month.

How can I add back the date to each row?

   id  dep  score
id511   10     34
id512   10     32
id512   10     34

I need to add back the date to each row

   id  dep  score      date
id511   10     34   2010-10
id511   10     34   2010-10
id511   10     34   2010-10
id511   10     34   2010-11
id511   10     34   2010-11
id511   10     34   2010-12
id511   10     34    2011-1

I made up the date. It is not the real data

orignial data

Monthly report"

"University of XXXXX"

"+--------+------+-----+"

"| id | dep | scores |

"+-------+-----+------+

"| id593 | 2 | 233 |

Use something like `Map(cbind, files, gsub("[A-za-z.-]", "", names(files))` before combining them into one data frame, where `files` is your list of data frames that you've pulled — David Arenburg, Oct 24 '14 at 09:01
@akrun no it's not.I have to put 12 different files into one dataframe/ — Emilia311, Oct 24 '14 at 09:08
@davidArenburg I used your code and it shows "Error in mapply(FUN = f, ..., SIMPLIFY = FALSE) : zero-length inputs cannot be mixed with those of non-zero length" — Emilia311, Oct 24 '14 at 09:15
Please show how your data looks before you combine them into one data frame — David Arenburg, Oct 24 '14 at 09:17
Try `do.call(rbind,lapply(files, function(x) cbind(read.table(x,header=TRUE), date= gsub("[[:alpha:]]+\\-[[:alpha:]]+\\-(.*)\\.txt", "\\1",x))))` — akrun, Oct 24 '14 at 09:18
@DavidArenburg I've just put my original data on the post =) — Emilia311, Oct 24 '14 at 09:25
@Emilia311 I guess this is the data from your previous post. Have you tried the solution in that other post? — akrun, Oct 24 '14 at 09:27
@Emilia311 So, you wanted to work with the same datasets from the list and get into the format you showed? — akrun, Oct 24 '14 at 09:42

score 0 · Answer 1 · answered Oct 24 '14 at 09:58

You could try:

 files <- list.files(pattern="^Student")
  files
 #[1] "Student-Score-2010-10.txt" "Student-Score-2010-11.txt"

 dat<- do.call(rbind,lapply(files, function(x) {
           al <- readLines(x)
           al2 <- grep("id[0-9]", al, value=TRUE)
           al3 <- gsub("^ +| +$", "", gsub("[[:punct:]]+", "", al2))
           al4 <-read.table(text=al3, header=FALSE, sep="", stringsAsFactors=FALSE)
           colnames(al4) <- c("id", "dep", "scores")
           transform(al4, date=gsub("[[:alpha:]]+\\-[[:alpha:]]+\\-(.*)\\.txt",
                                          "\\1",x))}))

  dat
  #   id dep scores    date
  #1 id592   2    235 2010-10
  #2 id593   2    233 2010-11

Extracting dates from filenames and adding them to data.frame

1 Answers1