2

I have several different folders which all contain one single .csv file. All of those .csv files have one single column containing the data of one condition of an experiment. I would like to merge those .csv files in such a way that the data of every file is added as a new column.

At the moment, it Looks somehow like this:

C1.csv
102
106
152
196
223
486
553

C2.csv
296
299
843
1033
1996

However, it would like to have one single .csv file, where all the separate files are copied into a new column containing the name of the source file, like:

C1     C2     ...    Cn
102    296    ...    ...
106    299    ...
152    843    ...
196    1033   ...
223    1996   ...
486           ...
553           ...

So far, I the following code:

myFiles = list.files(path = ".", recursive = TRUE, pattern = ".csv", full.names = TRUE)
data <- lapply(myFiles, read.table, sep="\t", header=FALSE)
Max <- max(sapply(data, length))
data <- lapply(data, function(x) c(x, rep(NA, Max - length(x))))
data <- do.call(cbind, data)
names(data) <- sub("^[^[:alnum:]]*([[:alnum:]]+)\\.csv$", "\\1", myFiles)

write.csv(data, "outfile.csv")

It yielded a document that looks like this instead of adding the data of every .csv file in a new column:

enter image description here

Improve this question

2 Answers 2

Reset to default
2

One can read all files using read.table in a list. Combine all data using dplyr::bind_rows. Afterwards, use reshape2::dcast to spread data in wide format with a column for data from every file. 

# Get list of files in directory
fileList <- list.files(".", "*.csv", full.names = TRUE)

# Read file data. This will generate a list containing dataframes
listData <- lapply(fileList, read.table)

# Name list using name of files
names(listData) <- gsub(".csv","",basename(fileList))

library(tidyverse)
library(reshape2)

bind_rows(listData, .id = "FileName") %>%
  group_by(FileName) %>%
  mutate(rowNum = row_number()) %>%
  dcast(rowNum~FileName, value.var = "V1") %>%
  select(-rowNum) %>%
  write.csv(file="Result.csv")

# Content of Result.csv
# "","C1","C2"
# "1",102,296
# "2",106,299
# "3",152,843
# "4",196,1033
# "5",223,1996
# "6",486,NA
# "7",553,NA
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

This seems to work really well! But how can I save it as a new .csv file? I have entered the code write_csv(listData, "C:/Users/XYZ/Desktop/Result.csv") but then this error message appeared: Error: is.data.frame(x) is not TRUE. Is it not possible to use write_csv()here?
@Marc You can have a look at answer now. The result.csv will contain result. If it works, then you can accept answer by clicking on tick symbol in left of answer box.
Nice one, basename, I completely forgot about it.
Thanks. You got a nice answer as well.
2

Is this what you want?
Note that I read the files in with scan. Since the files have only one column there is no need for a complex function like read.csv.

myFiles <- list.files(path = ".", pattern = "^C.*\\.csv", full.names = TRUE, recursive = TRUE)
data <- lapply(myFiles, scan)
Max <- max(sapply(data, length))
data <- lapply(data, function(x) c(x, rep(NA, Max - length(x))))
data <- do.call(cbind, data)
names(data) <- sub("^[^[:alnum:]]*([[:alnum:]]+)\\.csv$", "\\1", myFiles)

write.csv(data, "outfile.csv")

The contents of "outfile.csv" are

"","V1","V2"
"1",102,296
"2",106,299
"3",152,843
"4",196,1033
"5",223,1996
"6",486,NA
"7",553,NA
Improve this answer

2 Comments

It's not yet functioning as it is supposed to be. Now, it yields a .csv document that looks like the one added to the original question.
This is, what I was looking for. Thank you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.