R matrix transpose (2 into 1 column) many separate files and merge into one .csv

Question

I have ~217 identical .csv files in a folder, each represents one individual, and has 2 columns (header: x, y) and 180 rows of data.

I need to transpose these into a single row (new headers: x1:x180, continued into y1:y180), create an ID column with an abbreviated file name, and merge the separate files into one data frame of 217 rows and an ID columns, and 360 columns of data.

Here's example data from separate .csv files in the same folder, truncated to the first 6 rows:

#dataA_observer_date
          x          y
1 -2.100343 -0.2601952
2 -2.128320 -0.2805480
3 -2.152010 -0.3000733
4 -2.168258 -0.3170724
5 -2.174368 -0.3305717
6 -2.168887 -0.3403942

#dataB_observer_date
          x          y
1 0.7577988 -0.1212715
2 0.7256039 -0.1344822
3 0.6933261 -0.1496408
4 0.6638619 -0.1657460
5 0.6409363 -0.1815894
6 0.6281463 -0.1960087

I need the data to look like this, in one file:

head(dataA)
ID          [x1]     [x2]     [x3]      [x4]      [x5]      [x6]       [y1]      [y2]       [y3]       [y4]       [y5]       [y6] 
dataA  -2.100343 -2.12832 -2.15201 -2.168258 -2.174368 -2.168887 -0.2601952 -0.280548 -0.3000733 -0.3170724 -0.3305717 -0.3403942

dataB       
dataC...
...data217

for transposing, I tried the following, which results in a different column ordersince it works by row through the 180 rows:

t_Image1 <- matrix(t(Image1Coords), nrow = 1)

x1    y1    x2   y2...

I have the file names from the folder in a list using other help from https://stackoverflow.com/questions/31039269/combine-and-transpose-many-fixed-format-dataset-files-quickly

filenames <- list.files(path = "C:/Users/path_to_folder", pattern = "*.csv", full.names = FALSE)
require(data.table)
data_list <- lapply(filenames,read.csv)

But I can't get it to come together. So far, with help from https://stackoverflow.com/questions/21530672/in-r-loop-through-matrix-files-transpose-and-save-with-new-name and several other places, to just transpose the files and resave them to be combined in another step: But the exported file is hideous. The matrix transpose into one row retains quotes and puts 2 data points in one cell, and I'm not sure what its doing as headers, but its all in the first cell

for (i in filenames) {
  mat <- matrix(t(read_table(i, col_names = TRUE, skip_empty_rows = TRUE)), nrow = 1)
  mat$ID <- tools::file_path_sans_ext(basename(filename))
  filename <- paste0("transposed_", i)
  write.table(mat, file = filename)  
}

I have not addressed shortening the file names and making an ID column yet.

Any help/advice would be greatly appreciated.

R matrix transpose (2 into 1 column) many separate files and merge into one .csv

0 Answers0